Problem with Group By

Hi there,

I am a novice SQL Server Programmer. I have got a task to select multiple fields from multiple tables...I have successfully used joins to get the result. but I have got 2 questions

1. Is there any way of combining (concatenating) three strings as one in the query using "AS"

2. The final result had to be GROUPED by one particular field I am using in select statement...But, for every field in select statement, it lets me run the query without error, only if the field is included Group By clause.

Is there any way to avoid it?

yes, you can concatenate multiple strings into a single field with as...

select (f1+ f2 + f3) as singleString

and you to include any fields in the query in the group by clause...just put the field you are concerned with first. -- jp

|||

hi jp

Thanks for your reply...

your first answer fetched me correct results but regarding the Group By, I still have the problem :(

|||when you use group by you have to include all the fields from the query statement in the group by clause...|||

thank you jp..............

Problem with Execute SQL Task

I am having problems creating an "Execute SQL Task" which calls a stored procedure.

I have tested the procedure successfully using parameters that I have hardcoded on the command line (i.e., EXEC procedure_name 1, 2). This works fine, but I'm having problems using variables(i.e., EXEC procedure_name @.VAR1, @.VAR2). I'm using a ConnectionType of OLE DB.

When I parse the Query I get an error message that says "The query failed to parse. Must declare the variable '@.VAR'".

However, I have this variable declared and assigned a value. I have played around the Parameter Mapping pane but I'm not getting anywhere.

Can anyone shed some light on this particular problem and how I go about fixing this?

thanks

John

John,

Place 'EXEC procedure_name ?, ?' as the SQL Statement. Then, under Parameter Mapping, add a variable select User::Var1 (or whatever the name of Var1 is) as the variable name and enter '0' (zero) as the Parameter Name. Add another variable, select User::Var2 as the variable name and enter '1' as the Parameter Name. That should do it.

Also, I've never been able to parse a query with parameters in it.

Eric

|||I could not reproduce your problem. However, I could use "Execute SQL task" to execute a stored procedure by passing a variable as the input parameter.

I had sp_GetDetails in my db that took one input (varchar type). I created a variable called "inputVal" and assigned a value to it. In my "Execute SQL task", I had "Execute sp_GetDetails @.inputValParam" as my SQLStatement. I also created a mapping between inputVal and inputValParam using "Parameter Mapping" in my task. This task works if the connection type is ADO.Net. If I change that to OLE DB, it does not work.|||Thank you so much. This has been very frustrating at best. I don't think some of these transform tasks are that intuitive at all...|||How would you pass a mix of variables and hardcoded values (i.e., @.var1, @.var2, null, null, 2, "test") without using the parameter mapping?|||

Simply use "EXEC procedure_name ?, ?, 1, NULL, 'Yes', ?, ?". The question marks serve as placeholders for the parameters which you are going to map on the 'Parameter Mapping' page. The parameter list is a 0-based array. So your parameter names will be 0, 1, 2, 3, ..., n respectively, and they're placed into the SQL in the order they're named, so the first '?' corresponds to parameter 0, the second '?' corresponds to parameter 1, etc.

So, if I have User::var1 with a value of 'A' mapped to 0, User::var2 = 'B' -> 1, User::var3 = 'Jim' -> 2, User::var4 = 'Bob' -> 3 the SQL sent via the SQL task would be "EXEC procedure_name 'A', 'B', 1, NULL, 'Yes', 'Jim', 'Bob'".

Hope that doesn't confuse things more.

Eric

|||

Kaarthik,

Kirk has a useful post which may help you here: http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx

-Jamie

|||Thanks for the great info. I'll see if I can get this to work for me. Great help again ...

Problem with enqueue

We are trying to setup simple queue where we are trying to insert(enqueue) record ,it's inserted successfully .

But we can't find any element in the queue.when we used command

(select*fromsys.transmission_queue )

sys.transmission_queue, in column 'transmission_status' we are getting

'The session keys for this conversation could not be created or accessed. The database master key is required for this operation.'

For solving this we have used command

USE master ;

GO

ALTERDATABASE database name SET ENABLE_BROKER ;

but unable to solve it.

When we dequeue an element which already exist in the Queue we are able to dequeue it.

In a sample example we can dequeue the element which can be removed but we can't enqueue the element, we can send you sample if you need.

Regards,

Ali

Add an WITH ENCRYPTION = OFF to your BEGIN DIALOG statements to avoid session keys altogether.

Alternatively you could ensure that the databaseaster key exists and is encrypted with service master key:

create master key encryption by password = 'MyPassword';

alter master key add ADD ENCRYPTION BY SERVICE MASTER KEY;

problem with DtsTransferProvider

I am trying to transfer a database from one SQL Server 2005 server to another. TransferData method completes successfully (and all tables are available in the destination) if the source db contains only default database users (namely dbo, guest, sys). Then I repeated the same process after adding a database user (named 'testdbuser') to the source database, TransferData method threw the following exception, but the database was created in the destination (tables were not available in the destination)
ERROR : errorCode=-1073548784 description=Executing the query "CREATE USER [testdbuser] FOR LOGIN [BUILTIN\Admini..." failed with the following error: "User, group, or role 'testdbuser' already exists in the current database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}

The stack trace was:
StackTrace = " at Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()\r\n at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()\r\n at SMOTransferTest.MyTransfer.TransferDB() ...

This exception is thrown by DtsTransferProvider. I have SSIS installed in my machine and I guess probably that is causing this issue.

Is this a known issue?

I am not explicitly referencing DtsTransferProvider in my project. It references Microsoft.SqlServer.ConnectionInfo.dll and Microsoft.SqlServer.Smo.dll. So, I am not sure why calling TransferData method in the transfer object results in an exception thrown by DtsTransferProvider.

What login is 'testdbuser' mapped to?
Who is the owner of the source database\transferred database?

If I map a 'testuser' to the 'Builtin\Adminstrators' login when adding to the source database and then transfer the database the following error is received:

ERROR : errorCode=-1073548784 description=Executing the query “CREATE USER [testuser] FOR LOGIN [BUILTIN\Administrators]
“failed with the following error: “The login already has an account under a different user name.”.
Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

I don't get this error if the user is added to the database mapped to eg a SQL login 'testuser'.

Ian Dutton

|||

By defaulty transfer does not use IF NOT EXISTS statement when scripting out objects to the target database. If a role already exists in the target it will fail. However you can transfer the roles separately using the t ransfer.CopyAllRoles = true & transfer.Options.IncludeIfNotExists = true settings.

SSIS is used internally to carry out the actual transfer of tables.

jkh

|||Thanks for your response.

I assume you are referring to the "database role" and not "server role".

In my case the "database role" did not exist already because the database was available in the source server only. I was *not* trying to transfer parts of a database to the destination server but I was trying to copy the *entire* database to the destination server. So, the database (or "database role" in that database for that matter) was not present at the destination to start with. When attempting to copy the entire database, I assumed that "database users" and "database roles" would be copied along with other database objects. However, I am getting an error message that says "database user" already exists at the destination. Apparently, this message is thrown while trying to create the database users at the destination.

My question is: why does "CREATE USER" command fails when the database (now available at the destination server) does not have the "database user"?

As I have mentioned in my previous post, in spite of the exception, I could see the database (at the destination server) which, I guess, is nothing but the "model" database in the destination without any tables, views, "database users" or any other database object from the source.|||Within SSIS Database Transfer Task, where do I specify these parameters? I am getting the same errors.|||

Can you please furnish a full example?

This is becoming extremely annoying... nothing works as it should in SSIS when it comes to moving data between databases (short of backup-restore from backup), and any answers we (the users) get, are incomplete. Definitely not the way to go.

problem with DtsTransferProvider

I am trying to transfer a database from one SQL Server 2005 server to another. TransferData method completes successfully (and all tables are available in the destination) if the source db contains only default database users (namely dbo, guest, sys). Then I repeated the same process after adding a database user (named 'testdbuser') to the source database, TransferData method threw the following exception, but the database was created in the destination (tables were not available in the destination)
ERROR : errorCode=-1073548784 description=Executing the query "CREATE USER [testdbuser] FOR LOGIN [BUILTIN\Admini..." failed with the following error: "User, group, or role 'testdbuser' already exists in the current database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}

The stack trace was:
StackTrace = " at Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()\r\n at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()\r\n at SMOTransferTest.MyTransfer.TransferDB() ...

This exception is thrown by DtsTransferProvider. I have SSIS installed in my machine and I guess probably that is causing this issue.

Is this a known issue?

I am not explicitly referencing DtsTransferProvider in my project. It references Microsoft.SqlServer.ConnectionInfo.dll and Microsoft.SqlServer.Smo.dll. So, I am not sure why calling TransferData method in the transfer object results in an exception thrown by DtsTransferProvider.

What login is 'testdbuser' mapped to?
Who is the owner of the source database\transferred database?

If I map a 'testuser' to the 'Builtin\Adminstrators' login when adding to the source database and then transfer the database the following error is received:

ERROR : errorCode=-1073548784 description=Executing the query “CREATE USER [testuser] FOR LOGIN [BUILTIN\Administrators]
“failed with the following error: “The login already has an account under a different user name.”.
Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

I don't get this error if the user is added to the database mapped to eg a SQL login 'testuser'.

Ian Dutton

|||

By defaulty transfer does not use IF NOT EXISTS statement when scripting out objects to the target database. If a role already exists in the target it will fail. However you can transfer the roles separately using the t ransfer.CopyAllRoles = true & transfer.Options.IncludeIfNotExists = true settings.

SSIS is used internally to carry out the actual transfer of tables.

jkh

|||Thanks for your response.

I assume you are referring to the "database role" and not "server role".

In my case the "database role" did not exist already because the database was available in the source server only. I was *not* trying to transfer parts of a database to the destination server but I was trying to copy the *entire* database to the destination server. So, the database (or "database role" in that database for that matter) was not present at the destination to start with. When attempting to copy the entire database, I assumed that "database users" and "database roles" would be copied along with other database objects. However, I am getting an error message that says "database user" already exists at the destination. Apparently, this message is thrown while trying to create the database users at the destination.

My question is: why does "CREATE USER" command fails when the database (now available at the destination server) does not have the "database user"?

As I have mentioned in my previous post, in spite of the exception, I could see the database (at the destination server) which, I guess, is nothing but the "model" database in the destination without any tables, views, "database users" or any other database object from the source.|||Within SSIS Database Transfer Task, where do I specify these parameters? I am getting the same errors.|||

Can you please furnish a full example?

This is becoming extremely annoying... nothing works as it should in SSIS when it comes to moving data between databases (short of backup-restore from backup), and any answers we (the users) get, are incomplete. Definitely not the way to go.

problem with DtsTransferProvider

I am trying to transfer a database from one SQL Server 2005 server to another. TransferData method completes successfully (and all tables are available in the destination) if the source db contains only default database users (namely dbo, guest, sys). Then I repeated the same process after adding a database user (named 'testdbuser') to the source database, TransferData method threw the following exception, but the database was created in the destination (tables were not available in the destination)
ERROR : errorCode=-1073548784 description=Executing the query "CREATE USER [testdbuser] FOR LOGIN [BUILTIN\Admini..." failed with the following error: "User, group, or role 'testdbuser' already exists in the current database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}

The stack trace was:
StackTrace = " at Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()\r\n at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()\r\n at SMOTransferTest.MyTransfer.TransferDB() ...

This exception is thrown by DtsTransferProvider. I have SSIS installed in my machine and I guess probably that is causing this issue.

Is this a known issue?

I am not explicitly referencing DtsTransferProvider in my project. It references Microsoft.SqlServer.ConnectionInfo.dll and Microsoft.SqlServer.Smo.dll. So, I am not sure why calling TransferData method in the transfer object results in an exception thrown by DtsTransferProvider.

What login is 'testdbuser' mapped to?
Who is the owner of the source database\transferred database?

If I map a 'testuser' to the 'Builtin\Adminstrators' login when adding to the source database and then transfer the database the following error is received:

ERROR : errorCode=-1073548784 description=Executing the query “CREATE USER [testuser] FOR LOGIN [BUILTIN\Administrators]
“failed with the following error: “The login already has an account under a different user name.”.
Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

I don't get this error if the user is added to the database mapped to eg a SQL login 'testuser'.

Ian Dutton

|||

By defaulty transfer does not use IF NOT EXISTS statement when scripting out objects to the target database. If a role already exists in the target it will fail. However you can transfer the roles separately using the t ransfer.CopyAllRoles = true & transfer.Options.IncludeIfNotExists = true settings.

SSIS is used internally to carry out the actual transfer of tables.

jkh

|||Thanks for your response.

I assume you are referring to the "database role" and not "server role".

In my case the "database role" did not exist already because the database was available in the source server only. I was *not* trying to transfer parts of a database to the destination server but I was trying to copy the *entire* database to the destination server. So, the database (or "database role" in that database for that matter) was not present at the destination to start with. When attempting to copy the entire database, I assumed that "database users" and "database roles" would be copied along with other database objects. However, I am getting an error message that says "database user" already exists at the destination. Apparently, this message is thrown while trying to create the database users at the destination.

My question is: why does "CREATE USER" command fails when the database (now available at the destination server) does not have the "database user"?

As I have mentioned in my previous post, in spite of the exception, I could see the database (at the destination server) which, I guess, is nothing but the "model" database in the destination without any tables, views, "database users" or any other database object from the source.|||Within SSIS Database Transfer Task, where do I specify these parameters? I am getting the same errors.|||

Can you please furnish a full example?

This is becoming extremely annoying... nothing works as it should in SSIS when it comes to moving data between databases (short of backup-restore from backup), and any answers we (the users) get, are incomplete. Definitely not the way to go.