Clean old backup with retaindays

Hi,
i have 3 sql server, one with sql2000 and other with sql2005.
on each server i have a maintenance plan. this plan save all base to a
network share on another server.
all plans have the option retaindays at 2.
the sql2000 server clean correctly the old backup. but the sql2005 servers
don' t do it.
all the sql service are running under the same account.
i applied patch on a sql2005 at 3152, the other is always at 3042. but
nothing change.
everydays i muste clean manually the backup...retaindays don't work!
someone have an idea about this bug ?
Ty
ChristopheChristophe,
It's not a bug, it's a feature. :) I assume that you are using the
Maintenance Plans. One thing to know is that the option RETAINDAYS does not
actually delete anything, it is just marking the file to tell SQL Server not
to overwrite this file before the retain time is up.
If you are using SQL 2000 Legacy Maintenance Plans, check the Complete
Backup and Transaction Log Backup tabs and make sure that "Remove files
older than" is set.
If you are using SQL 2005 Maintenance plans, you also need to include the
Maintenance Cleanup task to specify file deletions.
RLF
"Christophe" <Christophe@.discussions.microsoft.com> wrote in message
news:C14216B5-32AA-4D94-972C-122D864DEA51@.microsoft.com...
> Hi,
> i have 3 sql server, one with sql2000 and other with sql2005.
> on each server i have a maintenance plan. this plan save all base to a
> network share on another server.
> all plans have the option retaindays at 2.
> the sql2000 server clean correctly the old backup. but the sql2005 servers
> don' t do it.
> all the sql service are running under the same account.
> i applied patch on a sql2005 at 3152, the other is always at 3042. but
> nothing change.
> everydays i muste clean manually the backup...retaindays don't work!
> someone have an idea about this bug ?
> Ty
> Christophe|||ty very much ! ;)
"Russell Fields" wrote:
> Christophe,
> It's not a bug, it's a feature. :) I assume that you are using the
> Maintenance Plans. One thing to know is that the option RETAINDAYS does not
> actually delete anything, it is just marking the file to tell SQL Server not
> to overwrite this file before the retain time is up.
> If you are using SQL 2000 Legacy Maintenance Plans, check the Complete
> Backup and Transaction Log Backup tabs and make sure that "Remove files
> older than" is set.
> If you are using SQL 2005 Maintenance plans, you also need to include the
> Maintenance Cleanup task to specify file deletions.
> RLF
>
> "Christophe" <Christophe@.discussions.microsoft.com> wrote in message
> news:C14216B5-32AA-4D94-972C-122D864DEA51@.microsoft.com...
> > Hi,
> >
> > i have 3 sql server, one with sql2000 and other with sql2005.
> > on each server i have a maintenance plan. this plan save all base to a
> > network share on another server.
> > all plans have the option retaindays at 2.
> > the sql2000 server clean correctly the old backup. but the sql2005 servers
> > don' t do it.
> > all the sql service are running under the same account.
> > i applied patch on a sql2005 at 3152, the other is always at 3042. but
> > nothing change.
> >
> > everydays i muste clean manually the backup...retaindays don't work!
> > someone have an idea about this bug ?
> >
> > Ty
> > Christophe
>
>

cleaing up text type fields

Hello.
I have a text type field in an SQL2000 table I need to clean up. After
converting from MySQL I am finding that I have leading Tabs and when I
display the text field in an asp textbox I end up see the record
double spaced.
Any suggestion on how I can clean the field up?
Also, as I test an application I just migrated from apache/php/mysql
to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
pentium 4 with 512mb I am finding the the MS solution is like 3 times
slower?
Thanks for any help or info.> I have a text type field in an SQL2000 table I need to clean up. After
> converting from MySQL I am finding that I have leading Tabs and when I
> display the text field in an asp textbox I end up see the record
> double spaced.
> Any suggestion on how I can clean the field up?
Trim it on the client before displaying it?
> Also, as I test an application I just migrated from apache/php/mysql
> to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
> pentium 4 with 512mb I am finding the the MS solution is like 3 times
> slower?
We don't even have a fraction of the information required to analyze your
environment and point out potential reasons for any performance differences.|||Assuming that each record has two leading tabs, you could do something like:
DECLARE @.ptr binary(16)
SELECT @.ptr=textptr(textColumn) FROM yourTable
UPDATETEXT yourTable.textColumn @.ptr 0 2 ''
That will delete the first two characters from each record. If you need to
do more complex evaluation, you can run a cursor over the table and use
CHARINDEX to determine where the replacement(s) should be made.
<jason@.cyberpine.com> wrote in message
news:ef0a04d7.0311070754.5567591d@.posting.google.com...
> Hello.
> I have a text type field in an SQL2000 table I need to clean up. After
> converting from MySQL I am finding that I have leading Tabs and when I
> display the text field in an asp textbox I end up see the record
> double spaced.
> Any suggestion on how I can clean the field up?
> Also, as I test an application I just migrated from apache/php/mysql
> to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
> pentium 4 with 512mb I am finding the the MS solution is like 3 times
> slower?
> Thanks for any help or info.|||On second thought, you will need a CURSOR - Running that code as-is will
only strip the first two characters from the last record selected.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:eB#svjUpDHA.2312@.TK2MSFTNGP12.phx.gbl...
> Assuming that each record has two leading tabs, you could do something
like:
> DECLARE @.ptr binary(16)
> SELECT @.ptr=textptr(textColumn) FROM yourTable
> UPDATETEXT yourTable.textColumn @.ptr 0 2 ''
> That will delete the first two characters from each record. If you need
to
> do more complex evaluation, you can run a cursor over the table and use
> CHARINDEX to determine where the replacement(s) should be made.
> <jason@.cyberpine.com> wrote in message
> news:ef0a04d7.0311070754.5567591d@.posting.google.com...
> > Hello.
> >
> > I have a text type field in an SQL2000 table I need to clean up. After
> > converting from MySQL I am finding that I have leading Tabs and when I
> > display the text field in an asp textbox I end up see the record
> > double spaced.
> >
> > Any suggestion on how I can clean the field up?
> >
> > Also, as I test an application I just migrated from apache/php/mysql
> > to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
> > pentium 4 with 512mb I am finding the the MS solution is like 3 times
> > slower?
> >
> > Thanks for any help or info.
>|||You can see this example for using replace in this case:
http://www.aspfaq.com/2445
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:eZxpAnUpDHA.684@.TK2MSFTNGP09.phx.gbl...
> On second thought, you will need a CURSOR - Running that code as-is will
> only strip the first two characters from the last record selected.
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
message
> news:eB#svjUpDHA.2312@.TK2MSFTNGP12.phx.gbl...
> > Assuming that each record has two leading tabs, you could do something
> like:
> >
> > DECLARE @.ptr binary(16)
> > SELECT @.ptr=textptr(textColumn) FROM yourTable
> > UPDATETEXT yourTable.textColumn @.ptr 0 2 ''
> >
> > That will delete the first two characters from each record. If you need
> to
> > do more complex evaluation, you can run a cursor over the table and use
> > CHARINDEX to determine where the replacement(s) should be made.
> >
> > <jason@.cyberpine.com> wrote in message
> > news:ef0a04d7.0311070754.5567591d@.posting.google.com...
> > > Hello.
> > >
> > > I have a text type field in an SQL2000 table I need to clean up. After
> > > converting from MySQL I am finding that I have leading Tabs and when I
> > > display the text field in an asp textbox I end up see the record
> > > double spaced.
> > >
> > > Any suggestion on how I can clean the field up?
> > >
> > > Also, as I test an application I just migrated from apache/php/mysql
> > > to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
> > > pentium 4 with 512mb I am finding the the MS solution is like 3 times
> > > slower?
> > >
> > > Thanks for any help or info.
> >
> >
>|||Ahhh, nice... I didn't realize this slightly annoying caveat:
"Initially I used CHARINDEX, but that failed if the pattern was deeper than
8,000 characters into the value. PATINDEX never dies."
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uAGRfqUpDHA.2772@.TK2MSFTNGP12.phx.gbl...
> You can see this example for using replace in this case:
> http://www.aspfaq.com/2445
>
>
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
message
> news:eZxpAnUpDHA.684@.TK2MSFTNGP09.phx.gbl...
> > On second thought, you will need a CURSOR - Running that code as-is will
> > only strip the first two characters from the last record selected.
> >
> > "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
> message
> > news:eB#svjUpDHA.2312@.TK2MSFTNGP12.phx.gbl...
> > > Assuming that each record has two leading tabs, you could do something
> > like:
> > >
> > > DECLARE @.ptr binary(16)
> > > SELECT @.ptr=textptr(textColumn) FROM yourTable
> > > UPDATETEXT yourTable.textColumn @.ptr 0 2 ''
> > >
> > > That will delete the first two characters from each record. If you
need
> > to
> > > do more complex evaluation, you can run a cursor over the table and
use
> > > CHARINDEX to determine where the replacement(s) should be made.
> > >
> > > <jason@.cyberpine.com> wrote in message
> > > news:ef0a04d7.0311070754.5567591d@.posting.google.com...
> > > > Hello.
> > > >
> > > > I have a text type field in an SQL2000 table I need to clean up.
After
> > > > converting from MySQL I am finding that I have leading Tabs and when
I
> > > > display the text field in an asp textbox I end up see the record
> > > > double spaced.
> > > >
> > > > Any suggestion on how I can clean the field up?
> > > >
> > > > Also, as I test an application I just migrated from apache/php/mysql
> > > > to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
> > > > pentium 4 with 512mb I am finding the the MS solution is like 3
times
> > > > slower?
> > > >
> > > > Thanks for any help or info.
> > >
> > >
> >
> >
>|||It took a few practical uses before I cleaned that up. The code is a little
simpler for this specific case, because you don't have to find the index,
and you don't have to loop through... you just replace the first two
characters if they're what you think they are.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:ePbcpuUpDHA.2528@.TK2MSFTNGP12.phx.gbl...
> Ahhh, nice... I didn't realize this slightly annoying caveat:
> "Initially I used CHARINDEX, but that failed if the pattern was deeper
than
> 8,000 characters into the value. PATINDEX never dies."sqlsql

Clarification of execution in SQL2000 SP3A

Hi,
I need some clarification of a situation that we now have when moving to
SP3A. If we try and execute a stored procedure from another database it runs
on the database the stored procedure belongs to.
e.g. USE DatabaseA
Execute DatabaseB.dbo.upRunsp
The sp upRunsp will run on DatabaseB not DatabaseA
Is this an effect of cross DB ownership chaining being set off?
Thanks
Chris Wood
Alberta Department of Energy
CANADA
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:e7MpPTmTEHA.1168@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I need some clarification of a situation that we now have when moving to
> SP3A. If we try and execute a stored procedure from another database it
runs
> on the database the stored procedure belongs to.
> e.g. USE DatabaseA
> Execute DatabaseB.dbo.upRunsp
> The sp upRunsp will run on DatabaseB not DatabaseA
> Is this an effect of cross DB ownership chaining being set off?
>
No. This has nothing to do with cross DB ownership chaining. When you
EXECUTE a procedure using a database-qualified name, the object names inside
the procedure are resolved of wrt the procedure's owner. When you run it
without qualifation, names are resolved wrt your current context.
compare the output of
EXECUTE sp_help --displays info about the currentdb.dbo.sysobjects
and
EXECUTE master.dbo.sp_help --displays info about master.dbo.sysobjects
And the only way to run a procedure in another database without a
database-qualified name is to put it in Master and prefix it with sp_. So
the only way to have one procedure run against different databases is to put
it in Master and prefix it with sp_.
David
|||David,
Did the behaviour change between Gold and SP3A?
Chris
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%238Lp6gmTEHA.2128@.TK2MSFTNGP11.phx.gbl...
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:e7MpPTmTEHA.1168@.TK2MSFTNGP11.phx.gbl...
> runs
> No. This has nothing to do with cross DB ownership chaining. When you
> EXECUTE a procedure using a database-qualified name, the object names
inside
> the procedure are resolved of wrt the procedure's owner. When you run it
> without qualifation, names are resolved wrt your current context.
> compare the output of
> EXECUTE sp_help --displays info about the currentdb.dbo.sysobjects
> and
> EXECUTE master.dbo.sp_help --displays info about master.dbo.sysobjects
> And the only way to run a procedure in another database without a
> database-qualified name is to put it in Master and prefix it with sp_. So
> the only way to have one procedure run against different databases is to
put
> it in Master and prefix it with sp_.
> David
>
>
|||"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:eTVRjlmTEHA.1472@.TK2MSFTNGP12.phx.gbl...
> David,
> Did the behaviour change between Gold and SP3A?
>
This behavior has been like that since at least 6.5.
David
|||David,
Just debugged this one better. Under SP2 if the default database is the one
you are on you can run xp_sendmail from master, while you are on your
default database, and look at a table on your default database without
needing to fully qualify it.
e.g. on DatabaseA
exec master..xp_sendmail @.recipients = '...',
@.query = 'SELECT FROM tablea where
This will work.
Under SP3A it gives an ODBC error 208 (42S02) unless I change the SELECT to
DatabaseA.dbo.tablea
We are using build 818 for testing.
Chris
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eGGxE0mTEHA.760@.TK2MSFTNGP12.phx.gbl...
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:eTVRjlmTEHA.1472@.TK2MSFTNGP12.phx.gbl...
> This behavior has been like that since at least 6.5.
> David
>
|||Also. This only works this way if the database is your default database. It
does not matter about the permissions of the user just its default database
setting.
Chris
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:e2yXy9xTEHA.2464@.TK2MSFTNGP10.phx.gbl...
> David,
> Just debugged this one better. Under SP2 if the default database is the
one
> you are on you can run xp_sendmail from master, while you are on your
> default database, and look at a table on your default database without
> needing to fully qualify it.
> e.g. on DatabaseA
> exec master..xp_sendmail @.recipients = '...',
> @.query = 'SELECT FROM tablea where
> This will work.
> Under SP3A it gives an ODBC error 208 (42S02) unless I change the SELECT
to
> DatabaseA.dbo.tablea
> We are using build 818 for testing.
> Chris
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:eGGxE0mTEHA.760@.TK2MSFTNGP12.phx.gbl...
>
|||"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:O8LPRQyTEHA.808@.tk2msftngp13.phx.gbl...
> Also. This only works this way if the database is your default database.
It
> does not matter about the permissions of the user just its default
database
> setting.
>
Ahh. xp_sendmail executes that query over a different connection created
from within the extended stored procedure, but bound into the same
transaction. Normal rules of name resolution don't apply here, as it
depends entirely on how xp_sendmail creates the second connection and what
database it connects to. Perhaps there was a change to the implementation
of xp_sendmail in SP3.
David
|||Are all of the objects needed in the same database on the same server? We
had "cross database ownership" introduced in sp3. Is this checked?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||Vikram,
I thought that this may have been the problem but it is not. We have a
stored procedure in database A that included a xp_sendmail with a query that
ran against a table in database A. Prior to SP3 we did not have to fully
qualify the table in database A. Fully qualifying works for both SP2 and SP3
and that is what we had to do.
Thanks
Chris
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:O%23j3ZkNXEHA.328@.cpmsftngxa10.phx.gbl...
> Are all of the objects needed in the same database on the same server? We
> had "cross database ownership" introduced in sp3. Is this checked?
>
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
|||Vikram,
I thought that this may have been the problem but it is not. We have a
stored procedure in database A that included a xp_sendmail with a query that
ran against a table in database A. Prior to SP3 we did not have to fully
qualify the table in database A. Fully qualifying works for both SP2 and SP3
and that is what we had to do.
Thanks
Chris
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:O%23j3ZkNXEHA.328@.cpmsftngxa10.phx.gbl...
> Are all of the objects needed in the same database on the same server? We
> had "cross database ownership" introduced in sp3. Is this checked?
>
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
sqlsql

Clarification of execution in SQL2000 SP3A

Hi,
I need some clarification of a situation that we now have when moving to
SP3A. If we try and execute a stored procedure from another database it runs
on the database the stored procedure belongs to.
e.g. USE DatabaseA
Execute DatabaseB.dbo.upRunsp
The sp upRunsp will run on DatabaseB not DatabaseA
Is this an effect of cross DB ownership chaining being set off?
Thanks
Chris Wood
Alberta Department of Energy
CANADA"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:e7MpPTmTEHA.1168@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I need some clarification of a situation that we now have when moving to
> SP3A. If we try and execute a stored procedure from another database it
runs
> on the database the stored procedure belongs to.
> e.g. USE DatabaseA
> Execute DatabaseB.dbo.upRunsp
> The sp upRunsp will run on DatabaseB not DatabaseA
> Is this an effect of cross DB ownership chaining being set off?
>
No. This has nothing to do with cross DB ownership chaining. When you
EXECUTE a procedure using a database-qualified name, the object names inside
the procedure are resolved of wrt the procedure's owner. When you run it
without qualifation, names are resolved wrt your current context.
compare the output of
EXECUTE sp_help --displays info about the currentdb.dbo.sysobjects
and
EXECUTE master.dbo.sp_help --displays info about master.dbo.sysobjects
And the only way to run a procedure in another database without a
database-qualified name is to put it in Master and prefix it with sp_. So
the only way to have one procedure run against different databases is to put
it in Master and prefix it with sp_.
David|||David,
Did the behaviour change between Gold and SP3A?
Chris
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%238Lp6gmTEHA.2128@.TK2MSFTNGP11.phx.gbl...
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:e7MpPTmTEHA.1168@.TK2MSFTNGP11.phx.gbl...
> runs
> No. This has nothing to do with cross DB ownership chaining. When you
> EXECUTE a procedure using a database-qualified name, the object names
inside
> the procedure are resolved of wrt the procedure's owner. When you run it
> without qualifation, names are resolved wrt your current context.
> compare the output of
> EXECUTE sp_help --displays info about the currentdb.dbo.sysobjects
> and
> EXECUTE master.dbo.sp_help --displays info about master.dbo.sysobjects
> And the only way to run a procedure in another database without a
> database-qualified name is to put it in Master and prefix it with sp_. So
> the only way to have one procedure run against different databases is to
put
> it in Master and prefix it with sp_.
> David
>
>|||"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:eTVRjlmTEHA.1472@.TK2MSFTNGP12.phx.gbl...
> David,
> Did the behaviour change between Gold and SP3A?
>
This behavior has been like that since at least 6.5.
David|||David,
Just debugged this one better. Under SP2 if the default database is the one
you are on you can run xp_sendmail from master, while you are on your
default database, and look at a table on your default database without
needing to fully qualify it.
e.g. on DatabaseA
exec master..xp_sendmail @.recipients = '...',
@.query = 'SELECT FROM tablea where
This will work.
Under SP3A it gives an ODBC error 208 (42S02) unless I change the SELECT to
DatabaseA.dbo.tablea
We are using build 818 for testing.
Chris
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eGGxE0mTEHA.760@.TK2MSFTNGP12.phx.gbl...
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:eTVRjlmTEHA.1472@.TK2MSFTNGP12.phx.gbl...
> This behavior has been like that since at least 6.5.
> David
>|||Also. This only works this way if the database is your default database. It
does not matter about the permissions of the user just its default database
setting.
Chris
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:e2yXy9xTEHA.2464@.TK2MSFTNGP10.phx.gbl...
> David,
> Just debugged this one better. Under SP2 if the default database is the
one
> you are on you can run xp_sendmail from master, while you are on your
> default database, and look at a table on your default database without
> needing to fully qualify it.
> e.g. on DatabaseA
> exec master..xp_sendmail @.recipients = '...',
> @.query = 'SELECT FROM tablea where
> This will work.
> Under SP3A it gives an ODBC error 208 (42S02) unless I change the SELECT
to
> DatabaseA.dbo.tablea
> We are using build 818 for testing.
> Chris
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:eGGxE0mTEHA.760@.TK2MSFTNGP12.phx.gbl...
>|||"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:O8LPRQyTEHA.808@.tk2msftngp13.phx.gbl...
> Also. This only works this way if the database is your default database.
It
> does not matter about the permissions of the user just its default
database
> setting.
>
Ahh. xp_sendmail executes that query over a different connection created
from within the extended stored procedure, but bound into the same
transaction. Normal rules of name resolution don't apply here, as it
depends entirely on how xp_sendmail creates the second connection and what
database it connects to. Perhaps there was a change to the implementation
of xp_sendmail in SP3.
David|||Are all of the objects needed in the same database on the same server? We
had "cross database ownership" introduced in sp3. Is this checked?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||Vikram,
I thought that this may have been the problem but it is not. We have a
stored procedure in database A that included a xp_sendmail with a query that
ran against a table in database A. Prior to SP3 we did not have to fully
qualify the table in database A. Fully qualifying works for both SP2 and SP3
and that is what we had to do.
Thanks
Chris
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:O%23j3ZkNXEHA.328@.cpmsftngxa10.phx.gbl...
> Are all of the objects needed in the same database on the same server? We
> had "cross database ownership" introduced in sp3. Is this checked?
>
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>

Clarification of execution in SQL2000 SP3A

Hi,
I need some clarification of a situation that we now have when moving to
SP3A. If we try and execute a stored procedure from another database it runs
on the database the stored procedure belongs to.
e.g. USE DatabaseA
Execute DatabaseB.dbo.upRunsp
The sp upRunsp will run on DatabaseB not DatabaseA
Is this an effect of cross DB ownership chaining being set off?
Thanks
Chris Wood
Alberta Department of Energy
CANADA"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:e7MpPTmTEHA.1168@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I need some clarification of a situation that we now have when moving to
> SP3A. If we try and execute a stored procedure from another database it
runs
> on the database the stored procedure belongs to.
> e.g. USE DatabaseA
> Execute DatabaseB.dbo.upRunsp
> The sp upRunsp will run on DatabaseB not DatabaseA
> Is this an effect of cross DB ownership chaining being set off?
>
No. This has nothing to do with cross DB ownership chaining. When you
EXECUTE a procedure using a database-qualified name, the object names inside
the procedure are resolved of wrt the procedure's owner. When you run it
without qualifation, names are resolved wrt your current context.
compare the output of
EXECUTE sp_help --displays info about the currentdb.dbo.sysobjects
and
EXECUTE master.dbo.sp_help --displays info about master.dbo.sysobjects
And the only way to run a procedure in another database without a
database-qualified name is to put it in Master and prefix it with sp_. So
the only way to have one procedure run against different databases is to put
it in Master and prefix it with sp_.
David|||David,
Did the behaviour change between Gold and SP3A?
Chris
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%238Lp6gmTEHA.2128@.TK2MSFTNGP11.phx.gbl...
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:e7MpPTmTEHA.1168@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > I need some clarification of a situation that we now have when moving to
> > SP3A. If we try and execute a stored procedure from another database it
> runs
> > on the database the stored procedure belongs to.
> >
> > e.g. USE DatabaseA
> > Execute DatabaseB.dbo.upRunsp
> >
> > The sp upRunsp will run on DatabaseB not DatabaseA
> >
> > Is this an effect of cross DB ownership chaining being set off?
> >
> No. This has nothing to do with cross DB ownership chaining. When you
> EXECUTE a procedure using a database-qualified name, the object names
inside
> the procedure are resolved of wrt the procedure's owner. When you run it
> without qualifation, names are resolved wrt your current context.
> compare the output of
> EXECUTE sp_help --displays info about the currentdb.dbo.sysobjects
> and
> EXECUTE master.dbo.sp_help --displays info about master.dbo.sysobjects
> And the only way to run a procedure in another database without a
> database-qualified name is to put it in Master and prefix it with sp_. So
> the only way to have one procedure run against different databases is to
put
> it in Master and prefix it with sp_.
> David
>
>|||"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:eTVRjlmTEHA.1472@.TK2MSFTNGP12.phx.gbl...
> David,
> Did the behaviour change between Gold and SP3A?
>
This behavior has been like that since at least 6.5.
David|||David,
Just debugged this one better. Under SP2 if the default database is the one
you are on you can run xp_sendmail from master, while you are on your
default database, and look at a table on your default database without
needing to fully qualify it.
e.g. on DatabaseA
exec master..xp_sendmail @.recipients = '...',
@.query = 'SELECT FROM tablea where
This will work.
Under SP3A it gives an ODBC error 208 (42S02) unless I change the SELECT to
DatabaseA.dbo.tablea
We are using build 818 for testing.
Chris
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eGGxE0mTEHA.760@.TK2MSFTNGP12.phx.gbl...
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:eTVRjlmTEHA.1472@.TK2MSFTNGP12.phx.gbl...
> > David,
> >
> > Did the behaviour change between Gold and SP3A?
> >
> This behavior has been like that since at least 6.5.
> David
>|||Also. This only works this way if the database is your default database. It
does not matter about the permissions of the user just its default database
setting.
Chris
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:e2yXy9xTEHA.2464@.TK2MSFTNGP10.phx.gbl...
> David,
> Just debugged this one better. Under SP2 if the default database is the
one
> you are on you can run xp_sendmail from master, while you are on your
> default database, and look at a table on your default database without
> needing to fully qualify it.
> e.g. on DatabaseA
> exec master..xp_sendmail @.recipients = '...',
> @.query = 'SELECT FROM tablea where
> This will work.
> Under SP3A it gives an ODBC error 208 (42S02) unless I change the SELECT
to
> DatabaseA.dbo.tablea
> We are using build 818 for testing.
> Chris
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:eGGxE0mTEHA.760@.TK2MSFTNGP12.phx.gbl...
> >
> > "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> > news:eTVRjlmTEHA.1472@.TK2MSFTNGP12.phx.gbl...
> > > David,
> > >
> > > Did the behaviour change between Gold and SP3A?
> > >
> >
> > This behavior has been like that since at least 6.5.
> >
> > David
> >
> >
>|||"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:O8LPRQyTEHA.808@.tk2msftngp13.phx.gbl...
> Also. This only works this way if the database is your default database.
It
> does not matter about the permissions of the user just its default
database
> setting.
>
Ahh. xp_sendmail executes that query over a different connection created
from within the extended stored procedure, but bound into the same
transaction. Normal rules of name resolution don't apply here, as it
depends entirely on how xp_sendmail creates the second connection and what
database it connects to. Perhaps there was a change to the implementation
of xp_sendmail in SP3.
David|||Are all of the objects needed in the same database on the same server? We
had "cross database ownership" introduced in sp3. Is this checked?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||Vikram,
I thought that this may have been the problem but it is not. We have a
stored procedure in database A that included a xp_sendmail with a query that
ran against a table in database A. Prior to SP3 we did not have to fully
qualify the table in database A. Fully qualifying works for both SP2 and SP3
and that is what we had to do.
Thanks
Chris
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:O%23j3ZkNXEHA.328@.cpmsftngxa10.phx.gbl...
> Are all of the objects needed in the same database on the same server? We
> had "cross database ownership" introduced in sp3. Is this checked?
>
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>

Chinese Characters in SQL 2000

Hi All

I have to develop a website which allows users to enter their comments into Chinese languages. I need sql2000 to support the chinese characters.

I am experienced .net web programmer, but have very little knowledge on the database side. Would really appreciate any help on this

THanks
JigneshAll you should need to do is use Unicode character types (NCHAR, NVARCHAR, and NTEXT) and SQL Server should handle the Chinese characters gracefully. Getting SQL Server to provide Chinese error messages is a bit more complicted, but let's deal with one problem at a time!

-PatP|||I have declared the table column as nvarchar. I was searching on google and found out that the only thing I need to do is declare them as either nvarchar or nchar etc..

I guess it does not work in my case. I am sure I am doing something wrong, but not sure what.|||I installed windows XP language bar on my machine. So when I go to enterprise manager, I can type in chinese characters in the database directly and then can view them on my ASP.net page.

But when I try to insert a record from an ASP.net page by typing chinese characters in the text box, they are all converted to ??.|||Something between your keyboard and the web page control is only passing 8 bit values. Since you can successfully enter Unicode (the Chinese characters) via SQL Enterprise Mangler, I think we can assume everything is Ok as far as your OS, which leads me to think the problem is either in your browser or beyond (probably the web page).

-PatP|||I guess you are right. The problem seems to be in the web page. I just created a sample ASP.net webpage, which have one text box and a button.

When user clicks chinese characters in textbox with help of language bar and press enter, the data goes to database. But the data are converted to ????|||I was doing some more trials and found out that only time when I can enter proper data in SQL is when I go to enterprise manager and type in the data in the cell myself ( just like Excel).

But when I run the following query
Insert into TestTable(Comments, Language) values('彰','chinese') even on the enterprise manager or query analyzer, the chinese characters are converted to ???|||Just for the jolly factor, let's cut SQL Server out of the problem altogether for a moment. try to construct a "hello world" sort of web page with just a text control and a button. Have it simply allow the user to enter text into a control, then create a popup that contains that text when the user presses the button. This will allow you to get the web page working first, then we can concentrate on getting the data back and forth from the database.

-PatP|||Hi Pat

I tried exactly what you said. The code is below:

Private Sub btnTemp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTemp.Click
ShowMessage(txtComment.Text)
End Sub

Private Sub ShowMessage(ByVal message As String)
Dim scriptString As String = "<script language=JavaScript>"
scriptString += "alert('" + message + "');"
scriptString += "</script>"
Page.RegisterStartupScript("ShowMessage", scriptString)
End Sub

The code works absolutely fine. I enter chinese characters in textbox and onclick of a button show a messagebox with javascript, which shows the chinese text perfectly fine.|||Good! Now, can you modify that same sample script so that a new button simply sticks the Textbox contents into a column in the database? If you can do that, then modify the existing button to pull the data from the column and display that data from the column.

-PatP|||I guess thats when it fails. Below is my code:

**********************The same button click event ***************
Dim sqlConnection1 As SqlConnection = New SqlConnection("Data Source=(local); Initial Catalog = ChineseTest; UID=jop; PWD=jop2279")
Dim cmd As SqlCommand = sqlConnection1.CreateCommand
sqlConnection1.Open()
cmd.CommandType = CommandType.Text
cmd.CommandText = "Insert into TestTable (Comments, Lang)values('" + txtComment.Text + "','Chinese')"
cmd.ExecuteNonQuery()
sqlConnection1.Close()
************************************************** ***********************

But when I go to the database, I can only see >???|||Try running the statement in Query Analyzer like this
Insert into TestTable(Comments, Language) values(N'彰','chinese')
and see if that works.

How are you constructing your INSERT statement in your asp.net code?
Is it something like

string cmd="Insert into your_table(Comments) values('"+someTextBox.Text"')";
// Execute cmd

?

Edit: I see from your post that that is the case. You should really consider using a parameterized query for this. It ought to solve your unicode issue AND help protect you against SQL injection attacks.|||Just for the jolly factor, I'd apply the latest MDAC to both the web server, and the client PC (this is just "iron underware" for the development environment, it is not needed in production).

-PatP|||I changed the SQL statement as per ded's instruction to:

Insert into TestTable(Comments, Language) values(N'彰','chinese')

and it start working fine. I can run the same query from ASP.net webpage and it works fine too..

Thanks for your support guys. Without you, it would have been impossible for me to figure out this thing.

Chinese characters in SQL 2000

Hi All
I have to develop a website which allows users to enter their comments into Chinese languages. I need sql2000 to support the chinese characters.
I am experienced .net web programmer, but have very little knowledge on the database side. Would really appreciate any help on this
THanks
JigneshI have already declared the column as nvarchar.
I installed windows XP language bar on my machine. So when I go to enterprise manager, I can type in chinese characters in the database directly and then can view them on my ASP.net page.
But when I try to insert a record from an ASP.net page by typing chinese characters in the text box, they are all converted to ?.

Quote:

Originally posted by patel_o
Hi All
I have to develop a website which allows users to enter their comments into Chinese languages. I need sql2000 to support the chinese characters.
I am experienced .net web programmer, but have very little knowledge on the database side. Would really appreciate any help on this
THanks
Jignesh

CheckSum in SQL2000

I want to use checksum function for ntext data field in a table. But this
function accepts only varchar. Is there a way i can find checksum for ntext
fields in SQL 2000.
Thanks!!!I think you can cast it to a varchar
ie checksum(cast(field1 as varchar(8000))|||If you absolutely need to use CHECKSUM on the ntext field and, depending on
the size of the ntext values you already have in the specific table, you
might be able to convert the ntext to nchar/nvarchar or char/varchar and
apply checksum afterwards:
create table ENTEXT
(ID int, txt ntext)
insert into ENTEXT
values (1, 'any value')
select CHECKSUM(convert(char(8000),txt))
, CHECKSUM(convert(nchar(4000),txt))
from ENTEXT
drop table ENTEXT
The only limit would be the number of characters you can house temporarily
(4000 for nchar/nvarchar or 8000 for char/varchar) during the conversion
"skg" wrote:

> I want to use checksum function for ntext data field in a table. But this
> function accepts only varchar. Is there a way i can find checksum for ntex
t
> fields in SQL 2000.
> Thanks!!!
>
>|||Thanks All!!!
Mostly my text fields are more than 8k. I am just curious will the checksum
work if i do a substring and
compute checksum of individual chunks of 4k and finally call
checksum_agg(checksum(value)) to
get the aggregate checksum for the data field.
TIA
"Edgardo Valdez, MCSD, MCDBA"
<EdgardoValdezMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:86760E51-9059-4609-B55F-6000E5113C1D@.microsoft.com...
> If you absolutely need to use CHECKSUM on the ntext field and, depending
> on
> the size of the ntext values you already have in the specific table, you
> might be able to convert the ntext to nchar/nvarchar or char/varchar and
> apply checksum afterwards:
> create table ENTEXT
> (ID int, txt ntext)
> insert into ENTEXT
> values (1, 'any value')
> select CHECKSUM(convert(char(8000),txt))
> , CHECKSUM(convert(nchar(4000),txt))
> from ENTEXT
> drop table ENTEXT
> The only limit would be the number of characters you can house temporarily
> (4000 for nchar/nvarchar or 8000 for char/varchar) during the conversion
>
>
> "skg" wrote:
>|||Would checksuming only the first 8000 characters suffice? For what purpose
are you wanting to use the checksum?
"skg" <skg@.yahoo.com> wrote in message
news:uNJQatfIGHA.3000@.TK2MSFTNGP14.phx.gbl...
>I want to use checksum function for ntext data field in a table. But this
>function accepts only varchar. Is there a way i can find checksum for ntext
>fields in SQL 2000.
> Thanks!!!
>|||JT thanks!!!
We have pdf documents which are saved in db in text field. we want to remove
duplicates.
thx
"JT" <someone@.microsoft.com> wrote in message
news:%23Gw84IpIGHA.208@.tk2msftngp13.phx.gbl...
> Would checksuming only the first 8000 characters suffice? For what purpose
> are you wanting to use the checksum?
> "skg" <skg@.yahoo.com> wrote in message
> news:uNJQatfIGHA.3000@.TK2MSFTNGP14.phx.gbl...
>|||This is the problem with storing unstructured data (or at least data with an
unfamiliar structure) in a relational database, so identifying rows
containing duplicate 'values' is not simple.
Think about how PDF documents are structured internally. I'm not familiar
with the PDF format specifically, but I'm guessing it begins with a header
containing meta data fields about the documents's size, the file name the
originaly saved as, the date/time created and last written to, and probably
even it's own internal checksum values. Therefore, even if the documents are
several hundred KBs or even MBs in size, only the first 8000 or 4000 bytes
or so may uniquely identify them. You don't need to actually parse this
information, just understand it's a stream of unique bytes and create a
checksum value off of it. This would apply not just to PDFs but also to MS
Word, JPGs, ZIPs or most any structured file type. You will need to read up
on the specifications involved and experiment to confirm this theory would
consistently work. I'm interested in the results, becuase I could possibly
use this technique myself, so reply back to the group when you discover
something.
"skg" <skg@.yahoo.com> wrote in message
news:OKPXYxvIGHA.3176@.TK2MSFTNGP12.phx.gbl...
> JT thanks!!!
> We have pdf documents which are saved in db in text field. we want to
> remove duplicates.
> thx
> "JT" <someone@.microsoft.com> wrote in message
> news:%23Gw84IpIGHA.208@.tk2msftngp13.phx.gbl...
>

Checkpoints

Hi,
In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
checkpoint if no actual processing has taken place since the last
transaction was checkpointed?
Thanks
Chris Wood
Alberta Department of Energy
CANADAI believe it does. You can turn on trace flag 3502 to do a test. -T3502
will print a message to errorlog whenever a checkpoint is run in SQL Server.
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Chris Wood wrote:
> Hi,
> In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
> checkpoint if no actual processing has taken place since the last
> transaction was checkpointed?
> Thanks
> Chris Wood
> Alberta Department of Energy
> CANADA
>|||Thanks Yih-Yoon.
The trace flag shows that checkpoints are written.
Chris
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> wrote in message
news:eh1o17IBFHA.2584@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
>I believe it does. You can turn on trace flag 3502 to do a test. -T3502
>will print a message to errorlog whenever a checkpoint is run in SQL
>Server.
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> Chris Wood wrote:|||Yih-Yoon,
What does the (9999999) number represent in the message produced by trace
flag 3502?
We see Ckpt dbid 6 started (80)
Ckpt dbid 6 phase 1 ended (80)
Ckpt 6 Complete
The number 80 comes out a lot of times with this flag set.
Thanks
Chris
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> wrote in message
news:eh1o17IBFHA.2584@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
>I believe it does. You can turn on trace flag 3502 to do a test. -T3502
>will print a message to errorlog whenever a checkpoint is run in SQL
>Server.
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> Chris Wood wrote:

Checkpoints

Hi,
In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
checkpoint if no actual processing has taken place since the last
transaction was checkpointed?
Thanks
Chris Wood
Alberta Department of Energy
CANADAI believe it does. You can turn on trace flag 3502 to do a test. -T3502
will print a message to errorlog whenever a checkpoint is run in SQL Server.
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Chris Wood wrote:
> Hi,
> In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
> checkpoint if no actual processing has taken place since the last
> transaction was checkpointed?
> Thanks
> Chris Wood
> Alberta Department of Energy
> CANADA
>|||Thanks Yih-Yoon.
The trace flag shows that checkpoints are written.
Chris
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> wrote in message
news:eh1o17IBFHA.2584@.TK2MSFTNGP09.phx.gbl...
>I believe it does. You can turn on trace flag 3502 to do a test. -T3502
>will print a message to errorlog whenever a checkpoint is run in SQL
>Server.
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> Chris Wood wrote:
>> Hi,
>> In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
>> checkpoint if no actual processing has taken place since the last
>> transaction was checkpointed?
>> Thanks
>> Chris Wood
>> Alberta Department of Energy
>> CANADA|||Yih-Yoon,
What does the (9999999) number represent in the message produced by trace
flag 3502?
We see Ckpt dbid 6 started (80)
Ckpt dbid 6 phase 1 ended (80)
Ckpt 6 Complete
The number 80 comes out a lot of times with this flag set.
Thanks
Chris
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> wrote in message
news:eh1o17IBFHA.2584@.TK2MSFTNGP09.phx.gbl...
>I believe it does. You can turn on trace flag 3502 to do a test. -T3502
>will print a message to errorlog whenever a checkpoint is run in SQL
>Server.
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> Chris Wood wrote:
>> Hi,
>> In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
>> checkpoint if no actual processing has taken place since the last
>> transaction was checkpointed?
>> Thanks
>> Chris Wood
>> Alberta Department of Energy
>> CANADA

Checkpoints

Hi,
In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
checkpoint if no actual processing has taken place since the last
transaction was checkpointed?
Thanks
Chris Wood
Alberta Department of Energy
CANADA
I believe it does. You can turn on trace flag 3502 to do a test. -T3502
will print a message to errorlog whenever a checkpoint is run in SQL Server.
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Chris Wood wrote:
> Hi,
> In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
> checkpoint if no actual processing has taken place since the last
> transaction was checkpointed?
> Thanks
> Chris Wood
> Alberta Department of Energy
> CANADA
>
|||Thanks Yih-Yoon.
The trace flag shows that checkpoints are written.
Chris
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> wrote in message
news:eh1o17IBFHA.2584@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
>I believe it does. You can turn on trace flag 3502 to do a test. -T3502
>will print a message to errorlog whenever a checkpoint is run in SQL
>Server.
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> Chris Wood wrote:
|||Yih-Yoon,
What does the (9999999) number represent in the message produced by trace
flag 3502?
We see Ckpt dbid 6 started (80)
Ckpt dbid 6 phase 1 ended (80)
Ckpt 6 Complete
The number 80 comes out a lot of times with this flag set.
Thanks
Chris
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> wrote in message
news:eh1o17IBFHA.2584@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
>I believe it does. You can turn on trace flag 3502 to do a test. -T3502
>will print a message to errorlog whenever a checkpoint is run in SQL
>Server.
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> Chris Wood wrote: