Parameterized Queries Running Slower Than Non-parameterized Queries
Jul 20, 2005
Hello
When I use a PreparedStatement (in jdbc) with the following query:
SELECT store_groups_id
FROM store_groups
WHERE store_groups_id IS NOT NULL
AND type = ?
ORDER BY group_name
It takes a significantly longer time to run (the time it takes for
executeQuery() to return ) than if I use
SELECT store_groups_id
FROM store_groups
WHERE store_groups_id IS NOT NULL
AND type = 'M'
ORDER BY group_name
After tracing the problem down, it appears that this is not precisely
a java issue, but rather has to do with the underlying cost of running
parameterized queries.
When I open up MS Enterprise Manager and type the same query in - it
also takes far longer for the parameterized query to run when I use
the version of the query with bind (?) parameters.
This only happens when the table in question is large - I am seeing
this behaviour for a table with > 1,000,000 records. It doesn't make
sense to me why a parameterized query would run SLOWER than a
completely ad-hoc query when it is supposed to be more efficient.
Furthermore, if one were to say that the reason for this behaviour is
that the query is first getting compliled and then the parameters are
getting sent over - thus resulting in a longer percieved execution
time - I would respond that if this were the case then A) it shouldn't
be any different if it were run against a large or small table B) this
performance hit should only be experienced the first time that the
query is run C) the performance hit should only be 2x the time for the
non-parameterized query takes to run - the difference in response time
is more like 4-10 times the time it takes for the non parameterized
version to run!!!
Is this a sql-server specific problem or something that would pertain
to other databases as well? I there something about the coorect use of
bind parameters that I overall don't understand?
If I can provide some hints in Java then this would be great..
otherwise, do I need to turn/off certain settings on the database
itself?
If nothing else works, I will have to either find or write a wrapper
around the Statement object that acts like a prepared statement but in
reality sends regular Statement objects to the JDBC driver. I would
then put some inteligence in the database layer for deciding whether
to use this special -hack- object or a regular prepared statement
depending on the expected overhead. (Obviously this logic would only
be written in once place.. etc.. IoC.. ) HOWEVER, I would desperately
want to avoid doing this.
Please help :)
View 1 Replies
ADVERTISEMENT
Sep 25, 2006
Hello..
Is there a way to use parameterized queries with RDA method? I write a program for WinCE5.0 and when I submit a query I use hardcoded date format and this causes problems in different systems.There's a solution for this?
Thanks in advance.
View 1 Replies
View Related
Feb 15, 2007
Just getting started using SSce and having a few problems
What I want to do is something like this...
Dim Code As Integer
Dim Description As String = txtDescription.Text.Trim
Dim conn As SqlCeConnection = ConnectToLocalDatabase()
Dim ssql As New System.Text.StringBuilder
ssql.AppendLine("INSERT INTO T_Titles (Description)")
ssql.AppendLine("VALUES(@Description)")
ssql.AppendLine("SELECT @Code = @@IDENTITY")
Dim cmd As New SqlCeCommand(ssql.ToString, conn)
Dim sqlCode As New SqlCeParameter("@Code", 0)
sqlCode.Direction = ParameterDirection.InputOutput
cmd.Parameters.Add(sqlCode)
cmd.Parameters.Add(New SqlCeParameter("@Description", Description))
cmd.ExecuteNonQuery()
Code = CInt(sqlCode.Value)
**********************************************************************
The above code doesnt work. Firstly I am not sure if I can execute the two statements in one go. Secondly, I am not sure if output parameters are supported.
I have been working with SQL Server since 6.5 but have always used sprocs and am feeling a little lost here without them. Any help getting started would be greatly appreciated.
Thanks
View 3 Replies
View Related
Jun 29, 2007
Hey fellas. Here's my situation. I have two textboxes where the user enters a "start" date and an "end" date. I want to search a table to find records who's "expired" column date is between those two dates provided by the user. The tricky part is, if the user just puts a start date in but no end date, I want it to search from whatever start date the user entered to the future and beyond. Essentially, I think I'm looking for a SQL statement along the lines of:
SELECT Request.RequestID, Request.URL, ActionProvider.Name, Request.CurrentStageID, Request.Decision, Request.SubmissionDate,
Request.ExpirationDate
FROM Request INNER JOIN
RequestSpecificActionProvider ON Request.RequestID = RequestSpecificActionProvider.RequestID INNER JOIN
ActionProvider ON RequestSpecificActionProvider.ActionProviderID = ActionProvider.ActionProviderID INNER JOIN
RoleActionProvider ON ActionProvider.ActionProviderID = RoleActionProvider.ActionProviderID INNER JOIN
Role ON RoleActionProvider.RoleID = Role.RoleID
WHERE
CASE WHEN @BeginDate is not null AND @BeginDate <> ''
THEN Request.ExpirationDate > @BeginDate
END
AND
CASE WHEN @EndDate is not null AND @EndDate <> ''
THEN Request.ExpirationDate > @EndDate
END
AND (Role.Description = 'Requestor')
I realize my code isn't correct and there's still a floating "AND" out there I would have to put some logic around. Anyway, how do I do this? Do I need to build three separate queries in my tableadapter (one for if both dates are provided, one for if start date is provided, one for if end date is provided) and build the logic in my application code or can I tackle it with SQL? If I can tackle it with SQL, where have I gone astray? I'm currently getting the error: "Error in WHERE clause near '>'. Unable to parse query text."
Thanks for the help everyone!
View 3 Replies
View Related
Aug 22, 2007
How would I debug such a query.
I have a sqlCommand to which I add several parameters for an insert statement.
if the statement fails, for some reason, I would like to copy the final sql with all values inserted as text and use this in e.g. TOAD to see where the error is coming from. Is this possible?
View 1 Replies
View Related
Feb 5, 2008
Hi,
I need to use parameters with the IN clause in a SQL statement like:
select * from tableX where field IN (1,2,3,4)
I don't know how to do that.
I'm using SQLServer and OleDB.
Thanks for your help.
View 1 Replies
View Related
May 16, 2004
I have a SQL stored procedure like so:
CREATE PROCEDURE sp_PRO
@cat_num nvarchar (10) ,
@descr nvarchar (200) ,
@price DECIMAL(12,2) ,
@products_ID bigint
AS
insert into product_items (cat_num, descr, price, products_ID) values (@cat_num, @descr, @price, @products_ID)
when I try and insert something like sp_PRO '123154', 'it's good', '23.23', 1
I can't insert "'" and "," because that is specific to how each item is delimited inorder to insert into the stored procedure. But if I hard code this into a aspx page and don't create a stored procedure I can insert "'" and ",". I have a scenario where I have to use a stored procedure...confused.
View 3 Replies
View Related
Feb 3, 2004
I have an application where users can enter data into any (or all) of 6 search fields,
to produce a filtered query.
This works fine using my Access version(see code below),
but as SQLS2k cannot use "IIF", I tried to replace these bits with
"CASE/WHEN/THEN/ELSE" lines, which does not work with numeric fields
as these cannot be "wild-carded" in the same way as Access allows.
Can anyone suggest a way forward that does not involve coding all the
possible permutations of "SELECT" blocks driven by lots of nested "IF/THEN/ELSE"s?
Hoping you can help
Alex
PARAMETERS
CurrentType Text,
CurrentCategoryID Long,
CurrentProductID Long,
CurrentClientID Long,
CurrentContractID Long,
FromDate DateTime,
ToDate DateTime;
SELECT
tAudit.AuditID,
tAudit.ActionType,
tAudit.ClientID,
tClients.ContactCompanyName,
tAudit.ContractID,
tContracts.ClientRef,
tAudit.ProductID,
tProducts.ProductName,
tAudit.CategoryID,
tCategories.CategoryName,
tAudit.Acknowledged,
tAudit.ValueAmount,
tAudit.DateStamp
FROM (((tAudit
LEFT JOIN tCategories
ON tAudit.CategoryID = tCategories.CategoryID)
LEFT JOIN tClients ON tAudit.ClientID = tClients.ClientID)
LEFT JOIN tContracts ON tAudit.ContractID = tContracts.ContractID)
LEFT JOIN tProducts ON tAudit.ProductID = tProducts.ProductID
WHERE (((tAudit.ActionType) Like IIf(IsNull([CurrentType]),"*",[CurrentType]))
AND ((tAudit.ClientID) Like IIf(IsNull([CurrentClientID]),"*",[CurrentClientID]))
AND ((tAudit.ContractID) Like IIf(IsNull([CurrentContractID]),"*",[CurrentContractID]))
AND ((tAudit.ProductID) Like IIf(IsNull([CurrentProductID]),"*",[CurrentProductID]))
AND ((tAudit.CategoryID) Like IIf(IsNull([CurrentCategoryID]),"*",[CurrentCategoryID]))
AND (([tAudit].[DateStamp]) Between [FromDate] And [ToDate]));
View 2 Replies
View Related
Jul 23, 2005
Is there any easy way to pass (dynamically) parameters to pass-throughqueries,when working with MS Access as front-end for SQL Server ?Thanks.
View 1 Replies
View Related
Mar 14, 2008
I've setup a parameterised query in PHP correctly and achieved the results I wanted from a basic view.
I then developed the application to use a Table-valued Function to be able to simplify the PHP code.
SELECT * FROM [CDBF].[dbo].[webOrganisation] ('w%',5,11)
This query works fine within the code.
/* Define the query. */
$tsql = "SELECT * FROM webOrganisation ('w%',5,11)";
/* Execute the query. */
$stmt = sqlsrv_query( $connection, $tsql);
if ( ! $stmt )
{
echo "Error in statement 2 execution in display_search().";
die( print_r( sqlsrv_errors(), true));
}
/* Iterate through the resultset printing a row of data upon each iteration.*/
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC))
{
// Display results (this bit works fine!)
}
/* Free statement and connection resources. */
sqlsrv_free_stmt( $stmt);
However when I try to parameterise it, I get errors.
Error in statement 2 execution in display_search(). Array ( [0] => Array ( [0] => 07009 [SQLSTATE] => 07009 [1] => 0 [code] => 0 [2] => [Microsoft][SQL Native Client]Invalid Descriptor Index [message] => [Microsoft][SQL Native Client]Invalid Descriptor Index ) [1] => Array ( [0] => 07009 [SQLSTATE] => 07009 [1] => 0 [code] => 0 [2] => [Microsoft][SQL Native Client]Invalid parameter number [message] => [Microsoft][SQL Native Client]Invalid parameter number ) )
/* Define the query. */
$tsql = "SELECT * FROM webOrganisation ( ? , ? , ? )";
/* Execute the query. */
$start = 5;
$finish = 11;
$params = array($search.'%',$start,$finish);
$stmt = sqlsrv_query( $connection, $tsql, $params);
if ( ! $stmt )
All other parts of the code remain the same.
Where am I going wrong
View 3 Replies
View Related
Jul 21, 2014
know if there is any way out to run execution plan for parameterized queries?
As application is sending queries which are mostly parameterized in nature and values being used are very robust in nature, So i can not even make a guess.
View 1 Replies
View Related
Aug 12, 2006
I've been driving myself nuts trying to get a sensible product search
going. The existing live site search is just a LIKE %searchterm% on
the Title field in our Products table. Fast, but not great ;) Talks
between IT and Marketing have resulted in this being the desired
results and order:
Exact Title match
Substring Title match
Substring Keywords match
Substring Description match
OK, I can easily do this with UNION queries using LIKE (and a virtual
"weight" column in each query), but the query takes too long. So I'm
trying out SQL Server full text indexing, in an attempt to get the
speed up (and the natural language stuff is just plain cool).
Where I'm running into problems is doing a FULLTEXT match on
Description. It seems that to do a phrase match (e.g. "new york"
should match only Descriptions where the phrase "new york" occurs) I
need to enclose the search term in quotation marks in the query (or
maybe single AND double quotes).
But using parameters in ASP.NET (which I'm supposed to do to avoid SQL
injection attacks, yes?) I don't really have full control of the
quoting - ASP.NET and/or SQL Server automagically quotes strings for
me before passing them into the query. I think.
For example, this doesn't find any description matches:
==========================================
Declare @theSearchTerm varchar(100), @theSearchTerm1 varchar(100)
set @theSearchTerm = "new york"
set @theSearchTerm1 = "%new york%"
SELECT m.TitleCode, m.ShortName, m.ShortDescription, 50 as theWeight
FROM Product m(NoLock)
WHERE m.ShortName = @theSearchTerm
UNION
(SELECT m.TitleCode, m.ShortName, m.ShortDescription, 40 as theWeight
FROM Product m(NoLock)
WHERE m.ShortName LIKE @theSearchTerm1)
UNION
(SELECT m.TitleCode, m.ShortName, m.ShortDescription, 30 as theWeight
FROM Product m(NoLock)
WHERE CONTAINS(*, '"@theSearchTerm"'))
ORDER BY theWeight DESC, m.ShortName
==========================================
But this one (where I put in the actual string instead of using the
parameter) *does* get the desired results, including matches in the
Description:
==========================================
Declare @theSearchTerm varchar(100), @theSearchTerm1 varchar(100)
set @theSearchTerm = "new york"
set @theSearchTerm1 = "%new york%"
SELECT m.TitleCode, m.ShortName, m.ShortDescription, 50 as theWeight
FROM Product m(NoLock)
WHERE m.ShortName = @theSearchTerm
UNION
(SELECT m.TitleCode, m.ShortName, m.ShortDescription, 40 as theWeight
FROM Product m(NoLock)
WHERE m.ShortName LIKE @theSearchTerm1)
UNION
(SELECT m.TitleCode, m.ShortName, m.ShortDescription, 30 as theWeight
FROM Product m(NoLock)
WHERE CONTAINS(*, '"new york"'))
ORDER BY theWeight DESC, m.ShortName
==========================================
Trying various permutations of quotes around the parameter gives me
either syntax errors or undesirable results.
Has anybody tried this sort of thing? How did you do it?
Thanks,
Greg Holmes
View 4 Replies
View Related
Apr 23, 2007
Oracle and MS drivers do not support parameterized queries, so update table set column=? where primarykey=? does not work for Oracle.
Anyone knows how to update an Oracle table through SSIS?
Thanks!
Wenbiao
View 5 Replies
View Related
Jan 24, 2008
I'm using SQL2005. I'm confused on why queries run slower after I add indexes to a table. I thought the system would pick the best index available when running select statements.
sys.dm_db_missing_index* and Tuning Advisor keep recommending indexes that make my system slower. Maybe they are making a few queries faster that I'm not aware of but I do know they makes some too slow. Now I have to document which indexes to avoid creating for each table.
View 5 Replies
View Related
Jun 27, 2001
Hi,
We have recently upgraded our production server from a dual pentium II 400mhz server with 384Mb of RAM to a triple 700mhz Pentium III system with 2gb of RAM . However, since switching over, all of our Access queries on the SQL7 databases are either running extremely slowly or not at all despite the DSN's being set up correctly . Does anyone have any ideas why, and more importantly, how I can resolve this???
Thanks
Pete Burton
(IT Support)
Durham Aged Mineworkers Homes Association
View 2 Replies
View Related
Feb 5, 2008
Looking at perfmon, I can see that SQL Server 2005 is running the CPU near 100% -- but I don't know what it's doing. How can I see a list of the queries that are currently executing?
Better yet, is there a more general "what is the database busy with right now" diagnostic tool somewhere? (I know I can capture a trace, but that won't show me what's already running, AFAIK.)
Thanks for any help...
View 6 Replies
View Related
Jan 1, 2006
I'm creating a search function right now and I'm running into a problem where my search function doesn't know what fields the user will search for, and therefore doesn't know whether or not to put quote marks around specific values or not.
For example, somebody might search for somebody else with year of birth in which case I might have a query:
SELECT userid FROM users WHERE yob = 1970
but somebody else might search for a name, in which case I need
SELECT userid FROM users WHERE first_name = 'Andrew'
or somebody else might search for both and need
SELECT userid FROM users WHERE yob = 1970 AND first_name = 'Andrew'
I'm accomplishing this by having the function (this is in PHP) take an array as an argument, where the key is the MySQL column and the value is the required value for that column. So in the 3rd example, it would be a 2-item array:
yob => 1970
first_name => 'Andrew'
And then cycling through that and dynamically creating a MySQL query based on the received array.
So... my problem is finding a way to specify when quote marks are required and when they're not. Is there any way to just have MySQL default to the format of the column? Also, if anybody thinks this isn't the right way to create a search function, let me know because I'm new at this .
Thanks!
PS: Right now what I'm doing is I'm creating arrays that include names of columns that do and don't need quote marks. Then in construcing the MySQL statement I'm checking to see which array a column is in, and making the quote decision based on that.
View 6 Replies
View Related
Apr 3, 2008
1. could any one tell me in a simple way how to troubleshoot long running queries.
2.what is the default recovery model
View 6 Replies
View Related
May 9, 2008
Hello,
I want to find long running queries?
Can any one help me?
Thanks
Prashant Hirani
View 4 Replies
View Related
Mar 27, 2015
I have a large number of relatively simple sql queries that each perform tests on different tables returning different columns and rows for each test.
I would like to be able to run all of these at once and get the output in a usable format.
Would it be possible to run a single query or store procedure which could perform each test then output the results from each test to a new sheet in excel?
Could it output the results of each test to individual csvs?
View 3 Replies
View Related
Jul 20, 2005
I have written quite a complex query in Query Analyser, now need toknow how to automate the running of the query to dump the results intoa flat file at scheduled times. Am an experienced Unix user, where itwould be quite simple but new to sql server, is there a relativelysimple way to do this?
View 1 Replies
View Related
May 2, 2007
I have a problem in running queries.
I developed an application uses sqlserver 2005 express edition
I create all queries in storedprocedures.every things work perfect but some times I get long delay in running queries. but after some minutes it comes regular . I coudlnt find any relation between delay and time of work.it comes by chance . also I set timeout for running query for 30 sec. but some times it took more than minutes.
what should I do?
View 3 Replies
View Related
Jan 10, 2008
Hello
Could you please help me solving this problem?
I have a stored procedure called subscribe for inserting a new row to subscriptions table. Then I added a new query (nonquery) to my dataset called 'Subscribequery' for handling the stored procedure.
now, I want to run the query lke this:
DataSet1TableAdapters.SubscribeQuery C = new DataSet1TableAdapters.SubscribeQuery();
C.Subscribe(Profile.UserName, Convert.ToInt32(Subscriptions.Rows[1].Cells[1].Text));
but nothing is added to table. what can I do?
Should I be looking for something like Update(dataset) method for my query?
many thanks in advance
View 2 Replies
View Related
Jan 11, 2002
Hi, I have the following error logged in SQL Server's error log whenever I run a query on a particular database :
2002-01-04 22:54:02.46 spid11 Error: 823, Severity: 24, State: 1 2002-01-04 22:54:02.46 spid11 I/O error 1117(The request could not be performed because of an I/O device error.) detected during read of BUF pointer = 0x14eac480, page ptr = 0x73c94000, pageid = (0x1:0x158c89), dbid = 8, status = 0x801, file = d:mssql7datamydb.mdf..
2002-01-05 05:54:22.01 kernel SQL Server terminating because of system shutdown. 2002-01-05 05:54:22.14 kernel LogEvent: Failed to report the current event. Operating system error = 31(A device attached to the system is not functioning.).
and get this error in the Results pane of Query analyzer : Server: Msg 823, Level 24, State 1, Line 1 I/O error 1117(The request could not be performed because of an I/O device error.) detected during read of BUF pointer = 0x14ace4c0, page ptr = 0x35df6000, pageid = (0x1:0x44b73), dbid = 14, status = 0x801, file = d:MSSQL7DATAmydb.mdf.
Connection Broken
select @@version returns, Microsoft SQL Server 7.00 - 7.00.961 (Intel X86) Oct 24 2000 18:39:12 Copyright (c) 1988-1998 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
Books Online Help on Error 823 indicates that it is a disk problem and suggests that DBCC checkdb needs to be run on the database. But, hardware diagnostics on the server do not report any disk problems and dbcc checkdb also returns no errors.
Help on the cause of error 823 would be greatly appreciated. Thank you, Praveena
View 1 Replies
View Related
Mar 5, 2002
Hello All
I have queries which take over 30 secs to run, which I wish to monitor.
Currently, I am monitoring using sql profiler.
Is there any way of setting up mail to e-mail me when such a query happens.
Could I set up an Alert, or is there some other method.
I want to be able to react to these events faster before the users complain.
I am using sql server 7 enterprise and I have exchange set up.
Any help would be great
Pargat
View 2 Replies
View Related
Aug 30, 2006
I have a table that contains approx 200 thousand records that I need to run validations on. Here's my stored proc:
[code]
CREATE PROCEDURE [dbo].[uspValidateLoadLeads]
@sQuotes char(1) = null, @sProjectId varchar(10) = null, @sErrorText varchar(1000) out
AS BEGIN
DECLARE @ProcName sysname, @Error int, @RC int, @lErrorCode bigint, DECLARE @SQL varchar(8000)
IF @sQuotes = '0'
BEGIN
UPDATE dbo.prProjectDiallingList_staging
SET sPhone = RTrim(LTrim(Convert(varchar(30), Convert(numeric(20, 1), phone))))
END
ELSE
BEGIN
UPDATE dbo.prProjectDiallingList_staging
SET sPhone = phone
END
--2. Remove quotes
UPDATE dbo.prProjectDiallingList_staging
SET sphone = REPLACE(sphone,'"' , '')
--3. Remove decimal, comma, dashes, parenthesis
UPDATE dbo.prProjectDiallingList_staging
SET sphone = replace(replace(replace(replace(replace(replace(sphone,'.',''),',','' ),'-',''), ' ',''), '(', ''), ')', '')
--4. Update failed Validation column if not 10 digits
UPDATE dbo.prProjectDiallingList_staging
SET sFailedValidation = 'X'
WHERE(Len(RTrim(LTrim(sPhone))) <> 10)
--5. Dedup
UPDATE a
SET a.sFailedValidation = 'X'
FROM dbo.prProjectDiallingList_staging a (nolock)
INNER JOIN dbo.prProjectDiallingList_staging b
ON a.sPhone= b.sPhone
WHERE(a.iList_StagingID > b.iList_StagingID)
--6. Update failed Validation column if not numeric
UPDATE dbo.prProjectDiallingList_staging
SET sFailedValidation = 'X'
WHERE(IsNumeric(RTrim(LTrim(sPhone))) = 0)
--7. Update time zones
UPDATE s
SET s.sTimeZone =z.sTimeZone
FROM dbo.prProjectDiallingList_staging s (nolock)
LEFT OUTER JOIN dbo.prPhoneTimeZone z
ON left(rtrim(ltrim(s.sphone)),3) = z.sPhoneAreaCode
--8. Insert into dialing table only records that have not failed the validation
INSERT dbo.prProjectDiallingList(iPrProjectId, sPhoneNumber, sTimeZone)
SELECT @sProjectId,sPhone, sTimeZone
FROM dbo.prProjectDiallingList_staging
WHERE ISNULL(sFailedValidation,'1') = '1'
UPDATE d
SET d.bProcessReporting = 1
FROM dbo.prProjectDialling d
WHERE d.iPrProjectId = @sProjectId
END
[/code]
When I execute this stored proc it runs for more than 5 minutes. Is there anything i can do to speed it up? Maybe there is a faster way of writing these queries?
Thanks,
Ninel
View 1 Replies
View Related
Jul 21, 2001
Is there any way to measure the progress of a long running query,
for instance, to find where in a query plan a query is in SQL 7.0?
I have a query I am running that is currently 2 1/2 hours into the
query. Since it's joining three large tables, one with 42 million rows
and two with 7 million rows, I'm expecting the query to take a
while. However, I have no way of estimating exactly how long
it will take. Before I ran it, I optimized it the best I could in
Query Analyzer using an estimated query plan, making sure I had
all the right indexes, etc. I've been trying to use the estimated cost
to project query time, but that hasn't been working since queries
with similar costs can take radically different amounts of time to
execute.
Now I'm sitting here waiting, wondering if the query is just taking
too long, and I should stop it and work on optimizing it some more
(since I will have to run a couple more queries like it), or let it
finish. But I have no clue how close it is to finishing. I've tried looking
at the Physical I/O given by sp_who2 and then trying to calculate the
number of pages it would have to read if it had to read everything
from disk, then estimating it's progress by that, but this seems dubious
at best, since I don't know a whole slew of factors (ie: how many
pages are being read from the cache, is my page calculation correct,
etc).
So, does anyone know of any way to figure out how soon a long
running query will finish in SQL 7.0?
Thanks.
--
Trevor Lohrbeer
trevor@truepeers.com
View 2 Replies
View Related
Feb 26, 2002
Permission problem w/ users running some queries
I have a SQL-authenticated user that I want to be able to run a pass-thru query. The user was created solely for this particular job. I've given it appriopriate permissions on all the tables, views, and stored procedures directly involved in the task. BUT, the task fails.
If I have the query perform a simple task, like SELECT * FROM TABLE, it works,
but, not on this:
CREATE PROCEDURE sp_gtn_RunFromAccess AS
EXEC msdb..sp_start_job @job_name='CA_GTN Download From Mainframe'
GO
If I give the user the DB_OWNER role, it fails; however, if I make it a sys admin, it suceeds. That is obviously no good. It also suceeds it I put sa and a password in the ODBC string, but that is unacceptable, as well.
What kinds of permission am I overlooking?
Any thoughts on this?
Thanks,
Michael G
View 2 Replies
View Related
Jun 28, 2004
An general question.
A database table contains a textfield with queries. Is it possible to run them with another query.
Code:
----ID----|-------------SQL------------------
0 |"Select * from table0
1 |"Select * from table2 where x = 3
or so?
Does any one know? It would be nice if possible very dynamic but perhaps not so safe?
View 2 Replies
View Related
May 21, 2014
I have for delete queries which I run separately. Could I have a one statement to run instead.
DELETE FROM dbo.PatientHistory
FROM dbo.PatientHistory INNERJOIN TEST_PATS ON dbo.PatientHistory.PatientGuidDigest = TEST_PATS.PatientGuidDigest
DELETE FROM dbo.PostcodeScores
FROM dbo.PostcodeScores INNERJOIN TEST_PATS
ON dbo.PostcodeScores.PatientGuidDigest = TEST_PATS.PatientGuidDigest
[Code] ....
View 2 Replies
View Related
Jul 23, 2005
Hi,i am having a strange problem running memory intensive queries on SQLserver.I am doing an update on a table with 9 million records from anothertablewith 50 records.the query i am running isupdate table1set var1 = b.var2from table2 bwhere key1=b.key1this query hanges for ever. I had thought that there was a problem withmy machine...but once out of the blue it ran in 16 minutes.I am running a 1 Ghz PIII with 512 MB of memory.Any ideas as to what could be the issue ?RegardsRishi
View 11 Replies
View Related
Jul 20, 2005
I can't seem to find a step-by-step guide to how to detect orcontinuously monitor for long-running queries that is suitablefor a comparative SQL Server novice. I know that it is possibleto monitor for such with other database products - can anyoneenlighten me as to how this is done with SQL Server? Ideally,I'd like to snapshot running queries (preferably with querytext) at a particular instant via a script, although any helpto show what queries are running at an instant will be muchappreciated. Any ideas?__________________________________________________ ___________Are you Catholic ?http://www.CatholicEmail.com100s of FREE email addresses --->http://www.UltimateEmail.comSend an Online Greeting Card http://www.UltimateEcards.com
View 1 Replies
View Related
Aug 14, 2007
I'm wondering if one less external sql server connection is available when my clr stored proc querys my database inside of the db engine.
View 1 Replies
View Related