script USE help

I have a script that has a local variable called @.DB which holds the name of
the database to use, like this:
DECLARE
@.dbname varchar (256);
set @.dbname = 'DBNAME_REPLACEONINSTALL'
Our GUI installation modifies the value of this script. This is validated by
testing the value of the variable, using:
IF @.dbname like '%REPLACEONINSTALL%'
RAISERROR('Script is invalid. Must be run through GUI installer or local
variables at top of script must be replaced manually. Exiting database
create', 20, 1) WITH LOG;
else
select 'Script validated, proceeding' as STEP;
I then issue the USE command, using:
use @.DBNAME
DDL SQL..
DDL SQL..
DDL SQL..
The script fails due to this:
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '@.dbname'.
I am guessing that you cannot use local variables in a USE command. Is this
true?
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:67AAD8FB-EDA2-4CE7-BDAC-93E8EC322185@.microsoft.com...
> I am guessing that you cannot use local variables in a USE command. Is
this
> true?
That is correct... You'll probably have to use dynamic SQL with the USE and
the DDL in a single batch.

script USE help

I have a script that has a local variable called @.DB which holds the name of
the database to use, like this:
DECLARE
@.dbname varchar (256);
set @.dbname = 'DBNAME_REPLACEONINSTALL'
Our GUI installation modifies the value of this script. This is validated by
testing the value of the variable, using:
IF @.dbname like '%REPLACEONINSTALL%'
RAISERROR('Script is invalid. Must be run through GUI installer or local
variables at top of script must be replaced manually. Exiting database
create', 20, 1) WITH LOG;
else
select 'Script validated, proceeding' as STEP;
I then issue the USE command, using:
use @.DBNAME
DDL SQL..
DDL SQL..
DDL SQL..
The script fails due to this:
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '@.dbname'.
I am guessing that you cannot use local variables in a USE command. Is this
true?"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:67AAD8FB-EDA2-4CE7-BDAC-93E8EC322185@.microsoft.com...
> I am guessing that you cannot use local variables in a USE command. Is
this
> true?
That is correct... You'll probably have to use dynamic SQL with the USE and
the DDL in a single batch.sql

Script to search for a string in all varchar columns in all tables in a database?

I have a string which I need to know where it came from in a database.
I don't want to spend time coding this so is there a ready made script
which takes a string as a parameter and searches all the tables which
contain varchar type columns and searches these columns and indicate which
tables contain that string?

Full text search is not enabled.

--
Tony
http://dotNet-Hosting.com - Super low $4.75/month.
Single all inclusive features plan with MS SQL Server, MySQL 5, ASP.NET,
PHP 5 & webmail support.http://vyaskn.tripod.com/search_all..._all_tables.htm

HTH, Jens Suessmeyer

Script Component Task: function that returns last word in a string

I have a field called CustomerName Varchar 100 and I wish to write a function that can do the following in a script component task

create a function called CleanString (ByVal CustomerName as String) As String

CleanString Returns the last word of a Customer name if the CustomerName field contains more than one word or if the CustomerName field does not contain Corp or Ltd

ie parse 'Mr John Tools' and the function returns 'Tools'

ie parse 'TechnicalBooks' and the function returns 'TechnicalBooks'

ie parse 'Microsoft Corp' return 'Microsoft Corp'

ie parse 'Digidesign Ltd' return 'Digidesign Ltd'

Any idea of a regular expression or existing piece of existing code I can have

thanks in advance

dave

I believe you'd want something along the lines of:

\s*(\w+(\s+(Corp|Ltd))?)$

Public Function CleanString(ByVal CustomerName As String) As String

Dim re As New Regex("\s*(\w+(\s+(Corp|Ltd))?)$")

Dim m As Match

m = re.Match(CustomerName)

' insert sanity to make sure we had a match

' group 0 would be the full string

' group 1 is the one we want

Dim g As Group = m.Groups(1)

Dim cc As CaptureCollection = g.Captures

CleanString = cc(0).Value

End Function

|||

Matt,

Thanks the code almost worked--I had to rewrite it like this. However I need help to tweak the regular expression Regex("\s*(\w+(\s+(Corp|Ltd))?)$") Im getting the following results In/Out

Input: Digidesign Australasia Pty Ltd Output: Pty Ltd --wrong should be Ltd

Input: Ms S Aiten Output: Aiten --correct

Function CleanString(ByVal CustomerName As String) As String

m = re.Match(CustomerName)

' group 0 would be the full string

' group 1 is the one we want

CleanString = m.Groups(1).Value

End Function

'Called by

Public Class ScriptMain

Inherits UserComponent

Dim re As Regex = New Regex("\s*(\w+(\s+(Corp|Ltd))?)$")

Dim m As Match

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

If Len(Row.CUSTOMERNAME) > 0 Then

Output0Buffer.oCUSTOMERNAME = Row.CUSTOMERNAME.ToString

Output0Buffer.oSearchName = CleanString(Row.CUSTOMERNAME.ToString)

End If

|||I don't think you can easily do this with a single regex. You might want to pre-process the string to remove the Pty first, or have a special case that captures the words you want to the left and right of "Pty" whenever the string appears in your customer name.

Script

Hi Folks,
I need a script that it'll changed varchar field to nvarchar a table which i
pass it's name via parameter.
I generated it but it'll raise exception when a field has foreign key etc...
I don't want to spend more time to this work. Do you have a script like
this? Or program?
If you have it could you share with me?
Thanks.
Hi
If you try and change a column's data type and the column is referenced by a
foreign key, or is part of a primary key will give you an error. You will
need to drop these before trying to change the columns datatype. The easiest
way would be to script them, drop them and re-create them afterwards. To drop
them see http://www.mssqlserver.com/scripts/drop_keys.sql
John
"Erencan SADIRODLU" wrote:

> Hi Folks,
> I need a script that it'll changed varchar field to nvarchar a table which i
> pass it's name via parameter.
> I generated it but it'll raise exception when a field has foreign key etc...
> I don't want to spend more time to this work. Do you have a script like
> this? Or program?
> If you have it could you share with me?
> Thanks.
>
>
|||Thanks John, but i want to foreign keys add again after change data type.
This script just drop foreign keys.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:55E6E65D-A653-4620-A2D3-C4BEB4A224AC@.microsoft.com...[vbcol=seagreen]
> Hi
> If you try and change a column's data type and the column is referenced by
> a
> foreign key, or is part of a primary key will give you an error. You will
> need to drop these before trying to change the columns datatype. The
> easiest
> way would be to script them, drop them and re-create them afterwards. To
> drop
> them see http://www.mssqlserver.com/scripts/drop_keys.sql
> John
> "Erencan SADIRODLU" wrote:
|||Hi
But you could script them first, which is what I indicated in the response.
There are several ways to do this, as this is a one off job use Enterprise
Manager. If you take a backup before making any changes and restore the
database under a different name, you could use a tool such as Red Gate's SQL
Compare to check the differences (in fact you could use this to re-apply the
missing FKs and PKs).
John
"Erencan SADIRODLU" wrote:

> Thanks John, but i want to foreign keys add again after change data type.
> This script just drop foreign keys.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:55E6E65D-A653-4620-A2D3-C4BEB4A224AC@.microsoft.com...
>
>

Script

Hi Folks,
I need a script that it'll changed varchar field to nvarchar a table which i
pass it's name via parameter.
I generated it but it'll raise exception when a field has foreign key etc...
I don't want to spend more time to this work. Do you have a script like
this? Or program?
If you have it could you share with me?
Thanks.Hi
If you try and change a column's data type and the column is referenced by a
foreign key, or is part of a primary key will give you an error. You will
need to drop these before trying to change the columns datatype. The easiest
way would be to script them, drop them and re-create them afterwards. To drop
them see http://www.mssqlserver.com/scripts/drop_keys.sql
John
"Erencan SAÃ?IROÃ?LU" wrote:
> Hi Folks,
> I need a script that it'll changed varchar field to nvarchar a table which i
> pass it's name via parameter.
> I generated it but it'll raise exception when a field has foreign key etc...
> I don't want to spend more time to this work. Do you have a script like
> this? Or program?
> If you have it could you share with me?
> Thanks.
>
>|||Thanks John, but i want to foreign keys add again after change data type.
This script just drop foreign keys. :(
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:55E6E65D-A653-4620-A2D3-C4BEB4A224AC@.microsoft.com...
> Hi
> If you try and change a column's data type and the column is referenced by
> a
> foreign key, or is part of a primary key will give you an error. You will
> need to drop these before trying to change the columns datatype. The
> easiest
> way would be to script them, drop them and re-create them afterwards. To
> drop
> them see http://www.mssqlserver.com/scripts/drop_keys.sql
> John
> "Erencan SADIRODLU" wrote:
>> Hi Folks,
>> I need a script that it'll changed varchar field to nvarchar a table
>> which i
>> pass it's name via parameter.
>> I generated it but it'll raise exception when a field has foreign key
>> etc...
>> I don't want to spend more time to this work. Do you have a script like
>> this? Or program?
>> If you have it could you share with me?
>> Thanks.
>>|||Hi
But you could script them first, which is what I indicated in the response.
There are several ways to do this, as this is a one off job use Enterprise
Manager. If you take a backup before making any changes and restore the
database under a different name, you could use a tool such as Red Gate's SQL
Compare to check the differences (in fact you could use this to re-apply the
missing FKs and PKs).
John
"Erencan SAÃ?IROÃ?LU" wrote:
> Thanks John, but i want to foreign keys add again after change data type.
> This script just drop foreign keys. :(
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:55E6E65D-A653-4620-A2D3-C4BEB4A224AC@.microsoft.com...
> > Hi
> >
> > If you try and change a column's data type and the column is referenced by
> > a
> > foreign key, or is part of a primary key will give you an error. You will
> > need to drop these before trying to change the columns datatype. The
> > easiest
> > way would be to script them, drop them and re-create them afterwards. To
> > drop
> > them see http://www.mssqlserver.com/scripts/drop_keys.sql
> >
> > John
> >
> > "Erencan SADIRODLU" wrote:
> >
> >> Hi Folks,
> >> I need a script that it'll changed varchar field to nvarchar a table
> >> which i
> >> pass it's name via parameter.
> >> I generated it but it'll raise exception when a field has foreign key
> >> etc...
> >> I don't want to spend more time to this work. Do you have a script like
> >> this? Or program?
> >>
> >> If you have it could you share with me?
> >> Thanks.
> >>
> >>
> >>
>
>

Script

Hi Folks,
I need a script that it'll changed varchar field to nvarchar a table which i
pass it's name via parameter.
I generated it but it'll raise exception when a field has foreign key etc...
I don't want to spend more time to this work. Do you have a script like
this? Or program?
If you have it could you share with me?
Thanks.Hi
If you try and change a column's data type and the column is referenced by a
foreign key, or is part of a primary key will give you an error. You will
need to drop these before trying to change the columns datatype. The easies
t
way would be to script them, drop them and re-create them afterwards. To dro
p
them see http://www.mssqlserver.com/scripts/drop_keys.sql
John
"Erencan SADIRODLU" wrote:

> Hi Folks,
> I need a script that it'll changed varchar field to nvarchar a table which
i
> pass it's name via parameter.
> I generated it but it'll raise exception when a field has foreign key etc.
.
> I don't want to spend more time to this work. Do you have a script like
> this? Or program?
> If you have it could you share with me?
> Thanks.
>
>|||Thanks John, but i want to foreign keys add again after change data type.
This script just drop foreign keys.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:55E6E65D-A653-4620-A2D3-C4BEB4A224AC@.microsoft.com...[vbcol=seagreen]
> Hi
> If you try and change a column's data type and the column is referenced by
> a
> foreign key, or is part of a primary key will give you an error. You will
> need to drop these before trying to change the columns datatype. The
> easiest
> way would be to script them, drop them and re-create them afterwards. To
> drop
> them see http://www.mssqlserver.com/scripts/drop_keys.sql
> John
> "Erencan SADIRODLU" wrote:
>|||Hi
But you could script them first, which is what I indicated in the response.
There are several ways to do this, as this is a one off job use Enterprise
Manager. If you take a backup before making any changes and restore the
database under a different name, you could use a tool such as Red Gate's SQL
Compare to check the differences (in fact you could use this to re-apply the
missing FKs and PKs).
John
"Erencan SADIRODLU" wrote:

> Thanks John, but i want to foreign keys add again after change data type.
> This script just drop foreign keys.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:55E6E65D-A653-4620-A2D3-C4BEB4A224AC@.microsoft.com...
>
>

Script

Hi Folks,
I need a script that it'll changed varchar field to nvarchar a table which i
pass it's name via parameter.
I generated it but it'll raise exception when a field has foreign key etc...
I don't want to spend more time to this work. Do you have a script like
this? Or program?
If you have it could you share with me?
Thanks.
Hi
If you try and change a column's data type and the column is referenced by a
foreign key, or is part of a primary key will give you an error. You will
need to drop these before trying to change the columns datatype. The easiest
way would be to script them, drop them and re-create them afterwards. To drop
them see http://www.mssqlserver.com/scripts/drop_keys.sql
John
"Erencan SADIRODLU" wrote:

> Hi Folks,
> I need a script that it'll changed varchar field to nvarchar a table which i
> pass it's name via parameter.
> I generated it but it'll raise exception when a field has foreign key etc...
> I don't want to spend more time to this work. Do you have a script like
> this? Or program?
> If you have it could you share with me?
> Thanks.
>
>
|||Thanks John, but i want to foreign keys add again after change data type.
This script just drop foreign keys.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:55E6E65D-A653-4620-A2D3-C4BEB4A224AC@.microsoft.com...[vbcol=seagreen]
> Hi
> If you try and change a column's data type and the column is referenced by
> a
> foreign key, or is part of a primary key will give you an error. You will
> need to drop these before trying to change the columns datatype. The
> easiest
> way would be to script them, drop them and re-create them afterwards. To
> drop
> them see http://www.mssqlserver.com/scripts/drop_keys.sql
> John
> "Erencan SADIRODLU" wrote:
|||Hi
But you could script them first, which is what I indicated in the response.
There are several ways to do this, as this is a one off job use Enterprise
Manager. If you take a backup before making any changes and restore the
database under a different name, you could use a tool such as Red Gate's SQL
Compare to check the differences (in fact you could use this to re-apply the
missing FKs and PKs).
John
"Erencan SADIRODLU" wrote:

> Thanks John, but i want to foreign keys add again after change data type.
> This script just drop foreign keys.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:55E6E65D-A653-4620-A2D3-C4BEB4A224AC@.microsoft.com...
>
>

Script

Hi Folks,
I need a script that it'll changed varchar field to nvarchar a table which i
pass it's name via parameter.
I generated it but it'll raise exception when a field has foreign key etc...
I don't want to spend more time to this work. Do you have a script like
this? Or program?
If you have it could you share with me?
Thanks.
Hi
If you try and change a column's data type and the column is referenced by a
foreign key, or is part of a primary key will give you an error. You will
need to drop these before trying to change the columns datatype. The easiest
way would be to script them, drop them and re-create them afterwards. To drop
them see http://www.mssqlserver.com/scripts/drop_keys.sql
John
"Erencan SADIRODLU" wrote:

> Hi Folks,
> I need a script that it'll changed varchar field to nvarchar a table which i
> pass it's name via parameter.
> I generated it but it'll raise exception when a field has foreign key etc...
> I don't want to spend more time to this work. Do you have a script like
> this? Or program?
> If you have it could you share with me?
> Thanks.
>
>
|||Thanks John, but i want to foreign keys add again after change data type.
This script just drop foreign keys.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:55E6E65D-A653-4620-A2D3-C4BEB4A224AC@.microsoft.com...[vbcol=seagreen]
> Hi
> If you try and change a column's data type and the column is referenced by
> a
> foreign key, or is part of a primary key will give you an error. You will
> need to drop these before trying to change the columns datatype. The
> easiest
> way would be to script them, drop them and re-create them afterwards. To
> drop
> them see http://www.mssqlserver.com/scripts/drop_keys.sql
> John
> "Erencan SADIRODLU" wrote:
|||Hi
But you could script them first, which is what I indicated in the response.
There are several ways to do this, as this is a one off job use Enterprise
Manager. If you take a backup before making any changes and restore the
database under a different name, you could use a tool such as Red Gate's SQL
Compare to check the differences (in fact you could use this to re-apply the
missing FKs and PKs).
John
"Erencan SADIRODLU" wrote:

> Thanks John, but i want to foreign keys add again after change data type.
> This script just drop foreign keys.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:55E6E65D-A653-4620-A2D3-C4BEB4A224AC@.microsoft.com...
>
>