Setup: ASP.net 3.5, Linq-to-Sql. Separate Web and DB servers (each 8-core, 8GB RAM). 4 databases. I am running an insert operation with a few million records into DB4 (using Linq-to-Sql for now, though I might switch to SqlBulkCopy). Logging shows that records are being put in consistently at a rate of 600-700 per second (I am running Da...
Hi, We have some tables where we store some sensitive information. These tables have one extra column "ModifiedBy". Currently we insert the value for this column programmatically. I am wondering if we have some way to get the current unix user into SQL Server. The idea is to auto-populate the user name/id into that column with the use ...
We want to have our test servers databases updated from our production server databases on a nightly basis to ensure we're developing on the most recent data. We, however, want to ensure that any fn, sp, etc that we're currently working on in the development environment doesn't get overwritten by the backup process. What we were thinking...
So I've got a query in a JSP class that retrieves, basically, the entire contents of a table as a string using MS SQL's "FOR XML AUTO" feature. I.e., select * from userdata for xml auto. The problem is that the result is being truncated at around 2033 characters. There's no unusual character at that point or anything; the break occurs...
I'm looking to create what I would think is simple. I want a user defined function which takes a row from a table as imput and returns a scalar value. Something like select mt.Id, MyUDF(mt) from M MyTable mt where mt.Price > 0 I understand that I can pass in the Id to the UDF, and then lookup the values from within the ...
How to manage NULL values in numeric fields returned by cursor in Select stament, to manage efficienly aritmetic operations ? ...
I am trying to write a stored procedure that takes a table name as a parameter. Yes I already know this is a security vulnerability, but this is an internal stored proc that doesn't face typical risks of SQL Injection. What I have so far is something like the following: CREATE PROCEDURE [dbo].[myprocedure] @tableName sysname AS DEC...
Is there a way in MS SQL Server 2005 to take a partition / file groups / files offline? I have a lot of data in certain tables and would like to use the sliding window Scenario: http://msdn.microsoft.com/en-us/library/ms345146%28SQL.90%29.aspx#sql2k5parti%5Ftopic24 Instead of keeping all the data in the first partition, I would like to...
I am using the JTDS driver and I'd like to make sure my java client is receiving the same query plan as when I execute the SQL in Mgmt studio, is there a way to get the query plan (ideally in xml format)? basically, I'd like the same format output as set showplan_xml on in management studio. Any ideas? Cheers, -James Some code f...
In my VB.NET code I construct a ODBC.COMMAND to call a stored procedure that eliminates a record from table in SQLSERVER 2008, I use this code in a FOR NEXT loop, it may contain 1 to 20 records_ID to pass to stored procedures to delete the records. This code work well for several months, but since the last compilation it only deletes th...
Hi I am using MS SQL Server 2000 and have a link from a test db to a live db which is in replication. The link seems to work fine and I can select from any of the tables using any of the fields apart from the field with the constraints on creating ids. So if I run select * from person where firstname like 'john' this works fine, but th...
Can anybody let me know that what access does public role have in SQL 2005. Thanks ...
Is there a way to have a trigger fire when a table hits a specified size? I am doubtful, but I thought I would ask. It would be useful for logging tables that I want to keep under a specific size. (I would delete older entries when the trigger fired.) ...
I have several questions regarding where to handle nulls. Let me set up a scenario. Imagine I have a table that has 5 varchar(50) columns to use as an example when providing reasons for using nulls or empty strings. Is it better to handle NULLS in code or in the database? By this I mean, is it better to assign an empty string to a var...
I've a moderate-scale web-app with an SQL-Server based backend DB. My DB overview - http://stackoverflow.com/questions/1667802/generic-suggestions-for-sql-2005-framework-design-and-implementation My App framework overview - http://stackoverflow.com/questions/1729199/l2s-linq-to-sql-or-ef-entity-framework So, for now as we'...
I have two columns: namecode name 050125 chris 050125 tof 050125 tof 050130 chris 050131 tof I want to group by namecode, and return only the name with the most number of occurrences. In this instance, the result would be 050125 tof 050130 chris 050131 tof This is with SQL Server 2000 ...
Does anyone know of a way to append text to a stored procedure from within another stored procedure? I would like to do something like the following in SQL Server 2005: Declare str as Nvarchar(Max) = '' set @spStr = dbo.spTest + 'Where testCol1 = ''Test''' exec(@spStr) I understand this may open some discussion about SQL ...
If one of the paramater is passed / or both passed how to match that to single column in WHERE clause. If both are passed should get result set just with the first one else with second one. Ex: --either will be passed declare @processStepID int = null declare @PROCESS_StepID int = null if(@processStepID is null) select * from CTL.CTR...
Using sp_repladdcolumn I can either: 1) add a column that allows nulls; or 2) add a column with a default value that does not allow nulls. Without dropping and recreating the whole subscription I would like to: 3) add a column with no default value that does not allow nulls. Is this possible? I realize I would need to populate the co...
I never had to do this before and never even thought about this before. How can i or what is the best way of storing RGB values in the database. I thought of couple of options. The most obvious one being 3 byte columns to store the R,G and the B.(I dont want to go this route) Another option is to store it in a 32 bit int column. ( I am ...