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 find out the number of row in all the table of a database.

HI,
I am using following script to find out the number of row in all the
table of a database is there any simple way out? if so pls mail me
declare @.TAB VARCHAR (20),
@.qu nvarchar (100)
DECLARE TABALE CURSOR FOR
select name from sysobjects where xtype='u' order by name open tabale
FETCH NEXT FROM TABALE INTO @.TAB while @.@.fetch_status = 0 begin --SET
@.TAB = 'SALES1'
--select name from sysobjects where name = @.tab SET @.QU ='SELECT
COUNT(*) FROM '+@.TAB print @.tab EXEC sp_executesql @.QU fetch next from
TABALE INTO @.TAB end close tabale deallocate tabale
Thanks
Sajid ChhapekarYOu could use the undocumented procedure sp_msforeachtable, but keep in
mind that this one is undocumented and might be deprecated in further
versions of SQL Server.
sp_msforeachtable 'SELECT ''?'' as TableName COUNT(*) AS Counted_rows
FROM ?'
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Hello,
Try the following query to get your row counts without having to use a cursor.
SELECT sysobjects.name, rows FROM Sysindexes
INNER JOIN Sysobjects
on Sysindexes.id = sysobjects.id
AND indid < 2
AND sysobjects.xtype = 'u'
AND sysobjects.name <> 'dtproperties'
You should get the same result as your cursor.
Thanks Kllyj64
"csajid@.gmail.com" wrote:
> HI,
> I am using following script to find out the number of row in all the
> table of a database is there any simple way out? if so pls mail me
>
> declare @.TAB VARCHAR (20),
> @.qu nvarchar (100)
> DECLARE TABALE CURSOR FOR
> select name from sysobjects where xtype='u' order by name open tabale
> FETCH NEXT FROM TABALE INTO @.TAB while @.@.fetch_status = 0 begin --SET
> @.TAB = 'SALES1'
> --select name from sysobjects where name = @.tab SET @.QU ='SELECT
> COUNT(*) FROM '+@.TAB print @.tab EXEC sp_executesql @.QU fetch next from
> TABALE INTO @.TAB end close tabale deallocate tabale
>
> Thanks
> Sajid Chhapekar
>|||Hi,
That's great. Thanks for this.
Thanks and regards,
Sajid.
kllyj64 wrote:
> Hello,
> Try the following query to get your row counts without having to use a cursor.
> SELECT sysobjects.name, rows FROM Sysindexes
> INNER JOIN Sysobjects
> on Sysindexes.id = sysobjects.id
> AND indid < 2
> AND sysobjects.xtype = 'u'
> AND sysobjects.name <> 'dtproperties'
> You should get the same result as your cursor.
>
> --
> Thanks Kllyj64
>
> "csajid@.gmail.com" wrote:
> > HI,
> >
> > I am using following script to find out the number of row in all the
> > table of a database is there any simple way out? if so pls mail me
> >
> >
> > declare @.TAB VARCHAR (20),
> > @.qu nvarchar (100)
> >
> > DECLARE TABALE CURSOR FOR
> > select name from sysobjects where xtype='u' order by name open tabale
> > FETCH NEXT FROM TABALE INTO @.TAB while @.@.fetch_status = 0 begin --SET
> > @.TAB = 'SALES1'
> > --select name from sysobjects where name = @.tab SET @.QU ='SELECT
> > COUNT(*) FROM '+@.TAB print @.tab EXEC sp_executesql @.QU fetch next from
> > TABALE INTO @.TAB end close tabale deallocate tabale
> >
> >
> > Thanks
> > Sajid Chhapekar
> >
> >

Script task: Bindingsource

I used a binding source in my script task codes to filter the data table. when i used that, even if i declare it (dim bs as bindingsource), i'm still having an error - "type bindingsource is not defined".

cherrie

Hi, bindingsource is part of the forms name space. Does your code include

Imports System.Windows.Forms

Script component and variable!!

Hi All,

I have developed a simple package utilizing script component and variable. What I am trying to do is declare a variable in the parent package that can be change at run time to either 1 or 0. If 1 I want the package to succeed and branch off to run some child packages and if 0, then branch the other way and run another child package. In ether way, I want to be able to load one of the two child packages and not both.

I I declare a variable in my parent package like IsExist, int32 with default 0.

Then I have this code thanks partly to Jamie Thomson in " SSIS: Writing to a variable from a script task"

Public Sub Main()

'

' Add your code here

'

Dim t As Integer

Dim vars As Variables

Dts.VariableDispenser.LockOneForWrite("IsExists", vars)

t = CInt((Dts.Variables("IsExists").Value))

'Dts.Variables(IsExists).Value = t

If t >= 1 Then

vars.Unlock()

Dts.TaskResult = Dts.Results.Success

Else

Dts.TaskResult = Dts.Results.Failure

End If

End Sub

End Class

I also place the variable name in the ReadWriteVariables in the script component.

The problem now is it is always failing. It stopped at the script task and fail. This is not what I want. I want the package to execute another task upon failure. Anyone knows what I am doing wrong?

Hi,

You can achieve this without using a script task. Simply use expressions on your precedence constraints to check the value of IsExists. Here's how: http://www.sqlis.com/default.aspx?306

Oh, and you may want to make IsExists a boolean variable as well.

-Jamie

|||

Thanks alot Jamie. I tried using the Precedence constraint by seting the @.IsExists to 1 on one flow and 0 on the other but it is always running the one for 0. This is without using script task.

Again, when I use a script task with the following code, it gave me bunch of errors:

Public Sub Main()

Dim vs As Variables

'We need to lock the variables so we can read it without anything else changing it
Dts.VariableDispenser.LockOneForWrite("IsExists", vs)

'Assign it a value
vs.Item("IsExists").Value = (Dts.Variables("IsExists").Value)

'remember to unlock the variable now
vs.Unlock()

Dts.TaskResult = Dts.Results.Success
End Sub

Thanks

Omon

|||

OK, so you're using the script task to set the variable as well. I see.

What errors are you getting?

|||

Thanks jamie,

Yes. I used below script to set the value:

Dim vs As Variables

Dim t As Boolean

'MsgBox("variable_Passed)

t = CBool((Dts.Variables("IsExists").Value))

If t = True Then

'MsgBox("Condition_Stage")

Dts.TaskResult = Dts.Results.Success

'MsgBox("Success")

Else

Dts.TaskResult = Dts.Results.Failure

'MsgBox("Failure")

End If

So I was getting conversion error and when I placed CBool in front of the IsExists like: CBool((Dts.Variables("IsExists").Value)) it worked. I had to set

LockOneForWrite in the script task instead of using the code.

Again, I also had to select Logical OR in the presedent editor . This fix my problem.

I think I am all good for now.

Thanks very much.

Omon