Problem in inner join ?

Hai ,

select emp_id,count(emp_id) as count1 from poll_data group by emp_id order by count1 desc

The output of above query is

emp_id count1

EMP10041 8
EMP10058 8
EMP10059 6
EMP10008 6
EMP10012 3
EMP10018 3
EMP10039 3
EMP10001 2

I have another table as user_table

user_id user_name

EMP10001 Raja
EMP10039 Ram
EMP10018 Ravi

etc

now i am writing a inner join as follows

select y.user_name, x.count1 from [select emp_id,count(emp_id) as count1 from poll_data group by emp_id order by count1 desc] x inner join [user_table] y on x.emp_id = y.user_id

I want to get the below format

Jambu 8
Elangovan 8
Ravi 6
Anitha 6
Ram 3

but i am getting the below error :

Invalid object name 'select emp_id,count(emp_id) as count1 from poll_data group by emp_id order by count1 desc

how to solve this

remove the [ ] & order by clasue on the sub query. Sub query should be enclosed with ( ).

Code Snippet

select

y.user_name

, x.count1

from

(select emp_id,count(emp_id) as count1 from poll_data group by emp_id) x

inner join [user_table] y on x.emp_id = y.user_id

|||

You need to enclose the derived table in parentheses -NOT square brackets.

Code Snippet

SELECT
u.User_Name,
p.Count1
FROM (SELECT
Emp_ID,
Count1 = count(Emp_ID)
FROM Poll_Data
GROUP BY Emp_ID
) p
INNER JOIN User_Table u
ON p.Emp_ID = u.User_ID

Problem getting security for configuration database

Below is what I have put into an execute sql task container. When I try to run it I have output open and have posted the output. The code runs fine when I have the configuration database set up to only check one server. However, when I add anymore to it, the package will get the security from the first db and then error out. Below is the error output and script. Any help with my problem is greatly appreciated.

**********Begin script*******************

declare @.dbname varchar(200)
declare @.mSql1 varchar(8000)

DECLARE DBName_Cursor CURSOR FOR
select name
from master.dbo.sysdatabases
where name not in ('mssecurity','tempdb')
Order by name

OPEN DBName_Cursor

FETCH NEXT FROM DBName_Cursor INTO @.dbname

WHILE @.@.FETCH_STATUS = 0
BEGIN
Set @.mSQL1 = ' Insert into [tempdb].[dbo].[DBROLES] ( DBName, UserName, db_owner, db_accessadmin,
db_securityadmin, db_ddladmin, db_datareader, db_datawriter,
db_denydatareader, db_denydatawriter )
SELECT '+''''+@.dbName +''''+ ' as DBName ,UserName, '+char(13)+ '
Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,
Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,
Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,
Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,
Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,
Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,
Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,
Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter
from (
select b.name as USERName, c.name as RoleName
from ' + @.dbName+'.dbo.sysmembers a '+char(13)+
' join '+ @.dbName+'.dbo.sysusers b '+char(13)+
' on a.memberuid = b.uid join '+@.dbName +'.dbo.sysusers c
on a.groupuid = c.uid )s
Group by USERName
order by UserName'

--Print @.mSql1
Execute (@.mSql1)

FETCH NEXT FROM DBName_Cursor INTO @.dbname
END

CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor
Go

****************End Script***********************

****************Begin Error Output********************

[Execute SQL Task] Error: Executing the query "declare @.dbname varchar(200) declare @.mSql1 varchar(8000) DECLARE DBName_Cursor CURSOR FOR select name from master.dbo.sysdatabases where name not in ('mssecurity','tempdb') Order by name OPEN DBName_Cursor FETCH NEXT FROM DBName_Cursor INTO @.dbname WHILE @.@.FETCH_STATUS = 0 BEGIN Set @.mSQL1 = ' Insert into [tempdb].[dbo].[DBROLES] ( DBName, UserName, db_owner, db_accessadmin, db_securityadmin, db_ddladmin, db_datareader, db_datawriter, db_denydatareader, db_denydatawriter ) SELECT '+''''+@.dbName +''''+ ' as DBName ,UserName, '+char(13)+ ' Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner, Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin , Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin, Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin, Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader, Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter, Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader, Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter from ( select b.name as USERName, c.name as RoleName from ' + @.dbName+'.dbo.sysmembers a '+char(13)+ ' join '+ @.dbName+'.dbo.sysusers b '+char(13)+ ' on a.memberuid = b.uid join '+@.dbName +'.dbo.sysusers c on a.groupuid = c.uid )s Group by USERName order by UserName' --Print @.mSql1 Execute (@.mSql1) FETCH NEXT FROM DBName_Cursor INTO @.dbname END CLOSE DBName_Cursor DEALLOCATE DBName_Cursor " failed with the following error: "Line 15: Incorrect syntax near '-'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

**************End Error Output**************

Not knowing your database names, I'd suspect that one or more of them have names that need to be bracketed in a SQL statement. The error indicates that one might have a hyphen in the name? Try putting brackets around the @.dbname variable. Ex: "from [' + @.dbName+'].dbo.sysmembers"|||I don't get why the SSIS package would work for one database then. I will try what you suggest though. I am attempting to spearhead the SSIS learning in my department and learning has been pretty slow.|||

kschlap wrote:

I don't get why the SSIS package would work for one database then. I will try what you suggest though. I am attempting to spearhead the SSIS learning in my department and learning has been pretty slow.

What you have would work fine for a database named FooBar, but it would fail with the error you posted if the database were named Foo-Bar.
|||

I"m sorry, I made a mistake in my last post. I meant to say it worked for one server, but won't work for any other server.

-Kyle

|||

I think I have figured out one of the problems. I need to change the OLE DB Connection Manager. However, it won't let me do this and hit ok because it says that the table in my FROM clause is not in the database. Well of course its not on the database because the table gets built dynamically and the servers are gathered dynamically. How can I change this?

-Kyle

|||SSIS tries to validate the tasks when you are editing them; so I guess the table have to exists while you are editing the package. At run time though you can set DelayValidation to true; so the package does not fail validation.|||

The problem with this is that the connection I am trying to use is generated dynamically too. Thus, there are no servers until run time that I could add the table to to get around this error.

-Kyle

|||I don't get that. Does this mean you don't have a development environment where you can create oll required stuff? remebember this is just for validation purposes.|||

No, I have the proper development environment. I just get an error that says that the table doesn't exist whenever I try to make a change to the OLE DB connection. Here is the message I sent to the template creator yesterday.

I am having trouble with changing an OLE DB source. The package is set to not validate until run time, but currently it won’t let me change one of the settings. I keep getting an error that the table it is referring to is not valid. However, I know that it isn’t created because it gets created at run-time. Is there a way to change OLE DB sources? As always any help is appreciated.

|||

I fixed the problem. The package needed me to make an advanced edit instead of a normal one? Weird, but I don't care why it worked just because it worked. If anybody else is building a sql dba configuration database, mine is from a template by sqlmag, please PM me and we can work together on them.

-Kyle

Problem for Calling A Stored Procedure, Please help.

I am writing a Stored Procedure for other server (using C++ to receive the output values) as below, where @.Total ， @.balance， @.A are output values
create proc [MaxTime]

@.number varchar(30),

@.numbera varchar(30),

@.numberb varchar(30)

as

begin

declare @.balancefloat

declare @.table varchar(20)

declare @.freetotal varchar(20)

declare @.SQL nvarchar(4000)

declare @.A float

declare @.Total float

select @.balance = balance, @.table = table, @.freetotal = freetotal

from info where number = @.number
SELECT @.SQL = 'select @.A = A FROM' + @.table

+ ' WHERE LEFT(code, 1) = ' + LEFT(@.incomingcode, 1)

+ ' AND CHARINDEX(LTRIM(RTRIM(code)), ' + @.incomingcode+ ') = 1' +

' ORDER BY LEN(code) DESC'

exec sp_executesql @.sql,N'@.Afloat output',@.Aoutput

set @.Total = @.balance / @.A + @.freetotal

end

return

but the server got nothing, then i wrote another Stored Procedure below:

create proc [MaxTime]

(@.number varchar(30),

@.numbera varchar(30),

@.numberb varchar(30),

@.Total float output,

@.balance float output,

@.A float output)

--here is the only modified i made

as

begin

declare @.table varchar(20)

declare @.freetotal varchar(20)

declare @.SQL nvarchar(4000)

select @.balance = balance, @.table = table, @.freetotal = freetotal

from info where number = @.number
SELECT @.SQL = \'select @.A = A FROM\' + @.table

+ \' WHERE LEFT(code, 1) = \' + LEFT(@.incomingcode, 1)

+ \' AND CHARINDEX(LTRIM(RTRIM(code)), \' + @.incomingcode+ \') = 1\' +

\' ORDER BY LEN(code) DESC\'

exec sp_executesql

--@.sql,N\'@.Afloat output\',@.Aoutput --im not sure about this line

set @.Total = @.balance / @.A + @.freetotal

end

return

system error message：

Msg 170, Level 15, State 1, Procedure MaxTime, Line 11

Line 11: Incorrect syntax near \'@.Total \'.

Msg 137, Level 15, State 1, Procedure MaxTime,, Line 21

Must declare the variable \'@.Total \'.

Please help, appreciated

Hi,xxd

If you wanna get data from database without using dataset.
maybe you can try function.

By the below case, the SQL substring can't add the local varity into the sentance.
It was because the "exec sp_executesql " will be the other Transcation.

|||Hi, HutTsai:

thanks for ur reply, as u mentioned about dataset, did you mean in SQL? or on the other server side?

and for the 'exec sp_executesql', it was only for excuting the dynamic@.sql to get those values that i need to calculate in set @.Total = @.balance / @.A + @.freetotal.

Thanks
|||

Hello xxd

as the requirement,I think you need to clac the value @.total return for Client that call sp [MAXTIME]

this is the sample code I wrote, try it. modi by your sample code.
if you need the detail for this. Let me know. :)

why "cursor"?
my target was get the return value From dynamic-SQLstring,
using the cursor delcare in global.
then fetch its content for out return value.

it's a better method.

Reference: Stored Procedure,Cursor;

Cheers,
Hunt

/* Sample Code by Hunt Begin*/

create proc [MaxTime]
(@.number varchar(30),
@.numbera varchar(30),
@.numberb varchar(30),
@.Total float output)
--here is the only modified i made

as
begin
declare @.table varchar(20)
declare @.freetotal varchar(20)
declare @.SQL nvarchar(4000)
select @.balance = balance, @.table = table, @.freetotal = freetotal
from info where number = @.number

set @.sql =
' Declare tmpcur cursor for '
' select @.A = A FROM' + @.table
+ ' WHERE LEFT(code, 1) = ' + LEFT(@.incomingcode, 1)
+ ' AND CHARINDEX(LTRIM(RTRIM(code)), ' + @.incomingcode+ ') = 1' +
' ORDER BY LEN(code) DESC'

exec (@.sql)
open tmpcur;
Fetch Next From tmpcur into @.A;
close tmpcur;
Deallocate tmpcur;

select @.total = @.balance / @.A + @.freetotal

/*Sample Code by Hunt End*/

|||Thanks Hunt:

1st, there are something wrong for this part below:
' Declare tmpcur cursor for '
' select @.a = a FROM'
please advise me that how to modify it, coz this is my 1st time to see write cursor this way :)
and for my case, i don't really need to use cursor, coz the ' select @.A = A FROM' + @.table
+ ' WHERE LEFT(code, 1) = ' + LEFT(@.incomingcode, 1)
+ ' AND CHARINDEX(LTRIM(RTRIM(code)), ' + @.incomingcode+ ') = 1' +
' ORDER BY LEN(code) DESC'
will only back me one set of data. anyway.

based on your code, i modified mine as below:
alter proc [MaxTime]

(@.number varchar(30),

@.numbera varchar(30),

@.numberb varchar(30),

@.Total float output,

@.balance float output)

as

begin

declare @.table varchar(20)

declare @.freetotal varchar(20)

declare @.SQL nvarchar(4000)

select @.balance = balance, @.table = table, @.freetotal = freetotal

from info where number = @.number
SELECT @.SQL = \'select @.A = A FROM\' + @.table

+ \' WHERE LEFT(code, 1) = \' + LEFT(@.incomingcode, 1)

+ \' AND CHARINDEX(LTRIM(RTRIM(code)), \' + @.incomingcode+ \') = 1\' +

\' ORDER BY LEN(code) DESC\'

exec sp_executesql

--don't know where did i get those \ from

set @.Total = @.balance / @.A + @.freetotal

end

return @.Total
return @.balance

however, got error message like:
Msg 201, Level 16, State 4, Procedure MaxTime, Line 0
Procedure 'MaxTime' expects parameter '@.Total', which was not supplied.

but one step closed i think

Cheers.
|||

ok,the important checkpoint on sys.procedure -> "exec sp_executesql"
check with my sample.
you'll get what you want.
Hint: as you set output varity with value,you shouldn't return any value. it's useless.
Reference with Books Online "sp_executesql"
Cheers,
Hunt
alter proc [sp_test1]
(@.number varchar(30),
@.numbera varchar(30),
@.numberb varchar(30),
@.Total float output,
@.balance float output)
as
declare @.table varchar(20)
declare @.freetotal varchar(20)
declare @.A float
declare @.SQL nvarchar(4000);
declare @.SQLparm nvarchar(500);

set @.table ='car'
set @.balance = 3.0
set @.freetotal = 2.0
--the section below will be the most important.
set @.SQLparm = N'@.A float output'
select @.sql = ' select @.A = 2.0 FROM ' + @.table
exec sp_executesql @.sql,@.SQLparm ,@.A output
set @.Total = @.balance / @.A + @.freetotal
go

declare @.tot float
declare @.free float
exec [sp_test1] '1','2','3',@.tot output,@.free output
print ''
print @.tot
print @.free
go

|||thank you for writing all of those code.

however, why i need those below?
declare @.tot float
declare @.free float
exec [sp_test1] '1','2','3',@.tot output,@.free output

and i think it should be like exec [sp_test1] '1','2','3', @.Total output, @.balance output

otherwise, in fact, there are two clients are calling this procedure 1 of them was alright for getting the outputs, the other one doesn't, it is an application, in this application all i can do is to identify three input parameters,
which are @.number varchar(30),
@.numbera varchar(30),
@.numberb varchar(30),
and there are no more space for @.Total output and @.balance output.

so that when this application pass the exec command to sql server, it would be like exec [sp_test1] '1','2','3' rather than exec [sp_test1] '1','2','3', @.Total output, @.balance output

hope i explained clearly.

Cheers
|||

this thread got a little problem,i can't post any word on it.

try put default value behind the varity.
@.total float =0 out,@.balance float =0 out

in this sample,you can call sp with no output param.
check it.

Cheer.

|||hi thanks,
i'll try it tomorrow, let you know then|||did you mean that
exec [sp_test1] '1','2','3', @.Total float = 0 output, @.balance float = 0 output

?
or exec [sp_test1] '1','2','3','0','0'

it works fine when i am using exec [sp_test1] '1','2','3','0','0' for outputing values for the server (C++) or i do not even need to use the output value, it also works. However i still can not set the @.total float =0 out,@.balance float =0 out for the application case, and that application tells me that it did not get anything.

so is there any better way to solve it out?

many thx
|||

You have to put the default value when you create stored procedure.
as below

Create Proc [MAXTime] (@.numbera varchar(20),@.numberb varchar(20),@.numberc varchar(20),
@.total float =0 out,@.balance float =0 out);

after you alter the sp,you can call the proc by
exec [MAXTime] '1','2','3'
or
exec [MAXTime] '1','2','3',@.total out,@.balance out

try it.

|||hi HuntTsai:

thanks for that, by now i do not thin k the output will solve my problem, after all i realised that there are 3 types of output function of sproc:

1select @.something
2@.something output
3return@.something and return(0)

all of above are the same(of course not) or for some special using?
thanks

|||

I think I don't know what's your original requirement(or question).
maybe it could be describe more detail. On basiclly, the output Question seems like be sloved.

anyway,when we use stored procedure, it was defined for regular process.
and the return types that you said in last post were the normal method.
(I add the 4th as fire_trigger).

1. select @.something
2. @.something output
3. return@.something and return(0)
4. sometimes we also set it up as another type of trigger.

Best Regrads. :)

|||Hi HuntTsai:

Thanks a lot.

Problem executing procedure and how can I use their results in a select statement

I have this Stored Procedure that walk through a table that stores hierarchical data and reorganize the output, so the resultset will be ordered by this hierarchy.

The table structure is (fieldnames in english between parentheses for better comprehension):

CD_CATEGORIA (CD_CATEGORY)
DS_CATEGORIA (DS_CATEGORY)
CD_CATEGORIAMAE (CD_MOTHERCATEGORY)

Here is the Stored Procedure code:

CREATE PROCEDURE [dbo].[sp_RetornaCategorias]

-- Add the parameters for the stored procedure here

@.ID int = 0

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

declare @.TabelaSaida table(

cd_categoria int,

ds_categoria varchar(70),

nr_nivel int);

declare @.i int

select @.i = 0

-- keep going until no more rows added

while @.@.rowcount > 0

begin

select @.i = @.i + 1

insert @.TabelaSaida

-- Get all children of previous level

select Categorias.cd_categoria, Categorias.ds_categoria, @.i + 1

from Categorias, @.TabelaSaida AS TblSaida

where nr_nivel = @.i

and Categorias.cd_categoriamae = TblSaida.cd_categoria

end

-- Saída de dados

-- output with hierarchy formatted

select space((nr_nivel-1)*4) + ds_categoria

from @.TabelaSaida

order by nr_nivel

END

But when I try to execute this Stored Procedure, it runs but nothing is returned.

I'm using this code to execute it:

EXEC [dbo].[sp_RetornaCategorias]

@.ID = 1

Are there anything wrong with it? How can I fix this?

And how can I call a Stored Procedure and get its resultset from a SELECT statement?

Hi Juliano,

Your SP wont actually return anything unless you declare a Variable as OUTPUT.

Your SP's resultset is from the select statement.

There is great MSDN documentation on SP's here; http://msdn2.microsoft.com/en-us/netframework/aa479373.aspx

With regards to fixing it, im not entirely sure its broken yet.

|||

It looks like your insert into the tablevariable is based in a join against that same tablevariable, but when you start out, it's newly created and thus empty..
So, the insert would then yield 0 rows, and the loop will break.

Try to run the SQL statements in a query window, then you can see what happens in each step.

/Kenneth

|||

ur procedure and calling seems to be alright...just check the data in the tyables ur refering...and is there actually nething to be returned.......basically run the select query seperately and check..

this 1...does this gives ne values ?

select Categorias.cd_categoria, Categorias.ds_categoria

from Categorias, @.TabelaSaida AS TblSaida

and Categorias.cd_categoriamae = TblSaida.cd_categoria

|||

I don't see how this could possibly return any rows, since @.TableSaida that is used in the join is newly declared and created, and thus is also empty.

/Kenneth

problem creating assembly using other assemblies

Hi,

I have developed a Stored procedure to output text to a text file, after doing a lot of reading here.
I want to be able to output data to a Postgres database, to be used for web mapping. I have referenced an Assembly Npgsql.dll so I can send updates to the postgres database when my ms sql database is updated. However, after building my class library, when I try to CREATE ASSEMBLY in sql server 2005 express, it sends out a message:
Msg 10301, Level 16, State 1, Line 1
Assembly 'ClassLibrary1' references assembly 'system.drawing, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(error not found)). Please load the referenced assembly into the current database and retry your request.
The error seems to be in the Npgsql.dll assembly, can anybody please shed some light on this.
I tried to load the system.drawing assembly, and can do so in Unrestricted mode. It then spits out the same problem for System.Windows.Forms.dll.
I am still very new to all of this so any help would be appreciated.
Cheers,
Jatz91.

I got around this problem.

I used CREATE ASSEMBLY with PERMISSION_SET=UNSAFE and put the Microsoft.NET Framework Assemblies in the same folder where my built assembly was.

When I used CREATE ASSEMBLY, a warning was spat out for each assembly saying:

Warning: The Microsoft .Net frameworks assembly 'system.drawing, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.

So now I run my CLR in UNSAFE mode

Cheers