I am trying to query for a list of stored procedure definitions using information_schema.routines that exist in one database but not in another. SELECT t1.Routine_Definition FROM [server1].MyDatabase.INFORMATION_SCHEMA.Routines t1 LEFT JOIN [server2].MyDatabase.INFORMATION_SCHEMA.Routines t2 ON t1.Routine_Name = t2.Routine_Name WHE...
I need to design a Key/value table in my database and I'm looking for guidance on the best way to do this. Basically, I need to be able to associate values to a dynamic set of named properties and apply them to an external key. The operations I need to be able to support are: Apply a key/value pair to a group of items Enumerate all ...
How can I fire a trigger BEFORE a delete in T-SQL 2005? The FOR actually fires AFTER an event and they seems no BEFORE argument in the TRIGGER function. The INSTEAD OF is not what I want. I need to fire before I delete a record. Any ideas? ...
Ok so I am writing a report against a third party database which is in sql server 2005. For the most part its normalized except for one field in one table. They have a table of users (which includes groups.) This table has a UserID field (PK), a IsGroup field (bit) , a members field (text) this members field has a comma separated list...
I have a table with a charge/credit column: Item | PriceVal | CostVal | CHARGE_CODE 1 5 3 CH 2 8 5 CH 1 -5 -3 CR 3 7 1 CH 4 15 10 CH 1 5 3 CH I've got the query I need to get th...
As the title says, I'm having issues connecting to MSSQL from a PHP Script. The setup:- PHP is running on an Apache Linux Server. Microsoft SQL Server 2008 is on an XP Machine. I've got Remote Connections turned on in the MSSQL Server. The database bggs does exist. The database is running (I can see a green arrow). I have no firewall ...
I'm trying to do some basic paging in MSSQL. The problem I'm having is that I'm sorting the paging on a row that (potentially) has similar values, and the ORDER BY clause is returning "random" results, which doesn't work well. So for example. If I have three rows, and I'm sorting them by a "rating", and all of the ratings are = '5' - t...
Hi guys. I have some stored procedures which are used for generating Reports. I am trying to build a report dashboard that will show how many records are on each report. The SPs are detailed in a table which details in which order they should be run. I have a dashboard SP in which I am using Cursor to go through the database table, e...
I have a situation in which I have several related/cascaded tables. Lets say all 1-to-many relationships cascading down table1, table2, table3, table4, etc. What I have are default rows in the tables. They start with 1 record in table1 and have 1 or more related records in other tables. What I am looking for is an easy way to replica...
Today, for the first time in 10 years of development with sql server I used a cross join in a production query. I needed to pad a result set to a report and found that a cross join between two tables with a creative where clause was a good solution. I was wondering what use has anyone found in production code for the cross join? Up...
I have a database table that contains a list of contacts, some of those contacts might have multiple records, e.g. CustomerID, CustomerName, Vehicle 1, Dan, Mazda 1, Dan, Suzuki 2, John, Ford 3, Dasha, Volvo 3, Dasha, Ford Can I write a select query to return the distinct customerID and CustomerName, and a list of vehicles in 1 record...
You know the one I am talking about. We have all been there at some point. You get that awful feeling of dread and the realisation of oh my god did that actually just happen. Sure you can laugh about it now though, right, so go on and share your SQL Server mishaps with us. Even better if you can detail how you resolved your issue so ...
Is it true that MS SQL restrict self-referencing constraints with ON DELETE CASCADE option? I have a table with parent-child relation, PARENT_ID column is foreign key for ID. Creating it with ON DELETE CASCADE option causes error "Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths. Specify ON DELETE N...
I'm looking at the AdventureWorks sample database for SQL Server 2008, and I see in their creation scripts that they tend to use the following: ALTER TABLE [Production].[ProductCostHistory] WITH CHECK ADD CONSTRAINT [FK_ProductCostHistory_Product_ProductID] FOREIGN KEY([ProductID]) REFERENCES [Production].[Product] ([ProductID]) GO ...
I have a database with event information, including date (in MMDDYYYY format). is it possible to write an SQL Server statement to only get rows that fall within a certain time frame? something like this pseudo-statement: SELECT * FROM events WHERE [current_date minus date <= 31] ORDER BY date ASC where date is the date in the SQL Ser...
Are there any static code analysis tools for stored procedures written particularly in PL/SQL and T-SQL? ...
Hi, I have a vb.net function that creates several Stored Procedures based on parameters being passed into the function. I want to move this vb.Net into a single SQL file (for maintenance reasons) but I am not sure how I can re-create it in SQL without creating 7 separate stored procedures. I create a total of 20 Stored Procedures and ...
I want to be able to pass in a list of parameters, and ignore the ones which are NULL. So that the query is in effect pretending that the filter isn't there and ignoring it. I was doing it like this: (@thing IS NULL or Thing=@thing) Is this right, and if so, would it perform badly? It's seems to be a lot slower than constructing the...
I have a table Users, so some rows specially in field Full Name are in different upper/lower case, so i found this function: CREATE function properCase(@texto varchar(8000)) returns varchar(8000) as begin --declare @texto = 'hola' set @texto = lower(@texto) declare @i int set @i = ascii('a') while...
I have a Table that looks like this: IP Hostname TransactionDate ------------- ---------- ------------------- 1.1.1.1 A 2009-01-01 01:00:00 1.1.1.1 A 2009-01-02 01:00:00 1.1.1.1 A 2009-01-03 01:45:00 1.1.1.1 B 2009-01-04 01:00:00 1.1.1.1 A 2009-01-05...