help getting parameters back from a stored procedure

Hello everyone,

I've been trying to use a stored procedure to return the names of some temporary tables that i put in the tempdb table in SQL Server.

--I've been getting the following error in visual basic 6 when i try to call this:
run-time error '-2147217900 (80040e14)': syntax access violation

--This is the error you get when you try to just run the code in query analyzer:
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation

--what code i was trying to use (in query analyzer):
{call EXEC CreateTempTables (@.RQSodfil = 'a', @.BulkRan = 'a', @.BulkFor = 'a', @.BulkJit = 'a', @.ID = '0', @.RQSodfilFlag = '1', @.BulkRanFlag = '0', @.BulkForFlag = '0', @.BulkJitFlag = '0', @.DeleteFlag = '0', @.ErrorNum = '0')}

--code that i was trying to use in vb 6:
Public Sub TemporaryTables( _
ByVal bytRQSodfilFlag As Byte, _
ByVal bytBulkRanFlag As Byte, _
ByVal bytBulkForFlag As Byte, _
ByVal bytBulkJitFlag As Byte, _
ByVal bytDeleteFlag As Byte, _
ByVal cnPlant As String)

Dim objConn As ADODB.Connection
Dim objCmd As ADODB.Command
Dim objRQSodfil As Parameter
Dim objBulkRan As Parameter
Dim objBulkFor As Parameter
Dim objBulkJit As Parameter
Dim objParamID As Parameter
Dim objRQSodfilFlag As Parameter
Dim objBulkRanFlag As Parameter
Dim objBulkForFlag As Parameter
Dim objBulkJitFlag As Parameter
Dim objDeleteFlag As Parameter
Dim objErrorNum As Parameter
Dim intErrorNum As Integer

' setup command variable
Set objCmd = New ADODB.Command
Set objConn = New ADODB.Connection
objConn.Open cnPlant
objCmd.CommandText = "EXEC CreateTempTables"
objCmd.CommandType = adCmdStoredProc
objCmd.ActiveConnection = objConn

' setup parameters
Set objRQSodfil = objCmd.CreateParameter("@.RQSodfil", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objRQSodfil
Set objBulkRan = objCmd.CreateParameter("@.BulkRan", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objBulkRan
Set objBulkFor = objCmd.CreateParameter("@.BulkFor", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objBulkFor
Set objBulkJit = objCmd.CreateParameter("@.BulkJit", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objBulkJit
Set objParamID = objCmd.CreateParameter("@.ID", adChar, adParamInputOutput, 2, 0)
objCmd.Parameters.Append objParamID
Set objRQSodfilFlag = objCmd.CreateParameter("@.RQSodfilFlag", adTinyInt, adParamInput, , bytRQSodfilFlag)
objCmd.Parameters.Append objRQSodfilFlag
Set objBulkRanFlag = objCmd.CreateParameter("@.BulkRanFlag", adTinyInt, adParamInput, , bytBulkRanFlag)
objCmd.Parameters.Append objBulkRanFlag
Set objBulkForFlag = objCmd.CreateParameter("@.BulkForFlag", adTinyInt, adParamInput, , bytBulkForFlag)
objCmd.Parameters.Append objBulkForFlag
Set objBulkJitFlag = objCmd.CreateParameter("@.BulkJitFlag", adTinyInt, adParamInput, , bytBulkJitFlag)
objCmd.Parameters.Append objBulkJitFlag
Set objDeleteFlag = objCmd.CreateParameter("@.DeleteFlag", adTinyInt, adParamInput, , bytDeleteFlag)
objCmd.Parameters.Append objDeleteFlag
Set objErrorNum = objCmd.CreateParameter("@.ErrorNum", adInteger, adParamInputOutput, , 0)
objCmd.Parameters.Append objErrorNum

' execute command
Set rsTableInfo = objCmd.Execute(, , adExecuteRecord)

' find returned parameters
gstrRQSodfilName = rsTableInfo.Fields("@.RQSodfil")
gstrBulkRanName = rsTableInfo.Fields("@.BulkRan")
gstrBulkForName = rsTableInfo.Fields("@.BulkFor")
gstrBulkJitName = rsTableInfo.Fields("@.BulkJit")
gstrID = rsTableInfo.Fields("@.ID")
intErrorNum = rsTableInfo.Fields("@.ErrorNum")

End Sub

any help would be appreciatedactually i just solved my own problem:

at the end of the stored procedure i selected the columns i wanted to return and that did it.

select @.RQSodfil,@.BulkRan,@.BulkFor,@.BulkJit,@.ID,@.ErrorNum

i also just did a regular sql statement where i called the EXEC command to run my stored procedure.

EXEC CreateTempTables @.RQSodfil = 'a', @.BulkRan = 'a', @.BulkFor = 'a', @.BulkJit = 'a', @.ID = '0', @.RQSodfilFlag = '1', @.BulkRanFlag = '0', @.BulkForFlag = '0', @.BulkJitFlag = '0', @.DeleteFlag = '0', @.ErrorNum = '0'

hopefully if someone else has the same problem i've had they can see what i did.

Help Fix Slow Query.

I have a query that is taking too long to run. It take 14 seconds to return 6800 rows. However, if I move the query out of a stored proc, it takes 1 second. I want to understand this issue and ideally fix the stored proc case.

I've simplified my actual queries for readability.

-- @.filter is value to filter against or NULL to return all records.
CREATE PROCEDURE queryPlayerStations(@.filter INTEGER)
AS
SELECT * FROM MyTable
-- Other joins and query logic omitted for brevity
WHERE ((@.filter IS NULL) OR (MyTable.Column = @.filter))
GO

DECLARE @.filter INTEGER
SET @.filter = NULL

-- Takes 14 seconds to return 6800 rows. That's unacceptable performance
EXEC dbo.queryPlayerStations @.filter

When I run the query directly in Query Analyzer, it runs very fast.

DECLARE @.filter INTEGER
SET @.filter = NULL

-- Takes ~1 second to return 6800 rows. That's great performance
SELECT * FROM MyTable
-- Other joins and query logic omitted for brevity
WHERE ((@.filter IS NULL) OR (MyTable.Column = @.filter))

When I put the parameters in the stored proc it runs fast.

CREATE PROCEDURE queryPlayerStations
AS
DECLARE @.filter INTEGER
SET @.filter = NULL

SELECT * FROM MyTable
-- Other joins and query logic omitted for brevity
WHERE ((@.filter IS NULL) OR (MyTable.Column = @.filter))
GO

-- Takes ~1 second to return 6800 rows. That's great performance
EXEC dbo.queryPlayerStations

Anyone have any ideas what I can do to improve the stored proc case?Just a quick *guess* before I leave office for tonight...

The optimization in SQL Server works differently depending
on where the parameter is defined (as a procedure call argument or inside using DECLARE). In one of the cases,
it doesn't have enough info to optimize in the best way.|||As Coolberg implied, what happens if you do this:

ALTER PROCEDURE queryPlayerStations(@.filterIN INTEGER)
AS
DECLARE @.filter INTEGER
SET @.filter = @.filterIN

SELECT * FROM MyTable
-- Other joins and query logic omitted for brevity
WHERE ((@.filter IS NULL) OR (MyTable.Column = @.filter))
GO

DECLARE @.filterIN INTEGER
SET @.filterIN = NULL
EXEC dbo.queryPlayerStations @.filterIN

Help expanding a query .. group By

Im using vb code to generate a SQL query to return a recordset. The follwing
query returns 2 records as follows.
Select a.BHYEAR_MOVEDATE,
Case b.BTYear_TransCode
WHEN '17' THEN 'DD'
WHEN '01' THEN 'DD'
WHEN '18' THEN 'DD'
Else 'AUD' END AS 'TYPE'
from dbo.BacsHdrYearly as a
LEFT JOIN dbo.BacsTrnYear as b
on a.BHYear_LedgerKey = b.BTYear_LedgerKey
Where (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '128')
OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '134')
OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '135')
OR (a.BHYEAR_LICENCE = '217001' AND a.BHYEAR_SERIALNUMBER = '136')
GROUP BY a.BHYEAR_MOVEDATE , b.BTYear_TransCode
BHYEAR_MOVEDATE TYPE
--- --
2005-04-21 00:00:00 DD
2005-04-21 00:00:00 DD
I need it to only return one record as for each date and type ( may be
multiple dates and types ).
BHYEAR_MOVEDATE TYPE
--- --
2005-04-21 00:00:00 DD
any suggestions on how to rephrase this query ?What ABout DISTINCT ?

> Select DISTINCT a.BHYEAR_MOVEDATE,
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Peter Newman" <PeterNewman@.discussions.microsoft.com> schrieb im
Newsbeitrag news:5AEEE5BB-67B2-4DEC-A918-F6C32E79312C@.microsoft.com...
> Im using vb code to generate a SQL query to return a recordset. The
> follwing
> query returns 2 records as follows.
> Select a.BHYEAR_MOVEDATE,
> Case b.BTYear_TransCode
> WHEN '17' THEN 'DD'
> WHEN '01' THEN 'DD'
> WHEN '18' THEN 'DD'
> Else 'AUD' END AS 'TYPE'
> from dbo.BacsHdrYearly as a
> LEFT JOIN dbo.BacsTrnYear as b
> on a.BHYear_LedgerKey = b.BTYear_LedgerKey
> Where (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '128')
> OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '134')
> OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '135')
> OR (a.BHYEAR_LICENCE = '217001' AND a.BHYEAR_SERIALNUMBER = '136')
> GROUP BY a.BHYEAR_MOVEDATE , b.BTYear_TransCode
>
> BHYEAR_MOVEDATE TYPE
> --- --
> 2005-04-21 00:00:00 DD
> 2005-04-21 00:00:00 DD
>
> I need it to only return one record as for each date and type ( may be
> multiple dates and types ).
> BHYEAR_MOVEDATE TYPE
> --- --
> 2005-04-21 00:00:00 DD
> any suggestions on how to rephrase this query ?|||or
GROUP BY clause
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OoaGSMNRFHA.204@.TK2MSFTNGP15.phx.gbl...
> What ABout DISTINCT ?
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Peter Newman" <PeterNewman@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:5AEEE5BB-67B2-4DEC-A918-F6C32E79312C@.microsoft.com...
>|||Of couse ;-)
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:e$TxrUNRFHA.3076@.TK2MSFTNGP14.phx.gbl...
> or
> GROUP BY clause
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:OoaGSMNRFHA.204@.TK2MSFTNGP15.phx.gbl...
>|||Peter,
Your statement is correct, except that in the GROUP BY clause you have to us
e:
...
GROUP BY
a.BHYEAR_MOVEDATE,
Case b.BTYear_TransCode
WHEN '17' THEN 'DD'
WHEN '01' THEN 'DD'
WHEN '18' THEN 'DD'
Else 'AUD' END;
AMB
"Peter Newman" wrote:

> Im using vb code to generate a SQL query to return a recordset. The follwi
ng
> query returns 2 records as follows.
> Select a.BHYEAR_MOVEDATE,
> Case b.BTYear_TransCode
> WHEN '17' THEN 'DD'
> WHEN '01' THEN 'DD'
> WHEN '18' THEN 'DD'
> Else 'AUD' END AS 'TYPE'
> from dbo.BacsHdrYearly as a
> LEFT JOIN dbo.BacsTrnYear as b
> on a.BHYear_LedgerKey = b.BTYear_LedgerKey
> Where (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '128')
> OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '134')
> OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '135')
> OR (a.BHYEAR_LICENCE = '217001' AND a.BHYEAR_SERIALNUMBER = '136')
> GROUP BY a.BHYEAR_MOVEDATE , b.BTYear_TransCode
>
> BHYEAR_MOVEDATE TYPE
> --- --
> 2005-04-21 00:00:00 DD
> 2005-04-21 00:00:00 DD
>
> I need it to only return one record as for each date and type ( may be
> multiple dates and types ).
> BHYEAR_MOVEDATE TYPE
> --- --
> 2005-04-21 00:00:00 DD
> any suggestions on how to rephrase this query ?sql

Help creating a SELECT statement for "today"

Hello,

I am attempting to build a MS SQL query that will return data from
"today"; today being current day 8:00AM-10:00PM today. My goal is to
return the data from a table that is written to throughout the day, the
query will provide the current grade of service in our call center.

I am having difficulty defining my where clause:

- I can accomplish my goal my statically defining my 'date between' as
the actual date and time (not ideal)

- I can accomplish the second part of my date using CURRENT_TIMESTAMP;
but I am unable to define the starting point

Here is where I am thus far:

/* We are going to count the total calls into each queue from start of
business today (8:00AM) to now */

select COUNT(Result) as "Total Sales Calls Offered" from
dbo.QueueEncounter
where Direction='0'
and
QueueID='1631'
and
/* This is where I get lost */
Time between DATEPART(day, GETDATE()) and DATEPART(day, GETDATE())

Clearly the last line returns zero as there are no calls between the
same date range. How can I add to that line, or write this to work?

Any thoughts?

Thanks for the help.

-ChrisOn 25 Jan 2006 10:03:53 -0800, Chris wrote:

>Hello,
>I am attempting to build a MS SQL query that will return data from
>"today"; today being current day 8:00AM-10:00PM today. My goal is to
>return the data from a table that is written to throughout the day, the
>query will provide the current grade of service in our call center.
>I am having difficulty defining my where clause:
>- I can accomplish my goal my statically defining my 'date between' as
>the actual date and time (not ideal)
>- I can accomplish the second part of my date using CURRENT_TIMESTAMP;
>but I am unable to define the starting point
>Here is where I am thus far:
>/* We are going to count the total calls into each queue from start of
>business today (8:00AM) to now */
>select COUNT(Result) as "Total Sales Calls Offered" from
>dbo.QueueEncounter
>where Direction='0'
>and
>QueueID='1631'
>and
>/* This is where I get lost */
>Time between DATEPART(day, GETDATE()) and DATEPART(day, GETDATE())
>Clearly the last line returns zero as there are no calls between the
>same date range. How can I add to that line, or write this to work?
>Any thoughts?
>Thanks for the help.
>-Chris

Hi Chris,

You say you want rows for today, 8:00AM-10:00PM. Does this imply that
the table also contains rows outside the 8:00AM-10:00PM time frame that
you don't want to include?

AND Time BETWEEN DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP),
'8:00AM')
AND DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP),
'10:00PM')

Note that this will include a row with time exactly equal to 10 PM, but
exclude a row with time 3 milliseconds after 10PM.

If you want all rows for the whole day (0:00 - 24:00), use
AND Time >= DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
AND Time < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 1)

--
Hugo Kornelis, SQL Server MVP|||Awesome Hugo, thanks so much for the help - My query is now nearly
complete; with one last problem....

declare @.today datetime,
@.tomorrow datetime,
@.offered smallint,
@.answeredin120 smallint,
@.GOS smallint

set @.today = convert(char(8), GETDATE ( ), 112)
set @.tomorrow = @.today + 1

-- Find total calls offered
set @.offered = (select COUNT(Result) from dbo.QueueEncounter
where Direction='0' and QueueID='1438' and Time >= @.today and Time <
@.tomorrow)

-- Find total calls answered in 120 seconds
set @.answeredin120 =(select COUNT(Result) from dbo.QueueEncounter
where Direction='0' and QueueID='1438' and Time >= @.today and Time <
@.tomorrow and WaitTime <= 120)

-- Divide the total calls offered by the total calls answered in X
multiplied by 100 to find current GOS ??

set @.GOS = (@.offered)/(@.answeredin120)*100

select @.GOS

The problem is my GOS is being returned as 100 when it is really apprx
77%.
Where did I go wrong?

-Thanks!|||On 25 Jan 2006 14:12:48 -0800, Chris wrote:

(snip)
>-- Divide the total calls offered by the total calls answered in X
>multiplied by 100 to find current GOS ??
>set @.GOS = (@.offered)/(@.answeredin120)*100
>select @.GOS
>The problem is my GOS is being returned as 100 when it is really apprx
>77%.
>Where did I go wrong?

Hi Chris,

Integer division: divide two integers, the result is integer too.

SELECT 1/3
SELECT 1.0/3
SELECT 1/3.0
SELECT 1.0/3.0

The above show that forcing at least one operand to non-integer suffices
to get a result with fraction. In your case, one possible way would be

SET @.GOS = CAST(@.offered AS decimal(10,2)) / @.answeredin120 * 100

Or even

SET @.GOS = 100.0 * @.offerec / @.answeredin120

--
Hugo Kornelis, SQL Server MVP|||Hugo, Again many thanks... I will try this at the office tomorrow.

Cheers.

Help :Custom Date Time Format

Dear All,
How to return current time in format as below:
YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
Thanks
Robert LieBest thing would be to write a UDF and put in some standardformat to get the
special format you need.
HTH, Jens Suessmeyer.
"Robert Lie" <robert.lie24@.gmail.com> schrieb im Newsbeitrag
news:O9BRSXJeFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> How to return current time in format as below:
> YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
> Thanks
> Robert Lie|||Hi,
Use this format
select Convert(Varchar,getdate(),120)
Hope this will help
Herbert
"Robert Lie" wrote:

> Dear All,
> How to return current time in format as below:
> YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
> Thanks
> Robert Lie
>|||To add to the other responses, consider performing data formatting in your
presentation layer rather than Transact-SQL. SQL Server is optimized for
efficient data access and application code/reporting tools generally provide
more robust and efficient formatting capabilities.
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:O9BRSXJeFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> How to return current time in format as below:
> YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
> Thanks
> Robert Lie

Help :Custom Date Time Format

Dear All,
How to return current time in format as below:
YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
Thanks
Robert Lie
Best thing would be to write a UDF and put in some standardformat to get the
special format you need.
HTH, Jens Suessmeyer.
"Robert Lie" <robert.lie24@.gmail.com> schrieb im Newsbeitrag
news:O9BRSXJeFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> How to return current time in format as below:
> YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
> Thanks
> Robert Lie
|||Hi,
Use this format
select Convert(Varchar,getdate(),120)
Hope this will help
Herbert
"Robert Lie" wrote:

> Dear All,
> How to return current time in format as below:
> YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
> Thanks
> Robert Lie
>
|||To add to the other responses, consider performing data formatting in your
presentation layer rather than Transact-SQL. SQL Server is optimized for
efficient data access and application code/reporting tools generally provide
more robust and efficient formatting capabilities.
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:O9BRSXJeFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> How to return current time in format as below:
> YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
> Thanks
> Robert Lie

Help :Custom Date Time Format

Dear All,
How to return current time in format as below:
YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
Thanks
Robert LieBest thing would be to write a UDF and put in some standardformat to get the
special format you need.
HTH, Jens Suessmeyer.
"Robert Lie" <robert.lie24@.gmail.com> schrieb im Newsbeitrag
news:O9BRSXJeFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> How to return current time in format as below:
> YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
> Thanks
> Robert Lie|||Hi,
Use this format
select Convert(Varchar,getdate(),120)
Hope this will help
Herbert
"Robert Lie" wrote:
> Dear All,
> How to return current time in format as below:
> YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
> Thanks
> Robert Lie
>|||To add to the other responses, consider performing data formatting in your
presentation layer rather than Transact-SQL. SQL Server is optimized for
efficient data access and application code/reporting tools generally provide
more robust and efficient formatting capabilities.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:O9BRSXJeFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> How to return current time in format as below:
> YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
> Thanks
> Robert Lie

Help : Dynamic table generation

Hi Every one,
Warm wishes
I have to create table dynimically using a query on second table and that query will return me folloing.

1. Table name.
2. Primary key.
3. columns Name And their data types.

So if any one had implemented such code I would definately appriciate their participation.
Thanks in advance
This can be easily implement using Dynamic SQL exec() or sp_executesql

Why don't you give it a try first and if you encounter any problem, just post it here.|||

Query INFORMATION_SCHEMA.[COLUMNS]. That will give you all you need.

HELP ! Database Restore version problem

I have a database that was backed up from SQL 7, restored onto SQL 2K - and now need to return it to SQL 7.

I have attempted this and get:

Error 3169: The backed-up database has on-disk structure version 539. The server supports version 515 and cannot restore or upgrade this database.

If anyone could let me know either way please do !

DonaldRE: I have a database that was backed up from SQL 7, restored onto SQL 2K - and now need to return it to SQL 7. I have attempted this and get: Error 3169: The backed-up database has on-disk structure version 539. The server supports version 515 and cannot restore or upgrade this database. If anyone could let me know either way please do !
Donald

Q1 [Can Sql Server 2k backup dumps be restored to Sql Server pre 2k versions]?
A1 No.

Q2 [How may one get a Sql Server 2k DB onto Sql Server pre2k]?
A2 Use DTS, or generate ddl sql scripts and then BCP data into your DB.

Help - Tasks that have circular dependencies

Hello,

I have a package, which calls a sub package to poulate a table depending on a flag in the database (using an ExecuteSQL task to return flagged table name).

The inner package populates some tables, and calculates what needs to be processed next. It sets the next flag.

However, I can't make this work in the control flow, as once the Execute package has completed, I need to start again from the top, as the flag will have changed to the next item.

I hope that I have explained this well enough.

I really need this to work, but SSIS will not let me create a circular dependency. Does anyone know a way around this, or can offer me an alternative solution?

I am getting desperate, so any suggestions will be welcome

Many thanks

The Foreach Loop Container may be of use. have you evaluated that?

Regards

-Jamie

|||

Hello Jamie,

Thanks for the reply.

Yes, I have, but as I have to pass it a recordset at the start of the for each.. it doesn't seem to fit the bill. I have to pick up these tables in an order only defined at run time by the sub package.

|||The Foreach container is exactly what you need to use. You will need to store your recordset in a user variable using an ExecuteSQL task and then use the Foreach container with a Foreach ADO enumerator. More direction is available on BOL but that should be enough to get you started.|||

Oh dear,

I am not explaining this very well, I;m sorry.

On the first pass I will get a table name from ny ontrol table which beeds to be populated. Once this is populated, I will mark the parent tables next to be processed in my control table.

So on the second iteration, I need to retrieve a new recordset to pass to the For Each loop. And so on.

Sorry if I am being obtuse, but I can't see how I can make this work.

Help - Select with newid()

While trying to get a list from joined tables that return 2 GUIDs, I ran
into an interesting gotcha. I'm trying to get 1 guid that's unique for all
the rows returned, and 1 guid that is unique only for each of the child
rows. I _thought_ that could be accomplished using a derived table.
To illustrate, here's a query of the pubs db.
select stores.stor_name, newid() as TitleStoreGUID, T.title, T.TitleGUID
from stores
join sales on stores.stor_id = sales.stor_id
join ( select title_id, title, newid() as TitleGUID from titles ) T on
sales.title_id = T.title_id
I would hope this would return something like:
StoreA 10 TitleA 100
StoreB 11 TitleA 100
StoreB 12 TitleB 101
StoreC 13 TitleA 100
StoreC 14 TitleC 102
etc...
Instead, both of the GUIDs are unique for each row. In looking at the
execution plan, it appears that the NewId() functions are being resolved at
the parent query.
Is there any way to have the NewId() function within the derived table stay
static? Or, any other ideas on how to generate the results would be
appreciated.
- RickThis works exactly as how it should work. The newid() as TitleStoreGUID
applies to each row in the resultset generated by the join. The newid() as
TitleGUID applies to each row in the resultset generated by titles. So, by
design each call to newid() should generate an unique id. I would be very
troubled if they generate a same id.
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"RickT" <rick@.npspamplease.kqrs.com> wrote in message
news:OP8GwqSiDHA.2504@.TK2MSFTNGP09.phx.gbl...
> While trying to get a list from joined tables that return 2 GUIDs, I ran
> into an interesting gotcha. I'm trying to get 1 guid that's unique for
all
> the rows returned, and 1 guid that is unique only for each of the child
> rows. I _thought_ that could be accomplished using a derived table.
> To illustrate, here's a query of the pubs db.
> select stores.stor_name, newid() as TitleStoreGUID, T.title, T.TitleGUID
> from stores
> join sales on stores.stor_id = sales.stor_id
> join ( select title_id, title, newid() as TitleGUID from titles ) T on
> sales.title_id = T.title_id
> I would hope this would return something like:
> StoreA 10 TitleA 100
> StoreB 11 TitleA 100
> StoreB 12 TitleB 101
> StoreC 13 TitleA 100
> StoreC 14 TitleC 102
> etc...
> Instead, both of the GUIDs are unique for each row. In looking at the
> execution plan, it appears that the NewId() functions are being resolved
at
> the parent query.
> Is there any way to have the NewId() function within the derived table
stay
> static? Or, any other ideas on how to generate the results would be
> appreciated.
> - Rick
>|||Well, oj, it may work exactly as how it should. But I'm not sure this is
precisely documented or widely known.
There is no question about the uniqueness of TitleStoreGUID. The question is
why TitleGUID is unique even for the same title.
If you look at the query plan, it definitely supports your explanation
because newid() as TutleGUID is indeed evaluated at the outer most layer in
the final resultset. Therefore, we see the uniqueness of its value for each
row, given the nature of newid().
However, I'm struggling to find where in the BOL or in the SQL specs does it
say that we shouldn't expect the subquery to be evaluated first and that the
subsequent evaluation of the query uses the cached resultset. In other
words, why can't it be evaluated as if the derived table is a real tmp table
or a table variable as something similar to the following:
declare @.tmp table(title_id varchar(6), title varchar(80), TitleGUID
uniqueidentifier)
insert into @.tmp
select title_id, title, newid() as TitleGUID from titles
select stores.stor_name, newid() as TitleStoreGUID, T.title, T.TitleGUID
from stores
join sales on stores.stor_id = sales.stor_id
join @.tmp T on
sales.title_id = T.title_id
order by title
Maybe this type of subquery is always considered a correlated subquery, and
therefore all this confusion would go away :-)
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"oj" <nospam_ojngo@.home.com> wrote in message
news:OwXAFvUiDHA.272@.tk2msftngp13.phx.gbl...
> This works exactly as how it should work. The newid() as TitleStoreGUID
> applies to each row in the resultset generated by the join. The newid() as
> TitleGUID applies to each row in the resultset generated by titles. So, by
> design each call to newid() should generate an unique id. I would be very
> troubled if they generate a same id.
> --
> -oj
> RAC v2.2 & QALite!
> http://www.rac4sql.net
>
> "RickT" <rick@.npspamplease.kqrs.com> wrote in message
> news:OP8GwqSiDHA.2504@.TK2MSFTNGP09.phx.gbl...
> > While trying to get a list from joined tables that return 2 GUIDs, I ran
> > into an interesting gotcha. I'm trying to get 1 guid that's unique for
> all
> > the rows returned, and 1 guid that is unique only for each of the child
> > rows. I _thought_ that could be accomplished using a derived table.
> >
> > To illustrate, here's a query of the pubs db.
> > select stores.stor_name, newid() as TitleStoreGUID, T.title,
T.TitleGUID
> > from stores
> > join sales on stores.stor_id = sales.stor_id
> > join ( select title_id, title, newid() as TitleGUID from titles ) T
on
> > sales.title_id = T.title_id
> >
> > I would hope this would return something like:
> > StoreA 10 TitleA 100
> > StoreB 11 TitleA 100
> > StoreB 12 TitleB 101
> > StoreC 13 TitleA 100
> > StoreC 14 TitleC 102
> > etc...
> >
> > Instead, both of the GUIDs are unique for each row. In looking at the
> > execution plan, it appears that the NewId() functions are being resolved
> at
> > the parent query.
> >
> > Is there any way to have the NewId() function within the derived table
> stay
> > static? Or, any other ideas on how to generate the results would be
> > appreciated.
> >
> > - Rick
> >
> >
>|||<< Maybe this type of subquery is always considered a correlated subquery,
and therefore all this confusion would go away :-) >>
I've always worked under the belief that what made a _correlated_ subquery
was a reference to an outer query object from within the inner query, thus
causing the behavior of re-evaluation of the inner query for each row of the
outer query.
The #tmp table does work. I could toss the statements:
select title_id, title, newid() as TitleGUID into #Tmp from titles
select stores.stor_name, newid() as TitleStoreGUID, T.title, T.TitleGUID
from stores
join sales on stores.stor_id = sales.stor_id
join #Tmp T on sales.title_id = T.title_id
into a stored procedure. I will if I have to, though I'm hoping the greater
minds in this group might be able to help me with a better way.
TIA,
- Rick
BTW - OJ, your utilities look awesome!
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:ujiw6xViDHA.2516@.TK2MSFTNGP09.phx.gbl...
> Well, oj, it may work exactly as how it should. But I'm not sure this is
> precisely documented or widely known.
> There is no question about the uniqueness of TitleStoreGUID. The question
is
> why TitleGUID is unique even for the same title.
> If you look at the query plan, it definitely supports your explanation
> because newid() as TutleGUID is indeed evaluated at the outer most layer
in
> the final resultset. Therefore, we see the uniqueness of its value for
each
> row, given the nature of newid().
> However, I'm struggling to find where in the BOL or in the SQL specs does
it
> say that we shouldn't expect the subquery to be evaluated first and that
the
> subsequent evaluation of the query uses the cached resultset. In other
> words, why can't it be evaluated as if the derived table is a real tmp
table
> or a table variable as something similar to the following:
> declare @.tmp table(title_id varchar(6), title varchar(80), TitleGUID
> uniqueidentifier)
> insert into @.tmp
> select title_id, title, newid() as TitleGUID from titles
> select stores.stor_name, newid() as TitleStoreGUID, T.title, T.TitleGUID
> from stores
> join sales on stores.stor_id = sales.stor_id
> join @.tmp T on
> sales.title_id = T.title_id
> order by title
> Maybe this type of subquery is always considered a correlated subquery,
and
> therefore all this confusion would go away :-)
> --
> Linchi Shea
> linchi_shea@.NOSPAMml.com
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:OwXAFvUiDHA.272@.tk2msftngp13.phx.gbl...
> > This works exactly as how it should work. The newid() as TitleStoreGUID
> > applies to each row in the resultset generated by the join. The newid()
as
> > TitleGUID applies to each row in the resultset generated by titles. So,
by
> > design each call to newid() should generate an unique id. I would be
very
> > troubled if they generate a same id.
> >
> > --
> > -oj
> > RAC v2.2 & QALite!
> > http://www.rac4sql.net
> >
> >
> > "RickT" <rick@.npspamplease.kqrs.com> wrote in message
> > news:OP8GwqSiDHA.2504@.TK2MSFTNGP09.phx.gbl...
> > > While trying to get a list from joined tables that return 2 GUIDs, I
ran
> > > into an interesting gotcha. I'm trying to get 1 guid that's unique
for
> > all
> > > the rows returned, and 1 guid that is unique only for each of the
child
> > > rows. I _thought_ that could be accomplished using a derived table.
> > >
> > > To illustrate, here's a query of the pubs db.
> > > select stores.stor_name, newid() as TitleStoreGUID, T.title,
> T.TitleGUID
> > > from stores
> > > join sales on stores.stor_id = sales.stor_id
> > > join ( select title_id, title, newid() as TitleGUID from titles )
T
> on
> > > sales.title_id = T.title_id
> > >
> > > I would hope this would return something like:
> > > StoreA 10 TitleA 100
> > > StoreB 11 TitleA 100
> > > StoreB 12 TitleB 101
> > > StoreC 13 TitleA 100
> > > StoreC 14 TitleC 102
> > > etc...
> > >
> > > Instead, both of the GUIDs are unique for each row. In looking at the
> > > execution plan, it appears that the NewId() functions are being
resolved
> > at
> > > the parent query.
> > >
> > > Is there any way to have the NewId() function within the derived table
> > stay
> > > static? Or, any other ideas on how to generate the results would be
> > > appreciated.
> > >
> > > - Rick
> > >
> > >
> >
> >
>|||RickT;
My understanding is the same as yours.
I'm deliberately cross posting this to the sqlserver.programming group,
hoping that Joe Celko has time to straighten us out :-)
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"RickT" <rick@.npspamplease.kqrs.com> wrote in message
news:u1SmlAWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> << Maybe this type of subquery is always considered a correlated subquery,
> and therefore all this confusion would go away :-) >>
> I've always worked under the belief that what made a _correlated_ subquery
> was a reference to an outer query object from within the inner query, thus
> causing the behavior of re-evaluation of the inner query for each row of
the
> outer query.
> The #tmp table does work. I could toss the statements:
> select title_id, title, newid() as TitleGUID into #Tmp from titles
> select stores.stor_name, newid() as TitleStoreGUID, T.title,
T.TitleGUID
> from stores
> join sales on stores.stor_id = sales.stor_id
> join #Tmp T on sales.title_id = T.title_id
> into a stored procedure. I will if I have to, though I'm hoping the
greater
> minds in this group might be able to help me with a better way.
> TIA,
> - Rick
> BTW - OJ, your utilities look awesome!
> "Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
> news:ujiw6xViDHA.2516@.TK2MSFTNGP09.phx.gbl...
> > Well, oj, it may work exactly as how it should. But I'm not sure this is
> > precisely documented or widely known.
> >
> > There is no question about the uniqueness of TitleStoreGUID. The
question
> is
> > why TitleGUID is unique even for the same title.
> >
> > If you look at the query plan, it definitely supports your explanation
> > because newid() as TutleGUID is indeed evaluated at the outer most layer
> in
> > the final resultset. Therefore, we see the uniqueness of its value for
> each
> > row, given the nature of newid().
> >
> > However, I'm struggling to find where in the BOL or in the SQL specs
does
> it
> > say that we shouldn't expect the subquery to be evaluated first and that
> the
> > subsequent evaluation of the query uses the cached resultset. In other
> > words, why can't it be evaluated as if the derived table is a real tmp
> table
> > or a table variable as something similar to the following:
> >
> > declare @.tmp table(title_id varchar(6), title varchar(80), TitleGUID
> > uniqueidentifier)
> > insert into @.tmp
> > select title_id, title, newid() as TitleGUID from titles
> >
> > select stores.stor_name, newid() as TitleStoreGUID, T.title, T.TitleGUID
> > from stores
> > join sales on stores.stor_id = sales.stor_id
> > join @.tmp T on
> > sales.title_id = T.title_id
> > order by title
> >
> > Maybe this type of subquery is always considered a correlated subquery,
> and
> > therefore all this confusion would go away :-)
> >
> > --
> > Linchi Shea
> > linchi_shea@.NOSPAMml.com
> >
> >
> > "oj" <nospam_ojngo@.home.com> wrote in message
> > news:OwXAFvUiDHA.272@.tk2msftngp13.phx.gbl...
> > > This works exactly as how it should work. The newid() as
TitleStoreGUID
> > > applies to each row in the resultset generated by the join. The
newid()
> as
> > > TitleGUID applies to each row in the resultset generated by titles.
So,
> by
> > > design each call to newid() should generate an unique id. I would be
> very
> > > troubled if they generate a same id.
> > >
> > > --
> > > -oj
> > > RAC v2.2 & QALite!
> > > http://www.rac4sql.net
> > >
> > >
> > > "RickT" <rick@.npspamplease.kqrs.com> wrote in message
> > > news:OP8GwqSiDHA.2504@.TK2MSFTNGP09.phx.gbl...
> > > > While trying to get a list from joined tables that return 2 GUIDs, I
> ran
> > > > into an interesting gotcha. I'm trying to get 1 guid that's unique
> for
> > > all
> > > > the rows returned, and 1 guid that is unique only for each of the
> child
> > > > rows. I _thought_ that could be accomplished using a derived table.
> > > >
> > > > To illustrate, here's a query of the pubs db.
> > > > select stores.stor_name, newid() as TitleStoreGUID, T.title,
> > T.TitleGUID
> > > > from stores
> > > > join sales on stores.stor_id = sales.stor_id
> > > > join ( select title_id, title, newid() as TitleGUID from
titles )
> T
> > on
> > > > sales.title_id = T.title_id
> > > >
> > > > I would hope this would return something like:
> > > > StoreA 10 TitleA 100
> > > > StoreB 11 TitleA 100
> > > > StoreB 12 TitleB 101
> > > > StoreC 13 TitleA 100
> > > > StoreC 14 TitleC 102
> > > > etc...
> > > >
> > > > Instead, both of the GUIDs are unique for each row. In looking at
the
> > > > execution plan, it appears that the NewId() functions are being
> resolved
> > > at
> > > > the parent query.
> > > >
> > > > Is there any way to have the NewId() function within the derived
table
> > > stay
> > > > static? Or, any other ideas on how to generate the results would be
> > > > appreciated.
> > > >
> > > > - Rick
> > > >
> > > >
> > >
> > >
> >
> >
>

HELP - need a function like MID in access

I am looking for a function that behaves like the MID function in access. I need to return all values in a column that has a specific charater in a specific location within the string. Example: I need to return all rows that have the number 2 in the fourth position of column TEXT.

Quote:

Originally Posted by tingirl76

I am looking for a function that behaves like the MID function in access. I need to return all values in a column that has a specific charater in a specific location within the string. Example: I need to return all rows that have the number 2 in the fourth position of column TEXT.

Nevermind I got it...SUBSTRING(expression, startposition, length)....thank you anyway!!!

Help - Can Execute return a value?

Hello
I'm relatively new to SQL Server/T-SQL and find myself stuck with this
problem:
I need to do something like this:
Declare @.someValue NVarchar(100)
Declare @.someFunction Varchar(100)
--
-- Assign value to @.someValue from a Cursor
--
--
-- Assign name of the function to @.someFunction from a Cursor
--
Declare @.ret NVarchar(100)
Execute 'Select @.ret = ' + @.someFunc + '(''' + @.someValue + ''')'
I expect the last Execute statement to leave the return value from the
function in @.ret.
What I get is
Must declare the variable '@.ret'.
I have also tried
Execute sp_ExecuteSQL 'Select @.ret = ' + @.someFunc + '(''' +
@.someValue + ''')'
with the same result.
Any help with making this work or other ways of doing this will be very
much appreciated!
TIA.
Vamsi.Vamsi,
Use sp_executesql.
Declare @.ret NVarchar(100)
declare @.sql nvarchar(4000)
set @.sql = N'Select @.ret = ' + @.someFunc + '(''' + @.someValue + ''')'
exec sp_executesql @.sql, N'@.ret NVarchar(100) output', @.ret output
print @.ret
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB|||Splendid!!
I still can't understand that one line of code, but, it worked
perfectly :-)
Thanks very much, AMB.
V.|||tvamsidhar (tvamsidhar@.gmail.com) writes:
> Splendid!!
> I still can't understand that one line of code, but, it worked
> perfectly :-)
For more details on sp_executesql and dynamic SQL in general, see
an article on my web site: http://www.sommarskog.se/dynamic_sql.html.
By the way, if you are new to T-SQL, dynamic SQL is probably not where
you should start.
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|||Thanks Erland, I'll study the article! Its already in my Google
bookmarks :)
As for starting with dynamic SQL, I really don't have a choice :-) I
need to be able to run validations and formatting on type-less text
data that should be "interpreted" to be of datatypes defined in a
metadata repository (SQL Server tables) and satisfying validations
(reg. expressions, TSQL functions/SPs, etc) specified in the same
repository.
Running these on the app. server turned out to be way too inefficient
and cumbursome; hence the dynamic SQL. And although I'm more
experienced with PL/SQL, the powers that be insist on using SQL Server
I thank you for the input.
V.

help

Does anyone see anything wrong with this statement

CREATE FUNCTION "All Bally Games" ()
RETURNS TABLE
AS RETURN (SELECT TOP 100 PERCENT "Master List"."Tribal No", "Master List"."Stand No",
"Master List"."Serial No", "Master List".Manufacturers, "Master List".Description, "Master
List".Denomination, "Master List".Class, "Bally Main Eprom"."ID Number A", "Bally Main
Eprom"."ID Number B"
FROM "Master List" INNER JOIN "Bally Main Eprom" ON ("Master List"."Tribal No" = "Bally
Main Eprom"."Tribal No")
WHERE ((("Master List".Manufacturers) Like '5'))Unless you include a percent sign (%), the LIKE clause could just as well be an equal sign. I would much prefer the use of brackets [] over the use of quotes "" if that is acceptable.

That's all that jumps right out at me.

-PatP

Hello? Left Join?

Am I missing something? I thought LEFT JOIN made the first table return a value even if there was nothing in the second table. Yet this query:

SELECT b.name, isnull(c.call_no, 0)

FROM business b

LEFT JOIN call c ON b.business_id=c.service_business_id

WHERE b.business_id = 1000634

AND c.create_time BETWEEN @.startDate AND @.endDate

Is not returning any rows at all because there are no calls during the time period I'm using.

The query works if I extend the date range to include at least one call, so I know the business id and everything is correct...

Am I losing my mind? Or have I completely misunderstood what LEFT JOIN is supposed to do?

Hi Telos

You need to re-arrange your query slightly, see below.

Chris

SELECT b.name, isnull(c.call_no, 0)

FROM business b

LEFT JOIN call c ON b.business_id=c.service_business_id AND c.create_time BETWEEN @.startDate AND @.endDate

WHERE b.business_id = 1000634

|||

Ok, that works.

I don't understand why though... can I get an explanation?

|||

The reason it didn't work before is that in case of OUTER JOINS the WHERE clause is applied after the ON clause. If you have rows that doesn't satisfy the condition in the ON clause the column values will be NULL and the BETWEEN check will fail (or evaluate to unknown and hence the row(s) will not qualify).

But the way to write the query is to use a simple sub-query in the SELECT list. There is really no reason to use outer join. You need to use outer join construct if you are retrieving more columns from the outer joined table for example. Otherwise, a sub-select is the way to go.

SELECT b.name

, coalesce((select c.call_no

from call c

where b.business_id=c.service_business_id

and c.create_time BETWEEN @.startDate AND @.endDate), 0) as call_no

FROM business b

WHERE b.business_id = 1000634

|||

Ok, that makes sense.

The real query is a bit more complex though, so I was trying to avoid the subselect. The one Chris posted is working well.

Thanks for your help!

|||

The other responses are great. I just thought I would add that in your original query you could have put

AND ((c.create_time BETWEEN @.startDate AND @.endDate)

OR (c.create_time IS NULL)

)

That way your WHERE clause accommodates the fact that the OUTER JOIN may not return a value for c.create_time.

|||

The subtle difference between DanR1's approach and the others is that if the value stored in the table in the c.create_time column is NULL then the row will qualify and, therefore, will be returned. However if the c.create_time column is not nullable then this query will behave in the same way as the others.

Chris

|||

Chris,

Thanks for the elaboration.

Dan