Using Microsoft SQL Server Management Studio 2008. I have done a simple transaction: BEGIN TRAN SELECT ko.ID, os.ID AS ID2 FROM table_a AS ko JOIN table_b AS os ON os.ID=ko.ID WHERE (ko.the_date IS NOT NULL AND os.the_date IS NULL); UPDATE table_b SET the_date=ko.the_date FROM table_a AS ko JOIN table_b AS os ON os.ID=ko.ID WHERE (ko....
When I use FOR XML PATH('') in the following query, the role values come back fine but for each result the FirstName value is <Unknown(-16)>. Here is the query: SELECT role, STUFF((SELECT ','+FirstName FROM Employees WHERE role = 'Engineer' FOR XML PATH('')),1,1,'') AS names FROM Employees GROUP BY role I have tried to find this erro...
I am facing a conceptual problem that I am having a hard time overcoming. I am hoping the SO folks can help me overcome it with a nudge in the right direction. I am in the process of doing some ETL work with the source data being very similar and very large. I am loading it into a table that is intended for replication and I only want...
Does anybody know what the recommend method is to copy data from one table to another using a DTS? Should we use the standard Insert/Select statement or a DTS transformation between two connections? Which one is faster? Has anybody done every any performance test on this? I am using SQL Server 2000 right now, but would also like to know...
I am doing research for an existing system that has SQL Server as a backend. We're starting to store ad-hoc item title data in multiple languages (including Russian language in Cyrillic characters and other European languages). My question is about sorting this data, what are my options for changing the sort order on demand without chang...
UPDATE : The issue was col1 was hiereachyid type and even a select didnt work for it. Hi all, I am getting this error - Objects exposing columns with CLR types are not allowed in distributed queries. Please use a pass-through query to access remote object '"RemoteDb"."dbo"."RemoteTable"'. I have already setup the linked server ...
I'm using this query to find some queries that have been running for a long time: SELECT sqltext.TEXT, req.session_id, req.status, req.command, req.cpu_time, req.total_elapsed_time FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext order by req.total_elapsed_time des Currently, we have some xp_cmdsh...
I'm doing joins incorrectly, and haven't figured out the proper way to get the data I need given the 3 tables below (Customer, SalesHeader, SalesDetail) I'm trying to get line items with 1 Customer per line and the sum of all Sales Details that are in GL Acct 4000 and all Sales Deails in GL Acct 5000. There are many more columns, and se...
I need to cross reference 2 tables. within tb1 is booking_ref, investor within tb2 is booking_ref, investor, cost The trouble is if there in no cost, no record is created in table 2 So I have the following query... SELECT tb1.booking_ref, tb1.investor, tb2.cost FROM tb1, tb2 WHERE tb1.booking_ref = tb2.booking_ref AND ...
SELECT tb1.booking_ref, tb1.investor, tb2.cost, tb3.product FROM tb1, tb3 LEFT JOIN tb2 ON tb1.booking_ref = tb2.booking_ref AND tb1.investor = tb2.investor AND tb1.investor = '12345' WHERE tb1.location = tb3.location The above query errors because of the references to tb3 - it works great without them. Does anyone...
I use sql server 2008 R2 as a data store. Until now on the test machine I had the english version of the software and used to make queries formatting the datetime field as fromDate.ToString("MM/dd/yyyy"); now I have deployed the database on another server which is in the italian language. I shall change the format in my code to fro...
I am using SQL Server 2005. SEE END OF THIS POST REGARDING BAD TABLE DESIGN. I have two columns. I would like to group by the first column with respect to the order of the second column. Microsofts documentation states that GROUP BY clause does not care about order, how can I enforce this?? Here is my pseudo query: SELECT col_1, ...
In SQL Server, how many transactions will this produce? DECLARE @deleted BIGINT SET @deleted = 100000 WHILE @deleted = 100000 BEGIN DELETE TOP(100000) FROM MYTABLE WITH (ROWLOCK) where Col1 = 7048 and COL2 = 39727 and Col3 = 0 SET @deleted = (SELECT @@ROWCOUNT) END If I cancel after running this for 10 minutes will it need to roll bac...
I've taken over an application that has a SQL backend. There are multiple tables, but the two that I'm concerned about are these: QAProfile --------- ProfileID <pk> -int ProfileName SecurityGroups -varchar(max) SecurityGroups -------------- GroupID <pk> -int GroupName My issue is that the the SecurityGroups field is a comma delimit...
Can anyone find my error in this query? I'm using sql server 2000 and I want to update all entries in the CostEntry table to the corresponding value in the ActiveCostDetails table. The where clause DOES work with a select statement. UPDATE CostEntry CE INNER JOIN ActiveCostDetails As AD ON CostEntry.lUniqueID = ActiveCostDetails....
I have a SQL Server 2005 database. I am logging data to a table. I want to prevent the table data from getting too big. How can I limit the size of the table to x number of rows, and keep logging? I want the oldest rows to drop off. ...
We all know that Linq to SQL (and SQLmetal/exe) is really a "prototype" quality product (it's missing basic features like schema "refresh" and detection of null columns with default value). Is there a way to automatically create my .dbml (similar to LINQ to SQL or SQLmetal) AND have it detect NOT NULL columns that have a default value? ...
I have a SQL Server 2005 database in which I have some tables contain Arabic text. The datatype for those fields is NVARCHAR(n). The Arabic text inside the table is appearing properly, and when selecting, they appear properly. Th problem is that searching for Arabic text results in 0 rows. select * from table_name where name='arabic_...
Hi I am using this script in the process of weekly maintenance, suggest best approach/scripts to do shrinklog. Currently am getting an error with the below script declare @s nvarchar(4000) set @s= ' if ''?'' not in (''tempdb'',''master'',''model'',''msdb'') begin use [?] Alter database [?] SET...
There is a problem with sqldatareader when the condtion in sql command is arabic like this: select user_name from users where typ=N 'arabic text' This does not retrieve any data although there is a user name which has this type so can you help me please ? thank ^_^ ...