I have multiple SQLDatasources on multiple pages. When I call the Select command for the datasource the query is run multiple times. I was wondering if anyone had any problems like this. The data is being read into listboxes. If you need any more info or have any specific questions, feel free to ask.
View 6 RepliesI want  the below query to run 24 hours ..once the insert is complete, run again , so on for 24 hours .
there is a way to run every second in as job but i want to run only after run complete ..is there a way to run the query after every complete run ? and keep in jobÂ
Â
INSERT INTO [dbo].[Audit_Active]
([SPID],[LoginName],[HostName],[ProgramName],[Command],[LastQuery],[DBName],[ServerName])
 SELECT  Â
--DISTINCT Â
p.SPID,
p.LogiName,
p.HostName, Â Â
[Code] ....
Hello -
I am having a difficult time trying to generate a result set that collapses multiple rows into one. However, I am also getting some of the columns that I am trying to SUM, to double their value.
I have two tables as:
Table 1
EmpID (int), AcctID (int), ActivityDate(datetime), Process (nvarchar 20), Cost (money)
Table 2
EmpID (int), AcctID (int), ActivityDate(datetime), Process (nvarchar 20), Duration (smallint)
Some sample data:
Table 1
501, 7998, 04-2-2008, Process1, $100
501, 7998, 04-2-2008, Process2, $75
Table 2
501, 7998, 04-2-2008, Process2, 3
Result i need is:
501, 7998, 04-02-2008, $175, 3
What I am getting is:
501, 7998, 04-02-2008, $175, 6
Any suggestions or help will be appreciated.
Thank you
- will
Hello,
I see the following problem regarding a SQL function that returns a value.
We have the following query
SELECT c.Id_Customer, c.Name
FROM t_Customers c
WHERE c.Id_Status = fn_GetParameter('ID_Status.Active')
The idea is not hardcoding the status and other values on each query, and, since SQL Server does not support the definition of constants, we have a table with many parameters and we search them.
We defined the function "WITH SCHEMABINDING" and the SQL Server recognizes it as DETERMINISTIC, so I do not understand why it executes the function as many times as records in the t_Customers table, since every time it is executed it returns the same value.
We could define a variable, assign the value returned by this function to this variable and then use it on the SELECT, but this approach is useless if we use SQL instead of stored procedures (for example, in reports from reporting / BI tools).
Any explanation about why SQL chooses to execute the function many times, and any hint regarding how to make SQL Server execute only once the function will be very appreciated.
Thanks in advance,
Lisandro.
Hi,
I was wondering if this can be done...
I have a complex query which has to do a few calculations. I'm using subqueries to do the calcs, but most of the calcs have to use a value gotten from the first subquery. I don't want to have to type the subquery out each time, so is there a way of assigning it to a variable or putting it in a UDF or SP?
E.g.
I have a table with 2 cols - amount, date.
SELECT total_amount, closing_amount,
FROM table1
GROUP BY month(date)
Total amount is the SUM(amount) for the month.
Closing amount is the Total Amount plus the amounts for the current month with a few extra calcs.
As I have to use SUM(amount) in the second subquery, is there a way I can do it without having to type hte subquery out again?
This is only a basic example, what I'm trying to do will invovle a lot more calcultions.
Hope someone can help,
Thanks,
Stuart
I need some help and feedbacks from other users that are currently running Reporting Services using DB2 as a database. I have been testing OLE DB and ODBC from Microsoft and IBM, and recently DB2 .Net Data provider from IBM.
We are currently running DB2 on z/os v 7, gateway v.8 fixpack 10. We will mainly be calling Stored Procedures on the DB2 and passing recordsets back to the client.
First I installed IBM DB2 ODBC DRIVER v. 7.01.00.88, but I did not get this too work with reporting Services calling Stored Procedures, so I had too upgrade the driver to v8.01.10.812 to get it to work.
I have been in contact with Microsoft and they recommend using Microsoft OLE DB Provider for DB2 (30.sept 2005). I have tried too get this driver too work but I have problems configuring it. I think that it has something too do with some packages that the driver tries too create on DB2. I get error messages both on the DB2 and in the Data access Tool that runs a wizard setting up the driver. '€”The package creation process has failed on resource "mytest", Data description mismatch SQLSTATE:HY000, SQLCODE: -292'
IBM have also developed a IBM DB2 Data provider for .Net Framework 2.0. This is the only driver that I can use too browse the database in the Server Explorer in VS. If you edit the RSReportDesigner.config file you can also use this driver in Report Server projects. Just add <Extension Name="DB2" Type="IBM.Data.DB2.DB2Connection,IBM.Data.DB2, Version=9.0.0.2, Culture=neutral, PublicKeyToken=7c307b91aa13d208" /> too the data section of the xml file.
So too wrap it all up. Which provider should I use? Are there any recommendations out there?
Thanks
B
I’m binding the distinct values from each of 9 columns to 9 drop-down-lists using a stored procedure. The SP accepts two parameters, one of which is the column name. I’m using the code below, which is opening and closing the database connection 9 times. Is there a more efficient way of doing this?
newSqlCommand = New SqlCommand("getDistinctValues", newConn)newSqlCommand.CommandType = CommandType.StoredProcedure
Dim ownrParam As New SqlParameter("@owner_id", SqlDbType.Int)Dim colParam As New SqlParameter("@column_name", SqlDbType.VarChar)newSqlCommand.Parameters.Add(ownrParam)newSqlCommand.Parameters.Add(colParam)
ownrParam.Value = OwnerID
colParam.Value = "Make"newConn.Open()ddlMake.DataSource = newSqlCommand.ExecuteReader()ddlMake.DataTextField = "distinct_result"ddlMake.DataBind()newConn.Close()
colParam.Value = "Model"newConn.Open()ddlModel.DataSource = newSqlCommand.ExecuteReader()ddlModel.DataTextField = "distinct_result"ddlModel.DataBind()newConn.Close()
and so on for 9 columns…
I created an SSIS Package and once complete there is 4 instances of the same data. I checked the source and it only has 1 instance. Why is it pulling the data 4 times? It will first show 3 instances and then another will show up before the package finishes running. Is there a bug in the program or is there a place I'm not aware of where it sets how many times the package should run.
Thank you,
Cindy
Hi Guys!
I have created a big list of packages, some calling others. They all work fine from my computer using Visual Studio.
When I try to deploy them (building them with deployment turned on and running them either directly from Management Studio or as a job) I get the errors with the password of connection strings. From what I read so far its the encryption process that kills it.
I have tried to add a password to some packages, but it still didnt work (only when run directly on my computer in management studio after deploying to SQL Server, but not as a job).
I have tried to change ProtectionLevel to SecurityStorage, wouldnt let me save in Visual Studio (I understand it is ot allowed in VS because you are saving to File System, how the hell am I supposed to save it to anything else? why is it showing there if its not even valid?).
If anyone can please give me the steps to doing it properly, that would be awesome. I simply need to run the packages from SQL Server! thats all! I have no idea why it has to be soooo difficult :/
Is it possible to run through a buffer multiple times in an asynchronous script?
Let say I have a data set and I want to get the max value and then compare/subtract each row in the data set to that max value and add that as a new column - is that possible in the asynchronous script?
Basically I would need to run through the buffer once and pull out the value for the max, and then go through the buffer again pushing to the output buffer the row with the new column "DiffFromMax".
I already tried adding an asynchronous script to pull the max and put into a variable and downstream add a derived column which subtract from the variable but it doesn't work as the variable cannot be assigned till the postexecute() so its always too late.
I've tried having an asynchronous script that has 2 output, one containing the max and the other the rest of the data, however there is no way to subtract without spoofing a cross join which is really slow becuase of the sort required (I still can't believe msft rejected my request for adding a cross join, it had lots of votes and it should be easy to add... I'd code it myself if they let me have a script transformation with multiple inputs)
I have a SQL Agent job that runs at 4:15 in the morning. The job has 5 steps, each step only runs if the preceding step succeeds. The second step, which calls an SSIS package that does the main processing, appears to finish as it goes on to the next step; however, when looking in 'View History' there are 2 entries for this step - the first one shows it as still running (Circled Green Arrow) but with a start and end time. The second entry says the job succeeded.
I have been seeing conflicts, such as deadlocks, with later jobs. I suspect this job is causing the conflicts - maybe the package is still running in the background instead of having actually completed?
what conditions a job step my be showing in the job history as both running AND completed successfully?
If I start a long running query running on a background thread is there a way to abort the query so that it does not continue running on SQL server?
The query would be running on SQL Server 2005 from a Windows form application using the Background worker component. So the query would have been started from the background workers DoWork event using ado.net. If the user clicks an abort button in the UI I would want the query to die so that it does not continue to use sql server resources.
Is there a way to do this?
Thanks
I have an VB.NET web app which performs a fairly complicated SQL query. It seems in the morning, the 1st time the page is loaded (and query executed) it takes up to 10-15 seconds to complete loading. Sometimes it even times out. However anytime after that, the page loads up (even from another computer) in about 4-5 seconds. Can someone explain the reason for this and how I might fix the load times in the morning?
Thanks
Hi there, I'm a little bit stumped on this one.
I have a column in a table that records when the date and time of an event took place.
Table Name: Chronicle
Column Name: Created (of type DateTime)
I would like to select the Chronicle records that are between two dates.
(e.g. 1 May 2001 and 20 May 2001)
And I would like to select those records that are between two times.
(e.g. 6:00am and 1:00pm)
Does anyone know how to do this or have any pointers for me?
I can see it would be easier if I had the date in one column and the time in the other.
Can it be done without doing that?
cheers,
Hamish
Hamish Norton
hamish@liftrider.com
Hi all, new to SSIS so please bear with me on the noobie question:
Situation: have a SQL database with several tables, each table has several char fields that represent dates (ex. YYYYMMDDHHMMSSMS)- this SQL database is created weekly from an extract of an old Oracle RDB database maintained by a third party vendor.
Need to copy the data to a new database and tables
Then for each table:
1. check each char date column and if the value is '1858111700000000' (Oracle dummy date) then change to SQL low date, if it's not then transform the date into SQL server date format. I' ve tried some of the data controls - just need to know which ones to use and in what order.
What would be the best controls to do iterative processing in an efficiant manner? Some tables have upto 5 million rows
Any Ideas would be appreciated! Thanks!
patelnimisha writes "i am try to read three xml files and transfer its data into sql databse.
so
i have no any idea about this qurey.
give me coding for this query"
We recently upgraded from SQL 6.5 to SQL 7. I have a few .sql files that were each running around 5 - 8 minutes under 6.5. These same files now each take over 30 minutes to run. Has anybody had problems with their queries taking longer to run under 7.0? These files are quite large and are comprised of 3 - 4 batches with several queries in each batch. If anybody has any thoughts on the cause please let me know.
Thanks in advance.
Hi,
I'm in an unfortunate situation. We are posting information using a stored procedure to an outside SQL server connected to through a System DSN on our server (win 2003 server) using php's ODBC functions (we never had any luck connecting directly to the SQL server using php's mssql functions).
Everything is working fine, we can connect, send querys, etc ... but between 1am and 10am we recieve errors when trying to execute queries (though we can connect fine).
Whoops ... forgot to get the error returned before it turned 10am ... I can post it tomorrow
I think the database is being locked, but unfortunately I know very little about MS SQL server
The people who's database we are connecting to are not being helpful ... I was hoping I could get some suggestions on what would be the cause.
Anything you can suggest would be a huge help! Thanks!
- Joe
We are having a problem with Query Analyzer not connecting to SQL Server anymore. Sometimes it will, sometimes it won't. Sometimes when it does, then you click on the databases drop-down, it may take a long time to return. Likewise, it may take a long time to open the object browser, or it may open without the database info but with just the "Common Objects" info.
All of this used to work just fine.
HELP
Simple version first:
The basic question is:
what would cause? this query takes minutes or times out:
select xx,xx,xx,xx
from view_xxx
where field in
(
select distinct xxx from table inner join table.....
)
the inner query
select distinct xxx from table inner join table..... takes less than a second
then if I take the results of that query and do this:
select xx,xx,xx,xx
from view_xxx
where field in
(
52193,23523,25233... and so on
)
this later query takes 2 seconds... What is the difference? For more details see the detailed version.
-Robert
now a more details version:
On a SQL2000 machine...
What would cause this query to take forever, or even timeout:
Select distinct VTR.Control_ID from View_ToolResults VTR
Where VTR.Control_ID in
(
------ NOTE INNER QUERY TAKES LESS THAN 1 SECOND
SELECT ORD.CONTROL_ID
FROM Orders ORD
INNER JOIN Providers PR ON PR.SUBJECT_ID = ORD.SUBJECT_ID and PR.SV_OFFICE_ID = ORD.SV_OFFICE_ID and PR.Source_ID = ORD.Source_ID
INNER JOIN VisitTypes VT ON ORD.VISIT_TYPE_ID = VT.VISIT_TYPE_ID and ORD.Source_ID = VT.Source_ID
INNER JOIN HealthPlans HP ON HP.MARKET_ID = ORD.MARKET_ID and HP.Source_ID = ORD.Source_ID
INNER JOIN HPGroups HPG ON HP.MARKET_ID = HPG.MARKET_ID AND HP.SOURCE_ID = HPG.SOURCE_ID WHERE (ORD.Status is not null AND ORD.Status <>'E')
AND HPG.GR_ID in
(
SELECT GR_ID from UserHPGroups Where User_ID= 7
)
AND ORD.RELEASED_DATE between '01/01/2001' and '02/11/2008'
and ORD.RECEIVED_DATE between '07/01/2007' and
'12/31/2007' and ORD.MARKET_ID= 2699431
)
as mentioned above the inner query takes less than 1 second. So if I take the results from that query and do a select like this:
Select distinct VTR.Control_ID from View_ToolResults VTR
Where VTR.Control_ID in
(55836
,55631
,55331
,50992
,51101
,51196
,51500
,52269
,52366
,52384
,52429
,52518
,53117
,53993
,53994
,53995
,54027
,54128
,54192
,54264
,54291
,54292
,54324
,54379
,54578
,54706
,54917
,52622
,54354
,55965
,55410
,55461)
It takes 2 seconds.....
WHAT!!!!!>???!?!?! i don't understand... what is the difference?
The field in all cases is declared as Numeric(10,0)... I've tried to convert to int. I've tried to do temp tables... it doesn't help.
Hi,I am trying to update a sql server 2000 database breaking up 1 huge confused table into several smaller tables to allow for more flexibilty.I want to use the "insert into" statement to create 2 new tables from the one old table. Old Table - structureCommercialProperties Cols: ID(identity) - PropName - PropAddress - Pic1 - Pic2 - Pic3New tablesPropertyCols: ID(doesn't have to be same as original id) - PropName - PropAddress PropertyImagesCols: ID - PropID(from new Property table ) - PicFile ----------------------------I know this won't work but it is to show what I want to do. I want to cycle through all the CommercialProperty rows and add the values to the new tables.I just need it to run in Query Analyzer declare @pic1, @pic2, @pic3 as varchar(255) declare @newID as integer declare @PropName, @PropAddress as varchar(255) select @PropName=PropName, @PropAddress =PropAddress , @pic1=Pic1, @Pic2=Pic2, @Pic3=Pic3 from CommercialPropertiesinsert into Property (PropName, PropAddress) values (@PropName, @PropAddress)SELECT @NewID AS [SCOPE_IDENTITY]how do I use the new Identity and to add the existing images details into the PropertyImages tableinsert into PropertyImages (PropID, PicFile) values (@NewID, @Pic1(from the original query)insert into PropertyImages (PropID, PicFile) values (@NewID, @Pic2)insert into PropertyImages (PropID, PicFile) values (@NewID, @Pic3) go to next CommercialProperty Thanks for your helpP
View 2 Replies View RelatedHello,
Here is the scenario.
I'm using SSIS and SQL 2005
I have two dtsx packages. Lets call them TESTA.dtsx and TESTB.dtsx. I've enabled using XML package configurations on both.
TestB.dtsx pulls some data from a table and writes it to a flat file.
TestA.dtsx calls TestB.dtsx.
For the test I changed the TestB fileoutput connection string to save to a different location.
When I run TestA.DTSX using DTEXEC. TestB's configuration file is not being used.
How do I kick off TestA.dtsx and specify a package configuration for test b?
I tried manually copying the Config parameters from TESTB's config file to TESTA.
When I run TESTB directly from DTEXEC and pass in it's config file as an argument it runs fine.
Dear Friends,
I am in problem & have to solve one query.
I have a one table with the employee time in & time out data, employee can go out & come in fequently in a day.
I want to know that how much time every emp have attend in the company per day.
Kindly, do reply as soon as possible.
I am enclosing data defination in txt file along with the data in the MS Excel file.
Thanx ....
Hi All,
I have two tables in my database.
I want to insert date and no.of hours worked on that day for a particular project.
So in a week if end user enters 7 dates and hrs for each day......
i have to insert those values into the table against one project only.
Can any one please help me out how to run insert query for 7 times (in a week) with different parameters
Thanks,
Praveen
I have been attempting to build a search engine that searches a database full of invoices. The user will enter in a invoice number and then the results will be returned if the exact invoice exists and if the record belongs to the user.
My first attempt has involved the use of a SQL Datasource to pass a stored procedure based on what is in the search textbox and the information in the cookie about the user. I was hoping that on the btnsearch.click event that I could somehow return the stored procedure in the SQL DataSource. My next step was to make the result (if exists) part of the query string like this:
Response.Redirect("~/Invoice.aspx?Invoice=?"+ SQLRETURNEDVALUE)
I'm looking for any help to a possible way to use my idea or any other ideas to get a invoice number and place it into a query string so that the result can be fetched on the next page. Thanks
Hey guys, could somebody pls provide me with an easy example as to how you would return multiple strings from a stored proc? Even a link to a decent tut would be great!
Muchos gracias!
Hi again,
I'd appreciate advice on the following. Thanks very much!
Given this Table:
family|product|type
fam1 |abc |XX
fam1 |def |YY
What query would return this?
Family|Type1|Type2
fam1 |xx |yy
--PhB
If exists (select fieldID from #tmploginfo where status <> 0
group by fieldID
having count(*) > 0)
begin
backup log rdb to disk = N'C:
db1.trn'
End
I want to iterate this query using a loop as many as 5 times max.
I am trying to write a SQL DataSource Statement that will do the following:
Select the Distinct Dates, count up the number of rows with that date
So for example:
Date Number with that Date
12/12/2007 3
14/12/2007 2
Database:
12/12/2007 Content 1
14/12/2007 Content 2
12/12/2007 Content 3
14/12/2007 Content 4
12/12/2007 Content 5
Need help to create a query to count how many times over a specified time the tag(s) equal the value of 1.
Thanks
Gary
We have a view with many left joins. The original creators of this view might have been lazy or sloppy, I don't know. I have rewritten the query to proper inner joins where required and also nested left joins.
So rather then the following exemplary fragment
select <many items>
from A
left join B on B.id_A = A.id
left join C on C.id_B = B.idthis now looks like
select <many items>
from A
left join (B
join C on C.id_B = B.id
) on B.id_A = A.id
Compilation time of the original view was 18s, of the new rewritten view 4s. The performance of execution is also better (not counting the compile of course). The results of the query are identical. There are about 30 left joins in the original view.
I can imagine that the optimizer has difficulty with all these left joins. But 14s is quite a big difference. I haven't looked into detail in the execution plans yet. I noticed that in both cases the Reason for Early Termination of Statement Optimization was Time Out.
Hi,
I apologize for the long post but I am trying to give as much information as I can about the steps I've taken to troubleshoot this.
We have a stored procedure that builds a sql statement and executes it using the Execute command. When I execute the stored procedure through query analyzer it takes close to 5 seconds to execute. When I print out the exact same statement and execute it directly in query analyzer as "raw sql", it takes 0.5 seconds - meaning it takes 10 times longer for the code to execute in the stored proc. I altered the stored proc to execute the printed sql instead of building but it still takes the full 5 seconds and there were no changes in the execution plan. This makes me confident that the issue is not caused by the dynamic sql. I've used with recompile to make sure that the stored procedure caches the most recent execution plan. When I compare the execution plans, the stored proc uses a nested loop whereas the raw sql statement uses a hash join. Seeing that, I added the hash hint to the stored proc and doing so brought down the execution time down from 5 secs to 2 secs but still the raw sql statement uses a clustered index whereas the stored proc uses a non-clustered index and that makes the statement 4 times slower. This proves how efficient clustered indexes are over non-clustered ones, but it doesn't help me since, as far as I know, I can't force SQL Server to use the clustered index.
Does anyone know why sql server is generating such an inefficient execution plan for the stored proc compared to the execution plan that it generates when executing the raw sql statement? The only thing I can think of is that some stats are not updated and that somehow throws off the stored proc. But then again, shouldn't it affect the raw sql statement?
Thank you,
Michael Tzoanos
I hope I am not asking about something that has been done before, but Ihave searched and cannot find an answer. What I am trying to do is torun a query, and then perform some logic on the rowcount and thenpossibly display the result of the query. I know it can be done withADO, but I need to do it in Query Analyzer. The query looks like this:select Varfrom DBwhere SomeCriteriaif @@Rowcount = 0select 'n/a'else if @@Rowcount = 1select -- this is the part where I need to redisplay the resultfrom the above queryelse if @@Rowcount > 1-- do something elseThe reason that I want to do it without re-running the query is that Iwant to minimize impact on the DB, and the reason that I can't useanother program is that I do not have a develpment environment where Ineed to run the queries. I would select the data into a temp table, butagain, I am concerned about impacting the DB. Any suggestions would begreatly appreciated. I am really hoping there is something as simple as@@resultset, or something to that effect.
View 6 Replies View Related