I've been running the following prior to creating stored procedures (on the advice of folks in this newsgroup): set ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS On go set NUMERIC_ROUNDABORT Off go In general, should the same be run before...more >>

I have a classic ASP application that uses Access as the db. I'm in the process of attempting to migrate it to SQL Server, and am learning about SQL Server as I go. One part of the app imports data to the db. This is not the most used part of the app - possibly once every month or two. The ...more >>

Hi, I have a simple query: SELECT COALESCE(fname,' ') + COALESCE(lname,' ') as Fullname From Table I changed fname from nvarchar(255) to char(20)... and to my surprise my query result was diff. I got big space (or tab) in between fname and lname resultant output of above query. I chan...more >>

To SQL experts, I have a table with an Account column. The Account number is a five = digit integer. I want to group my account numbers by the first 2 digits = and do perform a sum on another column based on the rows grouped. I can = parse the first 2 digits using the "LEFT" or "SUBSTRING" fun...more >>

Suppose we have data like. SAMPLE Data code is at the bottom. I have posted another posting like this and chris posted a reply with code but it did not had the correlation table and did any comparisons with that. The topic is complicated look query Any help is appreciated ID Value A 100 B ...more >>

Hello i have a sql script creating a database en tables. This script works fine when executing with osql.exe on the default instance. When i execute the script on a named instance, the database is created but create table is not executed. When i look in to the log file i see no error messag...more >>

Hello All, Wondering if anyone ahs thoughts on what the best practice for ownership of a DTS package is? We have several biz-critical packages that are owned by individual developers. One of the owner/developers has recently terminated employment and we wish to remove the associated login...more >>

I am not sure how to do this I have a select statement the uses a where clause. IF EXISTS (SELECT TasksID FROM FamilyJournal.Tasks WHERE ((FamilyJournalID = @FamilyJournalID) AND (TaskTypeID = @TaskTypeID) AND This is the part I am stuck at, I need to check if there is a mat...more >>

I have a column of data in a sql table that i want to combine into a single field for outputting. I was looking at some of the examples of pivoting and have not found one that does what i need. (I'm using SQL Server 2000, FYI). To give a visual on what i have: NOW: ----------- ITEM1 ITEM2 ...more >>

I have table "List" [code] CREATE TABLE [list] ( [listid] [bigint] IDENTITY (1, 1) NOT NULL , [enterpriselistid] [bigint] NULL , [countrycode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [areacode] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [n...more >>

HI all, I have an insert trigger that inserts additional rows based on the row that the user inserts I also have a bridging table that gives me a m:m relationship with another table. After the user insert rows into the policysection_division table, the trigger then inserts multiple rows(dep...more >>

I am experiencing an error message when I run a TSQL and I don't know how to correct it. Server: Msg 8115, Level 16, State 8, Line 38 Arithmetic overflow error converting numeric to data type numeric. In this code I am extracting hierarchical information. I am going 6 levels deep. The s...more >>

Hi, I need to evaluate a paramter and based on it's value add a condition on a different column as in the bellow example. How do I get it write? /* CREATE TABLE T1 (KeyCol int, DT1 datetime, DT2 datetime) INSERT T1 SELECT 1, '20060101', '20060110' INSERT T1 SELECT 2, '20060201', '20060210...more >>

Hello, I have very big table. I don't know the exact size, but the total size of the DB is ~80 GB and the table has ~18 million rows, its columns types are: int, varchar 500, float, char 500, char 100. I'm trying to do the following query and it takes a very long time: SELECT col1, count(*...more >>

Hi, I am newby and I need help, how to create query. I have table - Employee with columns - EmployeeID, Department, Salary How can I get all the Employees ID (EmployeeID), for every department with minimum salary? By example - there are the next data in the table EmployeeID, Department...more >>

I have a phone field I need to validate. I need to remove periods, commas, dashes from the phone numbers. Does anyone know how to write this query? Thanks, Ninel -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200608/1 ...more >>

I have 3 tables, t1, t2, t3. T1 and T2 will have a foreign key to T3. T3 has data like this: Fieldnames: ID, Code, TableName Sample Data: 1 A t1 2 B t1 3 C t1 4 A t2 5 B t2 6 C ...more >>

I have an int column with the unique constraint. Any easy way to generate obfuscator copy of the column? Each time it require different set of value which is also meet the unique constrant....more >>

Hello. I have a stored procedure that I have setup which when it fails (return code anything but 0) I would like to have a few people sent an email notification of this failure and maybe a little bit about it. I know this can be done but would like some suggestions on how to approach it...more >>

Hi, I have the following data from Excel spreadsheet. LST125A LST075A LST040A PBL125A LST125A 60 240 240 360 LST075A 240 60 240 360 LST040A 240 240 60 360 PBL125A 360 360 360 60 I need to extract the data in the following manner. ColNumber RowNumber ...more >>

Hello, can you please help me with this? Line Table L_ID P1_ID P2_ID Graph_ID Point Table P_ID X Y PointName Point data: P_ID, x, y, PointName 1, 11, 22, "one" 2, 23, 33, "two" 3, 34, 44, "three" 4, 45, 55, "four" Line data: L_ID, P1_ID, P1_ID, Graph_ID 24, 1, 2, 88 25, 2...more >>

Hello, I've got an intersting design/performace question. Lets say i've got a table of records, like so (untested simplified sql): CREATE TABLE records ( id int, userid int, createdate datetime, param1 int, param2 int ) What i want to do, is scan a range by the "createdate" column...more >>

Hi, I am trying to retrieve a table name from a field to pass on to another Select statement. This is to be a piece a bigger query. This is what I have tried: SELECT WFINDATE FROM ('DQUEUEDB..QUEUES.' + (SELECT TABNAM FROM DQUEUEDB..QUEUES WHERE QUENAM = 'RSNB_XFER') AS TABLENAME) ...more >>

Hi, Is it possible to use Profiler to learn when and how a particular column in a particular table is being used? For example, When it is referenced in a select list When it is used in a join on clause When it is used In a where clause When it is updated It would be nice to know what the...more >>

Do I have to do anything special in Management Studio to connect to a SQL Server 2000 server? Or is it expected to be able to connect without doing anything special (assuming I have valid login credentials)? ...more >>

Hi there, I'm using SQL server 2000 My question is how to calculate future working date like today is 8/16/2006 + 5 working date is = WED 8/23/2006 Thanks, Ed Dror ...more >>

Hello, I need to pivot the following data: CREATE TABLE #temp1(sessID int, sess int, sessCount int) INSERT INTO #temp1 SELECT 1, 1, 201 union SELECT 1, 2, 222 union SELECT 1, 3, 243 union SELECT 1, 4, 321 union SELECT 2, 1, 192 union SELECT 2, 2, 255 union SELECT 2, 3, 301 union SELEC...more >>

I have store procedure that returns a list of all user meeting a certain critiera. Now I need to cc everyone affected by the critera. I am able to create the email with the correct info from the query. But I need to requery the database and get the email addresses and cc them in the email Wha...more >>

Does anyone know the way temp table names are created in sysobjects? For example: create #paul (a int) then select name from tempdb..sysobjects where name like '#paul%' paul______________________.....__E2D6 where ... are some amount of under scores. Not sure what E2D6 represents, i...more >>

select distinct HumanResource.HumanResourceID CandidateID, ResumeID, KeyTable.RANK Rank from HumanResource join Resume on HumanResource.HumanResourceID = Resume.HumanResourceID -- search Resume table join CONTAINSTABLE(Resume, Content, @SearchString) KeyTable on Resume.ResumeID =...more >>

i just got an error when i startup my database 'could continue scan with NO LOCK due to data movement' anybody knows wht does it mean and solution? ...more >>

Let's say you have a database that has a trigger on five tables. And what if 3 of those 5 are disabled. How do you see which triggers are disabled and which enabled? It doesn't show up when you script it and I don't see it in sysobjects, but I know it has to be somewhere. (I'd like to know...more >>

I am trying to remove apostrophes from a column in my table. How should this be written so that it works in SQL Servere 2000? Update Zappos Set THIRDPARTYCATEGORY = replace(THIRDPARTYCATEGORY, 'men's', 'mens') Update Zappos Set THIRDPARTYCATEGORY = replace(THIRDPARTYCATEGORY, 'women's', 'wo...more >>

How can I find empty columns in each table in a database? Gracias. ...more >>

I need to calculate a fee given a certain value. The fee structure is tiered. Given the value 55, the fee for the first 50 is (50 * .005 = .25) . The fee for the remaining 5 is calcualted at a lower fee level. (5 * .004 = .02) . The total fee would be (.25 + 0.2 = .27 I'm looking for a select ...more >>

Hi, Does anyone know of a way to remove a range of characters in a string? For example, if I wanted to remove the characters with ASCII codes 058-063 as below from a string is there a quicker way of doing it than using the replace function several times? 058 -- : 059 -- ; 060 -- < 061 --...more >>

is there anyway to generate a report that will show which tables have cascade deletes? ...more >>

test ...more >>

I'm trying to get the name of the currently executing stored procedure within that procedure so I can add it to an error message using RAISEROR so I don't have to hard-code it. This only needs to work in SQL 2005. Rather than: RAISERROR(50002, 16, 1, 'update', 'UpdatePeople') I want to u...more >>

Hi all, I am having an issue using the SQLXMLBulkLoad. Here is the XML doc: <xml_feed> <Agent id="0000000" name="A Name" email="a@emailaddy.com" MCO="XXX"> <Transaction> <Type>BI</Type> <MVlastname>A Name</MVlastname> <PolicyNum>XXX0000000000</Po...more >>

Hello, I am writing stored procedures for SQL 2005. I keep writing the same code over and over, (Error variables and response). How can I create templates that will have the error catching code in it and all I need to do is change the name, table and variable information? Bryan ...more >>

I can post the code, and i will, but it but there is a lot of it. How can i test to see why a query that uses a derived table and then a select takes 47 seconds! but a temp table with the same query only takes takes less than a sec say 1/10 sec even? I'm asking two things: 1. does anyone kn...more >>

We have 2 databases. One is Five(Test database) and Second is SQLONE(Live database). i would like to copy live database(data, structure, keys everythng) into Five(test database). is there any script i can use? thanks Farrukh...more >>

I'm given a numeric range in 2 columns (start_number, end_number) and need to find which numbers within that range exist or don't exist in another table. Can anyone tell me what the best way is to do this? TIA ...more >>

Hi all, Can anybody tell me if there is a more efficient/easier way to complete the following query. The problem is as it is at the moment it takes 3mins 48s to execute and it has to be looped around another 54000 times!!! I think its the SUM lines and the UPDATE lines that are taking the time...more >>

I am a begginner to Transact-SQL and was given this situation with no examples to go off of with the information below to write an efficient query that returns the "CaseDescription" for all cases in the system, along with the defendant full name and the "ChargeDescription" of the primary charg...more >>

I'm building a new database that is intended to replace 4 existing databases. The new database will need to require (NOT NULL) a lot of data that is simply not in any of the existing databases. What are some reasonable ways I can go about requiring data in the new database while [having ...more >>

I have very simple query (may be stupid) question. Let's say I have a table tbl1 with two columns lname, fname lname fname John Doe Tom Tic Tee Toe Dee Doe I want to join the outout as fullname. Like 'John Doe' ty Chuck ...more >>

Is there a way to insure no OLE Automation Objects are open by other clients before calling sp_OAStop?...more >>

Hi everyone, I have the following statements to test the SQL Mail component: EXEC master.dbo.xp_StartMail EXEC master.dbo.xp_SendMail @Recipients='audrey@bacde.com', @Message='test', @Subject='test', @Set_User='dbo' EXEC master.dbo.xp_StopMail While the SQL stateme...more >>

Been beating my head on this. Help please. I'm trying to write a query to remove unwanted rows. -- CREATE TABLE #A (A int, B int, C varchar(2)) -- INSERT INTO #A VALUES (1, NULL, 'TI') -- INSERT INTO #A VALUES (3, 10, 'NV') -- INSERT INTO #A VALUES (4, 10, 'TO') -- INSERT INTO #A VALUES (5...more >>

Hi all, I have 2 databases, one is primarily filled with data (db1), and the second one (db2) is designed to contain a subset of data (i.e. subset of data from one table) from the first one. The reason for this kind of database design is beyond this topic Now what is the best way to 'copy' ...more >>

Been beating my head on this. Help please. I'm trying to write a query to remove unwanted rows. -- CREATE TABLE #A (A int, B int, C varchar(2)) -- INSERT INTO #A VALUES (1, NULL, 'TI') -- INSERT INTO #A VALUES (3, 10, 'NV') -- INSERT INTO #A VALUES (4, 10, 'TO') -- INSERT INTO #A VALUES (5...more >>

I need to access and modify an Data Reader data source using an ADO.NET provider, and change the SQL query property at runtime. If this were an OLEDB data source, I could specify parameters or set the SQL string from a variable, but the Data Reader doesn't offer this option. I've looked int...more >>

Hello, The application that I have developed involves loading 2 large files into 2 different tables in SQLServer database. These files have around a million records each and using these files, I create an output file which has several million records. Now, I have to repeat this process at lea...more >>

Hey guys, Have you ever tried to call the SqlDependency.Stop() method in a class destructor (C#)? It seems like the finalization process hangs after the call to the SqlDependency.Stop() method (for example the assignment after the SqlDependency.Stop() method call is never executed). ~Pro...more >>

I need to write something that will backup all databases with the exception of those that are subscriber copies. How can I identify: 1) that a database is part of replication; 2) that the database is publisher and not subscriber. Any hints to tables I need to query much appreciated. Thanks...more >>

Hi, Can someone help me ,how to find the space in the data inside the table. The problem is, there are junk characters appearing like " []" at end of the word, say - "Hello []" on the web page, but if i take a look into the database,it looks perfect with out any space/junk characters. I...more >>

I'm trying to automatically create a copy of a table with a stored procedure (it will be run everynight). I don't know what would be the best way to copy constraints. ...more >>

Okay a learned response: http://racster.blogspot.com/2006/08/rac-sealed-with-kiss.html Best, steve "Chris Lim" <blackcap80@hotmail.com> wrote in message news:1155638569.938676.172130@75g2000cwc.googlegroups.com... > Steve Dassin wrote: > > Given that your a great sql programmer have you...more >>

Hi I have a database using windows integrated authentication, and have added one windows group to logins - domain\domain users - as public. This group is now added to the specific database users and seems to have permissions on all tables and views without having to explicity grant access wh...more >>

Hello, I have this machine in which I have deployed my application. This machine has also a local instance of SQL Server 2000 SP4 and it runs Windows Server 2003 SP1. BizTalk is 2004 SP1. One of the orchestrations call a stored procedure in my SQL database and that stored procedure has a...more >>

I have a list of contractors and how far away they are from each site. If they are further than a certain distance from a site they are not listed against that site. So in the example below contractor id 107 is only listed against one site, whilst contractor id 101 is listed against two sites....more >>

...more >>