How can I format a Mailing Address so that I always push all non-null rows to the top? That is, I want to convert an address from the structure below to a mailing address. Here is the structure: [Line1] [varchar](50) NULL, [Line2] [varchar](50) NULL, [Line3] [varchar](50) NULL, [City] [varchar](50) NULL, [State] [varchar] (2) NULL, [P...
How do I do a SELECT * INTO [temp table] FROM [Stored Procedure]? Not FROM [Table] and without defining [temp table] ? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine Trying the same, but using a stored procedure that returns data, is not quite the same. select * into tmpBusLi...
Hi, Ideally I want to do this: UPDATE TOP (10) messages SET status=10 WHERE status=0 ORDER BY priority DESC; In English: I want to get the top 10 available (status=0) messages from the DB and lock them (status=10). A message with a higher priority should be gotten first. unfortunately MS SQL doesn't allow an order by clause in the up...
What is the best practice for right-justifying a numeric in TSQL? I have to format a fixed length extract file and need to have the Numeric fields right justified. (I am using SQL Server 2005) I found this, which seems pretty straight forward. right(' '+convert(varchar(20),a.num),12) Here is the full Select statement sel...
I have the following XML that I'm running in SQL Server, and it breaks, why? declare @xml varchar(max) declare @hDoc int set @xml = '<transaction> <item itemId="1" value="Hello World" /> <item itemId="2" value="Hello &World" /> <item itemId="3" value="Hello <World" /> <item itemId="4" value="Hello >World" /> <item itemId="5" va...
I have 2 tables (srcTable1 & destTable) that have identical schemas. I am trying to copy all rows from srcTable to destTable and ignore the duplicates. I thought I could just add a WHERE clause with a subquery that would give me only the rows that aren't duplicates. However, it doesn't seem to work. I don't get any rows inserted or selec...
Using T-SQL I've found that I can't use 'ALTER INDEX' with the table/index values in variables without getting a syntax error. Is there some way this could be done ? I'm on SQL Server 2005. My code looks like this : DECLARE @TABLENAME VARCHAR(256) DECLARE @IDXNAME VARCHAR(256) DECLARE @SCHEMAID INT SET @TABLENAME = 'T1' SET @IDXNAME = ...
I have a question about Microsoft SQL Server 2005. How can I delete or select a row from a table that has a specific row number? ...
I have a string of email recipients in the format like this: DECLARE @recipients VARCHAR(MAX); .... PRINT @recipients; /* the result [email protected];[email protected];[email protected];... */ "SELECT DISTIECT ..." is a simple and powerful SQL statement, but it works against a table. Is there a simple way to select distinct r...
I have the following TSQL codes: -- 1. define a cursor DECLARE c_Temp CURSOR FOR SELECT name FROM employees; DECLARE @name varchar(100); -- 2. open it OPEN c_Temp; -- 3. first fetch FETCH NEXT FROM c_Temp INTO @name; WHILE @@FETCH_STATUS = 0 BEGIN print @name; FETCH NEXT FROM c_Temp INTO @name; -- fetch again in a loop END -- 4...
I am trying to add a column (MSSQL 2005) to a table (Employee) with a default constraint of a primary key of another table (Department). Then I am going to make this column a FK to that table. Essentially this will assign new employees to a base department based off the department name if no DepartmentID is provided. This does not work...
I'm getting the error: the multi-part identifier "IC.industry" could not be bound when making this SQL query from a JSP page via JDBC: select C.company, C.shname, C.fullname, count(d_to_c.designer) from companies C left join ind_to_c IC on C.company = IC.company left join d_to_c on C.company= d_to_c.company where IC.indust...
I've been using this for some time: SUBSTRING(str_col, PATINDEX('%[^0]%', str_col), LEN(str_col)) However recently, I've found a problem with columns with all "0" characters like '00000000' because it never finds a non-"0" character to match. An alternative technique I've seen is to use TRIM: REPLACE(LTRIM(REPLACE(str_col, '0', ' ')...
I have the following example table: Dt Value1 Value2 Value3 ... 2008-12-01 12:34:00 100.1 0.123 43 .... Is there any way by using TSQL to generate trend graphics as image such as jpg? Or do I need Reporting service to do it? I need to do it TSQL so that the daily trend images can be generated in a sche...
I can use msdb.dbo.sp_send_dbmail to send out email in html format. It is very nice for text only in terms of format. For example: EXEC msdb.dbo.sp_send_dbmail @recipients = @p_recipients, @subject = @v_subject, @body=@emailHTML, @body_format = 'HTML'; However, if I want to include images such as trend generated from data on ...
Is there to way write a T-SQL command to just make it sleep for a period of time? I am writing a web service asynchronously and I want to be able to run some tests to see if the asynchronous pattern is really going to make it more scalable. In order to "mock" an external service that is slow, I want to be able to call a SQL server with...
I have the following code: sql = "select distinct [name] from tblCustomers left outer join tblCustomerInfo on tblCustomers.Id = tblCustomerInfo.CustomerId where (tblCustomer.Name LIKE '%@SEARCH%' OR tblCustomerInfo.Info LIKE '%@SEARCH%');"; using (SqlCommand command = new SqlCommand(sql, Connection)) { c...
How do I get this batch of SQL to get to the RollBack Transaction part at the end? SQL just stops halts script execution on the bad line of code. I know I can use a try/catch construct but i'm more interested in how this was this handled before SQL added try/catch. BEGIN TRAN CREATE TABLE TempTable (c1 INT NULL) INSERT INTO TempTable ...
What is the largest size text usable variable in a MSSQL Stored Procedure. I'm seeing that largest size you can use is a varchar(8000). We can use ntext, text, etc. So does it require stitching varchars together? What about if I used a CLR Stored Procedure?? ...
Hi I am trying to convert a normal STP to a partially dynamic SQL I need the dynamic part to set value to a parameter, which I would later use in my non-dynamic part of the STP. when I print it in the inside of the dynamic @stmt it's OK but outside it is empty what am I missing? ------------------------------------------------------...