I have 3 tables called: Applications (id, name) Resources (id, name) ApplicationsResources (id, app_id, resource_id) I want to show on a GUI a table of all resource names. In one cell in each row I would like to list out all of the applications (comma separated) of that resource. So the question is, what is the best way to do this ...
As we all know, CTEs were introduced in SQL Server 2005 and the crowd went wild. I have a case where I'm inserting a whole lot of static data into a table. What I want to know is which of the following is faster and what other factors I should be aware of. INSERT INTO MyTable (MyField) VALUES ('Hello') INSERT INTO MyTable (MyField) VAL...
I'm considering various ways to partition my data in SQL Server. One approach I'm looking at is to partition a particular huge table into 8 partitions, then within each of these partitions to partition on a different partition column. Is this even possible in SQL Server, or am I limited to definining one parition column+function+scheme...
Good day! I need help in writing a query.. I have records in a table below.. The condition would be no records should be displayed if the succeeding records' new_state was repeated from the previous records(new_state) and if it is changed in the same date.. here record_id 1 has gone through the ff states: 0->1->2->1->3->4->3 in the sam...
Hey everyone, Any one know a good way to remove punctuation from a field in SQL Server? I'm thinking UPDATE tblMyTable SET FieldName = REPLACE(REPLACE(REPLACE(FieldName,',',''),'.',''),'''' ,'') but it seems a bit tedious when I intend on removing a large number of different characters for example: !@#$%^&*()<>:" Thanks in advance ...
This is my query SELECT lbi.ItemGrpId,igm.ItemGrpName,lbi.Qty, 'BidAmt' = CASE WHEN lbi.ItemGrpId IN ( SELECT DISTINCT vbd.ItemGrpId FROM tbVebdorBidDetails vbd WHERE vbd.BidID=139 AND vbd.VendorEmailID='[email protected]' AND vbd.UpdatedDateTime IN ( SELECT MAX(UpdatedDateTime) AS [MAXMIN] FROM tbVebdorBidDeta...
We have some legacy stored procedures that use the now deprecated feature of SQL Server that allowed you to create multiple versions of a procedure within a procedure. For instance we have.. [up_MyProc] [up_MyProc];2 You can still create these "versions" by appending the version on the end of the name. However without dropping the p...
Is there a way to find a usage of a function in SQL server 2008? ...
"The query has been canceled because the estimated cost of this query (1660) exceeds the configured threshold of 1500. Contact the system administrator." I am getting error as above on live while running one of the stored procedure threads where parameter contain XML variable. I have checked the configuration value of QUERY_GOVERNOR_CO...
I have two tables: **Product** ID Name SKU **Brand** ID Name Product table has about 120K records Brand table has 30K records I need to find count of all the products with name and brand matching a specific keyword. I use freetext 'contains' like this: SELECT count(*) FROM Product inner join Brand on Product.B...
I am designing a standard ASP.Net site with a SQL database. I have a database schema and During the tests I am changing data types amongst other tasks and the data contained inside really is not that important. I keep getting errors as the old data does not match the new rules. This is not important and I am happy to clear everything b...
i have a db with about 15 tables. 14 of them work fine . .suddenly doing "select * from table" on one of then (which usually takes < 2 seconds to return, now doesn't seem to return ever. (i have been waiting over 8 minutes). what are the best next steps to debug what is going on? i noticed this as my asp.net website started timing out...
I'm working on a legacy project, written for the most part in Delphi 5 before it was upgraded to Delphi 2007. A lot has changed after this upgrade, except the database that's underneath. It still uses MS-Access for data storage. Now we want to support SQL Server as an alternate database. Still just for single-user situations, although m...
The following queries are taking 70 minutes and 1 minute respectively on a standard machine for 1 million records. What could be the possible reasons? Query [01:10:00] SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_PartitionTest( CASE WHEN sys.fn_cdc_increment_lsn(0x00)<sys.fn_cdc_get_min_lsn('dbo_PartitionTest') THEN sys.fn_...
I'm new to free-text search, so pardon the newbie question. Suppose I have the following full-text index: Create FullText Index on Contacts( FirstName, LastName, Organization ) Key Index PK_Contacts_ContactID Go I want to do a freetext search against all three columns concatenated FirstName + ' ' + LastName + ' ' + Organi...
I have the following columns in the database. AgreedStartDate, ActualStartDate. I need to apply the following logic: When the AcualStartDate is within the SAME business week as the AgreedStartDate, then it is 'inside' the SLA. If this is not the case then it is 'outside' the SLA. If either of the two columns is NULL, then it is 'inco...
Hi, I need to combine the following two SQL statements into one. Any help is greatly appreciated. Thanks. SELECT C.*, M.members_Email FROM tbl_Campaigns C JOIN tbl_Members M ON C.campaign_MemberId = M.members_Id WHERE C.campaign_MemberId = @userID ORDER BY C.campaign_Key DESC SELECT COUNT(*) FROM tbl_CampaignRecipients WHERE reci...
I am using below sql statement to create shared folder with permission EXEC xp_cmdshell 'net share BackupSharedFolder=D:\testshared /REMARK:"test shared"' It is working fine with create shed folder. then i will use below sql command to remove shared EXEC xp_cmdshell ' net share BackupSharedFolder /delete"' Above is also working fine ...
What's the best practice for storing a large amount of image data in SQL Server 2008? I'm expecting to store around 50,000 images using approx 5 gigs of storage space. Currently I'm doing this using a single table with the columns: ID: int/PK/identity Picture: Image Thumbnail: Image UploadDate: DateTime I'm concerned because at arou...
I have a couple stored procedures that run for about 2-3 minutes a piece (lots of data). When I run the stored procedures inside SQL Server Management Studio, the queries run fine and return the appropriate data, however, when I run my SSRS Report, it errors out with "Object has been disconnected or does not exist at the server." Any su...