ODBC error

Problem: I came in this morning, and our SQL server's IP address was
changed from a static to dynamic IP. I changed it back, but not sure if
that is the cause of the problem I am having.
Running some reports in Access 2000 that queries through ODBC to our SQL
Server 2k. We are getting an error when attempting to run the report.
*start error message
Connection failed:
SQLState: '01S00'
SQL Server Error: 0
[Microsoft][ODBC SQL Server Driver] Invalid connection string attrib
ute
Connection failed:
SQLState: '01000'
SQL Server Error: 10061
[Microsoft][ODBC SQL Server Driver][TCP/IP
Sockets]ConnectionOpen(connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 11
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets] General Netw
ork
Error
*end of error message
What is going on? How do I resolve it? Thanks in advance
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Something was up with the network side of accessing the SQL Server.
Rebooting the server cleared up the connection issues.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Hi! Good Day!
I have problem with my ODBC Data Source(32bit) which cannot
add,configure existing database because it will come up system error
code 31 (Microsoft Access Driver (*.mdb)) was missing or unable to load
the setup or translator library as well as another Micorosoft Office
database.
I'm asking for ur help.
Thank you.
jcp
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message519054.html

Obtaining a value from Openquery?

Hi all,

I have an Informix Dynamic Server linked within my MS SQL 7 server. What I want to achieve is to be able to obtain a value from the informix table and then to use this value to update the MS SQL server table. I am doing this within a trigger on SQL Server. I am not doing this from infromix as I cant get informix to see the SQL Server.

My problem is that I dont know how to assign the query result to a variable so I can use it in my Update. Can anyone help me with my syntax?? Below is my variable settings and query within the Insert trigger...(Not sure if its correct)

DECLARE @.TSQL VARCHAR(100)
DECLARE @.NAMEID VARCHAR(10)

SET @.NAMEID = (Select Inserted.NameID from Inserted)

SET @.TSQL = 'SELECT * FROM OPENQUERY(AUTHTEST, ''Select nar_num from aunrmast where dpid = '' + @.NAMEID + '')'

EXEC (@.TSQL)

How do I set a variable with the nar_num value that I get back from the informix server. Any Help would be great.

Thanks
Anthonyyou would need to fill in the data type for nar_num but give this a try:

DECLARE @.TSQL VARCHAR(100)
, @.NAMEID VARCHAR(10)

create table #tmp(nar_num <data type>)

select @.NAMEID = min(NameID) from Inserted
while (@.NAMEID is not null) begin
SET @.TSQL = 'SELECT * FROM OPENQUERY(AUTHTEST, ''Select nar_num from aunrmast where dpid = '' + @.NAMEID + '')'

truncate table #tmp
insert into #tmp
EXEC (@.TSQL)

select @.NAMEID = min(NameID) from Inserted where nameid > @.NAMEID
end

Please note that I changed things a bit to handle more than one one record.

obtain the result of dynamic query with openrowset

im running a dynamic query with open rowset in it

pseudocode:

@.CMD=declare @. RETURN SELECT @.RETURN =SUM(X) FROM OPENROWSET(....) SELECT @.RETURN

EXEC @.CMD

This pseudocode dipplay the result of @.return

the problem:

capture @.return into @.myvalue outside the dynamic sql scope

something like

Select @.myvalue=exec(@.cmd)

I don't wanna run on ditributed transaction like this

insert mytable

exec(@.cmd)

thanks,

joey

Well, in 2005, you can direct EXEC to execute on a different server, but I think what you want to do is to use sp_executeSQL:

You will have to configure linked servers, (as well as possibly MSDTC for the servers,) but that would be the direction I would head. If you just want a a single row of values, you can use the following type of syntax:

declare @.name sysname

exec [.\sqlexpress].master.dbo.sp_executesql N'select @.name = @.@.servername ',N'@.name sysname output',@.name output

select @.name

I did it with insert into tableName... and it wanted MSDTC to be on.

|||

hi louis,

i'm using sql server 2000

calling sql2k5

regards,

joey

|||sp_executeSQL existed in 2000 in the same manner.|||

thanks

how can i reuse the output parameter

it is within a loop.

got this error

The variable name '@.RESULT' has already been declared. Variable names must be unique within a query batch or stored procedure.

|||

Just declare it once and set it to NULL before you execute the command...

If I am missing the point, can you post the code?

|||

got it.

i have a declare within @.cmd

thanks