How to translate varchar into varbinary?

Hi everyone,

We're trying to migrate a varchar field from Sql2k to varbinary in a sql25k through a dtsx package. We get an error which tell us: "data will be lost".

Does anyone have any idea about that?

Thanks for your time and inputs,

Do it in the extract query, T-SQL supports explicit casts between those two types

DECLARE @.v varchar(10)

SET @.v = '0123456789'

SELECT CAST(@.v AS varbinary(10))

how to transfer logins between 2k5 instances

Hello,
I am wondering if there is a kb article on how to transfer logins
between two sql2k5 instances? I have seen one for sql2k but not for sql2k5.
Thanks.
JakeHi Jake,
You can use a SSIS package to transfer logins. Also you can check my blog. I
updated the MS procedure to SQL Server 2005. it is in Spanish, but the code
is T-SQL, of course :-)
http://solidqualitylearning.com/blo...02/25/1618.aspx
Regards
Antonio Soto
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Jake" <noreply@.nowhere.com> escribi en el mensaje
news:enqPtusQGHA.5036@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I am wondering if there is a kb article on how to transfer logins
> between two sql2k5 instances? I have seen one for sql2k but not for
> sql2k5. Thanks.
> Jake
>|||Antonio,
Thanks alot for the help.
Jake
"Antonio Soto" <antoniosotorodriguez@.gmail.com> wrote in message
news:OsSMC92QGHA.4608@.tk2msftngp13.phx.gbl...
> Hi Jake,
> You can use a SSIS package to transfer logins. Also you can check my blog.
> I updated the MS procedure to SQL Server 2005. it is in Spanish, but the
> code is T-SQL, of course :-)
> http://solidqualitylearning.com/blo...02/25/1618.aspx
> Regards
> Antonio Soto
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
> "Jake" <noreply@.nowhere.com> escribi en el mensaje
> news:enqPtusQGHA.5036@.TK2MSFTNGP12.phx.gbl...
>|||You can still do it using SSIS (as opposed to DTS) . One thing to watch out
for is that for SQL logins the account is disabled and the password set to
random value as per Books Online "Transfer Logins Task"
Apart from that pretty sure the same rules applies - ie have to fix up with
sp_change_users_login
cheers,
Andy.
"Jake" <noreply@.nowhere.com> wrote in message
news:enqPtusQGHA.5036@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I am wondering if there is a kb article on how to transfer logins
> between two sql2k5 instances? I have seen one for sql2k but not for
> sql2k5. Thanks.
> Jake
>

how to tell when log file is not in use

sql2k sp3
Ive got a db with almost 4 gig of free space. I created a
proc that attempted to shrink it last night before doing
my backup so as to not backup this space. But I got the
message "Cannot shrink log file 2 (bla_log) because all
logical log files are in use". So I either need a way to
tell my backup to ignore the free space(this would be
preferable, but I dont this its possible) or tell my
shrink proc to see if the Log file is not in use.
TIA, ChrisBackup does not put your free space into the backup file. So don't worry
about that.
hth
Quentin
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:09ff01c3dc46$b4e9d2b0$a601280a@.phx.gbl...

quote:

---

> sql2k sp3
> Ive got a db with almost 4 gig of free space. I created a
> proc that attempted to shrink it last night before doing
> my backup so as to not backup this space. But I got the
> message "Cannot shrink log file 2 (bla_log) because all
> logical log files are in use". So I either need a way to
> tell my backup to ignore the free space(this would be
> preferable, but I dont this its possible) or tell my
> shrink proc to see if the Log file is not in use.
> TIA, Chris

---

|||Then why do I have a backup thats 12.7 gigs when the used
space is about 9.7 gigs?

quote:

---

>--Original Message--
>Backup does not put your free space into the backup

---

file. So don't worry

quote:

---

>about that.
>hth
>Quentin
>"chris" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:09ff01c3dc46$b4e9d2b0$a601280a@.phx.gbl...
a[QUOTE]
>
>.
>

---

|||Have you updated the used space information?
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:018e01c3dc54$5ee62310$a101280a@.phx.gbl...[QUOTE]
> Then why do I have a backup thats 12.7 gigs when the used
> space is about 9.7 gigs?
>
> file. So don't worry
> message
> a|||Yes.
"Quentin Ran" <ab@.who.com> wrote in message
news:#9DozhG3DHA.2296@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Have you updated the used space information?
> "chris" <anonymous@.discussions.microsoft.com> wrote in message
> news:018e01c3dc54$5ee62310$a101280a@.phx.gbl...
>

---

how to tell when log file is not in use

sql2k sp3
Ive got a db with almost 4 gig of free space. I created a
proc that attempted to shrink it last night before doing
my backup so as to not backup this space. But I got the
message "Cannot shrink log file 2 (bla_log) because all
logical log files are in use". So I either need a way to
tell my backup to ignore the free space(this would be
preferable, but I dont this its possible) or tell my
shrink proc to see if the Log file is not in use.
TIA, ChrisBackup does not put your free space into the backup file. So don't worry
about that.
hth
Quentin
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:09ff01c3dc46$b4e9d2b0$a601280a@.phx.gbl...
> sql2k sp3
> Ive got a db with almost 4 gig of free space. I created a
> proc that attempted to shrink it last night before doing
> my backup so as to not backup this space. But I got the
> message "Cannot shrink log file 2 (bla_log) because all
> logical log files are in use". So I either need a way to
> tell my backup to ignore the free space(this would be
> preferable, but I dont this its possible) or tell my
> shrink proc to see if the Log file is not in use.
> TIA, Chris|||Then why do I have a backup thats 12.7 gigs when the used
space is about 9.7 gigs?
>--Original Message--
>Backup does not put your free space into the backup
file. So don't worry
>about that.
>hth
>Quentin
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:09ff01c3dc46$b4e9d2b0$a601280a@.phx.gbl...
>> sql2k sp3
>> Ive got a db with almost 4 gig of free space. I created
a
>> proc that attempted to shrink it last night before doing
>> my backup so as to not backup this space. But I got the
>> message "Cannot shrink log file 2 (bla_log) because all
>> logical log files are in use". So I either need a way to
>> tell my backup to ignore the free space(this would be
>> preferable, but I dont this its possible) or tell my
>> shrink proc to see if the Log file is not in use.
>> TIA, Chris
>
>.
>|||Have you updated the used space information?
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:018e01c3dc54$5ee62310$a101280a@.phx.gbl...
> Then why do I have a backup thats 12.7 gigs when the used
> space is about 9.7 gigs?
>
> >--Original Message--
> >Backup does not put your free space into the backup
> file. So don't worry
> >about that.
> >
> >hth
> >
> >Quentin
> >
> >"chris" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:09ff01c3dc46$b4e9d2b0$a601280a@.phx.gbl...
> >> sql2k sp3
> >>
> >> Ive got a db with almost 4 gig of free space. I created
> a
> >> proc that attempted to shrink it last night before doing
> >> my backup so as to not backup this space. But I got the
> >> message "Cannot shrink log file 2 (bla_log) because all
> >> logical log files are in use". So I either need a way to
> >> tell my backup to ignore the free space(this would be
> >> preferable, but I dont this its possible) or tell my
> >> shrink proc to see if the Log file is not in use.
> >>
> >> TIA, Chris
> >
> >
> >.
> >|||Yes.
"Quentin Ran" <ab@.who.com> wrote in message
news:#9DozhG3DHA.2296@.TK2MSFTNGP11.phx.gbl...
> Have you updated the used space information?
> "chris" <anonymous@.discussions.microsoft.com> wrote in message
> news:018e01c3dc54$5ee62310$a101280a@.phx.gbl...
> > Then why do I have a backup thats 12.7 gigs when the used
> > space is about 9.7 gigs?
> >
> >
> > >--Original Message--
> > >Backup does not put your free space into the backup
> > file. So don't worry
> > >about that.
> > >
> > >hth
> > >
> > >Quentin
> > >
> > >"chris" <anonymous@.discussions.microsoft.com> wrote in
> > message
> > >news:09ff01c3dc46$b4e9d2b0$a601280a@.phx.gbl...
> > >> sql2k sp3
> > >>
> > >> Ive got a db with almost 4 gig of free space. I created
> > a
> > >> proc that attempted to shrink it last night before doing
> > >> my backup so as to not backup this space. But I got the
> > >> message "Cannot shrink log file 2 (bla_log) because all
> > >> logical log files are in use". So I either need a way to
> > >> tell my backup to ignore the free space(this would be
> > >> preferable, but I dont this its possible) or tell my
> > >> shrink proc to see if the Log file is not in use.
> > >>
> > >> TIA, Chris
> > >
> > >
> > >.
> > >
>

how to surpress a server error msg?

background sql2k on nt5.
when using stored procedure sp_helprotect against a login. I've got the
following msg in query analyzer messages tab.
Server: Msg 15330, Level 11, State 1, Procedure sp_helprotect, Line 346
There are no matching rows on which to report.
is it possible to surpress that msg and just display the Grid tab?
thank you.
steveThat would be a client issue, but I assume that you are using QA and it does
n't have any awareness of any
particular stored procedures; it just displays whatever error comes back. On
e option can be to copy the source
code for the proc and modify it so it doesn't return that error message (I a
ssume that there's a RAISERROR
with that error somewhere in the source code).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message news:%23xdrpj0PEHA.640@.TK2MSFTN
GP12.phx.gbl...
> background sql2k on nt5.
> when using stored procedure sp_helprotect against a login. I've got the
> following msg in query analyzer messages tab.
> Server: Msg 15330, Level 11, State 1, Procedure sp_helprotect, Line 346
> There are no matching rows on which to report.
>
> is it possible to surpress that msg and just display the Grid tab?
> thank you.
>
> steve
>|||well, i'd rather not to change the system stored procedure since that would
mean i have to change that particular sp on all servers!
i'd prefer a solution to control the display.
i was calling the sp thru SQL pass thru in Access and return the query
result to display in an Access report. (the query set, not the error msg)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OExOyt0PEHA.2236@.TK2MSFTNGP10.phx.gbl...
> That would be a client issue, but I assume that you are using QA and it
doesn't have any awareness of any
> particular stored procedures; it just displays whatever error comes back.
One option can be to copy the source
> code for the proc and modify it so it doesn't return that error message (I
assume that there's a RAISERROR
> with that error somewhere in the source code).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:%23xdrpj0PEHA.640@.TK2MSFTNGP12.phx.gbl...
>|||As this is a client issue, I suggest you post the question to an Access grou
p. there might be some option in
Access to suppress error messages...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message news:eiigD40PEHA.3944@.tk2msftng
p13.phx.gbl...
> well, i'd rather not to change the system stored procedure since that woul
d
> mean i have to change that particular sp on all servers!
> i'd prefer a solution to control the display.
> i was calling the sp thru SQL pass thru in Access and return the query
> result to display in an Access report. (the query set, not the error msg)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:OExOyt0PEHA.2236@.TK2MSFTNGP10.phx.gbl...
> doesn't have any awareness of any
> One option can be to copy the source
> assume that there's a RAISERROR
> news:%23xdrpj0PEHA.640@.TK2MSFTNGP12.phx.gbl...
>|||> well, i'd rather not to change the system stored procedure since that
would
> mean i have to change that particular sp on all servers!
What if you found a more serious error in your stored procedure? You should
be setting up your servers so that deploying changes is not so convoluted
and difficult that you find yourself looking for more convenient
workarounds. IMHO.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/