ITW wont save script file

Hi all,

I am running the ITW against several trace files that were created from a single trace file. I only have the following problem when attempting to save the script file from the analysis of any one of the multiple trace files. I have used the ITW against single trace files with no problem.

Here's the problem:
Everything works fine until I select the "Save Script File" option. After selecting this option, designating a file name to save to, and then clicking the "Next" button, the application appears to hang up. It displays an hourglass pointer, as though it is working, but just sits there forever. Also, when I look at the task manager this application reports that it is "not responding". I have allowed it to sit there in this state for several hours before finaly killing the process. Is there some additional configuration or method required to analyze multiple trace files from a single trace session?

Any suggestions would be greatly appreciated.

Thanks,
RickThis could be a bug. You might want to contact PSS.

Iterating the rows of a script component

Hi There,

Can someone please let me know what is the best way to iterate the output rows of a script component and stick in those ids in a where clause of a select query (to retrieve additional info from a database)? Is this possible at all? If not, what is the best way to deal with this situation?

Thanks a lot!!

Sam_res03 wrote:

Hi There,

Can someone please let me know what is the best way to iterate the output rows of a script component and stick in those ids in a where clause of a select query (to retrieve additional info from a database)? Is this possible at all? If not, what is the best way to deal with this situation?

Thanks a lot!!

Any particular reason you aren't posting in your other thread on this topic? This is a dup.|||

No particular reason. I just thought I wasn't specific about my problem. I didn't know how to modify the title. I am in pressure to finish off a task and didn't take time to explore how i could modify the title of the post.

Thanks.

|||

Sam_res03 wrote:

Hi There,

Can someone please let me know what is the best way to iterate the output rows of a script component and stick in those ids in a where clause of a select query (to retrieve additional info from a database)? Is this possible at all? If not, what is the best way to deal with this situation?

Thanks a lot!!

Probably the best way is to push the data into recordset destination and then loop over them with a ForEach loop.

Hopefully this will help:

Execute SQL Task into an object variable - Shred it with a Foreach loop
(http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx)

In this example the recordset is populated by an Execute SQL Task rather than a recordsset destinaiton, but teh principle thereafter is the same I think.

-Jamie

|||

Jamie,

Thanks a lot for your reply. I think I am almost there...So I have one one column in the recordset. Is this what I should do?

By using a for each loop container, with foreach ado enumerator I can get the id and store in a variable.

Next can I have a dataflow task in the foreach loop...

and in that dataflow task, can I access this id pass it as a variable to an sql command (in the ole db source). The sql command is a select statement with a where clause.I am unable to figure out how that can be done.

I really appreciate your response....

Thanks a lot!!

|||

Sam_res03 wrote:

Jamie,

Thanks a lot for your reply. I think I am almost there...So I have one one column in the recordset. Is this what I should do?

By using a for each loop container, with foreach ado enumerator I can get the id and store in a variable.

Next can I have a dataflow task in the foreach loop...

and in that dataflow task, can I access this id pass it as a variable to an sql command (in the ole db source). The sql command is a select statement with a where clause.I am unable to figure out how that can be done.

I really appreciate your response....

Thanks a lot!!

You're right so far. You have two options to use your value in your SQL statement:

1) Use '?' parameters

2) Use expressions. Some info here: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/SSIS_3A00_-Using-dynamic-SQL-in-an-OLE-DB-Source-component.aspx

-Jamie

|||

Hi Jamie,

I took your approach and I get this error..

Error: ForEach Variable Mapping number 1 to variable cannot be applied.

I tried to use a foreach loop container. I mapped index 0 to a user variable of type integer. IN the script component I have the row object cast as an integer.

I also get this error..

Error: The type of the value being assigned to variable "User::ID" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Do you see what mistake I am doing? Can you please reply?

Thanks.

|||

What's the SQL statement? What's the type of the first column?

-Jamie

|||

Type of the first column is Integer.

For some reason, if I try to map it to a variable of type integer, it gives me an error. But if I try to map it to a variable of type object it doesn't complain. And with the help of msgbox scrript component i am able to see the ids. But the problem I am getting to is..if the variable is of type object, how can I create an expression with that object (in the where clause)and use it as a variable for sql command with variable.

Are you getting my problem? Please reply.

Thanks a lot for bearing with me.

|||

Sam_res03 wrote:

Type of the first column is Integer.

For some reason, if I try to map it to a variable of type integer, it gives me an error. But if I try to map it to a variable of type object it doesn't complain. And with the help of msgbox scrript component i am able to see the ids. But the problem I am getting to is..if the variable is of type object, how can I create an expression with that object (in the where clause)and use it as a variable for sql command with variable.

Are you getting my problem? Please reply.

Thanks a lot for bearing with me.

Hi Sam,

You can set up another variable which uses expressions that casts the object variable to an integer variable (e.g., the expression would look like "<DT_I4> @.[User::objvariable]" for an 4-byte signed integer which maps to the standard Int32 integer). Then you can use that in your where clause.

I do have a question... what is the exact type of your integer column? If it's bigint, it really won't work with a standard Integer type... and you have to use <DT_I8> instead.|||

Jon Limjap wrote:

Sam_res03 wrote:

Type of the first column is Integer.

For some reason, if I try to map it to a variable of type integer, it gives me an error. But if I try to map it to a variable of type object it doesn't complain. And with the help of msgbox scrript component i am able to see the ids. But the problem I am getting to is..if the variable is of type object, how can I create an expression with that object (in the where clause)and use it as a variable for sql command with variable.

Are you getting my problem? Please reply.

Thanks a lot for bearing with me.

Hi Sam,

You can set up another variable which uses expressions that casts the object variable to an integer variable (e.g., the expression would look like "<DT_I4> @.[User::objvariable]" for an 4-byte signed integer which maps to the standard Int32 integer). Then you can use that in your where clause.

I do have a question... what is the exact type of your integer column? If it's bigint, it really won't work with a standard Integer type... and you have to use <DT_I8> instead.

Yeah, Jon is right. If I were you I would investigate why you can't store it in an Integer variable. Try Int16, Int32 and Int64.

-Jamie

|||

Thanks Jamie. I will investigate it. By the way, if I have a dataflow task(source and the destination) in a foreach loop container and if the number of items it is iterating is x items , does it mean that it will make x calls to the source database?

Thanks.

|||

Thanks Jon. I really appreciate your response. I will try your trick and will let you know soon.

|||

Sam_res03 wrote:

Thanks Jamie. I will investigate it. By the way, if I have a dataflow task(source and the destination) in a foreach loop container and if the number of items it is iterating is x items , does it mean that it will make x calls to the source database?

Yes.

|||

Then that is not an efficient way either making 1200 calls. The only reason i tried to do this was, I didn't want to scan a table with 600k rows and do a look up in a temp table for the needed 1200 rows. I wanted to scan only for these 1200 rows/ids. Is there a way to achieve this? If I have the ids in an object, would i be able to concatenate those ids into a string varible and use that sring variable in a where clause of a select statement? I am getting this data from an Oracle DB. Is there any limitation on using a where clause in a select statement. If so, what is the other to solve this problem?

Thanks Jamie. I really appreciate your response. Thanks a lot!!

issues writing a deployment script

In this post, i asked if there were any tutorials available on using the RS command-line utility. I have since found some that steered me in the right direction.

This guy demonstrates how to render a report by passing a scripty-VB file with extension .rss into the utility :

http://www.sqljunkies.com/Article/B197B9E7-EF3D-4D70-9B5E-47B74E57EF38.scuk

And this guy explains some of the underlying principles behind this marvel of modern software engineering :

http://odetocode.com/Blogs/scott/archive/2005/01/30/975.aspx

What I'm trying to do is build a script that deploys my whole suite of reports to the report server. I've built a test class that mirrors the functionality of my RSS script, because error handling in the RS utility distills every error message to this beauty :
error BC30456: 'Main' is not a member of '_ScriptClass'.

When I run my VB class, I'm consistently getting "http state 401 : Access Denied" when I make an rs call. For instance my create statement

rs.CreateReport(report, serverPath, True, definition, Nothing)

and K. Scott Allen's simple

rs.GetSystemPermissions()

Both return 401. I'm assuming there are a dozen layers of security I have yet to uncover in my quest. My question : why do I have permissions to do this through my browser but not in code? And then, which specific hoops must i jump through in order to give myself access to do this?

I've discovered i can dodge this problem by running the command prompt window as a user who has Administrator privileges on the server. This is what my shortcut looks like :

%windir%\system32\runas.exe /profile /user:DOMAIN\AdminUser /savecred %windir%\system32\cmd.exe

Of course the preferred way to accomplish this would be to give myself access without giving myself Admin privileges. Any thoughts?

|||What is the command line you are using to execute rs.exe?|||rs -i DeployAllFinancialReports.rss -s http://ServerName/ReportServer

Issue with restore to new dbname on same server

I ran this script to backup to restore a backup onto a database with a new
name - on the same server.
USE master
> RESTORE DATABASE DBnew
> FROM DISK = 'E:\MSSQL7\BACKUP\DBold_db_200409270201.BAK'
> with RECOVERY,
> MOVE 'DBold_Data' TO 'E:\MSSQL7\data\DBnew_Data.MDF',
> MOVE 'DBold_Log' TO 'E:\MSSQL7\data\DBnew_Log.LDF',
> REPLACE
When I look at the properties for the database under em, I see that both the
original database and the copy-of-it-with-a-new-name have the same "File
Name" (DBold_Data and DBold_Log, respectively) under the "Data Files" and
"Transaction Files" tabs. The "Location", which points to the actual
physical file, is correctly and uniquely named.
Is the fact that these have the same File Name going to confuse SQL Server
when it goes to update a database?You should be fine.
"MarkAurit" <MarkAurit@.discussions.microsoft.com> wrote in message
news:59CC2C26-13F8-4664-99F1-CC550827A9D4@.microsoft.com...
>I ran this script to backup to restore a backup onto a database with a new
> name - on the same server.
> USE master
> When I look at the properties for the database under em, I see that both
> the
> original database and the copy-of-it-with-a-new-name have the same "File
> Name" (DBold_Data and DBold_Log, respectively) under the "Data Files" and
> "Transaction Files" tabs. The "Location", which points to the actual
> physical file, is correctly and uniquely named.
> Is the fact that these have the same File Name going to confuse SQL Server
> when it goes to update a database?

Issue with restore to new dbname on same server

I ran this script to backup to restore a backup onto a database with a new
name - on the same server.
USE master
> RESTORE DATABASE DBnew
> FROM DISK = 'E:\MSSQL7\BACKUP\DBold_db_200409270201.BAK'
> with RECOVERY,
> MOVE 'DBold_Data' TO 'E:\MSSQL7\data\DBnew_Data.MDF',
> MOVE 'DBold_Log' TO 'E:\MSSQL7\data\DBnew_Log.LDF',
> REPLACE
When I look at the properties for the database under em, I see that both the
original database and the copy-of-it-with-a-new-name have the same "File
Name" (DBold_Data and DBold_Log, respectively) under the "Data Files" and
"Transaction Files" tabs. The "Location", which points to the actual
physical file, is correctly and uniquely named.
Is the fact that these have the same File Name going to confuse SQL Server
when it goes to update a database?
You should be fine.
"MarkAurit" <MarkAurit@.discussions.microsoft.com> wrote in message
news:59CC2C26-13F8-4664-99F1-CC550827A9D4@.microsoft.com...
>I ran this script to backup to restore a backup onto a database with a new
> name - on the same server.
> USE master
> When I look at the properties for the database under em, I see that both
> the
> original database and the copy-of-it-with-a-new-name have the same "File
> Name" (DBold_Data and DBold_Log, respectively) under the "Data Files" and
> "Transaction Files" tabs. The "Location", which points to the actual
> physical file, is correctly and uniquely named.
> Is the fact that these have the same File Name going to confuse SQL Server
> when it goes to update a database?

Issue with restore to new dbname on same server

I ran this script to backup to restore a backup onto a database with a new
name - on the same server.
USE master
> RESTORE DATABASE DBnew
> FROM DISK = 'E:\MSSQL7\BACKUP\DBold_db_200409270201.BAK'
> with RECOVERY,
> MOVE 'DBold_Data' TO 'E:\MSSQL7\data\DBnew_Data.MDF',
> MOVE 'DBold_Log' TO 'E:\MSSQL7\data\DBnew_Log.LDF',
> REPLACE
When I look at the properties for the database under em, I see that both the
original database and the copy-of-it-with-a-new-name have the same "File
Name" (DBold_Data and DBold_Log, respectively) under the "Data Files" and
"Transaction Files" tabs. The "Location", which points to the actual
physical file, is correctly and uniquely named.
Is the fact that these have the same File Name going to confuse SQL Server
when it goes to update a database?You should be fine.
"MarkAurit" <MarkAurit@.discussions.microsoft.com> wrote in message
news:59CC2C26-13F8-4664-99F1-CC550827A9D4@.microsoft.com...
>I ran this script to backup to restore a backup onto a database with a new
> name - on the same server.
> USE master
>> RESTORE DATABASE DBnew
>> FROM DISK = 'E:\MSSQL7\BACKUP\DBold_db_200409270201.BAK'
>> with RECOVERY,
>> MOVE 'DBold_Data' TO 'E:\MSSQL7\data\DBnew_Data.MDF',
>> MOVE 'DBold_Log' TO 'E:\MSSQL7\data\DBnew_Log.LDF',
>> REPLACE
> When I look at the properties for the database under em, I see that both
> the
> original database and the copy-of-it-with-a-new-name have the same "File
> Name" (DBold_Data and DBold_Log, respectively) under the "Data Files" and
> "Transaction Files" tabs. The "Location", which points to the actual
> physical file, is correctly and uniquely named.
> Is the fact that these have the same File Name going to confuse SQL Server
> when it goes to update a database?

issue with dts.taskresult from script task

have a script task that checks for existense of a file if found it sets

Dts.TaskResult = Dts.Results.Success and moves to next task

and if not found it sends an email then sets Dts.TaskResult = Dts.Results.Failure

but when I run the job to test for failure I do get the email but the job continues

to be in yellow state it never turns red. I have to terminate it myself manually.

Is there anything else I need to set?

Thanks in advance

I find that it is easier to set a boolean variable indicating success or otherwise. you can then check the value within a conditional precedence constraint.

-Jamie

ISQL: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near ' '

G'day everyone
That's a space between the ticks.
It's all part of a longer script but seeing as the failure occurs on
line 1
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[config]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[config]
GO
That's three lines only. Does it matter that they're in Unicode?
Any ideas?
Kind regards,
Bruce M. Axtens
Software Engineer
Strapper TechnologiesI pasted that code into a .sql file and ran it through isql.exe and it executed without errors. What
version of SQL Server to you connect to?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"axtens" <Bruce.Axtens@.gmail.com> wrote in message
news:1162976105.388119.97910@.b28g2000cwb.googlegroups.com...
> G'day everyone
> That's a space between the ticks.
> It's all part of a longer script but seeing as the failure occurs on
> line 1
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[config]') and OBJECTPROPERTY(id, N'IsUserTable') => 1)
> drop table [dbo].[config]
> GO
> That's three lines only. Does it matter that they're in Unicode?
> Any ideas?
> Kind regards,
> Bruce M. Axtens
> Software Engineer
> Strapper Technologies
>|||Tibor,
Thanks for your reply. I found out from another group that the line
count is reset each time ISQL executes a "GO" statement, so the
offending line could have been anywhere in a 123 line file.
What I ended up doing was creating two scripts from the original: one
to do the "DROP"s and the other to do the "CREATE"s.
Thanks for your time.
Kind regards,
Bruce M. Axtens
Software Engineer
Strapper Technologies

isql.exe vs isqlw.exe vs osql.exe

I've tried running a script (creates a bunch of SPs) using all three
command-line utilities, but have observed very different performance on
each.
isql.exe isqlw.exe osql.exe
11:14 134 01:20 773 05:01 650
As you can see, the time to execute varies quite a bit.
Can anyone offer an explanation for this?
When should you use one or the other?
Any guidance would be much appreciated. Thanks.
FYI - Here are the command line switches used:
isql.exe -SMyServer -UMyUserId -PMyPassword -dMyDatabase -iMyInputFile.sql -oMyOutputFile.log
-a4096 -c -n -w8000
isqlw.exe -SMyServer -UMyUserId -PMyPassword -dMyDatabase -iMyInputFile.sql
-oMyOutputFile.log
osql.exe -SMyServer -UMyUserId -PMyPassword -dMyDatabase -iMyInputFile.sql -oMyOutputFile.log
-a4096 -c -n -w8000
Henri Fournier
Henri Fournier wrote:
> I've tried running a script (creates a bunch of SPs) using all three
> command-line utilities, but have observed very different performance
> on each.
> isql.exe isqlw.exe osql.exe
> 11:14 134 01:20 773 05:01 650
> As you can see, the time to execute varies quite a bit.
> Can anyone offer an explanation for this?
> When should you use one or the other?
> Any guidance would be much appreciated. Thanks.
> FYI - Here are the command line switches used:
> isql.exe -SMyServer -UMyUserId -PMyPassword -dMyDatabase
> -iMyInputFile.sql -oMyOutputFile.log -a4096 -c -n -w8000
> isqlw.exe -SMyServer -UMyUserId -PMyPassword -dMyDatabase
> -iMyInputFile.sql -oMyOutputFile.log
> osql.exe -SMyServer -UMyUserId -PMyPassword -dMyDatabase
> -iMyInputFile.sql -oMyOutputFile.log -a4096 -c -n -w8000
I believe OSQL uses ODBC. ISQL uses DB-Library. ISQLW uses ODBC as well,
but is a Windows application (although it can run in a pseudo-console
mode).
Not sure what you are running or why you're seeing such poor performance
with OSQL. Can you elaborate? In any case, make sure SET NOCOUNT ON is
the first item in your script.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:egk11OTcFHA.1148@.tk2msftngp13.phx.gbl...
> I believe OSQL uses ODBC. ISQL uses DB-Library. ISQLW uses ODBC as well,
> but is a Windows application (although it can run in a pseudo-console
> mode).
> Not sure what you are running or why you're seeing such poor performance
> with OSQL. Can you elaborate? In any case, make sure SET NOCOUNT ON is the
> first item in your script.
>
We have a series of scripts to update the database, where one contains all
the schema changes, another contains triggers, etc. The one I'm using to
test does a drop/create for some 500+ Stored Procedures, nothing fancy.
And yes, SET NOCOUNT ON is the first line in the script.
I just can't see why OSQL is so much slower. I've got the packetsize set to
4096, which is what the "Network package size" is set to in QA (assuming
those are in fact the same things).
I've got the columnwidth set to 8000, cause I don't want any wrapping in the
output file. Don't think this should effect performance.
Can't see any switches set or not set that can account for the performance
difference. Right?
Henri