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 datepart ....

hello
i have a table with date entries of type string ...tries of type varchar li
ke
01.01.2006
.....
01.02.2006
.....
28.02.2006
print DATEPART(dw,'01.02.2006') ->2
print DATEPART(dw,'14.02.2006') ->
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
i think the problems are because on server are default german and the othe
are english.
I did not want to change all kind of scrips - Is the any possibility to add
a command in the first line to change this ...
best reagrds
what can i do?
print DATEPART(dw,'14.02.2006')Try:
print DATEPART(dw,convert (datetime, '14.02.2006', 104))
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:624253D1-F280-433B-86DB-EAF822D6E6C3@.microsoft.com...
hello
i have a table with date entries of type string ...tries of type varchar
like
01.01.2006
.....
01.02.2006
.....
28.02.2006
print DATEPART(dw,'01.02.2006') ->2
print DATEPART(dw,'14.02.2006') ->
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
i think the problems are because on server are default german and the othe
are english.
I did not want to change all kind of scrips - Is the any possibility to add
a command in the first line to change this ...
best reagrds
what can i do?
print DATEPART(dw,'14.02.2006')|||try this
SET DATEFORMAT DMY
SELECT DATEPART(dw,'14.02.2006')
http://sqlservercode.blogspot.com/|||Xavier,
I suggest you look up (& make use of) the
SET DATEFORMAT
statement in Books Online.
Robert
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:624253D1-F280-433B-86DB-EAF822D6E6C3@.microsoft.com...
> hello
> i have a table with date entries of type string ...tries of type varchar
> like
> 01.01.2006
> .....
> 01.02.2006
> .....
> 28.02.2006
>
> print DATEPART(dw,'01.02.2006') ->2
> print DATEPART(dw,'14.02.2006') ->
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> i think the problems are because on server are default german and the othe
> are english.
> I did not want to change all kind of scrips - Is the any possibility to
> add
> a command in the first line to change this ...
> best reagrds
> what can i do?
> print DATEPART(dw,'14.02.2006')
>|||Try using CONVERT with the proper format.
print DATEPART(dw,convert(datetime, '14.02.2006', 104))|||thanks
"Tom Moreau" wrote:

> Try:
> print DATEPART(dw,convert (datetime, '14.02.2006', 104))
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:624253D1-F280-433B-86DB-EAF822D6E6C3@.microsoft.com...
> hello
> i have a table with date entries of type string ...tries of type varchar
> like
> 01.01.2006
> ......
> 01.02.2006
> ......
> 28.02.2006
>
> print DATEPART(dw,'01.02.2006') ->2
> print DATEPART(dw,'14.02.2006') ->
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> i think the problems are because on server are default german and the othe
> are english.
> I did not want to change all kind of scrips - Is the any possibility to ad
d
> a command in the first line to change this ...
> best reagrds
> what can i do?
> print DATEPART(dw,'14.02.2006')
>
>|||thanks