I have recently moved a database from Sql Server 2000 to Sql Server 2005. In the table designer, it insists on putting square brackets around a column named "Content" I don't see Content on the list of reserved words for Sql Server, so I don't understand why it is doing this. Is there a way I can prevent it? ...
Hi there, can't seem to find a way to do this anywhere. I have an SQL trigger that fires on an update. The trigger copies some details about the update to another table that someone elses program uses. Among the details passed is the name of the updated column. There a many, many, potential columns that have been updated (although only o...
I have a large table, 1B+ records that I need to pull down and run an algorithm on every record. How can I use ADO.NET to exec a "select * from table" asynchronously and start reading the rows one by one while ado.net is receiving the data? I also need to dispose of the records after I read them to save on memory. So I am looking of a ...
We have been storing our staging database on the production database server with the mindset that it makes sense to be as identical to production as possible. Lately, some comments have made me question that idea. Since there is some remote chance that I will do something to production by mistake it may make sense to not put both on the...
I have an ASP.NET app that accesses a SQL Server database with membership and roles tables included. The app works perfectly when run locally, but when I deploy it to a shared Godaddy host, write operations on the database seem to act funny. I added before my connection strings in web.config to see if the app was trying to access a di...
Hi Everyone, I'm trying to setup some data to calculate multiple medians in SQL Server 2008, but I'm having a performance problem. Right now, I'm using this pattern ([another example bottom). Yes, I'm not using a CTE, but using one won't fix the problem I'm having anyways and the performance is poor because the row_number sub-queries r...
Up until now I've been a lone wolf on my client projects. Anytime I would make changes to SQL Server: tables updates, stored procs, etc. I would generate the change script and plop it into a directory. When the application was ready for release, I would run the scripts on the live server and be done. Soon I will have another develope...
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED BEGIN TRAN DECLARE @res INT EXEC @res = sp_getapplock @Resource = 'This a Lock ID 3', @LockMode = 'Exclusive', @LockOwner = 'Transaction', @LockTimeout = 60000, @DbPrincipal = 'public' if @res < 0 begin declare @errorMessage nvarchar(200) set @errorMessage = case @r...
Sorry about the last message. I did a paste over my question. Long question short, when using sp_GetAppLock inside of a try / catch block, should I call sp_ReleaseAppLock when an exception is caught? SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED BEGIN TRAN DECLARE @res INT EXEC @res = sp_getapplock @Resource = 'This ...
I have a table ALPHA with 2 fields GroupId,Member: GroupId | Member; A1----------A; A1----------B; A1----------C; A2----------A; A2----------B; A3----------A; A3----------D; A3----------E; Objective: Given the input of - A,B,C - I have to query the table to find if a GroupId exists for this exact set of members. So, this is what I pl...
<game xmlns="http://my.name.space" ></game> This is my root element. I've written a stored procedure to insert elements into it. To sum up the stored procedure, here's the SQL UPDATE ChessGame SET GameHistory.modify('insert <move><player>black</player><piece>pawn</piece><start>E7</start><end>E6</end></move> as last into (/game)[0]') W...
I have the following hierarchical table: Table Category: CategoryId, ParentCategoryId, CategoryName 1, null, SomeRoot 2, 1, SomeChild 3, 2, SomeGrandchild 4, 3, SomeGreatGrandchild (note this sample data doesn't include a leaf on an earlier node than level 4, but that is possible). The data will never go deeper than level 4, if that i...
NOTE: I have solved the majority of this problem but have run into a snag. Read to the bottom please. You will see where I added a (NOTE) section. TIA. I have a rather extensive join query that I want dumped to XML. I have it almost working but I am missing a concept in here somewhere. My query (abbreviated) looks like: SELECT C...
I would like to run a (extensive) query that produces one line of XML. This XML represents about 12 tables worth of relational data. When I "delete" a row from the top level table I would like to "capture" the state of the data at that moment (in XML), save it to an archive table, then delete all the child table data and finally mark t...
Given a .NET environment with Windows CE, can you persist thousands of records per second in a local database (SQL Server 2008 - standard or CE). What are the performance issues with persisting realtime instrument data in a database versus a log file? ...
I've got a DB with a whole bunch of records containing lat and long of points I want to get onto a map. The level of detail I need is down to Australian street level and up to the country level and it can be either pure map data (suburbs, streets, etc) or aerial photos. This is purely for personal purposes and the output doesn't need to...
Ok we have critical transactional database and its in full recovery mode in SQL Server 2008. We have have two different servers in two different data centers on two different timezones. And I am trying to setup best way to make database as upto date as possible using various options. Database is currently only 1.5GB, expected to grow 1GB...
I created few user defined types in DB as below CREATE TYPE [dbo].[StringID] FROM [nvarchar](20) NOT NULL and assigned to various tables. My tables in db are in various schemas (not only dbo) But I realized I need bigger field, and I need to alter, e.g increase from nvarchar to nvarchar, but there is no ALTER TYPE statement I need a...
According to MSDN BOL (Books Online) description on SOME | ANY (Transact-SQL), SOME and ANY are equivalent. It does make sense to use either SOME | ANY to make a query more readable. But is that the only reason why there are 2 keywords in TSQL where they serve the exactly the same purpose? Are there any historic reasons why they ...
Hello everyone, On SQL Server 2000 there is a linked server to an Oracle 9.2 server. I am able to select from the remote tables, but all not latin (Greek) characters, return incorrect. I tried specifying a collation on the SQL Server, but nothing changed. ...