Error Creating DSN using odbcconf.exe for SQL Server

hi there

i want to create a DSN using command line arguments,with Trusted connection=no, Login name= sa password=*******

i try creating like this

C:\>odbcconf.exe /a {CONFIGSYSDSN "SQL Server" "DSN=blabla|Description=blablubb|SERVER=vsnet1|Trusted_Connection=no |Database=dm"}

this work fine, but when i specify the login id & password as

C:\>odbcconf.exe /a {CONFIGSYSDSN "SQL Server" "DSN=blabla|Description=blablubb|SERVER=vsnet1|Trusted_Connection=no|loginid=sa|password=sysadm|Database=dm"}

this throws an error as

CONFIGSYSDSN: Unable to create a data source for the 'SQL Server' Driver: Invalid keyword-value pair with error code :2147467259

I too try the

loginID, login id, login , user, userid, uid

Password, pwd

and many more...

all these throws same error.

Please help me.

Regards,

Thnaks.

Gurpreet S. Gill

Persistence of login credentials in a DSN is not supported (it's insecure). Using trusted connection would be the best way to achieve connecting without specifying credentials since the logged on user credentials is used for authenticating to the server. The command below should create the DSN for you.

odbcconf.exe /a {CONFIGSYSDSN "SQL Server" "DSN=blabla|Description=blablubb|SERVER=vsnet1|Trusted_Connection=Yes|Database=dm"}

Hope this helps

Uwa.

|||

Thanks Uwa Agbonile

this is right, that it is insecure, is there any way to do that, i mean the registry (regedit), or else?

Regards,

Thanks.

Gurpreet S. Gill

|||

Like I suggested already, one secure way to do this is to use integrated security. If you must use SQL Server authentication your options depend on the circumstances of your application. You could code the application to request the credentials from the user or retrieve them from a location you know to be secure.

You could also embed the credentials as part of the connection string directly in your application but again this is insecure and not recommended.

Regards,

Uwa.

|||

Uwa--

I understand, what you want to say.

Thanks for your help.

Regards

Gurpreet S. Gill

Error Creating DSN using odbcconf.exe for SQL Server

hi there

i want to create a DSN using command line arguments,with Trusted connection=no, Login name= sa password=*******

i try creating like this

C:\>odbcconf.exe /a {CONFIGSYSDSN "SQL Server" "DSN=blabla|Description=blablubb|SERVER=vsnet1|Trusted_Connection=no |Database=dm"}

this work fine, but when i specify the login id & password as

C:\>odbcconf.exe /a {CONFIGSYSDSN "SQL Server" "DSN=blabla|Description=blablubb|SERVER=vsnet1|Trusted_Connection=no|loginid=sa|password=sysadm|Database=dm"}

this throws an error as

CONFIGSYSDSN: Unable to create a data source for the 'SQL Server' Driver: Invalid keyword-value pair with error code :2147467259

I too try the

loginID, login id, login , user, userid, uid

Password, pwd

and many more...

all these throws same error.

Please help me.

Regards,

Thnaks.

Gurpreet S. Gill

Persistence of login credentials in a DSN is not supported (it's insecure). Using trusted connection would be the best way to achieve connecting without specifying credentials since the logged on user credentials is used for authenticating to the server. The command below should create the DSN for you.

odbcconf.exe /a {CONFIGSYSDSN "SQL Server" "DSN=blabla|Description=blablubb|SERVER=vsnet1|Trusted_Connection=Yes|Database=dm"}

Hope this helps

Uwa.

|||

Thanks Uwa Agbonile

this is right, that it is insecure, is there any way to do that, i mean the registry (regedit), or else?

Regards,

Thanks.

Gurpreet S. Gill

|||

Like I suggested already, one secure way to do this is to use integrated security. If you must use SQL Server authentication your options depend on the circumstances of your application. You could code the application to request the credentials from the user or retrieve them from a location you know to be secure.

You could also embed the credentials as part of the connection string directly in your application but again this is insecure and not recommended.

Regards,

Uwa.

|||

Uwa--

I understand, what you want to say.

Thanks for your help.

Regards

Gurpreet S. Gill

Error converting data type varchar to numeric.

Hello,
I cannot get the following Insert Command work. I get the error:
Error converting data type varchar to numeric.
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Error converting
data type varchar to numeric.
However 'rate' and 'maximum' variables are declared as Decimal
Dim rate As Decimal
Dim maximumAs Decimal
SqlDataSource1.InsertCommand = "INSERT INTO Example(userName, rate,
maximum, ticket) VALUES('blabla','" & rate & "','" & maximum & "','" &
RadioButtonList1.SelectedValue & "')"
SqlDataSource1.Insert()
CREATE TABLE Example(
userName nvarchar(50),
rate decimal(2, 2),
maximum decimal(6, 2),
ticket nchar(1)
)Try dropping the string delimiters (single quotes). Something like;
"INSERT INTO Example(userName, rate,
maximum, ticket) VALUES('blabla'," & rate & "," & maximum & ",'" &
RadioButtonList1.SelectedValue & "')"
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Dot Net Daddy" wrote:
| Hello,
|
| I cannot get the following Insert Command work. I get the error:
|
|
| Error converting data type varchar to numeric.
| Description: An unhandled exception occurred during the execution of
| the current web request. Please review the stack trace for more
| information about the error and where it originated in the code.
|
|
| Exception Details: System.Data.SqlClient.SqlException: Error converting
|
| data type varchar to numeric.
|
|
| However 'rate' and 'maximum' variables are declared as Decimal
|
|
| Dim rate As Decimal
| Dim maximumAs Decimal
|
|
| SqlDataSource1.InsertCommand = "INSERT INTO Example(userName, rate,
| maximum, ticket) VALUES('blabla','" & rate & "','" & maximum & "','" &
| RadioButtonList1.SelectedValue & "')"
|
|
| SqlDataSource1.Insert()
|
|
| CREATE TABLE Example(
| userName nvarchar(50),
| rate decimal(2, 2),
| maximum decimal(6, 2),
| ticket nchar(1)
| )
||||Hi,
Already tried that. But this time I got the error:
There are fewer columns in the INSERT statement than values specified
in the VALUES clause. The number of values in the VALUES clause must
match the number of columns specified in the INSERT statement.
Dave Patrick wrote:
> Try dropping the string delimiters (single quotes). Something like;
> "INSERT INTO Example(userName, rate,
> maximum, ticket) VALUES('blabla'," & rate & "," & maximum & ",'" &
> RadioButtonList1.SelectedValue & "')"
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "Dot Net Daddy" wrote:
> | Hello,
> |
> | I cannot get the following Insert Command work. I get the error:
> |
> |
> | Error converting data type varchar to numeric.
> | Description: An unhandled exception occurred during the execution of
> | the current web request. Please review the stack trace for more
> | information about the error and where it originated in the code.
> |
> |
> | Exception Details: System.Data.SqlClient.SqlException: Error converting
> |
> | data type varchar to numeric.
> |
> |
> | However 'rate' and 'maximum' variables are declared as Decimal
> |
> |
> | Dim rate As Decimal
> | Dim maximumAs Decimal
> |
> |
> | SqlDataSource1.InsertCommand = "INSERT INTO Example(userName, rate,
> | maximum, ticket) VALUES('blabla','" & rate & "','" & maximum & "','" &
> | RadioButtonList1.SelectedValue & "')"
> |
> |
> | SqlDataSource1.Insert()
> |
> |
> | CREATE TABLE Example(
> | userName nvarchar(50),
> | rate decimal(2, 2),
> | maximum decimal(6, 2),
> | ticket nchar(1)
> | )
> ||||I'm not dotnet savvy but you might try something to the effect of;
MsgBox SqlDataSource1.InsertCommand
to see the actual SQL being passed.
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Dot Net Daddy" wrote:
| Hi,
|
| Already tried that. But this time I got the error:
|
| There are fewer columns in the INSERT statement than values specified
| in the VALUES clause. The number of values in the VALUES clause must
| match the number of columns specified in the INSERT statement.|||thank you so much.. that helped..
silly me.. I was passing the logon name to the database, which was
causing the problem..
thanks for your help...
Dot Net Daddy wrote:
> Hi,
> Already tried that. But this time I got the error:
> There are fewer columns in the INSERT statement than values specified
> in the VALUES clause. The number of values in the VALUES clause must
> match the number of columns specified in the INSERT statement.
>
>
> Dave Patrick wrote:
> > Try dropping the string delimiters (single quotes). Something like;
> >
> > "INSERT INTO Example(userName, rate,
> > maximum, ticket) VALUES('blabla'," & rate & "," & maximum & ",'" &
> > RadioButtonList1.SelectedValue & "')"
> >
> > --
> >
> > Regards,
> >
> > Dave Patrick ...Please no email replies - reply in newsgroup.
> > Microsoft Certified Professional
> > Microsoft MVP [Windows]
> > http://www.microsoft.com/protect
> >
> > "Dot Net Daddy" wrote:
> > | Hello,
> > |
> > | I cannot get the following Insert Command work. I get the error:
> > |
> > |
> > | Error converting data type varchar to numeric.
> > | Description: An unhandled exception occurred during the execution of
> > | the current web request. Please review the stack trace for more
> > | information about the error and where it originated in the code.
> > |
> > |
> > | Exception Details: System.Data.SqlClient.SqlException: Error converting
> > |
> > | data type varchar to numeric.
> > |
> > |
> > | However 'rate' and 'maximum' variables are declared as Decimal
> > |
> > |
> > | Dim rate As Decimal
> > | Dim maximumAs Decimal
> > |
> > |
> > | SqlDataSource1.InsertCommand = "INSERT INTO Example(userName, rate,
> > | maximum, ticket) VALUES('blabla','" & rate & "','" & maximum & "','" &
> > | RadioButtonList1.SelectedValue & "')"
> > |
> > |
> > | SqlDataSource1.Insert()
> > |
> > |
> > | CREATE TABLE Example(
> > | userName nvarchar(50),
> > | rate decimal(2, 2),
> > | maximum decimal(6, 2),
> > | ticket nchar(1)
> > | )
> > ||||Good to hear. You're welcome.
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Dot Net Daddy" wrote:
| thank you so much.. that helped..
|
| silly me.. I was passing the logon name to the database, which was
| causing the problem..
|
| thanks for your help...|||I see you have found a resolution to your immediate problem but I want to
point out that this code has a serious vulnerability to SQL injection. I
strongly recommend that you use command parameters instead of string
concatenation and perhaps also use only stored procedures so that direct
table permissions are not needed.
Google "SQL injection" find many discussions on the topic.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dot Net Daddy" <cagriandac@.gmail.com> wrote in message
news:1156041975.673742.64030@.h48g2000cwc.googlegroups.com...
> Hello,
> I cannot get the following Insert Command work. I get the error:
>
> Error converting data type varchar to numeric.
> Description: An unhandled exception occurred during the execution of
> the current web request. Please review the stack trace for more
> information about the error and where it originated in the code.
>
> Exception Details: System.Data.SqlClient.SqlException: Error converting
> data type varchar to numeric.
>
> However 'rate' and 'maximum' variables are declared as Decimal
>
> Dim rate As Decimal
> Dim maximumAs Decimal
>
> SqlDataSource1.InsertCommand = "INSERT INTO Example(userName, rate,
> maximum, ticket) VALUES('blabla','" & rate & "','" & maximum & "','" &
> RadioButtonList1.SelectedValue & "')"
>
> SqlDataSource1.Insert()
>
> CREATE TABLE Example(
> userName nvarchar(50),
> rate decimal(2, 2),
> maximum decimal(6, 2),
> ticket nchar(1)
> )
>

Error converting data type varchar to numeric.

Hello,
I cannot get the following Insert Command work. I get the error:
Error converting data type varchar to numeric.
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Error converting
data type varchar to numeric.
However 'rate' and 'maximum' variables are declared as Decimal
Dim rate As Decimal
Dim maximumAs Decimal
SqlDataSource1.InsertCommand = "INSERT INTO Example(userName, rate,
maximum, ticket) VALUES('blabla','" & rate & "','" & maximum & "','" &
RadioButtonList1.SelectedValue & "')"
SqlDataSource1.Insert()
CREATE TABLE Example(
userName nvarchar(50),
rate decimal(2, 2),
maximum decimal(6, 2),
ticket nchar(1)
)Try dropping the string delimiters (single quotes). Something like;
"INSERT INTO Example(userName, rate,
maximum, ticket) VALUES('blabla'," & rate & "," & maximum & ",'" &
RadioButtonList1.SelectedValue & "')"
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Dot Net Daddy" wrote:
| Hello,
|
| I cannot get the following Insert Command work. I get the error:
|
|
| Error converting data type varchar to numeric.
| Description: An unhandled exception occurred during the execution of
| the current web request. Please review the stack trace for more
| information about the error and where it originated in the code.
|
|
| Exception Details: System.Data.SqlClient.SqlException: Error converting
|
| data type varchar to numeric.
|
|
| However 'rate' and 'maximum' variables are declared as Decimal
|
|
| Dim rate As Decimal
| Dim maximumAs Decimal
|
|
| SqlDataSource1.InsertCommand = "INSERT INTO Example(userName, rate,
| maximum, ticket) VALUES('blabla','" & rate & "','" & maximum & "','" &
| RadioButtonList1.SelectedValue & "')"
|
|
| SqlDataSource1.Insert()
|
|
| CREATE TABLE Example(
| userName nvarchar(50),
| rate decimal(2, 2),
| maximum decimal(6, 2),
| ticket nchar(1)
| )
||||Hi,
Already tried that. But this time I got the error:
There are fewer columns in the INSERT statement than values specified
in the VALUES clause. The number of values in the VALUES clause must
match the number of columns specified in the INSERT statement.
Dave Patrick wrote:
> Try dropping the string delimiters (single quotes). Something like;
> "INSERT INTO Example(userName, rate,
> maximum, ticket) VALUES('blabla'," & rate & "," & maximum & ",'" &
> RadioButtonList1.SelectedValue & "')"
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "Dot Net Daddy" wrote:
> | Hello,
> |
> | I cannot get the following Insert Command work. I get the error:
> |
> |
> | Error converting data type varchar to numeric.
> | Description: An unhandled exception occurred during the execution of
> | the current web request. Please review the stack trace for more
> | information about the error and where it originated in the code.
> |
> |
> | Exception Details: System.Data.SqlClient.SqlException: Error converting
> |
> | data type varchar to numeric.
> |
> |
> | However 'rate' and 'maximum' variables are declared as Decimal
> |
> |
> | Dim rate As Decimal
> | Dim maximumAs Decimal
> |
> |
> | SqlDataSource1.InsertCommand = "INSERT INTO Example(userName, rate,
> | maximum, ticket) VALUES('blabla','" & rate & "','" & maximum & "','" &
> | RadioButtonList1.SelectedValue & "')"
> |
> |
> | SqlDataSource1.Insert()
> |
> |
> | CREATE TABLE Example(
> | userName nvarchar(50),
> | rate decimal(2, 2),
> | maximum decimal(6, 2),
> | ticket nchar(1)
> | )
> ||||I'm not dotnet savvy but you might try something to the effect of;
MsgBox SqlDataSource1.InsertCommand
to see the actual SQL being passed.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Dot Net Daddy" wrote:
| Hi,
|
| Already tried that. But this time I got the error:
|
| There are fewer columns in the INSERT statement than values specified
| in the VALUES clause. The number of values in the VALUES clause must
| match the number of columns specified in the INSERT statement.|||thank you so much.. that helped..
silly me.. I was passing the logon name to the database, which was
causing the problem..
thanks for your help...
Dot Net Daddy wrote:[vbcol=seagreen]
> Hi,
> Already tried that. But this time I got the error:
> There are fewer columns in the INSERT statement than values specified
> in the VALUES clause. The number of values in the VALUES clause must
> match the number of columns specified in the INSERT statement.
>
>
> Dave Patrick wrote:|||Good to hear. You're welcome.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Dot Net Daddy" wrote:
| thank you so much.. that helped..
|
| silly me.. I was passing the logon name to the database, which was
| causing the problem..
|
| thanks for your help...|||I see you have found a resolution to your immediate problem but I want to
point out that this code has a serious vulnerability to SQL injection. I
strongly recommend that you use command parameters instead of string
concatenation and perhaps also use only stored procedures so that direct
table permissions are not needed.
Google "SQL injection" find many discussions on the topic.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dot Net Daddy" <cagriandac@.gmail.com> wrote in message
news:1156041975.673742.64030@.h48g2000cwc.googlegroups.com...
> Hello,
> I cannot get the following Insert Command work. I get the error:
>
> Error converting data type varchar to numeric.
> Description: An unhandled exception occurred during the execution of
> the current web request. Please review the stack trace for more
> information about the error and where it originated in the code.
>
> Exception Details: System.Data.SqlClient.SqlException: Error converting
> data type varchar to numeric.
>
> However 'rate' and 'maximum' variables are declared as Decimal
>
> Dim rate As Decimal
> Dim maximumAs Decimal
>
> SqlDataSource1.InsertCommand = "INSERT INTO Example(userName, rate,
> maximum, ticket) VALUES('blabla','" & rate & "','" & maximum & "','" &
> RadioButtonList1.SelectedValue & "')"
>
> SqlDataSource1.Insert()
>
> CREATE TABLE Example(
> userName nvarchar(50),
> rate decimal(2, 2),
> maximum decimal(6, 2),
> ticket nchar(1)
> )
>

Error converting data type varchar to datetime.

Hi,

It is not exactly what I stated in the subject - It's an outcome - exception thrown while executing non-query command.

I get this exception when I try to execute my stored procedure that takes datetime as one of its parameters.

I am using dataset designer to create table adapters and build queries. Then I simply use objectdatasource component that uses one of the table adapters and bind it to for example a detailsview control.

When I run this in debug mode and trace the objects everything looks perfect including these datetime parameters. It is sql server that throws the exception. I ran the sql profiler to see what exactly is going on, and I captured the command that is sent by ADO - it's broken into several lines right in the middle of my datetime parameters... this is the source of the problem. Everything is working fine when I take this command and execute it as a single line in the sql management studio.

Is there anything about ADO that I do not know?

Is the error only with that particular SQL statement or anyother query with date? What datatype you are passing to the stored procedure? DateTime or varchar?

Thanks

|||

Are you passing date as string ? Are you sure that you date format is the same as default date format on SQL server?

If your date is not in valid format for SQL you can have this kind of problems.

Thanks

Error concatenating string

I am trying to create a job that, as one of its steps, will kick off a
DTS package. As part of the command parameter, I need to concat a
system variable (@.@.SERVERNAME) to a constant string. I am receiving an
error about incorrect syntax near the +.

Here is the code for the job step.

-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep@.job_id = @.JobID,
@.step_id = 1,
@.step_name = N'Import OCC Series Data',
@.command = N'DTSRun /F
D:\Databases\Scripts\DTS\ImportOCCSeriesData.dts /A DbName:8=' +
@.@.SERVERNAME,
@.database_name = N'',
@.server = N'',
@.database_user_name = N'',
@.subsystem = N'CmdExec',
@.cmdexec_success_code = 0,
@.flags = 2,
@.retry_attempts = 0,
@.retry_interval = 1,
@.output_file_name = N'',
@.on_success_step_id = 0,
@.on_success_action = 3,
@.on_fail_step_id = 0,
@.on_fail_action = 3
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback

If I just try SELECT N'DTSRun /F
D:\Databases\Scripts\DTS\ImportOCCSeriesData.dts /A DbName:8=' +
@.@.SERVERNAME, everything works fine. I even tried declaring a local
variable named @.command and setting it in the select statement, but no
dice."Jason" <JayCallas@.hotmail.com> wrote in message
news:f01a7c89.0402051028.4aa7015e@.posting.google.c om...
> I am trying to create a job that, as one of its steps, will kick off a
> DTS package. As part of the command parameter, I need to concat a
> system variable (@.@.SERVERNAME) to a constant string. I am receiving an
> error about incorrect syntax near the +.
> Here is the code for the job step.
> -- Add the job steps
> EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID,
> @.step_id = 1,
> @.step_name = N'Import OCC Series Data',
> @.command = N'DTSRun /F
> D:\Databases\Scripts\DTS\ImportOCCSeriesData.dts /A DbName:8=' +
> @.@.SERVERNAME,
> @.database_name = N'',
> @.server = N'',
> @.database_user_name = N'',
> @.subsystem = N'CmdExec',
> @.cmdexec_success_code = 0,
> @.flags = 2,
> @.retry_attempts = 0,
> @.retry_interval = 1,
> @.output_file_name = N'',
> @.on_success_step_id = 0,
> @.on_success_action = 3,
> @.on_fail_step_id = 0,
> @.on_fail_action = 3
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> If I just try SELECT N'DTSRun /F
> D:\Databases\Scripts\DTS\ImportOCCSeriesData.dts /A DbName:8=' +
> @.@.SERVERNAME, everything works fine. I even tried declaring a local
> variable named @.command and setting it in the select statement, but no
> dice.

You can't build a parameter value 'dynamically' like that, but assigning the
entire string to a variable first should work:

declare @.mycommand nvarchar(1000)
set @.mycommand = N'DTSRun /F
D:\Databases\Scripts\DTS\ImportOCCSeriesData.dts /A DbName:8=' +
@.@.SERVERNAME

EXECUTE msdb.dbo.sp_add_jobstep
...
@.command = @.mycommand
...

What error did you get when you tried this?

Simon|||I ended up figuring that out and doing exactly what you suggested.
Something I did not know about passing parameters to stored
procedures...

The error I had gotten was
Incorrect syntax at '+'

Thanks for the help.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Error at parse query

Hello

I have the following:

One data flow task in the control flow
In this data flow task I have an OLE DB Source with the following sql command

'.... where cc = ?'

Then I click on the Parameters button and I map the ? to the right Parameter

Parameter0 = User::var_cc

When I click on parse query I get the following message :

"Parameter Information cannot be derived from SQL statements. Set parameter information before preparing command."

Anyone has an idea?
ThxThis is a known issue. Parse Query does not know how to use the parameters you mapped.

If you do not get any error after clicking OK, your query should be fine and you can safely ignore the error from Parse Query.

Error at parameter 34 during datastream processing of parameterized command.

replication is failing with mssg "Error at parameter 34 during datastream processing of parameterized command." any input what could be the reason for this failure and what action should i take to sort out this issue ?
From http://www.developmentnow.com/g/114_2003_7_22_0_0/sql-server-replication.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com
Not sure about this message. Have seen it reported a couple of times in the
newsgroup without there ever being a definitive fix (AFAIR). Please can you
tell us all the relevant info: type of replication, sp levels etc. Also do
some logging to try to get a more detailed message.
Rgds,
Paul Ibison

Error accessing stored procedure containing a temp table from VB

I am getting an error trying to generate a disconnected recordset from a SQL 2000 database. The application is in VB 6. I am using an ADO Command object.

This code works fine for most of my storer procedures. 2 procedures make use of temporary tables. The results set being passed top VB is a select from the temp table.

When I use the temp table I get an error that states:
"Cannot change the ActiveConnection property of a Recordset object which has a Command object as its source."

If I check the recordcount of rstResults, in the immediate window, after the .open method I get an error stating that the object is not open.

Again this is only for the stored procedure that Selects from a temp table.

Here is the code:

On Error GoTo ErrHandler

Dim rstResults As ADODB.Recordset
Dim conSQL As ADODB.Connection
Dim comFetch As ADODB.Command

Set conSQL = New Connection
conSQL.Open gstrAdminConn

'-- Associate the Command object to stored procedure --
Set comFetch = New ADODB.Command

With comFetch
Set .ActiveConnection = conSQL
.CommandType = adCmdStoredProc
.CommandText = "FetchChildren"
.Parameters("@.PageCode") = strPageCode
.Execute
End With

Set rstResults = New ADODB.Recordset
With rstResults

'-- Open a disconnected recordset off of the command --
.CursorLocation = adUseClient
.Open comFetch, , adOpenKeyset, adLockBatchOptimistic

'-- Dissociate command from connection --
Set comFetch.ActiveConnection = Nothing
Set comFetch = Nothing

'-- Dissociate recordset from connection --
'Set .ActiveConnection = Nothing

End With

Set GetChildren = rstResults

Exit Function

ErrHandler:
err.Raise vbObjectError + 1001, "basPages.GetChildren", Error$try using a

"set nocount on" at the begining of your procedure.|||Thanks. The SET NOCOUNT ON worked.

Why was that causing the error?