problem with linked servers and INSERT

Hello

I have 2 linked SQL servers, trying to communicate with each other. An SP on the one server calls a function on the other to insert the data into a temporary table, but this makes the whole SP freeze. If I just call the function to view the data, it works the fine.

Here's the code:

-------

create table #b (type int,label varchar(100),x int,y int,so int)

declare @.arg nvarchar(100)
set @.arg = 'BLANKET.MaalGrupper.ID38'

declare @.tsql varchar(1000)
select @.tsql = 'select * from openquery( [erinyes.resultmaker.com], ''select * from blanketter.dbo.fnSelect1( '' + @.arg + '' )'' )'
insert #b exec (@.tsql)

drop table #b

--------

I use the openquery function so I can provide the fnSelect1 function with a dynamically generated argument (@.arg). If I remove the 'insert #b' part of the next to last line, it works fine. I get the same behaviour if I use an SP instead of the fnSelect1 function.

Can anybody help with this very irritating problem?

Thanks
MNJdoesn't anybody have an idea of might be wrong?|||Below is working for me (SQL2000)

-- on linked server
CREATE FUNCTION getit (@.id int=null)
RETURNS TABLE
AS
RETURN (SELECT *
FROM sysobjects WHERE id = coalesce(@.id,id))
go
select * from getit(null)
-------------
create table #b (label varchar(100))
insert #b
select * from openquery(linked,'select name from testDB.dbo.getit(null)')

declare @.tsql varchar(1000)
select @.tsql = 'select * from openquery(linked,''select name from testDB.dbo.getit(null)'')'
insert #b exec (@.tsql)|||Do you have Distributed Transaction Coordinator running on both machines?|||Hm, the first select works for me, the second doesn't. And I need to be able to specify a parameter.

DTC is running on both machines.

MNJ|||Have you set any special environment variables or anything like that?

MNJ|||Have you set any special environment variables or anything like that?

MNJ
Nothing special...sql

Problem with Extended Stored Procedure calling an WebService...

Hi all!
I have a problem with a Extended Stored Procedure that calls a
WebService...I'm using SOAP to call the WebService and I'm using an
TokenManager that I have written myself...I have WSE 2.0 installed...
It works fine when I'm calling it from my PC, but when I call it from the
SQL test server which is a Windows 2003 server with SQL Server 2000
installed.
The message we get when run the Extended Stored Procedure is.
ODBC: Msg 0, Level 16, State 1
Cannot load the DLL XpMQSQL.DLL, or one of the DLLs it references. Reason:
126(The specified module could not be found.).
Does anyone know what I'm missing? We tried to install WSE 2.0 runtime
version but that didn't help...
Regards,
Tommy Selggren
Mandator Sverige AB
http://www.mandator.comUse DEPENDS.EXE to figure out which file(s) is/are missing. Download
DEPENS.EXE from http://www.dependencywalker.com/ and point it at you DLL
which hosts the XP
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Tommy Selggren" <tommy.selggren@.telia.com> wrote in message
news:e$pRK3kGFHA.2136@.TK2MSFTNGP14.phx.gbl...
> Hi all!
> I have a problem with a Extended Stored Procedure that calls a
> WebService...I'm using SOAP to call the WebService and I'm using an
> TokenManager that I have written myself...I have WSE 2.0 installed...
> It works fine when I'm calling it from my PC, but when I call it from the
> SQL test server which is a Windows 2003 server with SQL Server 2000
> installed.
> The message we get when run the Extended Stored Procedure is.
> ODBC: Msg 0, Level 16, State 1
> Cannot load the DLL XpMQSQL.DLL, or one of the DLLs it references. Reason:
> 126(The specified module could not be found.).
> Does anyone know what I'm missing? We tried to install WSE 2.0 runtime
> version but that didn't help...
> Regards,
> Tommy Selggren
> Mandator Sverige AB
> http://www.mandator.com
>
>|||Many thanks!
"Gert E.R. Drapers" <GertD@.SQLDevNet> wrote in message
news:eT2QgflGFHA.3092@.tk2msftngp13.phx.gbl...
> Use DEPENDS.EXE to figure out which file(s) is/are missing. Download
> DEPENS.EXE from http://www.dependencywalker.com/ and point it at you DLL
> which hosts the XP
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Tommy Selggren" <tommy.selggren@.telia.com> wrote in message
> news:e$pRK3kGFHA.2136@.TK2MSFTNGP14.phx.gbl...
the
Reason:
>

Problem with Execute SQL Task

I am having problems creating an "Execute SQL Task" which calls a stored procedure.

I have tested the procedure successfully using parameters that I have hardcoded on the command line (i.e., EXEC procedure_name 1, 2). This works fine, but I'm having problems using variables(i.e., EXEC procedure_name @.VAR1, @.VAR2). I'm using a ConnectionType of OLE DB.

When I parse the Query I get an error message that says "The query failed to parse. Must declare the variable '@.VAR'".

However, I have this variable declared and assigned a value. I have played around the Parameter Mapping pane but I'm not getting anywhere.

Can anyone shed some light on this particular problem and how I go about fixing this?

thanks

John

John,

Place 'EXEC procedure_name ?, ?' as the SQL Statement. Then, under Parameter Mapping, add a variable select User::Var1 (or whatever the name of Var1 is) as the variable name and enter '0' (zero) as the Parameter Name. Add another variable, select User::Var2 as the variable name and enter '1' as the Parameter Name. That should do it.

Also, I've never been able to parse a query with parameters in it.

Eric

|||I could not reproduce your problem. However, I could use "Execute SQL task" to execute a stored procedure by passing a variable as the input parameter.

I had sp_GetDetails in my db that took one input (varchar type). I created a variable called "inputVal" and assigned a value to it. In my "Execute SQL task", I had "Execute sp_GetDetails @.inputValParam" as my SQLStatement. I also created a mapping between inputVal and inputValParam using "Parameter Mapping" in my task. This task works if the connection type is ADO.Net. If I change that to OLE DB, it does not work.|||Thank you so much. This has been very frustrating at best. I don't think some of these transform tasks are that intuitive at all...|||How would you pass a mix of variables and hardcoded values (i.e., @.var1, @.var2, null, null, 2, "test") without using the parameter mapping?|||

Simply use "EXEC procedure_name ?, ?, 1, NULL, 'Yes', ?, ?". The question marks serve as placeholders for the parameters which you are going to map on the 'Parameter Mapping' page. The parameter list is a 0-based array. So your parameter names will be 0, 1, 2, 3, ..., n respectively, and they're placed into the SQL in the order they're named, so the first '?' corresponds to parameter 0, the second '?' corresponds to parameter 1, etc.

So, if I have User::var1 with a value of 'A' mapped to 0, User::var2 = 'B' -> 1, User::var3 = 'Jim' -> 2, User::var4 = 'Bob' -> 3 the SQL sent via the SQL task would be "EXEC procedure_name 'A', 'B', 1, NULL, 'Yes', 'Jim', 'Bob'".

Hope that doesn't confuse things more.

Eric

|||

Kaarthik,

Kirk has a useful post which may help you here: http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx

-Jamie

|||Thanks for the great info. I'll see if I can get this to work for me. Great help again ...

problem with DTS package

have a DTS package which utilizes an "Execute Process
Task". This task calls a batch file on my server. I keep getting this error trying to run the DTS from sqlserver:

"CreateProcessTask 'DTSTask_DTSCreateProcessTask_1': Process returned code -1, which does not match the specified SuccessReturnCode of 1:

Does anyone know what this error mean? TIAWhat is the level of service pack on SQL & OS?|||Possibly the batch job you are calling is producing an error. Try executing your batch job from a command window and inspect its result.

kbk|||Also enable DTS package logging for more information on this behaviour.