Problem with INSERT INTO Temp Table on SQL 2000 using Linked Server to SQL 2005

I have a SQL Server 2000 linked to a SQL Server 2005 and I am attempting populate a temporary table on the 2000 server using INSERT INTO by executing a stored procedure on the 2005 server. If I just execute the stored procedure, the data is returened with no issues. If I try to Insert the data into the temporary table the process hangs and times out.

This returns the data with no issues:

EXEC @.RetVal = MyLink.MyTable.dbo.spGetValue @.string1

This hangs and has to be killed or times out:

CREATE TABLE #TempTable (Value DECIMAL(19, 10) NULL)
INSERT INTO #TempTable

EXEC @.RetVal = MyLink.MyTable.dbo.spGetValue @.string1
SELECT * FROM #TempTable

I tried adding SET REMOTE_PROC_TRANSACTIONS OFF as suggested in an earlier post, but this had no effect.

You need to turn the MSDTC service on. You can this by clicking START > SETTINGS > CONTROL PANEL > ADMINISTRATIVE TOOLS > SERVICES. Find the service called 'Distributed Transaction Coordinator' and RIGHT CLICK (on it and select) > Start.

|||

Thanks for your reply.

MSDTC service is already running on both servers.

Problem with EXEC command

I am try to populate a table based on a variable (it holds the table name).
I have managed to create the table using the EXEC command but am having
problems populating it with the following command. I receive "Error 156:
Incorrect syntax near the keyword 'CONVERT'". Could anyone offer a solution
.
Thanks.
BEGIN
EXEC ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' +
CONVERT(DATETIME, @.StartDate, 102) + ') AND (DateEntered <= ' +
CONVERT(DATETIME, @.EndDate, 102) + ')')
END
However the following works fine into a regular table.
BEGIN
INSERT INTO SubsList (SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = @.PubCode) AND (DateEntered >= CONVERT(DATETIME,
@.StartDate, 102)) AND (DateEntered <= CONVERT(DATETIME, @.EndDate, 102))
ENDHi pete
try this:
BEGIN
EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' +
CONVERT(DATETIME, @.StartDate, 102) + ') AND (DateEntered <= ' +
CONVERT(DATETIME, @.EndDate, 102) + ')')
END
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Pete" wrote:

> I am try to populate a table based on a variable (it holds the table name)
.
> I have managed to create the table using the EXEC command but am having
> problems populating it with the following command. I receive "Error 156:
> Incorrect syntax near the keyword 'CONVERT'". Could anyone offer a soluti
on.
> Thanks.
> BEGIN
> EXEC ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' +
> CONVERT(DATETIME, @.StartDate, 102) + ') AND (DateEntered <= ' +
> CONVERT(DATETIME, @.EndDate, 102) + ')')
> END
>
> However the following works fine into a regular table.
> BEGIN
> INSERT INTO SubsList (SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = @.PubCode) AND (DateEntered >= CONVERT(DATETIME,
> @.StartDate, 102)) AND (DateEntered <= CONVERT(DATETIME, @.EndDate, 102))
> END|||Thanks Chandra but still no joy.
Now getting "Error 170: Line 125: Incorrect syntax near 'INSERT INTO'"
"Chandra" wrote:
> Hi pete
> try this:
> BEGIN
> EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' +
> CONVERT(DATETIME, @.StartDate, 102) + ') AND (DateEntered <= ' +
> CONVERT(DATETIME, @.EndDate, 102) + ')')
> END
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Pete" wrote:
>|||Hi pete
try now:
EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >=
CONVERT(DATETIME, ' + @.StartDate + ', 102) AND DateEntered <=
CONVERT(DATETIME,'+ @.EndDate +', 102)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Pete" wrote:
> Thanks Chandra but still no joy.
> Now getting "Error 170: Line 125: Incorrect syntax near 'INSERT INTO'"
>
> "Chandra" wrote:
>|||Hi pete
just observed and found that, "(" was missing before "DateEntered"
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Pete" wrote:
> Thanks Chandra but still no joy.
> Now getting "Error 170: Line 125: Incorrect syntax near 'INSERT INTO'"
>
> "Chandra" wrote:
>|||'Fraid not. Same error.
"Chandra" wrote:
> Hi pete
> try now:
> EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >=
> CONVERT(DATETIME, ' + @.StartDate + ', 102) AND DateEntered <=
> CONVERT(DATETIME,'+ @.EndDate +', 102)
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Pete" wrote:
>|||my mistake:
EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >=
CONVERT(DATETIME, ' + @.StartDate + ', 102) AND DateEntered <=
CONVERT(DATETIME,'+ @.EndDate +', 102)'
i missed a ' at the end.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Pete" wrote:
> 'Fraid not. Same error.
> "Chandra" wrote:
>|||Nope :-(
"Chandra" wrote:
> Noe then,
> this should work
> EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >=
> CONVERT(DATETIME, ' + @.StartDate + ', 102) AND DateEntered <=
> CONVERT(DATETIME,'+ @.EndDate +', 102)')
> too many quotes and brackets, these are confusing..
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Pete" wrote:
>|||Why don't you try printing your query instead of executing it?
Then you'll see the error yourself. With what you're doing
now, you make a change, ask the parse "is this right yet?", and
listen to the parser's particular complaint, over and over.
If you've never looked at the query yourself, are you going to
assume it's correct once the parser stops complaining about it?
Once you get it right, then you can execute it instead of print
it.
Steve Kass
Drew University
Pete wrote:
>Getting closer..
>The following gives "Syntax error converting datetime from character string
"
>SET @.SqlString = N'INSERT INTO ' + @.NewSubsList + ' (SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND (DateEntered >= ' +
>CONVERT(DATETIME, @.StartDate, 102) + ') AND (DateEntered <= ' +
>CONVERT(DATETIME, @.EndDate, 102) + ')'
>EXECUTE sp_executesql @.SqlString
>
>"Pete" wrote:
>
>|||hi
or do one thing,
first create the string that you want to execute.
just see if it clear in the syntax. and then pass it to sp_executesql
does this make sence.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Pete" wrote:
> Nope :-(
> "Chandra" wrote:
>