hi guys, I'm sorry that I can't find a better title of my question. Not lemme describe it in detail. I have 4 database which are a, b, c and d. Database a have all table's that appear in b, c and d, and they have the same structure with the same constraints(pk, fk, default, check). b, c,d just have some tables that appear in a. Now the...
I've got a query I'm working on and I want to increment one of the fields and restart the counter when a key value is different. I know this code doesn't work. Programmatically this is what I want... declare @counter int, @id set @counter = 0 set @id = 0 select distinct id, counter = when id = @id then @counter +...
I have an interesting case of results sets returned by SQL Server being different depending on whether a query is executed locally or remotely. In essence, if I execute the following query: SELECT p.ID AS Id FROM csdb.users.People AS p LEFT JOIN csdb.users.Accounts AS a ON p.ID = a.IdentityId then the left join is performed, and I o...
SQL Server 2008 - I want to concatenate four columns into delimited values, but I want them to be ordered alphabetically. Is this possible? *UPDATE:*More info... This will be used on approx 700k-1M rows per day in an ETL job via SSIS. If there is an easier way to do it within SSIS, please let me know (script task, etc). It could also be...
I have the following Db and query. The query takes two parameters: the sort column and the direction. However, I have to add custom sorting to the query (based on the Fuji should come first and Gala second, etc.). This part also work but it create duplicated code in my query. Because of that, I am pretty sure people will not let me check...
Let's say I have many sql server databases which have exactly the same schema. Is there any place where I can write a stored procedure and apply for all databases? If I create the stored procedure for each database, when needed, I have to update them all. ...
Hello everyone, I am using SQL Server 2008 Enterprise on Windows Server 2008. I want to select result from top 11 to top 20 (e.g. I am only interested in the 11th to the 20th result). Any ideas how to write this query in tsql efficiently? thanks in advance, George ...
I'm trying to return the SCOPE Identity of the inserted record in my stored procedure @temp_id, but how can I bind it to a label e.g <asp:ControlParameter ControlID="ASPxLabel_RequestTemp" Name="temp" PropertyName="Text" Type="Int32" /> its not working Thanks ...
Here is the question: +--------------+ Ships | +--------------+ Duke of north | ---------------+ Prince of Wales| ---------------+ Baltic | ---------------+ I need to replace all characters between the first and the last spaces (excluding these spaces) by symbols of an asterisk (*). The number of asterisks must be eq...
Hello i have table sitizen(id_citizen,name,sname,age,id_city) i try do query select a.name_city,b.name,b.age from city a,citizen b where a.id_city = b.id_city and b.name = 'DAVE' order by b.age desc expect result like this - NY | DAVE | 65 - NY | DAVE | 12 - NY | DAVE | 3 but see result what i can't anderstand - NY | DA...
Hi all, I created an excel sheets in Excel 2007. Then, I somehow created a linked server for this excel document in SQL server 2008. I try to access the data from this linked server, but it is giving two types of errors 7303 and 7399. I also tried to login to the SQl server as admininstrator, but its still giving these errors. I thi...
I have the following code for handling phone numbers such as country code for Australia +61, 61, 001161 etc. The problem that I have I can't insert any CASE statement anymore under: CASE WHEN LEFT(@BPartyNo, 4) = '+610' It said that Case expressions may only be nested to level 10 How do I streamline this TSQL so I can put more CASE? ...
Sorry because this is a noob question. I'm new with MySQL: I wrote a query like this: SELECT u.userid, u.alias, g.company_name, v.endtime - v.begintime AS duration, u.status, u.service_starttime, u.service_expiretime, v.begintime, u.email FROM company_users c, company_groups g INNER JOIN user_info u INNER JOIN vfon_l...
I'm using SQL-Server 2005 Standard. I have Users table with following columns: userID(int), userImage(varchar), userText(varchar), userLastVisit(smalldatetime), isActivated (bit), userHobby1 (bit), ..... userHobby10 (bit) userCharacteristic1 (bit), ..... userCharacteristic10 (bit) I did 6 queries to select users that haven't been o...
What's wrong with this T-SQL : DECLARE @temp TABLE(ID INT IDENTITY,[Value] VARCHAR(100)) SET @temp = dbo.[fnCSVToTable](',2,3') ...
I have a legacy stored procedure that returns a large resultset. The problem is that the resultset is too large to get all at once and then do paging and sorting on the web side. Without changing the stored procedure is it possible to wrap it in some other sql code to get only certain results back in a fast manner. Something like: EX...
I am receiving the error SQL0104N An unexpected token "," was found following "select .loan_number". Expected tokens may include: "". SQLSTATE=42601 IF OBJECT_ID('tempdb..#Temp1') is not null begin drop table #Temp1 select * into #Temp1 from openquery(LnkServer, ' Select X.loan_number, X.ls_code, x.ls_actual_completion_date x.ls_...
Hi everyone, I have a many-to-many relationship, in which I query all M which have a specific N, for example: SELECT M.* FROM M INNER JOIN ManyToManyTable ON M.Id = ManyToManyTable.M WHERE ManyToManyTable.N = @Id Or: SELECT M.* FROM M INNER JOIN ManyToManyTable ON M.Id = ManyToManyTable.M INNER JOIN N ON N.Id = ManyToManyTable.N WHE...
I recently created a couple inline table-valued UDF's and then referenced them in a couple views using Cross Apply in one case and Outer Apply in another case. After I got it working and tested, I thought it was a pretty cool use of UDFs and Cross/Outer Apply's. But then it occurred to me that I probably could have done the same thing us...
Hi guys I have a table with many URLS like www.topshop.com www.shopbop.co.uk http://www.magickingdom.net http://www.asos.co.uk UPDATE Needs to be able to handle this URL as well topshop.co.uk I want to be able to strip out the fluff such that it returns only topshop shopbop magickingdom asos Do I have to write a manual excepti...