Passing DB Name/Owner Name in a prepareCall statement

When calling a stored procedure in sql server passing just the Stored
Procedure name, my stored procedure name works correctly. Below is
the String I use when performing Connection.prepareCall(String)
{CALL sp_name (?,?,?)}
However when I want to specify the db name and the user name (name of
the user who owns the sp), I get an error that the driver could not
find the stored procedure. Any clues as to how this could be fixed?
In other words, how can I set the default database name and owner name
to query against?
{CALL dbName.username.sp_name (?,?,?)}
Ryan wrote:

> When calling a stored procedure in sql server passing just the Stored
> Procedure name, my stored procedure name works correctly. Below is
> the String I use when performing Connection.prepareCall(String)
> {CALL sp_name (?,?,?)}
> However when I want to specify the db name and the user name (name of
> the user who owns the sp), I get an error that the driver could not
> find the stored procedure.
It's the DBMS, not the driver, that can or cannot find a stored procedure.
Your JDBC SQL seems fine. Show the actual exception you get. Maybe that will
help.
Any clues as to how this could be fixed?
> In other words, how can I set the default database name and owner name
> to query against?
> {CALL dbName.username.sp_name (?,?,?)}
|||Joe Weinstein <joeNOSPAM@.bea.com> wrote in message news:<412CF482.9070904@.bea.com>...[vbcol=seagreen]
> Ryan wrote:
>
> It's the DBMS, not the driver, that can or cannot find a stored procedure.
> Your JDBC SQL seems fine. Show the actual exception you get. Maybe that will
> help.
> Any clues as to how this could be fixed?
I get the following exception:
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]The procedure name
'dbName.username1.dbName.userName2.' contains more than the maximum
number of prefixes. The maximum is 3.
The call I pass to Connection.prepareCall is:
{CALL dbName.username2.sp_name (?,?,?)}
where dbname = the name of the database that I logged into. Username1
is the username that I logged in with. Username2 is the username of
the owner of the stored procedure and sp_name is the name of the
stored procedure.
|||Joe Weinstein <joeNOSPAM@.bea.com> wrote in message news:<412CF482.9070904@.bea.com>...[vbcol=seagreen]
> Ryan wrote:
>
> It's the DBMS, not the driver, that can or cannot find a stored procedure.
> Your JDBC SQL seems fine. Show the actual exception you get. Maybe that will
> help.
> Any clues as to how this could be fixed?
I get the following exception:
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]The procedure name
'dbName.username1.dbName.userName2.' contains more than the maximum
number of prefixes. The maximum is 3.
The call I pass to Connection.prepareCall is:
{CALL dbName.username2.sp_name (?,?,?)}
where dbname = the name of the database that I logged into. Username1
is the username that I logged in with. Username2 is the username of
the owner of the stored procedure and sp_name is the name of the
stored procedure.
|||Ryan wrote:

> Joe Weinstein <joeNOSPAM@.bea.com> wrote in message news:<412CF482.9070904@.bea.com>...
>
> I get the following exception:
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
> JDBC][SQLServer]The procedure name
> 'dbName.username1.dbName.userName2.' contains more than the maximum
> number of prefixes. The maximum is 3.
> The call I pass to Connection.prepareCall is:
> {CALL dbName.username2.sp_name (?,?,?)}
> where dbname = the name of the database that I logged into. Username1
> is the username that I logged in with. Username2 is the username of
> the owner of the stored procedure and sp_name is the name of the
> stored procedure.
very odd. Does this code work for you?
It does for me... (logging in as a non-sa user...)
Statement s = c.createStatement();
s.execute("use tempdb");
s.execute("exec master.dbo.sp_who");
PreparedStatement p = c.prepareStatement("{ call master.dbo.sp_who() }");
p.execute();
CallableStatement cl = c.prepareCall("{ call master.dbo.sp_who() }");
cl.execute();
|||Thanks Joe,
I found the culprit in an extended class. It was prepending a default
databaseName.ownerName. causing this "weird" behavior. A wasted
thread,
{CALL dbname.ownername.sp_name (?,?,?)}
will work.
Joe Weinstein wrote:[vbcol=seagreen]
> Ryan wrote:
news:<412CF482.9070904@.bea.com>...[vbcol=seagreen]
Stored[vbcol=seagreen]
is[vbcol=seagreen]
of[vbcol=seagreen]
not[vbcol=seagreen]
procedure.[vbcol=seagreen]
that will[vbcol=seagreen]
name[vbcol=seagreen]
Username1[vbcol=seagreen]
of
> very odd. Does this code work for you?
> It does for me... (logging in as a non-sa user...)
> Statement s = c.createStatement();
> s.execute("use tempdb");
> s.execute("exec master.dbo.sp_who");
> PreparedStatement p = c.prepareStatement("{ call
master.dbo.sp_who() }");
> p.execute();
> CallableStatement cl = c.prepareCall("{ call master.dbo.sp_who()
}");
> cl.execute();

passing boolean to stored proc as SQLDBtype.bit not working

Hi I was hoping that someone might be able to help me with this.

I'm trying to figure out why my VB.net code below generates 0 or 1 but doesn't insert it when I can execute my stored procedure with: exec sp 0

myParm = myCommand.Parameters.Add("@.bolProMembCSNM", SqlDbType.Bit)
myParm.Value = IIf(CBool(rblProMembCSNM.SelectedItem.Value) = True, 1, 0)

I've tried everything I used to use with Classic ASP and am stumped now.
Any ideas? I will have to do this for numerous controls on my pages.

Thanks in advance for any advice.If you can execute the stored procedure with bit value in database, that means the value passed from application is not right. You may check the input value by inserting into a temp. table or as a return value to the calling app.|||thank you, that is what I thought.

I have printed out the value of the param in the trace and it is 0 which I find odd as that should be accepted as a valid bit, unless I'm totally missing something.

Is there not a way to pass 'true' or 'false' to the SQLDBtype.bit and have SQL Server convert it to 1 or 0?

still working on it here.
thanks again for your input|||What kind of control is "rblProMembCSNM"?|||thank you,

I took your advice and created a temp table and insert all of my values, after all that it was a different parameter I was passing and I was not escaping the ' ...... very frustrating to find that out after everything but I did learn quite a bit in my research.

Passing an array to sql

I haven't done sql in a year so I could use some help here. I've got a
procedure below that uses the Function (listed under it here) that is
supposed to parse a string and pass the parsed string as an array to sql.
I'm not doing something correctly. If I pass in a single Symbol (string) my
procedure returns what it is supposed to, but if I pass in a string like thi
s
'A,B,C' nothing is returned, as though there is no parsing taking place.
The function (http://www.sommarskog.se/arrays-in-sql.html#iterative) also
works when I run the example, so there must be some mistake in the way I've
writtem my procedure. I think the line in question is my last 'Join'
statement.
Anyone have any ideas?
Thanks,
Paul
===========
--- My Procedure
--
ALTER PROCEDURE [dbo].[_Portfolios_Basic] (@.PortfolioSymbols NvarChar(max))
AS
SELECT a_Name_Symbol.Name, a_Name_Symbol.Symbol, a_Sector.Sector,
a_Industry.Industry, a_Quarter_Index.Period, a_Financials.[00_Sales] AS
Revenue,
a_Financials.[15_Net_Inc_from_con_ops] AS Income,
a_Financials.[26_EPS_from_con_ops] AS EPS,
a_Financials.[15_Margins_-_NET_con_ops] AS [Net
Margin], a_Financials.PE, a_Hyperlinks.Yahoo_Main AS Yahoo,
a_Hyperlinks.MSN_10Qs AS Financials,
a_Hyperlinks.MSN_events AS Events, a_Hyperlinks.StockCharts AS TA1
FROM a_Hyperlinks
INNER JOIN
a_Financials ON a_Hyperlinks.Yahoo_Main =
a_Financials.Yahoo_Main
INNER JOIN
a_Industry ON a_Financials.Industry = a_Industry.Industry
INNER JOIN
a_Sector ON a_Financials.Sector = a_Sector.Sector
INNER JOIN
a_Quarter_Index ON a_Financials.Period = a_Quarter_Index.Period
INNER JOIN
a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol
JOIN
iter_charlist_to_table(@.PortfolioSymbols
, DEFAULT) s ON
a_Name_Symbol.Symbol = s.nstr
WHERE (a_Name_Symbol.Symbol IN (@.PortfolioSymbols))
ORDER BY a_Name_Symbol.Name
--- iter_charlist_to_table
Function --
List-of-strings
Here is a similar function, but that returns a table of strings.
CREATE FUNCTION iter_charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
Here is an example on how you would use the function:
CREATE PROCEDURE get_company_names_iter @.customers nvarchar(2000) AS
SELECT C.CustomerID, C.CompanyName
FROM Customers C
JOIN iter_charlist_to_table(@.customers, DEFAULT) s ON C.CustomerID
= s.nstr
go
EXEC get_company_names_iter 'ALFKI, BONAP, CACTU, FRANK'I was given the answer.
I just needed to remove the IN function in the WHERE clause.
========================================
=====
"a" wrote:

> I haven't done sql in a year so I could use some help here. I've got a
> procedure below that uses the Function (listed under it here) that is
> supposed to parse a string and pass the parsed string as an array to sql.
> I'm not doing something correctly. If I pass in a single Symbol (string)
my
> procedure returns what it is supposed to, but if I pass in a string like t
his
> 'A,B,C' nothing is returned, as though there is no parsing taking place.
> The function (http://www.sommarskog.se/arrays-in-sql.html#iterative) also
> works when I run the example, so there must be some mistake in the way I'v
e
> writtem my procedure. I think the line in question is my last 'Join'
> statement.
> Anyone have any ideas?
> Thanks,
> Paul
> ===========
>
> --- My Procedur
e --
> ALTER PROCEDURE [dbo].[_Portfolios_Basic] (@.PortfolioSymbols NvarChar(max))
> AS
> SELECT a_Name_Symbol.Name, a_Name_Symbol.Symbol, a_Sector.Sector,
> a_Industry.Industry, a_Quarter_Index.Period, a_Financials.[00_Sales] AS
> Revenue,
> a_Financials.[15_Net_Inc_from_con_ops] AS Income,
> a_Financials.[26_EPS_from_con_ops] AS EPS,
> a_Financials.[15_Margins_-_NET_con_ops] AS [Net
> Margin], a_Financials.PE, a_Hyperlinks.Yahoo_Main AS Yahoo,
> a_Hyperlinks.MSN_10Qs AS Financials,
> a_Hyperlinks.MSN_events AS Events, a_Hyperlinks.StockCharts AS TA1
> FROM a_Hyperlinks
> INNER JOIN
> a_Financials ON a_Hyperlinks.Yahoo_Main =
> a_Financials.Yahoo_Main
> INNER JOIN
> a_Industry ON a_Financials.Industry = a_Industry.Industry
> INNER JOIN
> a_Sector ON a_Financials.Sector = a_Sector.Sector
> INNER JOIN
> a_Quarter_Index ON a_Financials.Period = a_Quarter_Index.Peri
od
> INNER JOIN
> a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol
> JOIN
> iter_charlist_to_table(@.PortfolioSymbols
, DEFAULT) s ON
> a_Name_Symbol.Symbol = s.nstr
> WHERE (a_Name_Symbol.Symbol IN (@.PortfolioSymbols))
> ORDER BY a_Name_Symbol.Name
>
> --- iter_charlist_to_table
> Function --
> List-of-strings
> Here is a similar function, but that returns a table of strings.
> CREATE FUNCTION iter_charlist_to_table
> (@.list ntext,
> @.delimiter nchar(1) = N',')
> RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> str varchar(4000),
> nstr nvarchar(2000)) AS
> BEGIN
> DECLARE @.pos int,
> @.textpos int,
> @.chunklen smallint,
> @.tmpstr nvarchar(4000),
> @.leftover nvarchar(4000),
> @.tmpval nvarchar(4000)
> SET @.textpos = 1
> SET @.leftover = ''
> WHILE @.textpos <= datalength(@.list) / 2
> BEGIN
> SET @.chunklen = 4000 - datalength(@.leftover) / 2
> SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> SET @.textpos = @.textpos + @.chunklen
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> WHILE @.pos > 0
> BEGIN
> SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> END
> SET @.leftover = @.tmpstr
> END
> INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> ltrim(rtrim(@.leftover)))
> RETURN
> END
> Here is an example on how you would use the function:
> CREATE PROCEDURE get_company_names_iter @.customers nvarchar(2000) AS
> SELECT C.CustomerID, C.CompanyName
> FROM Customers C
> JOIN iter_charlist_to_table(@.customers, DEFAULT) s ON C.CustomerI
D
> = s.nstr
> go
> EXEC get_company_names_iter 'ALFKI, BONAP, CACTU, FRANK'
>|||> writtem my procedure. I think the line in question is my last 'Join'
> statement.
I suspect you problem is the WHERE clause:

> WHERE (a_Name_Symbol.Symbol IN (@.PortfolioSymbols))
It looks to me that this an artifact of a previous incorrect technique and
is superseded by your join to the table-valued function. Also, it looks
like you are using SQL 2005 since I see varchar(MAX). In that case, you
might consider passing XML. Untested example:
ALTER PROCEDURE [dbo].[_Portfolios_Basic] (@.PortfolioSymbols xml)
AS
SELECT
a_Name_Symbol.Name,
a_Name_Symbol.Symbol,
a_Sector.Sector,
a_Industry.Industry,
a_Quarter_Index.Period,
a_Financials.[00_Sales] AS Revenue,
a_Financials.[15_Net_Inc_from_con_ops] AS Income,
a_Financials.[26_EPS_from_con_ops] AS EPS,
a_Financials.[15_Margins_-_NET_con_ops] AS [Net Margin],
a_Financials.PE,
a_Hyperlinks.Yahoo_Main AS Yahoo,
a_Hyperlinks.MSN_10Qs AS Financials,
a_Hyperlinks.MSN_events AS Events,
a_Hyperlinks.StockCharts AS TA1
FROM a_Hyperlinks
JOIN a_Financials ON
a_Hyperlinks.Yahoo_Main = a_Financials.Yahoo_Main
JOIN a_Industry ON
a_Financials.Industry = a_Industry.Industry
JOIN a_Sector ON
a_Financials.Sector = a_Sector.Sector
JOIN a_Quarter_Index ON
a_Financials.Period = a_Quarter_Index.Period
JOIN a_Name_Symbol ON
a_Financials.Symbol = a_Name_Symbol.Symbol
JOIN (SELECT CAST(PortfolioSymbols.PortfolioSymbol.query('.') AS char(5)) AS
PortfolioSymbol
FROM @.PortfolioSymbols.nodes('/PortfolioSymbols/PortfolioSymbol/text()')
PortfolioSymbols(PortfolioSymbol)) AS PortfolioSymbols ON
a_Name_Symbol.Symbol = PortfolioSymbols.PortfolioSymbol
ORDER BY a_Name_Symbol.Name
GO
EXEC get_company_names_iter '<PortfolioSymbols>
<PortfolioSymbol>ALFKI</PortfolioSymbol>
<PortfolioSymbol>BONAP</PortfolioSymbol>
<PortfolioSymbol>CACTU</PortfolioSymbol>
<PortfolioSymbol>FRANK</PortfolioSymbol>
</PortfolioSymbols>'
Hope this helps.
Dan Guzman
SQL Server MVP
"a" <a@.discussions.microsoft.com> wrote in message
news:B6AF913C-74FE-4214-BBA1-768ED66ADD67@.microsoft.com...
>I haven't done sql in a year so I could use some help here. I've got a
> procedure below that uses the Function (listed under it here) that is
> supposed to parse a string and pass the parsed string as an array to sql.
> I'm not doing something correctly. If I pass in a single Symbol (string)
> my
> procedure returns what it is supposed to, but if I pass in a string like
> this
> 'A,B,C' nothing is returned, as though there is no parsing taking place.
> The function (http://www.sommarskog.se/arrays-in-sql.html#iterative) also
> works when I run the example, so there must be some mistake in the way
> I've
> writtem my procedure. I think the line in question is my last 'Join'
> statement.
> Anyone have any ideas?
> Thanks,
> Paul
> ===========
>
> --- My
> Procedure --
> ALTER PROCEDURE [dbo].[_Portfolios_Basic] (@.PortfolioSymbols
> NvarChar(max))
> AS
> SELECT a_Name_Symbol.Name, a_Name_Symbol.Symbol, a_Sector.Sector,
> a_Industry.Industry, a_Quarter_Index.Period, a_Financials.[00_Sales] AS
> Revenue,
> a_Financials.[15_Net_Inc_from_con_ops] AS Income,
> a_Financials.[26_EPS_from_con_ops] AS EPS,
> a_Financials.[15_Margins_-_NET_con_ops] AS [Net
> Margin], a_Financials.PE, a_Hyperlinks.Yahoo_Main AS Yahoo,
> a_Hyperlinks.MSN_10Qs AS Financials,
> a_Hyperlinks.MSN_events AS Events, a_Hyperlinks.StockCharts AS TA1
> FROM a_Hyperlinks
> INNER JOIN
> a_Financials ON a_Hyperlinks.Yahoo_Main =
> a_Financials.Yahoo_Main
> INNER JOIN
> a_Industry ON a_Financials.Industry = a_Industry.Industry
> INNER JOIN
> a_Sector ON a_Financials.Sector = a_Sector.Sector
> INNER JOIN
> a_Quarter_Index ON a_Financials.Period =
> a_Quarter_Index.Period
> INNER JOIN
> a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol
> JOIN
> iter_charlist_to_table(@.PortfolioSymbols
, DEFAULT) s ON
> a_Name_Symbol.Symbol = s.nstr
> WHERE (a_Name_Symbol.Symbol IN (@.PortfolioSymbols))
> ORDER BY a_Name_Symbol.Name
>
> --- iter_charlist_to_table
> Function --
> List-of-strings
> Here is a similar function, but that returns a table of strings.
> CREATE FUNCTION iter_charlist_to_table
> (@.list ntext,
> @.delimiter nchar(1) = N',')
> RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> str varchar(4000),
> nstr nvarchar(2000)) AS
> BEGIN
> DECLARE @.pos int,
> @.textpos int,
> @.chunklen smallint,
> @.tmpstr nvarchar(4000),
> @.leftover nvarchar(4000),
> @.tmpval nvarchar(4000)
> SET @.textpos = 1
> SET @.leftover = ''
> WHILE @.textpos <= datalength(@.list) / 2
> BEGIN
> SET @.chunklen = 4000 - datalength(@.leftover) / 2
> SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> SET @.textpos = @.textpos + @.chunklen
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> WHILE @.pos > 0
> BEGIN
> SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> END
> SET @.leftover = @.tmpstr
> END
> INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> ltrim(rtrim(@.leftover)))
> RETURN
> END
> Here is an example on how you would use the function:
> CREATE PROCEDURE get_company_names_iter @.customers nvarchar(2000) AS
> SELECT C.CustomerID, C.CompanyName
> FROM Customers C
> JOIN iter_charlist_to_table(@.customers, DEFAULT) s ON C.CustomerID
> = s.nstr
> go
> EXEC get_company_names_iter 'ALFKI, BONAP, CACTU, FRANK'
>

Passing a parameter as list in a Stored procedure

Hi All
How can i create a stored procedure which can pass a parameter as a list.
Below is sample sp:
create procedure spxx (@.@.param1 varchar(100))
as
select * from tb1 where col1 = @.@.param1
When executing i like would like to be able to do this:
1. exec spxx 'John'
0r
2. exec spxx 'John', 'Mary', 'Susan'
Thank you in advance.http://www.aspfaq.com/2248
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:B4089DB0-3CA6-432E-BCC2-108BB5502D74@.microsoft.com...
> Hi All
> How can i create a stored procedure which can pass a parameter as a list.
> Below is sample sp:
> create procedure spxx (@.@.param1 varchar(100))
> as
> select * from tb1 where col1 = @.@.param1
> When executing i like would like to be able to do this:
> 1. exec spxx 'John'
> 0r
> 2. exec spxx 'John', 'Mary', 'Susan'
> Thank you in advance.
>|||You pass it as a string and split it inside the Stored Proc.
Check this out...
http://www.sommarskog.se/arrays-in-sql.html
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||MittyKom wrote:
> Hi All
> How can i create a stored procedure which can pass a parameter as a list.
> Below is sample sp:
> create procedure spxx (@.@.param1 varchar(100))
> as
> select * from tb1 where col1 = @.@.param1
> When executing i like would like to be able to do this:
> 1. exec spxx 'John'
> 0r
> 2. exec spxx 'John', 'Mary', 'Susan'
> Thank you in advance.
>
One method:
http://www.realsqlguy.com/?p=9|||>> How can I create a stored procedure which can pass a parameter as a list.
<<
This is a common newbie error. Which you did not bother to Google
before posting, did you? It immediately tells us that:
1) You do not know what a compiled program is, but expect SQL to be an
intrpreter.
2) You do not know what a parameter or understand the concept of a
scalar value.
3) You do not know that a table is the only data structure in SQL --
there are no lists!
The kludge you get in a newsgroup is dynamic SQL, with all the problems
that come with it. Hey, but a kludge is so much easier and faster than
an education, or even a Google search beforee posting!
We can also give you some pure SQL that split up a string. But that is
not the point; this is a bad design and poor programming. If you
really give a damn about doing it right, Please post DDL, so that
people do not have to guess what the keys, constraints, Declarative
Referential Integrity, data types, etc. in your schema are. Sample data
is also a good idea, along with clear specifications.|||And there is this one:
http://www.realsqlguy.com/?p=9
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:A220E647-6408-4428-A397-49E268158664@.microsoft.com...
> You pass it as a string and split it inside the Stored Proc.
> Check this out...
> http://www.sommarskog.se/arrays-in-sql.html
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||Thank you all. Is there a way of doing this without using a function?
"Omnibuzz" wrote:

> You pass it as a string and split it inside the Stored Proc.
> Check this out...
> http://www.sommarskog.se/arrays-in-sql.html
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||something like this?
if exists(
Select 1 from TKCalls.dbo.tblCalls
where DATEDIFF(mi, StartedTime, GETDATE()) <=30
AND left(cast(Icent_Num as varchar(20)),6) = ('962472')
)
SET @.COUNT_CALLS_REC_1 = 1
else
SET @.COUNT_CALLS_REC_1 = 0
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||This answer tells us
1) You have no real industrial programming experience
2) You do not understand how SQL is being used out in the field
3) You are ignorant, condescending and arrogant
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1151689463.194144.106580@.d56g2000cwd.googlegroups.com...
> This is a common newbie error. Which you did not bother to Google
> before posting, did you? It immediately tells us that:
> 1) You do not know what a compiled program is, but expect SQL to be an
> intrpreter.
> 2) You do not know what a parameter or understand the concept of a
> scalar value.
> 3) You do not know that a table is the only data structure in SQL --
> there are no lists!
> The kludge you get in a newsgroup is dynamic SQL, with all the problems
> that come with it. Hey, but a kludge is so much easier and faster than
> an education, or even a Google search beforee posting!
> We can also give you some pure SQL that split up a string. But that is
> not the point; this is a bad design and poor programming. If you
> really give a damn about doing it right, Please post DDL, so that
> people do not have to guess what the keys, constraints, Declarative
> Referential Integrity, data types, etc. in your schema are. Sample data
> is also a good idea, along with clear specifications.
>|||I have tried to ignore your reply, but it's still bothering me. I have gone
through a few of your replies in this newsgroup and they are not helpful.You
are not being forced to help us here. So please if you are willing to help,
do so without all these stupid comments. Also if you are a programmer, it
does not mean that everyone is a programmer in this newsgroup. Cheers.
"--CELKO--" wrote:

> This is a common newbie error. Which you did not bother to Google
> before posting, did you? It immediately tells us that:
> 1) You do not know what a compiled program is, but expect SQL to be an
> intrpreter.
> 2) You do not know what a parameter or understand the concept of a
> scalar value.
> 3) You do not know that a table is the only data structure in SQL --
> there are no lists!
> The kludge you get in a newsgroup is dynamic SQL, with all the problems
> that come with it. Hey, but a kludge is so much easier and faster than
> an education, or even a Google search beforee posting!
> We can also give you some pure SQL that split up a string. But that is
> not the point; this is a bad design and poor programming. If you
> really give a damn about doing it right, Please post DDL, so that
> people do not have to guess what the keys, constraints, Declarative
> Referential Integrity, data types, etc. in your schema are. Sample data
> is also a good idea, along with clear specifications.
>

pass Date varible to XPath query

Hi,All
I want to pass Date varible value to XPATH query (see code below), it is not
work, can anyone help this out? Thanks. Martin
Dim currentDate As Date = CDate("5/29/2006")
adoCmd.CommandText = "<Root xmlns:sql=""urn:schemas-microsoft-com:xml-sql""
>" & _
"<sql:xpath-query mapping-schema = schema1.xsd/Node1[ Date ='" &
currentDate & "' ] </sql:xpath-query>" & _
" </Root>"
i figure out myself. Thanks
"martin1" wrote:

> Hi,All
>
> I want to pass Date varible value to XPATH query (see code below), it is not
> work, can anyone help this out? Thanks. Martin
> Dim currentDate As Date = CDate("5/29/2006")
> adoCmd.CommandText = "<Root xmlns:sql=""urn:schemas-microsoft-com:xml-sql""
> "<sql:xpath-query mapping-schema = schema1.xsd/Node1[ Date ='" &
> currentDate & "' ] </sql:xpath-query>" & _
> " </Root>"
|||What was it? Did you need to add a time zone? Convert it to a string?
"martin1" <martin1@.discussions.microsoft.com> wrote in message
news:8C91EB5E-F34D-45CE-A0CC-0957D2B321CC@.microsoft.com...
> Hi,All
>
> I want to pass Date varible value to XPATH query (see code below), it is
> not
> work, can anyone help this out? Thanks. Martin
> Dim currentDate As Date = CDate("5/29/2006")
> adoCmd.CommandText = "<Root
> xmlns:sql=""urn:schemas-microsoft-com:xml-sql""
> "<sql:xpath-query mapping-schema = schema1.xsd/Node1[ Date ='" &
> currentDate & "' ] </sql:xpath-query>" & _
> " </Root>"
|||you are right , convert date to string, then pass string to the query
"Mike C#" wrote:

> What was it? Did you need to add a time zone? Convert it to a string?
> "martin1" <martin1@.discussions.microsoft.com> wrote in message
> news:8C91EB5E-F34D-45CE-A0CC-0957D2B321CC@.microsoft.com...
>
>

pass Date varible to XPath query

Hi,All
I want to pass Date varible value to XPATH query (see code below), it is not
work, can anyone help this out? Thanks. Martin
Dim currentDate As Date = CDate("5/29/2006")
adoCmd.CommandText = "<Root xmlns:sql=""urn:schemas-microsoft-com:xml-sql""
>" & _
"<sql:xpath-query mapping-schema = schema1.xsd/Node1[ Date ='" &
currentDate & "' ] </sql:xpath-query>" & _
" </Root>"i figure out myself. Thanks
"martin1" wrote:

> Hi,All
>
> I want to pass Date varible value to XPATH query (see code below), it is n
ot
> work, can anyone help this out? Thanks. Martin
> Dim currentDate As Date = CDate("5/29/2006")
> adoCmd.CommandText = "<Root xmlns:sql=""urn:schemas-microsoft-com:xml-sql"
"
> "<sql:xpath-query mapping-schema = schema1.xsd/Node1[ Date ='"
&
> currentDate & "' ] </sql:xpath-query>" & _
> " </Root>"|||What was it? Did you need to add a time zone? Convert it to a string?
"martin1" <martin1@.discussions.microsoft.com> wrote in message
news:8C91EB5E-F34D-45CE-A0CC-0957D2B321CC@.microsoft.com...
> Hi,All
>
> I want to pass Date varible value to XPATH query (see code below), it is
> not
> work, can anyone help this out? Thanks. Martin
> Dim currentDate As Date = CDate("5/29/2006")
> adoCmd.CommandText = "<Root
> xmlns:sql=""urn:schemas-microsoft-com:xml-sql""
> "<sql:xpath-query mapping-schema = schema1.xsd/Node1[ Date ='" &
> currentDate & "' ] </sql:xpath-query>" & _
> " </Root>"|||you are right , convert date to string, then pass string to the query
"Mike C#" wrote:

> What was it? Did you need to add a time zone? Convert it to a string?
> "martin1" <martin1@.discussions.microsoft.com> wrote in message
> news:8C91EB5E-F34D-45CE-A0CC-0957D2B321CC@.microsoft.com...
>
>

pass a parameter to a SqlDataSource

I'm trying to pass my SqlDataSource a parameter that is defined in the code-behind file for the same page. I've tried the method below but it does not work. Is there a better way?

SubmitForm.ascx page:

<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ connection string..... %>"
SelectCommand="sp_CourseMaterialShipment_GetCourses" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:Parameter DefaultValue="<% ProgramID %>" Name="programID" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>

SubmitForm.ascx.vb page:

Private ProgramID as string = "25"

Public ReadOnly Property ProgramID() As String
Get
Return _ProgramID
End Get
End Property

Thanks

Jason

Hello my friend,

If this is what you want to do, take the DefaultValue attribute out of the aspx page and use the following in your code behind: -

sqlDataSource2.SelectParameters["programID"].DefaultValue = ProgramID;

Kind regards

Scotty

|||

worked. Thank you.

Jason