Problem Running Queries Of A Dataset
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
ADVERTISEMENT
Jun 1, 2007
Hi,Ive got a class in my code which contains data from several tables. A user profile has a single record in a user_profile tables, for each of these records there is several records in a another table, which itself has several records in a tags table.So far the save method on the class saves the record to the user profile table, then loops through the next two tables info to insert each record. I want to decrease the number of trips to the database and am wondering if anyone can advise on the best way to do this.I am considering using a dataset to retrieve and store the records, instead of a datareader Im using currently. Then when Im done altering the data in the dataset (all three tables) I can commit all the changes in one database transaction?? I have been avoiding datasets due to the fact datareaders are apparently faster.My other option is to try make three calls to the database, one per table, updating my stored procedures to accept arrays of data using XML?? Can anyone tell me what is the best option for me?Thanks,C
View 5 Replies
View Related
Jan 29, 2008
I have 4 sets of select queries under 1 stored proc, now on the report calling the stored proc via dataset. when i run the dataset the only first set of the select query related fields appearing under the dataset.
But on the back end sql server, if i execute the same stored proc, i get 4 resultsets in one single executioln.
i am not seeing the remaingin 3 resultsets, on the reports dataset.
Is it possible on the reports or not.
In the asp.net project i was able to use that kind of stored procedures whcih has multiple select statements in 1 procedure., i use to refer 0,1,2,3 tables under a dataset.
Thank you all very much for the information.
View 1 Replies
View Related
Mar 6, 2008
How can i write diffrent Queries in Data set using If...Else Conditions at the time of report Design.
Regards.
View 9 Replies
View Related
Aug 6, 2007
Hi Everyone,
I have a dataset like below
X15ForecastsCounts
X16ActualsCounts
WeekEnding
38
18
7/23/07
38
14
7/30/07
38
35
8/6/07
37
NULL
8/13/07
37
NULL
8/20/07
37
NULL
8/27/07
37
NULL
9/3/07
36
NULL
9/10/07
36
NULL
9/17/07
22
NULL
9/24/07
I am plotting this data set as an S-Curve using the Running Value funtion.
The value and data point value is =RunningValue(Fields!X15ForecastsCounts.Value,Sum,"Series") similar code for X16Actuals Counts.
Unfortunately I am getting the curve like this http://tinypic.com/view.php?pic=4lyiuko
I dont want my X16ActualsCounts to extend beyond 8/6/07 in this case....and next week I dont want it to extend beyond 8/13/07.
How can I fix this issue??
View 3 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
Jul 20, 2005
HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind 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 databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)
View 1 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 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
Feb 22, 2001
We have just implemented our new app and I need to improve performance. We are the victims of not having a very adequate stress testing tool prior to launch. Is there an easy way to track all queries or stored procs longer than a specified time?
View 1 Replies
View Related
Feb 26, 2001
We have just implemented our new app and are expericing some slowness but no blocking issues. Is there an easy way to track all queries taking over x seconds to run?
View 1 Replies
View Related
May 14, 2014
I have the following:
(a) One Dynamic SQL Query that takes 37 ms when run as a single query or in an SP.
(b) Three SQL Indexed View queries that take 0 ms when run together.
When i add (a) + (b) in the same SP, i should get 37 ms + 0 ms = 37ms, but NO it takes 400 ms.
What is causing the extra 363 ms of latency.
View 9 Replies
View Related
Jun 15, 2015
How can we get most frequent queries that are running against to a table in our database?
View 3 Replies
View Related
May 7, 2002
Here's a little SP to break up those long-running, massively-locking, bring-app-to-a-halt queries. By default it does 500 rows at a time and allows for a maximum SQL query size of 4000 characters; it should be trivial to adjust those.
Cheers
-b
CREATE PROCEDURE p_BatchExecute (@vcSQL varchar(4000)) AS
set nocount on
DECLARE @iRows int
select @iRows=1
SET ROWCOUNT 500
WHILE @iRows>0
BEGIN
print 'Executing batch of 500...'
exec (@vcSQL)
set @iRows=@@ROWCOUNT
END
GO
View 3 Replies
View Related
Jun 15, 2007
Can I ask how to split the dataset into training and validation when running descision tree model?
View 3 Replies
View Related
Oct 16, 2007
for some filegroups, we are turning readwrite back on periodically to trickle some data into an archive. This lasts a few seconds and then we turn readonly back on. What is the impact of doing this while a query is running on the archive?
View 1 Replies
View Related
Apr 9, 2008
Hi,
My company told me to prepare document on long runnings queries.
We have components and developed in vb6 and some methods are doing call to as400,db2,informix.
Here I am can not know how tackle and do reaserch on the same.
Question infront me as:
1. why timeout not happen on com+ components.
2. how to prepair document for my reaserch.
Thanks,
Bimal
View 2 Replies
View Related
May 23, 2008
All -
I am using SQL Server 2005 and I have an endpoint that exposes some stored procedures as web-methods in the endpoint.
One particular stored procedure I have exposed takes a long time to execute: about 10 - 15 minutes. While, it is OK, that this stored procedure takes this long, it is not desirable for the HTTP Request that executed this proc to not wait for that long.
What I want to be able to do is to call the stored procedure and have the call return immidetaly but the stored proc continues what its doing. I will call another stored proc at a later time to retrive the result of the first stored proc. The first proc will store its results in a temp table. I am thinking of using SQL Server Service Broker to achieve this.
Is there a better a way to achieve this? And how does SQL Server process the Service Broker requests, i.e., I dont want the query to be executed when the server is busy. Are there any hints that I need to give to Service Broker to be able to do this?
Thanks.
View 5 Replies
View Related
Jul 20, 2015
I need to search for such SPs in my database in which the queries for update a table contains where clause which uses non primary key while updating rows in table.
If employee table have empId as primary key and an Update query is using empName in where clause to update employee record then such SP should be listed. so there would be hundreds of tables with their primary key and thousands of SPs in a database. How can I find them where the "where" clause is using some other column than its primary key.
If there is any other hint or query to identify such queries that lock tables, I only found the above few queries that are not using primary key in where clause.
View 2 Replies
View Related
