Exec VBScript file from T-SQL?

Is there a way to run a VBScript file from a T-SQL script (SQL 2000)?

Yes, it requires using xp_executeSQL.

That is a 'MAJOR' security issue and should not be done lightly -if at all. It also requires admin permissions in the server (or a proxy).

Perhaps if you were to provide more detail about your needs, folks here would help come up with 'safer' alternatives -if possible.

|||The root of the problem is that CDOSYS email has stopped working from SQL. It still works from VBScript, though. We rebooted the server (Win 2000 SP 3) and that fixed the problem, but only for a few days. Someone suggested converting our CDOSYS emails to VBScript. I am hoping that someday CDOSYS works again from SQL so I wanted to not change the job too much if possible. However, if it's a big security issue, I may change the steps that generate emails from T-SQL to OS Command line steps and exec the VBScript that way.|||

If you are wanting to send email from SQL Server 2000, then I recommend that you examine xp_smtp_email. It is more reliable and less of a security issue than using a mapi client on the server.

Email for SQL Server 2000
http://www.sqldev.net/xp/xpsmtp.htm

Also, click here for a similar forum thread.

sql

Exec VBScript file from T-SQL?

Is there a way to run a VBScript file from a T-SQL script (SQL 2000)?

Yes, it requires using xp_executeSQL.

That is a 'MAJOR' security issue and should not be done lightly -if at all. It also requires admin permissions in the server (or a proxy).

Perhaps if you were to provide more detail about your needs, folks here would help come up with 'safer' alternatives -if possible.

|||The root of the problem is that CDOSYS email has stopped working from SQL. It still works from VBScript, though. We rebooted the server (Win 2000 SP 3) and that fixed the problem, but only for a few days. Someone suggested converting our CDOSYS emails to VBScript. I am hoping that someday CDOSYS works again from SQL so I wanted to not change the job too much if possible. However, if it's a big security issue, I may change the steps that generate emails from T-SQL to OS Command line steps and exec the VBScript that way.|||

If you are wanting to send email from SQL Server 2000, then I recommend that you examine xp_smtp_email. It is more reliable and less of a security issue than using a mapi client on the server.

Email for SQL Server 2000
http://www.sqldev.net/xp/xpsmtp.htm

Also, click here for a similar forum thread.

EXEC Statements in a sql script

Thanks for the response. Didn't help however can tell you what is
happening now. I do an alter to create a new column. Put it in a
transaction and commit it. Then the next transaction I do an update to
the newly created column and it complains it can't find the column. If
I run all this manually it's fine. Could it be an issue with the speed
that the script is running that SQL Server, even though I committed
between alter and update, still is not done creating the tables or
something?
Thanks.
JRJR (jriker1@.yahoo.com) writes:
> Thanks for the response. Didn't help however can tell you what is
> happening now. I do an alter to create a new column. Put it in a
> transaction and commit it. Then the next transaction I do an update to
> the newly created column and it complains it can't find the column. If
> I run all this manually it's fine. Could it be an issue with the speed
> that the script is running that SQL Server, even though I committed
> between alter and update, still is not done creating the tables or
> something?
No, speed has nothing to do with it.
If you do:
ALTER TABLE tbl ADD newcol int
UPDATE tbl
SET newcol = 91
this will fail, because when SQL Server compiles this batch, it sees
that you references a column that does not exist in tbl, and that is
an error. SQL Server has deferred name resolution, so that if a table
does not exist when the batch is compiled, SQL Server is silent in hope
that the table is created. There is, thankfully, not deferred name
resolution for column names. It is bad as it is.
There are a couple of ways to skin the cat. The best is probably
to wrap the UPDATE into EXEC(), so that it will not be compiled
until after the ALTER TABLE statement has been executed.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Mon, 27 Mar 2006 22:42:08 +0000 (UTC), Erland Sommarskog wrote:
(snip)
>There are a couple of ways to skin the cat. The best is probably
>to wrap the UPDATE into EXEC(), so that it will not be compiled
>until after the ALTER TABLE statement has been executed.
Hi Erland,
In a stored procedure: yes.
But in a SQL script, just adding a "go" between the ALTER TABLE and the
UPDATE is enough.
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:
> (snip)
> Hi Erland,
> In a stored procedure: yes.
> But in a SQL script, just adding a "go" between the ALTER TABLE and the
> UPDATE is enough.
I didn't mention that possibility because the hour was late, and there are
some caveates with it. Say that you do:
BEGIN TRANSACTION
-- Do something
go
-- Do something more
go
-- Yet something more
COMMIT TRANSACTION
Now, if there is an error on the line of the kind that aborts the batch,
the transaction will be rolled back, but the remaining batches will be
executed. You will get an error when you reach COMMIT, but then the damage
may already been done.
Of course, in this particular case if ALTER TABLE fails, the UPDATE command
will also fail. However, there can be other commands in other batches that
still can be carried out when they shouldn't.
One way to handles this is to open every batch with IF @.@.trancount > 0,
but I think would be prefer to keep all in one batch, and interleave
problematic statments in dynamic SQL. Not the least on SQL 2005, as I
then can have single CATCH handler at the end. (But note that if you
have:
BEGIN TRY
UPDATE tbl SET missingcolumn = <somevalue>
END TRY
BEGIN CATCH
-- handle error
END CATCH
that the CATCH handler will not be reached, as the error is a compilation
error.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Tue, 28 Mar 2006 10:27:33 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:
>I didn't mention that possibility because the hour was late, and there are
>some caveates with it. Say that you do:
(snip)
Hi Erland,
Good point. Thanks for adding this warning!
Hugo Kornelis, SQL Server MVPsql

Exclude User Defined Data Type owner from script

I need to exclude the User Defined Data Type owner when I script out my tables. I have set the ScriptingOptions.SchemaQualify = false which removes the owner from the table declaration but not from the User Defined Data Type.

I currently have to do a search and replace in order to remove all the [dbo].[MyDataType] from the CREATE TABLE scripts.

Any ideas how to fix this when creating my table script in SMO?

Given that the schema is part of the security framework of the database, why would you want to remove the schema from the create script? It would make the script unusable as a recovery tool.

Exclude User Defined Data Type owner from script

I need to exclude the User Defined Data Type owner when I script out my tables. I have set the ScriptingOptions.SchemaQualify = false which removes the owner from the table declaration but not from the User Defined Data Type.

I currently have to do a search and replace in order to remove all the [dbo].[MyDataType] from the CREATE TABLE scripts.

Any ideas how to fix this when creating my table script in SMO?

Given that the schema is part of the security framework of the database, why would you want to remove the schema from the create script? It would make the script unusable as a recovery tool.

exclude blank records

I have created a script that returns every column and row that is queried
and some of the fields are blank. I want to only return the fields that are
populated. Below is the script. Any advice would be appreciated:
SELECT Relation.xparent_prov_id,
Relation.Parent,
Provider.DataSource_ID as prov_datasource_ID,
Provider.Provider_Name,
Provider.Provider_Type,
Provider.Degree_Type,
FEIProviderStatus.Status,
Provider.DataSource_ID,
Provider.City,
Provider.State,
evClinician_Profile.arabic
CASE
WHEN evClinician_Profile.arabic = 'Y' THEN 'Arabic'
ELSE ''
END AS Arabic,
CASE
WHEN evClinician_Profile.chinese = 'Y' THEN 'Chinese'
ELSE ''
END AS Chinese,
CASE
WHEN evClinician_Profile.french = 'Y' THEN 'French'
ELSE ''
END AS French,
CASE
WHEN evClinician_Profile.german = 'Y' THEN 'German'
ELSE ''
END AS German,
CASE
WHEN evClinician_Profile.hebrew = 'Y' THEN 'Hebrew'
ELSE ''
END AS Hebrew,
CASE
WHEN evClinician_Profile.italian = 'Y' THEN 'Italian'
ELSE ''
END AS Italian,
CASE
WHEN evClinician_Profile.japanese ='Y' THEN 'Japanese'
ELSE ''
END AS Japanese,
CASE
WHEN evClinician_Profile.russian = 'Y' THEN 'Russian'
ELSE ''
END AS Russian,
CASE
WHEN evClinician_Profile.spanish = 'Y' THEN 'Spanish'
ELSE ''
END AS Spanish
INTO #TEMP
FROM Provider
INNER JOIN Relation ON Provider.DataSource_ID = Relation.datasource_id
INNER JOIN evClinician_Profile ON Provider.Provider_Key =
evClinician_Profile.RelMan_Key
INNER JOIN FEIProviderStatus ON Provider.DataSource_ID =
FEIProviderStatus.ProviderID
SELECT * FROM #TEMP
DROP TABLE #TEMP
Message posted via http://www.webservertalk.comAs a general suggestion, you can use a WHERE clause in your query like:
WHERE '' NOT IN ( Arabic, Chinese, ... Spanish )
Anith|||What if one of the fields is blank and others are populated, do you want to
reject the whole row because of this?
If so, specify a WHERE clause as mentioned in an earlier reply.
Ilyan Mishiyev
IGM Consulting Corporation
Enterprise Web Solutions
www.igmcc.com
"Jay via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in message
news:c3177033e42f430f85a389ecc7a323f6@.SQ
webservertalk.com...
>I have created a script that returns every column and row that is queried
> and some of the fields are blank. I want to only return the fields that
> are
> populated. Below is the script. Any advice would be appreciated:
> SELECT Relation.xparent_prov_id,
> Relation.Parent,
> Provider.DataSource_ID as prov_datasource_ID,
> Provider.Provider_Name,
> Provider.Provider_Type,
> Provider.Degree_Type,
> FEIProviderStatus.Status,
> Provider.DataSource_ID,
> Provider.City,
> Provider.State,
> evClinician_Profile.arabic
> CASE
> WHEN evClinician_Profile.arabic = 'Y' THEN 'Arabic'
> ELSE ''
> END AS Arabic,
> CASE
> WHEN evClinician_Profile.chinese = 'Y' THEN 'Chinese'
> ELSE ''
> END AS Chinese,
> CASE
> WHEN evClinician_Profile.french = 'Y' THEN 'French'
> ELSE ''
> END AS French,
> CASE
> WHEN evClinician_Profile.german = 'Y' THEN 'German'
> ELSE ''
> END AS German,
> CASE
> WHEN evClinician_Profile.hebrew = 'Y' THEN 'Hebrew'
> ELSE ''
> END AS Hebrew,
> CASE
> WHEN evClinician_Profile.italian = 'Y' THEN 'Italian'
> ELSE ''
> END AS Italian,
> CASE
> WHEN evClinician_Profile.japanese ='Y' THEN 'Japanese'
> ELSE ''
> END AS Japanese,
> CASE
> WHEN evClinician_Profile.russian = 'Y' THEN 'Russian'
> ELSE ''
> END AS Russian,
> CASE
> WHEN evClinician_Profile.spanish = 'Y' THEN 'Spanish'
> ELSE ''
> END AS Spanish
> INTO #TEMP
> FROM Provider
> INNER JOIN Relation ON Provider.DataSource_ID = Relation.datasource_id
> INNER JOIN evClinician_Profile ON Provider.Provider_Key =
> evClinician_Profile.RelMan_Key
> INNER JOIN FEIProviderStatus ON Provider.DataSource_ID =
> FEIProviderStatus.ProviderID
> SELECT * FROM #TEMP
> DROP TABLE #TEMP
> --
> Message posted via http://www.webservertalk.com|||No, if the field is populated I want those to return those records.
Message posted via http://www.webservertalk.com|||That doesn't answer Ilyan's question. For example, given the following:
CREATE TABLE T1 (x INTEGER NOT NULL PRIMARY KEY, y CHAR(1) NULL, z
CHAR(1) NULL)
INSERT INTO T1 VALUES (1,'A',NULL)
INSERT INTO T1 VALUES (2,NULL,'B')
INSERT INTO T1 VALUES (3,'A','B')
You could exclude rows where either Y or Z is NULL:
SELECT x,y,z
FROM T1
WHERE y IS NOT NULL
AND z IS NOT NULL
or only where BOTH are NULL
SELECT x,y,z
FROM T1
WHERE y IS NOT NULL
OR z IS NOT NULL
You said you wanted to return "fields that are populated". Does that
mean you want to see a different number of columns depending on what
data exists? You'll have to do that either client-side, or with Dynamic
SQL or using a set of IF statements to cover all the various cases. A
static query always returns the same number of columns - it can't be
changed at runtime.
David Portas
SQL Server MVP
--|||I think I solved the issue by using:
WHERE LEN (fieldname) < 0
I did this code for each of the fields that I was querying in the sp. Is
this the most effeicient way to do this?
Message posted via http://www.webservertalk.com|||This achieves nothing except exclude the rows where fieldname is NULL
so you might as well write:
WHERE fieldname IS NOT NULL
Notice that NULL is not the same as an empty string. If you want to
exclude empty strings as well then you can do:
WHERE fieldname > ''
David Portas
SQL Server MVP
--|||You're right I got the same number of rows. Thanks for the feedback.
Message posted via http://www.webservertalk.com

Exceptions in Data Flow Scripts

What is the "correct" way of dealing with exceptions in a data flow script component. i.e. am I supposed to catch all exceptions and then set some failure flag? The reason I ask is I've got a script in a dataflow which is occasionally throwing exceptions when trying to convert an empty string to a decimal. Problem was the package locked up and had to be terminated when the exception was thrown (and not caught in the script)?

The only thing which differentiates this package from others I've created is the data flow has a conditional split which creates 2 seperate paths loading 2 seperate SQL tables - the exception is being thrown in a script on one branch which seems to hand the entire flow

PS I have fixed the script so this doesn't happen, i.e. test if the input string is String.Empty and if so set the _IsNull property.

If you are going to go down the road of custom building functions inside a script versus using the derived column or conditional split transformations to look at your data (test your data for correctness), then you'll also have to build in your own error handling as the script component does not support error outputs as part of the data flow.

Phil|||

Hi Phil,

Thanks for the reply, I'm not sure you're answering my question though The problem is that if an exception is thrown in a script and not caught, the package simply locks up - the unhandled exception seems to be blocking - no event handler is called etc. So what I'm asking is how do you flag a failure in a data flow script (if it were a control flow script I would propably create a global catch handler with Dts.TaskResult = Dts.Results.Failure

Dave

|||I think the package should lock up though, as the script component doesn't support the error output stream. So if an error is encountered in a script task, how should the package continue? That is, the error is fatal to the script component.

Some of the other guys will undoubtedly have some more advice when they get in tomorrow morning, especially those that are well versed in script programming.

Sorry, but I won't be of no more help tonight, especially when I'm not sitting in front of SSIS.

Phil|||Does it really lock up, or does the package simply fail?|||

It locks up, i.e. if run from BIDS the scipt component goes yellow when it starts executing, and nothing more occurs.

In other data flows, exceptions do cause the script component (and ultimatly the package) to fail. The different seemingly being that the package has a conditional split and each path of the split appears to be running concurrently when the exception is thrown in the script.

It's interesting that there's no error output from a script - not sure what to make of that - of all my dataflow components, the ones which fail the most are my scripts , esp since I cannot single step through them. Maybe I need to add and error number colume, handle the exception and set the error number in the script and then pass the subsequent flow through a conditional split?

|||

Dave Waterworth wrote:

Maybe I need to add and error number colume, handle the exception and set the error number in the script and then pass the subsequent flow through a conditional split?

Yep, a very valid solution.

ExceptionMessageBox

I am trying to get the Exception Message Box to work in a script task in SSIS.

I am using the example from http://msdn2.microsoft.com/en-us/library/ms166340.aspx almost verbatum.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.MessageBox

Public Sub Main()

'

' Define the message and caption to display.

Dim str As String = "Do you want to push to Production?"

Dim caption As String = "Zip Push"

Dim var As Variables

' Show the exception message box with Yes and No buttons.

Dim box As ExceptionMessageBox = New ExceptionMessageBox(str, caption)

box.DefaultButton = ExceptionMessageBoxDefaultButton.Button2

box.Symbol = ExceptionMessageBoxSymbol.Question

box.Buttons = ExceptionMessageBoxButtons.YesNo

'box.SetButtonText("Yes", "No", "Cancel")

If Windows.Forms.DialogResult.Yes = box.Show(CType(Me,Windows.Forms.IWin32Window)) Then

Dts.Variables("Production").Value = True

End If

'

Dts.TaskResult = Dts.Results.Success

End Sub

And yet all I get is the following error

Unable to cast object of type 'ScriptTask_bc7fa8cd8b3c4f4d96407f2b13927e0f.ScriptMain' to type 'System.Windows.Forms.IWin32Window'.

Has anyone gotten this to work?

BTW: I am running SQL 2005 SP2

Just use MsgBox("...")

You don't even need to reference any assembly for it.

By the way, what are you trying to do here? SSIS is supposed to be a batch-oriented process, not interactive.

-Jamie

|||MsgBox works, but it's too limited. I wanted the added functionality of ExceptionMessageBox. Besides, my question was not "How do I NOT use ExceptionMessageBox?".|||

S1monk wrote:

MsgBox works, but it's too limited. I wanted the added functionality of ExceptionMessageBox. Besides, my question was not "How do I NOT use ExceptionMessageBox?".

Was it? I've just re-read your original post and didn't see that question anywhere. All I saw was "Has anyone gotten this to work?"

I'm afraid I don't know why your code isn't working. What exactly do you want to do?

-Jamie

|||

Jamie Thomson wrote:

What exactly do you want to do?

I second this question. Also Jamie's comment about SSIS being a batch utility, not an interactive one.

|||

What I would like to do is use ExceptionMessageBox to display a message and allow me to customize the buttons, which I believe you cannot do with MsgBox. The example I used is just a simplest case taken from http://msdn2.microsoft.com/en-us/library/ms166340.aspx to test the functionality. I cannot get it to run. All I get is

Unable to cast object of type 'ScriptTask_bc7fa8cd8b3c4f4d96407f2b13927e0f.ScriptMain' to type 'System.Windows.Forms.IWin32Window'.

Any help would be greatly appreciated, but comments like use something else and SSIS is a batch utility are not helpfull.

|||

S1monk wrote:

What I would like to do is use ExceptionMessageBox to display a message and allow me to customize the buttons, which I believe you cannot do with MsgBox. The example I used is just a simplest case taken from http://msdn2.microsoft.com/en-us/library/ms166340.aspx to test the functionality. I cannot get it to run. All I get is

I don't know for sure but my guess from the error message is that this simply cannot be done. The script task is not designed to be used interactively as you are attempting to do.

S1monk wrote:

Unable to cast object of type 'ScriptTask_bc7fa8cd8b3c4f4d96407f2b13927e0f.ScriptMain' to type 'System.Windows.Forms.IWin32Window'.

Any help would be greatly appreciated, but comments like use something else and SSIS is a batch utility are not helpfull.

If something isn't working then I would have thought suggesting an alternative was perfectly good advice. Likewise the advice about SSIS being a batch utility was intended to be helpful and, as I think your error message proves, this advice has been borne out to be true.

The question "What exactly are you trying to do?" was a lead-in to suggesting an alternative that WOULD work. Due to the very nature of SSIS, prompting users for input from INSIDE a package is not an appropriate thing to do - better to prompt them elsewhere and pass that information into the package so that it can act upon it dynamically.

I was trying to proffer some simple advice but obviously that advice is not appreciated hence I won't post on this thread again. I have no desire to help someone that doesn't value that help. Good luck in finding a solution to your problem.

-Jamie

|||

The type you are passing to the Show method is not a window. You have to catch a handle of the top window and pass it to this method. Perhaps, even null could work but it might create a weird effects (like popping up in the backround or something similar).

|||

The error is because Me is not a window, it does not implement IWin32Window, so the cast is invalid - CType(Me,Windows.Forms.IWin32Window)

You do not have a Form, because SSIS is just not aimed at being an interactive tool, which is the point others have tried to highlight.

You could get the same functionality with old MsgBox or System.Windows.Forms.MessageBox (same thing really), it supports Yes/No/Cancel if you wish.

Sorry for going so far off the question, but I thought it might help provide a solution.

|||

I found a way to make it work

Option Strict On

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.MessageBox

Public Class ScriptMain

Public Sub Main()

Dim str As String = "Are you sure you want to delete file 'c:\somefile.txt'?"

Dim caption As String = "Confirm File Deletion"

Dim win As Windows.Forms.IWin32Window

' Show the exception message box with Yes and No buttons.

Dim box As ExceptionMessageBox = New ExceptionMessageBox(str, _

caption, ExceptionMessageBoxButtons.YesNo, _

ExceptionMessageBoxSymbol.Question, _

ExceptionMessageBoxDefaultButton.Button2)

If Windows.Forms.DialogResult.Yes = box.Show(win) Then

' Delete the file.

End If

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

|||[Shaking head]

I just don't understand why you'd want to do this. I just don't understand........ SSIS is not an interactive tool - your solution only works when debugging SSIS.

[/Shaking head]|||I don't understand why you are all so hung up on SSIS being batch or interactive. It is what it is and it works how you use it. We do a lot of data loads on one server and then push the data to different environments (Development, QA, Staging, Production). With DTS we had a seperate package for each push. It leads to a LOT of packages. My goal was to clean this up a little and make the packages a little more versatile. I don't know what flavor of SSIS you are using, but the above code works just fine in my INTERACTIVE production environment.|||

S1monk wrote:

I don't understand why you are all so hung up on SSIS being batch or interactive. It is what it is and it works how you use it. We do a lot of data loads on one server and then push the data to different environments (Development, QA, Staging, Production). With DTS we had a seperate package for each push. It leads to a LOT of packages. My goal was to clean this up a little and make the packages a little more versatile. I don't know what flavor of SSIS you are using, but the above code works just fine in my INTERACTIVE production environment.

HAHAHAHA. Yeah, okay.|||I hope we can agree then, that when developing, testing or maintaining in BIDS, dialogs can be useful.

The use of dialogs IN SSIS packages is not a good idea in general, but even so, that's not a universal truth (dialogs + SSIS = misunderstanding SSIS and its intent) .

As long as the System::InteractiveMode variable is checked in advance, dialogs are fine. SSIS provides this variable since a good amount of time is spent in development and maintenance inside of BIDS, where its nice to make changes to variables without changing package source code.

Its for this same reason ("debugging and maintenance") that many shells and language interpreters have both an interactive mode ( bash, python,ruby, and powershell come to mind ) and may be run interactively as well as non-interactively.

Excel XP removes AS part of statement

I am trying to help an individual with a MS Query.

In Excel 97 MS Query the script was written:

TO_CHAR(LABTRANS.STARTDATE, 'YYYY') AS YEAR

In Excel XP I can run an existing Query and it returns the data as expected, but when I view the query it is:

TO_CHAR(LABTRANS.STARTDATE, 'YYYY') AS 'YEAR'

However if I try to create a new query using XP MS Query then the AS YEAR or AS 'YEAR' will be removed by MS Query and my column header will now be:

TO_CHAR(LABTRANS.STARTDATE, 'YYYY')

Here are my examples:

MS Excel 97 Query

SELECT LABTRANS.LABORCODE, LABTRANS.REGULARHRS, LABTRANS.OTHRS, LABTRANS.OTSCALE, LABTRANS.STARTDATE, LABTRANS.TRANSTYPE, VALUELIST.VALDESC,
TO_CHAR(LABTRANS.STARTDATE, 'YYYY') AS 'YEAR'
FROM MAXIMO.LABTRANS LABTRANS, MAXIMO.VALUELIST VALUELIST
WHERE LABTRANS.TRANSTYPE = VALUELIST.VALUE
AND (LABTRANS.TRANSTYPE IN (SELECT VALUE FROM VALUELIST WHERE LISTNAME = 'LTTYPE' AND MAXVALUE = 'NON-WORK'))
AND LABTRANS.LABORCODE IN (SELECT LABORCODE FROM LABOR WHERE LABOR.TYPE = 'CUPE' AND LABOR.CREWID IS NOT NULL AND LABOR.LA9 = 'N' AND LABOR.LA16 = 'WW' OR LABORCODE = '210308') AND VALUELIST.MAXVALUE = 'NON-WORK' ORDER BY LABTRANS.STARTDATE

MS Excel XP Query

SELECT LABTRANS.LABORCODE, LABTRANS.REGULARHRS, LABTRANS.OTHRS, LABTRANS.OTSCALE, LABTRANS.STARTDATE, LABTRANS.TRANSTYPE, VALUELIST.VALDESC,
TO_CHAR(LABTRANS.STARTDATE, 'YYYY')
FROM MAXIMO.LABTRANS LABTRANS, MAXIMO.VALUELIST VALUELIST
WHERE LABTRANS.TRANSTYPE = VALUELIST.VALUE
AND (LABTRANS.TRANSTYPE IN (SELECT VALUE FROM VALUELIST WHERE LISTNAME = 'LTTYPE' AND MAXVALUE = 'NON-WORK'))
AND LABTRANS.LABORCODE IN (SELECT LABORCODE FROM LABOR WHERE LABOR.TYPE = 'CUPE' AND LABOR.CREWID IS NOT NULL AND LABOR.LA9 = 'N' AND LABOR.LA16 = 'WW' OR LABORCODE = '210308') AND VALUELIST.MAXVALUE = 'NON-WORK' ORDER BY LABTRANS.STARTDATE

In Excel XP, no matter what I put in the AS part of the statement when I execute the script it runs and removes the AS part from the statement. This only happens when I run the script from MS Query, if I stay in Excel and just refresh the data, then it works fine.

I did not have this problem in Excel 97.

Any suggestions?I think your linking to a wrong database

TO_CHAR is an Oracle Function...not SQL Server

CONVERT(varchar(4),Col1) is SQL Server|||Your right, he is connecting to an Oracle server. I placed this topic under the wrong heading....

Thanks

Excel Objects From SSIS

I'm trying to use Excel objects from within an SSIS Script Task but I can't figure out how to add a reference the object library. I'm using Excel 2003 and tried Add Reference from the Project menu but there is no entry for Microsoft Excel Object Library or Microsoft Excel 11.0 Object Library. Any suggestions? Thanks.You would need to copy the Excel PIA to a specific folder for it to become available in the VSA Add Reference dialog. http://support.microsoft.com/kb/306149

Note that automation of Office apps from server-side components that often run unattended, like Integration Services packages, is discouraged. For more information, see 257757 INFO: Considerations for Server-Side Automation of Office
http://support.microsoft.com/?id=257757.

-Doug
|||I've got the Excel 2003 PIA installed. It appears to be in the GAC. Using windows explorer, I'm given the option to uninstall it from c:\windows\assembly, but not the option to copy or move it to another folder. Using the command line, when I navigate to c:\windows\assembly, I don't find the file listed in c:\windows\assembly, but instead I find a lot of nested folders. How do I move the PIA to somewhere that VSA can see it?

I'm trying to create the excel file that SSIS will soon be exporting data to. Is there another way to do that (programatically) inside SSIS?

Thanks,

Matthew Martin|||That's a lot of overhead to invoke excel.exe, and discouraged since packages often run unattended.

I would (1) use the Export Wizard to create the destination spreadsheet file initially or (2) save a blank one somewhere as a "template" and use a Script task (for example) to make a copy of it each time the package runs.

I once saw the syntax to create a new Excel file by using the Jet provider (through ADO rather than Excel), but now can't find it. Since the provider normally expects an existing database (naturally), I seem to recall that it wasn't obvious.

Best regards,

-Doug