I have a table with column ID that is identity one. Next I create new non-identity column new_ID and update it with values from ID column + 1. Like this: new_ID = ID + 1 Next I drop ID column and rename new_ID to name 'ID'. And how to set Identity on this new column 'ID'? I would like to do this programmatically! ...
this code using an OLEDB source will populate the OutputColumnCollection below it, the code using a flatfile source will not populate the OutputColumnCollection. Why not? Microsoft.SqlServer.Dts.Runtime.Application a = new Microsoft.SqlServer.Dts.Runtime.Application(); String SSISPackageFilePath; SSISPackageFile...
I want to add an integer column to a table with a large number of rows and many indexes (Its a data warehouse Fact Table). To keep the row width as narrow as possible all the columns in this table are defined as not null. So I want the new column to be not null with a default of zero. From experience adding this column will take some t...
OK, I'm trying to make an "empty" version of a database on another instance of SQL Server 2005. To do this, I use the Scripting wizard to make a big script that CREATEs all the tables, views, SPs, etc., adds some users, and grants permissions for them. Now, in the permission section of the script, I have (among other things) use [mast...
Hey guys. I have a column of data containing a string with the following format: blablablabla(XYZ) and I would like to discard everything outside the () - and the parenthesis themselves - and to set update that field with the value inside the (). This implies that, in this particular case, the "blablabla" would be discarded and that ...
I was writing a Stored procedure today, and I ran into a problem where if one of the values is null (whether it be from a SELECT statement or whether it be a passed in parameter) my expression would evaluate to false where it ought to evaluate to true. SET ANSI_NULLS ON; DECLARE @1 INT; DECLARE @2 INT; DECLARE @3 INT; DECLARE @4 INT; S...
A consultant employed by my client has asked for an export of the the first n rows (approx 30 or 40) of all the tables in one of our SQL Server 2000 databases in csv format, including column names on the first row. The database has about 100 tables so I don't want to do this manually. Can anyone recommend a script, tool or other appr...
I have a small SQL Server database that I need to copy on command -- I need to be able to take the mfd and ldf files at any given moment, copy them, zip them, and make them available to an end user. Right now this is possible by manually: 1) Logging onto the SQL server via Remote Desktop 2) Detaching the database via SQL Management St...
There is a process that seems to be running all the time in SQL Server 2005 activity monitor. Double clicking on it produces the following query: CREATE TABLE #tmpDBCCinputbuffer ([EVENT TYPE] NVARCHAR(512), [PARAMETERS] INT, [EVENT Info] NVARCHAR(512)) INSERT INTO #tmpDBCCinputbuffer EXEC ('DBCC INPUTBUFFER(58)') SELECT [EVEN...
I think what I need is CROSS APPLY, but I can't seem to figure out the syntax. I have a set of XML in an MSSQL 2008 database that looks like this: <Cookie> <id>Chocolate Chip</id> <ChocolateChipPeanutButter> ... </ChocolateChipPeanutButter> <ChocolateChipPecan> ... </ChocolateChipPecan> </Cookie> <Cookie> ...
Hi people. I am using a ASP.NET MVC framework, jQuery library (1.3.2), linq to sql, sql server 2005. I have a problem with jQuery MaskMoney plugin, to save data in sql server table. I created a table, where this table have a field "valueProducts" type Decimal(18,2). In my page, I have a html field with jQuery maskmoney. Example: 10.00...
Our workstations are not members of the domain our SQL Server is on. (They're not actually on a domain at all - don't ask). When we use SSMS or anything to connect to the SQL Server, we use RUNAS /NETONLY with DOMAIN\user. Then we type in the password and it launches the program. (RUNAS /NETONLY does not allow you to include the pass...
Hello, If I bind GridView to SqlDataSource and also set AutoGenerateEditButton to true, and if I then try to update a field ( this field being a primary key in database ), then database should return an error and thus SqlException should be thrown?! So why doesn’t page report an exception? Instead, all Gridview does is setting all fi...
How do I specify constraint on my field that allows null but if value exists it should be one of values of primary key within existing table? Take a look at the code: CREATE TABLE TestTable ( RowId int IDENTITY NOT NULL PRIMARY KEY, RowParentId int NULL, -- < how do I specify constraint that RowParentId if not NULL should be ...
I have a table with timestamps. What is the proper query to get the records counts for each minute for the last hour. I.e. if now is 2:25, I want to know how many record were between 1:25 and 1:26, 1:26 and 1:27, and so on, so I have 60 results. ...
I'm writing some code that needs to work against an array of different database products. MySql Sql Server 2000 to 2008 PostgreSQL Oracle 9i & 10g Jet 4.0 (MS Access) MSDE Sybase Adaptive Server Anywhere Sybase Sql Anywhere Progress OpenEdge We have a set of generic integration tests that we need to run against each database produc...
I want to to make to make the ordering in my query conditional so if it satisfiess the condition it should be ordered by descending For instance: SELECT * FROM Data ORDER BY SortOrder CASE WHEN @Direction = 1 THEN DESC END ...
I've mapped an EDM entity to a database(SQL Server 2005) View. The entity is a simple Movie Entity which has properties of ID, Name and DateInserted which corresponds to a View which has the following definition: SELECT iMovieID, vchName, dtInsertDate FROM dbo.t_Movie WITH (NOLOCK) The table t_Movie has the following definition...
can anyone explain how to read a execution plan in SQL Server With Example Please? ...
Hi all, Suppose i have the following table book ----- id name genre check_out_date location_id Can anyone post an example of an efficient stored procedure that only updates the book table where the passed in values are different from the current values in the table? i.e. if i passed in values ( 1, "Slaughterhouse V", "Fiction", 10/1...