Data Space Problem

Hello:
I have several SQL 2000 databases that displays 0 space
available when I view the properties of the database.
However when I change to the taskpad view, the view
indicates that there is space available.
Is this some kind of bug in SQL Server? Is there any fix
for it?
Any help would be appreciated?
Brennanrun DBCC UPDATEUSAGE and see if that helps.
Andrew J. Kelly SQL MVP
"Brennan" <anonymous@.discussions.microsoft.com> wrote in message
news:59ea01c40090$b4eedc60$a401280a@.phx.gbl...
> Hello:
> I have several SQL 2000 databases that displays 0 space
> available when I view the properties of the database.
> However when I change to the taskpad view, the view
> indicates that there is space available.
> Is this some kind of bug in SQL Server? Is there any fix
> for it?
> Any help would be appreciated?
> Brennan|||Thanks
That worked
Brennan

>--Original Message--
>run DBCC UPDATEUSAGE and see if that helps.
>--
>Andrew J. Kelly SQL MVP
>
>"Brennan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:59ea01c40090$b4eedc60$a401280a@.phx.gbl...
fix
>
>.
>

data length more than ...

Hello
I have problem with reading from XML when XML is to large.
Program delare 1-n variables where is declaration but can no make more
delarations than length 8000 :((

drop table tblBooksEx
CREATE TABLE [tblBooksEx] (
[Row_ID] [int] IDENTITY (1, 1) NOT NULL ,
[BooksData] [text] COLLATE Polish_CI_AS NULL ,
CONSTRAINT [PK__tblBooksEx__17036CC0] PRIMARY KEY CLUSTERED
(
[Row_ID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
insert into tblBooksEx(booksdata) values('')
exec master..xp_cmdshell 'TextCopy.exe /S serv /U usr /Ppsv /D Northwind /T
tblBooksEx /C BooksData /F c:\SCN.xml /W "WHERE Row_ID=1" /I'

/*PART 1*/
DECLARE @.id int
DECLARE @.idoc int
SET @.id = 1 -- or whatever the id

DECLARE @.datalen int
DECLARE @.sql varchar(8000)
DECLARE @.sql1 varchar(8000)
DECLARE @.cnt int
-- get the length
SELECT @.datalen = DATALENGTH (booksdata) / 4000 + 1 FROM tblBooksEx WHERE
row_id = @.id

-- phase 1 collect into @.sql declarations of @.str1, @.str2,...@.strn
SET @.cnt = 1
SET @.sql='DECLARE '
SET @.sql1 = ''
WHILE (@.cnt <= @.datalen)
BEGIN
SELECT
@.sql = @.sql + CASE @.cnt
WHEN 1 THEN ''
ELSE ', ' + CHAR(13)
END
+ ' @.str'+CONVERT(varchar(10),@.cnt)+' VARCHAR(4000)'
SET @.cnt = @.cnt + 1
END
-- phase 2 collect into @.sql selection of chunks (takng care of length)
SET @.cnt = 1
WHILE (@.cnt <= @.datalen)
BEGIN
IF LEN(@.sql) < 7850
SELECT @.sql = @.sql + CHAR (13) +
'SELECT @.str' + CONVERT(VARCHAR(10), @.cnt) + ' =
REPLACE(SUBSTRING(booksdata, ' +
CONVERT(VARCHAR(30), (@.cnt-1)*4000+1) + ', 4000),CHAR(39),
CHAR(39)+CHAR(39) ) ' +
'FROM tblBooksEx ' +
'WHERE row_id = ''' + cast(@.id as varchar) + ''''
ELSE
SELECT @.sql1 = @.sql1 + CHAR (13) +
'SELECT @.str' + CONVERT(VARCHAR(10), @.cnt) + ' =
REPLACE(SUBSTRING(booksdata, ' +
CONVERT(VARCHAR(30), (@.cnt-1)*4000+1) + ', 4000),CHAR(39),
CHAR(39)+CHAR(39) ) ' +
'FROM tblBooksEx ' +
'WHERE row_id = ''' + cast(@.id as varchar) + ''''
SET @.cnt = @.cnt + 1
END

/*PART 2*/
-- phase 3 preparing the 2nd level dynamic sql
SELECT @.sql1 = @.sql1 + CHAR(13) + 'EXEC ('+ CHAR(13) + '''DECLARE @.idoc
int'+ CHAR(13) +
'EXEC sp_xml_preparedocument @.idoc OUT, ''' + '
SET @.cnt = 1
WHILE (@.cnt <= @.datalen)
BEGIN
SELECT @.sql1 = @.sql1 + CHAR (13) + '@.str' + CONVERT (varchar(10), @.cnt) + '
+'
SET @.cnt = @.cnt + 1
END
SET @.sql1 = @.sql1 + ' ''' '
SET @.sql1 = @.sql1 + CHAR(13) + 'DECLARE idoc_cur CURSOR FOR SELECT @.idoc'''
+CHAR(13) + ')'
--debug code
/*
PRINT @.sql
PRINT '@.sql length=' +convert(varchar(5),datalength(@.sql))
PRINT '----'
PRINT @.sql1
PRINT '@.sql1 length=' +convert(varchar(5),datalength(@.sql1))
*/
EXEC (@.sql + @.sql1)
OPEN idoc_cur
FETCH NEXT FROM idoc_cur into @.idoc
DEALLOCATE idoc_cur

select * from OpenXML(@.idoc, '//transfer/body', 2) WITH (ng int, nk int, dw
nvarchar(50))
--When Complete
--/*
exec sp_xml_removedocument @.idoc
--*/

How to solve this problem??

Best Regards
AJA
"AJA" <ajanospam74@.gazeta.plwrote in message
news:eudi64$2c3$1@.inews.gazeta.pl...

Quote:

Originally Posted by

Hello
I have problem with reading from XML when XML is to large.
Program delare 1-n variables where is declaration but can no make more
delarations than length 8000 :((

If this is SQL 2005, try varchar(max)

Quote:

Originally Posted by

>
drop table tblBooksEx
CREATE TABLE [tblBooksEx] (
[Row_ID] [int] IDENTITY (1, 1) NOT NULL ,
[BooksData] [text] COLLATE Polish_CI_AS NULL ,
CONSTRAINT [PK__tblBooksEx__17036CC0] PRIMARY KEY CLUSTERED
(
[Row_ID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
insert into tblBooksEx(booksdata) values('')
exec master..xp_cmdshell 'TextCopy.exe /S serv /U usr /Ppsv /D Northwind
/T
tblBooksEx /C BooksData /F c:\SCN.xml /W "WHERE Row_ID=1" /I'
>
>
/*PART 1*/
DECLARE @.id int
DECLARE @.idoc int
SET @.id = 1 -- or whatever the id
>
DECLARE @.datalen int
DECLARE @.sql varchar(8000)
DECLARE @.sql1 varchar(8000)
DECLARE @.cnt int
-- get the length
SELECT @.datalen = DATALENGTH (booksdata) / 4000 + 1 FROM tblBooksEx WHERE
row_id = @.id
>
-- phase 1 collect into @.sql declarations of @.str1, @.str2,...@.strn
SET @.cnt = 1
SET @.sql='DECLARE '
SET @.sql1 = ''
WHILE (@.cnt <= @.datalen)
BEGIN
SELECT
@.sql = @.sql + CASE @.cnt
WHEN 1 THEN ''
ELSE ', ' + CHAR(13)
END
+ ' @.str'+CONVERT(varchar(10),@.cnt)+' VARCHAR(4000)'
SET @.cnt = @.cnt + 1
END
-- phase 2 collect into @.sql selection of chunks (takng care of length)
SET @.cnt = 1
WHILE (@.cnt <= @.datalen)
BEGIN
IF LEN(@.sql) < 7850
SELECT @.sql = @.sql + CHAR (13) +
'SELECT @.str' + CONVERT(VARCHAR(10), @.cnt) + ' =
REPLACE(SUBSTRING(booksdata, ' +
CONVERT(VARCHAR(30), (@.cnt-1)*4000+1) + ', 4000),CHAR(39),
CHAR(39)+CHAR(39) ) ' +
'FROM tblBooksEx ' +
'WHERE row_id = ''' + cast(@.id as varchar) + ''''
ELSE
SELECT @.sql1 = @.sql1 + CHAR (13) +
'SELECT @.str' + CONVERT(VARCHAR(10), @.cnt) + ' =
REPLACE(SUBSTRING(booksdata, ' +
CONVERT(VARCHAR(30), (@.cnt-1)*4000+1) + ', 4000),CHAR(39),
CHAR(39)+CHAR(39) ) ' +
'FROM tblBooksEx ' +
'WHERE row_id = ''' + cast(@.id as varchar) + ''''
SET @.cnt = @.cnt + 1
END
>
>
/*PART 2*/
-- phase 3 preparing the 2nd level dynamic sql
SELECT @.sql1 = @.sql1 + CHAR(13) + 'EXEC ('+ CHAR(13) + '''DECLARE @.idoc
int'+ CHAR(13) +
'EXEC sp_xml_preparedocument @.idoc OUT, ''' + '
SET @.cnt = 1
WHILE (@.cnt <= @.datalen)
BEGIN
SELECT @.sql1 = @.sql1 + CHAR (13) + '@.str' + CONVERT (varchar(10), @.cnt) +
'
+'
SET @.cnt = @.cnt + 1
END
SET @.sql1 = @.sql1 + ' ''' '
SET @.sql1 = @.sql1 + CHAR(13) + 'DECLARE idoc_cur CURSOR FOR SELECT
@.idoc'''
+CHAR(13) + ')'
--debug code
/*
PRINT @.sql
PRINT '@.sql length=' +convert(varchar(5),datalength(@.sql))
PRINT '----'
PRINT @.sql1
PRINT '@.sql1 length=' +convert(varchar(5),datalength(@.sql1))
*/
EXEC (@.sql + @.sql1)
OPEN idoc_cur
FETCH NEXT FROM idoc_cur into @.idoc
DEALLOCATE idoc_cur
>
>
>
>
select * from OpenXML(@.idoc, '//transfer/body', 2) WITH (ng int, nk int,
dw
nvarchar(50))
--When Complete
--/*
exec sp_xml_removedocument @.idoc
--*/
>
How to solve this problem??
>
>
Best Regards
AJA
>

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||>Hello

Quote:

Originally Posted by

Quote:

Originally Posted by

>I have problem with reading from XML when XML is to large.
>Program delare 1-n variables where is declaration but can no make more
>delarations than length 8000 :((

>
If this is SQL 2005, try varchar(max)

Sorry i forgot tell its MSSQL 2k :(( any other idea?

AJA

Quote:

Originally Posted by

>
>

Quote:

Originally Posted by

>>
>drop table tblBooksEx
>CREATE TABLE [tblBooksEx] (
>[Row_ID] [int] IDENTITY (1, 1) NOT NULL ,
>[BooksData] [text] COLLATE Polish_CI_AS NULL ,
>CONSTRAINT [PK__tblBooksEx__17036CC0] PRIMARY KEY CLUSTERED
>(
> [Row_ID]
>) ON [PRIMARY]
>) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>GO
>insert into tblBooksEx(booksdata) values('')
>exec master..xp_cmdshell 'TextCopy.exe /S serv /U usr /Ppsv /D Northwind
>/T
>tblBooksEx /C BooksData /F c:\SCN.xml /W "WHERE Row_ID=1" /I'
>>
>>
>/*PART 1*/
>DECLARE @.id int
>DECLARE @.idoc int
>SET @.id = 1 -- or whatever the id
>>
>DECLARE @.datalen int
>DECLARE @.sql varchar(8000)
>DECLARE @.sql1 varchar(8000)
>DECLARE @.cnt int
>-- get the length
>SELECT @.datalen = DATALENGTH (booksdata) / 4000 + 1 FROM tblBooksEx WHERE
>row_id = @.id
>>
>-- phase 1 collect into @.sql declarations of @.str1, @.str2,...@.strn
>SET @.cnt = 1
>SET @.sql='DECLARE '
>SET @.sql1 = ''
>WHILE (@.cnt <= @.datalen)
>BEGIN
>SELECT
>@.sql = @.sql + CASE @.cnt
>WHEN 1 THEN ''
>ELSE ', ' + CHAR(13)
>END
>+ ' @.str'+CONVERT(varchar(10),@.cnt)+' VARCHAR(4000)'
>SET @.cnt = @.cnt + 1
>END
>-- phase 2 collect into @.sql selection of chunks (takng care of length)
>SET @.cnt = 1
>WHILE (@.cnt <= @.datalen)
>BEGIN
>IF LEN(@.sql) < 7850
>SELECT @.sql = @.sql + CHAR (13) +
>'SELECT @.str' + CONVERT(VARCHAR(10), @.cnt) + ' =
>REPLACE(SUBSTRING(booksdata, ' +
>CONVERT(VARCHAR(30), (@.cnt-1)*4000+1) + ', 4000),CHAR(39),
>CHAR(39)+CHAR(39) ) ' +
>'FROM tblBooksEx ' +
>'WHERE row_id = ''' + cast(@.id as varchar) + ''''
>ELSE
>SELECT @.sql1 = @.sql1 + CHAR (13) +
>'SELECT @.str' + CONVERT(VARCHAR(10), @.cnt) + ' =
>REPLACE(SUBSTRING(booksdata, ' +
>CONVERT(VARCHAR(30), (@.cnt-1)*4000+1) + ', 4000),CHAR(39),
>CHAR(39)+CHAR(39) ) ' +
>'FROM tblBooksEx ' +
>'WHERE row_id = ''' + cast(@.id as varchar) + ''''
>SET @.cnt = @.cnt + 1
>END
>>
>>
>/*PART 2*/
>-- phase 3 preparing the 2nd level dynamic sql
>SELECT @.sql1 = @.sql1 + CHAR(13) + 'EXEC ('+ CHAR(13) + '''DECLARE @.idoc
>int'+ CHAR(13) +
>'EXEC sp_xml_preparedocument @.idoc OUT, ''' + '
>SET @.cnt = 1
>WHILE (@.cnt <= @.datalen)
>BEGIN
>SELECT @.sql1 = @.sql1 + CHAR (13) + '@.str' + CONVERT (varchar(10), @.cnt) +
>'
>+'
>SET @.cnt = @.cnt + 1
>END
>SET @.sql1 = @.sql1 + ' ''' '
>SET @.sql1 = @.sql1 + CHAR(13) + 'DECLARE idoc_cur CURSOR FOR SELECT
>@.idoc'''
>+CHAR(13) + ')'
>--debug code
>/*
>PRINT @.sql
>PRINT '@.sql length=' +convert(varchar(5),datalength(@.sql))
>PRINT '----'
>PRINT @.sql1
>PRINT '@.sql1 length=' +convert(varchar(5),datalength(@.sql1))
>*/
>EXEC (@.sql + @.sql1)
>OPEN idoc_cur
>FETCH NEXT FROM idoc_cur into @.idoc
>DEALLOCATE idoc_cur
>>
>>
>>
>>
>select * from OpenXML(@.idoc, '//transfer/body', 2) WITH (ng int, nk int,
>dw
>nvarchar(50))
>--When Complete
>--/*
>exec sp_xml_removedocument @.idoc
>--*/
>>
>How to solve this problem??
>>
>>
>Best Regards
>AJA
>>

>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com
http://www.greenms.com/sqlserver.html
>
>

|||On Mar 28, 4:05 pm, "AJA" <ajanospa...@.gazeta.plwrote:

Quote:

Originally Posted by

Hello
I have problem with reading from XML when XML is to large.
Program delare 1-n variables where is declaration but can no make more
delarations than length 8000 :((
>
drop table tblBooksEx
CREATE TABLE [tblBooksEx] (
[Row_ID] [int] IDENTITY (1, 1) NOT NULL ,
[BooksData] [text] COLLATE Polish_CI_AS NULL ,
CONSTRAINT [PK__tblBooksEx__17036CC0] PRIMARY KEY CLUSTERED
(
[Row_ID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
insert into tblBooksEx(booksdata) values('')
exec master..xp_cmdshell 'TextCopy.exe /S serv /U usr /Ppsv /D Northwind /T
tblBooksEx /C BooksData /F c:\SCN.xml /W "WHERE Row_ID=1" /I'
>
/*PART 1*/
DECLARE @.id int
DECLARE @.idoc int
SET @.id = 1 -- or whatever the id
>
DECLARE @.datalen int
DECLARE @.sql varchar(8000)
DECLARE @.sql1 varchar(8000)
DECLARE @.cnt int
-- get the length
SELECT @.datalen = DATALENGTH (booksdata) / 4000 + 1 FROM tblBooksEx WHERE
row_id = @.id
>
-- phase 1 collect into @.sql declarations of @.str1, @.str2,...@.strn
SET @.cnt = 1
SET @.sql='DECLARE '
SET @.sql1 = ''
WHILE (@.cnt <= @.datalen)
BEGIN
SELECT
@.sql = @.sql + CASE @.cnt
WHEN 1 THEN ''
ELSE ', ' + CHAR(13)
END
+ ' @.str'+CONVERT(varchar(10),@.cnt)+' VARCHAR(4000)'
SET @.cnt = @.cnt + 1
END
-- phase 2 collect into @.sql selection of chunks (takng care of length)
SET @.cnt = 1
WHILE (@.cnt <= @.datalen)
BEGIN
IF LEN(@.sql) < 7850
SELECT @.sql = @.sql + CHAR (13) +
'SELECT @.str' + CONVERT(VARCHAR(10), @.cnt) + ' =
REPLACE(SUBSTRING(booksdata, ' +
CONVERT(VARCHAR(30), (@.cnt-1)*4000+1) + ', 4000),CHAR(39),
CHAR(39)+CHAR(39) ) ' +
'FROM tblBooksEx ' +
'WHERE row_id = ''' + cast(@.id as varchar) + ''''
ELSE
SELECT @.sql1 = @.sql1 + CHAR (13) +
'SELECT @.str' + CONVERT(VARCHAR(10), @.cnt) + ' =
REPLACE(SUBSTRING(booksdata, ' +
CONVERT(VARCHAR(30), (@.cnt-1)*4000+1) + ', 4000),CHAR(39),
CHAR(39)+CHAR(39) ) ' +
'FROM tblBooksEx ' +
'WHERE row_id = ''' + cast(@.id as varchar) + ''''
SET @.cnt = @.cnt + 1
END
>
/*PART 2*/
-- phase 3 preparing the 2nd level dynamic sql
SELECT @.sql1 = @.sql1 + CHAR(13) + 'EXEC ('+ CHAR(13) + '''DECLARE @.idoc
int'+ CHAR(13) +
'EXEC sp_xml_preparedocument @.idoc OUT, ''' + '
SET @.cnt = 1
WHILE (@.cnt <= @.datalen)
BEGIN
SELECT @.sql1 = @.sql1 + CHAR (13) + '@.str' + CONVERT (varchar(10), @.cnt) + '
+'
SET @.cnt = @.cnt + 1
END
SET @.sql1 = @.sql1 + ' ''' '
SET @.sql1 = @.sql1 + CHAR(13) + 'DECLARE idoc_cur CURSOR FOR SELECT @.idoc'''
+CHAR(13) + ')'
--debug code
/*
PRINT @.sql
PRINT '@.sql length=' +convert(varchar(5),datalength(@.sql))
PRINT '----'
PRINT @.sql1
PRINT '@.sql1 length=' +convert(varchar(5),datalength(@.sql1))
*/
EXEC (@.sql + @.sql1)
OPEN idoc_cur
FETCH NEXT FROM idoc_cur into @.idoc
DEALLOCATE idoc_cur
>
select * from OpenXML(@.idoc, '//transfer/body', 2) WITH (ng int, nk int, dw
nvarchar(50))
--When Complete
--/*
exec sp_xml_removedocument @.idoc
--*/
>
How to solve this problem??
>
Best Regards
AJA

If you make an SP(stored Procedure) out of the Code , you can send
xml to SP as a text parameter|||

Quote:

Originally Posted by

If you make an SP(stored Procedure) out of the Code , you can send
xml to SP as a text parameter
>

Can you tell me clearly because i do not understand ..

AJA|||On Mar 29, 4:05 pm, "AJA" <ajanospa...@.gazeta.plwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

If you make an SP(stored Procedure) out of the Code , you can send
xml to SP as a text parameter

>
Can you tell me clearly because i do not understand ..
>
AJA

Need to do in an SP . You need to send text from client side. Sending
text within sql server not possible for SQL 2000

create proc usp_getxml
@.xmltext TEXT
as
Declare @.hdoc INT
--Create XML document.
EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.xmltext