I have a query to add a column if it doesn't exist. It works the first time, but if I run it again, it fails, saying that the column exists?!? IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TABLE_NAME' AND COLUMN_NAME = 'COLUMN_NAME') BEGIN ALTER TABLE TABLE_NAME ADD COLUMN nchar(3) NULL; END And when ...
Is it possible to have not only a LinkedServer, but linked database and server? My situation is that of having one environment with a setup of: ServerX: DatabaseOne, DatabaseTwo and another environment of: ServerY: MyDatabaseOne, MyDatabaseTwo Now, DatabaseOne and MyDatabaseOne are exactly the same, as are DatabaseT...
I need to constantly merge (upsert/delete) data from an ODBC data source to a SQL Server 2008 database (number of rows vary from one row to 100000 of rows) What would you recommend as the most efficient approach (using .net 3.5 ): Use SqlBulkCopy into temp table then call stored procedure with Merge command using temp table as sour...
Basically i've got a very simple insert statement which INSERT INTO [dbo].[ORDER] (ORDER_DATE ,ORDER_TYPE_ID ,PAYMENT_STATUS_ID ,TOTAL_COST ,SENDER_NAME ,SENDER_EMAIL ,SENDER_MESSAGE ,RECIPIENT_NAME ,RECIPIENT_ADDRESS) VALUES (@ORDER_DATE ,@ORDER_TYPE_ID ,@PAYMENT_STATUS_ID ,@TOTAL_COS...
I tried to use full text search for a table called "Business" in Sql sever 2008 here is the statement(the search term is in chinese) select * from Business biz where CONTAINS(biz.*,'家具') And the I use like statement to do the same select * from Business where Name like '%家具%' The Fulltext searc returns 8 results and the like searc...
Hello, i think the title says everything. Is it better(faster,space-saving according memory and disk) to store 8-digit unsigned numbers as Int or as char(8) type? Would i get into trouble when the number will change to 9 digits in future when i use a fixed char-length? Background-Info: i want to store TACs Thanks ...
I've seen some discussion about L2S vs EF4 on Stack Overflow back in April, when VS2010 was launched, namely: Dump Linq-To-Sql now that Entity Framework 4.0 has been released? Is Entity Framework worth moving to for a new small app? Now, after 6 months, presumably people have had more interacting with EF4, so I'm curious of fresh opin...
I'm trying to improve a legacy database. One problem is that it's missing a lot of foreign key relationships. In some of the columns involved, the associated application is setting fields to an empty string when it should be setting them to null. What I'd like to do is to intercept any attempt to set that column and replace empty strings...
Hello, My data is as follows: ORDER_ID CLIENT_ID DATE VALUE 1881 51 2010-07-19 100.17 1882 50 2010-07-19 100.17 2754 50 2010-07-25 135.27 2756 50 2010-07-25 100.28 5514 50 2010-07-27 121.76 5515 50 2010-07-28 109.59 5516 50 2010-07-27 135...
I'm currently working on an VB.net application using Win forms and SQL Server Express 2008. One of the major functions of our program is to generate work orders for field engineers. These engineers typically work in a factory and have to walk around the factory to complete these work orders doing things such as recording pressures and vo...
How can i do sql query optimization in sql server please explain me ...
For migrating my old database from MySQL to SQL Server, which edition is suitable, 2005 or 2008 R2? Some developers suggested me to stick to the old version. Suggest Pros and Cons. I will be using with my .NET standalone Windows app. ...
My interviewer asked me Question that i am inserting 10 rows in database table and in some 5th row i find the some trouble then how can i roll back all the records? Please let me know how can i do this ...
Am writing av analytical standard solution register from the doping laboratory that i work for and got stuck on the problem how to get the ipaddress of the client in to the audit table. I have found a straight forward method for actually get the ipaddress on the web and my question is not about that. I got triggers on every table ins...
Hi, I have a sql query that has two parts 1) Which calls a stored proc and populates the data in the temp table (using openXML function) 2) The other part of sql that joins with this table and the result set Currently when i execute the stored proc it returns me the two result set. But it should return only me the second result set no...
I have worked on SQL stored procedures and I have noticed that many people use two different approaches - First, to use select queries i.e. something like Select * from TableA where colA = 10 order by colA Second, is to do the same by constructing a query i.e. like Declare @sqlstring varchar(100) Declare @sqlwhereclause varchar(1...
When I create a new database, by default the files are saved to c:\program files... but I would like them by default to be saved into a different location WITHOUT having to adjust anything. Is there a way to have this done by default? Perhaps there's some stored system procedure that I would have to change? ...
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Procedure [dbo].[spGetQualityReport] ( @providerKey INT ) AS -- declare @providerKey INT -- set @providerKey = 1; --Get Database providerId first DECLARE @realProvId INT; SET @realProvId = (SELECT TOP(1) providerId from providerKeyTranslation where keyVa...
I have a stored procedure I don't want to modify. It's rather large and complex, and I don't want to add any more confusion to it. So what I would like to do is have another store procedure that calls on the big one, and uses the result set to perform further selects / joins etc. ...
Executing the following script via sqlcmd fails. However, executing it via ssmo or SQL Server Management Studio works. sqlcmd -S . -d test -i input.sql input.sql: CREATE FUNCTION test() RETURNS @t TABLE ("ID" INT) AS BEGIN RETURN END Even when I put SQL Server Management Studio into sqlcmd mode, it still fails. This i...