tsql

SQL Server types conversion

Possible Duplicate: sql server year EXACT duplicate of sql server year DECLARE @FINANCIALYEAR AS varchar(30) DECLARE @FINALFINANCIALYEAR AS int SELECT @FINANCIALYEAR=CONVERT(VARCHAR,YEAR(GETDATE())-2) + ', ' + CONVERT(VARCHAR,YEAR(GETDATE())-1) + ', ' + CONVERT(VARCHAR,YEAR(GETDATE())) set @FINALFINANCIALYEAR = ...

Can anyone explain how this constraint functions?

Can anyone explain how this constraint functions and how its affects insertion of records into this table? Running win sql 2k5. Having issue with trying to insert data. ALTER TABLE [dbo].[ws_shiptable] ADD CONSTRAINT [DF_ws_shiptable_ps_processed] DEFAULT (0) FOR [ps_processed] ...

UPDATE table from another table with case statement

Hi there! I'm currently having a problem. I need to update Table A from Table B based on this condition: If one record in Table A is null (ex. name) then update that record from Table B Here's my error driven script which I thought from my head. This is what I wanted to happen. UPDATE TableA SET NAME = ( CA...

Retrieve file list of an SQL server database which is offline.

Hi, I have some offline databases on a SQL server. I would like to know which files on disc are related to these databases. Is it possible to retrieve the file list of offline databases without taking them online first? cheers, Achim ...

Escaping command parameters passed to xp_cmdshell to dtexec

I am calling an SSIS package remotely using a stored procedure and a call to xp_cmdshell: declare @cmd varchar(5000) set @cmd = '"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe" /Rep E /Sql Package /SET \Package.Variables[User::ImportFileName].Value;c:\foo.xlsx' print @cmd exec xp_cmdshell @cmd This works fine, ho...

Execute stored procedure for all the result set without using cursor

If I want to execute stored procedure using the values returned from result set of a select statement. So number of times SP should get executed is equal to the number of result set from the select statement. Is there any other way than using a cursor to do the above? UPDATE Can anyone please give sample code with While loop at least? ...

Finding first search value across columns and retrieving column details it was found in

Hi, I was wondering if it was possible to determine from which column a coalesce value is drawn from? I have the following example data (actual years range from 1989 - 2010 in data, not shown for brevity) ID | 2000 | 2000 value | 2001 |2001 value | 2002 |2002 value | 2003 |2003 value | 2004 | 2004 value | 2005 | 2005 value...

Create T-SQL Constraint to prevent x amount of duplicate records in table?

In table A I have 2 columns: ID (int, PK) MaxUsers (int) In table B I have 2 columns: ItemID (int) UserID (int) The number of records in table A with matching ItemID's cannot exceed the MaxUsers value. Is it possible to write a T-SQL Table Constraint so that it's not physically possible for this to ever happen? Cheers! Curt ...

using generated values in instead of triggers

Hi, I have a simple table CREATE TABLE tItem (PK_Item INT ITENTITY(1,1) NOT NULL, ItemID VARCHAR(15) NOT NULL) where ItemID is a generated identifier, generally a number, but can differ by customer and other conditions. ItemID is a result of EXEC @TempID = pGeneralIdentifierRetrieve NULL, NULL, @ItemType currently, I have insertio...

How can I query 'between' numeric data on a not numeric field?

I've got a query that I've just found in the database that is failing causing a report to fall over. The basic gist of the query: Select * From table Where IsNull(myField, '') <> '' And IsNumeric(myField) = 1 And Convert(int, myField) Between @StartRange And @EndRange Now, myField doesn't contain numeric data in all the rows [it is o...

dbo.dbo is this valid?

Hello all, Is this valid dbo.dbo, if so what does it mean? I have spoted this on a query that someone else wrote: from dbo.tmp_BDCode aob inner join dbo.dbo.tmp_BDCode_03 ao Would it have an adverse effect on a query, because dbo just means its a global object. Thanks all for clarification on this. ...

SQL Query to get table name and number of rows based on column name"

Hello Everyone, I have declared a Cursor to get table names and no of columns in that tables based on column names.Please find the below query table name is not get inserted.Please suggest. Create table #t ( tabname varchar(500), NoOfRows bigint, ) Declare @Namee Varchar(500) Declare @GetName Cursor Set @Getname = Cursor for Sele...

Create a SQL table with a varying number of columns

I have two tables, say teacher and student. I want to build a third table called class. The class table will have one column for the teacher of the class. But I want too see if their is an elegant way to represent the students. My first thought is to have say 30 columns.. student1, student2 and have quids for each of these that tie back ...

Sql Pivot top N rows only

I have Following structure Col1 Col2 Col3 --------------- F P R1 F P R2 F P R3 F P R4 Col3 values can be any thing Now I want in following format only top 3 Col1 Col2 Res1 Res2 Res3 ------------------------------ F P R1 R2 R3 ...

Looking at SQL Server Execution logs ideally verbose for debugging

Hi, I was wondering if there is any way to check the execution logs of SQL server. I am not talking about the proprietary transactions logs or the error logs. But just the recently executed logs in a verbose fashion for debugging. For example, I have a web application that calls stored procedures, passes in parameters and populates gri...

Getting View's Column Description

Is it a way to retrieve the view's column description from the base table (fn_listextendedproperty)? The following syntax only list down the column name from the view SELECT * FROM sys.columns c JOIN sys.views v on c.OBJECT_ID = V.OBJECT_ID JOIN sys.schemas s ON s.schema_id = v.schema_id and V.NAME = 'v_P...

Call sp within another sp

I have an sp named 'FolderSttructure'.It returns the following result - FolderID 1 2 3 4 I have to call the above sp 'FolderSttructure' inside another sp named 'SearchFolder' and insert the result of first sp in to a temperory table. Is it possible? If yes how can we do that? ...

CRYPT_GEN_RANDOM strange effects

I want to randomly choose a value from a small range of integers (less than 200). As an alternative to SELECT RAND() I'm trying to use CAST(CAST(CRYPT_GEN_RANDOM(2) AS INTEGER) AS FLOAT) / 65535 but I'm getting some strange effects. For example: WITH Numbers (num) AS ( SELECT num FROM ( VAL...

How do I convert the following code to a SQL Server/T-SQL CTE?

I consider myself rather proficient with T-SQL and I'm usually able to optimize a query pretty good without loosing readability. In short: I like my SQL short, descriptive, declarative and elegant. While the following code works, i have two problems with it: I am using cursors and I can't shake the feeling I have in the back of my hea...

Data deduplificaton

The code below explains best what I'm trying to accomplish. I know that I can use a cursor or other looping routine to loop through the records to find the duplicates and create my notes records based on what is found. I'm trying to avoid that, unless there's no better option. DROP TABLE #orig DROP TABLE #parts DROP TABLE #part_notes ...