Hi,
I'm writing an Access pass-through query against a SQL server backend and I need some advice on passing parameters. Currently I use vba to substitute the literal values for the parameters prior to passing the query to SQL Server. However, I am going through a loop thousands of times with different literals for these parameters which causes the server's cache to fill up. In Oracle, there is a way to use bind variables for the parameters so that only one copy of the query is cached.
Does anyone know how I can do this in SQL Server?
For instance, I have 20,000 employees and I'm pulling info by SS#:
Select * from EmpTable where SS_number = [SSN]
Is there a way I can pass this query to SQL Server and then pass the value of [SSN] as I loop through the dataset?
Thanks.write a stored procedure, and instead of calling the database engine 20,000 times, just call it once and pass it a list of 20,000 numbers
come to think of it, where would you get 20,000 numbers? sounds like you might want to look for a JOIN solution|||SQL Server actually goes you one better, in that its ODBC drivers will automagically parameterize a query for you (unless you get really creative in modifying the query).
As Rudy pointed out though, if you have more than 20 iterations from a given client, you really ought to be thinking about a JOIN based solution... Doing that kind of thing on that scale one row at a time is WAY too much work for me!
-PatP|||Thanks, guys. I'll get write access to the backend and write a stored proc.
I'll have to read up on how to pass values to the proc (I'm guessing it's like a function).
Thanks again.|||How about this?:
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open "DSN=PKRebate2001", "sa", ""
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cnn
.CommandText = "sp_UpdateCustomerUnique"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@.ImportMonth").Value = IM
.Execute
End With
Set cmd = Nothing
Set cnn = Nothing|||If you are looking to get automatic parameterization, that VBA code is conceptually good.
-PatP
Showing posts with label queries. Show all posts
Showing posts with label queries. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
Bind many tables from a single sp on many tables on a single repor
Hi,
i've a single stored procedure that return many tables from different select
queries. it is possible to bind on a single report all "recordset" on
different tables? in other words how i can "navigate" the source dataset, is
possible to refer to a kind of dataset index? ex. dsname[1], dsname[2] etc?RS does not support this. You either need multiple stored procedures or you
need to pass a parameter to the sp that says which recordset you want. Note
that either way you will have to call a stored procedure per dataset. It is
a one to one relationship.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"luspo" <luspo@.discussions.microsoft.com> wrote in message
news:CC7325EC-29A7-43F7-B857-4F0F0CFF1E67@.microsoft.com...
> Hi,
> i've a single stored procedure that return many tables from different
select
> queries. it is possible to bind on a single report all "recordset" on
> different tables? in other words how i can "navigate" the source dataset,
is
> possible to refer to a kind of dataset index? ex. dsname[1], dsname[2]
etc?
i've a single stored procedure that return many tables from different select
queries. it is possible to bind on a single report all "recordset" on
different tables? in other words how i can "navigate" the source dataset, is
possible to refer to a kind of dataset index? ex. dsname[1], dsname[2] etc?RS does not support this. You either need multiple stored procedures or you
need to pass a parameter to the sp that says which recordset you want. Note
that either way you will have to call a stored procedure per dataset. It is
a one to one relationship.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"luspo" <luspo@.discussions.microsoft.com> wrote in message
news:CC7325EC-29A7-43F7-B857-4F0F0CFF1E67@.microsoft.com...
> Hi,
> i've a single stored procedure that return many tables from different
select
> queries. it is possible to bind on a single report all "recordset" on
> different tables? in other words how i can "navigate" the source dataset,
is
> possible to refer to a kind of dataset index? ex. dsname[1], dsname[2]
etc?
Tuesday, March 20, 2012
Big queries return empty result set
Hello guys,
MS SQL server 2000 behavies strange with big queries that involves relatively large number of tables:
If I just enumerate columns I want to receive, the result set is empty. Adding * to the column list without making any change to where clause seems to solve the problem but I guess it's not the best practice.
The most amazing is that this behavior is not stable, so I suppose there's something to deal with server itself, not the application.
Has anybody suffered this problem and what solution was adopted?
Thanks for any information you can provide.Most of my queries are small (less than 200 columns and 500,000 rows), so maybe I'm not hitting the kind of volume that you are, but I've never heard of that behavior! I also rarely push beyond 30-50 tables in a single query with the exception of one purchased package that sometimes can create monsters.
Can you give me a rough idea of how big your database is (in gigabytes) and how big your result set is (rows, columns, and megabytes)? I'll try to reproduce the behavior here with a known good server.
-PatP|||Definitely my queries never get this volume. I have a join of about 15 tables with 10 columns in each. Empty recordset is an ocasional behavior, i mean today the query may work perfectly, tomorrow absolutely exact query with absolutely exact parameters without any modification made to the database fails.
I was told that this could be caused by particular, localized version of SQL server that we use (sql server 2000 SP3 spanish, 8.00.760). Could you confirm that?
Originally posted by Pat Phelan
Most of my queries are small (less than 200 columns and 500,000 rows), so maybe I'm not hitting the kind of volume that you are, but I've never heard of that behavior! I also rarely push beyond 30-50 tables in a single query with the exception of one purchased package that sometimes can create monsters.
Can you give me a rough idea of how big your database is (in gigabytes) and how big your result set is (rows, columns, and megabytes)? I'll try to reproduce the behavior here with a known good server.
-PatP|||Originally posted by Pat Phelan
Most of my queries are small (less than 200 columns and 500,000 rows), so maybe I'm not hitting the kind of volume that you are, but I've never heard of that behavior! I also rarely push beyond 30-50 tables in a single query with the exception of one purchased package that sometimes can create monsters.
:D
Why not POST the query...|||Originally posted by Brett Kaiser
:D
Why not POST the query...
As you wish:
--NON-EMPTY RESULTSET--
select bb.i_object_id as bb_aa, bb.i_object_type_id as bb_ab,
<skipped about 50 columns>
bl.i_operation_id as bl_ak, bl.integra_operation_id as bl_al,
* <-- this allows me receive data
from i_sysobject bb, part_list_item bc,
i_relation bd,
i_sysobject be, part_list bf,
i_relation bg, i_sysobject bh,
production_order bi, i_relation bj,
i_sysobject bk, operation bl
where bc.i_part_list_item_id = bb.i_object_id and
bf.state = ? and
bf.i_part_list_id = be.i_object_id and
bi.i_production_order_id = bh.i_object_id and
bl.i_operation_id = bk.i_object_id and
bc.i_part_list_item_id=bd.i_relation_child_object_ id and
bd.i_relation_parent_object_id=bf.i_part_list_id and
bf.i_part_list_id=bg.i_relation_child_object_id and
bg.i_relation_parent_object_id=bi.i_production_ord er_id and
bf.i_part_list_id=bj.i_relation_child_object_id and
bj.i_relation_parent_object_id=bl.i_operation_id
order by bf.i_part_list_id asc
--EMPTY RESULTSET--
select bb.i_object_id as bb_aa, bb.i_object_type_id as bb_ab,
<skipped about 50 columns>
bl.i_operation_id as bl_ak, bl.integra_operation_id as bl_al
from i_sysobject bb, part_list_item bc,
i_relation bd,
i_sysobject be, part_list bf,
i_relation bg, i_sysobject bh,
production_order bi, i_relation bj,
i_sysobject bk, operation bl
where bc.i_part_list_item_id = bb.i_object_id and
bf.state = ? and
bf.i_part_list_id = be.i_object_id and
bi.i_production_order_id = bh.i_object_id and
bl.i_operation_id = bk.i_object_id and
bc.i_part_list_item_id=bd.i_relation_child_object_ id and
bd.i_relation_parent_object_id=bf.i_part_list_id and
bf.i_part_list_id=bg.i_relation_child_object_id and
bg.i_relation_parent_object_id=bi.i_production_ord er_id and
bf.i_part_list_id=bj.i_relation_child_object_id and
bj.i_relation_parent_object_id=bl.i_operation_id
order by bf.i_part_list_id asc
bf.state = ? receives the SAME parameter in both cases.|||Everything is identity, isn't it...
Is one of these tables a driver? Like what you want to base your result set on?
Lots of sele referencing...
Maybe you can use derived tables..
SELECT * FROM (SELECT * FROM ...join the table that relate) AS A
LEFT JOIN (SELECT * FROM ..same thing) AS B
ON A.key = B.Key
Here's your code cleaned up some..Not sure if it's doing the same thing...
I think it is...and easier to see what you're trying to do
SELECT *
FROM i_sysobject bb
LEFT JOIN part_list_item bc ON bc.i_part_list_item_id = bb.i_object_id
LEFT JOIN i_relation bd ON bc.i_part_list_item_id=bd.i_relation_child_object_ id
LEFT JOIN i_sysobject be ON bf.i_part_list_id = be.i_object_id and
LEFT JOIN part_list bf ON bd.i_relation_parent_object_id=bf.i_part_list_id
LEFT JOIN i_relation bg ON bf.i_part_list_id=bg.i_relation_child_object_id
LEFT JOIN i_sysobject bh ON bi.i_production_order_id = bh.i_object_id
LEFT JOIN production_order bi ON bg.i_relation_parent_object_id=bi.i_production_ord er_id
LEFT JOIN i_relation bj ON bf.i_part_list_id=bj.i_relation_child_object_id
LEFT JOIN i_sysobject bk ON bl.i_operation_id = bk.i_object_id
LEFT JOIN operation bl ON bj.i_relation_parent_object_id=bl.i_operation_id
WHERE bf.state = ?
MS SQL server 2000 behavies strange with big queries that involves relatively large number of tables:
If I just enumerate columns I want to receive, the result set is empty. Adding * to the column list without making any change to where clause seems to solve the problem but I guess it's not the best practice.
The most amazing is that this behavior is not stable, so I suppose there's something to deal with server itself, not the application.
Has anybody suffered this problem and what solution was adopted?
Thanks for any information you can provide.Most of my queries are small (less than 200 columns and 500,000 rows), so maybe I'm not hitting the kind of volume that you are, but I've never heard of that behavior! I also rarely push beyond 30-50 tables in a single query with the exception of one purchased package that sometimes can create monsters.
Can you give me a rough idea of how big your database is (in gigabytes) and how big your result set is (rows, columns, and megabytes)? I'll try to reproduce the behavior here with a known good server.
-PatP|||Definitely my queries never get this volume. I have a join of about 15 tables with 10 columns in each. Empty recordset is an ocasional behavior, i mean today the query may work perfectly, tomorrow absolutely exact query with absolutely exact parameters without any modification made to the database fails.
I was told that this could be caused by particular, localized version of SQL server that we use (sql server 2000 SP3 spanish, 8.00.760). Could you confirm that?
Originally posted by Pat Phelan
Most of my queries are small (less than 200 columns and 500,000 rows), so maybe I'm not hitting the kind of volume that you are, but I've never heard of that behavior! I also rarely push beyond 30-50 tables in a single query with the exception of one purchased package that sometimes can create monsters.
Can you give me a rough idea of how big your database is (in gigabytes) and how big your result set is (rows, columns, and megabytes)? I'll try to reproduce the behavior here with a known good server.
-PatP|||Originally posted by Pat Phelan
Most of my queries are small (less than 200 columns and 500,000 rows), so maybe I'm not hitting the kind of volume that you are, but I've never heard of that behavior! I also rarely push beyond 30-50 tables in a single query with the exception of one purchased package that sometimes can create monsters.
:D
Why not POST the query...|||Originally posted by Brett Kaiser
:D
Why not POST the query...
As you wish:
--NON-EMPTY RESULTSET--
select bb.i_object_id as bb_aa, bb.i_object_type_id as bb_ab,
<skipped about 50 columns>
bl.i_operation_id as bl_ak, bl.integra_operation_id as bl_al,
* <-- this allows me receive data
from i_sysobject bb, part_list_item bc,
i_relation bd,
i_sysobject be, part_list bf,
i_relation bg, i_sysobject bh,
production_order bi, i_relation bj,
i_sysobject bk, operation bl
where bc.i_part_list_item_id = bb.i_object_id and
bf.state = ? and
bf.i_part_list_id = be.i_object_id and
bi.i_production_order_id = bh.i_object_id and
bl.i_operation_id = bk.i_object_id and
bc.i_part_list_item_id=bd.i_relation_child_object_ id and
bd.i_relation_parent_object_id=bf.i_part_list_id and
bf.i_part_list_id=bg.i_relation_child_object_id and
bg.i_relation_parent_object_id=bi.i_production_ord er_id and
bf.i_part_list_id=bj.i_relation_child_object_id and
bj.i_relation_parent_object_id=bl.i_operation_id
order by bf.i_part_list_id asc
--EMPTY RESULTSET--
select bb.i_object_id as bb_aa, bb.i_object_type_id as bb_ab,
<skipped about 50 columns>
bl.i_operation_id as bl_ak, bl.integra_operation_id as bl_al
from i_sysobject bb, part_list_item bc,
i_relation bd,
i_sysobject be, part_list bf,
i_relation bg, i_sysobject bh,
production_order bi, i_relation bj,
i_sysobject bk, operation bl
where bc.i_part_list_item_id = bb.i_object_id and
bf.state = ? and
bf.i_part_list_id = be.i_object_id and
bi.i_production_order_id = bh.i_object_id and
bl.i_operation_id = bk.i_object_id and
bc.i_part_list_item_id=bd.i_relation_child_object_ id and
bd.i_relation_parent_object_id=bf.i_part_list_id and
bf.i_part_list_id=bg.i_relation_child_object_id and
bg.i_relation_parent_object_id=bi.i_production_ord er_id and
bf.i_part_list_id=bj.i_relation_child_object_id and
bj.i_relation_parent_object_id=bl.i_operation_id
order by bf.i_part_list_id asc
bf.state = ? receives the SAME parameter in both cases.|||Everything is identity, isn't it...
Is one of these tables a driver? Like what you want to base your result set on?
Lots of sele referencing...
Maybe you can use derived tables..
SELECT * FROM (SELECT * FROM ...join the table that relate) AS A
LEFT JOIN (SELECT * FROM ..same thing) AS B
ON A.key = B.Key
Here's your code cleaned up some..Not sure if it's doing the same thing...
I think it is...and easier to see what you're trying to do
SELECT *
FROM i_sysobject bb
LEFT JOIN part_list_item bc ON bc.i_part_list_item_id = bb.i_object_id
LEFT JOIN i_relation bd ON bc.i_part_list_item_id=bd.i_relation_child_object_ id
LEFT JOIN i_sysobject be ON bf.i_part_list_id = be.i_object_id and
LEFT JOIN part_list bf ON bd.i_relation_parent_object_id=bf.i_part_list_id
LEFT JOIN i_relation bg ON bf.i_part_list_id=bg.i_relation_child_object_id
LEFT JOIN i_sysobject bh ON bi.i_production_order_id = bh.i_object_id
LEFT JOIN production_order bi ON bg.i_relation_parent_object_id=bi.i_production_ord er_id
LEFT JOIN i_relation bj ON bf.i_part_list_id=bj.i_relation_child_object_id
LEFT JOIN i_sysobject bk ON bl.i_operation_id = bk.i_object_id
LEFT JOIN operation bl ON bj.i_relation_parent_object_id=bl.i_operation_id
WHERE bf.state = ?
Friday, February 24, 2012
Best way to tell if a SP is a query Programatically
Using ADO.NET Or regular queries on SQL Server, is there a good way of
determining if a Stored Proc will be returning a result set or if it is
a non-query that does all I/O through parameters?
determining if a Stored Proc will be returning a result set or if it is
a non-query that does all I/O through parameters?
Thanks.Hi
You should know this when you program your data access routines and a
recordset needs to be declared to hold the resultset. If the parameters are
input/output/result then they need to be declared accordingly. You may look
at the syscolumns table that has a isoutparam column that will determine if
a parameter is an output parameter.
John
<wackyphill@.yahoo.com> wrote in message
news:1126884177.488723.219470@.g43g2000cwa.googlegr oups.com...
> Using ADO.NET Or regular queries on SQL Server, is there a good way of
> determining if a Stored Proc will be returning a result set or if it is
> a non-query that does all I/O through parameters?
> Thanks.
Sunday, February 19, 2012
best way to run 45 queries in a row?
Hi,
I have created 45 queries that I need to run which will seperate and
stratify data. Each one has to be run one after the other. Right now I sit
there and muanually run them in the SQL Query Analyzer screen. Is there
something I can create to run them automatically, one after the other?
Thanks
If you have to do this more than once you can create a multistep Job or a DTS
package
http://sqlservercode.blogspot.com/
"John Jasper" wrote:
> Hi,
> I have created 45 queries that I need to run which will seperate and
> stratify data. Each one has to be run one after the other. Right now I sit
> there and muanually run them in the SQL Query Analyzer screen. Is there
> something I can create to run them automatically, one after the other?
> Thanks
|||You could copy and paste them all into one stored procedure, with some error
handling thrown in to exit and alert you if one failed...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:74DA6630-0986-4014-AE2E-DACEDF9200D9@.microsoft.com...
> Hi,
> I have created 45 queries that I need to run which will seperate and
> stratify data. Each one has to be run one after the other. Right now I
> sit
> there and muanually run them in the SQL Query Analyzer screen. Is there
> something I can create to run them automatically, one after the other?
> Thanks
I have created 45 queries that I need to run which will seperate and
stratify data. Each one has to be run one after the other. Right now I sit
there and muanually run them in the SQL Query Analyzer screen. Is there
something I can create to run them automatically, one after the other?
Thanks
If you have to do this more than once you can create a multistep Job or a DTS
package
http://sqlservercode.blogspot.com/
"John Jasper" wrote:
> Hi,
> I have created 45 queries that I need to run which will seperate and
> stratify data. Each one has to be run one after the other. Right now I sit
> there and muanually run them in the SQL Query Analyzer screen. Is there
> something I can create to run them automatically, one after the other?
> Thanks
|||You could copy and paste them all into one stored procedure, with some error
handling thrown in to exit and alert you if one failed...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:74DA6630-0986-4014-AE2E-DACEDF9200D9@.microsoft.com...
> Hi,
> I have created 45 queries that I need to run which will seperate and
> stratify data. Each one has to be run one after the other. Right now I
> sit
> there and muanually run them in the SQL Query Analyzer screen. Is there
> something I can create to run them automatically, one after the other?
> Thanks
best way to run 45 queries in a row?
Hi,
I have created 45 queries that I need to run which will seperate and
stratify data. Each one has to be run one after the other. Right now I sit
there and muanually run them in the SQL Query Analyzer screen. Is there
something I can create to run them automatically, one after the other?
ThanksIf you have to do this more than once you can create a multistep Job or a DT
S
package
http://sqlservercode.blogspot.com/
"John Jasper" wrote:
> Hi,
> I have created 45 queries that I need to run which will seperate and
> stratify data. Each one has to be run one after the other. Right now I si
t
> there and muanually run them in the SQL Query Analyzer screen. Is there
> something I can create to run them automatically, one after the other?
> Thanks|||You could copy and paste them all into one stored procedure, with some error
handling thrown in to exit and alert you if one failed...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:74DA6630-0986-4014-AE2E-DACEDF9200D9@.microsoft.com...
> Hi,
> I have created 45 queries that I need to run which will seperate and
> stratify data. Each one has to be run one after the other. Right now I
> sit
> there and muanually run them in the SQL Query Analyzer screen. Is there
> something I can create to run them automatically, one after the other?
> Thanks
I have created 45 queries that I need to run which will seperate and
stratify data. Each one has to be run one after the other. Right now I sit
there and muanually run them in the SQL Query Analyzer screen. Is there
something I can create to run them automatically, one after the other?
ThanksIf you have to do this more than once you can create a multistep Job or a DT
S
package
http://sqlservercode.blogspot.com/
"John Jasper" wrote:
> Hi,
> I have created 45 queries that I need to run which will seperate and
> stratify data. Each one has to be run one after the other. Right now I si
t
> there and muanually run them in the SQL Query Analyzer screen. Is there
> something I can create to run them automatically, one after the other?
> Thanks|||You could copy and paste them all into one stored procedure, with some error
handling thrown in to exit and alert you if one failed...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:74DA6630-0986-4014-AE2E-DACEDF9200D9@.microsoft.com...
> Hi,
> I have created 45 queries that I need to run which will seperate and
> stratify data. Each one has to be run one after the other. Right now I
> sit
> there and muanually run them in the SQL Query Analyzer screen. Is there
> something I can create to run them automatically, one after the other?
> Thanks
best way to run 45 queries in a row?
Hi,
I have created 45 queries that I need to run which will seperate and
stratify data. Each one has to be run one after the other. Right now I sit
there and muanually run them in the SQL Query Analyzer screen. Is there
something I can create to run them automatically, one after the other?
ThanksIf you have to do this more than once you can create a multistep Job or a DTS
package
http://sqlservercode.blogspot.com/
"John Jasper" wrote:
> Hi,
> I have created 45 queries that I need to run which will seperate and
> stratify data. Each one has to be run one after the other. Right now I sit
> there and muanually run them in the SQL Query Analyzer screen. Is there
> something I can create to run them automatically, one after the other?
> Thanks|||You could copy and paste them all into one stored procedure, with some error
handling thrown in to exit and alert you if one failed...
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:74DA6630-0986-4014-AE2E-DACEDF9200D9@.microsoft.com...
> Hi,
> I have created 45 queries that I need to run which will seperate and
> stratify data. Each one has to be run one after the other. Right now I
> sit
> there and muanually run them in the SQL Query Analyzer screen. Is there
> something I can create to run them automatically, one after the other?
> Thanks
I have created 45 queries that I need to run which will seperate and
stratify data. Each one has to be run one after the other. Right now I sit
there and muanually run them in the SQL Query Analyzer screen. Is there
something I can create to run them automatically, one after the other?
ThanksIf you have to do this more than once you can create a multistep Job or a DTS
package
http://sqlservercode.blogspot.com/
"John Jasper" wrote:
> Hi,
> I have created 45 queries that I need to run which will seperate and
> stratify data. Each one has to be run one after the other. Right now I sit
> there and muanually run them in the SQL Query Analyzer screen. Is there
> something I can create to run them automatically, one after the other?
> Thanks|||You could copy and paste them all into one stored procedure, with some error
handling thrown in to exit and alert you if one failed...
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:74DA6630-0986-4014-AE2E-DACEDF9200D9@.microsoft.com...
> Hi,
> I have created 45 queries that I need to run which will seperate and
> stratify data. Each one has to be run one after the other. Right now I
> sit
> there and muanually run them in the SQL Query Analyzer screen. Is there
> something I can create to run them automatically, one after the other?
> Thanks
Monday, February 13, 2012
best way to get queries fast
hi
i have over million records in my DB, what is the best way to get the results fast in case i need to get details of an employe name say "robert", if i do it normally it will take long, should i use index or is there any other good way.
thanx in advance
cheers
Hi,
It depends on type of database usage you have, if its an OLTP system then you can not use a lot of indexes as it will slow down insertion and updation, however in OLAP system you can use indexes wisely and this will help a lot.
Subscribe to:
Posts (Atom)