Hi, How to use CREATE LOGIN statement on SQL Server 2005? I was trying nearly everything, with commas, without them, with strings, without them etc. CREATE LOGIN @loginame WITH PASSWORD = 'pass', DEFAULT_DATABASE='dbname' DEFAULT_LANGUAGE='us_english', CHECK_POLICY= OFF; I always get below error: Incorrect syntax near the keyword '...
In a SQL Server database I've two tables: Job and JobKeyword. Each Job has JobKeywords. I'm trying to learn how to use Full Text Search. How can I get a list of all jobs in the databases, ordered by shared keywords between the search query and the jobs? ...
The following query does not work, and produces an error: A transport-level error has occurred when receiving results from the server SELECT * FROM table1 a, table2 b, table3 c WHERE a.location = b.location AND b.location = c.location AND a.id = c.id AND a.entry = ''34690'' Although this ...
Hi, I have the following query: WITH cte AS ( SELECT windowId, frameIndx, elemIndx, comment, ROW_NUMBER() OVER (PARTITION BY frameIndx ORDER BY elemIndx DESC) AS rn FROM dbo.translations WHERE windowId = 1 AND frameIndx IN ( SELECT indx FROM...
What's going on in the following code after the View is created? Can you give me any thoughts or path to follow? This code is taken from here. /****** Object: View [dbo].[vProductImages] Script Date: 04/28/2008 16:59:05 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[vProductImages] AS SELECT dbo....
Hi All, We have changed some base functions we use in an application and I am trying to figure out what the best way would be for me to find and replace the existing function with the new one? The input / outputs don't change, just the schema and some of the internal logic. Is there a good approach to finding the objects (views, proc...
Invoice ID, PO Number and dueDate are shown in duplicates. TotalPrice is an alias (It should be Unit Price, total price is a mistake, so assume it Unit Price not total price) TotalShippingPrice shows the shipping price that was associated with the InvoiceID/PONumber, for every invoiceID/PONumber there will be single shipping price. Sa...
My understanding is that when NOLOCK is used in SELECT statement, it could read uncommitted / dirty rows as well. But I wanted to take advantage of NOLOCK hint on table so that my SELECT query will run fast. Now, does NOLOCK on table but along with "SET TRANSACTION ISOLATION LEVEL READ COMMITTED" give me NOLOCK advantage and faster SE...
The way i designed my working sheet doesn't help me to calculate working hours easily. The output in snapshot has been gathered from multiple tables. Don't worry regarding setDate and timeEntered formatting. SetDate represents working day. tsTypeTitle represents type of shift, is it lunch time, etc. timeEntered represents the actual...
At my company we store the information gathered from our site wide error template, into the database, but because of the format of the error.datetime it is making hard for me to do any sql queries for different date ranges. Has anyone used some t-sql or coldfusion code to convert it to a mm/dd/yyyy format? Here is an example of the fo...
(I looked for an answer, but nothing really solved my issue so far.) I want to keep a table up to date with a file directory tree within Microsoft SQL Server 2005. I do the norm now: insert into #resultTable (Path) exec master.dbo.xp_cmdshell 'dir/s/b G:\FileLibrary' but this takes about 4 1/2 minutes to load each time (not to mentio...
I have a table that originates in an old legacy system that was case senstive, in particular a status column where 's' = 'Schedule import' and 'S' = 'Schedule management'. This table eventually makes its way into a SQL Server 2000 database which I can query against. My query is relatively simple just going for counts... Select trans_typ...
I'm trying to insert trailing spaces into a VARCHAR(50) column and the SQL insert seems to be cutting them off. Here's my code: create table #temp (field varchar(10)); insert into #temp select ' '; select LEN(field) from #temp; Unfortunately, this returns a length of zero, meaning the ' ' was inserted as a ''. I need a blank space to ...
Lets say I have the following table User_ID Manager_ID --------------------- Linda Jacob Mark Linda Kevin Linda Steve Mark John Kevin Basically the requirement is to pull all the managers under the user_id you are searching for. So for instance if I send in 'Linda' then it should retur...
id1 id2 year State Gender ==== ====== ====== ===== ======= 1 A 2008 ca M 1 B 2008 ca M 3 A 2009 ny F 3 A 2008 ny F 4 A 2009 tx F select state, gender, [year], count (distinct(cast(id1 as varchar(10)) + i...
I have some legacy code I'm going through, and i just found a join with two "on" clauses... select * from table inner join table3 inner join table2 on table3.key = table2.fkey on table.key = table2.otherkey What does this kind of join mean? (It currently works so it's not a syntax error) (Edit: Fixed the code, was missing a join) ...
I getting this error while developing stored procedure "Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict." statement is like this Select City COLLATE DATABASE_DEFAULT AS Place, State, Country FROM DEPT1 UNION ALL Select '' AS Place, 'Arizon...
I must update table, and I can't use else if in tsql. Is it exists? Status = ( CASE WHEN (Status in (0)) THEN 0 ELSE IF Status in (1) THEN 7 ELSE Status END ...
How do I pass a dataset object to a stored procedure? The dataset comprises multiple tables and I'll need to be able to access them from within the SQL. ...
--- This is the content of the XML file or consider as XML variable. From above xml file , i want the data like .. id name desc parentid 1 serach Search NULL 2 Search By Name SearchName 1 3 Search By ID SearchID 1 How can we get with single SQL statement in SQL serv...