tsql

quering remote database using sql server ?

I have used sp_addlinkedserver to access the remote machines db now i am writing queries explicitly on database like, select * from [server\instance].database.owner.tablename Now with this, [Server\instance] : this has to be provided explicitly [database] : can we find databases on specified instance using query like ms_ForEachDB ? ...

SQL Server - Finding the Highest Priority item using Bitwise operators

The Problem: I want to return all of the rows from the Primary Data table together with the Highest Priority Exception based on the currently assigned Priority in an Exception table. I have created a simplified example of my data set-up below (with creation scripts) so hopefully you can help with what should be a fairly quick T-SQL pr...

Data tweaking code runs fine when executed directly - but never stops when used in trigger

I have written some code to ensure that items on an order are all numbered (the "position number" or "item number" has been introduced only recently and we did not want to go and change all related code - as it is "asthetics only" and has no functional impact.) So, the idea is to go and check for an records that jave an itemno of NULL o...

group by sql statement

so i got this statement, which works fine: SELECT MAX(patient_history_date_bio) AS med_date, medication_name FROM biological WHERE patient_id = 12) GROUP BY medication_name but, I would like to have the corresponding medication_dose also. So I type this up SELECT MAX(patient_history_date_bio) AS med_date, medication_name, ...

Using T-SQL to Query a File System Folder

Is it possible to query a folder in TSQL, from SQL Management Studio, and return a list of file names? If so, how? ...

Output TSQL result to textfile in script

Is there a way to directly write result returned from TSQL / stored procedure to a text file (not using CTRL + T => Result to Text). As this TSQL will be dynamic in one of my service routine. Whenever I call this service routine it generates SQL Statement => executes and here I want to direct it to text file by passing the filepath as p...

Insert XML into SQL Table XML column

Am trying to insert XML into XML Column.. getting following error: . Msg 6819, Level 16, State 1, Line 5 The FOR XML clause is not allowed in a INSERT statement. My SQL query declare @tempTable Table (xmlValue xml) insert into @tempTable select EmployeeName, EmployeeSalary from Employee2 for xml path('EmployeeDetails') what am i doi...

Format XML returned from SQL for XML

Here is my Query : select EmployeeName, EmployeeSalary from Employee2 for xml path('EmployeeDetails') returns <EmployeeDetails> <EmployeeName>xxxxx</EmployeeName> <EmployeeSalary>5000.00000</EmployeeSalary> </EmployeeDetails> how can i get output as <EmployeeDetails> <EmployeeName = xxxxx /> <EmployeeSalary = 500...

Coalescing rows based on field value in row.

I have a table with tax rates where NULL entity type rows represent the default tax rates. Year End | EntityType | RateType | TaxRate ------------------------------------------ 2009 | NULL | Interest | 13 2009 | NULL | Other | 8 2009 | NULL | Interest | 13 2010 | NULL | Other | 9 2009...

How do I partition efficiently in SQL Server based on foreign keys?

I am working on SQL Server and want to create a partition on a table. I want to base it off of a foreign key which is in another table. table1 ( fk uniqueidentifier, data ) fk points to table2 table 2 ( partition element here ) I want to partition table1 base on table2's data, ie if table2 contains categories ...

Connect to SQL Server to run T-SQL use other's domain account.

Hi all In our database we have an SQL server account that has the correct roles to access some of the databases. All of our PC and Servers are in domain using Windows Account. Now there is ASP.NET web application, we want the users in the domain to browser some data in the sql server. But we do not want to grant direct permission to eve...

How to SPLIT the data in SQL server 2008 based on the length not on delimiter

Hi all, I have a query to split the data but it is based on a delimiter. My query is: DECLARE @xml xml,@str varchar(100),@delimiter varchar(10) SET @str= 'VINAYKUMAR,VINAYKUMAR,VINAYKUMAR,VINAYKUMAR,VINAYKUMAR,VINAYKUMAR,VINAYKUMAR' SET @delimiter =',' SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml) select @x...

Concerned with replicate fn in sql

hi guys, I am using following query create table #Attendence (id int identity(1,1),det varchar(2000)) insert into #Attendence (det ) select --convert(char(10),@Date,3) +REPLICATE(' ', 20 - LEN(convert(char(10),@Date,3)))+ staff.StaffNAme +REPLICATE(' ', 20 - LEN(staff.StaffNAme ))+ case Att.FN when 1 then 'Prese...

Tricky logic problem - how to create a set-based solution for this query?

Using SQL Server 2008 and any available features of TSQL, I am trying to work out how to if a set-based solution that does not involve a temp table exists for the following problem: Given a set of nodes that have a parent-child relationship, and a set of key-value pairs that apply to each, and given that the value (for a given key-value...

Dos command to execute all sql script in a directory and subdirectories

Hi, I need a DOS command or a batch (.bat) file I can execute to run all the *.sql script in a directory and it's subdirectories. Any ideas? ...

SQL Server 05\08: Grant Exec with special char in login

Trying to run the command: grant exec on GetPrograms to fp\ouruser_api When I run it, I get the error message "incorrect syntax near \", but this is the login id stored in our database, how can I grant this user permissions? Thanks ...

valid record in SQL query

I have a table with few columns and one of the column is DockNumber. I have to display the docknumbers if they confirm to a particular format First five characters are numbers followed by a - and followed by 5 characters. The last but one character should be a alpha. 12345-678V9 How can I check in SQL if the first 5 characters are nu...

Best equivalent for IsInteger in SQL Server

What is the best way to determine whether or not a field's value is an integer in SQL Server (2000/2005/2008)? IsNumeric returns true for a variety of formats that would not likely convert to an integer. Examples include '15,000' and '15.1'. You can use a like statement but that only appears to work well for fields that have a pre-det...

Syntax for Alter Column and Add Column in same query

Is it possible to both alter a column and add a new column in the same alter table query for MSQL? I've tried looking at the below MSDN article, but it was a bit confusing to understand. I could easily do it with multiple queries, but would rather do it with one query if possible. Thanks. http://msdn.microsoft.com/en-us/library/ms19027...

SQL-92 Query to find earliest date dependent on column value changing

I am querying a data system with an OLEDB interface that supports SQL92. My query problem is equivalent to the one solved here: http://stackoverflow.com/questions/2034094/sql-query-to-find-earliest-date-dependent-on-column-value-changing, but the solution provided there and copied below is too advanced for SQL92: SELECT JobCodeId, M...