I have a table like this: ID | F1 | F2 | F3| ______________ 1 | A | 3 | 4 | 1 | B | 2 | 5 | 1 | C | 8 | 10 | 2 | A | 25| 40 | 2 | D | 35| 50 | I need a query to return the following recordset: ID | F1 | F2 | F3| ______________ ...more >>

Hello. Thank you in advance for reading this question. I posted this question in the newbie area, but have not had a response. Is there a way to get all the fields for all user tables that are returned when you do sp_help tablename? I think I have found the columns I need in 'syscolumns'...more >>

I'm trying to create a script that will create triggers on the tables in the database, however, I need to skip any table that contains any one of these three data types: text, ntext or images. I'm currently trying to use this statement: Select DISTINCT t.table_name from INFORMATION_SC...more >>

Help: How do I constuct a queery returning all the rows from table A that do NOT have a match in table B for a given column? To be more specific, I am pulling a copy of the sysprcesses table. I then want to report out the rows that ARE NOT in my permitted logins (i.e., potiential problem...more >>

I'm currently working on editing my sprocs to include the newly created 2006 partitions, and have run into an issue. Currently, I'm querying sysobjects and syscomments and looking in the text column to find sprocs that utilize partitions. The problem is that my sprocs are quite large, and in...more >>

Hello all I have a situation so far I have been unable to find a solution I have 3 tables like so CREATE TABLE [dbo].[Users] ( [UserID] [int] IDENTITY (1, 1) NOT NULL , [CustomerID] [int] NULL , [FullName] [varchar] (50) NULL , [UserName] [varchar] (50) NULL , [Password] [varchar] (50...more >>

I am trying to create a matrix of counts to help identify our data holdings. Thanks to Joe Celko I now know I need to perform relational division to find datasets that match the criteria set up in a secondary table. I'd be happy to initially just get the counts for each category, as I could ...more >>

I need to create a view linking driverID as Integer on Table A and as character data type on table B. I need to convert B.driverID into numeric value, assign zero (or another value) to non-numeric data (Select all records from table B). Help is greatly appreciated Bill ...more >>

I am in environment where users constantly ask for data; and I constantly writing queries with correct joins and conditions. And sometime, I give users less than perfect data due to lack of time and effort. I guess a lot of you out there are in a similiar environment. I'm just thinking, wha...more >>

Hello, I compiled Vyas's SP sp_generate_inserts (http://vyaskn.tripod.com/code/generate_inserts.txt) and it works perfectly on my test servers - all except one server. On this server, I get the following errors Server: Msg 536, Level 16, State 3, Procedure sp_generate_inserts1, Line 33...more >>

Hi, When I look at the script generated from the ITW it often doesn't have all the recomendations I see on the recommendations page. Is this normal? The doc on the wiz seems a little thin... ...more >>

First I want to thank Mike Epprecht for helping me with the first part of my problem. I will sork on my use of caps. Actually I had copied and pasted a bunch of that stuff. Your solution worked and I see why that is the way to go. Thanks again. I ran into a problem I hadn't initially plan...more >>

Does anyone know of a way to get SQL to send email to multiple recipients consisting of a word (.doc) attachment(s)? I'm trying to get an app to create .doc reports and mail them to people... anyone? ...more >>

I'm wondering if there's a way to make a query for something like this... .... even if it's a join from a table to itself. One example is say that I'm tracking the number of times a customer orders something... so, simplifying it down, I have a table with: Customer Number Number Of Orders ...more >>

I have a blank SQL database that will be generated with tables transferred from an outside source. Once the tables are there, on a daily basis, incremental data will be sent to an FTP folder in order attach them to the original tables. What I need to know is how to attach the incremental data ...more >>

Hi, maybe I have a performance problem with a relatively simple insert or update command. The DB contains two tables connected by an id. The following stored procedure takes about 3ms on a 2GHz/1.5GB Centrino. This seems rather long to me. Typically a couple of thousands of these insert or up...more >>

Hi, This is probably a simple question, but I'm new at this. What I am trying to do is use the results from one select statement in my final select statement. So basically the following: select x from table_a where name = @inputname select date, name from table_a where level like x + '%...more >>

I'm doing an insert into a table with two varchar columns. One is NOT NULL 175, one is NULL 12. If I insert values of 176 characters and/or 13 characters, the value is inserted, just without the last character. Anything in excess of the field datalength is truncated. How can I prevent that...more >>

Since the query builder (not the query analyzer) in enterprise manager does not support case statments, I was wondering if there's a way to structure the following in a different way (without case statements) so that the query builder works. I thought there might be a way to do soemthing with NUL...more >>

I have an app that needs to insert 1 million records into a table. The table is very basic thus far, with no triggers or indexes on it. The procedure takes in excess of an hour, which I am willing to accept if I have to, but I would like to know what tools are available to streamline this pr...more >>

I'm am new to using SQL Server. In Oracle, when I create scripts to manipulate data, I have a log file automatically created from within the script by issuing the SPOOL command. Is there a similar functionality in SQL server? besides the log file which records each transaction. Is there any ...more >>

Hello, I have been using sql server for many years and have always been happy with it (and I am still). I have been totally surprised when I discover a bug yesterday. You may not call it a bug but it's at least an unexpected comportment from my developer's point of view. Here is how to ...more >>

Here's the first statement: select DCII.SEQ_NBR, count(1) FROM PS_DEM_CHG_INF_INV DCII , PS_PL_TRANS_IT_OPT PTIO WHERE DCII.SCHED_DATE >= '2004-10-05' AND DCII.SCHED_DATE <= '2007-10-05' AND DCII.DEMAND_SOURCE = 'PL' AND DCII.PROCESSED_FLG <> 'C' AND PTIO.PROBINST = 'COMBE_1005' AND DCII.B...more >>

Does anybody know how to get a list of the Jobs currently running on SQL server select <what> from <table> WHERE <cond> what do I select from where to get the list of active jobs. Thanks -- bnhcomputing...more >>

Hello I have a basic table design question. Actually it's 3 tables. Each is a separate entity and every row in every table is unique but I need to create associations between all three applying good normal design. Table 1 needs to be related to the data in table 2. It's a many to many r...more >>

Hey Folks, A 3rd party product uses some kind of bulk insert to update a table several times during the day. Sometimes the data is overwritten, which effectively loses history. A trigger on the table is bypassed by the bulk process. I'm hoping there is another way to get notified when th...more >>

I have the following send mail sp Create Procedure dbo.sp_send_mail @sender varchar(100) = 'PC1, @sendername varchar(100)='Sean', @serveraddress varchar(255)='10.10.10.193', -- email server was: mail.nexsure.com @recipient varchar(8000)='sean.john@rela.com', @recipientBCC varchar(80...more >>

Is there any way to remove the dash line in a query result? use pubs select emp_id, fname, lname from employee emp_id fname lname --------- -------------------- ------------------------------ A-C71970F Aria Cruz A-R89858F Annette...more >>

have a couple of quesitons that the books online are not answering about this command. 1. when running the exe file does it use the servers autoexec.nt ? yes or no 2. If I can setup a map path in question one, can i use a mapped path? yes or no 3. I need to send something like this( f:\db...more >>

Hi, Don't know if this is the correct newsgroup or not, but I'll give it a whirl. I currently have the following stored procedure... CREATE TABLE #stockLedgerReport ( DEPOT NVARCHAR(4), onVehiclesStock_ULSD DECIMAL DEFAULT '0.00', onVehiclesStock_GasOil DECIMAL ...more >>

Using restore command , I am trying to create a db on Dev server using a back up file of a db from a Prod server. The back up file resides on Dev server. My restore operation terminates after 2 hrs saying "you are not server user". Keep in mind that I have very limited rights to the Dev server (...more >>

I have tables with 3 keys, where I am changing data in the third key field. The problem is that there are some cases of duplicate keys, where Key1, Key2 and Key3 fields are identical. So I am looking for a query that would first find those rows so I can delete them. I can do this through a ...more >>

Why would the same have different run time...less than 5 sec vs more than a minute. ...more >>

Hi All, I have a question about SQL availability. My Application makes many calls to SQL Server every second. What I’m trying to do is write some fault tolerance into my system, so that if SQL goes offline my code will start writing the data to an alternate location until SQL comes back o...more >>

I'm populating a criteria list from a secondary table and want to pull back the matches in the primary table. However I want to set up something like a an AND criteria between the results. My current query is: SELECT Name, TopicID FROM PrimaryTable WHERE TopicID IN (SELECT TopicID FROM ...more >>

what if you wanted to kill the rollback? can it be done?...more >>

My database table has approx 400,000 rows, with an integer Id as primary key. I have an array of ids, with N elements where N is typically between 500 and 5,000. I want to retrieve into a collection all rows whose id matches a value in this array. What is the 'best' way to do this and/or what...more >>

Hi All I once saw this app that allowed you to pick a SQL server and provided your signon details; it then displayeda list of DBs on that server which you had access to with the provided signon. Does anybody know how they did this? How they got the list of databases off the server? T...more >>

In my application I try to select data from linked table. Then i do this i catch the exception. How to correct this problem? and Why this problem is occured? Stack trace: Caused by: java.sql.SQLException: [Microsoft][SQLServer JDBC Driver][SQLServer]The operation could not be performed beca...more >>

Hi, Is there a way to change the name of login account (windows) without having to drop the login and recreate it under the new name. Scenario: I had a bunch of users under a particular domain. Now the domain changed, and all the login accounts with the old domain prefix no longer work. ...more >>

I am trying to write a stored procedure but I don't know how. I want to declare a list of integers, but what shall I declare it as? Declare @list Select * from tblTable where id in (@list) It works as this: Select * from tblTABLE where id in(16,19,22,24) I hade tried to declare @list as var...more >>

Hi, I am looking to schedule a server side trace for a production server. I need to capture all events on the server from 2AM until 9AM. The server is automatically rebooted at 12-1AM so I have scripted a trace and wrapped it in a stored proc. A SQL Job is scheduled for 2AM to run the trace...more >>

Dear Frineds I am facing the problem for "ogin failed for user null not associated with trusted sql server" I have two server SERV1 AND SERV2 WHICH ARE IN SAME DOMAIN THE SERV1 IS A Domain controller. Now i have created a view which is refering to the table in the SERV2 server. The ve...more >>

Hi guys I have to write a reporting system for a .net web app which uses MSDE to hold data. The reports that we require are quite complicated, pulling data from a number of tables. Let me give you a pseudo-sql example... SELECT ProjectID, ProjectName, AOW, AOD, Client FROM Projects T...more >>

Hi - is it possible to have 2 Identity (auto increment) fields in table? Each time I try to change the one to an Identity column, it changes the other to NOT an identity column. Thanks, Mark *** Sent via Developersdex http://www.developersdex.com ***...more >>

Hello, I need to know the Column(s) that are Updated in a sp to determine which actions to take. I'm looking for something like: IF UPDATE(ColumName) ...... or IF NOT (SELECT serviceLastBy FROM tblInstallations WHERE tblInstallations.installationID = @installationID) = (SELEC...more >>

Hi guys, i appreciate the speed at which the response come and the quantity as well. i want an identity column with repetitions allowed after a certain condition is reached. actually, i want to generate invoice number automatically. it will be an integer data type. but the seed should i...more >>

I create format file with DTS then I execute the bulk insert statement: BULK INSERT tableTest FROM 'c:\testData.txt' WITH (FORMATFILE = 'c:\testFormat.fmt') but I get error: string or binary data would be truncated Why? What could be the reason? Format file seems to be ok. Regard...more >>

Hi Since short time I am experiencing the following problem when testing a stored procedure. Error on Insert since the following set options are not set properly: QUOTED_IDENTIFYER Sorry, that I do not have an exact error message, since I am working on a german server, and I only trans...more >>

Hello, I am confused with exists keyword. I want to update Previous Year Sales on a Summary table by self join. I changed the degin of the table and added new columns. Now I returns more row that ý expected. This was working before. --Update Prev Year's Sales Update MonthlySales ...more >>

Hi, I'm still trying to copy a SQL server database from one server to another, but now something strange happens. A view is created on the target server that doesn't exist on the source server. It did exists on the source server months ago, but I deleted then(including it's dependicies). S...more >>

Hello, is there any way how to achieve correct behavior of T-SQL while XML parsing error occures? If I execute (it does not matter how - QueryAnalyzer, ODBC, ADO) following script execution halt on sp_xml_preparedocument. DECLARE @result int DECLARE @hdoc int DECLARE @doc varchar(1000) ...more >>

Hi, In access, a form creates a new record. This record is not updateable - message says that someone else is editing the record. However, in EM, the record is updatable. What a I doing wrong? Do I need to supply further info? cheers, NEIL ...more >>

Hi all, Just wondering: If i use the following as default values for a stored procedure all is well: @sdt SMALLDATETIME = '19000101', @edt SMALLDATETIME = '20790606' However, when i try and change @edt to reflect the current date EG: @edt SMALLDATETIME = getdate I get this error: ...more >>

Hi, I have a very code which takes very long time to execute, it might take = more than a day to execute. So I want submit that part code to run asynchronously through job. When I am submitting this code through job, it execute the code. But = problem is that state of jub is also running, i...more >>

Hi all, Just a quick question. I am calling a stored procedure which has two optional parameters. Optional meaning they are declared with a default value NULL. If for some reason and empty string send sent to the procedure will the internal value of that parameter be NULL or "" ? Area...more >>