Cannot Register Any Instance of SQL Server 2000 to AD

Hello; I keep getting this message, 'SQL Enterprise Manager could not add
server '(local)' to AD. Error: 14303: Stored procedure
'sp_ActiveDirectory_SCP' failed to access registry key. Now, what does this
mean? I am the SA of this instance; I am the dbo, if you will, also. So, I
have the sufficient permissions to register any instance to AD, right? I even
installed SP4, but that didn't work. I"m a student of sql server services and
I'm in a training class, so I'd appreciate "guru"-level assistance. Thanks.
DominickAre you a local administrator on the machine? The error is telling you
that EM can't access the system registry. You also need the rights on
AD to publish the instance, sorry - I don't recall off hand the exact
permission.
Dominick D. wrote:
> Hello; I keep getting this message, 'SQL Enterprise Manager could not add
> server '(local)' to AD. Error: 14303: Stored procedure
> 'sp_ActiveDirectory_SCP' failed to access registry key. Now, what does this
> mean? I am the SA of this instance; I am the dbo, if you will, also. So, I
> have the sufficient permissions to register any instance to AD, right? I even
> installed SP4, but that didn't work. I"m a student of sql server services and
> I'm in a training class, so I'd appreciate "guru"-level assistance. Thanks.
> Dominick|||Yes, I am a local administrator on the machine where AD is located; I have
enterprise/domain admin rights. I don't understand why EM is not accessing
the registry. Can you dig a little deeper? Get some assistance?
Unfortunately, your response has not helped me.
"PSPDBA" wrote:
> Are you a local administrator on the machine? The error is telling you
> that EM can't access the system registry. You also need the rights on
> AD to publish the instance, sorry - I don't recall off hand the exact
> permission.
>
> Dominick D. wrote:
> > Hello; I keep getting this message, 'SQL Enterprise Manager could not add
> > server '(local)' to AD. Error: 14303: Stored procedure
> > 'sp_ActiveDirectory_SCP' failed to access registry key. Now, what does this
> > mean? I am the SA of this instance; I am the dbo, if you will, also. So, I
> > have the sufficient permissions to register any instance to AD, right? I even
> > installed SP4, but that didn't work. I"m a student of sql server services and
> > I'm in a training class, so I'd appreciate "guru"-level assistance. Thanks.
> >
> > Dominick
>|||Based on some reading, try change the SQL Service account to your
domain admin account, and use your domain admin to run the stored proc.
I would guess that the SQL Service account doesn't have the privileges
to read/edit the registy on the server.
Dominick D. wrote:
> Yes, I am a local administrator on the machine where AD is located; I have
> enterprise/domain admin rights. I don't understand why EM is not accessing
> the registry. Can you dig a little deeper? Get some assistance?
> Unfortunately, your response has not helped me.
> "PSPDBA" wrote:
> > Are you a local administrator on the machine? The error is telling you
> > that EM can't access the system registry. You also need the rights on
> > AD to publish the instance, sorry - I don't recall off hand the exact
> > permission.
> >
> >
> > Dominick D. wrote:
> > > Hello; I keep getting this message, 'SQL Enterprise Manager could not add
> > > server '(local)' to AD. Error: 14303: Stored procedure
> > > 'sp_ActiveDirectory_SCP' failed to access registry key. Now, what does this
> > > mean? I am the SA of this instance; I am the dbo, if you will, also. So, I
> > > have the sufficient permissions to register any instance to AD, right? I even
> > > installed SP4, but that didn't work. I"m a student of sql server services and
> > > I'm in a training class, so I'd appreciate "guru"-level assistance. Thanks.
> > >
> > > Dominick
> >
> >|||OK, I placed the SQLService domain user account into the domain admins group
and I was able to register another instance to the AD, so your advice worked;
however, I was not able to register the default instance to the AD :(. I
don't get that. Any other clue for that reason? Thanks for your help.
"PSPDBA" wrote:
> Based on some reading, try change the SQL Service account to your
> domain admin account, and use your domain admin to run the stored proc.
> I would guess that the SQL Service account doesn't have the privileges
> to read/edit the registy on the server.
>
> Dominick D. wrote:
> > Yes, I am a local administrator on the machine where AD is located; I have
> > enterprise/domain admin rights. I don't understand why EM is not accessing
> > the registry. Can you dig a little deeper? Get some assistance?
> > Unfortunately, your response has not helped me.
> >
> > "PSPDBA" wrote:
> >
> > > Are you a local administrator on the machine? The error is telling you
> > > that EM can't access the system registry. You also need the rights on
> > > AD to publish the instance, sorry - I don't recall off hand the exact
> > > permission.
> > >
> > >
> > > Dominick D. wrote:
> > > > Hello; I keep getting this message, 'SQL Enterprise Manager could not add
> > > > server '(local)' to AD. Error: 14303: Stored procedure
> > > > 'sp_ActiveDirectory_SCP' failed to access registry key. Now, what does this
> > > > mean? I am the SA of this instance; I am the dbo, if you will, also. So, I
> > > > have the sufficient permissions to register any instance to AD, right? I even
> > > > installed SP4, but that didn't work. I"m a student of sql server services and
> > > > I'm in a training class, so I'd appreciate "guru"-level assistance. Thanks.
> > > >
> > > > Dominick
> > >
> > >
>|||Ok, we're getting further. I did a little more reading - check the
MSSQLServerADHelper service and set it to use the domain admin account
too.
Dominick D. wrote:
> OK, I placed the SQLService domain user account into the domain admins group
> and I was able to register another instance to the AD, so your advice worked;
> however, I was not able to register the default instance to the AD :(. I
> don't get that. Any other clue for that reason? Thanks for your help.
> "PSPDBA" wrote:
> > Based on some reading, try change the SQL Service account to your
> > domain admin account, and use your domain admin to run the stored proc.
> >
> > I would guess that the SQL Service account doesn't have the privileges
> > to read/edit the registy on the server.
> >
> >
> > Dominick D. wrote:
> > > Yes, I am a local administrator on the machine where AD is located; I have
> > > enterprise/domain admin rights. I don't understand why EM is not accessing
> > > the registry. Can you dig a little deeper? Get some assistance?
> > > Unfortunately, your response has not helped me.
> > >
> > > "PSPDBA" wrote:
> > >
> > > > Are you a local administrator on the machine? The error is telling you
> > > > that EM can't access the system registry. You also need the rights on
> > > > AD to publish the instance, sorry - I don't recall off hand the exact
> > > > permission.
> > > >
> > > >
> > > > Dominick D. wrote:
> > > > > Hello; I keep getting this message, 'SQL Enterprise Manager could not add
> > > > > server '(local)' to AD. Error: 14303: Stored procedure
> > > > > 'sp_ActiveDirectory_SCP' failed to access registry key. Now, what does this
> > > > > mean? I am the SA of this instance; I am the dbo, if you will, also. So, I
> > > > > have the sufficient permissions to register any instance to AD, right? I even
> > > > > installed SP4, but that didn't work. I"m a student of sql server services and
> > > > > I'm in a training class, so I'd appreciate "guru"-level assistance. Thanks.
> > > > >
> > > > > Dominick
> > > >
> > > >
> >
> >|||OK, I can do that, but, I noticed that there is no such user/group called
MSSQLServerADHelper. Do I have to create it or is it already created?
"PSPDBA" wrote:
> Ok, we're getting further. I did a little more reading - check the
> MSSQLServerADHelper service and set it to use the domain admin account
> too.
>
> Dominick D. wrote:
> > OK, I placed the SQLService domain user account into the domain admins group
> > and I was able to register another instance to the AD, so your advice worked;
> > however, I was not able to register the default instance to the AD :(. I
> > don't get that. Any other clue for that reason? Thanks for your help.
> >
> > "PSPDBA" wrote:
> >
> > > Based on some reading, try change the SQL Service account to your
> > > domain admin account, and use your domain admin to run the stored proc.
> > >
> > > I would guess that the SQL Service account doesn't have the privileges
> > > to read/edit the registy on the server.
> > >
> > >
> > > Dominick D. wrote:
> > > > Yes, I am a local administrator on the machine where AD is located; I have
> > > > enterprise/domain admin rights. I don't understand why EM is not accessing
> > > > the registry. Can you dig a little deeper? Get some assistance?
> > > > Unfortunately, your response has not helped me.
> > > >
> > > > "PSPDBA" wrote:
> > > >
> > > > > Are you a local administrator on the machine? The error is telling you
> > > > > that EM can't access the system registry. You also need the rights on
> > > > > AD to publish the instance, sorry - I don't recall off hand the exact
> > > > > permission.
> > > > >
> > > > >
> > > > > Dominick D. wrote:
> > > > > > Hello; I keep getting this message, 'SQL Enterprise Manager could not add
> > > > > > server '(local)' to AD. Error: 14303: Stored procedure
> > > > > > 'sp_ActiveDirectory_SCP' failed to access registry key. Now, what does this
> > > > > > mean? I am the SA of this instance; I am the dbo, if you will, also. So, I
> > > > > > have the sufficient permissions to register any instance to AD, right? I even
> > > > > > installed SP4, but that didn't work. I"m a student of sql server services and
> > > > > > I'm in a training class, so I'd appreciate "guru"-level assistance. Thanks.
> > > > > >
> > > > > > Dominick
> > > > >
> > > > >
> > >
> > >
>|||Thats a service on the server, not a group/user.
Dominick D. wrote:
> OK, I can do that, but, I noticed that there is no such user/group called
> MSSQLServerADHelper. Do I have to create it or is it already created?
> "PSPDBA" wrote:
> > Ok, we're getting further. I did a little more reading - check the
> > MSSQLServerADHelper service and set it to use the domain admin account
> > too.
> >
> >
> > Dominick D. wrote:
> > > OK, I placed the SQLService domain user account into the domain admins group
> > > and I was able to register another instance to the AD, so your advice worked;
> > > however, I was not able to register the default instance to the AD :(. I
> > > don't get that. Any other clue for that reason? Thanks for your help.
> > >
> > > "PSPDBA" wrote:
> > >
> > > > Based on some reading, try change the SQL Service account to your
> > > > domain admin account, and use your domain admin to run the stored proc.
> > > >
> > > > I would guess that the SQL Service account doesn't have the privileges
> > > > to read/edit the registy on the server.
> > > >
> > > >
> > > > Dominick D. wrote:
> > > > > Yes, I am a local administrator on the machine where AD is located; I have
> > > > > enterprise/domain admin rights. I don't understand why EM is not accessing
> > > > > the registry. Can you dig a little deeper? Get some assistance?
> > > > > Unfortunately, your response has not helped me.
> > > > >
> > > > > "PSPDBA" wrote:
> > > > >
> > > > > > Are you a local administrator on the machine? The error is telling you
> > > > > > that EM can't access the system registry. You also need the rights on
> > > > > > AD to publish the instance, sorry - I don't recall off hand the exact
> > > > > > permission.
> > > > > >
> > > > > >
> > > > > > Dominick D. wrote:
> > > > > > > Hello; I keep getting this message, 'SQL Enterprise Manager could not add
> > > > > > > server '(local)' to AD. Error: 14303: Stored procedure
> > > > > > > 'sp_ActiveDirectory_SCP' failed to access registry key. Now, what does this
> > > > > > > mean? I am the SA of this instance; I am the dbo, if you will, also. So, I
> > > > > > > have the sufficient permissions to register any instance to AD, right? I even
> > > > > > > installed SP4, but that didn't work. I"m a student of sql server services and
> > > > > > > I'm in a training class, so I'd appreciate "guru"-level assistance. Thanks.
> > > > > > >
> > > > > > > Dominick
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >|||OK, how do I access it and by putting the sa into the domain admins group, I
was able to register both the default and named instance on the AD, so your
advice helped. Thanks.
"PSPDBA" wrote:
> Thats a service on the server, not a group/user.
>
> Dominick D. wrote:
> > OK, I can do that, but, I noticed that there is no such user/group called
> > MSSQLServerADHelper. Do I have to create it or is it already created?
> >
> > "PSPDBA" wrote:
> >
> > > Ok, we're getting further. I did a little more reading - check the
> > > MSSQLServerADHelper service and set it to use the domain admin account
> > > too.
> > >
> > >
> > > Dominick D. wrote:
> > > > OK, I placed the SQLService domain user account into the domain admins group
> > > > and I was able to register another instance to the AD, so your advice worked;
> > > > however, I was not able to register the default instance to the AD :(. I
> > > > don't get that. Any other clue for that reason? Thanks for your help.
> > > >
> > > > "PSPDBA" wrote:
> > > >
> > > > > Based on some reading, try change the SQL Service account to your
> > > > > domain admin account, and use your domain admin to run the stored proc.
> > > > >
> > > > > I would guess that the SQL Service account doesn't have the privileges
> > > > > to read/edit the registy on the server.
> > > > >
> > > > >
> > > > > Dominick D. wrote:
> > > > > > Yes, I am a local administrator on the machine where AD is located; I have
> > > > > > enterprise/domain admin rights. I don't understand why EM is not accessing
> > > > > > the registry. Can you dig a little deeper? Get some assistance?
> > > > > > Unfortunately, your response has not helped me.
> > > > > >
> > > > > > "PSPDBA" wrote:
> > > > > >
> > > > > > > Are you a local administrator on the machine? The error is telling you
> > > > > > > that EM can't access the system registry. You also need the rights on
> > > > > > > AD to publish the instance, sorry - I don't recall off hand the exact
> > > > > > > permission.
> > > > > > >
> > > > > > >
> > > > > > > Dominick D. wrote:
> > > > > > > > Hello; I keep getting this message, 'SQL Enterprise Manager could not add
> > > > > > > > server '(local)' to AD. Error: 14303: Stored procedure
> > > > > > > > 'sp_ActiveDirectory_SCP' failed to access registry key. Now, what does this
> > > > > > > > mean? I am the SA of this instance; I am the dbo, if you will, also. So, I
> > > > > > > > have the sufficient permissions to register any instance to AD, right? I even
> > > > > > > > installed SP4, but that didn't work. I"m a student of sql server services and
> > > > > > > > I'm in a training class, so I'd appreciate "guru"-level assistance. Thanks.
> > > > > > > >
> > > > > > > > Dominick
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>

Cannot Register Any Instance of SQL Server 2000 to AD

Hello; I keep getting this message, 'SQL Enterprise Manager could not add
server '(local)' to AD. Error: 14303: Stored procedure
'sp_ActiveDirectory_SCP' failed to access registry key. Now, what does this
mean? I am the SA of this instance; I am the dbo, if you will, also. So, I
have the sufficient permissions to register any instance to AD, right? I eve
n
installed SP4, but that didn't work. I"m a student of sql server services an
d
I'm in a training class, so I'd appreciate "guru"-level assistance. Thanks.
DominickAre you a local administrator on the machine? The error is telling you
that EM can't access the system registry. You also need the rights on
AD to publish the instance, sorry - I don't recall off hand the exact
permission.
Dominick D. wrote:
> Hello; I keep getting this message, 'SQL Enterprise Manager could not add
> server '(local)' to AD. Error: 14303: Stored procedure
> 'sp_ActiveDirectory_SCP' failed to access registry key. Now, what does th
is
> mean? I am the SA of this instance; I am the dbo, if you will, also. So, I
> have the sufficient permissions to register any instance to AD, right? I e
ven
> installed SP4, but that didn't work. I"m a student of sql server services
and
> I'm in a training class, so I'd appreciate "guru"-level assistance. Thanks
.
> Dominick|||Yes, I am a local administrator on the machine where AD is located; I have
enterprise/domain admin rights. I don't understand why EM is not accessing
the registry. Can you dig a little deeper? Get some assistance?
Unfortunately, your response has not helped me.
"PSPDBA" wrote:

> Are you a local administrator on the machine? The error is telling you
> that EM can't access the system registry. You also need the rights on
> AD to publish the instance, sorry - I don't recall off hand the exact
> permission.
>
> Dominick D. wrote:
>|||Based on some reading, try change the SQL Service account to your
domain admin account, and use your domain admin to run the stored proc.
I would guess that the SQL Service account doesn't have the privileges
to read/edit the registy on the server.
Dominick D. wrote:[vbcol=seagreen]
> Yes, I am a local administrator on the machine where AD is located; I have
> enterprise/domain admin rights. I don't understand why EM is not accessing
> the registry. Can you dig a little deeper? Get some assistance?
> Unfortunately, your response has not helped me.
> "PSPDBA" wrote:
>|||OK, I placed the SQLService domain user account into the domain admins group
and I was able to register another instance to the AD, so your advice worked
;
however, I was not able to register the default instance to the AD . I
don't get that. Any other clue for that reason? Thanks for your help.
"PSPDBA" wrote:

> Based on some reading, try change the SQL Service account to your
> domain admin account, and use your domain admin to run the stored proc.
> I would guess that the SQL Service account doesn't have the privileges
> to read/edit the registy on the server.
>
> Dominick D. wrote:
>|||Ok, we're getting further. I did a little more reading - check the
MSSQLServerADHelper service and set it to use the domain admin account
too.
Dominick D. wrote:[vbcol=seagreen]
> OK, I placed the SQLService domain user account into the domain admins gro
up
> and I was able to register another instance to the AD, so your advice work
ed;
> however, I was not able to register the default instance to the AD . I
> don't get that. Any other clue for that reason? Thanks for your help.
> "PSPDBA" wrote:
>|||OK, I can do that, but, I noticed that there is no such user/group called
MSSQLServerADHelper. Do I have to create it or is it already created?
"PSPDBA" wrote:

> Ok, we're getting further. I did a little more reading - check the
> MSSQLServerADHelper service and set it to use the domain admin account
> too.
>
> Dominick D. wrote:
>|||Thats a service on the server, not a group/user.
Dominick D. wrote:[vbcol=seagreen]
> OK, I can do that, but, I noticed that there is no such user/group called
> MSSQLServerADHelper. Do I have to create it or is it already created?
> "PSPDBA" wrote:
>|||OK, how do I access it and by putting the sa into the domain admins group, I
was able to register both the default and named instance on the AD, so your
advice helped. Thanks.
"PSPDBA" wrote:

> Thats a service on the server, not a group/user.
>
> Dominick D. wrote:
>sql

Cannot obtain recordset from stored procedure that contains Exec c

Hopefully someone can help. Using vbscript and ADO, I can call a very simpl
e
stored procedure that returns a recordset w/o any problems. However, when I
call a stored procedure that stuffs records into another table using a SQL
string built and the Exec command and then select the records from that
table--ADO always reports the recordset is closed.
The stored procedure works fine in Query Analyzer. It also doesn't matter
if table is temp or permanent. My workaround is to first call the stored
procedure followed by a second call to get the records. My goal is to use a
temp table in the stored procedure.
Psuedo code of stored procedure (which also uses cursor):
declare @.sqlstr varchar (100)
set @.sqlstr = 'insert into tmptable select field from anothertable'
exec(@.sqpstr)
select * from tmptableAre you setting SET NOCOUNT ON at the begining of the sp?
AMB
"reedholm" wrote:

> Hopefully someone can help. Using vbscript and ADO, I can call a very sim
ple
> stored procedure that returns a recordset w/o any problems. However, when
I
> call a stored procedure that stuffs records into another table using a SQL
> string built and the Exec command and then select the records from that
> table--ADO always reports the recordset is closed.
> The stored procedure works fine in Query Analyzer. It also doesn't matter
> if table is temp or permanent. My workaround is to first call the stored
> procedure followed by a second call to get the records. My goal is to use
a
> temp table in the stored procedure.
> Psuedo code of stored procedure (which also uses cursor):
> declare @.sqlstr varchar (100)
> set @.sqlstr = 'insert into tmptable select field from anothertable'
> exec(@.sqpstr)
> select * from tmptable
>

Cannot load the DLL xpweb70.dll

Hi all
Have you ever used [Web assistant Wizard] in Sql server to create a web page from a predefined [Select] statement or Stored procedure??
when i want to do so and when i complete the Wizard i encounter with the following Error message :

--------
Microsoft SQL-DMO (ODBC SQLState: 42000)
--------
Error 22272: Cannot load the DLL xpweb70.dll, or one of the DLLs it references. Reason: 126(error not found).
--------
OK
--------

Could anyone help me please?
Kind Regards.

Hi ZarrinPour,

I suppose you are using sql2000. Some other users have also encountered this problem and raised it up. Solution is try to install the lasted service package. More informaton you can refer to

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=336312&SiteID=1

orhttp://www.devnewsgroups.net/group/microsoft.public.sqlserver.xml/topic5089.aspx

thanks

This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

Cannot link from strored procedure...Error detected by DLL

Hi,

I'm using Crystal Reports Version 8.5 and SQLServer2000.
The main report is using Stored Procedure as datasource where as 2 sub reports using tables as datasource. I'm linking these subreports with some values from the main report.

When I run the main report it pops up the two error messages saying that

"Cannot link from strored procedure" followed by "Error detected by DLL".

Pls advice. Thanks in advance for your immediate attention.Is that Stored Procedure exits?
If not redesign the report|||Hi Madhi,

Yes, the stored procedure exists and works fine if i remove the sub reports. So I re-designed one of the sub-reports and its working now with out any problems.

Thanks for your reply.

Dubasi

cannot insert to db

Cannot get insert sql to work or stored procedures for some reason. They used to

work perfectly two weeks ago. All connection strings look good.....web config ok.

Can any one suggest where I might check next... Select statements are pulling data

ok from db.....run out of ideas

heres my web config......

I did move my application into another directory.....does this second connection string connect to that new instance of sql server?

<

configurationxmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">

<

connectionStrings>

<

addname="Databasest1000ConnectionString"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Documents and Settings\richard\My Documents\Visual Studio 2005\WebSites\st1000\App_Data\Databasest1000.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True"providerName="System.Data.SqlClient" />

<

addname="ConnectionString"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Databasest1000.mdf;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient" />

</

connectionStrings>|||

Hi,

Please check if you have permission to read and write to

C:\Documents and Settings\richard\My Documents\Visual Studio 2005\WebSites\st1000\App_Data\Databasest1000.mdf

for the ASPNET or NETWORK SERVICE account. I would suggest you put the .mdf file in App_Data folder instead of the folder under documents and settings.

|||

Hi Kevin

Thanks so much for reply.......I,m six months into project and its like someone has turned the light off.

""""Please check if you have permission to read and write to

C:\Documents and Settings\richard\My Documents\Visual Studio 2005\WebSites\st1000\App_Data\Databasest1000.mdf""

how do I do that

|||Go to the folder C:\Documents and Settings\richard\My Documents\Visual Studio 2005\WebSites\st1000\App_Data\, then right click with your mouse on Databasest1000.mdf file and click on Properties. Finally click on the tab Security.|||

I appear to have four group or user names.....strange...where have they come from

1. Administrators(DICKSLAPTOP\Administrators)

2. richard(DICKSLAPTOP\richard)

3. SYSTEM

4. Users(DICKSLAPTOP\Users)

Only the last one ....... (4.)........allows full control.....

all others are greyed out.......1. 2 3.

|||

under the advanced tab.....where 5x named users are listed

the last one Users(DICKSLAPTOP\Users).....seems to be read and execute...only

Cannot get stored procedure to work

I am trying to pass a value from one stored procedure into another. I have got the following stored procedures:

Stored Procedure 1:

ALTER PROCEDUREtest2

(

@.Business_Namenvarchar(50)

)

AS

BEGIN

DECLARE@.Client_IDINT

SET NOCOUNT ON;

INSERT INTOclient_details(Business_Name)VALUES(@.Business_Name)

SELECT@.Client_ID =scope_identity()

IF@.@.NESTLEVEL = 1

SET NOCOUNT OFF

RETURN@.Client_ID

END

Stored Procedure 2 - Taking Client_ID from the proecedure test2

ALTER PROCEDUREdbo.test3

(

@.AddressLine1varchar(MAX),

@.Client_IDINT OUTPUT

)

AS

DECLARE@.NewClient_IDINT

EXECtest2 @.Client_ID = @.NewClient_IDOUTPUT

INSERT INTOclient_premises_address(Client_ID, AddressLine1)VALUES(@.Client_ID, @.AddressLine1)

SELECT@.NewClient_ID

When I run this proecedure I get the following error:

FailedProcedure or function 'test2' expects parameter '@.Business_Name', which was not supplied. Cannot insert the value NULL into column 'Client_ID', table 'C:\INETPUB\WWWROOT\SWWEBSITE\APP_DATA\SOUTHWESTSOLUTIONS.MDF.dbo.client_premises_address'; column does not allow nulls. INSERT fails. The statement has been terminated.

However If I run Stored Procedure Test2 on its own it runs fine

Can anyone help with this issue? Is there something that I have done wrong in my stored procedures?

Also does anyone know If I can use the second stored procedure in a second webpage, but get the value that was created by running the stored proecdure in the previous webpage?

Try to make two changes:

1. in your test2, move@.Client_ID to parameter section

@.Business_Namenvarchar(50),

@.Client_ID int = 0 OUTPUT

2. in your test3, change code as following:

EXEC test 2@.Business_Name, @.NewClient_IDOUTPUT --Here you need to pass @.Business_Name into test2 too.

Then,

INSERT INTOclient_premises_address(Client_ID, AddressLine1)VALUES(@.NewClient_ID , @.AddressLine1)

|||

try this

ALTER PROCEDUREtest2

(

@.Business_Namenvarchar(50)

@.Client_Id INToutput

)

AS

BEGIN

SET NOCOUNT ON;

INSERT INTOclient_details(Business_Name)VALUES(@.Business_Name)

SELECT@.Client_ID =scope_identity()

IF@.@.NESTLEVEL = 1

SET NOCOUNT OFF

END

Stored Procedure 2 - Taking Client_ID from the proecedure test2

ALTER PROCEDUREdbo.test3

(

@.AddressLine1varchar(MAX),

@.Client_IDINT OUTPUT

)

AS

DECLARE@.NewClient_IDINT

EXECtest2 @.NewClient_IDoutput

SET@.Client_ID=@.NewClient_ID

INSERT INTOclient_premises_address(Client_ID, AddressLine1)VALUES(@.Client_ID, @.AddressLine1)

SELECT@.NewClient_ID

Please try doing this, I am in hurry now, will explain you in detail later,

|||

If I change the @.Client_ID and put it in the paramter then when I do my insert, it gives me an error telling me that it needs me to pass in the parameter @.Client_ID, also it doesn't appear to like the change to the second procedure either, and says that I need to pass in the paramter Business_Name.

Do I need to change anything in my insert parameters within by sql datasource to get this working?

|||

Thanks for the suggestion Ritesh, I tried using this code, However I got the following error:

FailedProcedure or function 'test2' expects parameter '@.Client_Id', which was not supplied

Do I need to change anything In my parameters witin my sqldatasource??

|||

Please read my post again. 1).Because in your SP test2, you had @.Business_Name as parameter, you got to pass it in (Even though I don't know what it is). Ohterwise, you need to remove it. 2). After you move @.Client_ID to parameter section and set it as OUTPUT, you still need to pass it in in your test3 but set it as OUTPUT too.

|||

It doesn't like it when I pass @.Business_Name into the second procedure, I get the following:

FailedProcedure or function 'test3' expects parameter '@.Business_Name', which was not supplied.

This is the code, where my insert parameters are:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"

ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

InsertCommand="test2"InsertCommandType="StoredProcedure">

<InsertParameters>

<asp:ControlParameterControlID="TextBoxBusinessName"Name="Business_Name"

PropertyName="Text"Type="String"/>

<asp:ParameterDirection="Output"Name="Client_ID"Type="Int32"/>

</InsertParameters>

</asp:SqlDataSource>

<asp:SqlDataSourceID="SqlDataSource3"runat="server"

ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

InsertCommand="test3"InsertCommandType="StoredProcedure">

<InsertParameters>

<asp:ControlParameterControlID="TextBoxAddressLine1"Name="AddressLine1"

PropertyName="Text"Type="String"/>

<asp:ParameterDirection="Output"Name="Client_ID"Type="Int32"/>

</InsertParameters>

</asp:SqlDataSource>

|||

I asked you to pass @.Business_Name to test3 because I saw you had it as Paramater. But, from your test2, I didn't see you pass it in as Parameter. I am not sure why you had this parameter for test2. Now, this error was caused because the code in test3

EXEC test2 @.Business_Name, @.NewClientID OUTPUT.

If you don't need to pass @.Business_Name into test2 from your test3, you can do

EXEC test2 '', @.NewClientID OUTPUT

Not sure that is your purpose or not.

|||ALTER PROCEDUREtest2

(

@.Business_Namenvarchar(50)

@.Client_Id INToutput

)

AS

BEGIN

SET NOCOUNT ON;

INSERT INTOclient_details(Business_Name)VALUES(@.Business_Name)

SELECT@.Client_ID =scope_identity()

IF@.@.NESTLEVEL = 1

SET NOCOUNT OFF

END

Stored Procedure 2 - Taking Client_ID from the proecedure test2

ALTER PROCEDUREdbo.test3

(

@.AddressLine1varchar(MAX),

@.Client_IDINT OUTPUT

)

AS

DECLARE@.NewClient_IDINT

EXECtest2 @.business_name,@.NewClient_IDoutput -- forgot to pass bussiness name

SET@.Client_ID=@.NewClient_ID

INSERT INTOclient_premises_address(Client_ID, AddressLine1)VALUES(@.Client_ID, @.AddressLine1)

SELECT@.NewClient_ID

--------

EXECtest2 @.business_name,@.NewClient_IDoutput -- forgot to pass bussiness name

please pass this parameter and try again

|||

Do I still need to pass @.Business_Name even though it is not needed in the cient_premises_address table?

The only variable which I wish to pass in from the first procedure is @.Client_ID which is a foreign key in the client_premises_address table

Sorry Im very new to this, so it is taking me a while to grasp

Cannot get Stored Procedure parameters in SMO

Hi ,

I have tried it all and I cannot seem to get the stored Procedure parameters

StoredProcedure sp=new StoredProcedure (mActiveDatabase,spName);

foreach (StoredProcedureParameter spParam in sp.Parameters) {

string fldName = spParam.Name;

}

Now I can see in the Debug Visualizer the spName and other properties

however the parameter.count is 0 even though I know for a fact i have 3 parameters.

Why is not getting them?Bug in SMO or AM I missing something.

Using reflection I get them!!

thanks in advance for your replies.

I modified procedure AfterSelect of smoBrowser.vb in the SMOBrowse sample program as shown below to dump the parameters of the selected stored procedure in the object treeview. It seems to work file:

00129Private Sub AfterSelect(ByVal sender As System.Object, ByVal e As System.Windows.Forms.TreeViewEventArgs)

Handles objectTreeView.AfterSelect

00130Dim oldCursor As Cursor = Me.Cursor

00131Me.Cursor = Cursors.WaitCursor

00132

00133Try

00134Me.propertyGrid1.SelectedObject = e.Node.Tag

00135

00136' *********************************************

00137' * If the node clicked is a stored procedure

00138' * then debug its parameters

00139' *********************************************

00140If TypeOf e.Node.Tag Is StoredProcedure Then

00141Dim p As Parameter

00142Dim sp As StoredProcedure

00143sp = CType(e.Node.Tag, StoredProcedure)

00144

00145

00146For Each p In sp.Parameters

00147Debug.WriteLine(p.Name & " " & p.DataType.ToString)

00148Next

00149

00150End If

00151

00152If TypeOf e.Node.Tag Is IScriptable Then

00153Me.textBox1.Text = GetScriptText(CType(e.Node.Tag, IScriptable))

00154Else

00155Me.textBox1.Text = String.Empty

00156End If

00157

00158Catch ex As SmoException

00159Dim emb As New ExceptionMessageBox(ex)

00160emb.Show(Me)

00161Finally

00162Me.Cursor = oldCursor

00163End Try

00164End Sub

Regards,
Joginder Nahil
www.starprint2000.com

|||

You are a star!!!

You are helping me so much thanks!

One question

To assign an image to the node in smoBrowser did you modify "CreateNode"

and depending on the item you assign the relative picture?Is this how you did it?

Thanks again

|||

Not a problem.

That is exacltly what I did. I took the images from SQL Server Management Studio.

Regards,
Joginder Nahil
www.starprint2000.com

Cannot find either column "dbo" or the user-defined function

Hi,

I have a stored procedure that calls a scalar function. It works fine on my development database, but on my production database, I get this error when trying to execute the procedure:

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.MyFunction", or the name is ambiguous.

However, I am able to execute the function outside of the procedure like this: select dbo.MyFunction(0, 0)

Anyone have idea? I can't figure it out.

Make sure that you have deployed the function ins the right DataBase from which you want to access...Some time this happens that the function will be deployed to a different DataBase or to the master DB by mistake.Also make sure that you grant the exeute permission on function to the user with which you currently trying to execute

|||

Thanks for the reply. It's definitely in the right database, I see it when I run

SELECT * FROM INFORMATION_SCHEMA.Routines

I'm pretty sure the permissions are OK, I am able to execute the function in a SQL query, but it's not working when I execute a stored procedure that uses it.

Any other ideas?

|||

Can u post the code you've written in the sproc and function?

|||

Yes, here it is:

set ANSI_NULLSONset QUOTED_IDENTIFIERONgoCREATE FUNCTION [dbo].[DistanceBetweenTEST]()RETURNSfloatASBEGIN-- test codereturn 10.2345ENDCREATE PROCEDURE [dbo].[md_UserSearchTEST]ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;-- test codeSELECT1WHERE dbo.md_DistanceBetweenTEST() > 5END-- this returns a value as expectedselect dbo.DistanceBetweenTEST()-- this throws an exceptionexec md_UserSearchTEST/*Msg 4121, Level 16, State 1, Procedure md_UserSearchTEST, Line 10Cannot find either column "dbo" or the user-defined function or aggregate "dbo.md_DistanceBetweenTEST", or the name is ambiguous.*/

|||

What exactly are you trying to do? Your function name is DistanceBetweenTEST, but you are calling it withmd_DistanceBetweenTEST? You got it all messed up. Explain what you are trying to do so we can help.

|||

WOW. I don't know why I didn't see that. That was the problem right there.

I think I've been looking at this for too long. Thanks.

Cannot find columns from a stored procedure...

I have an application that I inherited, and I have a annoying problem. We're using stored procedures to return most of our data, and occasionally we receive errors stating that a particular column cannot be found in the resulting data table. When I run the stored procedure against SQL Server I receive the expected output. What would make this random act happen, any ideas?

Also, I keep receiving errors stating that a connection is already open and needs to be closed before an action to the database is performed. I'm explicitly closing each connection in a finally block for every method in my data access code, so a connection should always be closed, right?

Does the code the runs the stored procedure site inside a try...catch, if so make the catch log the parameter that were supplied to the stored procedure. Also I would log the number of rows in the datatable. This should allow the problem to be reproduced.

>before an action to the database is performed
What action(s) give rise to this message.

For completeness, which version of SQL Server and ASP.NET are you using?

|||

Here is an example of the code:

SqlDataAdapter da = new SqlDataAdapter("GetWFSAInfo",SQLcn);
DataSet ds = new DataSet();
da.SelectCommand.Parameters.Add("@.WFSAID",SqlDbType.Int).Value = WFSAID;
da.SelectCommand.CommandType = CommandType.StoredProcedure;

try {
da.Fill(ds,"GetWFSAInfo");
}
catch (Exception ex) {
NotifyOfException (ex);
}
finally {
SQLcn.Close();
}

return ds;

Originally, none of this code was in try/catch/finally blocks at all, so I had to weed through over 11,000 lines of data access code to put these constructs in. That seems to curb some of the problems. I prefer to use a class library that I created that takes care of all of this for me. I swear, object orientation, inheritance and the like and the previous programmer were never introduced. Aggravating to say the least...

NotifyOfException logs the exception to a log file. I am using SQL Server 2000/ASP.NET 1.1.

Thanks for all of your help...

|||

You will need to modify the catch

catch (Exception ex) {
NotifyOfException (ex);
}

to

catch (Exception ex) {
ex.Data = WFSAID;
NotifyOfException (ex);
}

This assumes of course that WFSAID is a variable.

Cannot find stored procedure

I developed a ASP.net web application with a MSDE database backend on my laptop(vs.net 2003 XP Pro), then I transferred the website onto a server(Windows Server 2003) and generated a SQL Server 2000 database from the scripts I exported from MSDE(web administrator). The problem I am having is that it can't find any stored procedures. I keep getting errors when logging on, 'Could not find stored procedure "_myProc" '.
Any one with any clues what might be the problem?
Yes I have changed the connection strings.
Thanks in advance
PPerhaps the owner of the SP is not what you expect? I have not used web_administrator, but perhaps it does not prefix SPs scripted with dbo., and so the SPs are owned by a different user.|||My Error, SP is case sensitive and somehow during the transfer of the site and DB one letter in SP name was changed to upper case. Buggered if I know how.
Have a great weekend Doug
Thanks for the help
Pete

Cannot find stored proc dbo.sp_MSmakeexpandproc

This question has to do with SL Server replication with CRM
I moved the CRM SQL Server mdf & ldf files from the C drive to the E drive
following the instructions in KB872585.
Everything went well except I cannot enable replication.
I performed the following steps:
Reopen Microsoft SQL Enterprise Manager; click Start, point to All Programs,
point to Microsoft SQL Server, and then click Enterprise Manager.
Expand Microsoft SQL Servers, expand SQL Server Group, expand your Microsoft
CRM SQL Server, then expand Replication Monitor, expand Publishers, and
expand your SQL Server Name where you will find the CRMMetaPub and CRMPub
publications.
Select CRMMetaPub and right-click the Snapshot in the right window pane.
Click Start Agent. (When finished the CRMMetaPub snapshot states, "A
snapshot of 79 article(s) was generated." )
Select CRMPub and right-click the Snapshot in the right window pane. Click
Start Agent.
This step fails with the error:
"Cannot find stored proc dbo.sp_MSmakeexpandproc."
When I search both drives for this file, I cannot find it.
I cannot find this error message in the knowledgebase.
Is this issue CRM specific or is it a general SQL Server replication
problem?
Can anyone tell me what is wrong? Right now our CRM is dead in the water.
Sounds like this proc is missing
I'm not sure why this is, but from time to time on this newsgroup and on one
site I have worked on the replication stored procedures go missing. The last
time this happened I think we were pretty sure that the service pack had not
been correctly applied.
Any way you can reapply the sp? Perhaps even SP4?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dave" <dave@.nospam.ru> wrote in message
news:OsotUBNVFHA.3312@.TK2MSFTNGP09.phx.gbl...
> This question has to do with SL Server replication with CRM
> I moved the CRM SQL Server mdf & ldf files from the C drive to the E drive
> following the instructions in KB872585.
> Everything went well except I cannot enable replication.
> I performed the following steps:
> Reopen Microsoft SQL Enterprise Manager; click Start, point to All
Programs,
> point to Microsoft SQL Server, and then click Enterprise Manager.
> Expand Microsoft SQL Servers, expand SQL Server Group, expand your
Microsoft
> CRM SQL Server, then expand Replication Monitor, expand Publishers, and
> expand your SQL Server Name where you will find the CRMMetaPub and CRMPub
> publications.
> Select CRMMetaPub and right-click the Snapshot in the right window pane.
> Click Start Agent. (When finished the CRMMetaPub snapshot states, "A
> snapshot of 79 article(s) was generated." )
> Select CRMPub and right-click the Snapshot in the right window pane. Click
> Start Agent.
> This step fails with the error:
> "Cannot find stored proc dbo.sp_MSmakeexpandproc."
> When I search both drives for this file, I cannot find it.
> I cannot find this error message in the knowledgebase.
> Is this issue CRM specific or is it a general SQL Server replication
> problem?
> Can anyone tell me what is wrong? Right now our CRM is dead in the water.
>
>

Cannot find sp_send_cdosysmail

Hello, everyone:

I want to send email by sp_send_cdosysmail, but I got error,

Server: Msg 2812, Level 16, State 62, Line 3
Could not find stored procedure 'sp_send_cdosysmail'.

Even though I run the query under master database. Is it not available in SQL Server 2000?

This is my query:

declare @.Body varchar(4000)
select @.Body = 'This is a Test Message'
exec sp_send_cdosysmail 'someone@.example.com','someone2@.example.com','Test of CDOSYS',@.Body

Any help will be appreciated.

Thanks

ZYThttp://support.microsoft.com/kb/312839

Cannot find sp_repladdcolumn stored procedure.

I have merge replication set up on several database tables. I was reading
about using sp_repladdcolumn and sp_repldropcolumn to make modification to
the schema since it cannot be done directly with the alter table command.
However, I cannot find either of these store procedures in the list for this
database. I do see several sp_ followed by a long cryptic looking name
comprised of lettes and numbers.
Should the sp_repldropcolumn and sp_repladdcolumn be showing up on a merge
replicated database? If so, do you know of why these are not showing up?
Thanks,
Bill Mitchell
they are in the master database on all SQL Server 2000 systems.
"Bill Mitchell" <bill.mitchell@.jjindustries.com> wrote in message
news:u5M5LEDREHA.1340@.TK2MSFTNGP12.phx.gbl...
> I have merge replication set up on several database tables. I was reading
> about using sp_repladdcolumn and sp_repldropcolumn to make modification to
> the schema since it cannot be done directly with the alter table command.
> However, I cannot find either of these store procedures in the list for
this
> database. I do see several sp_ followed by a long cryptic looking name
> comprised of lettes and numbers.
> Should the sp_repldropcolumn and sp_repladdcolumn be showing up on a merge
> replicated database? If so, do you know of why these are not showing up?
> Thanks,
> Bill Mitchell
>
|||Thanks Hilary! I am just learning SQL Server and was sure I had corrupted
something in my database.
Bill
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:u3qr33EREHA.2716@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> they are in the master database on all SQL Server 2000 systems.
> "Bill Mitchell" <bill.mitchell@.jjindustries.com> wrote in message
> news:u5M5LEDREHA.1340@.TK2MSFTNGP12.phx.gbl...
reading[vbcol=seagreen]
to[vbcol=seagreen]
command.[vbcol=seagreen]
> this
merge[vbcol=seagreen]
up?
>

Cannot find select stored procedure wizard in SQL 2000

I was using the SQL 2000 stored procedure wizard to create stored
procedures. I can create insert, delete and update SPs however I am
not able to create the select SP. The wizard does not offer the option
of selecting "select" type SP. thank you for your replys

comprajHi

Select is not supported through the CREATE PROCEDURE wizard. Try using Query
Analyser and the CREATE PROCEDURE statement described in books online.

http://msdn.microsoft.com/library/d...create_4hk5.asp

If you require select statements for a given table you can right click the
table in object browser and script the select statement to a new window.

John

"compraj" <rajeshgambhir@.hotmail.com> wrote in message
news:b3cb283a.0310220207.12c76283@.posting.google.c om...
> I was using the SQL 2000 stored procedure wizard to create stored
> procedures. I can create insert, delete and update SPs however I am
> not able to create the select SP. The wizard does not offer the option
> of selecting "select" type SP. thank you for your replys
> compraj