Error when using more then one comment

Helo,
I=B4ve two comments and between them i put some code, but=20
when i execute Query Analyser show the following error
"Server: Msg 113, Level 15, State 1, Line 40
Missing end comment mark '*/'.
Server: Msg 170, Level 15, State 1, Line 34
Line 34: Incorrect syntax near '/'."
If i execute the code with the first comment it executes=20
without any error but if i try to execute evrything it=20
shows me the error
When was created the second comment i've done it like in=20
the first part of code, this is not a problem but i dont=20
know why SQL shows this message to me!!!
Best regards
Sounds like you have a 'go' in the middle of a multi line comment... Query
Analyzer behaves poorly when this occurs... Use a single line comment -- to
comment out the go statement.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1c03b01c42143$7e978080$a101280a@.phx.gbl...
Helo,
Ive two comments and between them i put some code, but
when i execute Query Analyser show the following error
"Server: Msg 113, Level 15, State 1, Line 40
Missing end comment mark '*/'.
Server: Msg 170, Level 15, State 1, Line 34
Line 34: Incorrect syntax near '/'."
If i execute the code with the first comment it executes
without any error but if i try to execute evrything it
shows me the error
When was created the second comment i've done it like in
the first part of code, this is not a problem but i dont
know why SQL shows this message to me!!!
Best regards
|||Thanks Wayne, you're right
Best regards

>--Original Message--
>Sounds like you have a 'go' in the middle of a multi line=20
comment... Query
>Analyzer behaves poorly when this occurs... Use a single=20
line comment -- to
>comment out the go statement.
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in=20
message
>news:1c03b01c42143$7e978080$a101280a@.phx.gbl...
>Helo,
>I=B4ve two comments and between them i put some code, but
>when i execute Query Analyser show the following error
>"Server: Msg 113, Level 15, State 1, Line 40
>Missing end comment mark '*/'.
>Server: Msg 170, Level 15, State 1, Line 34
>Line 34: Incorrect syntax near '/'."
>If i execute the code with the first comment it executes
>without any error but if i try to execute evrything it
>shows me the error
>When was created the second comment i've done it like in
>the first part of code, this is not a problem but i dont
>know why SQL shows this message to me!!!
>Best regards
>
>.
>
sql

Error when using more then one comment

Helo,
I=B4ve two comments and between them i put some code, but=20
when i execute Query Analyser show the following error
"Server: Msg 113, Level 15, State 1, Line 40
Missing end comment mark '*/'.
Server: Msg 170, Level 15, State 1, Line 34
Line 34: Incorrect syntax near '/'."
If i execute the code with the first comment it executes=20
without any error but if i try to execute evrything it=20
shows me the error
When was created the second comment i've done it like in=20
the first part of code, this is not a problem but i dont=20
know why SQL shows this message to me!!!
Best regardsSounds like you have a 'go' in the middle of a multi line comment... Query
Analyzer behaves poorly when this occurs... Use a single line comment -- to
comment out the go statement.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1c03b01c42143$7e978080$a101280a@.phx
.gbl...
Helo,
Ive two comments and between them i put some code, but
when i execute Query Analyser show the following error
"Server: Msg 113, Level 15, State 1, Line 40
Missing end comment mark '*/'.
Server: Msg 170, Level 15, State 1, Line 34
Line 34: Incorrect syntax near '/'."
If i execute the code with the first comment it executes
without any error but if i try to execute evrything it
shows me the error
When was created the second comment i've done it like in
the first part of code, this is not a problem but i dont
know why SQL shows this message to me!!!
Best regards|||Thanks Wayne, you're right
Best regards

>--Original Message--
>Sounds like you have a 'go' in the middle of a multi line=20
comment... Query
>Analyzer behaves poorly when this occurs... Use a single=20
line comment -- to
>comment out the go statement.
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in=20
message
> news:1c03b01c42143$7e978080$a101280a@.phx
.gbl...
>Helo,
>I=B4ve two comments and between them i put some code, but
>when i execute Query Analyser show the following error
>"Server: Msg 113, Level 15, State 1, Line 40
>Missing end comment mark '*/'.
>Server: Msg 170, Level 15, State 1, Line 34
>Line 34: Incorrect syntax near '/'."
>If i execute the code with the first comment it executes
>without any error but if i try to execute evrything it
>shows me the error
>When was created the second comment i've done it like in
>the first part of code, this is not a problem but i dont
>know why SQL shows this message to me!!!
>Best regards
>
>.
>

Error when using more then one comment

Helo,
I=B4ve two comments and between them i put some code, but when i execute Query Analyser show the following error
"Server: Msg 113, Level 15, State 1, Line 40
Missing end comment mark '*/'.
Server: Msg 170, Level 15, State 1, Line 34
Line 34: Incorrect syntax near '/'."
If i execute the code with the first comment it executes without any error but if i try to execute evrything it shows me the error
When was created the second comment i've done it like in the first part of code, this is not a problem but i dont know why SQL shows this message to me!!!
Best regardsSounds like you have a 'go' in the middle of a multi line comment... Query
Analyzer behaves poorly when this occurs... Use a single line comment -- to
comment out the go statement.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1c03b01c42143$7e978080$a101280a@.phx.gbl...
Helo,
I´ve two comments and between them i put some code, but
when i execute Query Analyser show the following error
"Server: Msg 113, Level 15, State 1, Line 40
Missing end comment mark '*/'.
Server: Msg 170, Level 15, State 1, Line 34
Line 34: Incorrect syntax near '/'."
If i execute the code with the first comment it executes
without any error but if i try to execute evrything it
shows me the error
When was created the second comment i've done it like in
the first part of code, this is not a problem but i dont
know why SQL shows this message to me!!!
Best regards|||Thanks Wayne, you're right
Best regards
>--Original Message--
>Sounds like you have a 'go' in the middle of a multi line comment... Query
>Analyzer behaves poorly when this occurs... Use a single line comment -- to
>comment out the go statement.
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
>news:1c03b01c42143$7e978080$a101280a@.phx.gbl...
>Helo,
>I=B4ve two comments and between them i put some code, but
>when i execute Query Analyser show the following error
>"Server: Msg 113, Level 15, State 1, Line 40
>Missing end comment mark '*/'.
>Server: Msg 170, Level 15, State 1, Line 34
>Line 34: Incorrect syntax near '/'."
>If i execute the code with the first comment it executes
>without any error but if i try to execute evrything it
>shows me the error
>When was created the second comment i've done it like in
>the first part of code, this is not a problem but i dont
>know why SQL shows this message to me!!!
>Best regards
>
>.
>

Error when using 'LIKE'

I am running the following query using Query Analyzer against an MSDE version. The query seems like a textbook use of 'LIKE', so the error makes to sense to me. Thanks for any help.

SELECT _FAX

FROM _IRISH_BNB_DETAILS

WHERE _FAX LIKE "[A-Z]%"

Error:

Server: Msg 207, Level 16, State 3, Line 1

Invalid column name '[A-Z]%'.

Try executing:

SET QUOTED_IDENTIFIER OFF

Dave

|||

In SQL, use the single quote character to delimit strings, so:

LIKE '[A-Z]%'

instead of

LIKE "[A-Z]%"

Double quotes are used to allow for quoted identifiers, so it is thinking that "[A-Z]%" is a column name

|||

Thank you!

I was following an example in "SQL Server 2000 Administrator Companion" on page 309 that uses double quotes.

|||

Sorry, my answer is too short. Try this:

SET QUOTED_IDENTIFIER OFF

SELECT _FAX
FROM _IRISH_BNB_DETAILS
WHERE _FAX LIKE "[A-Z]%"

SET QUOTED_IDENTIFIER ON

and then try this:

SELECT _FAX
FROM _IRISH_BNB_DETAILS
WHERE _FAX LIKE '[A-Z]%'

You are really better changing your literal, that is "[A-Z]%" into a single quote delimited literal for the sake of ANSI compliance; however, an alternative is to set QUOTED_IDENTIFIER on so that the original code can work as is.

Dave

|||Yeah, Louis answered before I could correct myself. Sorry about that.|||Yeah, and you answered while I was answering :)|||

Hey, it was a typing war! You won!

:-)

Error when running sp_helpdb query

I get the following error when running command "sp_helpdb" in Query Analyzer.
Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53 Cannot
insert the value NULL into column 'owner', table
'tempdb.dbo.#spdbdesc___________________________________________________________________________________________________________000100371F02';
column does not allow nulls. INSERT fails.
The statement has been terminated.Kush
use master
go
sp_helptext 'sp_helpdb'
I think it fails to find suser_sname of the current session
"Kush" <Kush@.discussions.microsoft.com> wrote in message
news:44DAF4E9-9DCA-47FC-85A3-79234F68EFF3@.microsoft.com...
>I get the following error when running command "sp_helpdb" in Query
>Analyzer.
> Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53 Cannot
> insert the value NULL into column 'owner', table
> 'tempdb.dbo.#spdbdesc___________________________________________________________________________________________________________000100371F02';
> column does not allow nulls. INSERT fails.
> The statement has been terminated.|||Here's an URL that might explain it. Google is your friend :-)
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/bc6f4659bb8dd60c/50f0170456dd13b5?q=sp_helpdb+insert+NULL&rnum=3#50f0170456dd13b5
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kush" <Kush@.discussions.microsoft.com> wrote in message
news:44DAF4E9-9DCA-47FC-85A3-79234F68EFF3@.microsoft.com...
>I get the following error when running command "sp_helpdb" in Query Analyzer.
> Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53 Cannot
> insert the value NULL into column 'owner', table
> 'tempdb.dbo.#spdbdesc___________________________________________________________________________________________________________000100371F02';
> column does not allow nulls. INSERT fails.
> The statement has been terminated.|||I will try that and let you know what happens!
"Tibor Karaszi" wrote:
> Here's an URL that might explain it. Google is your friend :-)
> http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/bc6f4659bb8dd60c/50f0170456dd13b5?q=sp_helpdb+insert+NULL&rnum=3#50f0170456dd13b5
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kush" <Kush@.discussions.microsoft.com> wrote in message
> news:44DAF4E9-9DCA-47FC-85A3-79234F68EFF3@.microsoft.com...
> >I get the following error when running command "sp_helpdb" in Query Analyzer.
> >
> > Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53 Cannot
> > insert the value NULL into column 'owner', table
> > 'tempdb.dbo.#spdbdesc___________________________________________________________________________________________________________000100371F02';
> > column does not allow nulls. INSERT fails.
> > The statement has been terminated.
>

Error when running sp_helpdb query

Kush
use master
go
sp_helptext 'sp_helpdb'
I think it fails to find suser_sname of the current session
"Kush" <Kush@.discussions.microsoft.com> wrote in message
news:44DAF4E9-9DCA-47FC-85A3-79234F68EFF3@.microsoft.com...
>I get the following error when running command "sp_helpdb" in Query
>Analyzer.
> Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53 Cannot
> insert the value NULL into column 'owner', table
> 'tempdb.dbo. #spdbdesc_______________________________
______________________
________________________________________
______________000100371F02';
> column does not allow nulls. INSERT fails.
> The statement has been terminated.I get the following error when running command "sp_helpdb" in Query Analyzer
.
Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53 Cannot
insert the value NULL into column 'owner', table
'tempdb.dbo. #spdbdesc_______________________________
________________________
________________________________________
____________000100371F02';
column does not allow nulls. INSERT fails.
The statement has been terminated.|||Kush
use master
go
sp_helptext 'sp_helpdb'
I think it fails to find suser_sname of the current session
"Kush" <Kush@.discussions.microsoft.com> wrote in message
news:44DAF4E9-9DCA-47FC-85A3-79234F68EFF3@.microsoft.com...
>I get the following error when running command "sp_helpdb" in Query
>Analyzer.
> Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53 Cannot
> insert the value NULL into column 'owner', table
> 'tempdb.dbo. #spdbdesc_______________________________
______________________
________________________________________
______________000100371F02';
> column does not allow nulls. INSERT fails.
> The statement has been terminated.|||Here's an URL that might explain it. Google is your friend :-)
3b5" target="_blank">http://groups.google.com/group/micr...6dd1
3b5
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kush" <Kush@.discussions.microsoft.com> wrote in message
news:44DAF4E9-9DCA-47FC-85A3-79234F68EFF3@.microsoft.com...
>I get the following error when running command "sp_helpdb" in Query Analyze
r.
> Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53 Cannot
> insert the value NULL into column 'owner', table
> 'tempdb.dbo. #spdbdesc_______________________________
______________________
________________________________________
______________000100371F02';
> column does not allow nulls. INSERT fails.
> The statement has been terminated.|||I will try that and let you know what happens!
"Tibor Karaszi" wrote:

> Here's an URL that might explain it. Google is your friend :-)
> d13b5" target="_blank">http://groups.google.com/group/micr...6d
d13b5
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kush" <Kush@.discussions.microsoft.com> wrote in message
> news:44DAF4E9-9DCA-47FC-85A3-79234F68EFF3@.microsoft.com...
>|||Here's an URL that might explain it. Google is your friend :-)
3b5" target="_blank">http://groups.google.com/group/micr...6dd1
3b5
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kush" <Kush@.discussions.microsoft.com> wrote in message
news:44DAF4E9-9DCA-47FC-85A3-79234F68EFF3@.microsoft.com...
>I get the following error when running command "sp_helpdb" in Query Analyze
r.
> Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53 Cannot
> insert the value NULL into column 'owner', table
> 'tempdb.dbo. #spdbdesc_______________________________
______________________
________________________________________
______________000100371F02';
> column does not allow nulls. INSERT fails.
> The statement has been terminated.|||I will try that and let you know what happens!
"Tibor Karaszi" wrote:

> Here's an URL that might explain it. Google is your friend :-)
> d13b5" target="_blank">http://groups.google.com/group/micr...6d
d13b5
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kush" <Kush@.discussions.microsoft.com> wrote in message
> news:44DAF4E9-9DCA-47FC-85A3-79234F68EFF3@.microsoft.com...
>

Error when running a Query

In query analyzer, when I have a cursor that runs through some code
enclosed in the begin and end statements. But, if the cursor dataset is
larger then 20 records, on the 20th record, it will give me this error.
The first 19 records will process fine.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (WrapperRead()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
What would cause this error?
That's pretty tough to answer without actually seeing the code.
Andrew J. Kelly SQL MVP
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:CFB808F4-DA2A-4C88-97C8-083B2EBCBEA0@.microsoft.com...
> In query analyzer, when I have a cursor that runs through some code
> enclosed in the begin and end statements. But, if the cursor dataset is
> larger then 20 records, on the 20th record, it will give me this error.
> The first 19 records will process fine.
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead
> (WrapperRead()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> What would cause this error?
|||DECLARE Cust_Cursor CURSOR FOR
select distinct tp, tpprimnetwork from #pricesheetNCustWithAddr
OPEN Cust_Cursor
FETCH NEXT FROM Cust_Cursor
INTO @.CustNo,@.TPNetwork
WHILE @.@.FETCH_STATUS = 0
BEGIN
execute sql4.dx_custom.dbo.dx_COM_RetrieveInvoiceToCustome r @.CustNo,
@.TPNetwork, @.InvcToTP = @.InvcToCust output
update #pricesheetNCustWithAddr
set InvcToTP = @.InvcToCust
where tp = @.CustNo
FETCH NEXT FROM Cust_Cursor
INTO @.CustNo,@.TPNetwork
END
CLOSE Cust_Cursor
DEALLOCATE Cust_Cursor
###########################################
CREATE procedure dx_COM_RetrieveInvoiceToCustomer (@.CustNo varchar(8),
@.TPNetwork varchar(100),
@.InvcToTP varchar(8) output)
as
--************************************************** *************
--* DateWhoDescription
--*----
--* 02/17/2005rhsiehNew
--* 02/22/2005rhsiehNetwork Nodes
--************************************************** *************
--attempts to go 9 layers down the tradingpartner network
--retrieve all the child nodes
select cnode.objectid, cnode.parentobjectid, cnode.tp, cnode.tpobjectid,
isnull(cnodeTP.hasinvoicetorole,null) as InvcToRole
into #NetworkNodes
from erp_p..coNetworkNode cnode
join erp_p..fdtradingpartne cnodeTP on cnodeTP.objectid = cnode.tpobjectid
where parentclassid = 10664 and collectionid = 1
--get the tp network and root node of the tradingpartner network
select ctn.tpnetworktpnname as TPNetwork,
rnode.objectid as RootNodeOID,
rnode.tp as RootNodeTP,
rnodeTP.hasinvoicetorole as RootNodeTPInvcToRole
into #Network1
from erp_p..coTPNetwork ctn
join erp_p..coVersionNode cvn on cvn.parentobjectid = ctn.objectid
join erp_p..coNetworkNode rnode on rnode.objectid = cvn.rootnodeobjectid
join erp_p..fdtradingpartne rnodeTP on rnodeTP.objectid = rnode.tpobjectid
where ctn.tpnetworktpnname = @.TPNetwork
and cvn.parentclassid = 11274 and cvn.collectionid = 1
and ctn.tpnetworkinsttype = 0
--get the child node(s) for the root node of the tradingparter network
select n1.*,
cnode.objectid as NodeOID1,
cnode.tp as TP1,
cnode.InvcToRole as TPInvcToRole1
into #network2
from #network1 n1
left join #NetworkNodes cnode on cnode.parentobjectid = n1.RootNodeOID
--get the child node(s) for the first child node of the tradingparter network
select n1.*,
cnode.objectid as NodeOID2,
cnode.tp as TP2,
cnode.InvcToRole as TPInvcToRole2
into #network3
from #network2 n1
left join #NetworkNodes cnode on cnode.parentobjectid = n1.NodeOID1
--get the child node(s) for the previous child node of the tradingparter
network
select n1.*,
cnode.objectid as NodeOID3,
cnode.tp as TP3,
cnode.InvcToRole as TPInvcToRole3
into #network4
from #network3 n1
left join #NetworkNodes cnode on cnode.parentobjectid = n1.NodeOID2
--get the child node(s) for the previous child node of the tradingparter
network
select n1.*,
cnode.objectid as NodeOID4,
cnode.tp as TP4,
cnode.InvcToRole as TPInvcToRole4
into #network5
from #network4 n1
left join #NetworkNodes cnode on cnode.parentobjectid = n1.NodeOID3
--get the child node(s) for the previous child node of the tradingparter
network
select n1.*,
cnode.objectid as NodeOID5,
cnode.tp as TP5,
cnode.InvcToRole as TPInvcToRole5
into #network6
from #network5 n1
left join #NetworkNodes cnode on cnode.parentobjectid = n1.NodeOID4
/* ##COMMENTED OUT FOR NOT CAUSE A NETWORK WOULD NOT GO DOWN THIS FAR, BUT
LEFT IN IF IT DOES IN THE FUTURE##
--get the child node(s) for the previous child node of the tradingparter
network
select n1.*,
cnode.objectid as NodeOID6,
cnode.tp as TP6,
cnode.InvcToRole as TPInvcToRole6
into #network7
from #network6 n1
left join #NetworkNodes cnode on cnode.parentobjectid = n1.NodeOID5
--get the child node(s) for the previous child node of the tradingparter
network
select n1.*,
cnode.objectid as NodeOID7,
cnode.tp as TP7,
cnode.InvcToRole as TPInvcToRole7
into #network8
from #network7 n1
left join #NetworkNodes cnode on cnode.parentobjectid = n1.NodeOID6
--get the child node(s) for the previous child node of the tradingparter
network
select n1.*,
cnode.objectid as NodeOID8,
cnode.tp as TP8,
cnode.InvcToRole as TPInvcToRole8
into #network9
from #network8 n1
left join #NetworkNodes cnode on cnode.parentobjectid = n1.NodeOID7
*/
declare @.RootTP varchar(8), @.RootInvcToRole int,
@.TP1 varchar(8), @.TPInvcToRole1 int,
@.TP2 varchar(8), @.TPInvcToRole2 int,
@.TP3 varchar(8), @.TPInvcToRole3 int,
@.TP4 varchar(8), @.TPInvcToRole4 int,
@.TP5 varchar(8), @.TPInvcToRole5 int
DECLARE Customer_Cursor CURSOR FOR
select RootNodeTP, RootNodeTPInvcToRole,
TP1, TPInvcToRole1,
TP2, TPInvcToRole2,
TP3, TPInvcToRole3,
TP4, TPInvcToRole4,
TP5, TPInvcToRole5
from #network6
where tp5 = @.CustNo
OPEN Customer_Cursor
FETCH NEXT FROM Customer_Cursor
INTO @.RootTP, @.RootInvcToRole,
@.TP1, @.TPInvcToRole1,
@.TP2, @.TPInvcToRole2,
@.TP3, @.TPInvcToRole3,
@.TP4, @.TPInvcToRole4,
@.TP5, @.TPInvcToRole5
WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.TPInvcToRole5 = 1
begin
set @.InvcToTP = @.TP5
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
if @.TPInvcToRole4 = 1
begin
set @.InvcToTP = @.TP4
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
if @.TPInvcToRole3 = 1
begin
set @.InvcToTP = @.TP3
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
if @.TPInvcToRole2 = 1
begin
set @.InvcToTP = @.TP2
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
if @.TPInvcToRole1 = 1
begin
set @.InvcToTP = @.TP1
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
if @.RootInvcToRole = 1
begin
set @.InvcToTP = @.RootTP
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
FETCH NEXT FROM Employee_Cursor
INTO @.RootTP, @.RootInvcToRole,
@.TP1, @.TPInvcToRole1,
@.TP2, @.TPInvcToRole2,
@.TP3, @.TPInvcToRole3,
@.TP4, @.TPInvcToRole4,
@.TP5, @.TPInvcToRole5
END
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
DECLARE Customer_Cursor CURSOR FOR
select RootNodeTP, RootNodeTPInvcToRole,
TP1, TPInvcToRole1,
TP2, TPInvcToRole2,
TP3, TPInvcToRole3,
TP4, TPInvcToRole4
from #network6
where tp4 = @.CustNo
OPEN Customer_Cursor
FETCH NEXT FROM Customer_Cursor
INTO @.RootTP, @.RootInvcToRole,
@.TP1, @.TPInvcToRole1,
@.TP2, @.TPInvcToRole2,
@.TP3, @.TPInvcToRole3,
@.TP4, @.TPInvcToRole4
WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.TPInvcToRole4 = 1
begin
set @.InvcToTP = @.TP4
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
if @.TPInvcToRole3 = 1
begin
set @.InvcToTP = @.TP3
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
if @.TPInvcToRole2 = 1
begin
set @.InvcToTP = @.TP2
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
if @.TPInvcToRole1 = 1
begin
set @.InvcToTP = @.TP1
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
if @.RootInvcToRole = 1
begin
set @.InvcToTP = @.RootTP
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
FETCH NEXT FROM Employee_Cursor
INTO @.RootTP, @.RootInvcToRole,
@.TP1, @.TPInvcToRole1,
@.TP2, @.TPInvcToRole2,
@.TP3, @.TPInvcToRole3,
@.TP4, @.TPInvcToRole4
END
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
DECLARE Customer_Cursor CURSOR FOR
select RootNodeTP, RootNodeTPInvcToRole,
TP1, TPInvcToRole1,
TP2, TPInvcToRole2,
TP3, TPInvcToRole3
from #network6
where tp3 = @.CustNo
OPEN Customer_Cursor
FETCH NEXT FROM Customer_Cursor
INTO @.RootTP, @.RootInvcToRole,
@.TP1, @.TPInvcToRole1,
@.TP2, @.TPInvcToRole2,
@.TP3, @.TPInvcToRole3
WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.TPInvcToRole3 = 1
begin
set @.InvcToTP = @.TP3
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
if @.TPInvcToRole2 = 1
begin
set @.InvcToTP = @.TP2
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
if @.TPInvcToRole1 = 1
begin
set @.InvcToTP = @.TP1
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
if @.RootInvcToRole = 1
begin
set @.InvcToTP = @.RootTP
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
FETCH NEXT FROM Employee_Cursor
INTO @.RootTP, @.RootInvcToRole,
@.TP1, @.TPInvcToRole1,
@.TP2, @.TPInvcToRole2,
@.TP3, @.TPInvcToRole3
END
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
DECLARE Customer_Cursor CURSOR FOR
select RootNodeTP, RootNodeTPInvcToRole,
TP1, TPInvcToRole1,
TP2, TPInvcToRole2
from #network6
where tp2 = @.CustNo
OPEN Customer_Cursor
FETCH NEXT FROM Customer_Cursor
INTO @.RootTP, @.RootInvcToRole,
@.TP1, @.TPInvcToRole1,
@.TP2, @.TPInvcToRole2
WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.TPInvcToRole2 = 1
begin
set @.InvcToTP = @.TP2
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
if @.TPInvcToRole1 = 1
begin
set @.InvcToTP = @.TP1
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
if @.RootInvcToRole = 1
begin
set @.InvcToTP = @.RootTP
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
FETCH NEXT FROM Employee_Cursor
INTO @.RootTP, @.RootInvcToRole,
@.TP1, @.TPInvcToRole1,
@.TP2, @.TPInvcToRole2
END
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
DECLARE Customer_Cursor CURSOR FOR
select RootNodeTP, RootNodeTPInvcToRole,
TP1, TPInvcToRole1
from #network6
where tp1 = @.CustNo
OPEN Customer_Cursor
FETCH NEXT FROM Customer_Cursor
INTO @.RootTP, @.RootInvcToRole,
@.TP1, @.TPInvcToRole1
WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.TPInvcToRole1 = 1
begin
set @.InvcToTP = @.TP1
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
if @.RootInvcToRole = 1
begin
set @.InvcToTP = @.RootTP
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
FETCH NEXT FROM Employee_Cursor
INTO @.RootTP, @.RootInvcToRole,
@.TP1, @.TPInvcToRole1
END
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
DECLARE Customer_Cursor CURSOR FOR
select RootNodeTP, RootNodeTPInvcToRole
from #network6
where RootNodeTP = @.CustNo
OPEN Customer_Cursor
FETCH NEXT FROM Customer_Cursor
INTO @.RootTP, @.RootInvcToRole
WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.RootInvcToRole = 1
begin
set @.InvcToTP = @.RootTP
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
FETCH NEXT FROM Employee_Cursor
INTO @.RootTP, @.RootInvcToRole
END
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
GO
###########################################
"Andrew J. Kelly" wrote:

> That's pretty tough to answer without actually seeing the code.
> --
> Andrew J. Kelly SQL MVP
>
> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> news:CFB808F4-DA2A-4C88-97C8-083B2EBCBEA0@.microsoft.com...
>
>
|||You have a &*^% load of cursors in the sp that you call fromt he first
cursor. It can take hours to go through that mess<g> of which unfortunately
I don't have the time at the moment. But I can say I can see that much of
it can probably be removed and a SET based method put in place. One thing
to try is change the cursors to STATIC and see if that helps some. I would
seriously think about cleaning up and optimizing that code though.
Andrew J. Kelly SQL MVP
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:97AE40F4-4C18-446D-8CAE-FB421432125A@.microsoft.com...[vbcol=seagreen]
> DECLARE Cust_Cursor CURSOR FOR
> select distinct tp, tpprimnetwork from #pricesheetNCustWithAddr
> OPEN Cust_Cursor
> FETCH NEXT FROM Cust_Cursor
> INTO @.CustNo,@.TPNetwork
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> execute sql4.dx_custom.dbo.dx_COM_RetrieveInvoiceToCustome r @.CustNo,
> @.TPNetwork, @.InvcToTP = @.InvcToCust output
> update #pricesheetNCustWithAddr
> set InvcToTP = @.InvcToCust
> where tp = @.CustNo
> FETCH NEXT FROM Cust_Cursor
> INTO @.CustNo,@.TPNetwork
> END
> CLOSE Cust_Cursor
> DEALLOCATE Cust_Cursor
>
> ###########################################
> CREATE procedure dx_COM_RetrieveInvoiceToCustomer (@.CustNo varchar(8),
> @.TPNetwork varchar(100),
> @.InvcToTP varchar(8) output)
> as
> --************************************************** *************
> --* Date Who Description
> --*----
> --* 02/17/2005 rhsieh New
> --* 02/22/2005 rhsieh Network Nodes
> --************************************************** *************
> --attempts to go 9 layers down the tradingpartner network
> --retrieve all the child nodes
> select cnode.objectid, cnode.parentobjectid, cnode.tp, cnode.tpobjectid,
> isnull(cnodeTP.hasinvoicetorole,null) as InvcToRole
> into #NetworkNodes
> from erp_p..coNetworkNode cnode
> join erp_p..fdtradingpartne cnodeTP on cnodeTP.objectid = cnode.tpobjectid
> where parentclassid = 10664 and collectionid = 1
> --get the tp network and root node of the tradingpartner network
> select ctn.tpnetworktpnname as TPNetwork,
> rnode.objectid as RootNodeOID,
> rnode.tp as RootNodeTP,
> rnodeTP.hasinvoicetorole as RootNodeTPInvcToRole
> into #Network1
> from erp_p..coTPNetwork ctn
> join erp_p..coVersionNode cvn on cvn.parentobjectid = ctn.objectid
> join erp_p..coNetworkNode rnode on rnode.objectid = cvn.rootnodeobjectid
> join erp_p..fdtradingpartne rnodeTP on rnodeTP.objectid = rnode.tpobjectid
> where ctn.tpnetworktpnname = @.TPNetwork
> and cvn.parentclassid = 11274 and cvn.collectionid = 1
> and ctn.tpnetworkinsttype = 0
> --get the child node(s) for the root node of the tradingparter network
> select n1.*,
> cnode.objectid as NodeOID1,
> cnode.tp as TP1,
> cnode.InvcToRole as TPInvcToRole1
> into #network2
> from #network1 n1
> left join #NetworkNodes cnode on cnode.parentobjectid = n1.RootNodeOID
> --get the child node(s) for the first child node of the tradingparter
> network
> select n1.*,
> cnode.objectid as NodeOID2,
> cnode.tp as TP2,
> cnode.InvcToRole as TPInvcToRole2
> into #network3
> from #network2 n1
> left join #NetworkNodes cnode on cnode.parentobjectid = n1.NodeOID1
> --get the child node(s) for the previous child node of the tradingparter
> network
> select n1.*,
> cnode.objectid as NodeOID3,
> cnode.tp as TP3,
> cnode.InvcToRole as TPInvcToRole3
> into #network4
> from #network3 n1
> left join #NetworkNodes cnode on cnode.parentobjectid = n1.NodeOID2
> --get the child node(s) for the previous child node of the tradingparter
> network
> select n1.*,
> cnode.objectid as NodeOID4,
> cnode.tp as TP4,
> cnode.InvcToRole as TPInvcToRole4
> into #network5
> from #network4 n1
> left join #NetworkNodes cnode on cnode.parentobjectid = n1.NodeOID3
> --get the child node(s) for the previous child node of the tradingparter
> network
> select n1.*,
> cnode.objectid as NodeOID5,
> cnode.tp as TP5,
> cnode.InvcToRole as TPInvcToRole5
> into #network6
> from #network5 n1
> left join #NetworkNodes cnode on cnode.parentobjectid = n1.NodeOID4
> /* ##COMMENTED OUT FOR NOT CAUSE A NETWORK WOULD NOT GO DOWN THIS FAR, BUT
> LEFT IN IF IT DOES IN THE FUTURE##
> --get the child node(s) for the previous child node of the tradingparter
> network
> select n1.*,
> cnode.objectid as NodeOID6,
> cnode.tp as TP6,
> cnode.InvcToRole as TPInvcToRole6
> into #network7
> from #network6 n1
> left join #NetworkNodes cnode on cnode.parentobjectid = n1.NodeOID5
> --get the child node(s) for the previous child node of the tradingparter
> network
> select n1.*,
> cnode.objectid as NodeOID7,
> cnode.tp as TP7,
> cnode.InvcToRole as TPInvcToRole7
> into #network8
> from #network7 n1
> left join #NetworkNodes cnode on cnode.parentobjectid = n1.NodeOID6
> --get the child node(s) for the previous child node of the tradingparter
> network
> select n1.*,
> cnode.objectid as NodeOID8,
> cnode.tp as TP8,
> cnode.InvcToRole as TPInvcToRole8
> into #network9
> from #network8 n1
> left join #NetworkNodes cnode on cnode.parentobjectid = n1.NodeOID7
> */
> declare @.RootTP varchar(8), @.RootInvcToRole int,
> @.TP1 varchar(8), @.TPInvcToRole1 int,
> @.TP2 varchar(8), @.TPInvcToRole2 int,
> @.TP3 varchar(8), @.TPInvcToRole3 int,
> @.TP4 varchar(8), @.TPInvcToRole4 int,
> @.TP5 varchar(8), @.TPInvcToRole5 int
> DECLARE Customer_Cursor CURSOR FOR
> select RootNodeTP, RootNodeTPInvcToRole,
> TP1, TPInvcToRole1,
> TP2, TPInvcToRole2,
> TP3, TPInvcToRole3,
> TP4, TPInvcToRole4,
> TP5, TPInvcToRole5
> from #network6
> where tp5 = @.CustNo
> OPEN Customer_Cursor
> FETCH NEXT FROM Customer_Cursor
> INTO @.RootTP, @.RootInvcToRole,
> @.TP1, @.TPInvcToRole1,
> @.TP2, @.TPInvcToRole2,
> @.TP3, @.TPInvcToRole3,
> @.TP4, @.TPInvcToRole4,
> @.TP5, @.TPInvcToRole5
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> if @.TPInvcToRole5 = 1
> begin
> set @.InvcToTP = @.TP5
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> if @.TPInvcToRole4 = 1
> begin
> set @.InvcToTP = @.TP4
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> if @.TPInvcToRole3 = 1
> begin
> set @.InvcToTP = @.TP3
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> if @.TPInvcToRole2 = 1
> begin
> set @.InvcToTP = @.TP2
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> if @.TPInvcToRole1 = 1
> begin
> set @.InvcToTP = @.TP1
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> if @.RootInvcToRole = 1
> begin
> set @.InvcToTP = @.RootTP
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> FETCH NEXT FROM Employee_Cursor
> INTO @.RootTP, @.RootInvcToRole,
> @.TP1, @.TPInvcToRole1,
> @.TP2, @.TPInvcToRole2,
> @.TP3, @.TPInvcToRole3,
> @.TP4, @.TPInvcToRole4,
> @.TP5, @.TPInvcToRole5
> END
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> DECLARE Customer_Cursor CURSOR FOR
> select RootNodeTP, RootNodeTPInvcToRole,
> TP1, TPInvcToRole1,
> TP2, TPInvcToRole2,
> TP3, TPInvcToRole3,
> TP4, TPInvcToRole4
> from #network6
> where tp4 = @.CustNo
> OPEN Customer_Cursor
> FETCH NEXT FROM Customer_Cursor
> INTO @.RootTP, @.RootInvcToRole,
> @.TP1, @.TPInvcToRole1,
> @.TP2, @.TPInvcToRole2,
> @.TP3, @.TPInvcToRole3,
> @.TP4, @.TPInvcToRole4
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> if @.TPInvcToRole4 = 1
> begin
> set @.InvcToTP = @.TP4
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> if @.TPInvcToRole3 = 1
> begin
> set @.InvcToTP = @.TP3
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> if @.TPInvcToRole2 = 1
> begin
> set @.InvcToTP = @.TP2
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> if @.TPInvcToRole1 = 1
> begin
> set @.InvcToTP = @.TP1
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> if @.RootInvcToRole = 1
> begin
> set @.InvcToTP = @.RootTP
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> FETCH NEXT FROM Employee_Cursor
> INTO @.RootTP, @.RootInvcToRole,
> @.TP1, @.TPInvcToRole1,
> @.TP2, @.TPInvcToRole2,
> @.TP3, @.TPInvcToRole3,
> @.TP4, @.TPInvcToRole4
> END
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> DECLARE Customer_Cursor CURSOR FOR
> select RootNodeTP, RootNodeTPInvcToRole,
> TP1, TPInvcToRole1,
> TP2, TPInvcToRole2,
> TP3, TPInvcToRole3
> from #network6
> where tp3 = @.CustNo
> OPEN Customer_Cursor
> FETCH NEXT FROM Customer_Cursor
> INTO @.RootTP, @.RootInvcToRole,
> @.TP1, @.TPInvcToRole1,
> @.TP2, @.TPInvcToRole2,
> @.TP3, @.TPInvcToRole3
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> if @.TPInvcToRole3 = 1
> begin
> set @.InvcToTP = @.TP3
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> if @.TPInvcToRole2 = 1
> begin
> set @.InvcToTP = @.TP2
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> if @.TPInvcToRole1 = 1
> begin
> set @.InvcToTP = @.TP1
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> if @.RootInvcToRole = 1
> begin
> set @.InvcToTP = @.RootTP
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> FETCH NEXT FROM Employee_Cursor
> INTO @.RootTP, @.RootInvcToRole,
> @.TP1, @.TPInvcToRole1,
> @.TP2, @.TPInvcToRole2,
> @.TP3, @.TPInvcToRole3
> END
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> DECLARE Customer_Cursor CURSOR FOR
> select RootNodeTP, RootNodeTPInvcToRole,
> TP1, TPInvcToRole1,
> TP2, TPInvcToRole2
> from #network6
> where tp2 = @.CustNo
> OPEN Customer_Cursor
> FETCH NEXT FROM Customer_Cursor
> INTO @.RootTP, @.RootInvcToRole,
> @.TP1, @.TPInvcToRole1,
> @.TP2, @.TPInvcToRole2
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> if @.TPInvcToRole2 = 1
> begin
> set @.InvcToTP = @.TP2
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> if @.TPInvcToRole1 = 1
> begin
> set @.InvcToTP = @.TP1
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> if @.RootInvcToRole = 1
> begin
> set @.InvcToTP = @.RootTP
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> FETCH NEXT FROM Employee_Cursor
> INTO @.RootTP, @.RootInvcToRole,
> @.TP1, @.TPInvcToRole1,
> @.TP2, @.TPInvcToRole2
> END
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> DECLARE Customer_Cursor CURSOR FOR
> select RootNodeTP, RootNodeTPInvcToRole,
> TP1, TPInvcToRole1
> from #network6
> where tp1 = @.CustNo
> OPEN Customer_Cursor
> FETCH NEXT FROM Customer_Cursor
> INTO @.RootTP, @.RootInvcToRole,
> @.TP1, @.TPInvcToRole1
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> if @.TPInvcToRole1 = 1
> begin
> set @.InvcToTP = @.TP1
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> if @.RootInvcToRole = 1
> begin
> set @.InvcToTP = @.RootTP
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> FETCH NEXT FROM Employee_Cursor
> INTO @.RootTP, @.RootInvcToRole,
> @.TP1, @.TPInvcToRole1
> END
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> DECLARE Customer_Cursor CURSOR FOR
> select RootNodeTP, RootNodeTPInvcToRole
> from #network6
> where RootNodeTP = @.CustNo
> OPEN Customer_Cursor
> FETCH NEXT FROM Customer_Cursor
> INTO @.RootTP, @.RootInvcToRole
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> if @.RootInvcToRole = 1
> begin
> set @.InvcToTP = @.RootTP
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> FETCH NEXT FROM Employee_Cursor
> INTO @.RootTP, @.RootInvcToRole
> END
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> GO
> ###########################################
> "Andrew J. Kelly" wrote:
|||I did already. I changed it, so I am not using cursors but using temporary
tables to get the info I needed. But, I responded just to figure out why I
was getting the error message so, in the future I would know why.
"Andrew J. Kelly" wrote:

> You have a &*^% load of cursors in the sp that you call fromt he first
> cursor. It can take hours to go through that mess<g> of which unfortunately
> I don't have the time at the moment. But I can say I can see that much of
> it can probably be removed and a SET based method put in place. One thing
> to try is change the cursors to STATIC and see if that helps some. I would
> seriously think about cleaning up and optimizing that code though.
> --
> Andrew J. Kelly SQL MVP
>
> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> news:97AE40F4-4C18-446D-8CAE-FB421432125A@.microsoft.com...
>
>
sql

error when I run script

Hi All,

I can't not run the query to update data but it raice an error as below:

"Server: Msg 1101, Level 17, State 10, Line 1
Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth."

HDD free space: 5GB
RAM: 1GB
CPU: 2.8MHz

What should I do?
Please advice me on this.

Thanks in advance,
TNLooks like your TempDb is running out of space.

Are you the database administrator?

The Fonzi approach (not reccomended) would be to restart the SQL Server Service on the server because the tempdb is recreated each time SQL Server is restarted.

In the Enterprise Manager you can right click on the tempdb and check the file growth propeties and adjust them as necessary.

Finally, I wonder what your query might be doing to fill up the tempdb. Do you want to post your script or tell us what you are trying to do?

Error when executing a distributed query on a linked server

Hi,
It's a repost of an old thread with more information included
I'm testing a Sql Server 2005 Enterprise Evaluation Edition 64 bits. I've
been trying to link a sql server 2000 SP4 to this but I'm having some
troubles. When creating the linked server from the Sql Server Management
Studio/New Linked Server I choosed Server type to be "SQL Server" for the 2K
linked server. The linked server was created. Then I go to
Properties/Security and configure the linked server to be accesed as sa. But
when I run a distributed query like
Select * from linkedserver.database.dbo.table I get the error:
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "SQLNCLI" for linked server "sqlservhpold" reported an
error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Line 2
Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked
server "sqlservhpold". A four-part name was supplied, but the provider does
not expose the necessary interfaces to use a catalog or schema.
I tested the link by using sp_testlinkedserver and it returns no failure.
I also created a linked server in the reverse. I mean let server A be the
Sql Server 2005 64 bits Enterprise, and server B be a Sql Server 2000
Standard. I'm in A making B as linked server and trying to make a
distributed query from A to B (Select * from B.database.dbo.table) and this
fails. I tryed in B create A as a linked server. Then I made a distributed
query from B to A (Select * from A.database.dbo.table) and it works as
expected.
The most frustrating is the message that error 7399 returns: "The provider
did not give any information about the error." I cn not find nothing about
such an error message...
Any hint is welcomed
Thanks in advance
SammySammyBar (sammybar@.gmail.com) writes:
> I'm testing a Sql Server 2005 Enterprise Evaluation Edition 64 bits.
> I've been trying to link a sql server 2000 SP4 to this but I'm having
> some troubles. When creating the linked server from the Sql Server
> Management Studio/New Linked Server I choosed Server type to be "SQL
> Server" for the 2K linked server. The linked server was created. Then I
> go to Properties/Security and configure the linked server to be accesed
> as sa. But when I run a distributed query like
> Select * from linkedserver.database.dbo.table I get the error:
> Msg 7399, Level 16, State 1, Line 2
> The OLE DB provider "SQLNCLI" for linked server "sqlservhpold" reported an
> error. The provider did not give any information about the error.
> Msg 7312, Level 16, State 1, Line 2
> Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked
> server "sqlservhpold". A four-part name was supplied, but the provider
> does not expose the necessary interfaces to use a catalog or schema.
Can you double check the SQL 2000 instance, by doing SELECT @.@.version?
I seem to recall that to run linked queries from SQL 2005 to SQL 2000,
the latter needs some catalog procedures that comes with SP4, or possbly
SP2.
Also the exact command you use to create the linked server could be of
interest.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Error when executing a distributed query on a linked server

Hi,
It's a repost of an old thread with more information included
I'm testing a Sql Server 2005 Enterprise Evaluation Edition 64 bits. I've
been trying to link a sql server 2000 SP4 to this but I'm having some
troubles. When creating the linked server from the Sql Server Management
Studio/New Linked Server I choosed Server type to be "SQL Server" for the 2K
linked server. The linked server was created. Then I go to
Properties/Security and configure the linked server to be accesed as sa. But
when I run a distributed query like
Select * from linkedserver.database.dbo.table I get the error:
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "SQLNCLI" for linked server "sqlservhpold" reported an
error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Line 2
Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked
server "sqlservhpold". A four-part name was supplied, but the provider does
not expose the necessary interfaces to use a catalog or schema.
I tested the link by using sp_testlinkedserver and it returns no failure.
I also created a linked server in the reverse. I mean let server A be the
Sql Server 2005 64 bits Enterprise, and server B be a Sql Server 2000
Standard. I'm in A making B as linked server and trying to make a
distributed query from A to B (Select * from B.database.dbo.table) and this
fails. I tryed in B create A as a linked server. Then I made a distributed
query from B to A (Select * from A.database.dbo.table) and it works as
expected.
The most frustrating is the message that error 7399 returns: "The provider
did not give any information about the error." I cn not find nothing about
such an error message...
Any hint is welcomed
Thanks in advance
SammySammyBar (sammybar@.gmail.com) writes:
> I'm testing a Sql Server 2005 Enterprise Evaluation Edition 64 bits.
> I've been trying to link a sql server 2000 SP4 to this but I'm having
> some troubles. When creating the linked server from the Sql Server
> Management Studio/New Linked Server I choosed Server type to be "SQL
> Server" for the 2K linked server. The linked server was created. Then I
> go to Properties/Security and configure the linked server to be accesed
> as sa. But when I run a distributed query like
> Select * from linkedserver.database.dbo.table I get the error:
> Msg 7399, Level 16, State 1, Line 2
> The OLE DB provider "SQLNCLI" for linked server "sqlservhpold" reported an
> error. The provider did not give any information about the error.
> Msg 7312, Level 16, State 1, Line 2
> Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked
> server "sqlservhpold". A four-part name was supplied, but the provider
> does not expose the necessary interfaces to use a catalog or schema.
Can you double check the SQL 2000 instance, by doing SELECT @.@.version?
I seem to recall that to run linked queries from SQL 2005 to SQL 2000,
the latter needs some catalog procedures that comes with SP4, or possbly
SP2.
Also the exact command you use to create the linked server could be of
interest.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Error when creating DMX query

On screen states [Error loading mining model metadata: No Metadata found]

and on the functions states [Error loading functions: no functions found]

This also occures when trying to use the Data mining wizard, it will just not respond.

Have deleted and removed all references to SQL2005 Standard and reloaded and still get same errors

Could someone please advise, have sent errors to Microsoft with no responce back yet.

I assume you are using the DMX query in SQL Management Studio?

These results occur when you have a database selected that doesn't contain any mining models. If you select a database in the database selector on the toolbar that contains models, you will get the list of models in the database in that pane of the user interface.

Also you could just do

"CREATE MINING MODEL foo ( custid LONG KEY, MyColumn LONG CONTINUOUS) USING Microsoft_Clustering"

and that would create a model in the current database that should show up in the metadata browser.

Given that you have issues using the wizard as well, it sounds like there may be some other issues. Can you provide more details?

Thanks,

-Jamie

Error when creating a view

Hello,

I'm getting the following error when I try to excute a view against my SQL 7.0 database:

The query processor encountered an unexpected error during execution.

When I click the help button this is what I get:

An ODBC error has been generated. You might have deleted a record that has a foreign key value related to it, or you might have violated a check constraint.

The view I'm trying to create is a query between a table and another view. Here is the other view:

SELECT CSQLocation.Location_ID, CSQMeasure.Measure_ID,
CSQLocation.Location, CSQMeasure.Measure_Name
FROM CSQLocation, CSQMeasure

Here is the view that I'm getting the error on

SELECT ISNULL(CSQObjective.Objective, 0) AS Objective,
vw_getLocationMeasure.Location,
vw_getLocationMeasure.Measure_Name,
vw_getLocationMeasure.Measure_ID
FROM CSQObjective RIGHT OUTER JOIN
vw_getLocationMeasure ON
CSQObjective.Location_ID = vw_getLocationMeasure.Location_ID
AND
CSQObjective.Measure_ID = vw_getLocationMeasure.Measure_ID

Does anyone have any ideas?

Thanks,
RyanWhy you need to use the other view (getLocationMeasure)? It seems the view doesn't add any value. Why don't you just use the two original tables? Just a thought.|||The reason I'm querying a view is because I wasn't able to get the information I was needed with one query and joins. Here is a break down of my db and what I need

MEASURE TABLE
Measure_ID
Measure_Name

LOCATION TABLE
Location_ID
Location

OBJECTIVE TABLE
Objective_ID
Location_ID
Measure_ID
Objective

There are 4 measures, there are about 100 locations, and there is 1 objective per measure per location. Does that make sense? So there will be about 400 obectives in the objective table. What I was trying to do is to return all the Locations, and all the Measures even if there was not and Objective associated with them. If there are no objectives I would like to return a 0, that is where the ISNULL came from. This is the output I would like to have

LOCATION - MEASURE - OBJECTIVE
Chicago - Clean Air - 78.8
Chicago - Clean Water - 75
Chicago - Clean Dumps - 0
Chicago - Clean Roads - 0
Dallas - Clean Air - 90
Dallas - Clean Water - 70
Dallas - Clean Dumps - 25
Dallas - Clean Roads - 0

etc... Does that make sense? With all the joins I've done I have only been able to return the Locations and Measures where there is acutally a value for the Objective. I would like to return 0 if there is no objective.

Thanks
Ryan

Error when connecting to server via SQL 2005 Management Studio in Object Explorer but not Query

Hi,

I'm getting the error below when I attempt to connect to a remote Sql 2005 server using Management Studio and the Object Explorer. The same error does not occur if I go through "New > Database Engine Query" and fill out the same connection dialog with the exact same information. FYI, I am connecting to the IP of an external SQL 2005 server via SQL Server Authentication, and there is some level of connection going on as connecting with the wrong authentication information gives me a standard authentication error even while using the Object Explorer dialog. I am able to do all operations I need in the database engine query window without problem.

---

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (Microsoft SQL Server, Error: 64)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=64&LinkId=20476

---

Any ideas on why this would occur? I know this is a standard "network issue" type problem but I'm not aware of any difference that would allow the connection to work fine in one and not the other.

Thanks!

I have this exact error too. Please post if you have made progress on this.

-H

|||Same error here as well. I can connect using 2005 management studio from my XP workstation, but my Vista machine throws this error.|||OK - Same error - also from a Vista machine. Has anyone found the solution|||I think there is some problem with the object explorer dlls. I got the error after installing MDAC 2.7. When i connect it says some dll does registered, but i can connect thru database engine query.|||This shouldn't be related to MDAC at all. Management Studio doesn't use MDAC for any connections. How are you establishing your connections (using server name or IP)?|||

I suspect you are running into a firewall issue on Vista. The built-in firewall on Vista is much more capable than the XP firewall. The Vista firewall can block outbound connections as well as the inbound connections the XP firewall can block.

Make sure you add a firewall exception for sqlwb.exe (the management studio executable). You might need to open up port 1433 TCP as well.

Hope this helps,

Steve

Error when connecting to server via SQL 2005 Management Studio in Object Explorer but not Query

Hi,

I'm getting the error below when I attempt to connect to a remote Sql 2005 server using Management Studio and the Object Explorer. The same error does not occur if I go through "New > Database Engine Query" and fill out the same connection dialog with the exact same information. FYI, I am connecting to the IP of an external SQL 2005 server via SQL Server Authentication, and there is some level of connection going on as connecting with the wrong authentication information gives me a standard authentication error even while using the Object Explorer dialog. I am able to do all operations I need in the database engine query window without problem.

---

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (Microsoft SQL Server, Error: 64)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=64&LinkId=20476

---

Any ideas on why this would occur? I know this is a standard "network issue" type problem but I'm not aware of any difference that would allow the connection to work fine in one and not the other.

Thanks!

I have this exact error too. Please post if you have made progress on this.

-H

|||Same error here as well. I can connect using 2005 management studio from my XP workstation, but my Vista machine throws this error.|||OK - Same error - also from a Vista machine. Has anyone found the solution|||I think there is some problem with the object explorer dlls. I got the error after installing MDAC 2.7. When i connect it says some dll does registered, but i can connect thru database engine query.|||This shouldn't be related to MDAC at all. Management Studio doesn't use MDAC for any connections. How are you establishing your connections (using server name or IP)?|||

I suspect you are running into a firewall issue on Vista. The built-in firewall on Vista is much more capable than the XP firewall. The Vista firewall can block outbound connections as well as the inbound connections the XP firewall can block.

Make sure you add a firewall exception for sqlwb.exe (the management studio executable). You might need to open up port 1433 TCP as well.

Hope this helps,

Steve

Error when connecting to server via SQL 2005 Management Studio in Object Explorer but not Query

Hi,

I'm getting the error below when I attempt to connect to a remote Sql 2005 server using Management Studio and the Object Explorer. The same error does not occur if I go through "New > Database Engine Query" and fill out the same connection dialog with the exact same information. FYI, I am connecting to the IP of an external SQL 2005 server via SQL Server Authentication, and there is some level of connection going on as connecting with the wrong authentication information gives me a standard authentication error even while using the Object Explorer dialog. I am able to do all operations I need in the database engine query window without problem.

---

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (Microsoft SQL Server, Error: 64)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=64&LinkId=20476

---

Any ideas on why this would occur? I know this is a standard "network issue" type problem but I'm not aware of any difference that would allow the connection to work fine in one and not the other.

Thanks!

I have this exact error too. Please post if you have made progress on this.

-H

|||Same error here as well. I can connect using 2005 management studio from my XP workstation, but my Vista machine throws this error.|||OK - Same error - also from a Vista machine. Has anyone found the solution|||I think there is some problem with the object explorer dlls. I got the error after installing MDAC 2.7. When i connect it says some dll does registered, but i can connect thru database engine query.|||This shouldn't be related to MDAC at all. Management Studio doesn't use MDAC for any connections. How are you establishing your connections (using server name or IP)?|||

I suspect you are running into a firewall issue on Vista. The built-in firewall on Vista is much more capable than the XP firewall. The Vista firewall can block outbound connections as well as the inbound connections the XP firewall can block.

Make sure you add a firewall exception for sqlwb.exe (the management studio executable). You might need to open up port 1433 TCP as well.

Hope this helps,

Steve

Error when connecting to server via SQL 2005 Management Studio in Object Explorer but not Query

Hi,

I'm getting the error below when I attempt to connect to a remote Sql 2005 server using Management Studio and the Object Explorer. The same error does not occur if I go through "New > Database Engine Query" and fill out the same connection dialog with the exact same information. FYI, I am connecting to the IP of an external SQL 2005 server via SQL Server Authentication, and there is some level of connection going on as connecting with the wrong authentication information gives me a standard authentication error even while using the Object Explorer dialog. I am able to do all operations I need in the database engine query window without problem.

---

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (Microsoft SQL Server, Error: 64)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=64&LinkId=20476

---

Any ideas on why this would occur? I know this is a standard "network issue" type problem but I'm not aware of any difference that would allow the connection to work fine in one and not the other.

Thanks!

I have this exact error too. Please post if you have made progress on this.

-H

|||Same error here as well. I can connect using 2005 management studio from my XP workstation, but my Vista machine throws this error.|||OK - Same error - also from a Vista machine. Has anyone found the solution|||I think there is some problem with the object explorer dlls. I got the error after installing MDAC 2.7. When i connect it says some dll does registered, but i can connect thru database engine query.|||This shouldn't be related to MDAC at all. Management Studio doesn't use MDAC for any connections. How are you establishing your connections (using server name or IP)?|||

I suspect you are running into a firewall issue on Vista. The built-in firewall on Vista is much more capable than the XP firewall. The Vista firewall can block outbound connections as well as the inbound connections the XP firewall can block.

Make sure you add a firewall exception for sqlwb.exe (the management studio executable). You might need to open up port 1433 TCP as well.

Hope this helps,

Steve

Error when checking if column exists before running query.

I am getting this error when the below query is run "Invalid column
name 'CellPhone'"
if exists (select * from information_schema.columns where table_name
='Customers' AND COLUMN_NAME = 'CellPhone')
UPDATE Customers SET PhoneNumber = CellPhone
GO
The Customers table exists but the column CellPhone doesn't.
SQL seems to try to validate the update query even if its not going to
be run.
If I change the table to something that doesn't exist, it will skip
over the line without any errors.
eg. UPDATE CustomersXXX SET PhoneNumber = CellPhone
So if the table does exist it must try to validate the columns against
the table.
Any ideas on how to get around this? I don't want it to run the query
if the column doesn't exist.
ThanksThere might be better ways but this splits the statement into two compiles:
if exists (select * from information_schema.columns where table_name
='Customers' AND COLUMN_NAME = 'CellPhone')
EXEC ('UPDATE Customers SET PhoneNumber = CellPhone')
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<focus.nz@.gmail.com> wrote in message
news:1151547434.570100.97390@.m73g2000cwd.googlegroups.com...
>I am getting this error when the below query is run "Invalid column
> name 'CellPhone'"
>
> if exists (select * from information_schema.columns where table_name
> ='Customers' AND COLUMN_NAME = 'CellPhone')
> UPDATE Customers SET PhoneNumber = CellPhone
> GO
>
> The Customers table exists but the column CellPhone doesn't.
> SQL seems to try to validate the update query even if its not going to
> be run.
> If I change the table to something that doesn't exist, it will skip
> over the line without any errors.
> eg. UPDATE CustomersXXX SET PhoneNumber = CellPhone
> So if the table does exist it must try to validate the columns against
> the table.
> Any ideas on how to get around this? I don't want it to run the query
> if the column doesn't exist.
> Thanks
>|||There might be better ways but this splits the statement into two compiles:
if exists (select * from information_schema.columns where table_name
='Customers' AND COLUMN_NAME = 'CellPhone')
EXEC ('UPDATE Customers SET PhoneNumber = CellPhone')
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<focus.nz@.gmail.com> wrote in message
news:1151547434.570100.97390@.m73g2000cwd.googlegroups.com...
>I am getting this error when the below query is run "Invalid column
> name 'CellPhone'"
>
> if exists (select * from information_schema.columns where table_name
> ='Customers' AND COLUMN_NAME = 'CellPhone')
> UPDATE Customers SET PhoneNumber = CellPhone
> GO
>
> The Customers table exists but the column CellPhone doesn't.
> SQL seems to try to validate the update query even if its not going to
> be run.
> If I change the table to something that doesn't exist, it will skip
> over the line without any errors.
> eg. UPDATE CustomersXXX SET PhoneNumber = CellPhone
> So if the table does exist it must try to validate the columns against
> the table.
> Any ideas on how to get around this? I don't want it to run the query
> if the column doesn't exist.
> Thanks
>|||Thanks, that did the trick.

Error when checking if column exists before running query.

I am getting this error when the below query is run "Invalid column
name 'CellPhone'"
if exists (select * from information_schema.columns where table_name
='Customers' AND COLUMN_NAME = 'CellPhone')
UPDATE Customers SET PhoneNumber = CellPhone
GO
The Customers table exists but the column CellPhone doesn't.
SQL seems to try to validate the update query even if its not going to
be run.
If I change the table to something that doesn't exist, it will skip
over the line without any errors.
eg. UPDATE CustomersXXX SET PhoneNumber = CellPhone
So if the table does exist it must try to validate the columns against
the table.
Any ideas on how to get around this? I don't want it to run the query
if the column doesn't exist.
ThanksThere might be better ways but this splits the statement into two compiles:
if exists (select * from information_schema.columns where table_name
='Customers' AND COLUMN_NAME = 'CellPhone')
EXEC ('UPDATE Customers SET PhoneNumber = CellPhone')
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<focus.nz@.gmail.com> wrote in message
news:1151547434.570100.97390@.m73g2000cwd.googlegroups.com...
>I am getting this error when the below query is run "Invalid column
> name 'CellPhone'"
>
> if exists (select * from information_schema.columns where table_name
> ='Customers' AND COLUMN_NAME = 'CellPhone')
> UPDATE Customers SET PhoneNumber = CellPhone
> GO
>
> The Customers table exists but the column CellPhone doesn't.
> SQL seems to try to validate the update query even if its not going to
> be run.
> If I change the table to something that doesn't exist, it will skip
> over the line without any errors.
> eg. UPDATE CustomersXXX SET PhoneNumber = CellPhone
> So if the table does exist it must try to validate the columns against
> the table.
> Any ideas on how to get around this? I don't want it to run the query
> if the column doesn't exist.
> Thanks
>|||Thanks, that did the trick.

Error when calling a Sub report (Jump to report)

I have created a two reports with a parameterized query - one master and one sub report.
When I want to call the sub report from the master report I would like to be able to pass the values for the parameters in to the sub report.

And I also don′t want the passed parameters to be prompted in the sub report (so I blanked out the 'Prompt:' field).

But I got following error:
The report parameter 'parameter name' is read-only and cannot be modified.

What to do?

Running Report Server: 8.00.743.00

/Christofer

Instead of blanking the Prompt field, check the 'Hidden' checkbox.|||

Well...
Should it be in the Report Parameters dialog box where I define parameters?
If so, I can′t find this checkbox "Hidden" in my version, or where should I find it?

Regards,
Christofer

error when an OLEDB source points to an OLEDB destination.

Hi all,

I got an error when i do an OLE db Source pointing to an sql 2000 database and executing a sql query inside the OLE Source. The ole source will point to an OLE DB destination which is an sql 2005 database.

But i got the below error:

Error at Data Flow Task [OLE DB Destination [245]]: the column firstname cannot be processed because more than one code page (936 and 1252) are specified for it.

Error at Data Flow Task [DTS.Pipeline]: "component "OLE DB destination" (245)" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow TaSK: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

my input columns firstname and lastname have 936 as codepages

but the destination columns lastname and firstname are set to 1252 codepages....and SSIS complains different codepages are used.

How do i change the defualt codepages to 936 for these two columns - firstname and lastname?

|||

You may want to try 1) set DefaultCodePage property at OLEDBDest to 936. 2) set AlwaysUseDefaultCodePage property at OLEDBDest to true.

That should fix the design time issue you got. However, you also need to make sure you set the destination column types properly at your dest table so as to receive good result.

HTH

wenyang

|||

I notice even if i set as 936 for the codepage...the default still doesnt change...so i add a data conversion transform which converts the column to 936 and add it to the destination column at 936 for codepage....