Problem with sp_xml_preparedocument

Hi All,

Follwing are the 2 code snippets of XOPEN

1.
declare @.idoc int
declare @.doc varchar(1000)
set @.doc ='
<b>
<a>
<s>aaa</s>
</a>
<a>
<s>bbb</s>
</a>
</b>'
exec sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc,'/b/a',2)
WITH (s varchar(100) '@.s')

2.

create table newtemp
(
s varchar(100)
)
declare @.idoc int
declare @.doc varchar(1000)
set @.doc ='
<b>
<a>
<s>aaa</s>
</a>
<a>
<s>bbb</s>
</a>
</b>'
exec sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc,'/b/a',2)
WITH newtemp

Code snippet 2 works but 1 doesnot.

Can some one me the reason why ?

Thanks & Regards

Nitesh

Try changing

WITH (s varchar(100) '@.s')

to

WITH (s varchar(100) 's')

|||

Thanks a lot !!!!

It worked out.

Problem with select to another server.

Hello All!

This is the first time I'm working between two SQL servers. Here is a simple SELECT statement.

DECLARE @.Acct nvarchar(50)

SELECT First_Name, Last_Name, Age, DOB, Account_Number

FROM [MKE01-Demo-XX].SAHPharm.dbo.Active_Orders

WHERE [MKE01-2NX2461].[Pharm Test Local].dbo.Active_Orders = @.Acct

Here is the error

Msg 4104, Level 16, State 1, Line 4

The multi-part identifier "MKE01-2NX2461.Pharm Test Local.dbo.Active_Orders" could not be bound.

What am I doing wrong?

Thanks!

Rudy

MKE01-2NX2461 is set up as a Linked Server in MKE01-Demo-XX?

|||

You are attempting to compare a table to a variable.

WHERE [MKE01-2NX2461].[Pharm Test Local].dbo.Active_Orders = @.Acct

It would be nice, but it isn't going to happen...

But more critically, I don't see any relationship between the two tables from the two databases. How would a row from [MKE01-2NX2461].[Pharm Test Local].dbo.Active_Orders be connected to the table [MKE01-Demo-XX].SAHPharm.dbo.Active_Orders in such a way that using the variable value against one table 'should' return a row from the other table.

I think that something isn't quite right here... (Perhaps a JOIN is missing.)

|||

Hi guys!

Ah yes. A JOIN would make sense. Mke Demo is the linked serve on MKe 29nx... So let me give that a shot. I'm sure I'll be back here with a question or two.

Thanks!

Rudy

|||

Perhaps something more like this?

Code Snippet

DECLARE @.Acct nvarchar(50)

SET @.Acct = {someValue}

SELECT
x.First_Name,
x.Last_Name,
x.Age,
x.DOB,
x.Account_Number
FROM [MKE01-Demo-XX].SAHPharm.dbo.Active_Orders x
JOIN [MKE01-2NX2461].[Pharm Test Local].dbo.Active_Orders t
ON x.Account_Number = t.Account_Number
WHERE x.Account_Number = @.Acct

This assumes that the values you wish to return are located in the [MKE01-Demo-XX].SAHPharm.dbo.Active_Orders table, and that both tables have the Account_Number column to link the data.

Problem with Script

Hey all,
Can anyone explain me why this doesnt work:
declare db_defrag cursor for
select [name] from master..sysdatabases where dbid
NOT IN (1,2,3,4,5,6) for read only
declare @.dbname varchar(50)
open db_defrag
fetch next from db_defrag into @.dbname
while @.@.fetch_status = 0
*****declare show_defrag cursor for
exec ('select [name] from ' + @.dbname
+ '..sysobjects where type =''U''') for read only *** how
come i cannot use the variable @.dbname in the second
cursor without getting an error? Any workarround?
Please help,
Thanxs> *****declare show_defrag cursor for
> exec ('select [name] from ' + @.dbname
> + '..sysobjects where type =''U''') for read only *** how
> come i cannot use the variable @.dbname in the second
> cursor without getting an error? Any workarround?
> Please help,
Do the whole lot inside dynamic SQL:
SET @.sql = 'DECLARE show_defrag cursor for SELECT "name" FROM ' + @.dbname +
'..sysobjects WHERE ...'
EXEC(@.sql)
However, you don't have to loop inside the database for DBCC SHOWCONTIG.
Just use DBCC SHOWCONTIG at the database level using the WITH TABLERESULTS
option.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
":)" <anonymous@.discussions.microsoft.com> wrote in message
news:5b3a01c3ad27$de17fe30$a601280a@.phx.gbl...
> Hey all,
> Can anyone explain me why this doesnt work:
> declare db_defrag cursor for
> select [name] from master..sysdatabases where dbid
> NOT IN (1,2,3,4,5,6) for read only
> declare @.dbname varchar(50)
> open db_defrag
> fetch next from db_defrag into @.dbname
> while @.@.fetch_status = 0
> *****declare show_defrag cursor for
> exec ('select [name] from ' + @.dbname
> + '..sysobjects where type =''U''') for read only *** how
> come i cannot use the variable @.dbname in the second
> cursor without getting an error? Any workarround?
> Please help,
> Thanxs|||It dont seem to work... the process just
stays "pending"... Here is the whole script.. any help
would be greatful:
set nocount on
CREATE TABLE #aux (
[ObjectName] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ObjectId] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[IndexName] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[IndexId] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Level] [varchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Pages] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Rows] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[MinimumRecordSize] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[MaximumRecordSize] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AverageRecordSize] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[FowardedRecords] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Extents] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ExtentSwitches] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AverageFreeBytes] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AveragePageDensity] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ScanDensity] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[BestCount] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ActualCount] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[LogicalFragmentation] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ExtentFragmentation] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
declare db_defrag cursor for
select [name] from master..sysdatabases where dbid
NOT IN (1,2,3,4,5,6) for read only
declare @.dbname varchar(50)
open db_defrag
fetch next from db_defrag into @.dbname
while @.@.fetch_status = 0
declare @.sql varchar (300)
SET @.sql = 'DECLARE show_defrag cursor for
SELECT [name] FROM ' + @.dbname
+ '..sysobjects WHERE type = ''U'' for read only'
declare @.objectname varchar(200)
declare @.msg varchar(200)
EXEC(@.sql)
--declare show_defrag cursor for
--select [name] from ..sysobjects where type = 'U'
for read only
open show_defrag
fetch next from show_defrag into @.objectname
while @.@.fetch_status = 0
Begin
INSERT INTO #aux
EXEC ('DBCC SHOWCONTIG (' + @.objectname
+ ') WITH TABLERESULTS')
if exists (select Objectname from #aux where
Objectname = @.objectname and
ExtentSwitches > Extents or
ScanDensity < cast (95.0 as Decimal) or
LogicalFragmentation > cast (10.0 as Decimal) or
ExtentFragmentation > cast (10.0 as Decimal))
Begin
set @.msg = 'The table ' + @.objectname + ' in
database' + @.dbname + ' is fragmented'
Print @.msg
--exec master..xp_logevent 51515, @.msg,
informational
End
fetch next from db_defrag into @.dbname
fetch next from show_defrag into @.objectname
End
deallocate db_defrag
deallocate show_defrag
drop table #aux
set nocount off
>--Original Message--
>> *****declare show_defrag cursor for
>> exec ('select [name] from ' + @.dbname
>> + '..sysobjects where type =''U''') for read only ***
how
>> come i cannot use the variable @.dbname in the second
>> cursor without getting an error? Any workarround?
>> Please help,
>Do the whole lot inside dynamic SQL:
>SET @.sql = 'DECLARE show_defrag cursor for SELECT "name"
FROM ' + @.dbname +
>'..sysobjects WHERE ...'
>EXEC(@.sql)
>However, you don't have to loop inside the database for
DBCC SHOWCONTIG.
>Just use DBCC SHOWCONTIG at the database level using the
WITH TABLERESULTS
>option.
>
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>":)" <anonymous@.discussions.microsoft.com> wrote in
message
>news:5b3a01c3ad27$de17fe30$a601280a@.phx.gbl...
>> Hey all,
>> Can anyone explain me why this doesnt work:
>> declare db_defrag cursor for
>> select [name] from master..sysdatabases where dbid
>> NOT IN (1,2,3,4,5,6) for read only
>> declare @.dbname varchar(50)
>> open db_defrag
>> fetch next from db_defrag into @.dbname
>> while @.@.fetch_status = 0
>> *****declare show_defrag cursor for
>> exec ('select [name] from ' + @.dbname
>> + '..sysobjects where type =''U''') for read only ***
how
>> come i cannot use the variable @.dbname in the second
>> cursor without getting an error? Any workarround?
>> Please help,
>> Thanxs
>
>.
>|||One thing I see that that inside your WHILE, you need BEGIN and END:
WHILE ...
BEGIN
...
...
FETCH NEXT FROM ...
END
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
":)" <anonymous@.discussions.microsoft.com> wrote in message
news:0b9501c3adb6$987ae5b0$a101280a@.phx.gbl...
> It dont seem to work... the process just
> stays "pending"... Here is the whole script.. any help
> would be greatful:
>
>
> set nocount on
> CREATE TABLE #aux (
> [ObjectName] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ObjectId] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [IndexName] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [IndexId] [varchar] (15) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Level] [varchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Pages] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Rows] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MinimumRecordSize] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MaximumRecordSize] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AverageRecordSize] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [FowardedRecords] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Extents] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ExtentSwitches] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AverageFreeBytes] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AveragePageDensity] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ScanDensity] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [BestCount] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ActualCount] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [LogicalFragmentation] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ExtentFragmentation] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> declare db_defrag cursor for
> select [name] from master..sysdatabases where dbid
> NOT IN (1,2,3,4,5,6) for read only
> declare @.dbname varchar(50)
> open db_defrag
> fetch next from db_defrag into @.dbname
> while @.@.fetch_status = 0
> declare @.sql varchar (300)
> SET @.sql = 'DECLARE show_defrag cursor for
> SELECT [name] FROM ' + @.dbname
> + '..sysobjects WHERE type = ''U'' for read only'
> declare @.objectname varchar(200)
> declare @.msg varchar(200)
> EXEC(@.sql)
>
> --declare show_defrag cursor for
> --select [name] from ..sysobjects where type = 'U'
> for read only
>
> open show_defrag
> fetch next from show_defrag into @.objectname
> while @.@.fetch_status = 0
> Begin
> INSERT INTO #aux
> EXEC ('DBCC SHOWCONTIG (' + @.objectname
> + ') WITH TABLERESULTS')
>
> if exists (select Objectname from #aux where
> Objectname = @.objectname and
> ExtentSwitches > Extents or
> ScanDensity < cast (95.0 as Decimal) or
> LogicalFragmentation > cast (10.0 as Decimal) or
> ExtentFragmentation > cast (10.0 as Decimal))
> Begin
> set @.msg = 'The table ' + @.objectname + ' in
> database' + @.dbname + ' is fragmented'
> Print @.msg
> --exec master..xp_logevent 51515, @.msg,
> informational
> End
> fetch next from db_defrag into @.dbname
> fetch next from show_defrag into @.objectname
> End
> deallocate db_defrag
> deallocate show_defrag
> drop table #aux
>
> set nocount off
>
>
>
> >--Original Message--
> >> *****declare show_defrag cursor for
> >> exec ('select [name] from ' + @.dbname
> >> + '..sysobjects where type =''U''') for read only ***
> how
> >> come i cannot use the variable @.dbname in the second
> >> cursor without getting an error? Any workarround?
> >> Please help,
> >
> >Do the whole lot inside dynamic SQL:
> >
> >SET @.sql = 'DECLARE show_defrag cursor for SELECT "name"
> FROM ' + @.dbname +
> >'..sysobjects WHERE ...'
> >EXEC(@.sql)
> >
> >However, you don't have to loop inside the database for
> DBCC SHOWCONTIG.
> >Just use DBCC SHOWCONTIG at the database level using the
> WITH TABLERESULTS
> >option.
> >
> >
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >":)" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:5b3a01c3ad27$de17fe30$a601280a@.phx.gbl...
> >> Hey all,
> >>
> >> Can anyone explain me why this doesnt work:
> >>
> >> declare db_defrag cursor for
> >> select [name] from master..sysdatabases where dbid
> >> NOT IN (1,2,3,4,5,6) for read only
> >>
> >> declare @.dbname varchar(50)
> >>
> >> open db_defrag
> >> fetch next from db_defrag into @.dbname
> >>
> >> while @.@.fetch_status = 0
> >>
> >> *****declare show_defrag cursor for
> >> exec ('select [name] from ' + @.dbname
> >> + '..sysobjects where type =''U''') for read only ***
> how
> >> come i cannot use the variable @.dbname in the second
> >> cursor without getting an error? Any workarround?
> >> Please help,
> >>
> >> Thanxs
> >
> >
> >.
> >