I'm currently trying to write a SQL Server 2005 function, which gets a string as parameter and creates a table with decimal values out of it. The problem is, that I have to define the decimal type based on parameters. This not working snippet should demonstrate the idea: CREATE FUNCTION [dbo].[ufn_ParseDecimal] ( @Sequence VARCHAR...
Are there any built in functions for SQL where I can pass in a number and it returns a Letter that matches the number. Example, I supply a 6 and it returns a "F" I realize its an incredibly long shot, just don't wanna re-invent the wheel ...
Hey guys, I'm tasked with doing a SQL Server 2000 to 2005 migration. I will be doing a side-by-side migration. I've read from a few different sources that I can either: perform a backup of the database on SQL Server 2000, ship that backup to the new hardware and restore the database in SQL Server 2005; or detach the database on SQL Ser...
I have a database in which there is a parent "Account" row that then has a 1-Many relationship with another table, and that table has a 1-Many relationship with another table. This goes on about 6 levels deep (with Account at the top). At the very bottom there could possibly be thousands (can even go beyond 100k) of rows. On each tabl...
I have an existing CRUD app that I have been tasked with implementing "tomb stoning". I need a way to allow a user to roll a given page of data back to the previous state. Say I have First Name, Last Name, and Social Security Number on a page in this app. User A updates the Last Name field. Later, User B notices that the New Last Nam...
Hi there, I had SQL Server 2005 Management Studio installed on my workstation. I have since installed SQL Server 2008 workstation tools and removed the SQL Server 2005 tools. I am now writing a c# program which iterates my registered servers in management studio. Problem is, it is iterating through my old list in the 2005 tools (which h...
I have been asked to keep track of how many times each item comes up within the results of a particular query. My thought is to just store the result of a query into a tracking table and then spit the results back to the caller. I am wondering what the most efficient method of storing these results would be since the result set could i...
Hi. Would really appreciate some help with a search angine I'm trying to make for a karaoke music site using ASP.NET and SQL SERVER... I have a table called Discs which has the following fields: ID, DiscCode, DiscTitle, DiscType, Theme, Manufacturer There is also a table called Tracks with the following fields ID, DiscID, A...
i have one simple table with following columns: id, name and parentID i created relationship diagram between id and parentID (on a same table), like simple tree, on the same table, but when i tried to user cascade delete it was disabled for me i know that it will be recursive delete if i will delete parent it will delete his children ...
I turned snapshot isolation on in my database using the following code ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON and got rid off lots of deadlocks. But my database still produces deadlocks, when I need to run a script every hour to clean up 100,000+ rows. Is...
A colleague of mine discovered a behaviour in SQL Server which I was unaware of. CREATE VIEW dbo.vRandNumber AS SELECT RAND() as RandNumber GO CREATE FUNCTION dbo.RandNumber() RETURNS float AS RETURN (SELECT RandNumber FROM vRandNumber) GO DECLARE @mytable TABLE (id INT) INSERT INTO @mytable SELECT 1 INSERT INTO @mytable SELECT 2 INSE...
Source Table Id, Name, Address 1 A #202 1 A #203 1 A #204 2 A #202 Target Table Id, Name, Address 1 A NULL After Merge Id, Name, Address 1 A #202 2 A #202 I am using this SQL create table #S (ID int, Name varchar(25) NULL, Address varchar(25) NULL) create table #T (ID int, Name varc...
OK, I have a table with no natural key, only an integer identity column as it's primary key. I'd like to insert and retrieve the identity value, but also use a trigger to ensure that certain fields are always set. Originally, the design was to use instead of insert triggers, but that breaks scope_identity. The output clause on the insert...
Assume I have a table called "table" and I have 3 columns, a, b, and c. What does it mean to have a non-clustered index on columns a,b? Is a nonclustered index on columns a,b the same as a nonclustered index on columns b,a? (Note the order). Also, Is a nonclustered index on column a the same as a nonclustered index on a,c? I was look...
The year is 2009 and SQL Server does not have CREATE OR ALTER/REPLACE. This is what I do instead. IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'SynchronizeRemoteCatalog' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE') EXEC ('DROP PROCEDURE dbo.SynchronizeRemoteCatalog') CREATE PROCEDURE dbo.Syn...
I'm using SQL Server Reporting Services 2008 to produce an invoice. The layout of this invoice is fairly standard - a page header/footer, then some address details at the top, followed by a single table for the invoice lines, and a set of rectangles for the totals below the table. This report worked absolutely fine in SSRS 2005, but s...
I have been trying to find out if the data-type enum exists in SQL Server 2008 like the one you have in MySQL. ...
Are these statements valid? UPDATE Table1 FROM (SELECT * FROM Table2) INSERT INTO Table1 (SELECT * FROM Table2) ...
I'm trying to make a judgment call in implementing a small-ish SQL Server '08 database. I'm translating an output text file of a flat-file database from an old COBOL system to the aforementioned SQL Server database. It's a database of vehicle and real estate loans, which can be uniquely identified by the combination of a Lender ID (a s...
I need to keep track of different dates (dynamic). So for a specific Task you could have X number of dates to track (for example DDR1 meeting date, DDR2 meeting date, Due Date, etc). My strategy was to create one table (DateTypeID, DateDescription) which would store the description of each date. Then I could create the main table (ID, ...