Error in variable mapping in Execute SQL Task

Hi,

I am getting an error message (mentioned below) in the variable mapping of Execute SQL Task in SSIS.

" Error: ForEach Variable Mapping number 9 to variable "User::Value" cannot be applied. "

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

Pls anyone have a look and give me a solution asap.

Thanks & Regards,

Prakash Srinivasan.

What was the type of the variable "User::Value" and what the was the value that you were trying to assign to this variable?|||

Hi,

Thanks for your response. I was trying to pass the float value and declared the variable as double only. But yesterday I deleted all the variable mappings and did the same mapping again, the problem got resolved.

I don't know what was the problem, even I re-assigned the same index values (starts with zero) for all the variables in the Foreach ADO Enumerator.

Anyway the problem got solved.

Thanks & Regards,

Prakash Srinivasan.

|||

Hi,

I'm seeing the same problem with one package I have put together. I have an "int" variable from a select statement. That variable is mapped to an int32 variable in the package. When I try and foreach through the data set (from previoeus Execute SQL task), it bombs and I get the same error. Error: ForEach Variable Mapping number 1 to variable "User::variablename" cannot be applied. Then in the PostExecute I get the following error: Error: The type of the value being assigned to variable "User::variablename" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Seems to me there must be a bug here that only gets triggered when you create the mapping with the incorrect data type, fix it, and then try again. For some reason, even though you have fixed it, it believes the data types are still the old ones. MS, if you are interested in a package that displays this behavior, then email me.

dcb99

|||

All,

Ok. Figured this out for my case. The problem is the foreach will not allow a NULL to be assigned to an int32 variable. Seems like SSIS should have nullable types built into it. Anyway, when I replace the column in my select statement with an ISNULL(columnname, 0) then it works fine. Too bad the error want something like: "Data is out of range for this variable type. Please adjust data or use aa different variable type."

dcb99

Error in variable mapping in Execute SQL Task

Hi,

I am getting an error message (mentioned below) in the variable mapping of Execute SQL Task in SSIS.

" Error: ForEach Variable Mapping number 9 to variable "User::Value" cannot be applied. "

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

Pls anyone have a look and give me a solution asap.

Thanks & Regards,

Prakash Srinivasan.

What was the type of the variable "User::Value" and what the was the value that you were trying to assign to this variable?|||

Hi,

Thanks for your response. I was trying to pass the float value and declared the variable as double only. But yesterday I deleted all the variable mappings and did the same mapping again, the problem got resolved.

I don't know what was the problem, even I re-assigned the same index values (starts with zero) for all the variables in the Foreach ADO Enumerator.

Anyway the problem got solved.

Thanks & Regards,

Prakash Srinivasan.

|||

Hi,

I'm seeing the same problem with one package I have put together. I have an "int" variable from a select statement. That variable is mapped to an int32 variable in the package. When I try and foreach through the data set (from previoeus Execute SQL task), it bombs and I get the same error. Error: ForEach Variable Mapping number 1 to variable "User::variablename" cannot be applied. Then in the PostExecute I get the following error: Error: The type of the value being assigned to variable "User::variablename" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Seems to me there must be a bug here that only gets triggered when you create the mapping with the incorrect data type, fix it, and then try again. For some reason, even though you have fixed it, it believes the data types are still the old ones. MS, if you are interested in a package that displays this behavior, then email me.

dcb99

|||

All,

Ok. Figured this out for my case. The problem is the foreach will not allow a NULL to be assigned to an int32 variable. Seems like SSIS should have nullable types built into it. Anyway, when I replace the column in my select statement with an ISNULL(columnname, 0) then it works fine. Too bad the error want something like: "Data is out of range for this variable type. Please adjust data or use aa different variable type."

dcb99

Error in stored procedure

When I'm trying to execute my stored procedure I'm getting the following code Line 35: Incorrect syntax near'@.SQL'.

Here is my procedure. Could someone tell me what mistake I'm doing.

Alterprocedure [dbo].[USP_SearchUsersCustomers_New]

@.UserIDINT

,@.RepName VARCHAR(50)

,@.dlStatus VARCHAR(5)=''

as

Declare

@.Criteria VARCHAR(500)

,@.SQL VARCHAR(8000)

SELECT @.Criteria=''

SET NOCOUNTON

if(@.dlStatus<>'ALL'AND(LEN(@.dlStatus)>1))

BEGIN

if(@.dlStatus='ALA')

SET @.Criteria='AND dbo.tbl_Security_Users.IsActive=1'

else

SET @.Criteria='AND dbo.tbl_Security_Users.IsActive=0'

END

--If the user is an Admin, select from all users.

if(dbo.UDF_GetUsersRole(@.UserID)= 1)

BEGIN

@.SQL='SELECT U.UserID

--,U.RoleID

,ISNULL((Select TOP 1 R.RoleName From dbo.tbl_Security_UserRoles UR

INNER JOIN dbo.tbl_Security_Roles R ON R.RoleID = UR.RoleID

Where UR.UserID = U.UserID), 'Unassigned') as 'RoleName'

,U.UserName

,U.Name

,U.Email

,U.IsActive

,U.Phone

FROM dbo.tbl_Security_Users U

--INNER JOIN dbo.tbl_Security_Roles R ON U.RoleID = R.RoleID

WHERE U.NAME LIKE @.RepName

AND U.UserTypeID < 3'+ @.Criteria

END

In your dynamic sql string, you need to escape the single quote by using it twice, i.e.: 'RoleName' should be ''RoleName''.

Also, before you build this string. make sure you test out the actual query first.

|||

I tried it still I get the same error "Incorrect Syntax near @.SQL". The query works fine when I execute it alone.

Here is the code again.

if(dbo.UDF_GetUsersRole(@.UserID)= 1)

BEGIN

@.SQL='SELECT U.UserID

--,U.RoleID

,ISNULL((Select TOP 1 R.RoleName From dbo.tbl_Security_UserRoles UR

INNER JOIN dbo.tbl_Security_Roles R ON R.RoleID = UR.RoleID

Where UR.UserID = U.UserID), ''Unassigned'') as ''RoleName''

,U.UserName

,U.Name

,U.Email

,U.IsActive

,U.Phone

FROM dbo.tbl_Security_Users U

--INNER JOIN dbo.tbl_Security_Roles R ON U.RoleID = R.RoleID

WHERE U.NAME LIKE @.RepName

AND U.UserTypeID < 3'+ @.Criteria

END

|||

SET @.SQL = '-- code here'

|||

I did that. Now when I click compile and execute it doesn't show any error. But when I execute the stored procedure it shows an error "Must declare @.UserID". But it's already declared.

|||

You dont need to use dynamic SQL in your scenario. Try something like this with your regular SELECT statement:

ANDdbo.tbl_Security_Users.IsActive= (CASEWHEN@.dlStatus='ALL'THENdbo.tbl_Security_Users.IsActive

WHEN@.dlStatus='ALA'Then1ELSE0END )

|||

Thanks! It worked and I liked the simplicity of the code while achieving the desired task.

Error in paramterized query - Execute SQL Task

Hi,

I am having some difficulties with a Execute SQL Task, I'll try to describe:

The task contains 2 queries:

UPDATE config SET last_timestamp_int=this_timestamp_int, this_timestamp_int=CAST(GETDATE() AS INT) WHERE company_id=?

SELECT last_timestamp_int AS last_timestamp_int, this_timestamp_int AS this_timestamp_int FROM config WHERE company_id=?

The ? reference to a variable set in Parameter Mapping, which has a initial string value set. Direction set to "Input", Datatype set to "varchar", and parametername to "0". The connectiontype is OLE DB. I have tried to set BypassPrepare to true, but that doesnt help.

The second query fetch 2 values which is stored in the task's Result Set, in two variables. Resultset is set to single row.

When I press Parse Query, I get an error:

"The query failed to parse. Parameter Information cannot be derived from SQL statements. Set parameter information before preparing command."

When I try to run the package, I get this error:

SSIS package "Package.dtsx" starting.

Error: 0xC002F210 at Store last timestamp in variable, Execute SQL Task: Executing the query "UPDATE config SET last_timestamp_int=this_timestamp_int, this_timestamp_int=CAST(GETDATE() AS INT) WHERE company_id=?

SELECT last_timestamp_int AS last_timestamp_int, this_timestamp_int AS this_timestamp_int FROM config WHERE company_id=?" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Store last timestamp in variable

Warning: 0x80019002 at Define global variables: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package.dtsx" finished: Failure.

Can someone please help me identify, what it is I am doing wrong?

Thanks in advance

Your setup looks correct to me, so I have two suggestions for troubleshooting this.

1. Break up the task into two separate tasks, one for the update and a second for the select.

2. Run profiler to see the value and result of your queries when they run.

I don't think there's any issue with the parsing. My understanding is that the parser doesn't know what to do with the ? variable so it always returns an error even if it's a valid working query.

|||Use two parameters, one for the UPDATE and one for the SELECT (even though the values of the parameters are the same)

Error in mdx querys with quotation marks

The problem:
When I try to execute a mdx with quotation mark inside, I get an error!
Why?
I know that (') is from mdx language.
Will I have to erase this characters from my databases?
I don't think it's good.
In my javascript code(asp):
var cst = new ActiveXObject("ADOMD.Cellset");
...
cst.Source = strSource + " CELL PROPERTIES FORMATTED_VALUE, BACK_COLOR,
FORE_COLOR";
cst.open();
strSource = " 'with ...'
Select {[Programa].[Sigla].[Todos os Programas].[Ajuda
r 'A INDUSTRIA],
[Programa].[Sigla].[Todos os Programas].[APOIO A PROJ SOCIAL
],[Programa].
[Sigla].[Todos os Programas].[APOIO ENSINO SUPERIO]}
on rows,{[Measures].[Libera'es]} ON COLUMNS FROM [dwe_cubo
_pird] CELL
PROPERTIES FORMATTED_VALUE, BACK_COLOR, FORE_COLOR"
The error occur when I execute cst.open()!
Microsoft? OLE DB Provider for Analysis Services error '80040e14'
Syntax error, expecting SELECT, near: 'A INDUSTRIA],[Programa].[Sigl
a].
[Todos...
Message posted via http://www.droptable.comYou need to double up quotation marks within quotes:

strSource = " 'with ...'
Select {[Programa].[Sigla].[Todos os Programas].[Ajudar
''A INDUSTRIA],
[Programa].[Sigla].[Todos os Programas].[APOIO A PROJ
SOCIAL],[Programa].
[Sigla].[Todos os Programas].[APOIO ENSINO SUPERIO]}
on rows,{[Measures].[Libera'es]} ON COLUMNS FROM [dwe_cubo
_pird] CELL
PROPERTIES FORMATTED_VALUE, BACK_COLOR, FORE_COLOR"[vbcol=seagreen]
Here's an earlier thread that discusses this:
http://groups-beta.google.com/group...erver.olap/msg/
5b1f83a30f0203eb[vbcol=seagreen]
Newsgroups: microsoft.public.sqlserver.olap
From: "George Spofford"
Date: Sat, 1 Jun 2002 07:33:16 -0700
Subject: MDX Error with Current Year's
One of those silly things: double it up inside.
With member [measures].[abc] as
'count(descendants([category]._[all Category].[current
year''s].[actuals],,leaves))'
HTH
George Spofford
Microsoft MVP
Chief Architect / OLAP Solution Provider
DSS Lab
http://www.dsslab.com
geo...@.dsslab.com
ISVs & IT organizations: Find out how DSS Lab can speed
your development!
[vbcol=seagreen]
>--Original Message--
>Hello,
>the following MDX fails:

>The error comes because the uniquename is ..year's]

>With member [measures].[abc] as
>'count(descendants([category]_.[all Category].[current
>year's].[actuals],,leaves))'

>How can i write a valid mdx?
[vbcol=seagreen]
>Thanks for reading this
>Jrg
>.
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||There is another error!
Microsoft? OLE DB Provider for Analysis Services error '80040e14'
Formula error - cannot find dimension member ("[Programa].[Sigla].&#
91;Todos os
Programas].[APOIO ''A INDUSTRIA]") - in a name-binding function
/Server_1.asp, line 520
"Deepak Puri" wrote:

> You need to double up quotation marks within quotes:
>
> strSource = " 'with ...'
> Select {[Programa].[Sigla].[Todos os Programas].[Ajud
ar ''A INDUSTRIA],
> [Programa].[Sigla].[Todos os Programas].[APOIO A PROJ
> SOCIAL],[Programa].
> [Sigla].[Todos os Programas].[APOIO ENSINO SUPERIO]}
> on rows,{[Measures].[Libera'es]} ON COLUMNS FROM [dwe_cu
bo_pird] CELL
> PROPERTIES FORMATTED_VALUE, BACK_COLOR, FORE_COLOR"
>
> Here's an earlier thread that discusses this:
> http://groups-beta.google.com/group...erver.olap/msg/
> 5b1f83a30f0203eb
> Newsgroups: microsoft.public.sqlserver.olap
> From: "George Spofford"
> Date: Sat, 1 Jun 2002 07:33:16 -0700
> Subject: MDX Error with Current Year's
> One of those silly things: double it up inside.
> With member [measures].[abc] as
> 'count(descendants([category].-[all Category].[current
> year''s].[actuals],,leaves))'
> HTH
> --
> George Spofford
> Microsoft MVP
> Chief Architect / OLAP Solution Provider
> DSS Lab
> http://www.dsslab.com
> geo...@.dsslab.com
> ISVs & IT organizations: Find out how DSS Lab can speed
> your development!
>
>
>
>
>
>
> - Deepak
> Deepak Puri
> Microsoft MVP - SQL Server
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||Maybe you used double-quote characters (Ascii 34), instead of 2
single-quote characters (Ascii 39) - that's the only way I could create
the error message you got?
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Did you copy your code from your application source and paste it here?
The reason I ask is that in my Internet Explorer, the so-called single
quote characters in the member names do not appear as single quotes but
rather as unicode gibberish. The single quotes surrounding calculation
value definitions appear as they should.
The fact your mdx runs on some applications and does not on others leads
me to be farely certain that the member names in your cube do not
contain regular single quote characters but other characters, and that
is what causes your problems.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Error in Maintenance Plan (one of database)

Hello
When i execute the Maintenance Plan,
Database Maintenance Plan (Optimization):
(Check) Remove unused space from database files
Shrink database when it grows beyonds: 50 MB
Amount of free space to remain after shrink: 10 % of data space
Schedude : Occurs every 1 week(s) on Thurday, at 15:04:00
Sucess
[1] Database openview: Removing unused space from the database files (if
database size is more than 50 MB). Reducing free space to 10 percent of
data...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'DB Maintenance Plan openview Optimization' on
13-07-2006 15:04:00
SQLMAINT.EXE Process Exit Code: 0 (Success)
************************************************************
but when i add this,
Database Maintenance Plan (Optimization):
(Check) Reorganize data and index pages
with
Reorganize pages with the original amount of free space
or
Change free space per page percentage to 10 %
(Check) Remove unused space from database files
Shrink database when it grows beyonds: 50 MB
Amount of free space to remain after shrink: 10 % of data space
Schedude : Occurs every 1 week(s) on Thurday, at 15:04:00
Failed - error
[1] Database openview: Index Rebuild (leaving 100%% free space)...
Rebuilding indexes for table 'OV_MS_Annotation'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 9002: [Microsoft][ODBC SQL
Server Driver][SQL Server]The log file for database 'openview' is full. Back
up the transaction log for the database to free up some log space.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.
** Execution Time: 0 hrs, 0 mins, 2 secs **
[2] Database openview: Removing unused space from the database files (if
database size is more than 50 MB). Reducing free space to 10 percent of
data...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'DB Maintenance Plan openview Optimization' on
13-07-2006 15:03:02
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Will it be that it doesn't let to execute everything in the same maintenance
Plan?
Regards,
José Júlio DuarteJosé Júlio Duarte wrote:
> Hello
> When i execute the Maintenance Plan,
> Database Maintenance Plan (Optimization):
> (Check) Remove unused space from database files
> Shrink database when it grows beyonds: 50 MB
> Amount of free space to remain after shrink: 10 % of data space
> Schedude : Occurs every 1 week(s) on Thurday, at 15:04:00
> Sucess
> [1] Database openview: Removing unused space from the database files (if
> database size is more than 50 MB). Reducing free space to 10 percent of
> data...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'DB Maintenance Plan openview Optimization' on
> 13-07-2006 15:04:00
> SQLMAINT.EXE Process Exit Code: 0 (Success)
> ************************************************************
> but when i add this,
> Database Maintenance Plan (Optimization):
> (Check) Reorganize data and index pages
> with
> Reorganize pages with the original amount of free space
> or
> Change free space per page percentage to 10 %
>
> (Check) Remove unused space from database files
> Shrink database when it grows beyonds: 50 MB
> Amount of free space to remain after shrink: 10 % of data space
> Schedude : Occurs every 1 week(s) on Thurday, at 15:04:00
> Failed - error
> [1] Database openview: Index Rebuild (leaving 100%% free space)...
> Rebuilding indexes for table 'OV_MS_Annotation'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 9002: [Microsoft][ODBC SQL
> Server Driver][SQL Server]The log file for database 'openview' is full. Back
> up the transaction log for the database to free up some log space.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> ** Execution Time: 0 hrs, 0 mins, 2 secs **
> [2] Database openview: Removing unused space from the database files (if
> database size is more than 50 MB). Reducing free space to 10 percent of
> data...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'DB Maintenance Plan openview Optimization' on
> 13-07-2006 15:03:02
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
> Will it be that it doesn't let to execute everything in the same maintenance
> Plan?
>
> Regards,
> José Júlio Duarte
>
Reindexing generates a log of transaction log activity, and your
transaction log isn't big enough to handle the volume. Exactly what the
error message says.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hello Tracy
Where can i change to resolve this? and how?
Regards
"Tracy McKibben" wrote:
> José Júlio Duarte wrote:
> > Hello
> >
> > When i execute the Maintenance Plan,
> >
> > Database Maintenance Plan (Optimization):
> >
> > (Check) Remove unused space from database files
> > Shrink database when it grows beyonds: 50 MB
> > Amount of free space to remain after shrink: 10 % of data space
> > Schedude : Occurs every 1 week(s) on Thurday, at 15:04:00
> >
> > Sucess
> >
> > [1] Database openview: Removing unused space from the database files (if
> > database size is more than 50 MB). Reducing free space to 10 percent of
> > data...
> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >
> > Deleting old text reports... 0 file(s) deleted.
> >
> > End of maintenance plan 'DB Maintenance Plan openview Optimization' on
> > 13-07-2006 15:04:00
> > SQLMAINT.EXE Process Exit Code: 0 (Success)
> >
> > ************************************************************
> > but when i add this,
> >
> > Database Maintenance Plan (Optimization):
> >
> > (Check) Reorganize data and index pages
> > with
> > Reorganize pages with the original amount of free space
> > or
> > Change free space per page percentage to 10 %
> >
> >
> >
> > (Check) Remove unused space from database files
> > Shrink database when it grows beyonds: 50 MB
> > Amount of free space to remain after shrink: 10 % of data space
> > Schedude : Occurs every 1 week(s) on Thurday, at 15:04:00
> >
> > Failed - error
> >
> > [1] Database openview: Index Rebuild (leaving 100%% free space)...
> >
> > Rebuilding indexes for table 'OV_MS_Annotation'
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 9002: [Microsoft][ODBC SQL
> > Server Driver][SQL Server]The log file for database 'openview' is full. Back
> > up the transaction log for the database to free up some log space.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> > terminated.
> >
> > ** Execution Time: 0 hrs, 0 mins, 2 secs **
> >
> > [2] Database openview: Removing unused space from the database files (if
> > database size is more than 50 MB). Reducing free space to 10 percent of
> > data...
> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >
> > Deleting old text reports... 0 file(s) deleted.
> >
> > End of maintenance plan 'DB Maintenance Plan openview Optimization' on
> > 13-07-2006 15:03:02
> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
> >
> > Will it be that it doesn't let to execute everything in the same maintenance
> > Plan?
> >
> >
> > Regards,
> >
> > José Júlio Duarte
> >
> Reindexing generates a log of transaction log activity, and your
> transaction log isn't big enough to handle the volume. Exactly what the
> error message says.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||José Júlio Duarte wrote:
> Hello Tracy
> Where can i change to resolve this? and how?
>
Hmmmm... This is precisely why I DESPISE that maintenance plan wizard.
You should not be creating processes like this without understanding
their impact. The fact that you have to ask how to expand (or
auto-grow) a transaction log file tells me that you are in over your head.
Read about "Creating and Maintaining Databases" in Books Online to learn
how to configure automatic file growth.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben wrote:
> José Júlio Duarte wrote:
>> Hello Tracy
>> Where can i change to resolve this? and how?
> Hmmmm... This is precisely why I DESPISE that maintenance plan wizard.
> You should not be creating processes like this without understanding
> their impact. The fact that you have to ask how to expand (or
> auto-grow) a transaction log file tells me that you are in over your head.
> Read about "Creating and Maintaining Databases" in Books Online to learn
> how to configure automatic file growth.
>
After re-reading this, I should clarify. I wasn't attacking you, my
apologies if it looks that way. Those maintenance plan wizards
frustrate me greatly, because they attempt to gloss over what are some
very critical and potentially dangerous processes. Reindexing is an
intensive process, and shouldn't be possible using a wizard, the
administrator should fully understand what's going on throughout the
process. Transaction log backups, if not done properly, can result in
unusable backups, full log files, all sorts of things.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||This is a multi-part message in MIME format.
--090306080204040602090200
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Tracy McKibben wrote:
> Tracy McKibben wrote:
>> José Júlio Duarte wrote:
>> Hello Tracy
>> Where can i change to resolve this? and how?
>>
>> Hmmmm... This is precisely why I DESPISE that maintenance plan
>> wizard. You should not be creating processes like this without
>> understanding their impact. The fact that you have to ask how to
>> expand (or auto-grow) a transaction log file tells me that you are in
>> over your head.
>> Read about "Creating and Maintaining Databases" in Books Online to
>> learn how to configure automatic file growth.
>>
> After re-reading this, I should clarify. I wasn't attacking you, my
> apologies if it looks that way. Those maintenance plan wizards
> frustrate me greatly, because they attempt to gloss over what are some
> very critical and potentially dangerous processes. Reindexing is an
> intensive process, and shouldn't be possible using a wizard, the
> administrator should fully understand what's going on throughout the
> process. Transaction log backups, if not done properly, can result in
> unusable backups, full log files, all sorts of things.
>
Just to add to Tracy's comments. Why do you run this shrink job every
week? In my opinion it won't give you anything but trouble and poor
performance. Especially when you have a limit of 50 Mb which is next to
nothing for a database file it's really waste of time to shrink it.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--090306080204040602090200
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Tracy McKibben wrote:
<blockquote cite="midOTbkp%23ppGHA.1140@.TK2MSFTNGP05.phx.gbl"
type="cite">Tracy McKibben wrote:
<br>
<blockquote type="cite">José Júlio Duarte wrote:
<br>
<blockquote type="cite">Hello Tracy
<br>
Where can i change to resolve this? and how?
<br>
<br>
</blockquote>
<br>
Hmmmm... This is precisely why I DESPISE that maintenance plan wizard.
 You should not be creating processes like this without understanding
their impact. The fact that you have to ask how to expand (or
auto-grow) a transaction log file tells me that you are in over your
head.
<br>
<br>
Read about "Creating and Maintaining Databases" in Books Online to
learn how to configure automatic file growth.
<br>
<br>
<br>
</blockquote>
<br>
After re-reading this, I should clarify. I wasn't attacking you, my
apologies if it looks that way. Those maintenance plan wizards
frustrate me greatly, because they attempt to gloss over what are some
very critical and potentially dangerous processes. Reindexing is an
intensive process, and shouldn't be possible using a wizard, the
administrator should fully understand what's going on throughout the
process. Transaction log backups, if not done properly, can result in
unusable backups, full log files, all sorts of things.
<br>
<br>
</blockquote>
<font size="-1"><font face="Arial"><br>
Just to add to Tracy's comments. Why do you run this shrink job every
week? In my opinion it won't give you anything but trouble and poor
performance. Especially when you have a limit of 50 Mb which is next to
nothing for a database file it's really waste of time to shrink it. <br>
<br>
<br>
-- <br>
Regards<br>
Steen Schlüter Persson<br>
Databaseadministrator / Systemadministrator<br>
</font></font>
</body>
</html>
--090306080204040602090200--

Error in Maintenance Plan (one of database)

Hello
When i execute the Maintenance Plan,
Database Maintenance Plan (Optimization):
(Check) Remove unused space from database files
Shrink database when it grows beyonds: 50 MB
Amount of free space to remain after shrink: 10 % of data space
Schedude : Occurs every 1 week(s) on Thurday, at 15:04:00
Sucess
[1] Database openview: Removing unused space from the database files (if
database size is more than 50 MB). Reducing free space to 10 percent of
data...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'DB Maintenance Plan openview Optimization' on
13-07-2006 15:04:00
SQLMAINT.EXE Process Exit Code: 0 (Success)
****************************************
********************
but when i add this,
Database Maintenance Plan (Optimization):
(Check) Reorganize data and index pages
with
Reorganize pages with the original amount of free space
or
Change free space per page percentage to 10 %
(Check) Remove unused space from database files
Shrink database when it grows beyonds: 50 MB
Amount of free space to remain after shrink: 10 % of data space
Schedude : Occurs every 1 week(s) on Thurday, at 15:04:00
Failed - error
[1] Database openview: Index Rebuild (leaving 100%% free space)...
Rebuilding indexes for table 'OV_MS_Annotation'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 9002: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]The log file for database 'openview' is full.
Back
up the transaction log for the database to free up some log space.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has
been
terminated.
** Execution Time: 0 hrs, 0 mins, 2 secs **
[2] Database openview: Removing unused space from the database files (if
database size is more than 50 MB). Reducing free space to 10 percent of
data...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'DB Maintenance Plan openview Optimization' on
13-07-2006 15:03:02
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Will it be that it doesn't let to execute everything in the same maintenance
Plan?
Regards,
José Júlio DuarteJosé Júlio Duarte wrote:
> Hello
> When i execute the Maintenance Plan,
> Database Maintenance Plan (Optimization):
> (Check) Remove unused space from database files
> Shrink database when it grows beyonds: 50 MB
> Amount of free space to remain after shrink: 10 % of data space
> Schedude : Occurs every 1 week(s) on Thurday, at 15:04:00
> Sucess
> [1] Database openview: Removing unused space from the database files (
if
> database size is more than 50 MB). Reducing free space to 10 percent of
> data...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'DB Maintenance Plan openview Optimization' on
> 13-07-2006 15:04:00
> SQLMAINT.EXE Process Exit Code: 0 (Success)
> ****************************************
********************
> but when i add this,
> Database Maintenance Plan (Optimization):
> (Check) Reorganize data and index pages
> with
> Reorganize pages with the original amount of free space
> or
> Change free space per page percentage to 10 %
>
> (Check) Remove unused space from database files
> Shrink database when it grows beyonds: 50 MB
> Amount of free space to remain after shrink: 10 % of data space
> Schedude : Occurs every 1 week(s) on Thurday, at 15:04:00
> Failed - error
> [1] Database openview: Index Rebuild (leaving 100%% free space)...
> Rebuilding indexes for table 'OV_MS_Annotation'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 9002: [Microsoft]
[ODBC SQL
> Server Driver][SQL Server]The log file for database 'openview' is full
. Back
> up the transaction log for the database to free up some log space.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement h
as been
> terminated.
> ** Execution Time: 0 hrs, 0 mins, 2 secs **
> [2] Database openview: Removing unused space from the database files (
if
> database size is more than 50 MB). Reducing free space to 10 percent of
> data...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'DB Maintenance Plan openview Optimization' on
> 13-07-2006 15:03:02
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
> Will it be that it doesn't let to execute everything in the same maintenan
ce
> Plan?
>
> Regards,
> José Júlio Duarte
>
Reindexing generates a log of transaction log activity, and your
transaction log isn't big enough to handle the volume. Exactly what the
error message says.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hello Tracy
Where can i change to resolve this? and how?
Regards
"Tracy McKibben" wrote:

> José Júlio Duarte wrote:
> Reindexing generates a log of transaction log activity, and your
> transaction log isn't big enough to handle the volume. Exactly what the
> error message says.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||José Júlio Duarte wrote:
> Hello Tracy
> Where can i change to resolve this? and how?
>
Hmmmm... This is precisely why I DESPISE that maintenance plan wizard.
You should not be creating processes like this without understanding
their impact. The fact that you have to ask how to expand (or
auto-grow) a transaction log file tells me that you are in over your head.
Read about "Creating and Maintaining Databases" in Books Online to learn
how to configure automatic file growth.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben wrote:
> José Júlio Duarte wrote:
> Hmmmm... This is precisely why I DESPISE that maintenance plan wizard.
> You should not be creating processes like this without understanding
> their impact. The fact that you have to ask how to expand (or
> auto-grow) a transaction log file tells me that you are in over your head.
> Read about "Creating and Maintaining Databases" in Books Online to learn
> how to configure automatic file growth.
>
After re-reading this, I should clarify. I wasn't attacking you, my
apologies if it looks that way. Those maintenance plan wizards
frustrate me greatly, because they attempt to gloss over what are some
very critical and potentially dangerous processes. Reindexing is an
intensive process, and shouldn't be possible using a wizard, the
administrator should fully understand what's going on throughout the
process. Transaction log backups, if not done properly, can result in
unusable backups, full log files, all sorts of things.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben wrote:
> Tracy McKibben wrote:
> After re-reading this, I should clarify. I wasn't attacking you, my
> apologies if it looks that way. Those maintenance plan wizards
> frustrate me greatly, because they attempt to gloss over what are some
> very critical and potentially dangerous processes. Reindexing is an
> intensive process, and shouldn't be possible using a wizard, the
> administrator should fully understand what's going on throughout the
> process. Transaction log backups, if not done properly, can result in
> unusable backups, full log files, all sorts of things.
>
Just to add to Tracy's comments. Why do you run this shrink job every
week? In my opinion it won't give you anything but trouble and poor
performance. Especially when you have a limit of 50 Mb which is next to
nothing for a database file it's really waste of time to shrink it.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator

Error in LoadFromSqlserver

Hi All,

I have created a SSIS package and deployed it on Sql server. I need to load this package from my C# code. when i am trying to execute following code it is giving error "Cannot find folder \MSDB\DevSAE\MeyyDev1" .. whereas this is already present in side stored packages of server.

pkg = app.LoadFromSqlServer(@."\MSDB\DevSAE\MeyyDev1", "BLRKEC36570D", "sa", "SAEuser123", error);

what could be the reason for this ?

Thanks,

Anshu

Have you read through the books online topic for this?

Don't use MSDB in your path. I think you want: \\DevSAE\MeyyDev1

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.loadfromsqlserver.aspx|||

thanks Phil,

its working file after removing MSDB. Can u please tell me its reason?

|||

Anshu nautiyal wrote:

thanks Phil,

its working file after removing MSDB. Can u please tell me its reason?

MSDB is not needed because that's the only place to store packages inside SQL Server. So why require it? In effect, you were specifying \msdb\msdb\path\package|||

Phil,

Loading package was succesful but when i m executing this package from my C# code it is giving me error . I have done package level setting in code it self like package password and protection level.

i m reading connection string from config file of my application. The connection string inside app.config is

"Data Source=servername;Initial Catalog=DevSAE ;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;"

protection level for this package is EncryptSensitiveWithPassword.

The package itself has same setting for password and protection level.

when i m trying to execute this package this is returning following error :

Microsoft.SqlServer.Dts.Runtime.Package/ : Failed to decrypt protected XML node "DTSassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available

It seems that there is problem with connection string or user id. m not able to trace it. I m new to SSIS i may be doing some silly mistake. but i m not able to resolve it.

can u please give some idea about this?

Thanks

|||

That error is related to ProetctionLevel property of the package. It looks like you are using EncryptWithUSerKey; which means only the author of the apckage can executed. You can use package configuration as described in method 4 in this KB:

http://support.microsoft.com/kb/918760

|||

No i m using EncryptWithPassword protection level. So m trying to pass password to my package before executing it. but its giving me error

Error in Microsoft.SqlServer.Dts.Runtime.Package/ : Failed to decrypt an encrypted XML node because the password was not specified or not correct. Package load will attempt to continue without the encrypted information.

it seems that it is not taking this password. .... is it not pssoible this way ?

Actually i want to execute packages deployed on sql server from my .net code. i m using package object to execute them before that i m setting the password and protection level for them. but still m getting the same error.

Can please help me out on this?

|||how are you executing the package...it should be straight forward as you just need to provide the password in the command line

error in in nested try catch-

HI,

getting error like this while using nested try catch.

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.

also it doesnt roll back because of error, instead of other procedures,insert ,update are executing ending with wrong creations......works partially.

if the try catch is removed form subprocedure1 it works perfectly.

below is the example exactly what i use with more exec procedures in main procedure.

main procedure

begin

begin try

begin transaction

exec subprocedure 1

insert.....

update

COMMIT TRANSACTION

END TRY

BEGIN CATCH

insert into spErrorLog(spName, params, errorMsg)

values('dbo.project_inspectionproject_save', @.newprojectnumber, @.@.error)

if @.@.error <> 0

begin

if @.@.trancount > 0 ROLLBACK TRANSACTION

end

END CATCH

end

sub procedure 1

Begin Try

insert into yy(a,c,c)values(a,b,c)

select @.@.identity

End Try

Begin Catch

IF (XACT_STATE())=-1 ROLLBACK TRANSACTION

insert into spErrorLog(spName, params, errorMsg)

values(@.spName, '', @.errorMsg)

select -1

RAISERROR(@.errorMsg, @.errSeverity, 1)

End Catch

please help me. struggling with for long time.

venp..

Perhaps your RAISERROR in the called proc is not a severity level high enough to force the error in the calling sproc, thereby when the attempted COMMIT finds no active TRANSACTION, you are getting the Transaction Count error message.

Try checking @.TRANCOUNT before the commit just like you do on the ROLLBACK -OR make sure that your RAISERROR is a high enough severity level (is it over 10?) to case the CATCH failure.

|||

HI,

if i remove the try catch from the main procedure sub procedure works fine always. right now i'm using

if @.@.error >o

rollback transaction

--

in my main procedure . i'm using the above st for every transaction st. I dont want to use this old one. Please help me with try catch.()

it doesnt produce any error right now.(just without try catch on main)

my problem is some other person is working on this sub procedure. I 've the main procedure. we both are in situation ro rollback the whole if something goes wrong.

venp

error in executing exec xp_cmdshell

Hello,
I am getting this error
Msg 50001, Level 1, State 50001
xpsql.cpp: Error 1314 from CreateProcessAsUser on line 636
when I try to execute this statement using sql user login
who is not having sysamin rights.
exec xp_cmdshell "copy D:\File1.txt E:\File1.txt"
I have configured the Proxy account for SQL Agent, but
still getting this error.
Can any help me ?
Regds,
ManojDoes the ID have rights to Execute xp_cmdshell? Did you check the NT id for
Proxy and make sure it has adequate rights for your copy? Read from the
root of D and write to the root of E. To check for sure make the Id a
temporary local admin for the windows box and rerun your query. Double
check by creating 2 folders and giving the ID full control of both folders
and change your query to copy to and from the folders instead.
Jeff Duncan
MCDBA, MCSE+I
"Manoj Raheja" <manoj_raheja@.hotmail.com> wrote in message
news:889301c43299$96196bd0$a601280a@.phx.gbl...
> Hello,
> I am getting this error
> Msg 50001, Level 1, State 50001
> xpsql.cpp: Error 1314 from CreateProcessAsUser on line 636
> when I try to execute this statement using sql user login
> who is not having sysamin rights.
> exec xp_cmdshell "copy D:\File1.txt E:\File1.txt"
> I have configured the Proxy account for SQL Agent, but
> still getting this error.
> Can any help me ?
> Regds,
> Manoj|||Make sure the SQL Server startup account has the necessary rights:
- Act as part of the operating system.
- Increase quotas.
- replace process level token.
- Log on as a batch job.
Having increase quotas missing has been a cause of this problem.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||The loging wich I am using is a member of Local and Domain
admin group.

>--Original Message--
>Does the ID have rights to Execute xp_cmdshell? Did you
check the NT id for
>Proxy and make sure it has adequate rights for your
copy? Read from the
>root of D and write to the root of E. To check for sure
make the Id a
>temporary local admin for the windows box and rerun your
query. Double
>check by creating 2 folders and giving the ID full
control of both folders
>and change your query to copy to and from the folders
instead.
>--
>Jeff Duncan
>MCDBA, MCSE+I
>"Manoj Raheja" <manoj_raheja@.hotmail.com> wrote in message
>news:889301c43299$96196bd0$a601280a@.phx.gbl...
636[vbcol=seagreen]
login[vbcol=seagreen]
>
>.
>|||> The loging wich I am using is a member of Local and Domain
> admin group.
Did you assign the SQL Server service account the advanced user rights
detailed in this thread by Rand? The rights are needed so that SQL Server
can change security context to the proxy account. The permissions are set
automatically when you specify the SQL Server service account during
installation or change it from Enterprise manager. However, these are not
set when you change the account directly.
Output from command Windows command NET HELPMSG 1314:
A required privilege is not held by the client.
Hope this helps.
Dan Guzman
SQL Server MVP
"Manoj Raheja" <manoj_raheja@.hotmail.com> wrote in message
news:9a2301c433ea$81364d80$a001280a@.phx.gbl...[vbcol=seagreen]
> The loging wich I am using is a member of Local and Domain
> admin group.
>
> check the NT id for
> copy? Read from the
> make the Id a
> query. Double
> control of both folders
> instead.
> 636
> login|||The problem got solved, The login user was not having the
Increase quotas on the server, which after setting worked
out
Thanks,
Manoj
>--Original Message--
Domain[vbcol=seagreen]
>Did you assign the SQL Server service account the
advanced user rights
>detailed in this thread by Rand? The rights are needed
so that SQL Server
>can change security context to the proxy account. The
permissions are set
>automatically when you specify the SQL Server service
account during
>installation or change it from Enterprise manager.
However, these are not
>set when you change the account directly.
>Output from command Windows command NET HELPMSG 1314:
> A required privilege is not held by the client.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Manoj Raheja" <manoj_raheja@.hotmail.com> wrote in message
>news:9a2301c433ea$81364d80$a001280a@.phx.gbl...
Domain[vbcol=seagreen]
you[vbcol=seagreen]
sure[vbcol=seagreen]
your[vbcol=seagreen]
message[vbcol=seagreen]
line[vbcol=seagreen]
but[vbcol=seagreen]
>
>.
>|||Hi,
One quick question - If I want to have my SQL service startup using a system
account, can I still set it to have "Increase Quotas on the server"? If so,
could you let me know where I can set that?
Thanks!
/ec
"Manoj Raheja" <manoj_raheja@.hotmail.com> wrote in message
news:ac9201c4368c$aa05ba80$a001280a@.phx.gbl...[vbcol=seagreen]
> The problem got solved, The login user was not having the
> Increase quotas on the server, which after setting worked
> out
> Thanks,
> Manoj
> Domain
> advanced user rights
> so that SQL Server
> permissions are set
> account during
> However, these are not
> Domain
> you
> sure
> your
> message
> line
> but

Error in Executing Bulk Load using SQLXML 4.0

I have a number of apps which do xml bulk load using sqlxml 4.0. But, in on
e
program when I attempt to execute the following
SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class bulkLoadObject = new
SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class();
I get this error raised.
"Unable to cast COM object of type 'SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class'
to interface type 'SQLXMLBULKLOADLib.ISQLXMLBulkLoad4'. This operation faile
d
because the QueryInterface call on the COM component for the interface with
IID '{88465BA7-AEEE-49A1-9499-4416287A0160}' failed due to the following
error: No such interface supported (Exception from HRESULT: 0x80004002
(E_NOINTERFACE))."
While in another app the same statement executes with no problems.
I have no compile errors in the offending program. I have dropped the
reference and re-added a couple of times.
Does anyone have any insight?Could this be something related to the interop DLL in the GAC? What
happens when you refresh it?|||All the other apps that use SQLXML 4.0 bulk load on that machine have no
problems.|||Hello,
This is usually due to the fact that you are missing [STAThread] declaration
like in:
[STAThread]
static void Main()
Hope this helps,
Monica Frintu
"AlanS" wrote:

> I have a number of apps which do xml bulk load using sqlxml 4.0. But, in
one
> program when I attempt to execute the following
> SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class bulkLoadObject = new
> SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class();
> I get this error raised.
> "Unable to cast COM object of type 'SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class
'
> to interface type 'SQLXMLBULKLOADLib.ISQLXMLBulkLoad4'. This operation fai
led
> because the QueryInterface call on the COM component for the interface wit
h
> IID '{88465BA7-AEEE-49A1-9499-4416287A0160}' failed due to the following
> error: No such interface supported (Exception from HRESULT: 0x80004002
> (E_NOINTERFACE))."
> While in another app the same statement executes with no problems.
> I have no compile errors in the offending program. I have dropped the
> reference and re-added a couple of times.
> Does anyone have any insight?

Error in execute sql task

I get the following error when trying to execute an sql statement in oracle and returning the results into an object variable with the execute sql task.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "select <columnlist> from <tablename>" failed with the following error: "The SelectCommand property has not been initialized before calling 'Fill'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

It executes fine if I select no results or first row but I can't get full result set to work. The query and connection string are valid. Any ideas?

Hi,

Have you selected the "Full Result Set" in the ResultSet option? If so, then create a variable of datatype "Object" and link that variable in the ResultSet tab. In that, enter "0" in the Result Name and in the Variable Name, select the variable you created as Object.

Pls try this and let me know if you have any issues.

Sorry for the delayed response. I just gone thru this issue.

Prakash Srinivasan

|||I have the same issues with the Script task in the control flow. I have the result set to full and assigned to a datatype of object. I also have 0 for the name of the result set but it still give me the same error. " selectcommand was in initialized before Fill" It would be a paid to enter 300 column names in the script component in the data flow side just because this the Script task isnt work. I will be glad if someone could help me.|||

How are you doing this? The Script Task isn't really supposed to be used for extracting SQL data. Why does Execute SQL Task not work for you?

-Jamie

|||

I am pulling data through an ado.net connection connected to a DB/C 4 database (odbc database). I needed to pull data based on the last date run so i need a way to insert a date into my sql command where clause. I am using an expression to set the sqlcommand of the Script task to "select .....from tableA where timestamp > = " @.[datetime::mydate] ". I have the resultset set to Full result set. I also have my resultset variable as Object::rs_data and the name set to 0 for Full resultset. I still get the error so I am think its probably the fact that the script task does not work for ado.net odbc connnections.

|||

I am still left wondering why you are not doing this in an Execute SQL Task. And yet you say "I have the resultset set to Full result set." Are you really using a script task?

-Jamie

|||Sorry, i wasnt paying attention. I meant to say Execute SQL Task instead of Script task. With that said, is there any ideas for fixing the situation and once to assign a resultset to a variable. How do you use that resultset(variable) as datasource in a Data flow.|||Hi,
does anyone have the solution to the error:
The SelectCommand property has not been initialized before calling 'Fill'."
for Execute SQL Task problem? I am also stuck there...
Daren
|||

I'm facing the same problem while I'm extracting data from an "SQL Task" using a result set "full result set".

Also can someone tell me how to read the temp result set created by the SQL task ? The documentation found is very poor for a novice like me and doesn't explain how to read the System.Data.Dataset in order to feed a SQL server destination table.

Thanks

Error in execute sql task

I get the following error when trying to execute an sql statement in oracle and returning the results into an object variable with the execute sql task.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "select <columnlist> from <tablename>" failed with the following error: "The SelectCommand property has not been initialized before calling 'Fill'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

It executes fine if I select no results or first row but I can't get full result set to work. The query and connection string are valid. Any ideas?

Hi,

Have you selected the "Full Result Set" in the ResultSet option? If so, then create a variable of datatype "Object" and link that variable in the ResultSet tab. In that, enter "0" in the Result Name and in the Variable Name, select the variable you created as Object.

Pls try this and let me know if you have any issues.

Sorry for the delayed response. I just gone thru this issue.

Prakash Srinivasan

|||I have the same issues with the Script task in the control flow. I have the result set to full and assigned to a datatype of object. I also have 0 for the name of the result set but it still give me the same error. " selectcommand was in initialized before Fill" It would be a paid to enter 300 column names in the script component in the data flow side just because this the Script task isnt work. I will be glad if someone could help me.|||

How are you doing this? The Script Task isn't really supposed to be used for extracting SQL data. Why does Execute SQL Task not work for you?

-Jamie

|||

I am pulling data through an ado.net connection connected to a DB/C 4 database (odbc database). I needed to pull data based on the last date run so i need a way to insert a date into my sql command where clause. I am using an expression to set the sqlcommand of the Script task to "select .....from tableA where timestamp > = " @.[datetime::mydate] ". I have the resultset set to Full result set. I also have my resultset variable as Object::rs_data and the name set to 0 for Full resultset. I still get the error so I am think its probably the fact that the script task does not work for ado.net odbc connnections.

|||

I am still left wondering why you are not doing this in an Execute SQL Task. And yet you say "I have the resultset set to Full result set." Are you really using a script task?

-Jamie

|||Sorry, i wasnt paying attention. I meant to say Execute SQL Task instead of Script task. With that said, is there any ideas for fixing the situation and once to assign a resultset to a variable. How do you use that resultset(variable) as datasource in a Data flow.|||Hi,

does anyone have the solution to the error:

The SelectCommand property has not been initialized before calling 'Fill'."

for Execute SQL Task problem? I am also stuck there...

Daren|||

I'm facing the same problem while I'm extracting data from an "SQL Task" using a result set "full result set".

Also can someone tell me how to read the temp result set created by the SQL task ? The documentation found is very poor for a novice like me and doesn't explain how to read the System.Data.Dataset in order to feed a SQL server destination table.

Thanks

Error in execute sql task

I get the following error when trying to execute an sql statement in oracle and returning the results into an object variable with the execute sql task.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "select <columnlist> from <tablename>" failed with the following error: "The SelectCommand property has not been initialized before calling 'Fill'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

It executes fine if I select no results or first row but I can't get full result set to work. The query and connection string are valid. Any ideas?

Hi,

Have you selected the "Full Result Set" in the ResultSet option? If so, then create a variable of datatype "Object" and link that variable in the ResultSet tab. In that, enter "0" in the Result Name and in the Variable Name, select the variable you created as Object.

Pls try this and let me know if you have any issues.

Sorry for the delayed response. I just gone thru this issue.

Prakash Srinivasan

|||I have the same issues with the Script task in the control flow. I have the result set to full and assigned to a datatype of object. I also have 0 for the name of the result set but it still give me the same error. " selectcommand was in initialized before Fill" It would be a paid to enter 300 column names in the script component in the data flow side just because this the Script task isnt work. I will be glad if someone could help me.|||

How are you doing this? The Script Task isn't really supposed to be used for extracting SQL data. Why does Execute SQL Task not work for you?

-Jamie

|||

I am pulling data through an ado.net connection connected to a DB/C 4 database (odbc database). I needed to pull data based on the last date run so i need a way to insert a date into my sql command where clause. I am using an expression to set the sqlcommand of the Script task to "select .....from tableA where timestamp > = " @.[datetime::mydate] ". I have the resultset set to Full result set. I also have my resultset variable as Object::rs_data and the name set to 0 for Full resultset. I still get the error so I am think its probably the fact that the script task does not work for ado.net odbc connnections.

|||

I am still left wondering why you are not doing this in an Execute SQL Task. And yet you say "I have the resultset set to Full result set." Are you really using a script task?

-Jamie

|||Sorry, i wasnt paying attention. I meant to say Execute SQL Task instead of Script task. With that said, is there any ideas for fixing the situation and once to assign a resultset to a variable. How do you use that resultset(variable) as datasource in a Data flow.|||Hi,
does anyone have the solution to the error:
The SelectCommand property has not been initialized before calling 'Fill'."
for Execute SQL Task problem? I am also stuck there...
Daren
|||

I'm facing the same problem while I'm extracting data from an "SQL Task" using a result set "full result set".

Also can someone tell me how to read the temp result set created by the SQL task ? The documentation found is very poor for a novice like me and doesn't explain how to read the System.Data.Dataset in order to feed a SQL server destination table.

Thanks

Error in execute sql task

I get the following error when trying to execute an sql statement in oracle and returning the results into an object variable with the execute sql task.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "select <columnlist> from <tablename>" failed with the following error: "The SelectCommand property has not been initialized before calling 'Fill'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

It executes fine if I select no results or first row but I can't get full result set to work. The query and connection string are valid. Any ideas?

Hi,

Have you selected the "Full Result Set" in the ResultSet option? If so, then create a variable of datatype "Object" and link that variable in the ResultSet tab. In that, enter "0" in the Result Name and in the Variable Name, select the variable you created as Object.

Pls try this and let me know if you have any issues.

Sorry for the delayed response. I just gone thru this issue.

Prakash Srinivasan

|||I have the same issues with the Script task in the control flow. I have the result set to full and assigned to a datatype of object. I also have 0 for the name of the result set but it still give me the same error. " selectcommand was in initialized before Fill" It would be a paid to enter 300 column names in the script component in the data flow side just because this the Script task isnt work. I will be glad if someone could help me.|||

How are you doing this? The Script Task isn't really supposed to be used for extracting SQL data. Why does Execute SQL Task not work for you?

-Jamie

|||

I am pulling data through an ado.net connection connected to a DB/C 4 database (odbc database). I needed to pull data based on the last date run so i need a way to insert a date into my sql command where clause. I am using an expression to set the sqlcommand of the Script task to "select .....from tableA where timestamp > = " @.[datetime::mydate] ". I have the resultset set to Full result set. I also have my resultset variable as Object::rs_data and the name set to 0 for Full resultset. I still get the error so I am think its probably the fact that the script task does not work for ado.net odbc connnections.

|||

I am still left wondering why you are not doing this in an Execute SQL Task. And yet you say "I have the resultset set to Full result set." Are you really using a script task?

-Jamie

|||Sorry, i wasnt paying attention. I meant to say Execute SQL Task instead of Script task. With that said, is there any ideas for fixing the situation and once to assign a resultset to a variable. How do you use that resultset(variable) as datasource in a Data flow.|||Hi,
does anyone have the solution to the error:
The SelectCommand property has not been initialized before calling 'Fill'."
for Execute SQL Task problem? I am also stuck there...
Daren
|||

I'm facing the same problem while I'm extracting data from an "SQL Task" using a result set "full result set".

Also can someone tell me how to read the temp result set created by the SQL task ? The documentation found is very poor for a novice like me and doesn't explain how to read the System.Data.Dataset in order to feed a SQL server destination table.

Thanks

Error in execute a scheduled package

I'm learning SSIS and ran into this error...

I have a package that has its source as an Oracle DB on another server. This package will feed data from that source to a SQL Server 2005 DB. So far, the package works fine if it is executed manually even in SQL Mgmt Studio. It's only failed when I tried it as a scheduled job. I guess I need to do "Package Configurations" which includes the UserID and password for accessing Oracle DB ... but I don't know how. Please help.

Regards,

dnncpt

-

Here is the error message:

Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
05/08/2007 09:49:00,DataFeed,Error,0,<MyDBServer>,DataFeed,(Job outcome),,The job failed. The Job was invoked by Schedule 8 (Schedule DataFeed Grant Tables). The last step to run was step 1 (DataFeed Grant Tables).,00:00:02,0,0,,,,0
05/08/2007 09:49:00,DataFeed,Error,1,<MyDBServer>,DataFeed,DataFeed Grant Tables,,Executed as user: <MyDBServer>\SYSTEM. ....3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:49:00 AM Error: 2007-05-08 09:49:00.81 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTSassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2007-05-08 09:49:01.86 Code: 0xC0202009 Source: AWARDS Connection manager "SourceConnectionOLEDB" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-01005: null password given; logon denied". End Error Error: 2007-05-08 09:49:01.86 Code: 0xC020801C Source: Feed data t... The package execution fa... The step failed.,00:00:02,0,0,,,,0

For the error, I think this link may be the answer:

http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b904800

So the protection level is the reason.

I really love the Microsoft technologies but if MS could provide such a nice software like SQL Server and SSIS why its tech teams don't go a further step as to provide a good how-to document for each application. This could benefit for both MS and its customers. (hope Mr. Gates or Mr. Ballmer read this)

Regards,

dnncpt

Error in Creating Stored Procedure from VS 2005

When I create a stored procedure in VS 2005 using C# and deploy it to the server I can't execute it there and here is the error message:

Msg 6522, Level 16, State 1, Procedure GetAll, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'GetAll':

System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

at System.Security.PermissionSet.Demand()

at System.Data.Common.DbConnectionOptions.DemandPermission()

at System.Data.SqlClient.SqlConnection.PermissionDemand()

at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at StoredProcedures.GetAll()

and the code for creating the stored procedure is:

SqlConnection connDB = new SqlConnection(@."Initial Catalog=MDB;Data Source=Server1;");

SqlCommand cmd = new SqlCommand();

cmd.Connection = connDB;

cmd.CommandText = "SELECT * FROM Modifier";

connDB.Open();

SqlDataReader rdr = cmd.ExecuteReader();

SqlContext.Pipe.Send(rdr);

rdr.Close();

connDB.Close();

your help is appreciated...

What is the permission_set that you assigned for the assembly? This is the one in the CREATE ASSEMBLY. You need to set it to EXTERNAL_ACCESS due to use of SqlConnection that is accessing remote resource. Additionally, you will have to enable TRUST_WORTHY bit (use with care) or do the recommended key based login creation & assign external access assembly permission to it and use that user as owner of the assembly. If you download the new version of SQL Server 2005 Books Online it should contain updated topics that show how to do this. If you need some examples, please post back and I will try to locate a sample for you.|||

I am getting the same error when trying to debug my stored procedure. How do I get around int?

Here's my code:

Try

Dim conn As SqlConnection = New SqlConnection

conn.ConnectionString = "Data Source=XXX-XXXX\SQLSERVER2005;Initial Catalog=MotorFleetConversion;User ID=xxxx;password=xxxx"

conn.Open()

command = New SqlCommand(sqlAction)

'command.Parameters.AddWithValue("@.rating", rating)

command.Connection = conn

' Execute the command and send the results directly to the client

'SqlContext.Pipe.ExecuteAndSend(command)

Dim drUnitCode As SqlDataReader = command.ExecuteReader()

While drUnitCode.Read

If drUnitCode.Item("CompanyCode").ToString <> prevCompanyCode Then

agencySysNo = 0

If drUnitCode.Item("CompanyCode").ToString <> "" Then

agencySysNo = InsertAgency(drUnitCode.Item("CompanyCode").ToString, drUnitCode.Item("UC_DEPARTMENT_DESC").ToString, _

Convert.ToBoolean(drUnitCode.Item("NCAS")), drUnitCode.Item("UC_BILLING_CODE").ToString)

End If

End If

If agencySysNo > 0 Then

InsertDivision(agencySysNo, drUnitCode.Item("UC_DIVISION_DESC").ToString, drUnitCode.Item("UC_SHORT_DEPT_DIV").ToString, _

drUnitCode.Item("UC_CODE_NUMBER").ToString)

End If

prevCompanyCode = drUnitCode.Item("CompanyCode").ToString

End While

Catch ex As Exception

End Try

Thanks!

Error in Creating Stored Procedure from VS 2005

When I create a stored procedure in VS 2005 using C# and deploy it to the server I can't execute it there and here is the error message:

Msg 6522, Level 16, State 1, Procedure GetAll, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'GetAll':

System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

at System.Security.PermissionSet.Demand()

at System.Data.Common.DbConnectionOptions.DemandPermission()

at System.Data.SqlClient.SqlConnection.PermissionDemand()

at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at StoredProcedures.GetAll()

and the code for creating the stored procedure is:

SqlConnection connDB = new SqlConnection(@."Initial Catalog=MDB;Data Source=Server1;");

SqlCommand cmd = new SqlCommand();

cmd.Connection = connDB;

cmd.CommandText = "SELECT * FROM Modifier";

connDB.Open();

SqlDataReader rdr = cmd.ExecuteReader();

SqlContext.Pipe.Send(rdr);

rdr.Close();

connDB.Close();

your help is appreciated...

What is the permission_set that you assigned for the assembly? This is the one in the CREATE ASSEMBLY. You need to set it to EXTERNAL_ACCESS due to use of SqlConnection that is accessing remote resource. Additionally, you will have to enable TRUST_WORTHY bit (use with care) or do the recommended key based login creation & assign external access assembly permission to it and use that user as owner of the assembly. If you download the new version of SQL Server 2005 Books Online it should contain updated topics that show how to do this. If you need some examples, please post back and I will try to locate a sample for you.|||

I am getting the same error when trying to debug my stored procedure. How do I get around int?

Here's my code:

Try

Dim conn As SqlConnection = New SqlConnection

conn.ConnectionString = "Data Source=XXX-XXXX\SQLSERVER2005;Initial Catalog=MotorFleetConversion;User ID=xxxx;password=xxxx"

conn.Open()

command = New SqlCommand(sqlAction)

'command.Parameters.AddWithValue("@.rating", rating)

command.Connection = conn

' Execute the command and send the results directly to the client

'SqlContext.Pipe.ExecuteAndSend(command)

Dim drUnitCode As SqlDataReader = command.ExecuteReader()

While drUnitCode.Read

If drUnitCode.Item("CompanyCode").ToString <> prevCompanyCode Then

agencySysNo = 0

If drUnitCode.Item("CompanyCode").ToString <> ""Then

agencySysNo = InsertAgency(drUnitCode.Item("CompanyCode").ToString, drUnitCode.Item("UC_DEPARTMENT_DESC").ToString, _

Convert.ToBoolean(drUnitCode.Item("NCAS")), drUnitCode.Item("UC_BILLING_CODE").ToString)

EndIf

EndIf

If agencySysNo > 0 Then

InsertDivision(agencySysNo, drUnitCode.Item("UC_DIVISION_DESC").ToString, drUnitCode.Item("UC_SHORT_DEPT_DIV").ToString, _

drUnitCode.Item("UC_CODE_NUMBER").ToString)

EndIf

prevCompanyCode = drUnitCode.Item("CompanyCode").ToString

EndWhile

Catch ex As Exception

EndTry

Thanks!

Error in ActiveX script in DTS

Hi,
When i m trying to execute the following function i m getting
Error Message is Invalid Procedure Call or arguement DTSSource

'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************

' Copy each source column to the destination column
Function Main()
DTSDestination("acct_id") = DTSSource("Account_ID")
DTSDestination("acct_nm") = DTSSource("Account_Name")
DTSDestination("acct_type") = 1
DTSDestination("acct_sts") = DTSSource("Enabled")

Main = DTSTransformStat_OK
End Function

thnks in advanceis DTSDestination("acct_type") of type integer,numeric or decimal?

if not, I think you have to add double quotes around the "1". Hope it helps :)

Error if table has not created yet at the first time

Hello,

I have created a package that check if a table exist otherwise it drop it, then it creates a new one (both with SSIS execute SQL task).

Then a Flow task run some transformation SSIS to load the table just created.

The problem is that when I run the package an error occur telling me that the table does not exist (if runs for the first time and in this case the table has not created yet).

How can I run a package that create a table and then Load it.

Thank

You need to set the DelayValidation property to True on the Data Flow and the Execute SQL tasks.