I want to issue a transaction where whole transaction should roll back properly. I want to be sure that anything that stops these statements from working will rollback the transaction. Example, the table2 no longer exists below so I get error Invalid object name 'table2'. How do I get sql to ...more >>

I decided to push this out to it's own thread. Here's the question, if you are deciding to abide purely by ANSI SQL 92 standards and are only thinking in terms of the logical model, how does the modeler decide when to use a SmallInt vs. an Integer if the allowed range is considered a physical ...more >>

SQL Server 2000 Standard, SP3a. I have a linked server defined pointing to DB2, using their DB2 Connect driver. When I execute a single table query from SQL Server against the DB2 linked server with a highly selective where clause using four part naming, it appears as if the query retrieve...more >>

SELECT item_id, unit_price, date_created FROM dbo.p21_view_po_line where complete='Y' GROUP BY item_id, unit_price, date_created OREDR BY item_id I NEED FOR EACH ITEM ID LAST DATE CREATED. ANY HELP PLEASE ...more >>

I'm trying to decide what is the best practice in terms of creating scripts that, for instance, update an existing database. It seems there are a couple of ways to do this (although I am sure there are more). It is often suggested that you code your changes, by hand, using Enterprise Manager...more >>

Im running into a problem here and i am unsure if it's possible to do this, and i have the slightest idea on how to do this. This is what i need to do though. I have a select statement SELECT IPQ20101.IPORFQNumber, IPQ20101.IPORFQDate, IPQ20101.EXPDATE, IPQ20101.BUYERID, IPQ20101.L...more >>

SELECT item_id, unit_price, date_created FROM dbo.p21_view_po_line where complete='Y' GROUP BY item_id, unit_price, date_created OREDR BY item_id I NEED FOR EACH ITEM ID LAST DATE CREATED. ANY HELP PLEASE ...more >>

I need to do the following: select sum(total) from wo_main UNION select sum(total) from wo_main_hist This is great except it outputs2 different sums, one for wo_main and one for wo_main_hist. How can I combine them? Thanks! ...more >>

can one use the maintenance plan to do a scheduled backup job for a msde (sql2k) database? i tried to do it, but there is no schedule availalbe under the complete backup tab. thank you ...more >>

In a query, how do I convert a datetime to display as mm/dd/yy hh:mm AMorPM? I don't see that option on the list of values in Sql Books. The only option I find for including the time abbreviates the month rather than using numericals: Convert(varchar, myDateTime, 9) displays: Jun 17 1998 12:...more >>

In the following code, the cursor returns a single column with more than one row. I'm trying to concatenate these rows to show them in a single row separated by comma. The problem is with @degrees. I guess, since this is not initialized, it always returns null. How do I handle this? T...more >>

considering the following example: CREATE TABLE foo (fooid int not null, datecreated datetime) GO CREATE TRIGGER tr_foo ON foo AFTER INSERT AS UPDATE foo SET datecreated = GetDate() WHERE fooid IN (SELECT fooid FROM Inserted) GO if i wanted to do this on every single table, all ...more >>

considering the following example: CREATE TABLE foo (fooid int not null, datecreated datetime) GO CREATE TRIGGER tr_foo ON foo AFTER INSERT AS INSERT INTO foo (datecreated) VALUES (GetDate()) GO if i wanted to do this on every single table, all with the column named [datecreated], i...more >>

I need to copy a file from another domain to my sql server. Is it possible to somehow pass credentials when I try to do something like this? exec master.dbo.xp_cmdshell 'copy \\172.17.72.202\sharename\filename c:\import\' If not, do you have any suggestions for how I'd accomplish this in an...more >>

This is probably an easy one but I can't seem to get it. I have the following query: SELECT Category, COUNT(Category) FROM v_test v GROUP BY Category ORDER BY Category DESC but would also like to add one more column to show the percentage total of each category. how do i count the ...more >>

Hi, new to TSQL, I'm facing a strange issue with a very simple stored procedure (sic). My procedure checks for @dbname existence. If @dbname exists on server I drop @dbname.Table1 then create a blank Table1. If @dbname doesn't exist I create @dbname, grant access to user1 then create @dbnam...more >>

SQL Server 2000 Standard Edition + SP3: -- This returns no records in Query Analyzer SELECT * FROM SampleSourceArchiveAndProfilingView WHERE (SampleSourceKey = 1650) AND (CompanyLocationKey IS NULL) -- This returns a count of 376981 SELECT COUNT(*) FROM SampleSourceArchiveAndProfilingView...more >>

I've developed a proc that takes an input parameter @TopN Int. I want to use it to dynamically pull the top n records from my DB as such Select TOP @TopN UserID, Metric1, Metrics2.... Order By Metric1 I can only get this to work if I use an integer constant. i.e. Select TOP 10 UserID I...more >>

Saw a friend of mine using a varchar field like a bit field (only storing 1 character). I told him that it would be better to change that to a bit field, and he said, Why? What is the difference between Varchar(1) and Bit? Thanks, Drew ...more >>

Hi, Could someone please tell me the "best" way to index a table? Is the best way (defined as quickest access to data) to create an index on every field individually that might be queried, or is it best to create an index that contains multiple fields if that's how they appear after the WHERE...more >>

I am executing the following query to determine the number of issues closed and opened in a problem tracking system: declare @BeginDate as datetime declare @EndDate as datetime set @BeginDate = '04-01-2005 12:00:00 AM' set @EndDate = '12-31-2005 12:00:00 AM' select [Date], S...more >>

I need to create a concatenated field based on both sides of a LEFT OUTER JOIN. When I tried this, I got all nulls in my resultset for that field. What I wanted was whatever is in the left side concatenated with nothing if the right side doesn't exist. How can I achieve this. My current S...more >>

How can I query to see all of the triggers that have been disabled or enabled?? Is there a way to do this using query analyzer or using a gui tool? THanks Richard...more >>

i'm trying to write a batch that will perform a complex task using set-based selects instead of a row-based cursor. let me know if you can help me figure out how. description of what i'm trying to do: there is TABLE1, TABLE2, and TABLE3 i want to select each row from TABLE1, do some analy...more >>

Hello! I have got a table with clustered key defined on primary key. This tables is referenced using FK by hundreds of other tables using its primary key. I am trying to build clustered index on different column in the most efficient way. It looks like I have to drop PK constraint first bef...more >>

The solution works great although I am having a little trouble understanding the logic, particularly HAVING MIN(A.accounttype)<MAX(A.accounttype). How would I modify these query to select only those securityIDs that are held exclusively by accounts with a type of A? SOLUTION: SELECT P.sec...more >>

I am running an insert sp that looks like this ***************************** @x int, @x1 int, @x2 int, @NewID int AS BEGIN INSERT INTO TABLE1 (x,x1,x2) VALUES (@x,@x1,@x2) SET @New ID = (Select @@IDENTITY) INSERT INTO TABLE2 (@newID,@x) END *********************** I am getting...more >>

Hi all, can someone please explain why this is happening: I execute the following: DECLARE @temp VARCHAR(500) SET @temp = '' SELECT @temp = @temp + '[' + T.NAME + ']' FROM MyTable T SELECT @temp Say [MyTable] looks like this: |NAME| -------- A B C D Here are the res...more >>

Hi SQL Query Expert, My table looks like this: CONTRACT_PK PARENT_PK CONTRACTOR_NAME CREATED_DATE 1 <NULL> ABC Company 4/7/2005 11:10:10 a.m. 2 1 XYZ Company 4/8/2005 10:10:1...more >>

Hello -- I have a database that is 102GB in size. It has been growing expotentially every month for the past couple of years. Since we have a bunch of similar database, we did not understand why this database was so much larger than the other databases, considering roughly the same kind/am...more >>

I am executing the following SQL query to determine the number of entries per date in a table: select entered_date "Entered Date", count(*) "#of SRs" from nxsdfct where defect_status in ('Fixed', 'FIXED', 'Assigned to QA','Hotsite Verified') and prod_code = 'aSU' group by entered_date ord...more >>

The query belowe does what I want it to do but it only lists stores greater than 0. I would like it to list all the stores regardless if it doesn't have any. I have linked a table with all thew sotres in it so I thought that would work but it doesn't. Anyone have any clues? Thanks. SELE...more >>

I have three queries that only differ in their ORDER BY clause but return three different result sets. In the examples below "Takedown" is a table, "CommitID" is an int column, and "TakedownDate" is a datetime column, and neither of these columns have NULL values. select * from Takedown where...more >>

How can I find out the sizes of the columns that are returned from a query? I need to know so that I can pad each one to it's full size with spaces so that everything will line up using a mono spaced font in a textbox .NET control. If I fire off a query and get back a DataSet of results, the...more >>

Hi SQL Query Guru, My table looks like this: CONTRACT_PK PARENT_PK CONTRACTOR_NAME CREATED_DATE 1 <NULL> ABC Company 4/7/2005 11:10:10 a.m. 2 1 XYZ Company 4/8/2005 10:10:12 ...more >>

Well i have a database on my production server which is containing a table having 10 million records and daily 10 - 20 K records are being inserted into the same. The Table is also containing 25 indexes on different columns so which is best time and way to reindex my table for newly inserted ...more >>

I hope this doesn't get posted twice, I was editing this message a minute ago and I hit something and it went away. I was using a web submission form, so I don't know if I submitted by mistake or killed the submisison. Anyway, I have a table TaskType: TYPEID - int TYPE - varchar(30) S...more >>

Hi, In excel, 0.018/12 give me the following result: 0.00666666666666667 But in query analyzer, select CAST((0.08/12.0) AS real) transaltes to 0.006666. How can I get the same result in query analyzer as I am getting in Excel. TIA...more >>

Hi All, How can I get the datatype of a column using a query? Thanks, Sanjeev ...more >>

I'm using the following query to determine the TotTons produced daily. A sample data set is included. On days that two grades are produced, I'd like the Grade name to be a combination of the two grade names separated by a \. For example the desired Grade name for 08-01-05 would be H\V. A...more >>

I have a database with 50,000,000 rows of data which is approximately 7GB in size. The samples with date time (sampled_time) are in the varchar(24) format. Our select statement use the sampled_time to restrict the data that is retrieve from the database. How can I add index to this tabl...more >>

Hi, I am writing a program to enable users to import data to SQL Server DB. For this, I build a dynamic query on the fly using the columns (first row in the spreadsheet) and try to insert data into a SQL table. Most of the times it works. The problem happens when there are some data issues ...more >>

In a many to many relationship, say Product to Orders with ProductOrders being the associative entity, what is the most commonly used definition for Primary keys (PK) in the associative entity and why? a)ProductOrders: ProductID - PK, OrderID - PK OR b) ProductOrders: ProductOrderID(PK, mostl...more >>

Here is my select statement: SELECT 'INTOL' AS company, pm.newpartnum, '', cast(month(getdate()) AS varchar(2)) + '/' + cast(day(getdate()) AS varchar(2)) + '/' + cast(year(getdate()) AS varchar(4)) AS effectivedate, vend.vendornum, CASE WHE...more >>

We have developed a application in SQL Server with Data Integrator.. The DI refers xp_cmdshell to call a .net application which takes data from 1 file and generate output in another file.. there are 5 such sequences (A , B , C , D ,E)..which runs one after another... The development environmen...more >>

Hya, I have a field in a table call PinNo PinNo --------- 123 <null> 343 <null> <null> 124 232 I need to create a constraint/ index to guarantee that the values of are unique if different to null, I cannot create a unique index because I have got a Key violation (due to the ...more >>

Hello All, I have a stored procedure that has an argument @search_criterion. Now is there a way to check whether that argument has only number data or if there are characters as well. Many thanks, Marios Koumides...more >>

Hi, (using Windows 2000 Server/SQL2K Ent Ed SP3a) I am looking to create a job with a number of steps inside it. I need the job to run on a sunday afternoon when our OLTP system is at our quietest in terms of user connections and throughput. The job purpose is to copy data (24,000,000 rows...more >>

Hi Guys, I appreciate that giving an answer to this might be difficult because I haven't posted the table schema, but the problem I'm getting only happens because there is a lot of data in the table. I'm running the statement: select count(*) from table1 where column1 in('A','B','C') ...more >>

I'm trying to build a new script to create a Stored Procedure. As part of the SP it creates two temporay tables, populates them and then return the resulting data. It then drops the tables. If I run the contents of the Create SP script (i.e. the actual bit that does something) it's fine. But,...more >>

Hi Problem - I want to create a query that give me this results Table, Column , Datatype, Rowcount, Size. I have a question though, I've read in BOL about how to calculate space based on variable and fixed lengths of datatypes. What I am not sure about is how to sql allocate space. Le...more >>

hi, I want to ask about how to transform capitalized words to properly cased, e.g. user input: HI ALL! I'M A NEWBIE! output: Hi all! I'm a newbie! How to do that?...more >>

Hello All, When I run the following query against Northwind..Orders table I get the output in the following format : WhichQuarter QuarterlyTotal ------------- -------------- FirstQuarter 274 FourthQuarter 202 SecondQuarter 181 ThirdQu...more >>

Hi, I need to update my table using its own data. Suppose that we add another field(BossName) to Employees table in Northwind. Now I want to update its value with the name of each employee's boss. I have written this TSQL command but I'm not sure if this is the best: update employees set Boss...more >>

Hi, I've an application that consists of a of main table. Each record requires a numeric reference and these references must be sequential with no gaps. At the moment, this reference field is of an identity type. This works fine most of the time, however every now and again the identity fiel...more >>

Hi all gurus From Pubs database I want to get some ObjectProperty value from NorthWind ObjectProperty(<object id of Northwind DB> , <property>) Is it possible? Thanks ...more >>

posting this on behalf of someone else, the question is how to produce the output in such a way as to get a Count(*) for Distinct dates from the sample query below this is what we have so far SELECT DATEADD(ss, time_stamp, '1970-01-01 00:00:00') AS Expr1, CONVER...more >>

Can anyone tell me why on earth you can't have two cascade paths to a table ? This essentially means we cannot use RI and have to maintain triggers for our DB. I see that they haven't fixed it in 2005 either. ...more >>

Hi All, I have a small problem.... I need to replace stored procedures in an SQL database with ones from another database without touching any of the tables or data is there a way I can do this? Any help would be great Thanks Gav ...more >>

Hello, Today I am creating my first one-to-many relationship database. My main table is: USERS Then I have 4 tables related with this one: PAYMENTS, ORDERS, BOOKS, ARTICLES For each user I need to create a field named VALUE. VALUE = N(PAYMENTS)*4 + N(ORDERS)*2 + N(BOOKS)*10 + N(ARTICL...more >>

I have parameter @cona. If @cona=1 then ................ from lokStanje l INNER JOIN (objSta Sc INNER JOIN artCona Ac ON Sc.ART_SIF=Ac.ART_SIF AND Sc.STA_OZN=@STA_OZN INNER JOIN LOKCONA Lc ON Ac.CONA=Lc.CONA) ON l.LOK_OZN=Lc.LOK_OZN LEFT JOIN ................. If @cona=0 then--I don't...more >>

Hi, I Execute & Trace through SQL Profiler the following SQL Statments though It is working fine : create table #Temp (id int,Name Varchar(100),Xtype char(4)) insert into #Temp select so.Id ,so.Name,so.xtype from dbo.sysobjects so select * from #Temp drop table #Temp SQL Profiler Report...more >>