I have a stored procedure that runs a query and the result of the query is a
varchar. I would like to use an output parameter to get the value and then
pass that value into a variable to use elsewhere. Is this possible?
ThanksSure.
Example:
use northwind
go
create procedure usp_get_companyname
@.customerid nchar(5),
@.companyname nvarchar(40) output
as
set nocount on
set @.companyname = (select companyname from customers where customerid =
@.customerid)
return @.@.error
go
declare @.cn nvarchar(40)
execute usp_get_companyname @.customerid = 'alfki', @.companyname = @.cn output
print @.cn
go
drop procedure usp_get_companyname
go
AMB
"Andy" wrote:
> I have a stored procedure that runs a query and the result of the query is
a
> varchar. I would like to use an output parameter to get the value and the
n
> pass that value into a variable to use elsewhere. Is this possible?
> Thanks|||Something like this?
use pubs
go
create proc first
@.au_lname varchar(50),
@.au_id varchar(11) OUTPUT
as
SELECT @.au_id = au_id from authors where au_lname = @.au_lname
RETURN (0)
GO
create proc second
@.au_id varchar(11)
as
select * from titleauthor where au_id = @.au_id
RETURN (0)
GO
declare @.lname varchar(50), @.id varchar(11)
set @.lname = 'white'
exec first @.lname, @.id output
select @.id
exec second @.id
go
declare @.lname varchar(50), @.id varchar(11)
set @.lname = 'green'
exec first @.lname, @.id output
select @.id
exec second @.id
go
drop proc first
drop proc second
Keith
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:0289E9D3-8AB3-481D-8869-92E6CFD12FF2@.microsoft.com...
> I have a stored procedure that runs a query and the result of the query is
a
> varchar. I would like to use an output parameter to get the value and
then
> pass that value into a variable to use elsewhere. Is this possible?
> Thanks
Showing posts with label variable. Show all posts
Showing posts with label variable. Show all posts
Friday, March 30, 2012
Passing Object Variable as input parameter to an Execute SQL Task Query
I've encountered a new problem with an SSIS Pkg where I have a seq. of Execute SQL tasks. My question are:
1) In the First Execute SQL Task, I want to store a single row result of @.@.identity type into a User Variable User::LoadID of What type. ( I tried using DBNull Type or Object type which works, not with any other type, it but I can't proceed to step 2 )
2) Now I want to use this User::LoadID as input parameter of What type for the next task (I tried using Numeric, Long, DB_Numeric, Decimal, Double none of there work).
Please give me solutions for the above two..
@.@.IDENTITY returns an integer, so your variable type should be Int32. This may help - http://www.sqlis.com/58.aspx|||Darren,
Thanks for your solution,
Still Execute SQL query task fails if I use Int32 type for the @.@.identity single row result Variable.
When I set it to dbnull it works however it's unable to use this Value as an input paraemter for the next Execute SQL task.
Moreover I want the solution for the step 2 as well|||
DarrenSQLIS wrote:
@.@.IDENTITY returns an integer, so your variable type should be Int32. This may help - http://www.sqlis.com/58.aspx
@.@.IDENTITY returns a numeric, not an integer. (http://msdn2.microsoft.com/en-us/library/aa933167(sql.80).aspx)
Just cast it to an integer and all will be well.
select cast(@.@.IDENTITY as int) as 'Ident'|||
Thanks Phil,
It seems that both of your solution will work here. Returns numeric so we can use int32 variable for assigning the result. Came to know that when we use int32 variable as an input parameter to another Execute SQL task, we declare it Long type.
There is one more issue i couldn't resolve. There is a type mismatch when I use to map String Variable as input Parameter to a Varchar type in Execute SQL Task. As such Execute SQL task Fails.
Under Parameter Mapping
User:: StrVar Varchar 0
However When I directly assign the String value in place of the parameter it works.
Select SrcRowCount AS CntLoadID From LoadDetails Where Source = ? ( Replacing this value '32, 2323, 2343,23434' works)
Is there any work around for this?
|||
Under Parameter Mapping
User:: StrVar Varchar 0
However When I directly assign the String value in place of the parameter it works.
Select SrcRowCount AS CntLoadID From LoadDetails Where Source = ? ( Replacing this value '32, 2323, 2343,23434' works)
Is there any work around for this?
That works for me, using OLE DB connection though. What type of connection are you using?
|||Hi Rafael,
I am using OLEDB connection.
When I create an expression: Left(sqlstatement)+@.[user::CntLoadID] + Right(sqlstatement), it works indeed. But it seems an alternate way.
Passing NULL-value into package variables
We have a package with a package variable.
This variable is of data-type 'DateTime'.
However, when i try to pass the value 'NULL' the package fails... i use the following statement with 'dtexec.exe'
/SET \Package.Variables[MyDate].Value;"NULL"
What's the correct syntax for passing null-values? But maybe (because i cannot find anything on this) i should ask if this is even possible...
I don't think you can pass nulls through the commandline dtexec or dtexecui simply because a DBNull is an object. One option you can pursue is to pass it using a console app written in VB or C#.This link shows one of the ways of doing that.|||
Dennis_v_E wrote:
We have a package with a package variable.
This variable is of data-type 'DateTime'.
However, when i try to pass the value 'NULL' the package fails... i use the following statement with 'dtexec.exe'/SET \Package.Variables[MyDate].Value;"NULL"
What's the correct syntax for passing null-values? But maybe (because i cannot find anything on this) i should ask if this is even possible...
Its not possible.
One way around it may be to have a boolean variable called IsDatetimeNull which you set to TRUE or FALSE from the command-line.
Then, you put an expression on your datetime variable which sets it to NULL(DT_DBTIMESTAMP) if IsDatetimeNull==TRUE.
Something like that anyway. You get the idea.
-Jamie
|||
Thanx,
To bad it cannot be done simple. But i get the idea.
Maybe i put a feature request in Connect.
Dennis
sqlWednesday, March 28, 2012
Passing multiple values in 1 varchar variable in a stored proc IN
I'm trying to pass a parameter to a stored procedure with the following quer
y:
sp_Monkey '24601', " 'ABC', 'DEF', 'GHI' "
The stored procedure is as follows:
CREATE PROCEDURE sp_Monkey
@.Field1 varchar(10)
@.Field2 varchar(50)
AS
SET NOCOUNT ON
UPDATE monkey
SET coupon = P.coupon
FROM monkey M, promotions P
WHERE M.recordID = P.recordID
AND P.coupon IN (@.Field2)
Even though @.Field2 should be passed in as multiple string values seperated
by a comma (thus creating a valid "IN" statement) it isn't functioning that
way. If I set the value of @.Field2 = 'ABC' (a single value) the statement
works as desired.ckeaton@.inergex.com (ckeaton@.inergex.com@.discussions.microsoft.com) writes:
> I'm trying to pass a parameter to a stored procedure with the following
> query:
> sp_Monkey '24601', " 'ABC', 'DEF', 'GHI' "
Before we go the actual question, permit me to point out two things. Or
three.
1) Don't name your stored procedures sp_something, the sp_ prefix is
reserved for system stored procedures, and SQL Server first looks
in master for procedures with names like this.
2) In SQL Server you use ' to quote strings. If the setting
QUOTED_IDENTIFER is OFF, you can use " as string delimiter as well.
This is very handy with nested strings, but alas, there is
functionality in SQL Server only works if QUOTED_IDENTIFIER is ON,
so best practice is to use ' only. Note that this setting is ON
by default in many contexts.)
3) And since you appear to get away with this, I suspect that you use
Enterprise Manager to edit your stored procedures. (EM has this
setting off by default for some inexplicable reason.) EM is a very
poor tool to edit stored procedures. You are better off using
Query Analyzer.
> UPDATE monkey
> SET coupon = P.coupon
> FROM monkey M, promotions P
> WHERE M.recordID = P.recordID
> AND P.coupon IN (@.Field2)
> Even though @.Field2 should be passed in as multiple string values
> seperated by a comma (thus creating a valid "IN" statement) it isn't
> functioning that way. If I set the value of @.Field2 = 'ABC' (a single
> value) the statement works as desired.
I am afraid that the answers from Mark Williams led you astray. And
the posting from Celko equally less helpful, as he uses syntax that
does not work on SQL Server. (But he claims it to be portable!)
Anyway, the way to do this, is to use a function that unpacks the
list into a table. I have a loooong article on this on my web site,
but this link brings you directly to a solution:
http://www.sommarskog.se/arrays-in-...ist-of-strings.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
y:
sp_Monkey '24601', " 'ABC', 'DEF', 'GHI' "
The stored procedure is as follows:
CREATE PROCEDURE sp_Monkey
@.Field1 varchar(10)
@.Field2 varchar(50)
AS
SET NOCOUNT ON
UPDATE monkey
SET coupon = P.coupon
FROM monkey M, promotions P
WHERE M.recordID = P.recordID
AND P.coupon IN (@.Field2)
Even though @.Field2 should be passed in as multiple string values seperated
by a comma (thus creating a valid "IN" statement) it isn't functioning that
way. If I set the value of @.Field2 = 'ABC' (a single value) the statement
works as desired.ckeaton@.inergex.com (ckeaton@.inergex.com@.discussions.microsoft.com) writes:
> I'm trying to pass a parameter to a stored procedure with the following
> query:
> sp_Monkey '24601', " 'ABC', 'DEF', 'GHI' "
Before we go the actual question, permit me to point out two things. Or
three.
1) Don't name your stored procedures sp_something, the sp_ prefix is
reserved for system stored procedures, and SQL Server first looks
in master for procedures with names like this.
2) In SQL Server you use ' to quote strings. If the setting
QUOTED_IDENTIFER is OFF, you can use " as string delimiter as well.
This is very handy with nested strings, but alas, there is
functionality in SQL Server only works if QUOTED_IDENTIFIER is ON,
so best practice is to use ' only. Note that this setting is ON
by default in many contexts.)
3) And since you appear to get away with this, I suspect that you use
Enterprise Manager to edit your stored procedures. (EM has this
setting off by default for some inexplicable reason.) EM is a very
poor tool to edit stored procedures. You are better off using
Query Analyzer.
> UPDATE monkey
> SET coupon = P.coupon
> FROM monkey M, promotions P
> WHERE M.recordID = P.recordID
> AND P.coupon IN (@.Field2)
> Even though @.Field2 should be passed in as multiple string values
> seperated by a comma (thus creating a valid "IN" statement) it isn't
> functioning that way. If I set the value of @.Field2 = 'ABC' (a single
> value) the statement works as desired.
I am afraid that the answers from Mark Williams led you astray. And
the posting from Celko equally less helpful, as he uses syntax that
does not work on SQL Server. (But he claims it to be portable!)
Anyway, the way to do this, is to use a function that unpacks the
list into a table. I have a loooong article on this on my web site,
but this link brings you directly to a solution:
http://www.sommarskog.se/arrays-in-...ist-of-strings.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Passing multiple values in 1 varchar variable in a stored proc IN
This is a common Newbie question. This shows that you don't know SQL
uses only scalar parameters and has only one data structure, the table.
This is a fundamental programming concept that you should learn in the
first w
of any SQL language class. SQL is not your original
procedural programming language.
1) The dangerous, slow kludge is to use dynamic SQL and admit that any
random furure user is a better programmer than you are. It is used by
Newbies who do not understand SQL or even what a compiled language is.
A string is a string; it is a scalar value like any other parameter; it
is not code. Again, this is not just an SQL problem; this is a basic
misunderstanding of programming principles.
2) Passing a list of parmeters to a stored procedure can be done by
putting them into a string with a separator. I like to use the
traditional comma. Let's assume that you have a whole table full of
such parameter lists:
CREATE TABLE InputStrings
(keycol CHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL);
INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
etc.
This will be the table that gets the outputs, in the form of the
original key column and one parameter per row.
CREATE TABLE Parmlist
(keycol CHAR(10) NOT NULL,
parm INTEGER NOT NULL);
It makes life easier if the lists in the input strings start and end
with a comma. You will need a table of sequential numbers -- a
standard SQL programming trick, Now, the query, in SQL-92 syntax
(translate into your local dialect):
INSERT INTO ParmList (keycol, parm)
SELECT keycol,
CAST (SUBSTRING (I1.input_string
FROM S1.seq
FOR MIN(S2.seq) - S1.seq -1)
AS INTEGER)
FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
WHERE SUBSTRING (',' || I1.input_string || ',' FROM S1.seq FOR 1) =
','
AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
','
AND S1.seq < S2.seq
GROUP BY I1.keycol, I1.input_string, S1.seq;
The S1 and S2 copies of Sequence are used to locate bracketing pairs of
commas, and the entire set of substrings located between them is
extracted and cast as integers in one non-procedural step. The trick
is to be sure that the right hand comma of the bracketing pair is the
closest one to the first comma. You can add a computation for the
relative postion of each element in the list (left as a exercise for
the student)
You can then write:a query like this:
SELECT *
FROM Foobar
WHERE x IN (SELECT parm FROM Parmlist WHERE parm IS NOT NULL);
Hey, I can write kludges with the best of them, but I don't. You need
to at the very least write a routine to clean out blanks and
non-numerics in the strings, take care of floating point and decimal
notation, etc. Basically, you must write part of a compiler in SQL.
Yeeeech! Or decide that you do not want to have data integrity, which
is what most Newbies do in practice altho they do not know it.
3) The right way is to use tables with the IN () predicate, You set up
the procedure declaration with a "fake array" made from a repeated
gorup, like this in SQL/PSM (translate into your local dialect):
CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
BEGIN
SELECT foo, bar, blah, yadda, ...
FROM Floob
WHERE my_col
IN (SELECT DISTINCT parm -- kill redundant dups
FROM (VALUES (p1), (p2), .., (pN)) AS ParmList(parm)
WHERE parm IS NOT NULL -- ignore empty aparameters
AND <other conditions> )
AND <more predicates>;
<more code>;
END;
The idea is that creating a derived table will perform better .You can
also add functions to the parameters like UPPER(pi), apply CASE
expressions like in T-SQL
(CASE WHEN @.p1 = 'usa' THEN @.p2 ELSE 2.2 * @.p2 END)
or use scalar subqueries like this on subsets of the parameters:
(SELECT L.address_code
FROM Locations AS L
WHERE @.p1 = L.longitude
AND @.p2 = L.latitude
AND @.p3 = 'Paris');
SQL Server can have up to 1,024 parameters in a stored procedure and
that is usually good enough. If not, make two calls to the procedure
...> This is a common Newbie question. This shows that you don't know SQL
> uses only scalar parameters and has only one data structure, the table.
> This is a fundamental programming concept that you should learn in the
> first w of any SQL language class. SQL is not your original
> procedural programming language.
This shows your lack of industrial programming experience and exposure, this
is a common requirement from application screeens that allow multiple
values, for instance a multi-value select list.
> 1) The dangerous, slow kludge is to use dynamic SQL and admit that any
> random furure user is a better programmer than you are. It is used by
> Newbies who do not understand SQL or even what a compiled language is.
> A string is a string; it is a scalar value like any other parameter; it
> is not code. Again, this is not just an SQL problem; this is a basic
> misunderstanding of programming principles.
Why is it dangerous?
Why is it slow?
Why is it a kludge?
Dynamic SQL is not used by newbies, its used by people who understand how to
get an effiecent well maintained and scalable solution.
You repeated 'can' this answer and people repeatedly ask you (including
myself) to post your statistics backing up your claims - you never do which
we can only conclude that you are talking rubbish.
Your proposed solution is slow and will not scale and is certainly
significantly slower and more combersome than do it the correct way - using
dynamic SQL or XML instead.
You need to stop and do a fundemental programming course, go get some real
industrial experience instead of gaining experience from books and playing
with the product.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1135719723.547696.113660@.g14g2000cwa.googlegroups.com...
> This is a common Newbie question. This shows that you don't know SQL
> uses only scalar parameters and has only one data structure, the table.
> This is a fundamental programming concept that you should learn in the
> first w of any SQL language class. SQL is not your original
> procedural programming language.
> 1) The dangerous, slow kludge is to use dynamic SQL and admit that any
> random furure user is a better programmer than you are. It is used by
> Newbies who do not understand SQL or even what a compiled language is.
> A string is a string; it is a scalar value like any other parameter; it
> is not code. Again, this is not just an SQL problem; this is a basic
> misunderstanding of programming principles.
> 2) Passing a list of parmeters to a stored procedure can be done by
> putting them into a string with a separator. I like to use the
> traditional comma. Let's assume that you have a whole table full of
> such parameter lists:
> CREATE TABLE InputStrings
> (keycol CHAR(10) NOT NULL PRIMARY KEY,
> input_string VARCHAR(255) NOT NULL);
> INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
> INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
> etc.
> This will be the table that gets the outputs, in the form of the
> original key column and one parameter per row.
> CREATE TABLE Parmlist
> (keycol CHAR(10) NOT NULL,
> parm INTEGER NOT NULL);
> It makes life easier if the lists in the input strings start and end
> with a comma. You will need a table of sequential numbers -- a
> standard SQL programming trick, Now, the query, in SQL-92 syntax
> (translate into your local dialect):
> INSERT INTO ParmList (keycol, parm)
> SELECT keycol,
> CAST (SUBSTRING (I1.input_string
> FROM S1.seq
> FOR MIN(S2.seq) - S1.seq -1)
> AS INTEGER)
> FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
> WHERE SUBSTRING (',' || I1.input_string || ',' FROM S1.seq FOR 1) =
> ','
> AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
> ','
> AND S1.seq < S2.seq
> GROUP BY I1.keycol, I1.input_string, S1.seq;
> The S1 and S2 copies of Sequence are used to locate bracketing pairs of
> commas, and the entire set of substrings located between them is
> extracted and cast as integers in one non-procedural step. The trick
> is to be sure that the right hand comma of the bracketing pair is the
> closest one to the first comma. You can add a computation for the
> relative postion of each element in the list (left as a exercise for
> the student)
> You can then write:a query like this:
> SELECT *
> FROM Foobar
> WHERE x IN (SELECT parm FROM Parmlist WHERE parm IS NOT NULL);
> Hey, I can write kludges with the best of them, but I don't. You need
> to at the very least write a routine to clean out blanks and
> non-numerics in the strings, take care of floating point and decimal
> notation, etc. Basically, you must write part of a compiler in SQL.
> Yeeeech! Or decide that you do not want to have data integrity, which
> is what most Newbies do in practice altho they do not know it.
> 3) The right way is to use tables with the IN () predicate, You set up
> the procedure declaration with a "fake array" made from a repeated
> gorup, like this in SQL/PSM (translate into your local dialect):
> CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
> INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
> BEGIN
> SELECT foo, bar, blah, yadda, ...
> FROM Floob
> WHERE my_col
> IN (SELECT DISTINCT parm -- kill redundant dups
> FROM (VALUES (p1), (p2), .., (pN)) AS ParmList(parm)
> WHERE parm IS NOT NULL -- ignore empty aparameters
> AND <other conditions> )
> AND <more predicates>;
> <more code>;
> END;
> The idea is that creating a derived table will perform better .You can
> also add functions to the parameters like UPPER(pi), apply CASE
> expressions like in T-SQL
> (CASE WHEN @.p1 = 'usa' THEN @.p2 ELSE 2.2 * @.p2 END)
> or use scalar subqueries like this on subsets of the parameters:
> (SELECT L.address_code
> FROM Locations AS L
> WHERE @.p1 = L.longitude
> AND @.p2 = L.latitude
> AND @.p3 = 'Paris');
> SQL Server can have up to 1,024 parameters in a stored procedure and
> that is usually good enough. If not, make two calls to the procedure
> ...
>|||Tony Rogerson (tonyrogerson@.sqlserverfaq.com) writes:
> Dynamic SQL is not used by newbies, its used by people who understand
> how to get an effiecent well maintained and scalable solution.
But for this particular problem, dynamic SQL is definitely not very
scalable. When the list grows in size, the performance for IN() gets
horrendeous, at least in SQL 2000. (I have not checked whether SQL 2005
has any improvements.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||That depends on the number of elements for the IN clause, there are only 3
on the OP's post so I don't see a problem, and the problem isn't anything to
do with dynamic SQL, rather, the way the IN clause works.
For a larger IN list, say hundreds rather than < dozen then i would then I'd
probably chop the list up into a set and do an IN or EXISTS.
But to repeat for the benefit of celko, this is not a dynamic sql
performance problem but rather the number of elements on the IN clause.
Tony.
Tony Rogersonen
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns973BE48CB0Yazorman@.127.0.0.1...
> Tony Rogerson (tonyrogerson@.sqlserverfaq.com) writes:
> But for this particular problem, dynamic SQL is definitely not very
> scalable. When the list grows in size, the performance for IN() gets
> horrendeous, at least in SQL 2000. (I have not checked whether SQL 2005
> has any improvements.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
uses only scalar parameters and has only one data structure, the table.
This is a fundamental programming concept that you should learn in the
first w
of any SQL language class. SQL is not your originalprocedural programming language.
1) The dangerous, slow kludge is to use dynamic SQL and admit that any
random furure user is a better programmer than you are. It is used by
Newbies who do not understand SQL or even what a compiled language is.
A string is a string; it is a scalar value like any other parameter; it
is not code. Again, this is not just an SQL problem; this is a basic
misunderstanding of programming principles.
2) Passing a list of parmeters to a stored procedure can be done by
putting them into a string with a separator. I like to use the
traditional comma. Let's assume that you have a whole table full of
such parameter lists:
CREATE TABLE InputStrings
(keycol CHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL);
INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
etc.
This will be the table that gets the outputs, in the form of the
original key column and one parameter per row.
CREATE TABLE Parmlist
(keycol CHAR(10) NOT NULL,
parm INTEGER NOT NULL);
It makes life easier if the lists in the input strings start and end
with a comma. You will need a table of sequential numbers -- a
standard SQL programming trick, Now, the query, in SQL-92 syntax
(translate into your local dialect):
INSERT INTO ParmList (keycol, parm)
SELECT keycol,
CAST (SUBSTRING (I1.input_string
FROM S1.seq
FOR MIN(S2.seq) - S1.seq -1)
AS INTEGER)
FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
WHERE SUBSTRING (',' || I1.input_string || ',' FROM S1.seq FOR 1) =
','
AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
','
AND S1.seq < S2.seq
GROUP BY I1.keycol, I1.input_string, S1.seq;
The S1 and S2 copies of Sequence are used to locate bracketing pairs of
commas, and the entire set of substrings located between them is
extracted and cast as integers in one non-procedural step. The trick
is to be sure that the right hand comma of the bracketing pair is the
closest one to the first comma. You can add a computation for the
relative postion of each element in the list (left as a exercise for
the student)
You can then write:a query like this:
SELECT *
FROM Foobar
WHERE x IN (SELECT parm FROM Parmlist WHERE parm IS NOT NULL);
Hey, I can write kludges with the best of them, but I don't. You need
to at the very least write a routine to clean out blanks and
non-numerics in the strings, take care of floating point and decimal
notation, etc. Basically, you must write part of a compiler in SQL.
Yeeeech! Or decide that you do not want to have data integrity, which
is what most Newbies do in practice altho they do not know it.
3) The right way is to use tables with the IN () predicate, You set up
the procedure declaration with a "fake array" made from a repeated
gorup, like this in SQL/PSM (translate into your local dialect):
CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
BEGIN
SELECT foo, bar, blah, yadda, ...
FROM Floob
WHERE my_col
IN (SELECT DISTINCT parm -- kill redundant dups
FROM (VALUES (p1), (p2), .., (pN)) AS ParmList(parm)
WHERE parm IS NOT NULL -- ignore empty aparameters
AND <other conditions> )
AND <more predicates>;
<more code>;
END;
The idea is that creating a derived table will perform better .You can
also add functions to the parameters like UPPER(pi), apply CASE
expressions like in T-SQL
(CASE WHEN @.p1 = 'usa' THEN @.p2 ELSE 2.2 * @.p2 END)
or use scalar subqueries like this on subsets of the parameters:
(SELECT L.address_code
FROM Locations AS L
WHERE @.p1 = L.longitude
AND @.p2 = L.latitude
AND @.p3 = 'Paris');
SQL Server can have up to 1,024 parameters in a stored procedure and
that is usually good enough. If not, make two calls to the procedure
...> This is a common Newbie question. This shows that you don't know SQL
> uses only scalar parameters and has only one data structure, the table.
> This is a fundamental programming concept that you should learn in the
> first w
of any SQL language class. SQL is not your original> procedural programming language.
This shows your lack of industrial programming experience and exposure, this
is a common requirement from application screeens that allow multiple
values, for instance a multi-value select list.
> 1) The dangerous, slow kludge is to use dynamic SQL and admit that any
> random furure user is a better programmer than you are. It is used by
> Newbies who do not understand SQL or even what a compiled language is.
> A string is a string; it is a scalar value like any other parameter; it
> is not code. Again, this is not just an SQL problem; this is a basic
> misunderstanding of programming principles.
Why is it dangerous?
Why is it slow?
Why is it a kludge?
Dynamic SQL is not used by newbies, its used by people who understand how to
get an effiecent well maintained and scalable solution.
You repeated 'can' this answer and people repeatedly ask you (including
myself) to post your statistics backing up your claims - you never do which
we can only conclude that you are talking rubbish.
Your proposed solution is slow and will not scale and is certainly
significantly slower and more combersome than do it the correct way - using
dynamic SQL or XML instead.
You need to stop and do a fundemental programming course, go get some real
industrial experience instead of gaining experience from books and playing
with the product.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1135719723.547696.113660@.g14g2000cwa.googlegroups.com...
> This is a common Newbie question. This shows that you don't know SQL
> uses only scalar parameters and has only one data structure, the table.
> This is a fundamental programming concept that you should learn in the
> first w
of any SQL language class. SQL is not your original> procedural programming language.
> 1) The dangerous, slow kludge is to use dynamic SQL and admit that any
> random furure user is a better programmer than you are. It is used by
> Newbies who do not understand SQL or even what a compiled language is.
> A string is a string; it is a scalar value like any other parameter; it
> is not code. Again, this is not just an SQL problem; this is a basic
> misunderstanding of programming principles.
> 2) Passing a list of parmeters to a stored procedure can be done by
> putting them into a string with a separator. I like to use the
> traditional comma. Let's assume that you have a whole table full of
> such parameter lists:
> CREATE TABLE InputStrings
> (keycol CHAR(10) NOT NULL PRIMARY KEY,
> input_string VARCHAR(255) NOT NULL);
> INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
> INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
> etc.
> This will be the table that gets the outputs, in the form of the
> original key column and one parameter per row.
> CREATE TABLE Parmlist
> (keycol CHAR(10) NOT NULL,
> parm INTEGER NOT NULL);
> It makes life easier if the lists in the input strings start and end
> with a comma. You will need a table of sequential numbers -- a
> standard SQL programming trick, Now, the query, in SQL-92 syntax
> (translate into your local dialect):
> INSERT INTO ParmList (keycol, parm)
> SELECT keycol,
> CAST (SUBSTRING (I1.input_string
> FROM S1.seq
> FOR MIN(S2.seq) - S1.seq -1)
> AS INTEGER)
> FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
> WHERE SUBSTRING (',' || I1.input_string || ',' FROM S1.seq FOR 1) =
> ','
> AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
> ','
> AND S1.seq < S2.seq
> GROUP BY I1.keycol, I1.input_string, S1.seq;
> The S1 and S2 copies of Sequence are used to locate bracketing pairs of
> commas, and the entire set of substrings located between them is
> extracted and cast as integers in one non-procedural step. The trick
> is to be sure that the right hand comma of the bracketing pair is the
> closest one to the first comma. You can add a computation for the
> relative postion of each element in the list (left as a exercise for
> the student)
> You can then write:a query like this:
> SELECT *
> FROM Foobar
> WHERE x IN (SELECT parm FROM Parmlist WHERE parm IS NOT NULL);
> Hey, I can write kludges with the best of them, but I don't. You need
> to at the very least write a routine to clean out blanks and
> non-numerics in the strings, take care of floating point and decimal
> notation, etc. Basically, you must write part of a compiler in SQL.
> Yeeeech! Or decide that you do not want to have data integrity, which
> is what most Newbies do in practice altho they do not know it.
> 3) The right way is to use tables with the IN () predicate, You set up
> the procedure declaration with a "fake array" made from a repeated
> gorup, like this in SQL/PSM (translate into your local dialect):
> CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
> INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
> BEGIN
> SELECT foo, bar, blah, yadda, ...
> FROM Floob
> WHERE my_col
> IN (SELECT DISTINCT parm -- kill redundant dups
> FROM (VALUES (p1), (p2), .., (pN)) AS ParmList(parm)
> WHERE parm IS NOT NULL -- ignore empty aparameters
> AND <other conditions> )
> AND <more predicates>;
> <more code>;
> END;
> The idea is that creating a derived table will perform better .You can
> also add functions to the parameters like UPPER(pi), apply CASE
> expressions like in T-SQL
> (CASE WHEN @.p1 = 'usa' THEN @.p2 ELSE 2.2 * @.p2 END)
> or use scalar subqueries like this on subsets of the parameters:
> (SELECT L.address_code
> FROM Locations AS L
> WHERE @.p1 = L.longitude
> AND @.p2 = L.latitude
> AND @.p3 = 'Paris');
> SQL Server can have up to 1,024 parameters in a stored procedure and
> that is usually good enough. If not, make two calls to the procedure
> ...
>|||Tony Rogerson (tonyrogerson@.sqlserverfaq.com) writes:
> Dynamic SQL is not used by newbies, its used by people who understand
> how to get an effiecent well maintained and scalable solution.
But for this particular problem, dynamic SQL is definitely not very
scalable. When the list grows in size, the performance for IN() gets
horrendeous, at least in SQL 2000. (I have not checked whether SQL 2005
has any improvements.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||That depends on the number of elements for the IN clause, there are only 3
on the OP's post so I don't see a problem, and the problem isn't anything to
do with dynamic SQL, rather, the way the IN clause works.
For a larger IN list, say hundreds rather than < dozen then i would then I'd
probably chop the list up into a set and do an IN or EXISTS.
But to repeat for the benefit of celko, this is not a dynamic sql
performance problem but rather the number of elements on the IN clause.
Tony.
Tony Rogersonen
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns973BE48CB0Yazorman@.127.0.0.1...
> Tony Rogerson (tonyrogerson@.sqlserverfaq.com) writes:
> But for this particular problem, dynamic SQL is definitely not very
> scalable. When the list grows in size, the performance for IN() gets
> horrendeous, at least in SQL 2000. (I have not checked whether SQL 2005
> has any improvements.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
Passing multiple values in 1 varchar variable in a stored proc
Although my earlier posts weren't "perfect," I tested my later posting prett
y
thoroughly in a sample db, and it produces the desired results. In addition,
the code shown is almost exactly the same code as shown at
http://www.aspfaq.com/show.asp?id=2248
which was reference by Aaron Bertrand in a thread very similar to this one.
http://www.microsoft.com/technet/co...94-b4fd1783d45e
So please explain to me how I am leading him "astray."
"Erland Sommarskog" wrote:
> ckeaton@.inergex.com (ckeaton@.inergex.com@.discussions.microsoft.com) writes
:
> Before we go the actual question, permit me to point out two things. Or
> three.
> 1) Don't name your stored procedures sp_something, the sp_ prefix is
> reserved for system stored procedures, and SQL Server first looks
> in master for procedures with names like this.
> 2) In SQL Server you use ' to quote strings. If the setting
> QUOTED_IDENTIFER is OFF, you can use " as string delimiter as well.
> This is very handy with nested strings, but alas, there is
> functionality in SQL Server only works if QUOTED_IDENTIFIER is ON,
> so best practice is to use ' only. Note that this setting is ON
> by default in many contexts.)
> 3) And since you appear to get away with this, I suspect that you use
> Enterprise Manager to edit your stored procedures. (EM has this
> setting off by default for some inexplicable reason.) EM is a very
> poor tool to edit stored procedures. You are better off using
> Query Analyzer.
>
> I am afraid that the answers from Mark Williams led you astray. And
> the posting from Celko equally less helpful, as he uses syntax that
> does not work on SQL Server. (But he claims it to be portable!)
> Anyway, the way to do this, is to use a function that unpacks the
> list into a table. I have a loooong article on this on my web site,
> but this link brings you directly to a solution:
> http://www.sommarskog.se/arrays-in-...ist-of-strings.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>Thank you Mark Williams for you thorough answers. Your final solution worke
d
perfectly! If you're ever in Rochester, look me up. I owe you a drink.
For the rest of you who were helpful, thank you as well. I will take all of
your advice to heart. I am relatively new to stored procedures on MS SQL
Server, and thank you for your advice.
For our "newbie"-slinging friend, --CELKO--; I hope you can more helpful in
the future. You seem to know what you're talking about, but it gets lost in
the white noise of your apparent hostility.
To all, Happy New Year.
"Mark Williams" wrote:
> Although my earlier posts weren't "perfect," I tested my later posting pre
tty
> thoroughly in a sample db, and it produces the desired results. In additio
n,
> the code shown is almost exactly the same code as shown at
> http://www.aspfaq.com/show.asp?id=2248
> which was reference by Aaron Bertrand in a thread very similar to this one
.
> http://www.microsoft.com/technet/co...94-b4fd1783d45e
> So please explain to me how I am leading him "astray."
> "Erland Sommarskog" wrote:
>sql
y
thoroughly in a sample db, and it produces the desired results. In addition,
the code shown is almost exactly the same code as shown at
http://www.aspfaq.com/show.asp?id=2248
which was reference by Aaron Bertrand in a thread very similar to this one.
http://www.microsoft.com/technet/co...94-b4fd1783d45e
So please explain to me how I am leading him "astray."
"Erland Sommarskog" wrote:
> ckeaton@.inergex.com (ckeaton@.inergex.com@.discussions.microsoft.com) writes
:
> Before we go the actual question, permit me to point out two things. Or
> three.
> 1) Don't name your stored procedures sp_something, the sp_ prefix is
> reserved for system stored procedures, and SQL Server first looks
> in master for procedures with names like this.
> 2) In SQL Server you use ' to quote strings. If the setting
> QUOTED_IDENTIFER is OFF, you can use " as string delimiter as well.
> This is very handy with nested strings, but alas, there is
> functionality in SQL Server only works if QUOTED_IDENTIFIER is ON,
> so best practice is to use ' only. Note that this setting is ON
> by default in many contexts.)
> 3) And since you appear to get away with this, I suspect that you use
> Enterprise Manager to edit your stored procedures. (EM has this
> setting off by default for some inexplicable reason.) EM is a very
> poor tool to edit stored procedures. You are better off using
> Query Analyzer.
>
> I am afraid that the answers from Mark Williams led you astray. And
> the posting from Celko equally less helpful, as he uses syntax that
> does not work on SQL Server. (But he claims it to be portable!)
> Anyway, the way to do this, is to use a function that unpacks the
> list into a table. I have a loooong article on this on my web site,
> but this link brings you directly to a solution:
> http://www.sommarskog.se/arrays-in-...ist-of-strings.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>Thank you Mark Williams for you thorough answers. Your final solution worke
d
perfectly! If you're ever in Rochester, look me up. I owe you a drink.
For the rest of you who were helpful, thank you as well. I will take all of
your advice to heart. I am relatively new to stored procedures on MS SQL
Server, and thank you for your advice.
For our "newbie"-slinging friend, --CELKO--; I hope you can more helpful in
the future. You seem to know what you're talking about, but it gets lost in
the white noise of your apparent hostility.
To all, Happy New Year.
"Mark Williams" wrote:
> Although my earlier posts weren't "perfect," I tested my later posting pre
tty
> thoroughly in a sample db, and it produces the desired results. In additio
n,
> the code shown is almost exactly the same code as shown at
> http://www.aspfaq.com/show.asp?id=2248
> which was reference by Aaron Bertrand in a thread very similar to this one
.
> http://www.microsoft.com/technet/co...94-b4fd1783d45e
> So please explain to me how I am leading him "astray."
> "Erland Sommarskog" wrote:
>sql
Passing multiple values in 1 varchar variable in a stored proc
So in my case, how do I differentiate between the string intended for @.Field
1
from the ones intended for @.Field2?
sp_Monkey '24601', ''ABC'', ''DEF'', ''GHI'' -- all single quotes :)
or do I do something like this:
sp_Monkey '24601', " ''ABC'', ''DEF'', ''GHI'' " -- same as before, but
encased in a double qoute to identify it as one string to be passed over.
THANK YOU! :)
"Mark Williams" wrote:
> You'll need double single-quotes (that's really clear!) to get what you wa
nt
> For example
> DECLARE @.foo varchar(50)
> SET @.foo = '''ABC'', ''DEF'', ''GHI''' --those are all single quotes!
> SELECT @.foo
> returns
> 'ABC', 'DEF', 'GHI'
>
> "ckeaton@.inergex.com" wrote:
>I'm not sure why it's not working when I try to pass the value as it stands
in your most recent example.
It's not finding the individual values which leads me to believe that
something about how the construct is being presented to the database is wron
g.
It should end out looking like this...
UPDATE monkey
SET coupon = P.coupon
FROM monkey M, promotions P
WHERE M.recordID = P.recordID
AND P.coupon IN ('ABC', 'DEF', 'GHI');
But it appears not to be doing that.
Thank you for your suggestions.
Frustrating, because this seems like such a simple thing, but it's tripping
me up! :)
"Mark Williams" wrote:
> You would want something like the second example, but with all single quot
es
> again:
> sp_Monkey '24601','''ABC'',''DEF'',''GHI'''
> "ckeaton@.inergex.com" wrote:
>|||> It's not finding the individual values which leads me to believe that
> something about how the construct is being presented to the database is
> wrong.
Maybe you could show us your code? (I, for one, only see three messages so
far in this thread. It appears to have been orphaned in some way.)|||It looks like the statement is evaluating the variable as a single string.
For example, try the following in the Northwind sample database
EXEC sp_executesql N'SELECT * FROM Products
WHERE ProductName IN (@.var1, @.var2)', N'@.var1 varchar(50), @.var2 varchar(50)
',
@.var1='Konbu',@.var2='Tofu'
EXEC sp_executesql N'SELECT * FROM Products
WHERE ProductName IN (@.var1)', N'@.var1 varchar(50)',
@.var1='''Konbu'',''Tofu'''
The first one works, but the second doesn't.
If you wanted to pass your list of coupons as just comma-seperated, you
could do something like this
CREATE PROCEDURE sp_Monkey
@.Field1 varchar(10)
@.Field2 varchar(50)
AS
SET NOCOUNT ON
DECLARE @.coupons TABLE (coupon char(3))
DECLARE @.coupon char(3)
WHILE CHARINDEX(',',@.Field2)<>0
BEGIN
INSERT INTO @.coupons (coupon) VALUES (LEFT(@.Field2, CHARINDEX(',',@.Field2)
- 1))
SET @.Field2=SUBSTRING(@.Field2, CHARINDEX(',', @.Field2) + 1,LEN(@.Field2))
END
INSERT INTO @.coupons (coupon) VALUES (@.Field2)
UPDATE monkey
SET coupon = P.coupon
FROM monkey M, promotions P
WHERE M.recordID = P.recordID
AND P.coupon IN (SELECT coupon FROM @.coupons)
--alternatively AND EXISTS (SELECT coupon FROM @.coupons WHERE
coupon=P.coupon)
The procedure would be called like
sp_Monkey '24601','ABC,DEF,GHI'
The procedure splits the comma-seperated Field2 and inserts each value into
the table variable @.coupons, which is reference later.
"ckeaton@.inergex.com" wrote:
> I'm not sure why it's not working when I try to pass the value as it stand
s
> in your most recent example.
> It's not finding the individual values which leads me to believe that
> something about how the construct is being presented to the database is wr
ong.
> It should end out looking like this...
> UPDATE monkey
> SET coupon = P.coupon
> FROM monkey M, promotions P
> WHERE M.recordID = P.recordID
> AND P.coupon IN ('ABC', 'DEF', 'GHI');
> But it appears not to be doing that.
> Thank you for your suggestions.
> Frustrating, because this seems like such a simple thing, but it's trippin
g
> me up! :)
>|||You would want something like the second example, but with all single quotes
again:
sp_Monkey '24601','''ABC'',''DEF'',''GHI'''
"ckeaton@.inergex.com" wrote:
> So in my case, how do I differentiate between the string intended for @.Fie
ld1
> from the ones intended for @.Field2?
> sp_Monkey '24601', ''ABC'', ''DEF'', ''GHI'' -- all single quotes :)
> or do I do something like this:
> sp_Monkey '24601', " ''ABC'', ''DEF'', ''GHI'' " -- same as before, but
> encased in a double qoute to identify it as one string to be passed over.
> THANK YOU! :)
> "Mark Williams" wrote:
>
1
from the ones intended for @.Field2?
sp_Monkey '24601', ''ABC'', ''DEF'', ''GHI'' -- all single quotes :)
or do I do something like this:
sp_Monkey '24601', " ''ABC'', ''DEF'', ''GHI'' " -- same as before, but
encased in a double qoute to identify it as one string to be passed over.
THANK YOU! :)
"Mark Williams" wrote:
> You'll need double single-quotes (that's really clear!) to get what you wa
nt
> For example
> DECLARE @.foo varchar(50)
> SET @.foo = '''ABC'', ''DEF'', ''GHI''' --those are all single quotes!
> SELECT @.foo
> returns
> 'ABC', 'DEF', 'GHI'
>
> "ckeaton@.inergex.com" wrote:
>I'm not sure why it's not working when I try to pass the value as it stands
in your most recent example.
It's not finding the individual values which leads me to believe that
something about how the construct is being presented to the database is wron
g.
It should end out looking like this...
UPDATE monkey
SET coupon = P.coupon
FROM monkey M, promotions P
WHERE M.recordID = P.recordID
AND P.coupon IN ('ABC', 'DEF', 'GHI');
But it appears not to be doing that.
Thank you for your suggestions.
Frustrating, because this seems like such a simple thing, but it's tripping
me up! :)
"Mark Williams" wrote:
> You would want something like the second example, but with all single quot
es
> again:
> sp_Monkey '24601','''ABC'',''DEF'',''GHI'''
> "ckeaton@.inergex.com" wrote:
>|||> It's not finding the individual values which leads me to believe that
> something about how the construct is being presented to the database is
> wrong.
Maybe you could show us your code? (I, for one, only see three messages so
far in this thread. It appears to have been orphaned in some way.)|||It looks like the statement is evaluating the variable as a single string.
For example, try the following in the Northwind sample database
EXEC sp_executesql N'SELECT * FROM Products
WHERE ProductName IN (@.var1, @.var2)', N'@.var1 varchar(50), @.var2 varchar(50)
',
@.var1='Konbu',@.var2='Tofu'
EXEC sp_executesql N'SELECT * FROM Products
WHERE ProductName IN (@.var1)', N'@.var1 varchar(50)',
@.var1='''Konbu'',''Tofu'''
The first one works, but the second doesn't.
If you wanted to pass your list of coupons as just comma-seperated, you
could do something like this
CREATE PROCEDURE sp_Monkey
@.Field1 varchar(10)
@.Field2 varchar(50)
AS
SET NOCOUNT ON
DECLARE @.coupons TABLE (coupon char(3))
DECLARE @.coupon char(3)
WHILE CHARINDEX(',',@.Field2)<>0
BEGIN
INSERT INTO @.coupons (coupon) VALUES (LEFT(@.Field2, CHARINDEX(',',@.Field2)
- 1))
SET @.Field2=SUBSTRING(@.Field2, CHARINDEX(',', @.Field2) + 1,LEN(@.Field2))
END
INSERT INTO @.coupons (coupon) VALUES (@.Field2)
UPDATE monkey
SET coupon = P.coupon
FROM monkey M, promotions P
WHERE M.recordID = P.recordID
AND P.coupon IN (SELECT coupon FROM @.coupons)
--alternatively AND EXISTS (SELECT coupon FROM @.coupons WHERE
coupon=P.coupon)
The procedure would be called like
sp_Monkey '24601','ABC,DEF,GHI'
The procedure splits the comma-seperated Field2 and inserts each value into
the table variable @.coupons, which is reference later.
"ckeaton@.inergex.com" wrote:
> I'm not sure why it's not working when I try to pass the value as it stand
s
> in your most recent example.
> It's not finding the individual values which leads me to believe that
> something about how the construct is being presented to the database is wr
ong.
> It should end out looking like this...
> UPDATE monkey
> SET coupon = P.coupon
> FROM monkey M, promotions P
> WHERE M.recordID = P.recordID
> AND P.coupon IN ('ABC', 'DEF', 'GHI');
> But it appears not to be doing that.
> Thank you for your suggestions.
> Frustrating, because this seems like such a simple thing, but it's trippin
g
> me up! :)
>|||You would want something like the second example, but with all single quotes
again:
sp_Monkey '24601','''ABC'',''DEF'',''GHI'''
"ckeaton@.inergex.com" wrote:
> So in my case, how do I differentiate between the string intended for @.Fie
ld1
> from the ones intended for @.Field2?
> sp_Monkey '24601', ''ABC'', ''DEF'', ''GHI'' -- all single quotes :)
> or do I do something like this:
> sp_Monkey '24601', " ''ABC'', ''DEF'', ''GHI'' " -- same as before, but
> encased in a double qoute to identify it as one string to be passed over.
> THANK YOU! :)
> "Mark Williams" wrote:
>
Monday, March 26, 2012
Passing in variable number of parameters to a stored procedure
I am fairly new to MSSQL. Looking for a answer to a simple question.
I have a application which passes in lot of stuff from the UI into a stored procedure that has to be inserted into a MSSQL 2005 database. All the information that is passed will be spilt into 4 inserts hitting 4 seperate tables. All 4 inserts will be part of a stored procedure that have to be in one TRANSACTION. All but one insert are straight forward.
The structure of this table is something like
PKID
customerID
email address
....
customerID is not unique and can have n email addresses passed in. Each entry into this table when inserted into, will be passed n addresses (The number of email addresses passed is controlled by the user. It can be from 1..n). Constructing dynamic SQL is not an option. The SP to insert all the data is already in place. Typically I would just create the SP with IN parameters that I will use to insert into tables. In this case I can't do that since the number of email addresses passed is dynamic. My question is what's the best way to design this SP, where n email addresses are passed and each of them will have to be passed into a seperate insert statement? I can think of two ways to this...
Is there a way to create a variable length array as a IN parameter to capture the n email addresses coming in and use them to construct multiple insert statements?
Is it possible to get all the n email addresses as a comma seperated string? I know this is possible, but I am not sure how to parse this string and capture the n email addresses into variables before I construct them into insert statements.
Any other ways to do this? ThanksFrom a relational perspective, the best answer is to write a single stored procedure that takes one email address and processes it, and then call that procedure N times from your UI. This is because a relational database is based on relational algebra, and while that processes sets well as output, it doesn't process them nearly as easily as input.
If you decide to pursue your original idea and use a delimited (probably comma separated) list, you can use fSplit (http://www.dbforums.com/t997070.html) which I posted here ages ago. This is cleaner from the UI perspective, but it will eventually byte you because of the poor fit with relational databases.
-PatP|||This is because a relational database is based on relational algebra, and while that processes sets well as output, it doesn't process them nearly as easily as input.Really? I never knew that.
What's the basic reasoning around that Pat?|||Ok - I had a few mins. Played around.
CREATE TABLE InsertTable
(
MyPKFld VarChar(5) PRIMARY KEY
)
DECLARE @.i AS SmallInt
SET NOCOUNT ON
DECLARE @.Insert AS VarChar(2000)
--SELECT @.Insert = '10001,10022,20099,15073,28948,18737,90273,27910,3 7891'
SELECT @.Insert = '10001,10022,15073,18737,20099,27910,28948,37891,9 0273'
SELECT @.i = 1
DECLARE @.LoopUpper AS TinyInt
SELECT @.LoopUpper = (SELECT COUNT(*) FROM dbo.Split(@.Insert, ','))
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DECLARE @.Start AS DateTime
SELECT @.Start = GETDATE()
WHILE @.i <= @.LoopUpper BEGIN
INSERT INTO InsertTable
SELECT Data
FROM dbo.Split(@.Insert, ',')
WHERE ID = @.i
SELECT @.i = @.i + 1
END
PRINT 'LOOP takes ' + CAST(DATEDIFF(ms, @.Start, GETDATE()) AS VarChar(4)) + 'ms'
DELETE
FROM InsertTable
WHERE MyPKFld > 10000
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SELECT @.Start = GETDATE()
INSERT INTO InsertTable
SELECT Data
FROM dbo.Split(@.Insert, ',')
PRINT 'SET takes ' + CAST(DATEDIFF(ms, @.Start, GETDATE()) AS VarChar(4)) + 'ms'
DROP TABLE InsertTable
Basically tests looping and inserting one record on each pass and inserting a set. I typically get the set at between 1/3 and 2/3 the time the loop takes. What have I missed?|||What's the basic reasoning around that Pat?In a set based environment (where sets are fully supported by both the language itself and the implementation), there's no issue. SQL as a language doesn't support passing sets in (at least it doesn't yet, the new draft standard has basic support for them).
What you are doing is passing a complex argument (more than one atomic element in a single argument). That is outside of relational algebra altogether since it violates first normal form. The reason it runs faster is that you're trading multiple calls in a relational solution for processing complexity in a code oriented solution. It certainly works, and at least for now it is faster, but eventually it will get to the point that it causes problems.
-PatP|||Thanks Pat
I don't think I totally get you. The csv string is not normalised. However the function (or whatever code one might run) normalises the input. As far as SQL Server is concerned, it might always have been a set.
It certainly works, and at least for now it is faster, but eventually it will get to the point that it causes problems.By this do you mean it will be a bugger to maintain or do you mean there will be some sort of technical problem over time? If the latter - what would that be?
Grateful for the education as ever :)|||When you create a non-normalized interface like this, you've broken one of the fundamental building block "contracts" between a client and server. That relationship is either relational, or it isn't relational, and any non-relational interface makes the relationship between client and server non-relational.
This kind of change can make sense when you're implementing a different paradyme such as OOP. When you do that, you leave the relational world behind, so the rigorous "proofs" of behavior no longer have any meaning, but that happens any time you switch from one paradyme to another.
There are lots of really fundamental characteristics involved in relational processing. These make it predictable, which in turn makes it dependable. While relational technology certainly isn't the best possible way to do things, it is the best that I've found so far that is widely commercially supported and clearly understood by many professionals.
There are thousands of ways this can go wrong (and I've personally tried several hundred of those ;)). One example would be that you could have an application start out as a single server implementation, grow to use a cluster, expand further to use a cloud of replicating servers... When something goes worng :o in the process of getting data from a web server client into the data cloud, you have to start using network monitors to find the problem since you can't rely on which app/web server will initiate the conversation and which database server will process it. If you have bundled multiple calls into one and then rely on the server to parse them, you can no longer predict what the data "payload" will be exactly, so you need to start doing moderately sophisticated pattern matching. The process gets ugly, really fast.
Not everyone will face this specific problem. Given sufficient time though, I'll guarantee that you'll hit some problem related to the bundling effect. If you are making a paradyme shift, and that shift buys you something substantial in terms of coding time, support, ability to use new features, etc. then it is certainly worth considering. If all it buys you is a slight performance gain in exchange for the predictability of the pure relational model, I'd be hard pressed to "green light" this change.
-PatP|||Thanks Pat - appreciated :D
As it happens - I asked this question some time ago and got one yay and one nay from two of your esteemed peers hence why I jumped on your answer.
EDIT - plus my initial reading of your answer went against everything I thought I knew about SQL.sql
I have a application which passes in lot of stuff from the UI into a stored procedure that has to be inserted into a MSSQL 2005 database. All the information that is passed will be spilt into 4 inserts hitting 4 seperate tables. All 4 inserts will be part of a stored procedure that have to be in one TRANSACTION. All but one insert are straight forward.
The structure of this table is something like
PKID
customerID
email address
....
customerID is not unique and can have n email addresses passed in. Each entry into this table when inserted into, will be passed n addresses (The number of email addresses passed is controlled by the user. It can be from 1..n). Constructing dynamic SQL is not an option. The SP to insert all the data is already in place. Typically I would just create the SP with IN parameters that I will use to insert into tables. In this case I can't do that since the number of email addresses passed is dynamic. My question is what's the best way to design this SP, where n email addresses are passed and each of them will have to be passed into a seperate insert statement? I can think of two ways to this...
Is there a way to create a variable length array as a IN parameter to capture the n email addresses coming in and use them to construct multiple insert statements?
Is it possible to get all the n email addresses as a comma seperated string? I know this is possible, but I am not sure how to parse this string and capture the n email addresses into variables before I construct them into insert statements.
Any other ways to do this? ThanksFrom a relational perspective, the best answer is to write a single stored procedure that takes one email address and processes it, and then call that procedure N times from your UI. This is because a relational database is based on relational algebra, and while that processes sets well as output, it doesn't process them nearly as easily as input.
If you decide to pursue your original idea and use a delimited (probably comma separated) list, you can use fSplit (http://www.dbforums.com/t997070.html) which I posted here ages ago. This is cleaner from the UI perspective, but it will eventually byte you because of the poor fit with relational databases.
-PatP|||This is because a relational database is based on relational algebra, and while that processes sets well as output, it doesn't process them nearly as easily as input.Really? I never knew that.
What's the basic reasoning around that Pat?|||Ok - I had a few mins. Played around.
CREATE TABLE InsertTable
(
MyPKFld VarChar(5) PRIMARY KEY
)
DECLARE @.i AS SmallInt
SET NOCOUNT ON
DECLARE @.Insert AS VarChar(2000)
--SELECT @.Insert = '10001,10022,20099,15073,28948,18737,90273,27910,3 7891'
SELECT @.Insert = '10001,10022,15073,18737,20099,27910,28948,37891,9 0273'
SELECT @.i = 1
DECLARE @.LoopUpper AS TinyInt
SELECT @.LoopUpper = (SELECT COUNT(*) FROM dbo.Split(@.Insert, ','))
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DECLARE @.Start AS DateTime
SELECT @.Start = GETDATE()
WHILE @.i <= @.LoopUpper BEGIN
INSERT INTO InsertTable
SELECT Data
FROM dbo.Split(@.Insert, ',')
WHERE ID = @.i
SELECT @.i = @.i + 1
END
PRINT 'LOOP takes ' + CAST(DATEDIFF(ms, @.Start, GETDATE()) AS VarChar(4)) + 'ms'
DELETE
FROM InsertTable
WHERE MyPKFld > 10000
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SELECT @.Start = GETDATE()
INSERT INTO InsertTable
SELECT Data
FROM dbo.Split(@.Insert, ',')
PRINT 'SET takes ' + CAST(DATEDIFF(ms, @.Start, GETDATE()) AS VarChar(4)) + 'ms'
DROP TABLE InsertTable
Basically tests looping and inserting one record on each pass and inserting a set. I typically get the set at between 1/3 and 2/3 the time the loop takes. What have I missed?|||What's the basic reasoning around that Pat?In a set based environment (where sets are fully supported by both the language itself and the implementation), there's no issue. SQL as a language doesn't support passing sets in (at least it doesn't yet, the new draft standard has basic support for them).
What you are doing is passing a complex argument (more than one atomic element in a single argument). That is outside of relational algebra altogether since it violates first normal form. The reason it runs faster is that you're trading multiple calls in a relational solution for processing complexity in a code oriented solution. It certainly works, and at least for now it is faster, but eventually it will get to the point that it causes problems.
-PatP|||Thanks Pat
I don't think I totally get you. The csv string is not normalised. However the function (or whatever code one might run) normalises the input. As far as SQL Server is concerned, it might always have been a set.
It certainly works, and at least for now it is faster, but eventually it will get to the point that it causes problems.By this do you mean it will be a bugger to maintain or do you mean there will be some sort of technical problem over time? If the latter - what would that be?
Grateful for the education as ever :)|||When you create a non-normalized interface like this, you've broken one of the fundamental building block "contracts" between a client and server. That relationship is either relational, or it isn't relational, and any non-relational interface makes the relationship between client and server non-relational.
This kind of change can make sense when you're implementing a different paradyme such as OOP. When you do that, you leave the relational world behind, so the rigorous "proofs" of behavior no longer have any meaning, but that happens any time you switch from one paradyme to another.
There are lots of really fundamental characteristics involved in relational processing. These make it predictable, which in turn makes it dependable. While relational technology certainly isn't the best possible way to do things, it is the best that I've found so far that is widely commercially supported and clearly understood by many professionals.
There are thousands of ways this can go wrong (and I've personally tried several hundred of those ;)). One example would be that you could have an application start out as a single server implementation, grow to use a cluster, expand further to use a cloud of replicating servers... When something goes worng :o in the process of getting data from a web server client into the data cloud, you have to start using network monitors to find the problem since you can't rely on which app/web server will initiate the conversation and which database server will process it. If you have bundled multiple calls into one and then rely on the server to parse them, you can no longer predict what the data "payload" will be exactly, so you need to start doing moderately sophisticated pattern matching. The process gets ugly, really fast.
Not everyone will face this specific problem. Given sufficient time though, I'll guarantee that you'll hit some problem related to the bundling effect. If you are making a paradyme shift, and that shift buys you something substantial in terms of coding time, support, ability to use new features, etc. then it is certainly worth considering. If all it buys you is a slight performance gain in exchange for the predictability of the pure relational model, I'd be hard pressed to "green light" this change.
-PatP|||Thanks Pat - appreciated :D
As it happens - I asked this question some time ago and got one yay and one nay from two of your esteemed peers hence why I jumped on your answer.
EDIT - plus my initial reading of your answer went against everything I thought I knew about SQL.sql
Passing in own Variable to print
I want to display some of the information not in the datasource selection but
through application passing in information. For example: personName,
reportName (dynamical defined depending on which button user click) and etc.
These variable only use in header or footer.
How can I pass these information to the report viwer? Any help is greatly
appreciated.I've done similar things using report parameters.
"Locus" <Locus@.discussions.microsoft.com> wrote in message
news:1FF52F89-D3F6-44C3-B42D-45BBABBDE2F0@.microsoft.com...
>I want to display some of the information not in the datasource selection
>but
> through application passing in information. For example: personName,
> reportName (dynamical defined depending on which button user click) and
> etc.
> These variable only use in header or footer.
> How can I pass these information to the report viwer? Any help is greatly
> appreciated.|||Hi Bill, how you do it?
I tried adding parameters, it seems to automatically pop up to ask for input
value, not allowing me to passing in value programmatically.
-- Locus
"Bill Miller" wrote:
> I've done similar things using report parameters.
> "Locus" <Locus@.discussions.microsoft.com> wrote in message
> news:1FF52F89-D3F6-44C3-B42D-45BBABBDE2F0@.microsoft.com...
> >I want to display some of the information not in the datasource selection
> >but
> > through application passing in information. For example: personName,
> > reportName (dynamical defined depending on which button user click) and
> > etc.
> > These variable only use in header or footer.
> >
> > How can I pass these information to the report viwer? Any help is greatly
> > appreciated.
>
>|||Try setting the Parameters Hidden parameter to checked.
"Locus" wrote:
> Hi Bill, how you do it?
> I tried adding parameters, it seems to automatically pop up to ask for input
> value, not allowing me to passing in value programmatically.
> -- Locus
> "Bill Miller" wrote:
> > I've done similar things using report parameters.
> >
> > "Locus" <Locus@.discussions.microsoft.com> wrote in message
> > news:1FF52F89-D3F6-44C3-B42D-45BBABBDE2F0@.microsoft.com...
> > >I want to display some of the information not in the datasource selection
> > >but
> > > through application passing in information. For example: personName,
> > > reportName (dynamical defined depending on which button user click) and
> > > etc.
> > > These variable only use in header or footer.
> > >
> > > How can I pass these information to the report viwer? Any help is greatly
> > > appreciated.
> >
> >
> >
through application passing in information. For example: personName,
reportName (dynamical defined depending on which button user click) and etc.
These variable only use in header or footer.
How can I pass these information to the report viwer? Any help is greatly
appreciated.I've done similar things using report parameters.
"Locus" <Locus@.discussions.microsoft.com> wrote in message
news:1FF52F89-D3F6-44C3-B42D-45BBABBDE2F0@.microsoft.com...
>I want to display some of the information not in the datasource selection
>but
> through application passing in information. For example: personName,
> reportName (dynamical defined depending on which button user click) and
> etc.
> These variable only use in header or footer.
> How can I pass these information to the report viwer? Any help is greatly
> appreciated.|||Hi Bill, how you do it?
I tried adding parameters, it seems to automatically pop up to ask for input
value, not allowing me to passing in value programmatically.
-- Locus
"Bill Miller" wrote:
> I've done similar things using report parameters.
> "Locus" <Locus@.discussions.microsoft.com> wrote in message
> news:1FF52F89-D3F6-44C3-B42D-45BBABBDE2F0@.microsoft.com...
> >I want to display some of the information not in the datasource selection
> >but
> > through application passing in information. For example: personName,
> > reportName (dynamical defined depending on which button user click) and
> > etc.
> > These variable only use in header or footer.
> >
> > How can I pass these information to the report viwer? Any help is greatly
> > appreciated.
>
>|||Try setting the Parameters Hidden parameter to checked.
"Locus" wrote:
> Hi Bill, how you do it?
> I tried adding parameters, it seems to automatically pop up to ask for input
> value, not allowing me to passing in value programmatically.
> -- Locus
> "Bill Miller" wrote:
> > I've done similar things using report parameters.
> >
> > "Locus" <Locus@.discussions.microsoft.com> wrote in message
> > news:1FF52F89-D3F6-44C3-B42D-45BBABBDE2F0@.microsoft.com...
> > >I want to display some of the information not in the datasource selection
> > >but
> > > through application passing in information. For example: personName,
> > > reportName (dynamical defined depending on which button user click) and
> > > etc.
> > > These variable only use in header or footer.
> > >
> > > How can I pass these information to the report viwer? Any help is greatly
> > > appreciated.
> >
> >
> >
Passing GUID into dtexec, invalid cast to a string variable. Solution?
I am getting an invalid cast specification when I call dtexec and try to /SET a user variable within the package that is defined as a string data type. Is there any solution to this? I have tried passing the GUID with {} w/o {} w/ '' w/ "" etc.. many variations... and still get an invalid cast specification. Is there a data type that I need to set the User variable to besides String? The User Variable is used to Select records from a SQL data source where the GUID is stored. I do not have an option of GUID data type for a User Variable.
Thanks for any help! Aaron B.
What does your SET look like, this works fine for me. I just set it, and check the value in a Script Task, just to ensure the value is being passed in OK.
/SET "\Package.Variables[StringVariable].Value";"{2B7045E0-F3D2-478a-BCC3-0E73858C59A8}"
|||Thanks for your help i am using another field for the subquery which is not GUID and its working fine. Thanks for your help.
Passing Global Variables from a Execute Package Task
I have a package (Package1) that is run from another package (Package2) via a Execute Package Task. I set a Global Variable called sErrorMessage in the in Package1 and would like to access that Global Variable in an ActiveX Script Task in Package2. How can I do this?Package 1:
Edit your Execute Package Task, and click on the "Outer Package Global Variables" tab. Select all the Global Variables you want available to Package 2. Save and close.
Package 2:
Edit your ActiveX Script Task and use the code below to access the variable:
DTSGlobalVariables("<global variable name>").Value|||Package1 doesnt have an Execute Package Task, Package2 does...
Package1:
ActiveX Script --> Connection1 -TransformData-> Connection2
Package2:
Exec Package1 -Success-> Send Success Email
'-Failure-> ActiveX (msgbox DTSGlobalVariables("sErrorMessage").Value) --> Send Failure Email|||Ooookay.. so substitute 2 for 1 and 1 for 2... done...|||Thats what I figured when you posted, but I tried that and got nothing.....I might have edited the reply since you looked at it...
When I run Package1 by itself I can see in the Package1 Properties that the Global Variable is being set properly from Package1.
The Execute Package Task in Package2 has the Outer Package Global Variable selected, but when I reference it with DTSGlobalVariables("sErrorMessage").Value in ActiveX Code it shows the variable as being empty.|||Just a update on this:
Seppuku was nice enough the help me via AIM. So far we have come to the conclusion that there is no builtin way for a child to pass data to the parent except maybe through file drops, or writing to a db table.|||Will attempt having the child write to a common table for communication...and will post the results here.
Edit your Execute Package Task, and click on the "Outer Package Global Variables" tab. Select all the Global Variables you want available to Package 2. Save and close.
Package 2:
Edit your ActiveX Script Task and use the code below to access the variable:
DTSGlobalVariables("<global variable name>").Value|||Package1 doesnt have an Execute Package Task, Package2 does...
Package1:
ActiveX Script --> Connection1 -TransformData-> Connection2
Package2:
Exec Package1 -Success-> Send Success Email
'-Failure-> ActiveX (msgbox DTSGlobalVariables("sErrorMessage").Value) --> Send Failure Email|||Ooookay.. so substitute 2 for 1 and 1 for 2... done...|||Thats what I figured when you posted, but I tried that and got nothing.....I might have edited the reply since you looked at it...
When I run Package1 by itself I can see in the Package1 Properties that the Global Variable is being set properly from Package1.
The Execute Package Task in Package2 has the Outer Package Global Variable selected, but when I reference it with DTSGlobalVariables("sErrorMessage").Value in ActiveX Code it shows the variable as being empty.|||Just a update on this:
Seppuku was nice enough the help me via AIM. So far we have come to the conclusion that there is no builtin way for a child to pass data to the parent except maybe through file drops, or writing to a db table.|||Will attempt having the child write to a common table for communication...and will post the results here.
Passing figures calculated in subreports
Can anybody tell me if it is possible to use a variable calculated within a subreport outside this scope (in the parent report)?
I have to list (eg) an opening balance, set of processing data, adjustments, then a closing balance.
The opening balance is a single figure in my main report, and the processing data is in the details section along with it's sum. This leaves the adjustments which have to be listed individually and then summed - done in a subreport. Can I access this sum in the body of the main report, as I need it to calculate a closing balance.
Thank you!Use shared variable|||Thanks for pointing me in the right direction. If anyone else is interested here is an article which gave a clear step by step explanation:
http://www.experts-exchange.com/Databases/Crystal_Reports/Q_21141425.html
I have to list (eg) an opening balance, set of processing data, adjustments, then a closing balance.
The opening balance is a single figure in my main report, and the processing data is in the details section along with it's sum. This leaves the adjustments which have to be listed individually and then summed - done in a subreport. Can I access this sum in the body of the main report, as I need it to calculate a closing balance.
Thank you!Use shared variable|||Thanks for pointing me in the right direction. If anyone else is interested here is an article which gave a clear step by step explanation:
http://www.experts-exchange.com/Databases/Crystal_Reports/Q_21141425.html
Friday, March 23, 2012
Passing datetime variable to stored proc as parameter
Hello,
I'm attempting to pass a datetime variable to a stored proc (called via sql task). The variables are set in a previous task where they act as OUTPUT paramters from a stored proc. The variables are set correctly after that task executes. The data type for those parameters is set to DBTIMESTAMP.
When I try to exectue a similar task passing those variables as parameters, I get an error:
Error: 0xC002F210 at ax_settle, Execute SQL Task: Executing the query "exec ? = dbo.ax_settle_2 ?, ?,?,3,1" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
If I replace the 2nd and 3rd parameters with quoted strings, it is successful:
exec ?= dbo.ax_settle ?, '3/29/06', '4/30/06',3,1
The stored proc is expecting datetime parameters.
Thanks for the help.
Mike
As a stopgap, I'm building the exec statement in an expression - converting the dates to single quoted strings.
I would still like to see the correct way to pass date parameters, so if anybody can help it would be much appreciated.
Thankee.
Mike
|||Any chance your passing the value in as type "DBTimestamp"? Try changing the parameter to type DATE
This looks like a bug to me:
setup:
1. Extract date from query (SSIS will show type as DBTIMESTAMP..)
2. Pass that same date taken from SQL Server as a parameter of type DBTimestamp back into the same SQL Server (using same connection) and it will error with message like you have above.
I tried changing the stored procedure to take nvarchar(2000) so I could see what it was passing for the DBTimestamp parameter to SQL and it was passing '' (empty set.. not to be confused with NULL)
Passing Date to Stored procedure as variable
Thanks in advance for your help!!!!!
I have created a stored procedure that uses a date range for paramenters. As long as I hardcode the dates in (3/21/03, 3/25/03) I get no errors. As soon as I replace the dates with the variable name and try to run the sp, I get an error message that it can't convert string to date.
It is my plan to call this sp from a web page and pass the date paramenters where they look like this 3/21/03
Here is what I have
@.BegDate Smalldatetime,
@.EndDate Smalldatetime
....
....
Where EntryDate BETWEEN '@.BegDate' AND '@.EndDate'
Could someone please help me understand what I am missing?
Thanks,
Leeyou are searching where EntryDate is between the string '@.BegDate' and the string '@.EndDate' not the values contained in @.BegDate and @.EndDate.
use Where EntryDate BETWEEN @.BegDate AND @.EndDate|||Thanks so much!!!!!! ' '
I have created a stored procedure that uses a date range for paramenters. As long as I hardcode the dates in (3/21/03, 3/25/03) I get no errors. As soon as I replace the dates with the variable name and try to run the sp, I get an error message that it can't convert string to date.
It is my plan to call this sp from a web page and pass the date paramenters where they look like this 3/21/03
Here is what I have
@.BegDate Smalldatetime,
@.EndDate Smalldatetime
....
....
Where EntryDate BETWEEN '@.BegDate' AND '@.EndDate'
Could someone please help me understand what I am missing?
Thanks,
Leeyou are searching where EntryDate is between the string '@.BegDate' and the string '@.EndDate' not the values contained in @.BegDate and @.EndDate.
use Where EntryDate BETWEEN @.BegDate AND @.EndDate|||Thanks so much!!!!!! ' '
Passing data from db to ssis variable
Is there anyway to pass data from a sql database to a variable in a ssis package. I'm trying to get a few fields from a sql database into some variables in my package and send an email using these variables with the send mail task?
Thanks,
Execute SQL task can do this.|||I tried, but don't even know where to begin. is there a tutorial or something on the net some where?|||Have you searched this forum? Tons of examples.Execute SQL task.
If you are selecting where the result is a single row:
Result Set - Single row. Result set tab: Result Name: 0 thru n based on the number of columns. Then simply select the variable name you need to map to.|||There is more than 1 row in my table and 4 columns. I looked at a couple of examples but can't figure it out.|||http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx|||
I have everything set up, when i go to expressions in the send mail task which I have inside my foreach loop and populate the to,from,subject and body lines with my variables from the result set, nothing happens? Am I able to use the send mail task in a foreach loop like this?
|||
I have everything set up, when i go to expressions in the send mail task which I have inside my foreach loop and populate the to,from,subject and body lines with my variables from the result set, nothing happens? Am I able to use the send mail task in a foreach loop like this?
Sure you can. What do you mean nothing happens?|||
sorry to keep bugging you and I appreciate your help and patience, but when I open my package the send mail task has error code can't find recipient. I have 4 expressions in this mail task:
toline--set to my to variable
fromline--set to my from variable
subject--set to my subject variable
messagesource--set to my body variable
From what i gather, it's not getting the data from the variables for some reason. Everything seems to look ok.
|||Perhaps you need to add a script task inside the foreach loop so that you can display (via "MsgBox()") the contents of the variables to actually verify that they have data in them.|||i tried that but i get an element is not found in the collection error.|||
i tried that but i get an element is not found in the collection error.
First, make sure that your variables are of package scope, not some other task scope.
And how did you reference the variables in your script task?|||
they are part of the package and not just a specific task, i also set the delay validation property to true for the foreach loop and mail task, and the error is cannot recognize recipient address. Here is my script code.
Public Sub Main()
Dim Message As String
Message = Dts.Variables("to").Value + " " + Dts.Variables("from").Value + ", " + Dts.Variables("subject").Value
MsgBox(Message)
Dts.TaskResult = Dts.Results.Success
End Sub
|||Try this instead:
Code Snippet
Public Sub Main()
Dim Message as String
Dim vars as Variables
Dts.VariableDispenser.LockForRead("to")
Dts.VariableDispenser.LockForRead("from")
Dts.VariableDispenser.LockForRead("subject")
Dim ToVar as String = vars("to").Value.ToString
Dim FromVar as String = vars("from").Value.ToString
Dim SubjectVar as String = vars("subject").Value.ToString
Message = ToVar + " " + FromVar + ", " + SubjectVar
MsgBox(Message)
Dts.TaskResult = Dts.Results.Success
End Sub
Wednesday, March 21, 2012
Passing contents of text file into variable?
Is there a way to get the contents of a text into a variable in PL/SQL?Hello,
if you mean you want to read a file into a pl/sql var the use the
SYS.UTL_FILE to load th content.
Hope that helps ?
Best regards
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com
if you mean you want to read a file into a pl/sql var the use the
SYS.UTL_FILE to load th content.
Hope that helps ?
Best regards
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com
Passing Command line arguments from Visual Studio
Hi all,
Is it possible to pass command line arguments to a package when running it from within VS? I want to set the value of a variable via the commandline, and found that you can to this in DtExec with the "/set \Package.Variables[...].Value;..." syntax. According to the docs, you should be able to pass the same argument via the 'CmdLineArguments' property in the 'Properties' dialog of an SSIS project in VS (CmdLineArguments. Run the package with the specified command-line arguments. For information about command-line arguments, see dtexec Utility), but unfortunately, this doesn't seem to work (even though the exact same argument does work when entered in DtExec)
Is it possible to pass command line arguments to a package when running it from within VS? I want to set the value of a variable via the commandline, and found that you can to this in DtExec with the "/set \Package.Variables[...].Value;..." syntax. According to the docs, you should be able to pass the same argument via the 'CmdLineArguments' property in the 'Properties' dialog of an SSIS project in VS (CmdLineArguments. Run the package with the specified command-line arguments. For information about command-line arguments, see dtexec Utility), but unfortunately, this doesn't seem to work (even though the exact same argument does work when entered in DtExec)
Any help would be greatly appreciated :-)
Steven
No. Since you are in a IDE/Debug environment with Visual Studio, I think it is a minor limitation that you cannot do this, you can just change and set anything your require in the IDE.
If you have external configuration information you wish to set all the time then /SET is probably not the best solution. Using the built in Configurations support in SSIS would be a better choice and this does work in VS. See the SSIS menu.
|||I have the exact same issue. I just need a single parameter that needs to change every time I call my package, so it doesn't really warrant external configuration. The following article implies that CmdLineArguments are only taken into account whenever you use dtexec externally to execute the package and then attach to it to debug. See the last section ("Testing and Debugging your code") for details.
http://msdn2.microsoft.com/en-us/library/ms403356.aspx
Passing Command line arguments from Visual Studio
Hi all,
Is it possible to pass command line arguments to a package when running it from within VS? I want to set the value of a variable via the commandline, and found that you can to this in DtExec with the "/set \Package.Variables[...].Value;..." syntax. According to the docs, you should be able to pass the same argument via the 'CmdLineArguments' property in the 'Properties' dialog of an SSIS project in VS (CmdLineArguments. Run the package with the specified command-line arguments. For information about command-line arguments, see dtexec Utility), but unfortunately, this doesn't seem to work (even though the exact same argument does work when entered in DtExec)
Is it possible to pass command line arguments to a package when running it from within VS? I want to set the value of a variable via the commandline, and found that you can to this in DtExec with the "/set \Package.Variables[...].Value;..." syntax. According to the docs, you should be able to pass the same argument via the 'CmdLineArguments' property in the 'Properties' dialog of an SSIS project in VS (CmdLineArguments. Run the package with the specified command-line arguments. For information about command-line arguments, see dtexec Utility), but unfortunately, this doesn't seem to work (even though the exact same argument does work when entered in DtExec)
Any help would be greatly appreciated :-)
Steven
No. Since you are in a IDE/Debug environment with Visual Studio, I think it is a minor limitation that you cannot do this, you can just change and set anything your require in the IDE.
If you have external configuration information you wish to set all the time then /SET is probably not the best solution. Using the built in Configurations support in SSIS would be a better choice and this does work in VS. See the SSIS menu.
|||I have the exact same issue. I just need a single parameter that needs to change every time I call my package, so it doesn't really warrant external configuration. The following article implies that CmdLineArguments are only taken into account whenever you use dtexec externally to execute the package and then attach to it to debug. See the last section ("Testing and Debugging your code") for details.
http://msdn2.microsoft.com/en-us/library/ms403356.aspx
sqlPassing an Entire XQuery String as Variable
Hi,
Is there any way to formulate an entire XQuery (XPath) string as a
variable and then pass it to a XQuery e.g.
Use something like this:
@.Path nvarchar(4000)
set @.Path='//category[@.name="RTE"]//sku'
SELECT AllSKUs.value('./@.name','nvarchar(MAX)') AS SKU
FROM Hierarchy CROSS APPLY
HierarchyXML.nodes(@.Path) AS NewTable(AllSKUs)
To achieve something like this:
SELECT AllSKUs.value('./@.name','nvarchar(MAX)') AS SKU
FROM Hierarchy CROSS APPLY
HierarchyXML.nodes('//category[@.name="RTE"]//sku') AS NewTable(AllSKUs)
Cheers,
Bob
Hello Bob,
No, not really because the XQuery parser is one-pass, so something like this:
declare @.x xml
set @.x = '<v>3</v>'
declare @.q varchar(255)
set @.q = '(/v)[1]'
select @.x.value('sql:variable("@.q")','int')
go
assumes that that query in @.q is meant to be a literal value so it doens't
get eval'ed as a query.
Cheers,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Thanks Kent,
That's a shame really - the power of XPath is not fully realised there.
I think I'll try writing the whole stored procedure dynamically.
Bob
Kent Tegels wrote:
> Hello Bob,
> No, not really because the XQuery parser is one-pass, so something like
> this:
> declare @.x xml
> set @.x = '<v>3</v>'
> declare @.q varchar(255)
> set @.q = '(/v)[1]'
> select @.x.value('sql:variable("@.q")','int')
> go
> assumes that that query in @.q is meant to be a literal value so it
> doens't get eval'ed as a query.
> Cheers,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
|||Hi Bob,
> That's a shame really - the power of XPath is not fully realised
> there. I think I'll try writing the whole stored procedure
> dynamically.
I don't know if I'd agree with that. I would say that the team has done the
best they could with limited resources, variable targets and a slower than
expected standards process. Could they have done more? Sure -- if they had
more people and time. They didn't. So I look at as glass half full. Afterall,
we're much futher along the XQuery path than the client (.NET folks are)
period.
And as I look it, I think we're only going to get limited improvements in
this support over time as Microsoft seems much more interested in putting
their resources into LINQ instead of XQuery. I'm okay with that because it
solves the problem (at least eventually), but part of me is sad to seem go
this way instead of fully investing in both.
Time will tell...
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||It is a shame - but what we've got now is still a huge leap ahead of
what we had before. And it's, in general, a joy to work with.
Thanks again,
Bob
Kent Tegels wrote:
> Hi Bob,
>
> I don't know if I'd agree with that. I would say that the team has done
> the best they could with limited resources, variable targets and a
> slower than expected standards process. Could they have done more? Sure
> -- if they had more people and time. They didn't. So I look at as glass
> half full. Afterall, we're much futher along the XQuery path than the
> client (.NET folks are) period.
> And as I look it, I think we're only going to get limited improvements
> in this support over time as Microsoft seems much more interested in
> putting their resources into LINQ instead of XQuery. I'm okay with that
> because it solves the problem (at least eventually), but part of me is
> sad to seem go this way instead of fully investing in both.
> Time will tell...
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
|||Hi Bob
Thanks for the comment ;)
We are aware of the request to not having to write dynamic SQL to provide
the XPath expression as a string variable to the XQuery methods.
I would like to encourage you to either file (or if already filed vote) such
a request at
http://lab.msdn.microsoft.com/productfeedback/
On the server we are still investing in XQuery, but as Kent mentions, many
competing requests for the same resources will have to be influenced by you
the customer requesting us to help you improve.
Thanks
Michael
"Bob Turner" <b_turner@.hotmail.co.uk> wrote in message
news:DhCTf.77298$zk4.16164@.fe3.news.blueyonder.co. uk...[vbcol=seagreen]
> It is a shame - but what we've got now is still a huge leap ahead of what
> we had before. And it's, in general, a joy to work with.
> Thanks again,
> Bob
> Kent Tegels wrote:
Is there any way to formulate an entire XQuery (XPath) string as a
variable and then pass it to a XQuery e.g.
Use something like this:
@.Path nvarchar(4000)
set @.Path='//category[@.name="RTE"]//sku'
SELECT AllSKUs.value('./@.name','nvarchar(MAX)') AS SKU
FROM Hierarchy CROSS APPLY
HierarchyXML.nodes(@.Path) AS NewTable(AllSKUs)
To achieve something like this:
SELECT AllSKUs.value('./@.name','nvarchar(MAX)') AS SKU
FROM Hierarchy CROSS APPLY
HierarchyXML.nodes('//category[@.name="RTE"]//sku') AS NewTable(AllSKUs)
Cheers,
Bob
Hello Bob,
No, not really because the XQuery parser is one-pass, so something like this:
declare @.x xml
set @.x = '<v>3</v>'
declare @.q varchar(255)
set @.q = '(/v)[1]'
select @.x.value('sql:variable("@.q")','int')
go
assumes that that query in @.q is meant to be a literal value so it doens't
get eval'ed as a query.
Cheers,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Thanks Kent,
That's a shame really - the power of XPath is not fully realised there.
I think I'll try writing the whole stored procedure dynamically.
Bob
Kent Tegels wrote:
> Hello Bob,
> No, not really because the XQuery parser is one-pass, so something like
> this:
> declare @.x xml
> set @.x = '<v>3</v>'
> declare @.q varchar(255)
> set @.q = '(/v)[1]'
> select @.x.value('sql:variable("@.q")','int')
> go
> assumes that that query in @.q is meant to be a literal value so it
> doens't get eval'ed as a query.
> Cheers,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
|||Hi Bob,
> That's a shame really - the power of XPath is not fully realised
> there. I think I'll try writing the whole stored procedure
> dynamically.
I don't know if I'd agree with that. I would say that the team has done the
best they could with limited resources, variable targets and a slower than
expected standards process. Could they have done more? Sure -- if they had
more people and time. They didn't. So I look at as glass half full. Afterall,
we're much futher along the XQuery path than the client (.NET folks are)
period.
And as I look it, I think we're only going to get limited improvements in
this support over time as Microsoft seems much more interested in putting
their resources into LINQ instead of XQuery. I'm okay with that because it
solves the problem (at least eventually), but part of me is sad to seem go
this way instead of fully investing in both.
Time will tell...
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||It is a shame - but what we've got now is still a huge leap ahead of
what we had before. And it's, in general, a joy to work with.
Thanks again,
Bob
Kent Tegels wrote:
> Hi Bob,
>
> I don't know if I'd agree with that. I would say that the team has done
> the best they could with limited resources, variable targets and a
> slower than expected standards process. Could they have done more? Sure
> -- if they had more people and time. They didn't. So I look at as glass
> half full. Afterall, we're much futher along the XQuery path than the
> client (.NET folks are) period.
> And as I look it, I think we're only going to get limited improvements
> in this support over time as Microsoft seems much more interested in
> putting their resources into LINQ instead of XQuery. I'm okay with that
> because it solves the problem (at least eventually), but part of me is
> sad to seem go this way instead of fully investing in both.
> Time will tell...
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
|||Hi Bob
Thanks for the comment ;)
We are aware of the request to not having to write dynamic SQL to provide
the XPath expression as a string variable to the XQuery methods.
I would like to encourage you to either file (or if already filed vote) such
a request at
http://lab.msdn.microsoft.com/productfeedback/
On the server we are still investing in XQuery, but as Kent mentions, many
competing requests for the same resources will have to be influenced by you
the customer requesting us to help you improve.
Thanks
Michael
"Bob Turner" <b_turner@.hotmail.co.uk> wrote in message
news:DhCTf.77298$zk4.16164@.fe3.news.blueyonder.co. uk...[vbcol=seagreen]
> It is a shame - but what we've got now is still a huge leap ahead of what
> we had before. And it's, in general, a joy to work with.
> Thanks again,
> Bob
> Kent Tegels wrote:
Passing an Entire XQuery String as Variable
Hi,
Is there any way to formulate an entire XQuery (XPath) string as a
variable and then pass it to a XQuery e.g.
Use something like this:
@.Path nvarchar(4000)
set @.Path='//category[@.name="RTE"]//sku'
SELECT AllSKUs.value('./@.name','nvarchar(MAX)') AS SKU
FROM Hierarchy CROSS APPLY
HierarchyXML.nodes(@.Path) AS NewTable(AllSKUs)
To achieve something like this:
SELECT AllSKUs.value('./@.name','nvarchar(MAX)') AS SKU
FROM Hierarchy CROSS APPLY
HierarchyXML.nodes('//category[@.name="RTE"]//sku') AS NewTable(AllSKUs)
Cheers,
BobHello Bob,
No, not really because the XQuery parser is one-pass, so something like this
:
declare @.x xml
set @.x = '<v>3</v>'
declare @.q varchar(255)
set @.q = '(/v)[1]'
select @.x.value('sql:variable("@.q")','int')
go
assumes that that query in @.q is meant to be a literal value so it doens't
get eval'ed as a query.
Cheers,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Thanks Kent,
That's a shame really - the power of XPath is not fully realised there.
I think I'll try writing the whole stored procedure dynamically.
Bob
Kent Tegels wrote:
> Hello Bob,
> No, not really because the XQuery parser is one-pass, so something like
> this:
> declare @.x xml
> set @.x = '<v>3</v>'
> declare @.q varchar(255)
> set @.q = '(/v)[1]'
> select @.x.value('sql:variable("@.q")','int')
> go
> assumes that that query in @.q is meant to be a literal value so it
> doens't get eval'ed as a query.
> Cheers,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||Hi Bob,
> That's a shame really - the power of XPath is not fully realised
> there. I think I'll try writing the whole stored procedure
> dynamically.
I don't know if I'd agree with that. I would say that the team has done the
best they could with limited resources, variable targets and a slower than
expected standards process. Could they have done more? Sure -- if they had
more people and time. They didn't. So I look at as glass half full. Afterall
,
we're much futher along the XQuery path than the client (.NET folks are)
period.
And as I look it, I think we're only going to get limited improvements in
this support over time as Microsoft seems much more interested in putting
their resources into LINQ instead of XQuery. I'm okay with that because it
solves the problem (at least eventually), but part of me is
to seem go
this way instead of fully investing in both.
Time will tell...
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||It is a shame - but what we've got now is still a huge leap ahead of
what we had before. And it's, in general, a joy to work with.
Thanks again,
Bob
Kent Tegels wrote:
> Hi Bob,
>
>
> I don't know if I'd agree with that. I would say that the team has done
> the best they could with limited resources, variable targets and a
> slower than expected standards process. Could they have done more? Sure
> -- if they had more people and time. They didn't. So I look at as glass
> half full. Afterall, we're much futher along the XQuery path than the
> client (.NET folks are) period.
> And as I look it, I think we're only going to get limited improvements
> in this support over time as Microsoft seems much more interested in
> putting their resources into LINQ instead of XQuery. I'm okay with that
> because it solves the problem (at least eventually), but part of me is
> to seem go this way instead of fully investing in both.
> Time will tell...
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||Hi Bob
Thanks for the comment ;)
We are aware of the request to not having to write dynamic SQL to provide
the XPath expression as a string variable to the XQuery methods.
I would like to encourage you to either file (or if already filed vote) such
a request at
http://lab.msdn.microsoft.com/productfeedback/
On the server we are still investing in XQuery, but as Kent mentions, many
competing requests for the same resources will have to be influenced by you
the customer requesting us to help you improve.
Thanks
Michael
"Bob Turner" <b_turner@.hotmail.co.uk> wrote in message
news:DhCTf.77298$zk4.16164@.fe3.news.blueyonder.co.uk...
> It is a shame - but what we've got now is still a huge leap ahead of what
> we had before. And it's, in general, a joy to work with.
> Thanks again,
> Bob
> Kent Tegels wrote:
Is there any way to formulate an entire XQuery (XPath) string as a
variable and then pass it to a XQuery e.g.
Use something like this:
@.Path nvarchar(4000)
set @.Path='//category[@.name="RTE"]//sku'
SELECT AllSKUs.value('./@.name','nvarchar(MAX)') AS SKU
FROM Hierarchy CROSS APPLY
HierarchyXML.nodes(@.Path) AS NewTable(AllSKUs)
To achieve something like this:
SELECT AllSKUs.value('./@.name','nvarchar(MAX)') AS SKU
FROM Hierarchy CROSS APPLY
HierarchyXML.nodes('//category[@.name="RTE"]//sku') AS NewTable(AllSKUs)
Cheers,
BobHello Bob,
No, not really because the XQuery parser is one-pass, so something like this
:
declare @.x xml
set @.x = '<v>3</v>'
declare @.q varchar(255)
set @.q = '(/v)[1]'
select @.x.value('sql:variable("@.q")','int')
go
assumes that that query in @.q is meant to be a literal value so it doens't
get eval'ed as a query.
Cheers,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Thanks Kent,
That's a shame really - the power of XPath is not fully realised there.
I think I'll try writing the whole stored procedure dynamically.
Bob
Kent Tegels wrote:
> Hello Bob,
> No, not really because the XQuery parser is one-pass, so something like
> this:
> declare @.x xml
> set @.x = '<v>3</v>'
> declare @.q varchar(255)
> set @.q = '(/v)[1]'
> select @.x.value('sql:variable("@.q")','int')
> go
> assumes that that query in @.q is meant to be a literal value so it
> doens't get eval'ed as a query.
> Cheers,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||Hi Bob,
> That's a shame really - the power of XPath is not fully realised
> there. I think I'll try writing the whole stored procedure
> dynamically.
I don't know if I'd agree with that. I would say that the team has done the
best they could with limited resources, variable targets and a slower than
expected standards process. Could they have done more? Sure -- if they had
more people and time. They didn't. So I look at as glass half full. Afterall
,
we're much futher along the XQuery path than the client (.NET folks are)
period.
And as I look it, I think we're only going to get limited improvements in
this support over time as Microsoft seems much more interested in putting
their resources into LINQ instead of XQuery. I'm okay with that because it
solves the problem (at least eventually), but part of me is
to seem gothis way instead of fully investing in both.
Time will tell...
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||It is a shame - but what we've got now is still a huge leap ahead of
what we had before. And it's, in general, a joy to work with.
Thanks again,
Bob
Kent Tegels wrote:
> Hi Bob,
>
>
> I don't know if I'd agree with that. I would say that the team has done
> the best they could with limited resources, variable targets and a
> slower than expected standards process. Could they have done more? Sure
> -- if they had more people and time. They didn't. So I look at as glass
> half full. Afterall, we're much futher along the XQuery path than the
> client (.NET folks are) period.
> And as I look it, I think we're only going to get limited improvements
> in this support over time as Microsoft seems much more interested in
> putting their resources into LINQ instead of XQuery. I'm okay with that
> because it solves the problem (at least eventually), but part of me is
>
to seem go this way instead of fully investing in both.> Time will tell...
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||Hi Bob
Thanks for the comment ;)
We are aware of the request to not having to write dynamic SQL to provide
the XPath expression as a string variable to the XQuery methods.
I would like to encourage you to either file (or if already filed vote) such
a request at
http://lab.msdn.microsoft.com/productfeedback/
On the server we are still investing in XQuery, but as Kent mentions, many
competing requests for the same resources will have to be influenced by you
the customer requesting us to help you improve.
Thanks
Michael
"Bob Turner" <b_turner@.hotmail.co.uk> wrote in message
news:DhCTf.77298$zk4.16164@.fe3.news.blueyonder.co.uk...
> It is a shame - but what we've got now is still a huge leap ahead of what
> we had before. And it's, in general, a joy to work with.
> Thanks again,
> Bob
> Kent Tegels wrote:
Subscribe to:
Posts (Atom)