Is there any significance or reliability in putting an ORDER BY clause in the "derived table" of an INSERT INTO statement? In the example below: #A is a table with an identity column representing the order in which entries were made into table #A If these records are copied to #B with a INSER...more >>

What are the recommendations for running multiple instances of SQL Server on a production server? Is the general view that you should avoid this setup? ...more >>

I have a structure organization in tree form, for that I set up the following tables: T_ELEMENTOS IdElemento Nome 01 MJ 02 SNJ 03 SNSP T_HO IdHO IdElemento IdHOPai 01 01 NULL 02 02 01 03 03 01 In Oracle for me to see the tree I pass the following instruction SQL: SELECT * ...more >>

I am writing scripts to monitor sql server. Microsoft supplied WMI scripts with sql 2000 CD which uses DMO objects. It is easy to write MOF using perfprov. Which method is less overhead for the server?...more >>

Hi, I am trying to update a field in my table after update - this field is a datetime field and I want to capture the datetime that the record was updated. Please help, Frank...more >>

I wrote some SQL for SQL Server 2000: select case A_Number when NULL then 9999 else A_Number end ... When A_Number is NULL, case returns NULL. I thought it would substitute 9999. Why didn't it? (And yes I know about ISNULL.) Kevin...more >>

I have a work order system with 6 tables. When the first asp form is submitted it creates a record that has an identity field in the maintable. it also inserts a record into the other 5 tables putting the identity field in each table as the PK. The user then submits an asp form for each table ...more >>

Greetings, How do I insert a column on a table into a certain order? I have seen this question a few times in my searching the web, and a few people have answered something to the effect of "just add it to the end and use a view to change the column order". I don't mean to be offensive, but that'...more >>

Hello, Im getting the following when trying to bcp out a query w/ a lot of fields Starting copy... SQLState = S1000, NativeError = 0 Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Warning: Server data (3046 bytes) exceeds host-file field length (0 bytes) for field (1). Use prefix le...more >>

Hi, I have simple table with information Col1 Col2 Col3 Col4 1 1 1 1 1 2 ABC 1 1 3 1 1 4 2 7 3 2 7 4 2 7 8 ...more >>

Hello there! I stopped a DTS package, by selecting the "cancel" button, after that I closed the window, and now I cannot connect again to the database, it gives me a "Connection Timeout Expired" Is there any other sql command should i run, in order to stopped completely any processes th...more >>

Running the SQL statement below returns multiple rows for each company. I need it to return the most recent row based on the column "DatePulled". Select total=Sum(PipelineData.Amount), DatePulled, [Schedule].[StatusCode], [Schedule].[PurchaseType] AS [EQUIPMENT TYPE], Customers.[Company N...more >>

i really really really could use ideas for how to perform a recursive self-join, or emulate one, or it's effects; without having to resort to thousands of cursor operations. Is there any way? ...more >>

I am trying to use VBScript to automate some SQL NS operations. I receive the following error on my machine on which I have installed and re-installed SQL NS multiple times. Could not create object named "Microsoft.SqlServer.NotificationServices.NSInstance" Seems like the assembly is not regis...more >>

I'm working with a customer's Application that seems to be having some issues with Locks on a table. The table that has a PK of Column_1, Column_2, non clustered. The Select Statement is based on column_2 When the query is done the Server process for the query fills up the locks/objects w...more >>

I've create a DTS package. It will import a text file to sql server. The file is located in a computer on the network. When I execute the pakage, it works. However, when I scheduled the package, the following error reported. Can anyone help me? DTSRun: Loading... DTSRun: Executing... ...more >>

Hi, Anyone have some ideas about this one, I'd like to calculate the last = working day/weekday in the month for a given date. I'm trying to work = it into a user-defined function Select @lastday =3D getLastDay(@inputDate) Thank for any help! --Michael...more >>

Hello, Perhaps someone could offer me a better way to accomplish this, I need = to calculate a month-end date. For my purposes though, this date = doesn't necessarily reflect the last day of the month, but rather the = last weekday of the month that doesn't fall on a holiday. (I'm not = try...more >>

Hi all, Is it possible to provide a join hint when using the 'old' comma syntax for the join? Thanks, Ami ...more >>

ok - i must be doing something wrong - or microsoft would have fixed this bug... to calculate age. first check If datediff(y, dateofbirth, getdate()) < 1 set age = datediff(yyyy, dateofbirth, getdate()) - 1 Else Set Age = datediff(yyyy, dateofbirth, getdate()) the problem is that the ...more >>

Hi, I am trying to return a varchar to my calling application (VB.NET). Basically i am looping through a set of databases whos names are derived from a comma seperated list passed into the procedure. I am checking if the username i send to the procedure exits. If it does i want to add a '1' to t...more >>

I have a nvarchar field of 8 digits that represents a date. ex. 20040701. How do I convert that to a date? ex. 07/01/2004 ...more >>

How can I query TableA.RecordID and find all RecordID's that may exist in TableB but doesn't have a particular value? Would I need a stored procedure and cursor through, or can it be done in a query or two? For example: Table A: Table B RecordID 1 Name: ab...more >>

Hello, Is there much performance difference between an INSERT that addresses the table directly, and an INSERT that addresses a view which is mapped to the same table. For example: Table1 View1 = SELECT * FROM Table1 What is faster or are they the same? 1) INSERT INTO Table1 or 2...more >>

Hi All, First of all, I am facing problem for not following the expert's advice to store datetime as YYYYMMDD format. I plan of modifying my client programs later as I will have to fix many programs. I get data from a different database (in the same server. That database works as tra...more >>

How can I write a query that would give me a record number of every record that does not have x amount of entries. For instance, I have a table that needs to have 4 entries for every record that exists in another table. Table A: Table B RecordID 1 Name: abc...more >>

Hi, I'm using sql mail and sql agent mail with outlook xp. When sql server needs to send email(via xp_sendmail or a job), the email is delivered to outlook and it is not send until I manually press send/receive button in outlook. How can I automate this? I mean I don't want to manually send emai...more >>

I can return variables from stored procedures but can I return a result set? ie I would like to return all the rows from select * from table Thank you ...more >>

I've been getting this error from running a stored procedure that has an "Update" statement. Everything I've read about this error so far advises to install SP1 to resolve it, but I'm at SP3. DESCRIPTION: Error: 1203, Severity: 20, State: 1 Process ID 55 attempting to unlock unowned resource PA...more >>

Is there anyway that I can prevent a trigger from being called from within a stored procedure for just the values being entered in the stored procedure. I know that you can alter table and disable the trigger but in this instance I nned the trigger to be active for records inserted/altere...more >>

Hi, I am trying to find the user sessions from the IIS Log file. I am using the W3CSVC Extended Log Format. I load the log file on to a SQL Table and filter data for the User Session. I am using the following query Select Distinct UserView.[User_Name], ( Select Max(UVMax.Access_Date...more >>

We are using a product called CinCom to print official documents for = mailing. The product retrieves DB info from SQL Server and CinCom = formats the document from an Intranet application and it creates a Word = ..DOC file that then can be printed. =20 We are having problems with sometimes t...more >>

I'm tryig to select just the order number, customer name and one date field from an Order Header table where an update just occured on the Hold Field. When an order's hold status changes, wanted just those 3 column data copied into another table for that particular order, but i'm really just begi...more >>

Hi all, This should be easy for most of you !! I have to tables A and B. A is the master and B is the details. A contains a date column. B contains a date column and a bit column which denotes wheter a record has been completed or not. Now I would like to use a query to update the date in...more >>

Hi, My SQL Server is Win2K SP4, SQL 2000 standard SP3. When I run the code that is a stored procedure in Query Analyzer it takes 22 secs but If I executed the stored procedure it takes 24 mins? Does anyone know why this would be? Thanks in advance Danielle...more >>

Hi, I want to know if there is a maximum number of tables that can be created in one database. Thnx. Stan...more >>

Can any one advise or help me on accessing items contained in a collection object using T-SQL...more >>

Hi, For sending email from within a stored procedure, I now use the stored procedure sp sendSMTPmail (Source code: http://www.databasejournal.com/img/sp sendSMTPmail.sql) I would like to switch to using xp_smtp_sendmail (Download: http://sqldev.net/xp/xpsmtp.htm) because it seems to be a lo...more >>

Do any versions of SQL Server support the following functions, as they appear in the Oracle Database:- 1) XMLElement 2) XMLAttributes 3) XMLForest Thanks in Advance for your reply s...more >>

Hi All, I just installed the SQL 2005 Beta 2 and I couldn't find the Diagrams that is have in SQL 2000. Can some one tell me where it is? Or... if it exist at SQL 2005? -- Abraços, Ruben Zevallos Jr. [ruben@zevallos.com] +55 98 8117-2509 ------------------------------------------- ...more >>