In CODE 1, I get 3 orders returned from the orderItems table placed by userID 445. However, CODE 2 fails when trying to delete the same 3 records from the orderItems table. Is it against the rules to use a JOIN within a DELETE statement? If so, could someone hyelp me re-write CODE 2 perhaps...more >>

Hi, all, I have a database of documents and keywords with two tables: Documents(ID, some other fields) Keywords(ID, Keyword) So each document has a unique ID, and each row in the Keywords table holds one keyword associated with a document. I want to search for a match on multiple ke...more >>

I have a very large database TB's in size and need to make a smaller subset of the database. Example: I need to take all the rows and all the related tables for all users with a lastname between 'A%' and 'C%'. Currently the testing we need to do takes far to long on a restore of the product...more >>

Hi everyone, I need a query that needs to calculate average savings on the TOP 50 invoices by company. To start, this query will give all invoices in tblinvoices: select invoiceid, companyid, billed, repriced, savings from tblinvoices At first, I thought I could do just a: select T...more >>

Folks, Any help with creating a SP that denormalizes the below table to the desired output will be appreciated. Current Input Table Structure: SNum KeyName KeyVal value 1 SCM1 name DFW 1 SCM1 OVal 200 1 SCM1 OSum 500 2 SCM2 name ORL 2 SCM2 ...more >>

When attempting to set a varchar variable to something bigger than its capacity, it seems that the extra chars which do not fit will silently get ignored. eg : == DECLARE @data varchar(3) SET @data = 'abcd' print @data == This prints 'abc'. Is this "silent cutting off" behaviour AN...more >>

I use the below code to concatenate a person's full name. It only displays the period after the middle initial if a middle initial exists. However, I just realized that the COALESCE function treats an empty value just like a regular value. My problem is that my code shows the extra period o...more >>

Using a stored procedure, I need to insert several values per record into tableA. One of these columns derives it's value from a column in tableB while the remaining values are passed in as parameters. What is the best way to accomplish this?? -- Message posted via SQLMonster.com http://w...more >>

The following is legal in 2000, but not 2005 (90 compat mode) - generates "Ambiguous column name" error. SELECT client_status, client_status FROM ClientStatus ORDER BY client_status I have to change it to: SELECT client_status, client_status FROM ClientStatus ORDER BY ClientStatus.clien...more >>

Regarding foreign key constraints and cascading deletes: Is it a generally recommend or best practice to, as a matter of course, specify cascading deletes with foreign key constraints? Thanks! ...more >>

how do i switch the user in the middle of a session?? since the later part of my query requires higher permssion, I like to switch the user to a different user. is this possible and will it work? Thanks ...more >>

Can anyone tell me the biggest advantage of 2005 over 2000? My company seems like not want to make an upgrade unless I can tell them what is the major things 2005 (Other than all the fancy tools like SSMS) can do but 2000 can't? Can anyone tell me? Thanks Ed...more >>

A couple of years ago, I investigated how to record any changes made to a table. I have now been asked to implement this feature. I no longer have my notes from my first foray, but I do recall that the phrase used is "auditing trigger". Here's the short version: I have several tables in a ...more >>

Dear experts, I hate to ask such a seemingly dumb question. But I have spent some time trying to solve this already to no avail. When I was using my query analyzer last, a few months ago, I was tuning some strange code. I changed some settings to see what was going on. Now, when I logi...more >>

Hi, I'm creating a SSIS package that contains Execute SQL task. The task is calling a store procedure that returns xml output in a parameter. I mapped the parameter to the VARCHAR variable, but I'm getting the following error message. [Execute SQL Task] Error: Executing the query "exec...more >>

Hi, I've been trying to automate the bcp utility using the xp_cmdshell and one of the strings needs to be enclosed by apostrophes. Could someone be kind enough to show me how to add the apostrophe to my @cmd statment. the @channel_id and @patient_id should be enclosed with apostrophes. Thanks...more >>

I have a trigger on table A that insert and update table B. some of the users do not have permission to insert/update or access to table B what is my option to resolve this? i am thinking of temporary grand insert/update to the user. However, i don't know how to check for current executi...more >>

I have a situation in which what I would really like to do is pass a table to a function. An employee can have several certifications which makes him eligible to do certain work. I would like a single function where I can ask "does this employee have certification A" or "does this employee have ...more >>

Hi all, I wrote a sql script for the data migration from one table into my database. That script has one update and seven insert statements, a cursor and print instructions . The source table is filled with 3 million rows. First, I let a cursor catch 2 mio of that source data and do a inser...more >>

Hi, I need to store an IStorage in a BLOB column. Anybody have samples of how to insert, update and read this kind of columns? -- Thanks in advance, Juan Dent, M.Sc....more >>

Hello, I'm just starting to learn SQL, and I would like some help writing a query. I have 2 tables in an access database. I need the "Status" for Table 1 to read pending unless there is a matching record in Table 2. In which case I need the status in Table 2 to update the status in table 1...more >>

Using SS2000 SP4. I'm creating a new stored procedure to use with a new report. The new sp is very close to an existing sp. When I try to test the new sp using "uspNewSP '2006-5-25' '2006-6-25' I get the above error. Here is my sp: CREATE PROCEDURE [dbo].[uspNewSP ] @StartDate datetime, @...more >>

sql = "select a,b,c,d,f,g,h,i,j,k from DATA,SCREEN where a in (" & m_EEIs & ")" sql = sql & " and DATA.a= SCREEN.a" sql = sql & " order by a,b,c,d,e" Set rs = New ADODB.Recordset rs.OpenRs sql, adoConn, adOpenForwardOnly, adLockReadOnly When I do the above sql statement, I go...more >>

Let me preface this message by stating that i am a newbie. Here's my senario and what I would like to do. I have a table in a SQL database called 'users'. I want to update the a field in 'users' called 'subscription' . I want to do this for multiple users who I have listed in an excel sheet. ...more >>

I need to run a query that will pull the most recent revision, and it must be based on the RevisionDate (if you need to know why it is because the revision column can contain letters, numbers, hyphens, or underscores - numbers, in this case have the highest value, but SQL interprets letters as h...more >>

Just wondering if it is a common practice to preface the name of ALL tables in a db with some characters - like to indicate the application or vendor product being supported? I ask because I recently reviewed a few reference applications, and all table names in the supporting SQL Server databa...more >>

Is there an equivalent site to this in Spanish?...more >>

I am moving from Access to SQL 2005, so I am still learning. I have tables in my db for training information dbo.Training is the main with all relevant information. I have a dbo.Training_Presenters table for the presenter information. I have a dbo.Training_Locations table for location info...more >>

I am trying to import all MS Access tables and their data using DTS import wizard in SQL server 2000 using enterprise manager. I do not see Access tables in Source Tables and Views. It only show Queries of mdb database. Could there be any security on access database that is restriciting import i...more >>

Hi I'm getting error "Incorrect syntax near @BACKUPCOPYTABLENAME Can I not have variable after INTO? FOR A TABLE NAME? DECLARE @date varchar(20) DECLARE @BACKUPCOPYTABLENAME VARCHAR(40) SET @date = convert(varchar(20),getdate(),112)+'_'+left(convert(char(9),getdate(),114),2)+ substring(...more >>

I think this should be easy, but the only answer I see looks unnecessarily complex. Anybody out there have a better idea? Here's my table: manufacturer city unitsSold ------------ ----------- ------------- Nike LA 20 Nike SFO 3...more >>

I am pulling a set of records from the database where the records are in groups of 1 or more records tied together by a main number. Think of transactions with individual line items if you will. These line items each have the main transaction number, and another field that states which line ...more >>

Hello all, If I want to be able to perform full text catalog seaches and use the AND keyword, does it have to be removed from the noise file? Thanks, JY...more >>

Hi, I'm having a hard time coming up with a solution to the following scenario. I need to display the data from three columns in a table, but I need to view them as two rows for each record. For example, I have three columns: tbl1.id, tbl1.costA, tbl1.costB; what I'd like to do is view the ...more >>

Hi all, I am looking for an efficient way to do name comparisons. We have a database of customers. This has fields like companyname, address, city, state, zip, phone etc. These customers get inserted when they go online and register. The problem is that we are having an issue with duplicate...more >>

I'm reading a deadlock and the key information in the second node is this: KEY: 7:598861205471232 (d3017748c704) I thought the second piece was the objectID, but that # above is bigger than the ID field of sysobjects. Any ideas? Darrick. ...more >>

Hi, a system that i need to run quires on has many tables (one for each year) of the following table structure joined in a view called "Data" using "UNION ALL" (used for both Inserts and Selects): CREATE TABLE Data200603 ( ID int NOT NULL, VAL varchar(150) COLLATE Latin1_General_CI_AS ...more >>

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'PresentOrder'. I understand that ORDER is a reserved word, but PresentOrder is working fine in both SQL 7/SQL 2000 databases in my office and at dozens of installations around the world for years now. Why would one particul...more >>

Hello, How do I define a FK on a table? Thanks ...more >>

Hi everyone, I've created the following query to insert data into a table, however its taking absolutly ages to run, can anyone see a way of making the following faster? At the moment its taking more than 10 hrs! CREATE TABLE flow (id INT IDENTITY, coupling INT, product VARCHAR(35), [year] IN...more >>

I have been able to restore master, msdb and the production DB. One database named production_test is marked as suspect and I am not able to restore this one. I am also not able to restore the system db Distribution too. At the time of restoring 'distribution' it is giving error message " Canno...more >>

Hi All, I have 2 DBs on the one Server. DB1 has a clustered Index View called vwProduct. vwProduct also has an instead of update trigger defined. On DB2 I have created another view, called vwCMSProduct, to access DB1.dbo.vwProduct. This hides the location of vwProduct on DB1 incase we ever...more >>

Hi guys.. Having trouble with the (code generated) query... SELECT ProjectID, ProjectName, ProjectClient, StartDate, EndDate, DartsContact, LeadArtist, Projects.AreaOfWork AS AOWids, AreaOfDoncaster, StartDate, EndDate, Running, WorkAreas.AreaofWork AS AOWName, WorkAreas.RelatesTo AS AOW...more >>

Hello How can we generate SQL script to copy all database information like tables, all data, stored procedures etc....? best regards, -- Maikal...more >>

Hi there, I'm a newbie in SQL query with ASP web programming, I need help to embed SQL query to get data from my MS-SQL 2K server, there will be 2 ASP file involved, one ASP will be running from a vendor server & pass query for PO data to another ASP file stored in my company's web server, ...more >>

Hello all, Ok I need to Select all records from table1 joined on table2 by two columns. But there are some duplicates in table2? So I want all records from table1 that are matched up by those two columns but I'm getting duplicates. I do want one of those rows that are dup...more >>