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 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 using join to access sql server content

Why would this error? The join works in Query Analyzer.

GridViewShowA.Visible = true;
String objConnection = ConfigurationManager.ConnectionStrings["MyConnection"].ToString();
String strSQL = "SELECT x.office as Office, x.email as Email, z.fname as 'First Name', z.lname as 'Last Name', ";
strSQL += "z.title as Title ";
strSQL += "FROM db1.dbo.tblA X ";
strSQL += "JOIN db2.dbo.tblA Z ";
strSQL += " ON x.email = z.email";
strSQL += "WHERE x.office = '" + DropDownListoffice.SelectedValue.ToString() + "' ";
strSQL += "AND z.email = x.email";
SqlDataAdapter objAdapter = new SqlDataAdapter(strSQL, objConnection);
DataSet dataSet = new DataSet();
objAdapter.Fill(dataSet, "myData");
DataTable dataTable = dataSet.Tables[0];
GridViewShowA.DataSource = dataTable.DefaultView;
GridViewShowA.DataBind();

error:

Line 1: Incorrect syntax near 'x'.
Description: An unhandled exception occurred during the execution of the current web request.
Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'x'.
Source Error:
Line 101: objAdapter.Fill(dataSet, "myData");

nevermind - fixed error, forgot space

Error to open SQL Profiler

I only Microsoft Clients Tools are installed in this Computer. Try to open
Profiler and Query Analyzer in Windows XP PRO SP2.
I get the following message:
"Program Files\Microsoft Sql Server\80\Tools\Binn\Profiler.exe is not a
valid Win32 application."
"Program Files\Microsoft Sql Server\80\Tools\Binn\isqlw.exe is not a valid
Win32 application."
How do I troubleshoot resolve this issue?
Thanks
Carlos Andrés Lozano G.
Solution Development
Try to reinstall Sql Server Client Tools.
Cristian Lefter, SQL Server MVP
"c@.rlos" <crlos@.discussions.microsoft.com> wrote in message
news:6266E3EB-6128-4E92-B640-E758ED1FB770@.microsoft.com...
>I only Microsoft Clients Tools are installed in this Computer. Try to open
> Profiler and Query Analyzer in Windows XP PRO SP2.
> I get the following message:
> "Program Files\Microsoft Sql Server\80\Tools\Binn\Profiler.exe is not a
> valid Win32 application."
> "Program Files\Microsoft Sql Server\80\Tools\Binn\isqlw.exe is not a valid
> Win32 application."
> How do I troubleshoot resolve this issue?
> --
> Thanks
> Carlos Andrs Lozano G.
> Solution Development
|||I have installed in 3 occasions and the same error leaves.
Will be able to be the computer?
"Cristian Lefter" wrote:

> Try to reinstall Sql Server Client Tools.
> Cristian Lefter, SQL Server MVP
|||c@.rlos wrote:[vbcol=seagreen]
> I have installed in 3 occasions and the same error leaves.
> Will be able to be the computer?
> "Cristian Lefter" wrote:
Try uninstalling the client tools, reinstalling from the SQL Server CD,
and then applying either the SP3a or SP4 (if you're already using SP4)
to the client. Make sure you install using an admin account.
Barring that option, you may have some unrelated corruption on the PC.
You could try reinstalling MDAC from http://www.microsoft.com/data.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||The installers are found in a unit shared of the Servant. We have done the
installation in a Windows 2000 and functions without problems
"David Gugick" escribió:

> Try uninstalling the client tools, reinstalling from the SQL Server CD,
> and then applying either the SP3a or SP4 (if you're already using SP4)
> to the client. Make sure you install using an admin account.
> Barring that option, you may have some unrelated corruption on the PC.
> You could try reinstalling MDAC from http://www.microsoft.com/data.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
|||isqlw is not a valid Win32 application
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Are you sure?
"Durga Prasad Datta" wrote:

> isqlw is not a valid Win32 application
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Durga Prasad Datta wrote:
> isqlw is not a valid Win32 application
>
It's a valid Windows EXE as far as I know. If you open up the EXE you
can see the ubiquitous "The program cannot be run in DOS mode" message
in the EXE header.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Error running SQL 2005 Best Practices Analyzer from command line - Object reference not set to a

I'm getting an error trying to run SQL 2005 Best Practices Analyzer from command line. Here's a sample of the command I run...

SqlBPACmd2005.EXE -u WINLOGON mydomain\\administrator P@.ssw0rd -r Limited -c -a

And here's the error I get...

Object reference not set to an instance of an object

I suspect this partially due to the fact that I left off the -sci option, and this appears to be a required parameter; however, I do not know XML and have not been able to find an example of this file anywhere. Could someone give me an example of an XML file formatted for scanning several 2005 instances?

Does my command have other problems?

Thanks!

Yes, you need to use the -sci options. The XML file can be created through the GUI. Run the GUI, choose the items you wish to scan, then choose, "Export to file". Pass this .xml filename in as the parameter to the -sci command line option.

Does this solve your problem?

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server
http://blogs.msdn.com/sqlrem/