Suppose my database has 3 table A though C and I need to get the number of records in each. I could do it as individual selects like
SELECT 'A' AS 'Table', COUNT(*) AS 'Count' FROM A
SELECT 'B' AS 'Table', COUNT(*) AS 'Count' FROM B
SELECT 'C' AS 'Table', COUNT(*) AS 'Count' FROM C
or
SELECT 'A' AS 'Table', COUNT(*) AS 'Count' FROM A
UNION
SELECT 'B' AS 'Table', COUNT(*) AS 'Count' FROM B
UNION
SELECT 'C' AS 'Table', COUNT(*) AS 'Count' FROM C
Is there any problem in unioning statements like these? In reality there were over a hundred tables and the TSQL was generated by looping through SYSOBJECTS. I eventually used the first construct as the XML was simpler to generate.
I have a query which does 3 selects and Union ALLs each to get a final result set. The performance is unacceptable - takes around a minute to run. If I remove the Union All so that the result sets are returned individually it returns all 3 from the query in around 6 seconds (acceptable performance).
Any way to join the result sets together without using Union All.
Each result set has exactly the same structure returned...
Query below [for reference]...
WITH cte AS ( SELECT A.[PoleID], ISNULL(B.[IsSpanClear], 0) AS [IsSpanClear], B.[SurveyDate], ROW_NUMBER() OVER (PARTITION BY A.[PoleID] ORDER BY B.[SurveyDate] DESC) rownum FROM[UT_Pole] A LEFT OUTER JOIN [UT_Surveyed_Pole] B ON A.[PoleID] = B.[PoleID]
Hi. I’m having a conceptual problem with tracking sales vs. call center calls. Each record in the fact table represents a call to the call center. In this record are various facts and foreign keys that map to marketing campaigns, etc. The product sold ID is NULL on no sale, and filled in for a sale. When creating MDX to retrieve data by campaign, for example, to track the number of calls for a campaign vs. it’s sales, that works fine (because there’s a column labeled sale that’s either 0 or 1 for the sale and I just sum it). This way I get the conversion percentage of calls to sales.
But when creating MDX to track the product sold, as soon as I do a crossjoin on campaign and products sold, for example, I lose the total calls- the number of calls is the same as the sales (similar results as a SQL join). Am I doing something wrong or is it conceptually impossible to track this type of metric down to the product when there are NULLs in the fact table? I’ve tried converting the nulls to a NONE category, but that doesn’t stop the crossjoin from not giving me the desired results, plus I then have to filter out NONE as a product. Is that why a lot of sample warehouses have a separate sales cube?
I've got a performance issue. Here's in global what the sp (Let's call it SP_A) does. Step 1 Call a different SP (Lets call it SP_B) and store the output in a variable Step 2 SP_B runs a select statement that returns 1 value Step 3 SP_A uses this value as a parameter in a select statement. Step 4 The result of the SP_A is the result of the select statement (744 rows (always))
All tables used in SP_A and SP_B are temp tables. Total performance of SP_A is between 0.090 and 0.140 seconds.
The problem is that this SP is called 180 times from outside SQL server. That means that the total processing time is somewhere between 21 and 25 seconds.
When I move the entire processing to within SQL server I gain only 2 seconds. So I lose 2 seconds in connecting to the database 180 times.
Can someone give me some pointers on where to look for performance wins?
I have an old app that I'm trying to recode and improve performance.
From the start it makes three seperate calls to a db, checks to see if the record exists, if it doesn't it adds it, then takes the data from all three and inserts it into a final call.
Here is a quick example of the script
Select * from table1 where id = " & tempVariable If Not RS.EOF Then strTable1 = RS("SomeRec") Else RS.ADDNEW RS("SomeRec") = tempRec1 RS.UPDATE RS.Requery strTable1 = RS("SomeRec") End If RS.CLOSE
Select * from table2 where id =2 If Not RS.EOF Then strTable2 = RS("SomeRec") Else RS.ADDNEW RS("SomeRec") = tempRec2 RS.UPDATE RS.Requery strTable2 = RS("SomeRec") End If RS.CLOSE
Select * from table3 where id =3 If Not RS.EOF Then strTable3 = RS("SomeRec") Else RS.ADDNEW RS("SomeRec") = tempRec3 RS.UPDATE RS.Requery strTable3 = RS("SomeRec") End If RS.CLOSE
I am attempting to see if an application is calling a particular stored proc. i have the Profiler setup to only display the login and it is. I have all items set to display under Stored Procedures, but I can't see names of procedures in the Text Data.
hi all. when i worked with oracle before, it was very common to call a function from inside a s.procedure. now, someone talled me that in sqlserver this is not a best performance approach, and i should try to avoid this. is this true?
My Problem is that I don€™t know really what does a call means.
We got a SQL Standard Edition. Can I install as many instances I want in a Server? How many users can access the Server at the same time?
If I use SQL authentication, and two users use this logging information, how many calls do they made? 2 one per each user (PC) or just 1 one per logging.
If I have a client / server application, and the server access the SQL Server using SQL authentication. Can I have unlimited number of clients?
I'd like to keep state between calls to a UDF (mainly for caching purposes). I can shove an object into the appdomain using SetData and read it using GetData, but that requires the assembly to be set to UNSAFE. I'm confident I can secure the DB and the assembly fairly well, but I like defense in depth, and if there's another way to save state between calls to a UDF, I would prefer those.
Is there another way to store state between calls to a UDF, without putting data into DB tables or using things that will require the assembly to have such a wide permission set?
I need to develope a stored procedure (eventually called by a trigger) that creates a record in an event table for all the descendants of a drawing. There are 3 tables involved as example tables shown below:
DwgTable
DwgID (integer)--drawing record identifier
PrntDwgID (integer)--parent drawing record identifier (a previously defined DwgID from this table)
DwgEventTable
DwgEventID (integer)--record identifier
DwgID (integer)--value from DwgTable
EventID (integer)--value from EventTable
EventTable
EventID There are other fields in two of the tables and only the fields shown in the DwgEventTable, but only the fields shown are required for adding a record in the DwgEventTable for a new event in the EventTable. The problem is identifying all the DwgID's of the descendant of the DwgID where the EventID occurred. There may be 0 to N descendants in 0 to N generations. I need to add a record for the original DwgID and all the descendant DwgID's in the DwgEventTable for the event identified by EventID.
I could do this from the client side, but a better place would be from the server side. I need some clue(s) on how to start coding a recursive stored procedure in SQL Server 2005. From what I have read, you cannot create a managed code procedure that appends or updates records--if managed code can add/modify records then I can do the above with managed code procedure.
I am running the code below in a sql job. Exec (@SQL) errors every time but after 4 failures the job fails instead of trying @MaxRetries times. Any idea on why this is happening? Set @RetryCounter = 0 WHILE (@RetryCounter < @MaxRetries) Begin SELECT @error_value = 0 Exec (@SQL) SELECT @error_value = @@ERROR If @error_value = 0 Set @RetryCounter = @MaxRetries Else Begin WAITFOR DELAY '00:05:00' Set @RetryCounter = @RetryCounter + 1 End End
I have several SSIS Packages which are called by few different applications. I need to make packages Mutually Exclusive, so that If one is running; calls to any other Package should be put on wait till the running one has finished its execution. What are the best ways to do this ?
I've got a huge inefficiency in my code that I'm trying to fix. I’m coding in VB.NET using ASP.NET and an MSSQL 2000 server.
I’m working in a temporary table that has an identical layout as another, non-temporary table in my database. Once I get the temp table how I want it, I need to insert everything from that table into my main table. Before I can do that, however, I need to delete all the records in the main table with certain fields that match a record’s fields in the temporary table.
Right now, I have a method that builds one delete statement per record in the temporary table and then runs those statements on the main table. Since I’m dealing with the order of 50,000 records (at least) here, building and sending those statements to the server takes forever.
Is there a way I can accomplish the same thing without building and sending such a huge SQL call to the server? If so, how would I go about doing that?
Thanks in advance for whatever help you can give, -Starwiz
is it true that I will not be able to use the returned value from an sp that is called on every row from an oledb command transformation? I see all kinds of complaints on the web but cant determine if this would be a waste of time. I'd like to append the returned value (which is calculated and cannot be joined in the buffer) to the data on its way out of the transformation.
I am using internal activation on a queue to process the messages, should an error be encountered I call stored procedure A in the same database to log the error. Part of the processing in stored procedure A is a call to stored procedure B in another database (on the same server), however I have not been able to get this call to B to work. Currently I get the error "The server principal XXXXXX is not able to access the database YYYYYYY under the current security context".
I have tried various combinations (too many to remember) of database owners, roles and permissions as well as EXECUTE AS on both A and B and the Queue but none seem to work. Can anyone give me simple example of a setup which would allow this cross database call to work?
I am wondering if it is at all possible to keep an object in memory between calls to a SqlFunction or SqlProcedure.
Every time a row is inserted in to a table, i need to do some processing on the data. The processing becomes many times faster if I can do some preprocessing (the preprocessing is independent of the data inserted into the table). So basically, I want to save the result of the preprocessing, only do it one time, and save the result in a object in memory. Is this at all possible?
I'm having a terrible time trying to figure out how to do this, does anyone have experience?
My code works fine in the report viewer, but all I get from the deployment is #Error symbols. I've added my custom dll file to the config file and gave it full trust, as well as the URL of the web service I'm calling. I've even modified the rest of the codegroups within the file to FullTrust just in case.
I've also added the [assembly: AllowPartiallyTrustedCallers] to the AssemblyInfo class, and [WebPermission(SecurityAction.Assert)] to the constructor of my custom assembly no avail.
I'm at a complete loss now as to why my deployment cannot run this code. As far as debugging goes, Visual Studio is not located on the reporting server, nor is it an option to have it put there. I've been able to run the code with the web service lines commented out, but as soon as I put even the webservice.Credentials = CredentialCache.DefaultCredentials in I'm greeted with the nice and calming #Error.
I want to retrieve some parameter values from Reporting Services using GetReportParameters from
Microsoft.SqlServer.ReportingServices.ReportService2005. It works fine unless the parameter types are date/time. In such a case it seems as if the format of the parameter depends on the ReportServices installation:
Example: On a German installation the date/time format is "dd.mm.yyyy" but on an English installation the format is different (mm/dd/yyyy) (and of course the time part is also different).
Is there a way to determine the format to be expected? (... even in the case I've no knowledge about the server ReportingServices is running.)
I am trying to query a list in sharepoint using its lists.asmx webservice and in turn its GetListItems call. I currently have the call working using the following code in the Querystring for the report
This seems to work fine except that the list has some empty fields in the first record and for some unbelivable reason they (the empty fields) do not get returned as part of the dataset unless i put a value into them. I then came across a parameter for GetListItems called viewFields which is supposed to . Tehn sample Soap request is shown as follows
As i have a parameter for viewfields setup.... What value do i pass to it ??? My XML is in its infancy but i though something along the lines of the following would do as an input.
Also, maybe i am going the wrong way about this.Is there an easier way of defining the Parameters as part of the Querystring or do Parameters always have to be setup in the parameters tab (My Parameter values are static and will never change).
I have a link that inserts postid, catid, and postdate. I am trying to get count of catid(how many times its in the table) then display the number on a page. i have never tried this before. one more thing. Can you put a datalist inside a formview when the formview is databound already? Can someone help? Thank you.
I am trying to restore from a backup of an individual filegroup in SQL Server 7.0. I have backed up the individual filegroups, and I want to restore one of them (not the whole database). The process in EM is almost the same as restoring the entire database, only you choose the radio button 'Filegroups' and the files/filegroups you want to restore.
The restoration appears to complete sucessfully, but afterward, my database seems to be permanently loading. In other words, in EM the database is greyed out and you cannot use it anymore. It stays like this until you drop the database altogether. Is this a bug? Has anyone else had this problem or does anyone have experience restoring from filegroups sucessfully???
I am recieving complete row in one single column and I have pipe delimeter in this row . I want to retrieve the data in individual columns...Currently row is in one column
tblClients -------------------------------------------------- ClientID (varchar(3)) | ClientName (varchar(100)) -------------------------------------------------- 001 | First 002 | Second
tblProjects --------------------------------------------------- ProjectID (varchar(6)) | ProjectName (varchar(100)) --------------------------------------------------- 001001 | First Project1 001002 | First Project2 002001 | Second Project1 002002 | Second Project2
As you can see, the ClientID is the first 3 digits of the ProjectID, signifying that a certain project belongs to that client. I know it's a bad design, but I can't change the DB, I have to use it.
The problem is that I need to join the two tables based on the ClientID, but I don't know how to just retrieve the first 3 numbers in the ProjectID.
I need something like: tblClients INNER JOIN tblProjects ON tblClients.ClientID = tblProjects.First3DigitsOfTheProjectID
In previous versions of SQL Server you were able to execute single steps within a DTS package. It doesn't seemt to be possible in visual studio. Does anybody know otherwise?
I I work for a small business who has a somewhat successful ecommerce site. Our website is based on some VERY large DB's with 10's of millions of records, and 40+GB of images, so we choose to self host so we could easily manage the data. As things have grown and we've cobbled a fair amount of integration between the website databases and our internal systems. Fast forward 4-5 years, the website has grown to the point that self hosting is costing us a fortune in bandwidth costs, so I'm thinking of moving to a co-location facility. If I do that, I need to send the website tables along with it. Is there a replication stradagy that would allow us to have two independant DB's, both able to take updates that would sync with one another? My other thought is to spit the tables on two different DB's and let some of the data live in the co-location facility.. but I am worried about the performance impact for local users.
I have a SQL Server 2005 HTTP endpoint. I create a transaction scope in a client application and call two methods on that endpoint. Will the operations performed in the two methods, really, be in a transaction?
In other words, will SQL Server enlist in the ambient transaction even when I am accessing it through a HTTP endpoint?
How can I get the messages sent by the server while I'm executing a stored procedure via the JDBC driver?
I need to get my own debug messages (done through the print() function) and also standard server messages (such as "x row(s) affected" or results from SET STATISTICS TIME ON). Is this possible?
I developed one DLL in vb .Net which call two web services (sql endpoints) I wish to use this solution but for hundreds of servers (endpoints) I want to ask if I could do that with dynamic parameters passed to de DLL CALL in sql server clr.
Actually when I compile de DLL I need to have referenced (hardcoded) the webservices (endpoints) and when I need a brand new endpoint I need to reference it, compile the dll and release.
I cannot find how to invoke the service within the DLL with the namespace or webservice name as parameter.
Mi code to invoke now my referenced end point is: (note that my endpoint name is rperez)
Dim CallWebService As New rperez.IntelisisServiceEndPoint
I created a Switch statement below that surprisingly doesn't throw any errors and some of it actually works. The problem is that the function calls in the 3rd and 4th sections of it below (in bold) are not working.
=switch(HASONEVALUE('s1JudgeIds'[JudgeName]), values('s1JudgeIds'[JudgeName])<>"1 - All Judges" && values('s1Perm1'[Exit])<>"Still in Out-of-Home Care",CALCULATE(count(s1Perm1[entity_id]),FILTER(ALL(Time[ExitMonthCategory]),Time[ExitMonthCategory] <= MAX(Time[ExitMonthCategory]))), values('s1JudgeIds'[JudgeName])="1 - All Judges" && values('s1Perm1'[Exit])<>"Still in Out-of-Home Care",calculate([Numerator],all('s1JudgeIds'[JudgeName])), values('s1JudgeIds'[JudgeName])="1 - All Judges" && values('s1Perm1'[Exit])="Still in Out-of-Home Care",calculate([Numerator-stillincare],all('s1JudgeIds'[JudgeName])), values('s1JudgeIds'[JudgeName])<>"1 - All Judges" && values('s1Perm1'[Exit])="Still in Out-of-Home Care",calculate([Numerator-stillincare])
