Multi Value Parameter Passing in OLEDB or ODBC

Hi Robert
We are integrating Sql server 2005 with sybase through ODBC.We are using
named parameters in the query...query which will fetch data from sybase
database.Now named parameters are those parameters which we specify with
'@.'.i.e. @.emp_id.
As we are conneting sybase from sql server 2005 through ODBC...but ODBC
doesnt support named parameter...so we are using unnamed parameters (?)
that is for e.g.
Select emp_id,emp_name from t_employee where emp_id = ?
but this is single value unnamed paramater...do u have idea how to pass
multivalue unnamed parameter which i can use with "In" clause..
Select emp_id,emp_name from t_employee where emp_id in ()
if i use ? along with "In" clause...it throws an error saying "Multivalue
Parameter is not supported in Data Extension"
Based on your last thread , we try to get something regarding creation of Custom Data Extension, but unfortunately we are not able to find much more on this particular topic.
We are able to find some readymain Custom Data Extension code from following link
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
but unfortunately we are not able to find more from that link also.
Can u plz help us regarding this, as we are passing through very crucial problem.
We will really very glad, if you can help us to solve this problem.
Thanks in advance :).

Take your sql and replace the ? with a valid emp_id and then run the query:

Select emp_id,emp_name from t_employee where emp_id = 2056

Goto report parameters and delete all of the query parameters the sql was using. Then create a report parameter named EmpID. Set in to multivalue. You then can change your sql to the following:

="Select emp_id,emp_name from t_employee where emp_id in (" & Parameters!EmpID.Value & ")"

You have to follow the process as described. Also, if EmpId is a string then you need to surround your values with single quotes. ie. When you select your drop down to choose the EmpID's to run for you should see" '20034' and '23456' etc. This will then pass a comma delimited string value to the SQL.

Mssql2000

Environment: OS-Win2K MSSQL2000
My problem:
I need to insert data from a table in MSSQL2000 to a Sybase V12.5 table.
I did a C++ program (as extended stored procedure-ESP), which runs O.K. if I start it manually.
If I start it thru a MSSQL2000 Job (defining a job, inside it is a SP and inside of it is my start of ESP), then I'm getting some errors.
This is now priority 2.
Priority 1 is:
for testing of this ESP I have to stop MSSQLSERVER service and copy the new version if ESP. And then I'm starting MSSQLSERVER service and SQLSERVERAGENT. Both are running O.K. without any events in Event Viewer.
But if I'd like to start SQL Query Analyzer I can't login into it. I'm getting an error:
Unable to connect to server:Msg 17, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][TCP/IP Socekts] SQL Server does not exist or access denied"
Below are 2 files: ERRRLOG and SQLAGENT.OUT
************************************************** *********************************************
ERRORLOG
************************************************** *********************************************
2003-05-29 08:26:18.22 server Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3)

2003-05-29 08:26:18.32 server Copyright (C) 1988-2000 Microsoft Corporation.
2003-05-29 08:26:18.32 server All rights reserved.
2003-05-29 08:26:18.32 server Server Process ID is 828.
2003-05-29 08:26:18.32 server Logging SQL Server messages in file 'c:\MSSQL\log\ERRORLOG'.
2003-05-29 08:26:18.48 server SQL Server is starting at priority class 'normal'(1 CPU detected).
2003-05-29 08:26:19.60 server SQL Server configured for thread mode processing.
2003-05-29 08:26:19.74 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2003-05-29 08:26:19.79 server Attempting to initialize Distributed Transaction Coordinator.
2003-05-29 08:26:22.69 server Failed to obtain TransactionDispenserInterface: Result Code = 0x80004005
2003-05-29 08:26:23.16 spid3 Starting up database 'master'.
2003-05-29 08:26:27.17 server Using 'SSNETLIB.DLL' version '8.0.194'.
2003-05-29 08:26:27.17 spid5 Starting up database 'model'.
2003-05-29 08:26:27.27 spid3 Server name is 'OTCPSRV01'.
2003-05-29 08:26:27.31 spid8 Starting up database 'msdb'.
2003-05-29 08:26:27.31 spid9 Starting up database 'pubs'.
2003-05-29 08:26:27.31 spid10 Starting up database 'QA'.
2003-05-29 08:26:27.31 spid11 Starting up database 'DEVEXEC'.
2003-05-29 08:26:29.55 spid5 Clearing tempdb database.
2003-05-29 08:26:29.89 spid11 Analysis of database 'DEVEXEC' (7) is 100% complete (approximately 0 more seconds)
2003-05-29 08:26:31.21 spid10 Analysis of database 'QA' (6) is 100% complete (approximately 0 more seconds)
2003-05-29 08:26:34.20 server SQL server listening on Shared Memory.
2003-05-29 08:26:34.20 server SQL Server is ready for client connections
2003-05-29 08:26:37.49 spid5 Starting up database 'tempdb'.
2003-05-29 08:26:38.64 spid5 Analysis of database 'tempdb' (2) is 100% complete (approximately 0 more seconds)
2003-05-29 08:26:39.87 spid3 Recovery complete.
2003-05-29 08:26:58.87 spid51 Using 'xpsqlbot.dll' version '2000.80.194' to execute extended stored procedure 'xp_qv'.
2003-05-29 08:28:03.56 spid53 Using 'xplog70.dll' version '2000.80.194' to execute extended stored procedure 'xp_cmdshell'.
2003-05-29 08:28:05.23 spid53 Using 'xpstar.dll' version '2000.80.194' to execute extended stored procedure 'xp_sqlagent_enum_jobs'.

************************************************** *********************************************
SQLAGENT.OUT
************************************************** *********************************************
2003-05-29 08:26:59 - ? [100] Microsoft SQLServerAgent version 8.00.194 (x86 unicode retail build) : Process ID 1284
2003-05-29 08:26:59 - ? [101] SQL Server OTCPSRV01 version 8.00.194 (0 connection limit)
2003-05-29 08:26:59 - ? [102] SQL Server ODBC driver version 3.80.528
2003-05-29 08:26:59 - ? [103] NetLib being used by driver is DBMSSHRN.DLL; Local host server is (local)
2003-05-29 08:26:59 - ? [310] 1 processor(s) and 768 MB RAM detected
2003-05-29 08:26:59 - ? [339] Local computer is OTCPSRV01 running Windows NT 5.0 (2195) Service Pack 3
2003-05-29 08:27:02 - ? [129] SQLSERVERAGENT starting under Windows NT service control
2003-05-29 08:27:02 - + [260] Unable to start mail session (reason: No mail profile defined)
2003-05-29 08:27:02 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect
2003-05-29 08:28:33 - ! [298] SQLServer Error: 17, SQL Server does not exist or access denied. [SQLSTATE 08001]
2003-05-29 08:28:33 - ! [298] SQLServer Error: 10061, ConnectionOpen (Connect()). [SQLSTATE 01000]
2003-05-29 08:28:33 - ! [382] Logon to server 'OTCPSRV01' failed (ConnAttemptCachableOp)
2003-05-29 08:28:33 - ! [298] SQLServer Error: 17, SQL Server does not exist or access denied. [SQLSTATE 08001]
2003-05-29 08:28:33 - ! [298] SQLServer Error: 10061, ConnectionOpen (Connect()). [SQLSTATE 01000]
2003-05-29 08:28:33 - ! [382] Logon to server 'OTCPSRV01' failed (ConnAttemptCachableOp)
the last 3 lines are repeating ....
The definition of Client Network Utility is O.K. ( it was good until I have started with tests)
SSNETLIB.DLL is V2000.80.194 - is it O.K. or too old for this task?

My question is - what should I do? to make SQL runable?:(May take help of this KBA (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q311836&gssnb=1).|||Originally posted by Satya
May take help of this KBA (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q311836&gssnb=1).

Thank you for your advice. I'll check it just now!:rolleyes: :rolleyes:|||Originally posted by Wedlechowicz
Thank you for your advice. I'll check it just now!:rolleyes: :rolleyes:

Hi Satya,

I have checked it all.
Sorry, but it doesn't work.

And it's funny, but I have set in Services the MSSQLSERV on MANUAL and after reboot that services is started!!! Why? I thought it won't be started after reboot. And there's no Job in MSSQL that could do it automatically.

What do you mean about it all?
Zbig:confused:|||Make sure to make the SQLAgent service manual too.
Start your SQL server service first and then agent service, see what error you will get. Seems your SQL server service is not started correctly. Make sure your agent service is set up correctly including the username and password.|||True, make sure to have necessary privileges for SQL services account.|||Originally posted by Satya
True, make sure to have necessary privileges for SQL services account.
Hi,
I decided to do so:
I reinstalled the whole MSSQL Server, e.g. thru it I have got the "NEW" refreshed Registry (ONLY this and nothing MORE) and all was then O.K.

Thank you for this help!
:) :p