install SQL on WinXP: auto

Showing posts with label auto. Show all posts

Tuesday, March 20, 2012

Can not allocate space for rebuilding index

I got the error msg below while rebuilding index for a large table. I have
allocated plenty of space for the primary filegroup and "auto" grow by 10% is
set. There are plenty of space in the drive. Please help me to resolve this
isse.
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.Hi
Rather grow the DB to the correct size before you do such an operation.
Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
while. During this time, the DBCC command will find the DB full and abort.
The DBCC command does not know that more space will be available shortly.
Regards
Mike
"Bill Little" wrote:
> I got the error msg below while rebuilding index for a large table. I have
> allocated plenty of space for the primary filegroup and "auto" grow by 10% is
> set. There are plenty of space in the drive. Please help me to resolve this
> isse.
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
> id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
>|||Thank you very much. I have set the space for the primary filegroup the same
size as the size of the whole database. Do I still need to increase the size
or the size of the log file?
Regards
Bill
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Rather grow the DB to the correct size before you do such an operation.
> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
> while. During this time, the DBCC command will find the DB full and abort.
> The DBCC command does not know that more space will be available shortly.
> Regards
> Mike
> "Bill Little" wrote:
> > I got the error msg below while rebuilding index for a large table. I have
> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
> > set. There are plenty of space in the drive. Please help me to resolve this
> > isse.
> >
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> > terminated.
> >|||> I have set the space for the primary filegroup the same
> size as the size of the whole database.
Whether that is enough or not is impossible to answer without more information. If the table has a
clustered index, rebuilding is essentially copying the table to a new location and after the copy
removing the old table. I.e., you need as much free space as the size of the table (plus some ore)
on the file group where the table resides.
> Do I still need to increase the size
> or the size of the log file?
Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data moves (as
explained above) will be logged to the transaction log.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
> Thank you very much. I have set the space for the primary filegroup the same
> size as the size of the whole database. Do I still need to increase the size
> or the size of the log file?
> Regards
> Bill
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> Rather grow the DB to the correct size before you do such an operation.
>> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
>> while. During this time, the DBCC command will find the DB full and abort.
>> The DBCC command does not know that more space will be available shortly.
>> Regards
>> Mike
>> "Bill Little" wrote:
>> > I got the error msg below while rebuilding index for a large table. I have
>> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
>> > set. There are plenty of space in the drive. Please help me to resolve this
>> > isse.
>> >
>> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
>> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
>> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
>> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
>> > terminated.
>> >|||Thanks. I tried to add more space to the database but I got the error msg
below. What shall we do to resolve the issue?
Server: Msg 1827, Level 16, State 2, Line 1
CREATE/ALTER DATABASE failed because the resulting cumulative database size
would exceed your licensed limit of 2048 MB per database.
Extending database by 1024.00 MB on disk 'Primary'.
"Tibor Karaszi" wrote:
> > I have set the space for the primary filegroup the same
> > size as the size of the whole database.
> Whether that is enough or not is impossible to answer without more information. If the table has a
> clustered index, rebuilding is essentially copying the table to a new location and after the copy
> removing the old table. I.e., you need as much free space as the size of the table (plus some ore)
> on the file group where the table resides.
>
> > Do I still need to increase the size
> > or the size of the log file?
> Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data moves (as
> explained above) will be logged to the transaction log.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
> > Thank you very much. I have set the space for the primary filegroup the same
> > size as the size of the whole database. Do I still need to increase the size
> > or the size of the log file?
> >
> > Regards
> >
> > Bill
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> >> Hi
> >>
> >> Rather grow the DB to the correct size before you do such an operation.
> >>
> >> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
> >> while. During this time, the DBCC command will find the DB full and abort.
> >> The DBCC command does not know that more space will be available shortly.
> >>
> >> Regards
> >> Mike
> >>
> >> "Bill Little" wrote:
> >>
> >> > I got the error msg below while rebuilding index for a large table. I have
> >> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
> >> > set. There are plenty of space in the drive. Please help me to resolve this
> >> > isse.
> >> >
> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
> >> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
> >> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> >> > terminated.
> >> >
>
>|||Seems you are running MSDE, which has a max database size of 2GB data per database. You might want
to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...
> Thanks. I tried to add more space to the database but I got the error msg
> below. What shall we do to resolve the issue?
> Server: Msg 1827, Level 16, State 2, Line 1
> CREATE/ALTER DATABASE failed because the resulting cumulative database size
> would exceed your licensed limit of 2048 MB per database.
> Extending database by 1024.00 MB on disk 'Primary'.
>
> "Tibor Karaszi" wrote:
>> > I have set the space for the primary filegroup the same
>> > size as the size of the whole database.
>> Whether that is enough or not is impossible to answer without more information. If the table has
>> a
>> clustered index, rebuilding is essentially copying the table to a new location and after the copy
>> removing the old table. I.e., you need as much free space as the size of the table (plus some
>> ore)
>> on the file group where the table resides.
>>
>> > Do I still need to increase the size
>> > or the size of the log file?
>> Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data moves
>> (as
>> explained above) will be logged to the transaction log.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
>> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
>> > Thank you very much. I have set the space for the primary filegroup the same
>> > size as the size of the whole database. Do I still need to increase the size
>> > or the size of the log file?
>> >
>> > Regards
>> >
>> > Bill
>> >
>> > "Mike Epprecht (SQL MVP)" wrote:
>> >
>> >> Hi
>> >>
>> >> Rather grow the DB to the correct size before you do such an operation.
>> >>
>> >> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
>> >> while. During this time, the DBCC command will find the DB full and abort.
>> >> The DBCC command does not know that more space will be available shortly.
>> >>
>> >> Regards
>> >> Mike
>> >>
>> >> "Bill Little" wrote:
>> >>
>> >> > I got the error msg below while rebuilding index for a large table. I have
>> >> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
>> >> > set. There are plenty of space in the drive. Please help me to resolve this
>> >> > isse.
>> >> >
>> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
>> >> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
>> >> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
>> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
>> >> > terminated.
>> >> >
>>|||Thank you very much. We are using standard edition. So we cannot rebuild
index with this lisence limit condition, right? Any workaround or
suggestions. We have clustered indexes so I still want to use rebuild index
function.
Regards
Bill
"Tibor Karaszi" wrote:
> Seems you are running MSDE, which has a max database size of 2GB data per database. You might want
> to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...
> > Thanks. I tried to add more space to the database but I got the error msg
> > below. What shall we do to resolve the issue?
> >
> > Server: Msg 1827, Level 16, State 2, Line 1
> > CREATE/ALTER DATABASE failed because the resulting cumulative database size
> > would exceed your licensed limit of 2048 MB per database.
> > Extending database by 1024.00 MB on disk 'Primary'.
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> > I have set the space for the primary filegroup the same
> >> > size as the size of the whole database.
> >>
> >> Whether that is enough or not is impossible to answer without more information. If the table has
> >> a
> >> clustered index, rebuilding is essentially copying the table to a new location and after the copy
> >> removing the old table. I.e., you need as much free space as the size of the table (plus some
> >> ore)
> >> on the file group where the table resides.
> >>
> >>
> >> > Do I still need to increase the size
> >> > or the size of the log file?
> >>
> >> Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data moves
> >> (as
> >> explained above) will be logged to the transaction log.
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >>
> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> >> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
> >> > Thank you very much. I have set the space for the primary filegroup the same
> >> > size as the size of the whole database. Do I still need to increase the size
> >> > or the size of the log file?
> >> >
> >> > Regards
> >> >
> >> > Bill
> >> >
> >> > "Mike Epprecht (SQL MVP)" wrote:
> >> >
> >> >> Hi
> >> >>
> >> >> Rather grow the DB to the correct size before you do such an operation.
> >> >>
> >> >> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
> >> >> while. During this time, the DBCC command will find the DB full and abort.
> >> >> The DBCC command does not know that more space will be available shortly.
> >> >>
> >> >> Regards
> >> >> Mike
> >> >>
> >> >> "Bill Little" wrote:
> >> >>
> >> >> > I got the error msg below while rebuilding index for a large table. I have
> >> >> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
> >> >> > set. There are plenty of space in the drive. Please help me to resolve this
> >> >> > isse.
> >> >> >
> >> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
> >> >> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
> >> >> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> >> >> > terminated.
> >> >> >
> >>
> >>
> >>
>
>|||You wouldn't get this error if you are on standard edition. Are you sure you work against the right
instance of SQL Server. Can you check the licensing?
SELECT @.@.VERSION
Also, INDEXDEFRAG work just fine on nc indexes...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
news:DFF76B92-66DD-47F9-9D63-EADEFFFFE9B2@.microsoft.com...
> Thank you very much. We are using standard edition. So we cannot rebuild
> index with this lisence limit condition, right? Any workaround or
> suggestions. We have clustered indexes so I still want to use rebuild index
> function.
> Regards
> Bill
> "Tibor Karaszi" wrote:
>> Seems you are running MSDE, which has a max database size of 2GB data per database. You might
>> want
>> to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
>> news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...
>> > Thanks. I tried to add more space to the database but I got the error msg
>> > below. What shall we do to resolve the issue?
>> >
>> > Server: Msg 1827, Level 16, State 2, Line 1
>> > CREATE/ALTER DATABASE failed because the resulting cumulative database size
>> > would exceed your licensed limit of 2048 MB per database.
>> > Extending database by 1024.00 MB on disk 'Primary'.
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> > I have set the space for the primary filegroup the same
>> >> > size as the size of the whole database.
>> >>
>> >> Whether that is enough or not is impossible to answer without more information. If the table
>> >> has
>> >> a
>> >> clustered index, rebuilding is essentially copying the table to a new location and after the
>> >> copy
>> >> removing the old table. I.e., you need as much free space as the size of the table (plus some
>> >> ore)
>> >> on the file group where the table resides.
>> >>
>> >>
>> >> > Do I still need to increase the size
>> >> > or the size of the log file?
>> >>
>> >> Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data
>> >> moves
>> >> (as
>> >> explained above) will be logged to the transaction log.
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> http://www.sqlug.se/
>> >>
>> >>
>> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
>> >> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
>> >> > Thank you very much. I have set the space for the primary filegroup the same
>> >> > size as the size of the whole database. Do I still need to increase the size
>> >> > or the size of the log file?
>> >> >
>> >> > Regards
>> >> >
>> >> > Bill
>> >> >
>> >> > "Mike Epprecht (SQL MVP)" wrote:
>> >> >
>> >> >> Hi
>> >> >>
>> >> >> Rather grow the DB to the correct size before you do such an operation.
>> >> >>
>> >> >> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
>> >> >> while. During this time, the DBCC command will find the DB full and abort.
>> >> >> The DBCC command does not know that more space will be available shortly.
>> >> >>
>> >> >> Regards
>> >> >> Mike
>> >> >>
>> >> >> "Bill Little" wrote:
>> >> >>
>> >> >> > I got the error msg below while rebuilding index for a large table. I have
>> >> >> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
>> >> >> > set. There are plenty of space in the drive. Please help me to resolve this
>> >> >> > isse.
>> >> >> >
>> >> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
>> >> >> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
>> >> >> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
>> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
>> >> >> > terminated.
>> >> >> >
>> >>
>> >>
>> >>
>>|||We have having the same exact problem. There is 30+ GB Free on the Drive
(Dynamic Disk, by the way). Database is set to unrestricted file growth.
Everything looks fine with the configuration of the sql database...however,
the database thinks there is no free space on the drive. Very wierd!!
Database is just shy of 5gb, but General Tab says 0.00Mb Free. Looks like
ill be calling MS soon!!
Dale
txskibum@.hotmail.com
"Tibor Karaszi" wrote:
> You wouldn't get this error if you are on standard edition. Are you sure you work against the right
> instance of SQL Server. Can you check the licensing?
> SELECT @.@.VERSION
> Also, INDEXDEFRAG work just fine on nc indexes...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> news:DFF76B92-66DD-47F9-9D63-EADEFFFFE9B2@.microsoft.com...
> > Thank you very much. We are using standard edition. So we cannot rebuild
> > index with this lisence limit condition, right? Any workaround or
> > suggestions. We have clustered indexes so I still want to use rebuild index
> > function.
> >
> > Regards
> >
> > Bill
> >
> > "Tibor Karaszi" wrote:
> >
> >> Seems you are running MSDE, which has a max database size of 2GB data per database. You might
> >> want
> >> to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
> >> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >>
> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> >> news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...
> >> > Thanks. I tried to add more space to the database but I got the error msg
> >> > below. What shall we do to resolve the issue?
> >> >
> >> > Server: Msg 1827, Level 16, State 2, Line 1
> >> > CREATE/ALTER DATABASE failed because the resulting cumulative database size
> >> > would exceed your licensed limit of 2048 MB per database.
> >> > Extending database by 1024.00 MB on disk 'Primary'.
> >> >
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> > I have set the space for the primary filegroup the same
> >> >> > size as the size of the whole database.
> >> >>
> >> >> Whether that is enough or not is impossible to answer without more information. If the table
> >> >> has
> >> >> a
> >> >> clustered index, rebuilding is essentially copying the table to a new location and after the
> >> >> copy
> >> >> removing the old table. I.e., you need as much free space as the size of the table (plus some
> >> >> ore)
> >> >> on the file group where the table resides.
> >> >>
> >> >>
> >> >> > Do I still need to increase the size
> >> >> > or the size of the log file?
> >> >>
> >> >> Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data
> >> >> moves
> >> >> (as
> >> >> explained above) will be logged to the transaction log.
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >> http://www.sqlug.se/
> >> >>
> >> >>
> >> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> >> >> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
> >> >> > Thank you very much. I have set the space for the primary filegroup the same
> >> >> > size as the size of the whole database. Do I still need to increase the size
> >> >> > or the size of the log file?
> >> >> >
> >> >> > Regards
> >> >> >
> >> >> > Bill
> >> >> >
> >> >> > "Mike Epprecht (SQL MVP)" wrote:
> >> >> >
> >> >> >> Hi
> >> >> >>
> >> >> >> Rather grow the DB to the correct size before you do such an operation.
> >> >> >>
> >> >> >> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
> >> >> >> while. During this time, the DBCC command will find the DB full and abort.
> >> >> >> The DBCC command does not know that more space will be available shortly.
> >> >> >>
> >> >> >> Regards
> >> >> >> Mike
> >> >> >>
> >> >> >> "Bill Little" wrote:
> >> >> >>
> >> >> >> > I got the error msg below while rebuilding index for a large table. I have
> >> >> >> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
> >> >> >> > set. There are plenty of space in the drive. Please help me to resolve this
> >> >> >> > isse.
> >> >> >> >
> >> >> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
> >> >> >> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
> >> >> >> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
> >> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> >> >> >> > terminated.
> >> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||> We have having the same exact problem.
Same as which problem? The thread has several issues discussed. Anyhow, you don't seem to get the
"MSDE" error message, as MSDE has max size of 2GB for data storage per database.
Are you getting error messages or are you only worried that EM say 0MB free? Remember that EM
reports free space *inside* the database files, without regard to free space on disk SQL Server
pre-allocates storage.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Dale" <Dale@.discussions.microsoft.com> wrote in message
news:922A620C-5AEE-4DD6-93BD-36645F72B881@.microsoft.com...
> We have having the same exact problem. There is 30+ GB Free on the Drive
> (Dynamic Disk, by the way). Database is set to unrestricted file growth.
> Everything looks fine with the configuration of the sql database...however,
> the database thinks there is no free space on the drive. Very wierd!!
> Database is just shy of 5gb, but General Tab says 0.00Mb Free. Looks like
> ill be calling MS soon!!
> Dale
> txskibum@.hotmail.com
>
> "Tibor Karaszi" wrote:
>> You wouldn't get this error if you are on standard edition. Are you sure you work against the
>> right
>> instance of SQL Server. Can you check the licensing?
>> SELECT @.@.VERSION
>> Also, INDEXDEFRAG work just fine on nc indexes...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
>> news:DFF76B92-66DD-47F9-9D63-EADEFFFFE9B2@.microsoft.com...
>> > Thank you very much. We are using standard edition. So we cannot rebuild
>> > index with this lisence limit condition, right? Any workaround or
>> > suggestions. We have clustered indexes so I still want to use rebuild index
>> > function.
>> >
>> > Regards
>> >
>> > Bill
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Seems you are running MSDE, which has a max database size of 2GB data per database. You might
>> >> want
>> >> to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
>> >> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> http://www.sqlug.se/
>> >>
>> >>
>> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
>> >> news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...
>> >> > Thanks. I tried to add more space to the database but I got the error msg
>> >> > below. What shall we do to resolve the issue?
>> >> >
>> >> > Server: Msg 1827, Level 16, State 2, Line 1
>> >> > CREATE/ALTER DATABASE failed because the resulting cumulative database size
>> >> > would exceed your licensed limit of 2048 MB per database.
>> >> > Extending database by 1024.00 MB on disk 'Primary'.
>> >> >
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> > I have set the space for the primary filegroup the same
>> >> >> > size as the size of the whole database.
>> >> >>
>> >> >> Whether that is enough or not is impossible to answer without more information. If the
>> >> >> table
>> >> >> has
>> >> >> a
>> >> >> clustered index, rebuilding is essentially copying the table to a new location and after
>> >> >> the
>> >> >> copy
>> >> >> removing the old table. I.e., you need as much free space as the size of the table (plus
>> >> >> some
>> >> >> ore)
>> >> >> on the file group where the table resides.
>> >> >>
>> >> >>
>> >> >> > Do I still need to increase the size
>> >> >> > or the size of the log file?
>> >> >>
>> >> >> Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data
>> >> >> moves
>> >> >> (as
>> >> >> explained above) will be logged to the transaction log.
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://www.solidqualitylearning.com/
>> >> >> http://www.sqlug.se/
>> >> >>
>> >> >>
>> >> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
>> >> >> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
>> >> >> > Thank you very much. I have set the space for the primary filegroup the same
>> >> >> > size as the size of the whole database. Do I still need to increase the size
>> >> >> > or the size of the log file?
>> >> >> >
>> >> >> > Regards
>> >> >> >
>> >> >> > Bill
>> >> >> >
>> >> >> > "Mike Epprecht (SQL MVP)" wrote:
>> >> >> >
>> >> >> >> Hi
>> >> >> >>
>> >> >> >> Rather grow the DB to the correct size before you do such an operation.
>> >> >> >>
>> >> >> >> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
>> >> >> >> while. During this time, the DBCC command will find the DB full and abort.
>> >> >> >> The DBCC command does not know that more space will be available shortly.
>> >> >> >>
>> >> >> >> Regards
>> >> >> >> Mike
>> >> >> >>
>> >> >> >> "Bill Little" wrote:
>> >> >> >>
>> >> >> >> > I got the error msg below while rebuilding index for a large table. I have
>> >> >> >> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
>> >> >> >> > set. There are plenty of space in the drive. Please help me to resolve this
>> >> >> >> > isse.
>> >> >> >> >
>> >> >> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
>> >> >> >> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
>> >> >> >> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
>> >> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
>> >> >> >> > terminated.
>> >> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||Please disregard. I posted to the wrong trend.
"Tibor Karaszi" wrote:
> > We have having the same exact problem.
> Same as which problem? The thread has several issues discussed. Anyhow, you don't seem to get the
> "MSDE" error message, as MSDE has max size of 2GB for data storage per database.
> Are you getting error messages or are you only worried that EM say 0MB free? Remember that EM
> reports free space *inside* the database files, without regard to free space on disk SQL Server
> pre-allocates storage.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Dale" <Dale@.discussions.microsoft.com> wrote in message
> news:922A620C-5AEE-4DD6-93BD-36645F72B881@.microsoft.com...
> > We have having the same exact problem. There is 30+ GB Free on the Drive
> > (Dynamic Disk, by the way). Database is set to unrestricted file growth.
> > Everything looks fine with the configuration of the sql database...however,
> > the database thinks there is no free space on the drive. Very wierd!!
> > Database is just shy of 5gb, but General Tab says 0.00Mb Free. Looks like
> > ill be calling MS soon!!
> >
> > Dale
> > txskibum@.hotmail.com
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> You wouldn't get this error if you are on standard edition. Are you sure you work against the
> >> right
> >> instance of SQL Server. Can you check the licensing?
> >> SELECT @.@.VERSION
> >>
> >> Also, INDEXDEFRAG work just fine on nc indexes...
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >>
> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> >> news:DFF76B92-66DD-47F9-9D63-EADEFFFFE9B2@.microsoft.com...
> >> > Thank you very much. We are using standard edition. So we cannot rebuild
> >> > index with this lisence limit condition, right? Any workaround or
> >> > suggestions. We have clustered indexes so I still want to use rebuild index
> >> > function.
> >> >
> >> > Regards
> >> >
> >> > Bill
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Seems you are running MSDE, which has a max database size of 2GB data per database. You might
> >> >> want
> >> >> to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
> >> >> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >> http://www.sqlug.se/
> >> >>
> >> >>
> >> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> >> >> news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...
> >> >> > Thanks. I tried to add more space to the database but I got the error msg
> >> >> > below. What shall we do to resolve the issue?
> >> >> >
> >> >> > Server: Msg 1827, Level 16, State 2, Line 1
> >> >> > CREATE/ALTER DATABASE failed because the resulting cumulative database size
> >> >> > would exceed your licensed limit of 2048 MB per database.
> >> >> > Extending database by 1024.00 MB on disk 'Primary'.
> >> >> >
> >> >> >
> >> >> > "Tibor Karaszi" wrote:
> >> >> >
> >> >> >> > I have set the space for the primary filegroup the same
> >> >> >> > size as the size of the whole database.
> >> >> >>
> >> >> >> Whether that is enough or not is impossible to answer without more information. If the
> >> >> >> table
> >> >> >> has
> >> >> >> a
> >> >> >> clustered index, rebuilding is essentially copying the table to a new location and after
> >> >> >> the
> >> >> >> copy
> >> >> >> removing the old table. I.e., you need as much free space as the size of the table (plus
> >> >> >> some
> >> >> >> ore)
> >> >> >> on the file group where the table resides.
> >> >> >>
> >> >> >>
> >> >> >> > Do I still need to increase the size
> >> >> >> > or the size of the log file?
> >> >> >>
> >> >> >> Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data
> >> >> >> moves
> >> >> >> (as
> >> >> >> explained above) will be logged to the transaction log.
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://www.solidqualitylearning.com/
> >> >> >> http://www.sqlug.se/
> >> >> >>
> >> >> >>
> >> >> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> >> >> >> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
> >> >> >> > Thank you very much. I have set the space for the primary filegroup the same
> >> >> >> > size as the size of the whole database. Do I still need to increase the size
> >> >> >> > or the size of the log file?
> >> >> >> >
> >> >> >> > Regards
> >> >> >> >
> >> >> >> > Bill
> >> >> >> >
> >> >> >> > "Mike Epprecht (SQL MVP)" wrote:
> >> >> >> >
> >> >> >> >> Hi
> >> >> >> >>
> >> >> >> >> Rather grow the DB to the correct size before you do such an operation.
> >> >> >> >>
> >> >> >> >> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
> >> >> >> >> while. During this time, the DBCC command will find the DB full and abort.
> >> >> >> >> The DBCC command does not know that more space will be available shortly.
> >> >> >> >>
> >> >> >> >> Regards
> >> >> >> >> Mike
> >> >> >> >>
> >> >> >> >> "Bill Little" wrote:
> >> >> >> >>
> >> >> >> >> > I got the error msg below while rebuilding index for a large table. I have
> >> >> >> >> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
> >> >> >> >> > set. There are plenty of space in the drive. Please help me to resolve this
> >> >> >> >> > isse.
> >> >> >> >> >
> >> >> >> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
> >> >> >> >> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
> >> >> >> >> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
> >> >> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> >> >> >> >> > terminated.
> >> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

Can not allocate space for rebuilding index

Friday, February 24, 2012

Can I Turn off Auto Execution of Report when all parameters have default values?

My situation....

I deploy a report in Report Server. Ther report has 5-10 parameters. When I design the report, I supply a default value for each parameter, similiar to the "Add a select all option" tutorials.

So I end up with defaults like "Select a Project Manager", "Select a Fiscal Year", etc... I use dynamic queries to read the parameters and insert the appropriate logic into the where string of my main dataset. If the user selects a value from my parameter lists, I append a clause to the SQL string, if they choose the default option, I ignore the parameter, and that effectively returns all of the records (like Select * from ....)

My Issue...

When I provide a default paramater for ALL of the parameters available, the report auto-executes when the user picks it from the folder, and isn;t given the opportunity to refine the default criteria I have provided.

My Question....

Is there any way to turn the autoexecution off?

Thanks for any help...

There is no method to turn off the auto execution of the report if all parameters have default values.

As you have probable already found out, you can populate all but one of the parameters to get the result you desire. But that makes the user have to supply a value of the parameter.

Can I trust Microsoft for FOR XML AUTO or FOR XML RAW ?

Hi,
I have been there a situation of an apprehension that Microsoft may issue
some patch or hotfix in future for SQL SERVER, that will change the shape of
XML results yielded by FOR XML AUTO or FOR XML RAW query.
Our query is going to be rigid in the application and the data would then be
passed through sensitive application that may crash if xml is not valid, we
have many types of xmls so we cannot create schema for each and every guy an
d
same with EXPLICIT.
Is this superstition valid that I shouldnt trust Microsoft here ?
Any input in this will sincerely be appreciated.
Fahad"Fahad Ashfaque" <FahadAshfaque@.discussions.microsoft.com> wrote in message
news:86415A5A-428D-48DF-9175-114E9E0030F7@.microsoft.com...
> Hi,
> I have been there a situation of an apprehension that Microsoft may issue
> some patch or hotfix in future for SQL SERVER, that will change the shape
> of
> XML results yielded by FOR XML AUTO or FOR XML RAW query.
FOR XML AUTO and FOR XML RAW results are pretty well defined, since SQL
2000. It's doubtful these would change in the current version of SQL Server
(2005) or espcially in 2000. A lot of people have already written
applications that use this functionality which would surely break if they
changed it. However the results are subject to change if your schema
changes which is probably a much more likely situation.

> Our query is going to be rigid in the application and the data would then
> be
> passed through sensitive application that may crash if xml is not valid,
> we
> have many types of xmls so we cannot create schema for each and every guy
> and
> same with EXPLICIT.
I would recommend using FOR XML PATH if you have SQL 2005. It's easy to
use, uses XPath notation to define your XML, and you can easily update or
modify it if your schema changes.

> Is this superstition valid that I shouldnt trust Microsoft here ?
Like I said, if they change this in a Service Pack (and it's been around
since SQL 2000), they are going to have a lot of unhappy customers who have
invested a lot of time and money in building applications to use it. OTOH,
one could say the same thing about COM and VB 6 :) At any rate I wouldn't
worry too much about it until at least the next major version release.|||"Fahad Ashfaque" <FahadAshfaque@.discussions.microsoft.com> wrote in message
news:86415A5A-428D-48DF-9175-114E9E0030F7@.microsoft.com...
> Hi,
> I have been there a situation of an apprehension that Microsoft may issue
> some patch or hotfix in future for SQL SERVER, that will change the shape
> of
> XML results yielded by FOR XML AUTO or FOR XML RAW query.
>
> Our query is going to be rigid in the application and the data would then
> be
> passed through sensitive application that may crash if xml is not valid,
> we
> have many types of xmls so we cannot create schema for each and every guy
> and
> same with EXPLICIT.
> Is this superstition valid that I shouldnt trust Microsoft here ?
>
> Any input in this will sincerely be appreciated.
>
> Fahad
>
I know Microsoft occasionally make breaking changes but normally they go the
other way, everything has to be backwards compatible even to the detriment
of the latest version.
Have you any specific reason for worrying that the FOR XML statements are
going to be modified?
Joe Fawcett (MVP - XML)
http://joe.fawcett.name|||This seems like a strange thing to worry about. Aside from the basic issue
of how else would you represent a simple result set as XML, what motivation
would there be to ever changing this? What would happen if Microsoft did
this? There would be thousands of blog entries, newsgroup postings, and
news articles vilifying Microsoft for sticking it to the little guy, the
stock would drop, hundreds of employees would have to change their
retirement plans, and the SQL team would be hated by the rest of Microsoft.
Contrast this with the alternative of not changing code that works well
already and it makes changing the format a poor alternative.
Reading between the lines of your post makes me worry that you plan to do
some kind of roll your own text parsing instead of using a real XML parser.
If this is true then there could be an issue. While the results will be
semantically the same, it's hard to guarantee character for character
compatibility forever. If you want to do your own parsing you are better
off using something like comma separated values.
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
"Fahad Ashfaque" <FahadAshfaque@.discussions.microsoft.com> wrote in message
news:86415A5A-428D-48DF-9175-114E9E0030F7@.microsoft.com...
> Hi,
> I have been there a situation of an apprehension that Microsoft may issue
> some patch or hotfix in future for SQL SERVER, that will change the shape
> of
> XML results yielded by FOR XML AUTO or FOR XML RAW query.
>
> Our query is going to be rigid in the application and the data would then
> be
> passed through sensitive application that may crash if xml is not valid,
> we
> have many types of xmls so we cannot create schema for each and every guy
> and
> same with EXPLICIT.
> Is this superstition valid that I shouldnt trust Microsoft here ?
>
> Any input in this will sincerely be appreciated.
>
> Fahad
>|||"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:OpxtSS7RHHA.4832@.TK2MSFTNGP03.phx.gbl...
> Reading between the lines of your post makes me worry that you plan to do
> some kind of roll your own text parsing instead of using a real XML
> parser. If this is true then there could be an issue. While the results
> will be semantically the same, it's hard to guarantee character for
> character compatibility forever. If you want to do your own parsing you
> are better off using something like comma separated values.
Do you see this being a problem if the OP is using Unicode?|||Could you expand a bit on what you mean? I'm not sure how Unicode fits into
the question.
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
"Mike C#" <xyz@.xyz.com> wrote in message
news:%238jjscTSHHA.5060@.TK2MSFTNGP06.phx.gbl...
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:OpxtSS7RHHA.4832@.TK2MSFTNGP03.phx.gbl...
> Do you see this being a problem if the OP is using Unicode?
>|||"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:O8Ac8EUSHHA.3592@.TK2MSFTNGP06.phx.gbl...
> Could you expand a bit on what you mean? I'm not sure how Unicode fits
> into the question.
"While the results will be semantically the same, it's hard to guarantee
character for character compatibility forever."
I assumed you were talking about character sets, code pages, etc., in your
"character for character compatibility" statement. Did I misunderstand?|||No, I was talking about white space and namespace prefixes, etc. These are
the things that break when people write their own parsers instead of using a
full XML parser.
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
"Mike C#" <xyz@.xyz.com> wrote in message
news:uvLHAwWSHHA.3948@.TK2MSFTNGP05.phx.gbl...
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:O8Ac8EUSHHA.3592@.TK2MSFTNGP06.phx.gbl...
> "While the results will be semantically the same, it's hard to guarantee
> character for character compatibility forever."
> I assumed you were talking about character sets, code pages, etc., in your
> "character for character compatibility" statement. Did I misunderstand?
>|||As the program manager still responsible for FOR XML, we are not planning on
changing the XML infoset generated by FOR XML RAW, EXPLICIT or PATH (unless
we guard it by a backwards-compatibility flag and will document the changed
behaviour ahead of time).
Between SQL Server 2000 and 2005 we did a few changes, mostly along the
lines of entitization changes. We had one breaking change in how FOR XML
AUTO dealt with subqueries and views that caused some pain, but was actually
a bug fix in the design of FOR XML AUTO's heuristics.
So if you can be more specific of what changes you fear, I can give you a
more definitive answer.
Changes that may happen in the future include addition or removal or certain
namespace declarations, changing order of attributes. But we are not
planning on changing the resultshapes of the different modes.
Best regards
Michael
"Fahad Ashfaque" <FahadAshfaque@.discussions.microsoft.com> wrote in message
news:86415A5A-428D-48DF-9175-114E9E0030F7@.microsoft.com...
> Hi,
> I have been there a situation of an apprehension that Microsoft may issue
> some patch or hotfix in future for SQL SERVER, that will change the shape
> of
> XML results yielded by FOR XML AUTO or FOR XML RAW query.
>
> Our query is going to be rigid in the application and the data would then
> be
> passed through sensitive application that may crash if xml is not valid,
> we
> have many types of xmls so we cannot create schema for each and every guy
> and
> same with EXPLICIT.
> Is this superstition valid that I shouldnt trust Microsoft here ?
>
> Any input in this will sincerely be appreciated.
>
> Fahad
>|||Thankyou very much for your interest in helping me Michael and other guys to
o.
Well I am getting simple shaped XML from a single table which we then
transform using XSL. The changes which I am anticipating include
Adding any namespace to data element that will make our XPath expressions
useless. We will NEVER be able to update XPaths and issue new release of our
software once it is burnt into the hardware.
Changes in the source tree, again it will require us to update XSL.
"Michael Rys [MSFT]" wrote:

> As the program manager still responsible for FOR XML, we are not planning
on
> changing the XML infoset generated by FOR XML RAW, EXPLICIT or PATH (unles
s
> we guard it by a backwards-compatibility flag and will document the change
d
> behaviour ahead of time).
> Between SQL Server 2000 and 2005 we did a few changes, mostly along the
> lines of entitization changes. We had one breaking change in how FOR XML
> AUTO dealt with subqueries and views that caused some pain, but was actual
ly
> a bug fix in the design of FOR XML AUTO's heuristics.
> So if you can be more specific of what changes you fear, I can give you a
> more definitive answer.
> Changes that may happen in the future include addition or removal or certa
in
> namespace declarations, changing order of attributes. But we are not
> planning on changing the resultshapes of the different modes.
> Best regards
> Michael
> "Fahad Ashfaque" <FahadAshfaque@.discussions.microsoft.com> wrote in messag
e
> news:86415A5A-428D-48DF-9175-114E9E0030F7@.microsoft.com...
>
>

Can I trust Microsoft for FOR XML AUTO or FOR XML RAW ?

At any rate I wouldn't
worry too much about it until at least the next major version release.
|||"Fahad Ashfaque" <FahadAshfaque@.discussions.microsoft.com> wrote in message
news:86415A5A-428D-48DF-9175-114E9E0030F7@.microsoft.com...
> Hi,
> I have been there a situation of an apprehension that Microsoft may issue
> some patch or hotfix in future for SQL SERVER, that will change the shape
> of
> XML results yielded by FOR XML AUTO or FOR XML RAW query.
>
> Our query is going to be rigid in the application and the data would then
> be
> passed through sensitive application that may crash if xml is not valid,
> we
> have many types of xmls so we cannot create schema for each and every guy
> and
> same with EXPLICIT.
> Is this superstition valid that I shouldnt trust Microsoft here ?
>
> Any input in this will sincerely be appreciated.
>
> Fahad
>
I know Microsoft occasionally make breaking changes but normally they go the
other way, everything has to be backwards compatible even to the detriment
of the latest version.
Have you any specific reason for worrying that the FOR XML statements are
going to be modified?

Joe Fawcett (MVP - XML)
http://joe.fawcett.name
|||This seems like a strange thing to worry about. Aside from the basic issue
of how else would you represent a simple result set as XML, what motivation
would there be to ever changing this? What would happen if Microsoft did
this? There would be thousands of blog entries, newsgroup postings, and
news articles vilifying Microsoft for sticking it to the little guy, the
stock would drop, hundreds of employees would have to change their
retirement plans, and the SQL team would be hated by the rest of Microsoft.
Contrast this with the alternative of not changing code that works well
already and it makes changing the format a poor alternative.
Reading between the lines of your post makes me worry that you plan to do
some kind of roll your own text parsing instead of using a real XML parser.
If this is true then there could be an issue. While the results will be
semantically the same, it's hard to guarantee character for character
compatibility forever. If you want to do your own parsing you are better
off using something like comma separated values.
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
"Fahad Ashfaque" <FahadAshfaque@.discussions.microsoft.com> wrote in message
news:86415A5A-428D-48DF-9175-114E9E0030F7@.microsoft.com...
> Hi,
> I have been there a situation of an apprehension that Microsoft may issue
> some patch or hotfix in future for SQL SERVER, that will change the shape
> of
> XML results yielded by FOR XML AUTO or FOR XML RAW query.
>
> Our query is going to be rigid in the application and the data would then
> be
> passed through sensitive application that may crash if xml is not valid,
> we
> have many types of xmls so we cannot create schema for each and every guy
> and
> same with EXPLICIT.
> Is this superstition valid that I shouldnt trust Microsoft here ?
>
> Any input in this will sincerely be appreciated.
>
> Fahad
>
|||"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:OpxtSS7RHHA.4832@.TK2MSFTNGP03.phx.gbl...
> Reading between the lines of your post makes me worry that you plan to do
> some kind of roll your own text parsing instead of using a real XML
> parser. If this is true then there could be an issue. While the results
> will be semantically the same, it's hard to guarantee character for
> character compatibility forever. If you want to do your own parsing you
> are better off using something like comma separated values.
Do you see this being a problem if the OP is using Unicode?
|||Could you expand a bit on what you mean? I'm not sure how Unicode fits into
the question.
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
"Mike C#" <xyz@.xyz.com> wrote in message
news:%238jjscTSHHA.5060@.TK2MSFTNGP06.phx.gbl...
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:OpxtSS7RHHA.4832@.TK2MSFTNGP03.phx.gbl...
> Do you see this being a problem if the OP is using Unicode?
>
|||"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:O8Ac8EUSHHA.3592@.TK2MSFTNGP06.phx.gbl...
> Could you expand a bit on what you mean? I'm not sure how Unicode fits
> into the question.
"While the results will be semantically the same, it's hard to guarantee
character for character compatibility forever."
I assumed you were talking about character sets, code pages, etc., in your
"character for character compatibility" statement. Did I misunderstand?
|||No, I was talking about white space and namespace prefixes, etc. These are
the things that break when people write their own parsers instead of using a
full XML parser.
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
"Mike C#" <xyz@.xyz.com> wrote in message
news:uvLHAwWSHHA.3948@.TK2MSFTNGP05.phx.gbl...
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:O8Ac8EUSHHA.3592@.TK2MSFTNGP06.phx.gbl...
> "While the results will be semantically the same, it's hard to guarantee
> character for character compatibility forever."
> I assumed you were talking about character sets, code pages, etc., in your
> "character for character compatibility" statement. Did I misunderstand?
>
|||As the program manager still responsible for FOR XML, we are not planning on
changing the XML infoset generated by FOR XML RAW, EXPLICIT or PATH (unless
we guard it by a backwards-compatibility flag and will document the changed
behaviour ahead of time).
Between SQL Server 2000 and 2005 we did a few changes, mostly along the
lines of entitization changes. We had one breaking change in how FOR XML
AUTO dealt with subqueries and views that caused some pain, but was actually
a bug fix in the design of FOR XML AUTO's heuristics.
So if you can be more specific of what changes you fear, I can give you a
more definitive answer.
Changes that may happen in the future include addition or removal or certain
namespace declarations, changing order of attributes. But we are not
planning on changing the resultshapes of the different modes.
Best regards
Michael
"Fahad Ashfaque" <FahadAshfaque@.discussions.microsoft.com> wrote in message
news:86415A5A-428D-48DF-9175-114E9E0030F7@.microsoft.com...
> Hi,
> I have been there a situation of an apprehension that Microsoft may issue
> some patch or hotfix in future for SQL SERVER, that will change the shape
> of
> XML results yielded by FOR XML AUTO or FOR XML RAW query.
>
> Our query is going to be rigid in the application and the data would then
> be
> passed through sensitive application that may crash if xml is not valid,
> we
> have many types of xmls so we cannot create schema for each and every guy
> and
> same with EXPLICIT.
> Is this superstition valid that I shouldnt trust Microsoft here ?
>
> Any input in this will sincerely be appreciated.
>
> Fahad
>
|||Thankyou very much for your interest in helping me Michael and other guys too.
Well I am getting simple shaped XML from a single table which we then
transform using XSL. The changes which I am anticipating include
Adding any namespace to data element that will make our XPath expressions
useless. We will NEVER be able to update XPaths and issue new release of our
software once it is burnt into the hardware.
Changes in the source tree, again it will require us to update XSL.
"Michael Rys [MSFT]" wrote:

> As the program manager still responsible for FOR XML, we are not planning on
> changing the XML infoset generated by FOR XML RAW, EXPLICIT or PATH (unless
> we guard it by a backwards-compatibility flag and will document the changed
> behaviour ahead of time).
> Between SQL Server 2000 and 2005 we did a few changes, mostly along the
> lines of entitization changes. We had one breaking change in how FOR XML
> AUTO dealt with subqueries and views that caused some pain, but was actually
> a bug fix in the design of FOR XML AUTO's heuristics.
> So if you can be more specific of what changes you fear, I can give you a
> more definitive answer.
> Changes that may happen in the future include addition or removal or certain
> namespace declarations, changing order of attributes. But we are not
> planning on changing the resultshapes of the different modes.
> Best regards
> Michael
> "Fahad Ashfaque" <FahadAshfaque@.discussions.microsoft.com> wrote in message
> news:86415A5A-428D-48DF-9175-114E9E0030F7@.microsoft.com...
>
>

Friday, February 10, 2012

Can I just get a string back from SQL Server with "FOR XML AUTO, ELEMENTS"?

I keep getting back some hexadecimal garbage data rather than the nice XML I
see in the Query Analyzer.
What gives?
WTH
Are you using OLEDB or ADO?
You should use the command STREAM object instead of the recordset. See the
documentation for some example code.
Best regards
Michael
"WTH" <nospam@.spamtheman.com> wrote in message
news:eUPcJDRwEHA.356@.TK2MSFTNGP10.phx.gbl...
>I keep getting back some hexadecimal garbage data rather than the nice XML
>I
> see in the Query Analyzer.
> What gives?
> WTH
>
|||"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OFN0lnRwEHA.3668@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Are you using OLEDB or ADO?
> You should use the command STREAM object instead of the recordset. See the
> documentation for some example code.
> Best regards
> Michael
> "WTH" <nospam@.spamtheman.com> wrote in message
> news:eUPcJDRwEHA.356@.TK2MSFTNGP10.phx.gbl...
XML[vbcol=seagreen]
I read that using that method results in differing results depending upon
whether a remote server returns the results or a local server does, no
thanks.
I just decided to pull off the "FOR XML AUTO, ELEMENTS" and simply use a
CXMLAccessor OleDB Accessor class and call ::GetXMLRowData instead.
WTH
|||> I read that using that method results in differing results depending upon
> whether a remote server returns the results or a local server does, no
> thanks.
There is no problem that I am aware off regarding connecting to a local or
remote server using the command stream object and FOR XML. Do you have a
reference to this?
However, if your other solution works, that's fine.
Best regards
Michael
"WTH" <nospam@.spamtheman.com> wrote in message
news:emhf28RwEHA.3808@.TK2MSFTNGP15.phx.gbl...
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OFN0lnRwEHA.3668@.tk2msftngp13.phx.gbl...
> XML
> I read that using that method results in differing results depending upon
> whether a remote server returns the results or a local server does, no
> thanks.
> I just decided to pull off the "FOR XML AUTO, ELEMENTS" and simply use a
> CXMLAccessor OleDB Accessor class and call ::GetXMLRowData instead.
> WTH
>

Tuesday, March 20, 2012

Can not allocate space for rebuilding index

Can not allocate space for rebuilding index

Can not allocate space for rebuilding index

Friday, February 24, 2012

Can I Turn off Auto Execution of Report when all parameters have default values?

Can I trust Microsoft for FOR XML AUTO or FOR XML RAW ?

Can I trust Microsoft for FOR XML AUTO or FOR XML RAW ?

Friday, February 10, 2012

Can I just get a string back from SQL Server with "FOR XML AUTO, ELEMENTS"?

install SQL on WinXP

Blog Archive

About Me