[TRANSACT] Can't switch from database to another

Hello,
I encounter a problem with a small portion of sqlcode. I
try to go on database using "use dbname" but i always stay
in master. I execute script with the sa user.
declare @.dbnamesysname
declare @.ret_codeint
DECLARE db_cursor CURSOR FOR
select
name
from
master..sysdatabases
where
name not in ('master', 'model', 'tempdb', 'pubs',
'Northwind')
-- Open cursor
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @.dbname
WHILE @.@.FETCH_STATUS = 0
BEGIN
execute ('use ' + @.dbname)
execute ('select db_name()')
Thank's for help,
Pierrot.
AFAIK, executing just open up a session to excute something, when executing
the next statement you are in another Session, try to combine these two
together with
EXECUTE ('Use Northwind;Select DB_NAME()')
HTH, Jens Smeyer.
http://www.sqlserver2005.de
"pierrot" <anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:104e01c5419c$afcb5850$a401280a@.phx.gbl...
> Hello,
> I encounter a problem with a small portion of sqlcode. I
> try to go on database using "use dbname" but i always stay
> in master. I execute script with the sa user.
> declare @.dbname sysname
> declare @.ret_code int
> DECLARE db_cursor CURSOR FOR
> select
> name
> from
> master..sysdatabases
> where
> name not in ('master', 'model', 'tempdb', 'pubs',
> 'Northwind')
> -- Open cursor
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.dbname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> execute ('use ' + @.dbname)
> execute ('select db_name()')
> Thank's for help,
> Pierrot.
|||As Jens indicates each EXECUTE is its own session, so to do this in an
execute you must place all of the statements together...
Depending on what you are trying to do you could also use the 3part name
Declare @.str varchar(100)
Select @.str = 'select * from ' + @.dbname + '.dbo.thetable'
execute (@.str)
for System Sps you could also do
Declare @.str varchar(100)
Select @.str = 'Execute ' + @.dbname + '.dbo.sp_spaceused'
execute (@.str)
If you wish to do something for each database try sp_foreachdatabase ( I
think is the name of the sp.)
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"pierrot" <anonymous@.discussions.microsoft.com> wrote in message
news:104e01c5419c$afcb5850$a401280a@.phx.gbl...
> Hello,
> I encounter a problem with a small portion of sqlcode. I
> try to go on database using "use dbname" but i always stay
> in master. I execute script with the sa user.
> declare @.dbname sysname
> declare @.ret_code int
> DECLARE db_cursor CURSOR FOR
> select
> name
> from
> master..sysdatabases
> where
> name not in ('master', 'model', 'tempdb', 'pubs',
> 'Northwind')
> -- Open cursor
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.dbname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> execute ('use ' + @.dbname)
> execute ('select db_name()')
> Thank's for help,
> Pierrot.

[TRANSACT] Can't switch from database to another

Hello,
I encounter a problem with a small portion of sqlcode. I
try to go on database using "use dbname" but i always stay
in master. I execute script with the sa user.
declare @.dbname sysname
declare @.ret_code int
DECLARE db_cursor CURSOR FOR
select
name
from
master..sysdatabases
where
name not in ('master', 'model', 'tempdb', 'pubs',
'Northwind')
-- Open cursor
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @.dbname
WHILE @.@.FETCH_STATUS = 0
BEGIN
execute ('use ' + @.dbname)
execute ('select db_name()')
Thank's for help,
Pierrot.AFAIK, executing just open up a session to excute something, when executing
the next statement you are in another Session, try to combine these two
together with
EXECUTE ('Use Northwind;Select DB_NAME()')
HTH, Jens Süßmeyer.
--
http://www.sqlserver2005.de
--
"pierrot" <anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:104e01c5419c$afcb5850$a401280a@.phx.gbl...
> Hello,
> I encounter a problem with a small portion of sqlcode. I
> try to go on database using "use dbname" but i always stay
> in master. I execute script with the sa user.
> declare @.dbname sysname
> declare @.ret_code int
> DECLARE db_cursor CURSOR FOR
> select
> name
> from
> master..sysdatabases
> where
> name not in ('master', 'model', 'tempdb', 'pubs',
> 'Northwind')
> -- Open cursor
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.dbname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> execute ('use ' + @.dbname)
> execute ('select db_name()')
> Thank's for help,
> Pierrot.|||As Jens indicates each EXECUTE is its own session, so to do this in an
execute you must place all of the statements together...
Depending on what you are trying to do you could also use the 3part name
Declare @.str varchar(100)
Select @.str = 'select * from ' + @.dbname + '.dbo.thetable'
execute (@.str)
for System Sps you could also do
Declare @.str varchar(100)
Select @.str = 'Execute ' + @.dbname + '.dbo.sp_spaceused'
execute (@.str)
If you wish to do something for each database try sp_foreachdatabase ( I
think is the name of the sp.)
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"pierrot" <anonymous@.discussions.microsoft.com> wrote in message
news:104e01c5419c$afcb5850$a401280a@.phx.gbl...
> Hello,
> I encounter a problem with a small portion of sqlcode. I
> try to go on database using "use dbname" but i always stay
> in master. I execute script with the sa user.
> declare @.dbname sysname
> declare @.ret_code int
> DECLARE db_cursor CURSOR FOR
> select
> name
> from
> master..sysdatabases
> where
> name not in ('master', 'model', 'tempdb', 'pubs',
> 'Northwind')
> -- Open cursor
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.dbname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> execute ('use ' + @.dbname)
> execute ('select db_name()')
> Thank's for help,
> Pierrot.

[TRANSACT] Can't switch from database to another

Hello,
I encounter a problem with a small portion of sqlcode. I
try to go on database using "use dbname" but i always stay
in master. I execute script with the sa user.
declare @.dbname sysname
declare @.ret_code int
DECLARE db_cursor CURSOR FOR
select
name
from
master..sysdatabases
where
name not in ('master', 'model', 'tempdb', 'pubs',
'Northwind')
-- Open cursor
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @.dbname
WHILE @.@.FETCH_STATUS = 0
BEGIN
execute ('use ' + @.dbname)
execute ('select db_name()')
Thank's for help,
Pierrot.AFAIK, executing just open up a session to excute something, when executing
the next statement you are in another Session, try to combine these two
together with
EXECUTE ('Use Northwind;Select DB_NAME()')
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"pierrot" <anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:104e01c5419c$afcb5850$a401280a@.phx.gbl...
> Hello,
> I encounter a problem with a small portion of sqlcode. I
> try to go on database using "use dbname" but i always stay
> in master. I execute script with the sa user.
> declare @.dbname sysname
> declare @.ret_code int
> DECLARE db_cursor CURSOR FOR
> select
> name
> from
> master..sysdatabases
> where
> name not in ('master', 'model', 'tempdb', 'pubs',
> 'Northwind')
> -- Open cursor
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.dbname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> execute ('use ' + @.dbname)
> execute ('select db_name()')
> Thank's for help,
> Pierrot.|||As Jens indicates each EXECUTE is its own session, so to do this in an
execute you must place all of the statements together...
Depending on what you are trying to do you could also use the 3part name
Declare @.str varchar(100)
Select @.str = 'select * from ' + @.dbname + '.dbo.thetable'
execute (@.str)
for System Sps you could also do
Declare @.str varchar(100)
Select @.str = 'Execute ' + @.dbname + '.dbo.sp_spaceused'
execute (@.str)
If you wish to do something for each database try sp_foreachdatabase ( I
think is the name of the sp.)
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"pierrot" <anonymous@.discussions.microsoft.com> wrote in message
news:104e01c5419c$afcb5850$a401280a@.phx.gbl...
> Hello,
> I encounter a problem with a small portion of sqlcode. I
> try to go on database using "use dbname" but i always stay
> in master. I execute script with the sa user.
> declare @.dbname sysname
> declare @.ret_code int
> DECLARE db_cursor CURSOR FOR
> select
> name
> from
> master..sysdatabases
> where
> name not in ('master', 'model', 'tempdb', 'pubs',
> 'Northwind')
> -- Open cursor
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.dbname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> execute ('use ' + @.dbname)
> execute ('select db_name()')
> Thank's for help,
> Pierrot.sql

[newbie] Execute SQL task bypassed, why ?

Hi!

I meet a strange behaviour which is probably caused by my SSIS newbie nature.

I have a Execute SQL Task, used to drop and create some temporary tables. It works when invoked manually.

I have chained a Data Flow Task behind, but when I launch the whole process, although the Execute SQL tasks goes green, it is not executed (then the Data Flow fails because the required tables are missing).

Would anyone have any hint on why the execute sql task seems to be bypassed ?

kind regards

Thibaut

hi Thibaut,

how odd! Have you defined a log file for your package? If so, what kind of info is providing to you?

|||

Is that a package that you strated from scratch? If not; make sure there is not an expression or package configuration that changes the SQL Statement or the connection strings. If you are getting green on the SQL task I bet the object is actually being created, perhaps in the wrong side (server, schema, DB, etc)

Rafael Salas

|||I am now restarting the package from scratch, and making it configurable ("keeping it in the dark"). I'll report back if I meet the same issue again.

thanks!|||Hi!

I finally found out what is happening. The Data flow task is using a XML source to load the data into tables which are created by the Execute SQL task.

The XML source was trying to validate the schema provided at run time against tables which are not created yet... I just disabled the validation on the XML source and everything went fine.

thanks for all the replies.

cheers!

Thibaut

[mssql][ant] Failed to execute: GO

Hi,

I am trying to execute a simple sql file thru ant task. The file is:

use [centivaTest]
GO

However I get the errror message:
[sql] Failed to execute: use [centivaTest] GO

So I changed the file to:

use [centivaTest] ;
GO

as ant treats ';' as a delimiter. However another erro message
appears:

[mssql][ant] Failed to execute: GO

Do you know how to execute my simple sql statements thru ant (or
generally what wrong am I doing)?

Best regards,
Maciek Zywno"maciek zywno" wrote:

> I am trying to execute a simple sql file thru ant task. The file is:

<snip
> However I get the errror message:
> [sql] Failed to execute: use [centivaTest] GO

Maciek,

GO isn't understood by SQL Server: it's used as a batch delimiter for isql,
osql, and QA. I'm not an ant expert, but a few choices I see are:

- Have ant execute your SQL file via osql.

- Removed unnecessary GO's and/or manually break your script into separate
scripts (since that's what GO does anyway).

Craig

[Help] EXECUTE permission denied on object

Microsoft OLE DB Provider for SQL Server error '80040e09'

EXECUTE permission denied on object 'wwfSpTimeAndDateSettings', database 'iobmi6_ETSasp', owner 'dbo'.

/forum/functions/functions_date_time_format.asp, line 82


What does the EXECUTE permission denied on object... line mean?It means that the object owner decided NOT to run the following statement:

grant execute on dbo.wwfSpTimeAndDateSettings to <you>|||Ok i spent time trying to figure out how to fix it bymyself... but i have no idea. I'm trying to install Iportal... so i remade sure i installed all the files completly. Looked up my database info to see that correct. Is it somthing with my database not having dbo autority? (cause then i'll need to ask my hosting for it)|||What is the User ID specified in the connection string? Issue the statement from the previous post and replace <you> with that value.|||Dude! did you read rdjabarob post? if yes, read it again .. if you still don't get it, then I suggest you do some BOL reading about "permissions-SQL Server: Managing permissions"

[EXTREMELY URGENT] sp_attach_db error

I am attaching a database with 3 data files.
When I execute "exe sp_attache_db..." I obtain this error:
database 'POINT' cannot be opened because some of the files could not be
activated.
I have deleted its LDF file.
Usually I detach my db, then I delete transaction log, and reattach 3 data
files...
Now it doesn'work!!!!!!!!!!
Someone can help me?
Thanks."George" <giorgio.benedettiNOSPAM@.libero.it> wrote in message
news:bovm8o$8o0$1@.newsreader.mailgate.org...
> I am attaching a database with 3 data files.
> When I execute "exe sp_attache_db..." I obtain this error:
> database 'POINT' cannot be opened because some of the files could not be
> activated.
> I have deleted its LDF file.
> Usually I detach my db, then I delete transaction log, and reattach 3 data
> files...
> Now it doesn'work!!!!!!!!!!

First, it should be noted in case you're not aware that deleting the LDF
file is a VERY bad idea. It should only be done in the most rare of cases.

I"m actually surprised that sp_attach_db has worked at all w/o the log file.

However, you can try sp_attach_singlefile_db, but I doubt it will work.

You'll probably have to call Microsoft on this.

> Someone can help me?
> Thanks.

[Execute SQL Task] Error: An error occurred while assigning a value to variable...

hi chaps

i m getting the following ERROR:

[Execute SQL Task] Error: An error occurred while assigning a value to variable "JDETimezone": "Unable to find column Timezone in the result set.".

i know what the problem is i.e. no row is returned then what is the problem

here you are.... i want to it work... strange... ok i explain...

actully i have some processign to do with variable JDETimezone even no row is returned.... can u tell me the alternative to do the follwing task...

I want to retrieve a record from some table and do some processing and if no row is present or returned then i want to do seperate processing.... can ne one help me out ?

regards,

Anas

That error usually occurs when you either don't have a column in your select statement named "Timezone", or you didn't name the resultset Timezone. You may want to check your settings.

If you want to do separate processing when no rows are returned then in the Error Flow redirect the rows instead of Failing or Ignoring them.

|||

thanx for your reply

but i sure I have both, column name Timezone in Select statement as well as in Resultset...

problem is that it doesnt return any row and causes error...Sean you are right I can use Error flow but the problem that i dont want any of my task to fail becuase i m doing error handling by Task failure event which then email the failure information to admin group

does ne one have solution of my problem?

regards,

Anas

|||

One way to implement different processing logic based on the result set row count is by having a Execute SQL task that counts the total number of rows returned by a query and assign the count to a variable which can later be used to determine the control flow.

See http://msdn2.microsoft.com/en-US/library/ms140153.aspx

[Execute SQL Task] Error: An error occurred while assigning a value to variable

hello
I have a problem with Sql task
when sql task tried to assing a value to my variable I have this error ""La valeur n'est pas comprise dans la plage attendue."
I'm using ODBC connexion for a csv file

someone can help me ?

thanks
It might help if you translated the error message to english.|||

we can translate as " the value is not included in the correct space " or something like that

|||

Take a look at this - it explains how to set up the Execute SQL task for retrieving values.

http://www.sqlis.com/58.aspx

[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x0

This error occurs when the ActiveX task tries to execute:

[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x001B6438.

Anybody know how to troubleshoot these errors? I can't find anything on this error code. The same script works in DTS.

I have got the same error, the code is different but couldn't figure out what to do...|||

Hi Igor,

This isn't much of an answer but it is true: you should try to move away from the ActiveX Script Task and use the Script Task instead.

Hope this helps,
Andy

|||Script Task does mean that there is a different scripting language?

I tryed a different thing: I moved all the script from a SSIS task to a SQL Server job (a job with one "activeX script" step).

It sounds great, but I have a different error in the step that says that
"The command script does not destroy all the objects that it creates. Revise the command script. (Microsoft SQL Server, Error: 14277)"

I am not a VB programmer, so I think I have to ask someone to revise the script.

Thanks for the help|||

ActiveX scripts in SQL Jobs (all versions) appear to have a major bug in that they report Error: 14277 whenever the string "createobject(" appears more than once anywhere in the script. It does not matter whether the string is just part of a character expression such as: sTemp = "..... createobject( ...."; whether it appears in a comment or whether it is used to actually create an object. Any combination of the above that puts "createobject(" in the script more than once will cause the 14277 error to appear when you try to close the Job modifier.

There is a trick that I have found to overcome this. That is, to create any and all objects in a single common subroutine. Even in this subroutine, you have to trick the system into thinking that you have just destroyed the object that you are trying to create.

The subroutine is cobj. It takes the variable that will become the object and a string that defines the activex control. The "set ... = Nothing" that appears after the "Exit Sub" is the trick that makes the system think that the object is destroyed within the scope of cobj. Note: be sure to destroy the object in the scope where the object variable was defined.

Here is a code sample that sends an email using ASPMAIL, which contains data from an ADO SQL query.

'*********************************************
' ActiveX Script - no 14277 error
'*********************************************
MailMe readSQL(1006), "mymail@.mail.com"

Sub cobj(newobj, ax)
Set newobj = createobject(ax) ' only appears once, here
exit sub
Set newobj = Nothing ' never executed but tricks checker
End Sub

sub MailMe (sMsg, sAddress)
dim Mailer, vRet
if instr(sAddress,"@.")<1 then exit sub

cobj Mailer, "SMTPsvg.Mailer"

Mailer.FromName = "ASP_Debug"
Mailer.FromAddress = sAddress
Mailer.RemoteHost = "127.0.0.1"
Mailer.AddRecipient "", sAddress
Mailer.Subject = "Debug ActiveX Script - 14277 Error"
Mailer.BodyText = sMsg
Mailer.SendMail
Set Mailer=Nothing
end sub

Function readSQL(ndx)
Dim SQL, sConn, oRst
readSQL = "No Record"
SQL = "SELECT Note FROM NoteTable WHERE [ID]=" & CStr(ndx)
sConn = "Provider=SQLOLEDB.1;Initial Catalog=xx;Data Source=zz"

cobj oRst, "ADODB.Recordset"

oRst.Open SQL, sConn
If oRst.State = 1 Then
readSQL = oRst(0)
oRst.Close
End If
Set oRst = Nothing
End Function

[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x0

This error occurs when the ActiveX task tries to execute:

[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x001B6438.

Anybody know how to troubleshoot these errors? I can't find anything on this error code. The same script works in DTS.

I have got the same error, the code is different but couldn't figure out what to do...|||

Hi Igor,

This isn't much of an answer but it is true: you should try to move away from the ActiveX Script Task and use the Script Task instead.

Hope this helps,
Andy

|||Script Task does mean that there is a different scripting language?

I tryed a different thing: I moved all the script from a SSIS task to a SQL Server job (a job with one "activeX script" step).

It sounds great, but I have a different error in the step that says that
"The command script does not destroy all the objects that it creates. Revise the command script. (Microsoft SQL Server, Error: 14277)"

I am not a VB programmer, so I think I have to ask someone to revise the script.

Thanks for the help|||

ActiveX scripts in SQL Jobs (all versions) appear to have a major bug in that they report Error: 14277 whenever the string "createobject(" appears more than once anywhere in the script. It does not matter whether the string is just part of a character expression such as: sTemp = "..... createobject( ...."; whether it appears in a comment or whether it is used to actually create an object. Any combination of the above that puts "createobject(" in the script more than once will cause the 14277 error to appear when you try to close the Job modifier.

There is a trick that I have found to overcome this. That is, to create any and all objects in a single common subroutine. Even in this subroutine, you have to trick the system into thinking that you have just destroyed the object that you are trying to create.

The subroutine is cobj. It takes the variable that will become the object and a string that defines the activex control. The "set ... = Nothing" that appears after the "Exit Sub" is the trick that makes the system think that the object is destroyed within the scope of cobj. Note: be sure to destroy the object in the scope where the object variable was defined.

Here is a code sample that sends an email using ASPMAIL, which contains data from an ADO SQL query.

'*********************************************
' ActiveX Script - no 14277 error
'*********************************************
MailMe readSQL(1006), "mymail@.mail.com"

Sub cobj(newobj, ax)
Set newobj = createobject(ax) ' only appears once, here
exit sub
Set newobj = Nothing ' never executed but tricks checker
End Sub

sub MailMe (sMsg, sAddress)
dim Mailer, vRet
if instr(sAddress,"@.")<1 then exit sub

cobj Mailer, "SMTPsvg.Mailer"

Mailer.FromName = "ASP_Debug"
Mailer.FromAddress = sAddress
Mailer.RemoteHost = "127.0.0.1"
Mailer.AddRecipient "", sAddress
Mailer.Subject = "Debug ActiveX Script - 14277 Error"
Mailer.BodyText = sMsg
Mailer.SendMail
Set Mailer=Nothing
end sub

Function readSQL(ndx)
Dim SQL, sConn, oRst
readSQL = "No Record"
SQL = "SELECT Note FROM NoteTable WHERE [ID]=" & CStr(ndx)
sConn = "Provider=SQLOLEDB.1;Initial Catalog=xx;Data Source=zz"

cobj oRst, "ADODB.Recordset"

oRst.Open SQL, sConn
If oRst.State = 1 Then
readSQL = oRst(0)
oRst.Close
End If
Set oRst = Nothing
End Function

[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x0

This error occurs when the ActiveX task tries to execute:

[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x001B6438.

Anybody know how to troubleshoot these errors? I can't find anything on this error code. The same script works in DTS.

I have got the same error, the code is different but couldn't figure out what to do...|||

Hi Igor,

This isn't much of an answer but it is true: you should try to move away from the ActiveX Script Task and use the Script Task instead.

Hope this helps,
Andy

|||Script Task does mean that there is a different scripting language?

I tryed a different thing: I moved all the script from a SSIS task to a SQL Server job (a job with one "activeX script" step).

It sounds great, but I have a different error in the step that says that
"The command script does not destroy all the objects that it creates. Revise the command script. (Microsoft SQL Server, Error: 14277)"

I am not a VB programmer, so I think I have to ask someone to revise the script.

Thanks for the help|||

ActiveX scripts in SQL Jobs (all versions) appear to have a major bug in that they report Error: 14277 whenever the string "createobject(" appears more than once anywhere in the script. It does not matter whether the string is just part of a character expression such as: sTemp = "..... createobject( ...."; whether it appears in a comment or whether it is used to actually create an object. Any combination of the above that puts "createobject(" in the script more than once will cause the 14277 error to appear when you try to close the Job modifier.

There is a trick that I have found to overcome this. That is, to create any and all objects in a single common subroutine. Even in this subroutine, you have to trick the system into thinking that you have just destroyed the object that you are trying to create.

The subroutine is cobj. It takes the variable that will become the object and a string that defines the activex control. The "set ... = Nothing" that appears after the "Exit Sub" is the trick that makes the system think that the object is destroyed within the scope of cobj. Note: be sure to destroy the object in the scope where the object variable was defined.

Here is a code sample that sends an email using ASPMAIL, which contains data from an ADO SQL query.

'*********************************************
' ActiveX Script - no 14277 error
'*********************************************
MailMe readSQL(1006), "mymail@.mail.com"

Sub cobj(newobj, ax)
Set newobj = createobject(ax) ' only appears once, here
exit sub
Set newobj = Nothing ' never executed but tricks checker
End Sub

sub MailMe (sMsg, sAddress)
dim Mailer, vRet
if instr(sAddress,"@.")<1 then exit sub

cobj Mailer, "SMTPsvg.Mailer"

Mailer.FromName = "ASP_Debug"
Mailer.FromAddress = sAddress
Mailer.RemoteHost = "127.0.0.1"
Mailer.AddRecipient "", sAddress
Mailer.Subject = "Debug ActiveX Script - 14277 Error"
Mailer.BodyText = sMsg
Mailer.SendMail
Set Mailer=Nothing
end sub

Function readSQL(ndx)
Dim SQL, sConn, oRst
readSQL = "No Record"
SQL = "SELECT Note FROM NoteTable WHERE [ID]=" & CStr(ndx)
sConn = "Provider=SQLOLEDB.1;Initial Catalog=xx;Data Source=zz"

cobj oRst, "ADODB.Recordset"

oRst.Open SQL, sConn
If oRst.State = 1 Then
readSQL = oRst(0)
oRst.Close
End If
Set oRst = Nothing
End Function

[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x0

This error occurs when the ActiveX task tries to execute:

[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x001B6438.

Anybody know how to troubleshoot these errors? I can't find anything on this error code. The same script works in DTS.

I have got the same error, the code is different but couldn't figure out what to do...|||

Hi Igor,

This isn't much of an answer but it is true: you should try to move away from the ActiveX Script Task and use the Script Task instead.

Hope this helps,
Andy

|||Script Task does mean that there is a different scripting language?

I tryed a different thing: I moved all the script from a SSIS task to a SQL Server job (a job with one "activeX script" step).

It sounds great, but I have a different error in the step that says that
"The command script does not destroy all the objects that it creates. Revise the command script. (Microsoft SQL Server, Error: 14277)"

I am not a VB programmer, so I think I have to ask someone to revise the script.

Thanks for the help|||

ActiveX scripts in SQL Jobs (all versions) appear to have a major bug in that they report Error: 14277 whenever the string "createobject(" appears more than once anywhere in the script. It does not matter whether the string is just part of a character expression such as: sTemp = "..... createobject( ...."; whether it appears in a comment or whether it is used to actually create an object. Any combination of the above that puts "createobject(" in the script more than once will cause the 14277 error to appear when you try to close the Job modifier.

There is a trick that I have found to overcome this. That is, to create any and all objects in a single common subroutine. Even in this subroutine, you have to trick the system into thinking that you have just destroyed the object that you are trying to create.

The subroutine is cobj. It takes the variable that will become the object and a string that defines the activex control. The "set ... = Nothing" that appears after the "Exit Sub" is the trick that makes the system think that the object is destroyed within the scope of cobj. Note: be sure to destroy the object in the scope where the object variable was defined.

Here is a code sample that sends an email using ASPMAIL, which contains data from an ADO SQL query.

'*********************************************
' ActiveX Script - no 14277 error
'*********************************************
MailMe readSQL(1006), "mymail@.mail.com"

Sub cobj(newobj, ax)
Set newobj = createobject(ax) ' only appears once, here
exit sub
Set newobj = Nothing ' never executed but tricks checker
End Sub

sub MailMe (sMsg, sAddress)
dim Mailer, vRet
if instr(sAddress,"@.")<1 then exit sub

cobj Mailer, "SMTPsvg.Mailer"

Mailer.FromName = "ASP_Debug"
Mailer.FromAddress = sAddress
Mailer.RemoteHost = "127.0.0.1"
Mailer.AddRecipient "", sAddress
Mailer.Subject = "Debug ActiveX Script - 14277 Error"
Mailer.BodyText = sMsg
Mailer.SendMail
Set Mailer=Nothing
end sub

Function readSQL(ndx)
Dim SQL, sConn, oRst
readSQL = "No Record"
SQL = "SELECT Note FROM NoteTable WHERE [ID]=" & CStr(ndx)
sConn = "Provider=SQLOLEDB.1;Initial Catalog=xx;Data Source=zz"

cobj oRst, "ADODB.Recordset"

oRst.Open SQL, sConn
If oRst.State = 1 Then
readSQL = oRst(0)
oRst.Close
End If
Set oRst = Nothing
End Function

@XMLVariable.query

How do I get the value of ID when an attribute is part of the XML? The
following script shows the problem. It should be ready to execute,
depending on wrapping. Test1 works; test2 and test3 have an attribute, and
do not work.
Thanks,
Richard
set nocount on
use tempdb
go
declare @.Message xml
select @.Message =
N'<?xml version="1.0" standalone="yes" ?>
<DataSet>
<Request>
<ID>14</ID>
</Request>
</DataSet>'
select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test1
go
declare @.Message xml
select @.Message =
N'<?xml version="1.0" standalone="yes" ?>
<DataSet xmlns="http://tempuri.org/DataSet.xsd">
<Request>
<ID>14</ID>
</Request>
</DataSet>'
select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test2
go
declare @.Message xml
select @.Message =
N'<?xml version="1.0" standalone="yes" ?>
<DataSet xmlns="http://tempuri.org/DataSet.xsd">
<Request>
<ID>14</ID>
</Request>
</DataSet>'
select @.Message.query( 'data( /DataSet
xmlns="http://tempuri.org/DataSet.xsd"/Request/ID )' ) as Test3
goDoes this help?
declare @.Message xml
select @.Message =
N'<?xml version="1.0" standalone="yes" ?>
<DataSet xmlns="http://tempuri.org/DataSet.xsd">
<Request>
<ID>14</ID>
</Request>
</DataSet>'
select @.Message.query( 'declare default element namespace
"http://tempuri.org/DataSet.xsd";
data( /DataSet/Request/ID )' ) as Test2 ;
with xmlnamespaces(default 'http://tempuri.org/DataSet.xsd' )
select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test2|||Your xml document is using a default namespace which must be defined somewhe
re.
Here is an example of one way to do it, note the use of the semicolon.
declare @.Message xml
select @.Message =
N'<?xml version="1.0" standalone="yes" ?>
<DataSet xmlns="http://tempuri.org/DataSet.xsd">
<Request>
<ID>14</ID>
</Request>
</DataSet>';
WITH XMLNAMESPACES (DEFAULT 'http://tempuri.org/DataSet.xsd')
select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test2
Dan

> How do I get the value of ID when an attribute is part of the XML?
> The following script shows the problem. It should be ready to
> execute, depending on wrapping. Test1 works; test2 and test3 have an
> attribute, and do not work.
> Thanks,
> Richard
> set nocount on
> use tempdb
> go
> declare @.Message xml
> select @.Message =
> N'<?xml version="1.0" standalone="yes" ?>
> <DataSet>
> <Request>
> <ID>14</ID>
> </Request>
> </DataSet>'
> select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test1
> go
> declare @.Message xml
> select @.Message =
> N'<?xml version="1.0" standalone="yes" ?>
> <DataSet xmlns="http://tempuri.org/DataSet.xsd">
> <Request>
> <ID>14</ID>
> </Request>
> </DataSet>'
> select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test2
> go
> declare @.Message xml
> select @.Message =
> N'<?xml version="1.0" standalone="yes" ?>
> <DataSet xmlns="http://tempuri.org/DataSet.xsd">
> <Request>
> <ID>14</ID>
> </Request>
> </DataSet>'
> select @.Message.query( 'data( /DataSet
> xmlns="http://tempuri.org/DataSet.xsd"/Request/ID )' ) as Test3 go
>|||Perfect! Thanks for the quick replies.
"Richard" <napa299@.yahoo.com> wrote in message
news:%23c%23EM$miGHA.4284@.TK2MSFTNGP05.phx.gbl...
> How do I get the value of ID when an attribute is part of the XML? The
> following script shows the problem. It should be ready to execute,
> depending on wrapping. Test1 works; test2 and test3 have an attribute,
> and do not work.
> Thanks,
> Richard
> set nocount on
> use tempdb
> go
> declare @.Message xml
> select @.Message =
> N'<?xml version="1.0" standalone="yes" ?>
> <DataSet>
> <Request>
> <ID>14</ID>
> </Request>
> </DataSet>'
> select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test1
> go
> declare @.Message xml
> select @.Message =
> N'<?xml version="1.0" standalone="yes" ?>
> <DataSet xmlns="http://tempuri.org/DataSet.xsd">
> <Request>
> <ID>14</ID>
> </Request>
> </DataSet>'
> select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test2
> go
> declare @.Message xml
> select @.Message =
> N'<?xml version="1.0" standalone="yes" ?>
> <DataSet xmlns="http://tempuri.org/DataSet.xsd">
> <Request>
> <ID>14</ID>
> </Request>
> </DataSet>'
> select @.Message.query( 'data( /DataSet
> xmlns="http://tempuri.org/DataSet.xsd"/Request/ID )' ) as Test3
> go
>