Showing posts with label fails. Show all posts
Showing posts with label fails. Show all posts

Monday, March 26, 2012

I would propose the following workaround ...

I setup my ExecuteSQL task to have a "Single Row" resultset. The query returns no rows. It fails. I don't think it should but then maybe this is consistent with the lookup transform piping rows down the error output if there is not a lookup value returned.

The error returned is

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "Variable": "Single Row result set is specified, but no rows were returned.".
Thanks

Allan
Doesn't sound right to me. If you were getting the MAX of something its perfectly plausible that no results would be returned (if no records in the table).

-Jamie|||Exactly Big Smile|||Hmm... interesting. Can you open this on BetaPlace, please?

thanks!
ash|||I'm working with the realease version and having the same problem. Any answer to this?
|||

The following discussion pertains to the Execute SQL Task Control flow.

THE SUGGESTIONS I MAKE ARE TO THE PROPERTIES WHICH YOU HAVE TO RIGHT CLICK AND GO TO PROPERTIES ON THE EXECUTE SQL TASK. DO NOT TRY TO CORRECT THIS PROBLEM IN THE EXECUTE SQL TASK EDITOR.

What you need to do is change the "ForceExecutionResults" property to "Success." This will fix your problem.

Secondly you need to be watchful of your MaximumErrorCount property else your system will fail out.

I hope this helps merry christmas.

|||

SELECT 0 + ISNULL((SELECT MAX( COLUMN1 ) FROM TABLE1 WHERE COLUMN2 = 'XXX'), 0)

OR

SELECT '' + ISNULL((SELECT COLUMN1 FROM TABLE1 WHERE COLUMN2 = 'XXX'), '')

doing so, you will always have a result the task can forward.

Not pretty but preferable vs. the ForceExecutionResults solution i think.

|||

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now. Any feedback about a permanent solution from the SSIS development team?

I also prefer the coded ISNULL workaround over setting the ForceExecutionResults property. You can also use the COALESCE function to the same effect, which I prefer for similar scenarios.

|||

highpockets wrote:

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now.

bug reports should be submitted here: http://connect.microsoft.com/feedback/default.aspx?SiteID=68

Monday, March 12, 2012

I TRIED THE PACKAGE AND IT FAILS ALL THE TIME

I think I am missing something, and this something I could achieve with Enterprise Manager. How do I copy one object, lke a table, including primary keys, indexed and triggers, plus data, from one SQL Server to another, or a set of tables? I am at a loss. Of I choose "Copy Data", that is not the same because the table data is copied, but nothing else, not even the primary key gets transferred. So the arriving table is --quite frankly-- useless. What do I do with a table that lacks keys, constraints, indexes and triggers?. On the other hand, I don't see any click-and-shoot way to transfer the missing information for one single or a few tables together. If I script the table, the table gets dropped and recreated, so I lose the data. In the older Enterprise Manager, you cold choose "transfer objects", and everything moved along, data and script, so the arriving table could be used immediately.
Am I going blind or somebody dropped the ball here?

There is a "Transfer Objects Task" in SSIS which does this. I don't know if it gets used by the import/export wizard cos I've never looked at it but the fact that you can't find it probably means that it isn't in which case you'll have to build your own package to do this. Which won't be at all difficult.

-Jamie|||Jamie is correct. The transfer objects functionality does not appear in the wizard, but is available in the SSIS package designer.|||[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".

To make the problem more clear: I don't need to transfer logins, just the tables, views, stored procedures, user data types, user-defined functions and defaults.

I think that Microsoft watered down the Enterprise Manager, and is doing exactly what they did with VB6 on Vb.NET, take away the best of the tool: in that case it was change-and-contine, and in the case, the wizards became useless. In any case, I built a package and get the error above all the time, no matter what database I intend transfer from SQL 2000. How do I get around this? I can not start working until I transfer my objects and data, so I need some help here, not just some philosophical advice.
If it takes a lot of steps to achieve what we did with one single wizard, the somebody dropped the ball at Microsof. The "Export Data" should allow the user to select all the details of the transfer. I manage close to 20 SQL Server production machines, and I use that transfer wizard day in and day out.|||Funny you bring this up, we were doing some migrations earlier. I have blogged about it here: http://jason.blogsource.com/

Hope this helps steer you in the right direction...|||I read you blog but I can not guess what the reaso for my error is. Any ideas?|||I was referring to your earlier problem: where you could get tbl structures but not data.... The method I illustrated does add extra steps but is a way if you are desperate.|||

So I assume that you in fact used a package to transfer entire databases from SQL 2000 to SQL 2005 and it worked? you never got the error that I am getting?

Message: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".

I wonder what is possibly the matter.

I tried deploying an SSAS project under SQL 2005 dev server, but, deployment fails

This project is with multi data sources, multi cube - when I deploy, I get the following error:

Error 1 Either the 'MELLONBANK\ADCDT2Q' user does not have permission to create a new object in 'SMAMSQL5DEV', or the object does not exist. 0 0

I tried from SQL Server Management studio, I can create tables, write to it fine.

I asked the DBA whether permission problem - she said if SSIS is trying to write to file, there might be some problems, but, database creation shouldn't be a problem. My question is does SSIS try to write some XML when I deploy - my guess is it does with all the properties written in XML. Just trying to confirm with you guys!|||

I assume that you were actually talking about SSAS, not SSIS in your previous post.

The fact that you get this error when deploying an SSAS project means that you are not in the Admin group for SSAS. SSAS is different to the SQL Server relational engine and the fact that you could create tables/databases there does not mean that you have permissions to do this in SSAS.

You will need an Administrator (maybe the person who installed SSAS) to open up SQL Server Management Studio and do 1 of 2 things.

either.

1) Right click on the server, go to properties, click on the security option and add your user account - this will set you up as an SSAS administrator, which will let you create new databases.

Or

2) Create an empty database for you, add a role with your user account and set this role to have admin priviledges just on this database.

|||

My Admin has created an empty database and an Role for Me with Admin Permission,

but I am Still not able to Deploy the Solution with BIDS.

Error was "Für die XYZ-Datenbank wurde ein Identit?tswechselmodus festgelegt, der für Serveradministratoren reserviert ist."

if i freely translate to englisch "For the database xyz a identityimpersonationmode was set that is reserved for system administrators"

Whats wrong there?

Best Regards

HANNES

|||This has to do with how your datasource is configured. If you are not an admin, you cannot deploy a database that has a datasource with the identityImpersonate property set to something like ImpersonateServiceAccount. This is to prevent you possibly getting access to a connection with higher priviledges than your user account. You will either need to set the impersonation mode to another value. If you need it to be set like it is then you could get the admin to change it for you after deployment, the make sure you only deploy changed values or use the Database Deployment Wizard to deploy without the datasources.

I tried deploying an SSAS project under SQL 2005 dev server, but, deployment fails

This project is with multi data sources, multi cube - when I deploy, I get the following error:

Error 1 Either the 'MELLONBANK\ADCDT2Q' user does not have permission to create a new object in 'SMAMSQL5DEV', or the object does not exist. 0 0

I tried from SQL Server Management studio, I can create tables, write to it fine.

I asked the DBA whether permission problem - she said if SSIS is trying to write to file, there might be some problems, but, database creation shouldn't be a problem. My question is does SSIS try to write some XML when I deploy - my guess is it does with all the properties written in XML. Just trying to confirm with you guys!|||

I assume that you were actually talking about SSAS, not SSIS in your previous post.

The fact that you get this error when deploying an SSAS project means that you are not in the Admin group for SSAS. SSAS is different to the SQL Server relational engine and the fact that you could create tables/databases there does not mean that you have permissions to do this in SSAS.

You will need an Administrator (maybe the person who installed SSAS) to open up SQL Server Management Studio and do 1 of 2 things.

either.

1) Right click on the server, go to properties, click on the security option and add your user account - this will set you up as an SSAS administrator, which will let you create new databases.

Or

2) Create an empty database for you, add a role with your user account and set this role to have admin priviledges just on this database.

|||

My Admin has created an empty database and an Role for Me with Admin Permission,

but I am Still not able to Deploy the Solution with BIDS.

Error was "Für die XYZ-Datenbank wurde ein Identit?tswechselmodus festgelegt, der für Serveradministratoren reserviert ist."

if i freely translate to englisch "For the database xyz a identityimpersonationmode was set that is reserved for system administrators"

Whats wrong there?

Best Regards

HANNES

|||This has to do with how your datasource is configured. If you are not an admin, you cannot deploy a database that has a datasource with the identityImpersonate property set to something like ImpersonateServiceAccount. This is to prevent you possibly getting access to a connection with higher priviledges than your user account. You will either need to set the impersonation mode to another value. If you need it to be set like it is then you could get the admin to change it for you after deployment, the make sure you only deploy changed values or use the Database Deployment Wizard to deploy without the datasources.