problem running where table2.column1 in (table1.column2)

I have 2 tables
table1 contains data on a product, and has a column2 with a list of
values separated by a comma (ie. 1577,256,436). column2 is of type
varchar
table2 contains a column1 which has a list of ids (which are the ids
referenced in table1.column2) and another column with descriptions for
those ids. column1 is of type int
if i run a sql statement with a where clause
WHERE table2.column1 in (table1.column2)
i get an error saying its unable to convert varchar to int
the clause with the data in it should look like
WHERE table2.column1 in (1577,256,436)
is it not possible to pass a list of values from another table and
check against it, as if it was a haldfull of int values separated by a
comma?
ive also tried casting both columns to type varchar, and
table1.column2 adding single quotes using a replace() command so that
the values are varchar values, but end up with 0 results.
at the same time if i just run
WHERE table2.column1 in (1577,256,436)
meaning the data is not being passed im just typing it in, than the
query is ran just fine and produces the results im looking for.
any help on this is appreciatedYou didn't provide table definition scripts nor sample data, but I am
guessing that you will need dynamic sql here.
create table #tmp1 (id int identity, a char(1))
insert #tmp1 values ('a')
insert #tmp1 values ('2')
insert #tmp1 values ('d')
create table #tmp2 (b varchar(10))
insert #tmp2 values ('1,2')
declare @.sql varchar(100)
select @.sql = 'select * from #tmp1 where id in (' + b + ')'
from #tmp2
print @.sql
exec (@.sql)
drop table #tmp1
drop table #tmp2
TheSQLGuru
President
Indicium Resources, Inc.
<igornik@.gmail.com> wrote in message
news:1187194472.590943.165600@.d55g2000hsg.googlegroups.com...
>I have 2 tables
> table1 contains data on a product, and has a column2 with a list of
> values separated by a comma (ie. 1577,256,436). column2 is of type
> varchar
> table2 contains a column1 which has a list of ids (which are the ids
> referenced in table1.column2) and another column with descriptions for
> those ids. column1 is of type int
> if i run a sql statement with a where clause
> WHERE table2.column1 in (table1.column2)
> i get an error saying its unable to convert varchar to int
> the clause with the data in it should look like
> WHERE table2.column1 in (1577,256,436)
> is it not possible to pass a list of values from another table and
> check against it, as if it was a haldfull of int values separated by a
> comma?
> ive also tried casting both columns to type varchar, and
> table1.column2 adding single quotes using a replace() command so that
> the values are varchar values, but end up with 0 results.
> at the same time if i just run
> WHERE table2.column1 in (1577,256,436)
> meaning the data is not being passed im just typing it in, than the
> query is ran just fine and produces the results im looking for.
> any help on this is appreciated
>|||/*
Value lists are very bad design and do not belong in Relational
Databases.
I suspect that you currently have something that looks something like
this:
*/
create table #t1 (t1_id int)
create table #t2 (t2_id int, t1_ids varchar(16))
insert into #t1 (t1_id) values (1)
insert into #t1 (t1_id) values (2)
insert into #t1 (t1_id) values (3)
insert into #t1 (t1_id) values (4)
insert into #t2 (t2_id, t1_ids) values (1, '1,3,4')
insert into #t2 (t2_id, t1_ids) values (2, '1,2,3')
select * from #t1
select * from #t2
drop table #t2
drop table #t1
/*
I suspect you would try something like:
select
t1_id,
t2_id
from
t1,t2
where
t1_id in (t1_ids)
But that won't work, this value list should be stored in the proper
case below:
*/
create table #t1 (t1_id int)
create table #t2 (t2_id int, t1_id int)
insert into #t1 (t1_id) values (1)
insert into #t1 (t1_id) values (2)
insert into #t1 (t1_id) values (3)
insert into #t1 (t1_id) values (4)
insert into #t2 (t2_id, t1_id) values (1, 1)
insert into #t2 (t2_id, t1_id) values (1, 3)
insert into #t2 (t2_id, t1_id) values (1, 4)
insert into #t2 (t2_id, t1_ids) values (2, 1)
insert into #t2 (t2_id, t1_ids) values (2, 2)
insert into #t2 (t2_id, t1_ids) values (2, 3)
select * from #t1
select * from #t2
select
t1_id,
t2_id
from
#t1 t1
inner join #t2 t2 on t1.t1_id = t2.t1_id
drop table #t2
drop table #t1
On Aug 15, 12:14 pm, igor...@.gmail.com wrote:
> I have 2 tables
> table1 contains data on a product, and has a column2 with a list of
> values separated by a comma (ie. 1577,256,436). column2 is of type
> varchar
> table2 contains a column1 which has a list of ids (which are the ids
> referenced in table1.column2) and another column with descriptions for
> those ids. column1 is of type int
> if i run a sql statement with a where clause
> WHERE table2.column1 in (table1.column2)
> i get an error saying its unable to convert varchar to int
> the clause with the data in it should look like
> WHERE table2.column1 in (1577,256,436)
> is it not possible to pass a list of values from another table and
> check against it, as if it was a haldfull of int values separated by a
> comma?
> ive also tried casting both columns to type varchar, and
> table1.column2 adding single quotes using a replace() command so that
> the values are varchar values, but end up with 0 results.
> at the same time if i just run
> WHERE table2.column1 in (1577,256,436)
> meaning the data is not being passed im just typing it in, than the
> query is ran just fine and produces the results im looking for.
> any help on this is appreciated|||thanks for all of your help, this got me going into the right
direction again.

problem running vb.net application with sqlexpress in system with only .NET FRAMEWORK

Hi,

I have developed a stand alone application that get data from excel and dumps it in local database (sql server database primary data file) in visual studio .net 2005. I had no issues while developing the application. When i am trying to install this application in the standalone system with .net framework 2.0 and no backend database it giving the following error

provider: sql network interfaces, error 26 - Error locating Server/ Instance Specified

connection string i am using is

Dim objLocalDB As System.Data.SqlClient.SqlConnection

objLocalDB = New System.Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=" & System.AppDomain.CurrentDomain.BaseDirectory & "LFDB.mdf;Integrated Security=True;User Instance=True")

I dont want to use any backend database. I only want to use the database that comes with .net (i.e sqlexpress)

Please help me how can i get through this problem.

hi,

Manyam wrote:

When i am trying to install this application in the standalone system with .net framework 2.0 and no backend database it giving the following error

provider: sql network interfaces, error 26 - Error locating Server/ Instance Specified

I can't understand if you installed SQLExpress as well, on the target computer... did you?

regards

|||SQL Server Express is a backend database although the GUI tools are more lightweight than the ones of the more bigger editions. If you want to use user instances as mentioned in the connection string, you will need to have a SQL Server Express Service in place to attach the database to the Service. SQL Server Express can be deployed within your application (even through clickonce deplyoment but at the end need to be installed).

HTH, Jens K. Suessmeyer.

http://www.sqlserver0205.de
|||

Manyam wrote:

I dont want to use any backend database. I only want to use the database that comes with .net (i.e sqlexpress)

Please help me how can i get through this problem.

SQL Express is not part of the .NET Framework, it is a completely separate product that must be installed if you are going to use it. You can add SQL Express as a prerequisite to you application installation automatically using either ClickOnce or standard Setup Projects in VS 2005, in your projects properties, click the Prerequisite button and make sure SQL Express is checked in the list. This will automatically include the SQL Express installer in your deployment and install it on the target computer if it is needed.

Mike

|||

Hi,

Thanks for your suggestion. So we cannot run vb.net application that is developed using sql server management studio without SQL EXPRESS and only .net framework.

I tried to open the link provided by you. Its not opening. I am getting page cannot be displayed.

|||

Hi,

Thanks for your valueable suggestion. So i cannot run a vb application developed using sql server management studio without SQL EXPRESS.I tried installing SQL EXPRESS in standalone system, but its giving issues.

Process followed -->

Uninstalled existing framework.

Installed .NET framework 2.0Dowloaded SQLEXPRESS and istalled in system.

Its throwing a error that .net should require some updates... What updates do we need to install to get this working.

Thanks in advance.

|||What's your OS? If WIndows XP Pro, this needs SP2 and updated security patches

Problem running stored procedure inside another store procedure

hi,

I want to use a stored procedure inside a stored procedure simulteanously changing the database.
this is my base store procedure

alter PROCEDURE create_file @.dbname sysname
AS
declare @.fname varchar(30)
declare @.fsizes nvarchar
BEGIN
DECLARE @.cmd varchar(1000)
set @.cmd = 'osql -E -d ' + @.dbname + ' -Q "exec ret_sizes @.fname OUTPUT,@.fsizes OUTPUT"'
exec master..xp_cmdshell @.cmd
END

if i execute the his "exec create_file ertis"
i get error as

Msg 137, Level 15, State 2, Server HCC-BPVHD1, Line 1
Must declare the variable '@.fname'.
NULL

the procedure called inside the mail procedure is

alter procedure ret_sizes
@.fname varchar(30) OUTPUT,
@.fsizes nvarchar OUTPUT
as
begin
select @.fname=name, @.fsizes=size from sysfiles
order by fileid
end

Please help meThe @.Cmd string doesn't seem to include the second exec.

set @.cmd = 'osql -E -d ' + @.dbname + ' + '-Q' + ' "exec ret_sizes @.fname OUTPUT,@.fsizes OUTPUT"' '

exec @.CMD

__________________________________________________ ___________

Quote:

Originally Posted by eRTIS SQL

hi,

I want to use a stored procedure inside a stored procedure simulteanously changing the database.
this is my base store procedure

alter PROCEDURE create_file @.dbname sysname
AS
declare @.fname varchar(30)
declare @.fsizes nvarchar
BEGIN
DECLARE @.cmd varchar(1000)
set @.cmd = 'osql -E -d ' + @.dbname + ' -Q "exec ret_sizes @.fname OUTPUT,@.fsizes OUTPUT"'
exec master..xp_cmdshell @.cmd
END

if i execute the his "exec create_file ertis"
i get error as

Msg 137, Level 15, State 2, Server HCC-BPVHD1, Line 1
Must declare the variable '@.fname'.
NULL

the procedure called inside the mail procedure is

alter procedure ret_sizes
@.fname varchar(30) OUTPUT,
@.fsizes nvarchar OUTPUT
as
begin
select @.fname=name, @.fsizes=size from sysfiles
order by fileid
end

Please help me

sql

Problem running stored Procedure

Hi Guys & Gals

I'm having problems running a stored procedure, I'm getting an error that I don't understand. My procedure is this:

ALTER PROC sp_get_allowed_growers
@.GrowerList varchar(500)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.SQL varchar(600)
SET @.SQL =
'SELECT nu_code, nu_description, nu_master
FROM nursery WHERE nu_master IN (' + @.GrowerList + ') ORDER BY nu_code ASC'
EXEC(@.SQL)
END
GO

and the code I'm using to execute the procedure is this:

public DataSet GetGrowers(string Username)
{
System.Text.StringBuilder UserRoles = new System.Text.StringBuilder();
UsersDB ps = new UsersDB();
SqlDataReader dr = ps.GetRolesByUser(Username);
while(dr.Read())
{
UserRoles.Append(dr["RoleName"]+",");
}
UserRoles.Remove(UserRoles.Length-1,1);
//Create instance of Connection and Command objects
SqlConnection transloadConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionStringTARPS"]);
SqlDataAdapter transloadCommand = new SqlDataAdapter("sp_get_allowed_growers",transloadConnection);
 //Create and fill the DataSet
SqlParameter paramList = new SqlParameter("@.GrowerList",SqlDbType.VarChar);
paramList.Value = UserRoles.ToString();
transloadCommand.SelectCommand.Parameters.Add(paramList);
DataSet dsGrowers = new DataSet();
transloadCommand.Fill(dsGrowers);
return dsGrowers;
}

The UserRoles stringbuilder has an appropriate value when it is passed to the stored procedure. When I run the stored procedure in query analyser it runs just fine. However, when I step through the code above, I get the following error:

Line 1: Incorrect syntax near 'sp_get_allowed_growers'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'sp_get_allowed_growers'.

Anyone with any ideas would be very helpful...Try surrounding your parameter list (just the one parameter) in your stored procedure in parentheses. Also, I've never used Alter proc, I use Create Procedure (not to say that Alter proc doesn't work, I don't know - just an observation)|||CREATE PROCEDURE dbo.sp_get_allowed_growers
(
@.GrowerList varchar(500)
)
AS

I always use dbo.sp"NAME" because when you push to production your account may or may not be the dbo.|||Although, technically, you should never preface your stored procedure names with sp_. sp_ is used by sql server to designate system stored procedures. When you preface your own sprocs with sp_, you cause sql server to first search through all system sprocs, then through the local sprocs to find your procedure. In other words, you're adding in a bit of inefficiency to each and every procedure call.|||Thanks for the suggestions guys, I tried that and I still get the same error. It's a bit of an odd one because the stored procedure seems fine. I compiled it in query analyser and I can run it in query analyser by right-clicking and selecting "Open", I'm then prompted for the paramter and it returns the expected result so, as far as I know, it's functioning correctly. When I step through the code calling the procedure, the Autos window shows the value of the parameter as "'Admins','NH'" which is exactly what I'd expect.

I'm pretty sure it's something to do with the way that the value of the parameter is being assigned. In query analyser, when I run the stored proc and I am prompted for the value of the @.GrowerList parameter, I have to type the values in exactly like this 'Admins','NH' and it works.

The odd thing is that the database is raising the error and saying that there's a syntax error in the stored proc when there definitely isn't. It would be different if it was raising a type conversion error but it isn't.

Keep thinking folks, I'll send a small prize to whoever can help me crack it (don't get too excited, it will probably be a company pen or something!)

Cheers,|||Add

transloadCommand.SelectCommand.CommandType=CommandType.StoredProcedure;

before you call .Fill()

And I would also concur that naming an sp with an sp_ prefix will slow things down...|||Thanks doug, that cracked it straight away, I'm very grateful. If you email me your address to imacleverbloke@.mcowan.info I'll pop the freebies in the post. Don't get too excited but they're worth having!

As for the sp_ naming convention, I hear about that performance issue too late and I'm kinda stuck with it until the next comprehensive overhaul.

Cheers guys.

Problem running SSIS from C# program.

I am able to run it from the file system but when I try running it from SQL Server I get an error message that it cannot load package. I am using the following code.

Package package = app.LoadFromSqlServer("trans_hist", Properties.Resources.dbServerName, Properties.Resources.userId, Properties.Resources.password, null);Nobody is doing this?|||What is the error message exactly? Can you see the package in the MSDB database when looking in Management Studio?

Have you read through this? http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.loadfromsqlserver.aspx|||The specified package could not be loaded from the SQL Server Database /r/n

I tried both this
Package package = app.LoadFromSqlServer("trans_hist", Properties.Resources.dbServerName, Properties.Resources.userId, Properties.Resources.password, null);

and this
Package package = app.LoadFromSqlServer("\\MSDB\\Maintenance Plans\\trans_hist", "ppntt240", "id", "password", null);

I also tries adding .dtsx to the SSIS name.|||I think you may want:

"\\Maintenance Plans\\trans_hist"|||That was it, thank you.

Problem running SSIS from C# program.

I am able to run it from the file system but when I try running it from SQL Server I get an error message that it cannot load package. I am using the following code.

Package package = app.LoadFromSqlServer("trans_hist", Properties.Resources.dbServerName, Properties.Resources.userId, Properties.Resources.password, null);

Nobody is doing this?
|||What is the error message exactly? Can you see the package in the MSDB database when looking in Management Studio?

Have you read through this? http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.loadfromsqlserver.aspx|||The specified package could not be loaded from the SQL Server Database /r/n

I tried both this
Package package = app.LoadFromSqlServer("trans_hist", Properties.Resources.dbServerName, Properties.Resources.userId, Properties.Resources.password, null);

and this
Package package = app.LoadFromSqlServer("\\MSDB\\Maintenance Plans\\trans_hist", "ppntt240", "id", "password", null);

I also tries adding .dtsx to the SSIS name.
|||I think you may want:

"\\Maintenance Plans\\trans_hist"|||That was it, thank you.

Problem running SSIS from C# executable when kicked off by another executable.

If I kick off my C# executable from the server everything works fine but if I have another C# executable kick off the C# executable that runs the SSIS I get an error message saying it cannot run SSIS package. I am thinking it sounds like a permissions problem but not sure. Any suggestions/guidance would be greatly appreciated.

Errors I am getting.

2007-09-26 15:02:30,187 [1] ERROR reporting.Processor [(null)] - Problem with Direct Script

2007-09-26 15:02:30,843 [1] ERROR reporting.Processor [(null)] - {Microsoft.SqlServer.Dts.Runtime.DtsError, Microsoft.SqlServer.Dts.Runtime.DtsError, Microsoft.SqlServer.Dts.Runtime.DtsError, Microsoft.SqlServer.Dts.Runtime.DtsError, Microsoft.SqlServer.Dts.Runtime.DtsError}

Thanks in advance.It appears to hqve a different ID running the executable when kicked off from the first executable which is kicked off from JCL. When I run the Exe it has my id and runs fine. What is a better approach to fixing this, changing the roles on the SSIS scripts or adding the id to SQL Server? Assuming this is the problem.|||You might have to better show the whole picture. Where packages are stored, how many packages, what your "C# executables" do, and then you mention JCL, a mainframe concept. Seems like quite a few pieces here.

Do you have package logging turned on?|||

Can you add a message box in your app to display the current credentials? I'd guess that your user credentials aren't being passed from the first app to the second.