Would you kindly take a moment to look at the following: Table1 Columns: Id email 1 2 3 Data 122 d@d.com 3 1 2 133 a@a.com 2 ...more >>

Generally, is it ok to have one table with 4,5 foreign keys? Will this create any problems? Is this considered bad design? For every field in my table whose value must pre-exist in another lookup type table I have created a foreign Key for that field. Should I do this for lookup type relations...more >>

I need to send a reading list to customers ordered by Quiz data that the customer has given us. The 25 quiz questions are in a table called tblEmailList (sample below). The reading list is in tblReadingGrid (sample below). I need a query to pull the reading grid and order it by severity of t...more >>

Hi, I am a newbie to sql. Can anyone tell me how can i convert the default date and time format like '10/19/2004 4:23:39 PM' to '10/19/04 16:23:39', omitting the century and making the time in 24hrs format cuz i was unable to find any pre-provided time format like this. Secondly...is it poss...more >>

where do triggers show up in SEM ? ...more >>

Inside a Stored Procedure, I am creating a variable of type table, like so: DECLARE @temp TABLE ( ... ) That works just fine. Later in the SP, I want to ALTER that table, but it doesn't seem possible. ALTER TABLE @temp ADD col_c int NULL does not work. Can tables that are variables b...more >>

can the 'deleted' and 'inserted' psuedotables be used anywhere inside the body of a trigger? so could i do something like .... CREATE TRIGGER dbo.TriggerChangeHistoryLicense ON ChangeHistoryLicense FOR UPDATE AS DECLARE @ChangeID [int] IF UPDATE(LicenseeName) BEGIN SELECT @Chan...more >>

Is it possible to make the ORDER BY clause of a query insensitive to case? Or must I create a column with UPPER(<field>) and order by that? I looked in BOL, couldn't find this. ...more >>

hi I am looking for assistance on how to create a batch for the follow text below. I am kinda of a newbie on this and stuck. Any help would be :) Michelle. Look at directories only, arranged alphabetically by name in the root directory. (Call this Root Directory Only.) Look at director...more >>

When I execute this stored proc I get an error on line 6, but it has clearly already executed up to line 7 because it returns the values 1,1 from the table. Can any explain what is going on? Thanks Michael CREATE PROCEDURE [dbo].[_Test] AS CREATE TABLE #ABC (ID INT, Test INT) INSER...more >>

Hi I have a SQL script that I have created and it is initialised by a batch file and run through OSQL. The data is dumped to a designated text file. What I would like is to have OSQL put breaks in whenever a value in a certain field changes. This script reports all overdue accounts so I would ...more >>

What are my options for table/db designs to model an organization chart. Parent/child/siblings root/leaves ideas? How can depth or "layers deep" be handled? I haven't had to think about this for awhile and all of the sudden I have people asking me questions about using queues to simplify hiea...more >>

I have a table with column ColDateTime of type Datetime. For example: 12/1/2004 4:22:02 PM How can I do a sql stt to get all the records from 20 minutes ago ? The following sql stt returns all records: select * from tblA where ColDateTime >= DATEADD(minute,-20,CONVERT( CHAR(10), getdate(), 10...more >>

Hi, Anyone know if there's a way to search all stored procedures for a particular string? That is, I have a database with a billion stored procedures, and I want to find out which ones affect a certain table in the database. So I was hoping there'd be a way to search any or all of the stored p...more >>

I need to send a reading list to customers ordered by Quiz data that the customer has given us. The 25 quiz questions are in a table called tblEmailList (sample below). The reading list is in tblReadingGrid (sample below). I need a query to pull the reading grid and order it by severity of...more >>

Hi all, Is there a field type in sql server to store large strings similar to the Access Memo field? I want to store XML strings between 10,000-30,000 characters. Thanks. -- kpg...more >>

I noticed that there were several indexes and statistics in my system beginning with the name hind_. I discovered that these are temporary "hypothetical indexes" created by the index tuning wizard, that are automatically removed by the wizard after it is complete. I found an KB article from Mi...more >>

i have datetime column like '21.12.2004 13:59' how to change to '21.12.2004 00:59' ...more >>

Hello all. We've been using SQL Server 2000 for some time and have always been impressed with the optimizer's ability to optimize away unneeded joins and other work. There is a fairly simple case, though, where the optimizer fails to do this. Here's an example: DROP TABLE Test GO CREATE T...more >>

Which SQL commend(s) I should run for a table to find out who is the table creator? Thanks in advance, -Mike...more >>

Anybody know how to bind a CheckBoxList in such a way that some of the boxes are checked by default an some not? In other words, bind it to a boolean field that indicates whether the box is checked?...more >>

Hi, this is the first time I try to create an insert/update trigger on a table. I cannot seem to find the equivalent of oracle's "before" triggers, however. According to the documentation there are three options for a trigger: for / after / instead of. The documentation also says: "AFTER is t...more >>

Hello, I hope I am posting in correct place. I am having difficulty with creating tables in VB with MS SQL Server 2000. When a table is created, only the person who created it can see it via the application. I am certain this is something to do with access privillages, which if the c...more >>

I undestand the reasoning behind using Stored Procedures for DELETE, UPDATE, and INSERT operations, and even SELECT operations on a single table where you're only returning a single row. However, I'm less clear on what the "right" way to handle SELECT queries in general (for example ones involvi...more >>

I would like to set up a scheduled backup job so that it will log failures into the Windows 2000 event log. According to BOL, this should merely require setting @notify_level_eventlog = 2 (the default) in sp_add_job. I did that, but I'm not seeing the errors show up in the log. They are howev...more >>

Hello I have the following query on MSSQL 2000. declare @t table(i int identity, j int, k int) declare @orderBy varchar(128) set @orderBy = 'f1' insert @t select company_id f1, parent_id f2 from t_company --order by f1 order by case when @orderBy = 'f1' then company_id else parent...more >>

We have a table that every week we need to purge. The table has an identity column that starts with 1. When we purge the table, we delete all the data in the table, and we would like to reset the identity column value back to 1. Can I do that and how ? Thanks. ...more >>

Hello, I have reviewed similar threads but have not found an exact match. I want to SET a nvarchar column to equal only its first 12 characters from the left. I tried using a substring guess but that did not work. The column name is DateRcvd. Thanks, Pancho...more >>

Hi, How am I able to retrive tables' information while i am in another database e.g. assume i am in PUBS database and I exceute the following SQL command select * from Northwind.dbo.[Information_Schema.Tables] it generates an error... Am i missing something??? I know I can use sysobjects...more >>

I have a table with columns like this (It has scores that users have gotten on quizzes): Name Date Score John 1/1/2004 75 John 1/3/2004 85 Bob 1/1/2004 65 Bob 1/3/2004 95 I want to do a query which will return one record per user. And it should show the score of th...more >>

Hi, When I execute this query: SELECT su.fullname as UserName, sue.email as UserEmailAddress, sg.name as UserGroup FROM S_User su LEFT JOIN S_useremail sue ON su.id = sue.id_user LEFT JOIN S_userGroup sug ON su.id = sug.id_sysUser LEFT JOIN S_group sg ON sug.id_group = sg.id GROUP by su....more >>

I am a little stuck, can someone help me out? I have the following query: INSERT INTO Interface_File (ClientNumber, Location, DelStartDate,ProductCode,LaborCharge) SELECT (ClientNumber, Location, DelStartDate, 'TRIP CHG',LaborCharge) FROM Interface_File WHERE RegionCode = 'CSW' AND Lab...more >>

Hi all, I have the following select statement: Delcare @Paid int SELECT @Paid = Paid_Flag FROM InvoiceTable WHERE Customer = 'Cust1' AND InvoiceNum = 'Inv1'. This works as it should returning 1. If I use local variables to supply Customer and InvoiceNum as follows: Declare @Paid int...more >>

How can I query a DB and find out which tables do no contain any rows of data? TIA...more >>

Hi, I tried the following statement select datediff(month, '11/1/04', '12/31/04') and get the result: 1 I am expecting: 2 TIA...more >>

Hi, I was wondering if it was possible to pass an array of values into a stored proecdure parameter? e.g. I have a list of ID's in a list box, the user can select as many ID's as the like from the listbox so that it will bring back a recordset based on their selections. Each time they make a...more >>

Hello, I would like to check syntax of all trigers in my database. It is possible to do that from one point, or should I check all triggers manually, step by step. Regards, Robert ...more >>

hi, I have column in one table with datetime type and data stored like '21.08.1984 13:58' how to get only '21.08.1984' from sql as date not like char. thx ...more >>

I have a table called CALLS with 10 column. I am interested extracting 4 columns -- Date, CollectionTime, TotalCalls, TotalAttempts The Call tables will have a one month worth of data. Each date will have a record for 24 for each hour of the day. What I need is to find the highest 4 consecut...more >>

I was reading Celko and he says table names should be plural. I think this is completely and totally redundant. By definition a TABLE is designed to hold multiple rows so to add an 'S' to the end of a table name is redundant. I would argue that since the case for a Table holding a single row...more >>

I need help with another query statment to return the count for the following columns: SELECT count(id) as NumberofMails -- how many rows total count(inbound) as InboundMsg -- how many rows where inbound = 1 and outbound = 0 count(outbound) as OutboundMsg -- h...more >>

Hello, I currently has a user table in one of my database, which stores all the userids and passwords. We could like to know what users has access to the database lately...I wonder if there is a trigger that allow me to so something similar to insert, update or delete. But for my case it wi...more >>

I have maybe a very stupid question. I need to provide the rownnr as a column in a select query. For instance: select ROWNRXXX, THEFIELD from THETABLE should provide result ROWNNR THEFIELD--- ------1 23452 84753 53474 3464How do I calculate ROWNNRXXX in this query - Is there some kind o...more >>

I need to allocate $100 to a persons account. A person can have multiple accounts, with different eligability per account. A total ot $100 can only be allocated per account eg: Name Account Eligable Free John 1111 $40 $40 John 1111 $50 $50 John 1111 $40 ...more >>

Hi, By default, STDEV function only works on a single column. Is there a way to get STDEV on a whole table (multiple columns, multiple records)? Thanks. Qingsong GE Healthcare...more >>

I'm finding that CASE WHEN statements are very slow when used in a group by statement. In the query I am writing I'm summing on a CASE WHEN. If a certain field contains a value I use that value but if that field is null I have to use another field, something like this: SELECT SUM(CASE WHEN ...more >>

please yes, i know not 1st normal form before update... table: points point1 point2 point3 point4 point5 a b c d e a b c d e a b c d null a b null null null b c ...more >>

Hello, how can I script a given check constraint for a table? I graphically added a constraint like 'Year < 2020 and Year > 1990', but when i try the All Tasks->Generate Scrips Dialog, I cannot find an option which gives me the constraint in the form of a T-SQL-command or clause. Thank You ...more >>

Hi, I am trying to do the following: SET @data = CAST('1/1/EYEAR' AS DATETIME) SET @data = REPLACE(@data, 'EYEAR', @e_year) SELECT @data Ex: SELECT CAST('1/1/2003' AS DATETIME) So far everything is good. I would like to change the EYEAR to EYEAR+1 as below: SET @data = CAST('1/1/EYEAR...more >>

I have a database that has several ToOne tables that are used to hold BLOB/Image data for the referencing table. So for example I have an Employee table that has a ToOne EmployeeImage table that stores only the image and the foriegn key. The reson these tables were originally created was t...more >>

I have 3 tables: In ATTRIBUTE table the data is : attribute_id(PK) atttribute_name ----------------------------------- 1 Location 2 Department In CODE_LOCATION table the data is : loc_id(PK) loc_name ------------------------- 101...more >>

With Hugo Kornelis’s help I managed to have a solution working ok. However, our management in their wisdom have now changed our vacation period from 01 Jan – 31 Dec to 01 Jul – 30 Jun for next year. This means the normal allowance of 28 days is valid for 01 Jan 2004 – 31 Dec 2004. The ...more >>

I have an image field that I would like to view. Is there a quick, easy way to view this data? Thanks in advance. ...more >>

I have three tables, Contacts, ContactsRequests and Requests Contacts: ContactID,Firstname,lastname, Address, City, State, Zip ContactsRequests: ContactId, RequestId A Comma delimited string with RequestIds is passed say (1,2,3) to a stored procedure. I have to get all the Contacts which ...more >>

Hi, We are taking information in a simple sales prospect entry form on our web site via asp.net, we want to capture accurate data so we have included the following expression validator: <asp:regularexpressionvalidator id="Regularexpressionvalidator1" ValidationExpression="((\(\d{3}\)?)|(...more >>

Is there a way to move a database diagram from one database to another? I can copy it, but there doesn't seem to be a way to paste it. I just recreated the database I was using and don't want to do the whole thing by hand again. Thanks, Tom. ...more >>

Hi, This might sound like a basic question to some. I have a database with a view. This view creates the following table: UniversityClass(StudentID, StudentName, StudentSurname) I have been asked to create an 'attendance sheet' out of this view. The attendance sheet needs to be in the ...more >>

I am trying to write a query that will pull records with a particular date between Noon today and Noon Tomorrow. Can anyone please help with some ideas. Thanks...more >>

I'm trying to combine data from two different tables on two different servers that are linked together. The first table on server 1: CREATE TABLE [2004Adjustments] ( [AdjustAmount] [money] NULL , [BillNPA] [nvarchar] (3) NULL , [BillNXX] [nvarchar] (3) NULL , [BillLine] [nvarchar] (4)...more >>

Hello, We are trying to decide which of these two querys is best: A) Charindex set @paramId = '|1|2|3|' select * from table whrere 1=1 AND CHARINDEX('|' + convert(varchar(3),id.Table) + '|',@paramId) > 0 B) Dynamic SQl set @paramId = '1,2,3' set @SQL = 'select * from table wh...more >>

I am trying to find out how easy it is to import a XML file/document in to a SQL SERVER DB? I tried reading the BOL, but can't make much sense of it. If anyone knows or can show me through an example , it would be great. Thanks...more >>

I have seen a mumber of posts on this subject, but am still unsure as how to implement. In my Windows Forms application, I am dynamically building a query based on criteria the user chooses. In the event that the query will return a larger number of rows (say 100 for discussion purposes), ...more >>

Hi I recieve at database snapshot which I restore and then pull across to a mart. Once restored there's no indexes on the tables, so in order for me to do my processes I create indexes in order to process my data faster. I've got 3 tables, A 4mil - customer PK id .. B 4mil - customer a...more >>

Greetings All, we are currently in the exploratory phase of building a financial app that has the requirement of globalization support. Although there are many questions to answer I have a specific question around the actual physical storage of monetary values in the DB. Specifically, I am con...more >>

Hi Does MSSQLServer have a maximum number of allowed open cursors? If yes, is that an option that can be changed? If no, what else can then set an upper limit? -- Klaus...more >>

I've got a few reports that are run using stored procedures called from a web page. The user can run a report and order the returned records by a single column OR by a combination of colums, either ascending or descending. So taking the Employees table in Northwind as an example, Report1 can b...more >>

Using EM, i am signed on as an administrator. i went into DataBase Maintenance Plans and created a DB backup for all the Datbases. when this was done i went into Jobs and tried to run the Database Backup Job, and got a SQLSTATE 42000 error 22029 on each step. im not sure as to ow to resolv...more >>

Hello, I have a stored procedure that returns image type data. (ie ... gifs and jpegs). I want to use the ISNULL(xxx, XXX) function to return something if the image type data returns NULL. If anyone can help that would be great. Here is my sproc. NOTE: lowRes is of datatype IMAGE (SQL ...more >>

Hi, end_date End_time Start_date Start_time GroupNbr line 20041029 11:31:57 20041029 11:08:50 3689020 2 20041029 11:46:02 20041029 11:32:30 3689020 2 20041029 12:14:23 20041029 11:48:00 3689020 2 20041029 15:22:11 20041029 12:30:50 3707132 2 20...more >>

Hi, Suppose that I have a table that each time a person adds money to his account, a record is inserted into table. TransactionId(PK),PersonID,Amount and Date are its fields. I need to report the maximum amount of money for each person that has added to his account but I also need its date. I w...more >>

Hi, I saw "Segment" operator in execution plan of my query but I couldn't find information in BOL. What is that? Thanks, Leila ...more >>