Cant take Backups

Hi
I have Database in SQL-Server2000 size is 1.37 GB i am tryn to take backup followin are the problems:-
1. Maintainence Plan for Backup Fails for DB
2. Jobs fails for Backup

--> I goto SQL Server Logs Error message is Operating system error 23(Data error (cyclic redundancy check).)

--> Now i am tryin Backup using command :-
Backup Database 'DB1' To Disk ='Path' followin error occurs:-
"Nonrecoverable I/O error occurred on file"

--> Now i am doing back-up directly from Enterprise Manager following error message occurs same error comes

Whats surprising Jobs, maintainence plan, Backup for other database is working perfectly, i checked HDD doesnt have any bad blocks etc. i have even changed path for Backups but still only for these database it gives problem

With Regards

Have you issued a DBCC CHECKDB against the database?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

Can't shrink system tempdb

The shrink file dialogue box indicates the system tempdb has a current size of

1091MB, and a space used of 19MB. I’m assuming I should be able to reduce the

size to 19MB? Regardless of what shrink action I invoke it still maintains a size

of 1091MB. Is it possible to reduce the size of this system DB? Thanks to everyone

for being there to help.

SQL 2000 SP4

Hi, take a look here: http://support.microsoft.com/Default.aspx?id=307487

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks, that's exactly what I needed. I'm having difficulty finding a link

that demonstrates how to relocate system DB's, like the master DB.

Do you have a link for that also?

SQL 2000 SP4

|||Sure, --> http://support.microsoft.com/default.aspx?scid=kb;en-us;224071

HTH;, Jens Suessmeyer.

http://www.sqlserver2005.de

Can't shrink DB

Hi,

The DB size (MDF) is getting too big, sp_spaceused returns:

reserved data index unused

1418656 KB 250168 KB 1408 KB 1167080 KB

I've tried both SHRINKFILE and SHRINKDATABASE with all types of options.

The DB designed on SQL 2000 and ported to SQL 2005 EXPRESS, I've laso used DBCC UPDATEUSAGE.

Any ideas?

Thanks,

YP

Hi,

if the size is the initial size of the database file, you won′t be able to shrink it any further. WHats is the initial size ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

The initail size was 120MB.

Thanks,

YP

|||

BTW...... what the command u give to shrinkfil and what the error u gets ..... if any

Madhu

|||

I've tried it with all options:

DBCC SHRINKFILE (FILE_NAME)

also I've tried to set target size, with both notruncate/truncateonly.

I didn't get error

YP

|||

Finally managed to shrink the DB,

I've noticed that every DBCC SHRINKFILE command shrinked the DB in about 4-5%,

so I just executed the command few 10s of time and it shrank to ~200MB.

Thanks,

YP

Can't shrink a Transaction Log File

I backed up my log file and attempted to shrink it with DBCC
SHRINKFILE. The command executes but leaves the transaction log at
it's current size. I know that an uncommited tansaction can cause this
to occur.
a) How do I test for this?
b)How do I get rid of it?
Regards
John L
Hi Gemel,
Try to backup your transaction log again and then try to shrink the file.
If its possible to loose youre trans log info(i guess you dont) change the
recovery model to simple(you'll loose your log info), shrink your tlog to the
desired size, then change recovery model to full and finally backup youre
database.
Hope that this can help you.
Regards
"gemel" wrote:

> I backed up my log file and attempted to shrink it with DBCC
> SHRINKFILE. The command executes but leaves the transaction log at
> it's current size. I know that an uncommited tansaction can cause this
> to occur.
> a) How do I test for this?
> b)How do I get rid of it?
> Regards
> John L
>
|||If you have a long running open transaction that could prevent the log from
shrinking..
DBCC opentran will show you the oldest transaction still in transaction
state.
You might also try
DBCC SQLPERF(logspace) to see how much space in the log is used
also
DBCC loginfo ( google on this to find more details.)
hope this helps
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"gemel" <jonel@.glenavon1.demon.co.uk> wrote in message
news:gdhm31tl8rs5t2804ibhc9onv36lee5evb@.4ax.com...
>I backed up my log file and attempted to shrink it with DBCC
> SHRINKFILE. The command executes but leaves the transaction log at
> it's current size. I know that an uncommited tansaction can cause this
> to occur.
> a) How do I test for this?
> b)How do I get rid of it?
> Regards
> John L

Can't shrink a Transaction Log File

I backed up my log file and attempted to shrink it with DBCC
SHRINKFILE. The command executes but leaves the transaction log at
it's current size. I know that an uncommited tansaction can cause this
to occur.
a) How do I test for this?
b)How do I get rid of it?
Regards
John LHi Gemel,
Try to backup your transaction log again and then try to shrink the file.
If its possible to loose youre trans log info(i guess you dont) change the
recovery model to simple(you'll loose your log info), shrink your tlog to th
e
desired size, then change recovery model to full and finally backup youre
database.
Hope that this can help you.
Regards
"gemel" wrote:

> I backed up my log file and attempted to shrink it with DBCC
> SHRINKFILE. The command executes but leaves the transaction log at
> it's current size. I know that an uncommited tansaction can cause this
> to occur.
> a) How do I test for this?
> b)How do I get rid of it?
> Regards
> John L
>|||If you have a long running open transaction that could prevent the log from
shrinking..
DBCC opentran will show you the oldest transaction still in transaction
state.
You might also try
DBCC SQLPERF(logspace) to see how much space in the log is used
also
DBCC loginfo ( google on this to find more details.)
hope this helps
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"gemel" <jonel@.glenavon1.demon.co.uk> wrote in message
news:gdhm31tl8rs5t2804ibhc9onv36lee5evb@.
4ax.com...
>I backed up my log file and attempted to shrink it with DBCC
> SHRINKFILE. The command executes but leaves the transaction log at
> it's current size. I know that an uncommited tansaction can cause this
> to occur.
> a) How do I test for this?
> b)How do I get rid of it?
> Regards
> John Lsql

can't shrink a log file

actually i have a db file size of 400MB and it's log file about 4.5GB, i rea
d
all about backup log and shrink the file, and the problems is every virtual
log file has an status of active, some are really old ( 2002 ), how can i
turn off the virtual files and left only one active (the last)Do you have any uncommitted transactions? What does DBCC OPENTRAN output?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fidel Ramrez" <Fidel Ramrez@.discussions.microsoft.com> wrote in message
news:A2AE904A-CE8C-48F1-9852-23F0033C56AD@.microsoft.com...
> actually i have a db file size of 400MB and it's log file about 4.5GB, i
read
> all about backup log and shrink the file, and the problems is every
virtual
> log file has an status of active, some are really old ( 2002 ), how can i
> turn off the virtual files and left only one active (the last)|||this is the message result
Información de transacción para la base de datos 'SII'.
Información de transacción duplicada:
LSN distribuido más antiguo : (4796:432:1)
LSN no distribuido más antiguo : (4796:457:1)
Ejecución de DBCC completada. Si hay mensajes de error, consulte al
administrador del sistema.
transaction information for db SII (my db)
transaction information duplicated
LSN older distributed : (4796:432:1)
LSN older not distributed : (4796:457:1)
DBCC execution completed. If there are error messages, contact system
administrator.
?What's next?
"Paul S Randal [MS]" wrote:

> Do you have any uncommitted transactions? What does DBCC OPENTRAN output?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Fidel Ram_rez" <Fidel Ramrez@.discussions.microsoft.com> wrote in message
> news:A2AE904A-CE8C-48F1-9852-23F0033C56AD@.microsoft.com...
> read
> virtual
>
>|||Looks like you're running SQL Server 7.0 - is that correct? If so, DBCC
OPENTRAN doesn't tell you the start time of the oldest transaction but I
suspect you've got an uncommitted transaction that's preventing the log from
being truncated. Your best bet is to contact PSS
(http://support.microsoft.com) for help and guidance.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fidel Ramirez" <Fidel Ramirez@.discussions.microsoft.com> wrote in message
news:54DEF4D8-CA4B-497C-A998-FE7E0FB59346@.microsoft.com...[vbcol=seagreen]
> this is the message result
> Informacin de transaccin para la base de datos 'SII'.
> Informacin de transaccin duplicada:
> LSN distribuido ms antiguo : (4796:432:1)
> LSN no distribuido ms antiguo : (4796:457:1)
> Ejecucin de DBCC completada. Si hay mensajes de error, consulte al
> administrador del sistema.
>
> transaction information for db SII (my db)
> transaction information duplicated
> LSN older distributed : (4796:432:1)
> LSN older not distributed : (4796:457:1)
> DBCC execution completed. If there are error messages, contact system
> administrator.
> What's next?
>
> "Paul S Randal [MS]" wrote:
>
output?[vbcol=seagreen]
rights.[vbcol=seagreen]
message[vbcol=seagreen]
i[vbcol=seagreen]
can i[vbcol=seagreen]|||thnks a lot i'll do it
"Paul S Randal [MS]" wrote:

> Looks like you're running SQL Server 7.0 - is that correct? If so, DBCC
> OPENTRAN doesn't tell you the start time of the oldest transaction but I
> suspect you've got an uncommitted transaction that's preventing the log fr
om
> being truncated. Your best bet is to contact PSS
> (http://support.microsoft.com) for help and guidance.
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Fidel Ramirez" <Fidel Ramirez@.discussions.microsoft.com> wrote in message
> news:54DEF4D8-CA4B-497C-A998-FE7E0FB59346@.microsoft.com...
> output?
> rights.
> message
> i
> can i
>
>

can't shrink a log file

actually i have a db file size of 400MB and it's log file about 4.5GB, i read
all about backup log and shrink the file, and the problems is every virtual
log file has an status of active, some are really old ( 2002 ), how can i
turn off the virtual files and left only one active (the last)
Do you have any uncommitted transactions? What does DBCC OPENTRAN output?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fidel Ramrez" <Fidel Ramrez@.discussions.microsoft.com> wrote in message
news:A2AE904A-CE8C-48F1-9852-23F0033C56AD@.microsoft.com...
> actually i have a db file size of 400MB and it's log file about 4.5GB, i
read
> all about backup log and shrink the file, and the problems is every
virtual
> log file has an status of active, some are really old ( 2002 ), how can i
> turn off the virtual files and left only one active (the last)
|||this is the message result
Información de transacción para la base de datos 'SII'.
Información de transacción duplicada:
LSN distribuido más antiguo : (4796:432:1)
LSN no distribuido más antiguo : (4796:457:1)
Ejecución de DBCC completada. Si hay mensajes de error, consulte al
administrador del sistema.
transaction information for db SII (my db)
transaction information duplicated
LSN older distributed : (4796:432:1)
LSN older not distributed : (4796:457:1)
DBCC execution completed. If there are error messages, contact system
administrator.
?What's next?
"Paul S Randal [MS]" wrote:

> Do you have any uncommitted transactions? What does DBCC OPENTRAN output?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Fidel RamXrez" <Fidel Ramrez@.discussions.microsoft.com> wrote in message
> news:A2AE904A-CE8C-48F1-9852-23F0033C56AD@.microsoft.com...
> read
> virtual
>
>
|||Looks like you're running SQL Server 7.0 - is that correct? If so, DBCC
OPENTRAN doesn't tell you the start time of the oldest transaction but I
suspect you've got an uncommitted transaction that's preventing the log from
being truncated. Your best bet is to contact PSS
(http://support.microsoft.com) for help and guidance.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fidel Ramirez" <Fidel Ramirez@.discussions.microsoft.com> wrote in message
news:54DEF4D8-CA4B-497C-A998-FE7E0FB59346@.microsoft.com...[vbcol=seagreen]
> this is the message result
> Informacin de transaccin para la base de datos 'SII'.
> Informacin de transaccin duplicada:
> LSN distribuido ms antiguo : (4796:432:1)
> LSN no distribuido ms antiguo : (4796:457:1)
> Ejecucin de DBCC completada. Si hay mensajes de error, consulte al
> administrador del sistema.
>
> transaction information for db SII (my db)
> transaction information duplicated
> LSN older distributed : (4796:432:1)
> LSN older not distributed : (4796:457:1)
> DBCC execution completed. If there are error messages, contact system
> administrator.
> What's next?
>
> "Paul S Randal [MS]" wrote:
output?[vbcol=seagreen]
rights.[vbcol=seagreen]
message[vbcol=seagreen]
i[vbcol=seagreen]
can i[vbcol=seagreen]
|||thnks a lot i'll do it
"Paul S Randal [MS]" wrote:

> Looks like you're running SQL Server 7.0 - is that correct? If so, DBCC
> OPENTRAN doesn't tell you the start time of the oldest transaction but I
> suspect you've got an uncommitted transaction that's preventing the log from
> being truncated. Your best bet is to contact PSS
> (http://support.microsoft.com) for help and guidance.
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Fidel Ramirez" <Fidel Ramirez@.discussions.microsoft.com> wrote in message
> news:54DEF4D8-CA4B-497C-A998-FE7E0FB59346@.microsoft.com...
> output?
> rights.
> message
> i
> can i
>
>

can't shrink a log file

actually i have a db file size of 400MB and it's log file about 4.5GB, i read
all about backup log and shrink the file, and the problems is every virtual
log file has an status of active, some are really old ( 2002 ), how can i
turn off the virtual files and left only one active (the last)Do you have any uncommitted transactions? What does DBCC OPENTRAN output?
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fidel Ramírez" <Fidel Ramrez@.discussions.microsoft.com> wrote in message
news:A2AE904A-CE8C-48F1-9852-23F0033C56AD@.microsoft.com...
> actually i have a db file size of 400MB and it's log file about 4.5GB, i
read
> all about backup log and shrink the file, and the problems is every
virtual
> log file has an status of active, some are really old ( 2002 ), how can i
> turn off the virtual files and left only one active (the last)|||this is the message result
Información de transacción para la base de datos 'SII'.
Información de transacción duplicada:
LSN distribuido más antiguo : (4796:432:1)
LSN no distribuido más antiguo : (4796:457:1)
Ejecución de DBCC completada. Si hay mensajes de error, consulte al
administrador del sistema.
transaction information for db SII (my db)
transaction information duplicated
LSN older distributed : (4796:432:1)
LSN older not distributed : (4796:457:1)
DBCC execution completed. If there are error messages, contact system
administrator.
¿What's next?
"Paul S Randal [MS]" wrote:
> Do you have any uncommitted transactions? What does DBCC OPENTRAN output?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Fidel Ramírez" <Fidel Ramrez@.discussions.microsoft.com> wrote in message
> news:A2AE904A-CE8C-48F1-9852-23F0033C56AD@.microsoft.com...
> > actually i have a db file size of 400MB and it's log file about 4.5GB, i
> read
> > all about backup log and shrink the file, and the problems is every
> virtual
> > log file has an status of active, some are really old ( 2002 ), how can i
> > turn off the virtual files and left only one active (the last)
>
>|||Looks like you're running SQL Server 7.0 - is that correct? If so, DBCC
OPENTRAN doesn't tell you the start time of the oldest transaction but I
suspect you've got an uncommitted transaction that's preventing the log from
being truncated. Your best bet is to contact PSS
(http://support.microsoft.com) for help and guidance.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fidel Ramirez" <Fidel Ramirez@.discussions.microsoft.com> wrote in message
news:54DEF4D8-CA4B-497C-A998-FE7E0FB59346@.microsoft.com...
> this is the message result
> Información de transacción para la base de datos 'SII'.
> Información de transacción duplicada:
> LSN distribuido más antiguo : (4796:432:1)
> LSN no distribuido más antiguo : (4796:457:1)
> Ejecución de DBCC completada. Si hay mensajes de error, consulte al
> administrador del sistema.
>
> transaction information for db SII (my db)
> transaction information duplicated
> LSN older distributed : (4796:432:1)
> LSN older not distributed : (4796:457:1)
> DBCC execution completed. If there are error messages, contact system
> administrator.
> ¿What's next?
>
> "Paul S Randal [MS]" wrote:
> > Do you have any uncommitted transactions? What does DBCC OPENTRAN
output?
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "Fidel Ramírez" <Fidel Ramrez@.discussions.microsoft.com> wrote in
message
> > news:A2AE904A-CE8C-48F1-9852-23F0033C56AD@.microsoft.com...
> > > actually i have a db file size of 400MB and it's log file about 4.5GB,
i
> > read
> > > all about backup log and shrink the file, and the problems is every
> > virtual
> > > log file has an status of active, some are really old ( 2002 ), how
can i
> > > turn off the virtual files and left only one active (the last)
> >
> >
> >|||thnks a lot i'll do it
"Paul S Randal [MS]" wrote:
> Looks like you're running SQL Server 7.0 - is that correct? If so, DBCC
> OPENTRAN doesn't tell you the start time of the oldest transaction but I
> suspect you've got an uncommitted transaction that's preventing the log from
> being truncated. Your best bet is to contact PSS
> (http://support.microsoft.com) for help and guidance.
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Fidel Ramirez" <Fidel Ramirez@.discussions.microsoft.com> wrote in message
> news:54DEF4D8-CA4B-497C-A998-FE7E0FB59346@.microsoft.com...
> > this is the message result
> >
> > Información de transacción para la base de datos 'SII'.
> >
> > Información de transacción duplicada:
> > LSN distribuido más antiguo : (4796:432:1)
> > LSN no distribuido más antiguo : (4796:457:1)
> > Ejecución de DBCC completada. Si hay mensajes de error, consulte al
> > administrador del sistema.
> >
> >
> > transaction information for db SII (my db)
> > transaction information duplicated
> > LSN older distributed : (4796:432:1)
> > LSN older not distributed : (4796:457:1)
> > DBCC execution completed. If there are error messages, contact system
> > administrator.
> >
> > ¿What's next?
> >
> >
> > "Paul S Randal [MS]" wrote:
> >
> > > Do you have any uncommitted transactions? What does DBCC OPENTRAN
> output?
> > >
> > > --
> > > Paul Randal
> > > Dev Lead, Microsoft SQL Server Storage Engine
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > > "Fidel Ramírez" <Fidel Ramrez@.discussions.microsoft.com> wrote in
> message
> > > news:A2AE904A-CE8C-48F1-9852-23F0033C56AD@.microsoft.com...
> > > > actually i have a db file size of 400MB and it's log file about 4.5GB,
> i
> > > read
> > > > all about backup log and shrink the file, and the problems is every
> > > virtual
> > > > log file has an status of active, some are really old ( 2002 ), how
> can i
> > > > turn off the virtual files and left only one active (the last)
> > >
> > >
> > >
>
>

can't publish a table because the row exceeds maximum row size (80

I keep getting an error 511 when I try to publish a table. It says that the
row size exceeds the maximum row size of 8060 bytes. I hear that splitting up
the table is one way, but this table is an integral fixture across many apps
in our system (we've been using it for years), so redesigning it is virtually
out of the question at this point. Is there any way I can work around this
error? How can I include this table in the publication?
Any help would be greatly appreciated!
-b.
can you change one or more columns to the text data type?
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"pukeboot" <pukeboot@.discussions.microsoft.com> wrote in message
news:6933DBA6-0CE9-47B8-9FA7-83CF1C447BFA@.microsoft.com...
>I keep getting an error 511 when I try to publish a table. It says that the
> row size exceeds the maximum row size of 8060 bytes. I hear that splitting
> up
> the table is one way, but this table is an integral fixture across many
> apps
> in our system (we've been using it for years), so redesigning it is
> virtually
> out of the question at this point. Is there any way I can work around this
> error? How can I include this table in the publication?
> Any help would be greatly appreciated!
> -b.

Can't increase datafile size

Hello All, a peculiar problem. I have a SQL Server 2000 with SP3. The
database on it has several datafiles spread over two file groups
"primary" and "secondary". On one of the files, which is on the primary
file group, when I try to increase the space allocated from 2100MB to
3000 MB it won't do it. No error message is generated. I am doing this
on enterprise manager. There is plenty of space on the disk to allow
for this increase. Any ideas? I am able to increase the datafile by
100 MB. So I can go from 2100 to 2200 but not to 3000.
Thanks,
Raziq.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Disk quota? Also, some confusion can sometimes be eliminated by
communication in the form of TSQL statements instead of "I do this in EM". I
suggest you construct a TSQL statement to do the file size change and post
here if you don't sort it out. ALTER DATABASE... MODIFY FILE...
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Raziq Shekha" <raziq_shekha@.anadarko.com> wrote in message
news:e5M%23LEVrDHA.2636@.TK2MSFTNGP09.phx.gbl...
> Hello All, a peculiar problem. I have a SQL Server 2000 with SP3. The
> database on it has several datafiles spread over two file groups
> "primary" and "secondary". On one of the files, which is on the primary
> file group, when I try to increase the space allocated from 2100MB to
> 3000 MB it won't do it. No error message is generated. I am doing this
> on enterprise manager. There is plenty of space on the disk to allow
> for this increase. Any ideas? I am able to increase the datafile by
> 100 MB. So I can go from 2100 to 2200 but not to 3000.
> Thanks,
> Raziq.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Check your restricted file growth.. that one gets me all
the time..
>--Original Message--
>Hello All, a peculiar problem. I have a SQL Server 2000
with SP3. The
>database on it has several datafiles spread over two file
groups
>"primary" and "secondary". On one of the files, which is
on the primary
>file group, when I try to increase the space allocated
from 2100MB to
>3000 MB it won't do it. No error message is generated.
I am doing this
>on enterprise manager. There is plenty of space on the
disk to allow
>for this increase. Any ideas? I am able to increase the
datafile by
>100 MB. So I can go from 2100 to 2200 but not to 3000.
>Thanks,
>Raziq.
>
>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.
>

cant increase column size

hey

i've a db running sql server express sp2. the db size now is 1.1 gb
i've a table with a varchar column of size 20 . when i try to increase the column size to 50 i get a
timeout exception, and the the cloumn size is unchanged. this table has 2.5 million records

i use sql server management studio express to do the changes

is there a way to increase this timeout or whtever i can do to update this column size?

thx in advance

I am guessing you are using ssms and the GUI to edit this column. There are occasions when you use the GUI that sql server doesn't choose the most efficient way of executing a task. In this instance, i would hazard a guess that it will be creating a temp table with the new schema and then dumping the records in to it before dropping your table and renaming the new temp one to the original one. (stay with me!!)

If you just try,

ALTER TABLE tbl
ALTER COLUMN col1 VARCHAR(50)

Hopefully, that will be quicker.

|||well strangely thats actually works
weird how the gui can be soo stupid in such a simple task

thx man
|||

There is subtle difference doing this task from GUI and from T-SQL.

From T-SQL:

It alters the existing table to modify that column alone.

From GUI:

1) Creates a new tmp table with the new structure and inserts the data from the table you are modifying with insert select clause.

2) Drops the table you are modifying.

3) Renames the new tmp table to the old table's name.

You could see this for yourself if you turn on SQL Profiler and try to modify the table with T-SQL and from GUI.

Can't import image when size is more then 8K using "microsoft.xmlhttp"

I'm using following script to import image into SQL Server from web and it does not work for image 2 (http://dbalinks.com/images/mol2.jpg) but it work for image 1 (http://dbalinks.com/images/mol1.jpg):

declare @.local int
declare @.retcode int
declare @.status int
declare @.url varchar(2000)

create table #httpresult(pic image)

--set @.url = 'http://dbalinks.com/images/mol1.jpg'
set @.url = 'http://dbalinks.com/images/mol2.jpg'

exec @.retcode = sp_oacreate 'microsoft.xmlhttp', @.local out
exec @.retcode = sp_oamethod @.local, 'open', null, 'get',@.url , 'false'
exec @.retcode = sp_oamethod @.local, 'send', null, @.status
exec @.retcode = sp_oagetproperty @.local, 'status', @.status out
insert #httpresult (pic)
exec @.retcode = sp_oagetproperty @.local, 'responsebody'

select * from #httpresult

drop table #httpresult

exec sp_oadestroy @.local

Is it possible to import image larger then 8K into SQL Server from the web using T-SQL and stored procedure?

Is there any chance that admin can do something about this?

Maybe using stream:

exec sp_oacreate 'adodb.stream',@.o OUT
exec sp_oasetproperty @.o,'Type',adTypeBinary

or something like that.

Can't import image when size is more then 8K using "microsoft.xmlhttp"

I'm using following script to import image into SQL Server from web and it does not work for image 2 (http://dbalinks.com/images/mol2.jpg) but it work for image 1 (http://dbalinks.com/images/mol1.jpg):

declare @.local int
declare @.retcode int
declare @.status int
declare @.url varchar(2000)

create table #httpresult(pic image)

--set @.url = 'http://dbalinks.com/images/mol1.jpg'
set @.url = 'http://dbalinks.com/images/mol2.jpg'

exec @.retcode = sp_oacreate 'microsoft.xmlhttp', @.local out
exec @.retcode = sp_oamethod @.local, 'open', null, 'get',@.url , 'false'
exec @.retcode = sp_oamethod @.local, 'send', null, @.status
exec @.retcode = sp_oagetproperty @.local, 'status', @.status out
insert #httpresult (pic)
exec @.retcode = sp_oagetproperty @.local, 'responsebody'

select * from #httpresult

drop table #httpresult

exec sp_oadestroy @.local

Is it possible to import image larger then 8K into SQL Server from the web using T-SQL and stored procedure?

Is there any chance that admin can do something about this?

Maybe using stream:

exec sp_oacreate 'adodb.stream',@.o OUT
exec sp_oasetproperty @.o,'Type',adTypeBinary

or something like that.

Cant get proper Field size using Enterprise Manager

This is frustrating. Im new at MS SQL and have created a table using Enterprise Manager. I need one of the fields to hold a paragraph or page of text... Ive tried every data type and even those with max length of 8000 or greater, I cannot get it to save more than the first 256 characters. I use an "INSERT" or "UPDATE" command from a PHP script on the front end...

any help would be amazing!

thank you!

michael wolffhave you thought that perhaps it is php that is not letting your text be longer then 256??

if you are using sql to insert the record get php to print out the sql that it is sending to the server and then see what happens if you execute that in the query analyzer|||Originally posted by rokslide
have you thought that perhaps it is php that is not letting your text be longer then 256??

if you are using sql to insert the record get php to print out the sql that it is sending to the server and then see what happens if you execute that in the query analyzer

the php shows the field as it should be... then i execute the INSERT... then missing text!|||have you actually tried executing the sql in the query analyzer?

I have been doing web based updates to sql for ages and I have only experienced this problem when I had stuffed up the command somewhere.|||you mean running the insert directly in EM to see if it takes the field size?

no, but ill try it now!|||i tried it and got this:

*********
Server: Msg 103, Level 15, State 7, Line 1
The identifier that starts with 'IN CASE YOU WERE WONDERING!! We've had some problems with our host and are currently working on fixing the problems this has cau' is too long. Maximum length is 128.
Server: Msg 128, Level 15, State 1, Line 1
The name 'IN CASE YOU WERE WONDERING!! We've had some problems with our host and are currently working on fixing the problems this has cau' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
************

its a 'VARCHAR' field of max length 8000... whys it telling me the max is 128 here? and whats it mean by "The nam"? its just text...

*****

heres the statement i ran:

INSERT INTO headlines VALUES (getdate(),"IN CASE YOU WERE WONDERING!! We've had some problems with our host and are currently working on fixing the problems this has caused with the site. Just getting tour dates updated, BIG TEXTBIG TEXTBIG TEXTBIG TEXTBIG TEXTBIG TEXTBIG TEXTBIG TEXTBIG TEXTBIG TEXTBIG TEXT 25 BIG TEXTBIG TEXTBIG TEXTBIG TEXTBIG TEXTBIG TEXT 50 BIG TEXTBIG TEXTBIG TEXTBIG TEXTBIG TEXTBIG TEXT 100",25)

field types are: datetime, varchar, numeric...

and ideas?

thank you.

michael wolff|||okie, well that error message is not coming from MS SQL. MS SQL would say something like - Action aborted. String or Binary data would have been truncated - or some such...

can't remember the exact syntax...

so something else is saying that the field is 128/256.

in your php do you define the fields that you are putting data into? do they have default values for things like length??

I have passed the error to a friend that uses php and will pass on what he says.|||dont need to define the fields... well, its just a 'textarea' html field... and that value is passed into the 'INSERT' command...

the errors i posted above i got directly from the Query Analyzer... so i dont know...

please let me know what your friend says!

thank you

michael wolff|||Hi Michael,

I wonder if it's not just a problem with your double quotes around the string.
Could you try it with 'single quotes' (apostrophes?).
SQL thinks you refer to a column or other database name if you put it within double quotes.

Cheers,
Robert|||still cut it off at 254 characters..... and this is directly with the QA... so its the same results as doing it from a PHP script...|||Can you provide me with the script to create the table and the insert statement you're using and I'll take a look for you ?

Thanks
Mark|||i created the table directly in EM...

but heres the INSERT code, pretty straighforward:

$query="INSERT INTO headlines VALUES (getdate(),\"$newheadline\",\"$theid\")";|||Originally posted by celloshred
i created the table directly in EM...

You could right click the table in EM and choose All tasks->Generate SQL Scripts to get the script that would help us track down the problem.

I guess this is not the case, but there was a limit in old ODBC drivers which cut off chars/varchars at 256 characters.|||you got there just before me :-)|||not sure what that is, but heres the file (attached)|||Its the double quotes around the inserted string. Use single quotes.
However as you have a single quote inside the string you provided, additional quotes will need to surround those quotes.

Or you could on use run via iSQL

SET quoted_identifier OFF

which will allow you to use the query just as you have it, ie with double quotes