What are the best options for putting SQL Server artifacts like triggers into source code control, and then automating moving the objects from human readable files back into the database as triggers? Are any of the good options cheap? -- Will ...more >>

I have a Client table with a province code and an amount. Is there anyway to return 1 row for each client that contains the amounts for each province without a subselect ? Clt Amt1 Amt2 Amt3 1 12 13 9 2 7 11 15 Clt Prov ...more >>

I have a view that contains a self join: SELECT dbo.Clients1.ClientID, dbo.Clients1.AccountName, dbo.Clients1.OwnedByClientID, Clients1_1.AccountName AS OwnedByClientName FROM dbo.Clients1 INNER JOIN dbo.Clients1 AS Clients1_1 ON dbo....more >>

I want to retrieve the model of cars in Groups. However the field Model is filled with the model and the type. Is there a way to group on the first word, lets say 147, 156, .. Thx GL 147 1.9 D 147 2.1 D 156 1.6 156 1.7 156 1.9 D 156 2.1 D ...more >>

I know this is a heavily debated topic, and I know many times it's totally subjective and up to personal preferences. But I want to bring up a discussion that I've yet to see covered. When building column names, I'm trying to find out what pros/cons are to prefixing them with table names. I ...more >>

I thought that it would be interesting to point out some very subtle issues that can occur when using SELECT's inside a transaction that also includes INSERTS/UPDATES or DELETES.). The issues really arise when concurrency occurs (ie many users trying to execute this transaction at the same time ...more >>

I'm trying to pass a query from sql server 2000 to Oracle using linked servers. I don't want to use DTS. While it's easy enough to use OPENQUERY to pass thru a query that returns a dataset, I can't seem to pass thru a query that doesn't return a dataset eg a create table query or a drop tabl...more >>

Hello, i have a stored proc where i create two temp tables with complete equal structure. But the structure is different every time the stored proc is called. Now i want to find all records in temp table 1 which are not in temp table 2. I am not sure if the sorting in both tables is equal. So...more >>

Hi I created a trigger and now it is missing. When I run Create Trigger Customer_Update on Customers routine it says there is already a procedure by that name however I have searched and searched to edit or remove this trigger and I cannot find it. Any Ideas I am stumped I have never seen t...more >>

For security reasons I am trying to develop a trigger that is capable of sensing data and then running a process. The idea is to only allow internal processing and not have to grant access to the entire database to run a process. The test script I am look at would go something like this. Te...more >>

Hi! I have a question about implementing cascade deletes in sql server 2000 database. I have two tables: Object (ObjectId, ObjectName, ObjectType, etc.) Object_Association (ObjectParentId, ObjectId) Both fields in the association table are foreign key related to the ObjectId field in th...more >>

Can an SQL script be generated for a DTS package? I need to be able to create script to recreate every object that relates to the normal functioning of the database. Most other objects are easy to get an executable script to recreate. I can't seem to figure this one out. -- Thank you, ...more >>

I want to run one of two SELECT statements, one with a Join the other without, depending on wheteher the Join fails because of an unrelated record. The second statement should always return one row as the @RequestID value being passed comes from a Tree selection. But no row is returned. Where ...more >>

Hello, I am in the process of designing a big commercial database for a wholesaler. Since many months now, I have been asking myself questions about how to physically implement a system of tables that would efficiently represent the logical situation of many "sub-entities" related to a sin...more >>

I'm having an issue with a date field in Access. I have imported data from a database and the date field is a HUGE number rather than an actual date. I have a formula from crystal reports to convert the number into a date, but I;m not sure how to convert the crystal report formula into a SQL o...more >>

Hi, I need to delete large amount of record from sql2k table weekly. My question is that there is any way that I could delete them pypassing sql log file. Delete * from table1 where year(createdate) < '02' Thanks, ...more >>

I had installed Sql Express on my machine, now i have Visual Studio Pro 2005 which came with Sql Server 2005 Developer edition. I carefully uninstalled anything I could find that was related to the express edition before installing Developer. But still when I try to connect in Management studio,...more >>

Is ther a character that can be used yo match numeric datatypes similar to the Like keyword and the % cbaracter? I want to us a parameter that is a bit type and return records that true false or both. In the WHERE clause I can get either true or false field = 0 or feild = 1 but get stuck ...more >>

I have a table of patients. There's a date column called AdmitDate. So if I want to know the number of patients that were admitted in each year, I can do: SELECT DATENAME([year], AdmitDate) AS 'Year', COUNT(*) AS Admissions FROM Patients WHERE (0 = 0) GROUP BY DATENAME([yea...more >>

Where do I find the STATS output from Backup or restore operations when these are called programmatically? SQL books says "Displays a message each time another percentage completes and is used to gauge progress." - but where can I find that message, so I can use it to update a progress bar? Is...more >>

How can one update the values of one column from the values with the values the column of another table? For example, I have two tables, Customers and Invoices. The customer table has an identity column CustomerID, and a Col. CustomerName associated with that ID. On the Invoice table, the...more >>

I have the following. CREATE TABLE Assessment ( AssessmentKey int Primary identity(1,1), AssessmentType int, AssessmentDate datetime ) The following values are inserted into the table. AssessmentKey AssessmentType ClassDate 1 ...more >>

DECLARE @ErrorNum INT , @RowCount INT DELETE Seller WHERE SellerId = 9999999999999 SELECT @ErrorNum = @@ERROR , @RowCount = @@ROWCOUNT SELECT @RowCount , @ErrorNum Cases @ErrorNum @RowCount 1 0 0 2 0 <> 0 3 <> 0 0 4 <> 0 <> ...more >>

I have been using an OODB as the repository for a stock-quote ticker plant. I would love to be able to convert this to SQL Server. Quotes come in at a nominal rate of 10K per second with peaks up to 60K. The quote is fairly basic data: time, price, volume, ExchangeID - leaving out the min...more >>

I have inherited the maintenance of a product which includes the snipet of code below. Every 10 seconds the code is executed. It is causing a deadlock in some instances, but I am undable to reproduce the problem on my machine. The "PC" table contains a list of PCs seen on a network, so isn't ...more >>

Hi I have a strange problem connecting to a local SQL Server 2000 on a machine running Windows 2003 Server. I'm using a plain connection as shown in the code below: Dim oServer as New SQLDMO.SQLServer With oServer .LoginTimeout = -1 .LoginSecure = True 'Use NT Authentication .Au...more >>

Hi, I'm trying to use BCP to import a .csv file into a .mdf file. At the commandline, I'm using the following command: bcp commercedb.mdf.crmc_products in test.csv -T I'm getting errors (below) telling me that I'm not successfully connecting to the database. Any help would be appreci...more >>

Hello , I have created a UDT and depoyed it. Later on again i have chnaged my code and tried to re deploy it and its throwing an error message. When i right click on my project in the solution explorer and click on deploy its throwing a message saying that "Error 1 Cannot drop type 'S...more >>

i have this sql Q: a delete statement not allowed to use with "as" (delete from table as t where..), so when i need "as" i do it in sub q, like this: delete from customers where 3<(select count(*) from customer t where t.date=customer.date) but when table in memory table, I got err: d...more >>

hey guys very new to sql server - i can take care of the basic CRUD but,i'm not familiar with some of the syntax to handle stuff right in the proc. i've been making multiple round trips to accomplish what i think i should be able to do based on a condition right when im in the procedure itself. so, ...more >>

Please help, I need to only select 1 address for each customer_id, address_id is primary key. Thanks in advance, Culam address_id customer_id street_addr_line_1 city_nm ----------- ----------- ---------------------------------------------- 516428 65619 3347 BARO...more >>

What's going on here ? I had a stored procedure running oddly slow, which I've fixed, but I'd love to know why it was slow in the first place. This query takes 35 seconds to run: Select starttime from tbldata inner join tblbatch on tbldata.batchid = tblbatch.batchid where starttime > '02 dec...more >>

When execute xp_sendmail in sql analyzer, is there a way to specify Email sender instead of the default sql mail sender. For example, when I exceute the following code exec xp_sendmail @recipients='yyzz@some.com' The recipient will only see the mail address from the default sql mail server....more >>

Hi, I would like to find customers (same id) with different addresses: Id Address -- ----------- 1 123 ABC 2 456 DEF 1 789 GHI 2 456 DEF 3 000 XYZ As above example: my result would be Id=1. Thanks in advanced, Culam...more >>

hello, i want to find the rows in which field1 did not mach field2 .. (problem with special caracters) i have a table which is a result of a insert ..... In the table are id and 2 fields of type Varchar(50) How can i compare if in the table the two fields are identical. I have problem in...more >>

I have the following data and I swear I am losing my mind. I want to condense the table below into 3 unique combinations and I am having the worst time on this seemingly simple problem. I don't care which column the numbers end up on, I just want to ensure that the columns together are t...more >>

I got various Job Details listed in my table. I have set all jobs in a such a way that they will be LIVE when Flag=1 and EXPIRED when Flag=0. But how can I compare the Expiry Job Date with the Current Date and Update Flag=0 automatically when its expired. I probably need compare everyday. I hav...more >>

Hi and TIA. What I'm trying to do is create a DTS package in SQL Server(2K). What I then want to do is have my users locate the database(Access2K) file, upload the file to a virtual directory, and then fire off the DTS package to import the data from the access tables into existing tables ...more >>

Hello, has somebody a tip how to make the Oracle-MINUS with SQL Server? thanks, Helmut...more >>

Hi all, We have a claims table in out database. It has A child table which would hold different financial ammount with regards to the claim loss, eg material dammage, third party, towing etc. Each catergory of loss is a new row. I am trying to see which is the best way of showing and storing ...more >>

Hi,, I have a simple MS Access database that contains two tables at present. Customer_Info and Payment_Details. I want to run a query that will give me a list of customers who owe money on their account based on the date in the SQL query. The problem is that if I put in a date I am getting...more >>

How to list the names of check constraint of a table? How to show the content of a check constraint? Thanks ---Frank, SQL2005 ...more >>