I'm trying to write a stored procedure to copy a subset of data from one set of tables to an identical set of tables in a different database. The "source" database needs to be a parameter to the stored procedure. I've struggled with this for two days now, and I thought I had a good solution: Validate that the schemas are the same. Cr...
Hi, I am studying SQL, I would like to know how can I use local variables in CASE statements in SQL. Thanks guys for your help. This script gives me error: DECLARE @Test int; DECLARE @Result char(10); SET @Test = 10; CASE @Test WHEN @Test = 10 THEN SET @Result='OK test' END Print @Result; I USE MS...
In an app, where SQL server databases with schemas and locale unknown at compile-time (determined at runtime) can be plugged in, I need to figure out how to update a decimal field - ie. what decimal separator to use. So against one server, if I need to update a decimal field, I would send 100.125. On another server that would be interpr...
I've been creating imports that use SSIS to import data into a temp table, then using a stored procedure, steps through the data one by one with a cursor to process the data and insert information into 3 different tables. The inserts in the first 2 tables are complicated because if there is a record that already exists with the same dat...
Hi, I want to organise my private software development process and I plan to use JIRA together with SQL Server 2008 R2 Enterprise and Perforce. I have already installed and configured this "pack" for evaluation purposes on desktop machine. For a clean production environment I want to migrate (of course with a clean install) all those ...
Using NHibernate 2.1.2.4000 against SQL Server 2008. The target table has no triggers or extraneous indexes. It is simply: create table LogEntries ( Id INT IDENTITY NOT NULL, HostName NVARCHAR(32) not null, UserName NVARCHAR(64) not null, LogName NVARCHAR(512) not null, Timestamp DATETIME not null, Level INT not null,...
Hi All, I have two separate tables TVs and Receivers that I am using the FOR XML PATH commands to build XML off of. My issue is that I want to combine the output of my TV XML Build with my Receiver XML Build to create one XML output. So I would have something like this(Which allows me to keep the TVs and Receivers Tags Separate within...
Is it possible to construct a dynamic query to for a linked server (and if so how)? For example: @linkedServer varchar(50) @var1 varchar(10) @var2 varchar(10) select * from openquery(@linkedServer, 'select c1,c2 from t1 where p1 = @var1 and p2= @var2...
Hi Based on the 2 databases below: Database_A on Server_1 Database_B on Server_2 I have created a linked server to Database_B on Server_1 instance by name 'LS_B'. I have a huge script file which basically creates required tables, functions, views, and stored procs on Database_A. These functions, views and stored procs in turn ref...
I've got a sub-select in a query that looks something like this: left outer join (select distinct ID from OTHER_TABLE) as MYJOIN on BASE_OBJECT.ID = MYJOIN.ID It's pretty straightforward. Checks to see if a certain relation exists between the main object being queried for and the object represented by OTHER_TABLE by whether or not ...
Hi I am writing a long program in TSQL that pulls in data from an OLD (and very dirty data set) scrubs the data and reformats the output including column headers to match a new data set There are 130 columns in both the new and old tables. For the purpose of testing I am bringing in 100k rows from each. To validate that the table struct...
Let's say I have the following table fID Field1 Field2 1 a dennis 1 f mac 2 j clownbaby 1 k charlie 3 t frank 7 d dee and I want to take all the rows with 1 in the first column and insert them in the same table with a number I could pick as an argument to my stored procedure. So that i...
Hi we have a server with 32 cores and 256*GB* RAM, we are using this with SQL Server 2008 Enterprise on Windows 2008 R2 Enterprise. Currently windows has allocated automatically a swapfile of 256GB which seems excessive. Is it advisable to hard limit the swapfile to something smaller like 32GB to force it to use the physical RAM? ...
I need to collapse multiple ranges of sequential numbers (1 or more) to sets of their minimum and maximum values. I have unique integers (no duplicates) stored in a table column. The obvious way (to me) to solve this problem is to use a cursor (see my algorithm below) and iterate through every integer. However, it seems inefficient to ...
Hi there, what I'm interested in is the final row of the table. Instead of having a few empty cells to the left of "total" I was hoping that I could use colspan to make it look a lot nicer. SET @html = @html + CAST(( SELECT td = ' ', ' ', td = ' ', ' ', td = ' ', ' ', td = ' ', ' ', td = 'To...
I have a simple query where in FROM I've got something like: WHERE ([Comment] LIKE '%something%' OR [Comment] LIKE '%aaaa%' OR [Commnet] LIKE '%ccc') AND Replace([Number], ' ', '') = @number Unfortunetly this is now hardcoded in code so if anything changes (more OR's on the [Comment] field I have to go and change it in t...
How to insert the resultset given by the commands RESTORE FILELISTONLY RESTORE HEADERONLY RESTORE VERIFYONLY into an automatically generated temp table ? I would like to use a technique similar to (so the table is auto created, with all the columns matching the resultset's columns) SELECT * INTO #TempTable FROM (RESTORE FILELISTON...
Hi, i am preparing for Microsoft exam 70-432 SQL Server 2008 . In the book i read, its highly recommended that you use separate log-in for each SQL Service. But i am unable to understand what is a benefit in using separate USER? Please let me know if anyone has an idea about this. Regards, fayalif ...
Hi, I have a series of stored procedures that select data from a db. I have a role (cctc_reader) that has execute permissions granted on the procedures. One of the procedure calls another stored procedure called recControl_system_option which in turn queries Information_schema.columns. The problem is that in this proc the query selec...
Hi All, I am trying to create some dynamic DDL to build a function and when I run it, it keeps giving me an error. I am not sure what I am doing wrong with the format....I have tried a lot of different things and now it is just out of curiousity that I want to understand how to get it to work. Any input is greatly appreciated. CODE: ...