Hi There, Is there a way in which Sqlserver can notify the dotnet application when a new column is added . i believe we can not use Query notifiaction here(correct me if i am wrong) .i am using sqlserver 2005 and dotnet 2.0 . if anybody have an idea of how to do this or know any link in th...more >>

Hi, I have a table whose PK col. is varchar but whose values are numerical. How can I order this 1,2,3,...8,9,10,11 ? As varchar it orders 1,10,2,3,;;;8,9, etc. (As though it were a character) Many thanks for any ideas on this Ant...more >>

I would like to determine if a table has had any changes to any row since the last time I checked. The table undergoes a heavy series of updates at irregular times (a result of a seperate batch system), and I would like to quickly poll the table every n minutes to see if things have happened t...more >>

I have a union query that's driving me nuts. I run each query separately, and the first query returns one record with a client ID =1. The second query returns client ID 1 and 2. When I put these into a UNION query, the result is returning client ID 1, 2 and 3. Any ideas? The SQL is pa...more >>

Hi, I am trying to write a query that will add sequentially. For example, Month Week Amount Amount2 1 1 200 200 1 2 150 350 1 3 400 750 How do I get the Amount2 colmn which is really a running total to work in sql. Thanks ...more >>

Hi, I've been working through this and thought I might ask the group if there is a better way to do it without cursors. Problem: A class has students who took an instance of a test StudentID, DateTaken, TestID, Score The teacher only wants to see Students who have retaken TestIDs in ...more >>

I need to do a select on a table then update a column in that table after I grab the records I need. Is this possible and how would it be done? so something like this: I would do: select make, model, year, vin, new, used, sold, uploaded from carTable the column uploaded is 0 or 1,, this ...more >>

Hi, Is there any way to check if any objects owned by a specific schema? I tried to run select schema_name(schema_id), * from sys.object and I did not see any object owned by the schema that i want to drop. But when I drop the schema, it keeps telling me it can't be dropped because some o...more >>

I have a query that builds a temptable that contains two fields, both strings: a code, which contains a comma delimited string of integers, and an email address. '1, 10, 11, 12, 13, 15, 18, 22, 23, 25' 'fred@flintstone.com' '2, 7, 9, 12, 13, 18, 19' 'barney@rubble.com' '1, 10, 11, 12, 25' 'mr...more >>

Table #Exchanges-A list of exchanges containing an exchange code and a country code Table #ExchangeListMember-Defines members of an exchange list I need an UPDATE statement with the following logic. INSERT into ExchangeListMember so that if a Exchange_List_CD has at least one exchange in a pa...more >>

What is the fastest way to find the year-month-day values between two arbitrary dates? I have played with DateAdd and DateDiff until I'm dizzy and have nothing to show for it. :( With 2005.01.01 and 2005.04.01 as parameter values, I am trying to get 2005.01.01 2005.02.01 2005.03.01 2005.0...more >>

Hi all, What would be the best way to keep anyone from accessing a database while I'm bulk inserting data into tables using a DTS package. Should I put the database in single user mode or a specific lock? thanks gv ...more >>

1. What is the correct syntax for a select statment using linked server? The following sql returns error message select * from MyLinkedServer..MyDatabase.MyTable The OLE DB provider "MSDAORA" for linked server "MyLinkedServer" does not contain the table ""MyDatabase"."MyTable"" ...more >>

I have tried to use the following statement: Select Idx, Name, Sort, ID, Count(dbo.queue.clients where dbo.queue.clients like dbo.Clients.ID) as [Count] >From dbo.Clients Order By Sort I have two tables 'queue' and 'clients' that don't have a common field. I do know that the id field va...more >>

In the good ole days of SQL2000, I would have the need to copy a table from our live server to a dev server. I would simply use the Import/Export wizard, select the objects (tables), make sure I did the extended propeties, and the wizard would smoothly drop my existing tables and copy the dat...more >>

I have 2 tables Address Table AddressID int Person Table PersonID int AddressID int DateAdded datetime The person table is defined so that when a new occupant lives at the address, the previous occupants are retained. But the current occupant is defined as the one with the mos...more >>

can anyone help with this case condition. for some reason when it gets to the second case it's only validating the last or condition 'P COMP' when the previous or conditions are met it is not working. select xyz, case when p21_view_invoice_hdr.invoice_date < '04/01/2006' then (C...more >>

Hello, How can I change an integer value to a time format (without date). The integers in the basic table are 'seconds'. When the integer = <17> (seconds) the result should be <00:00:17> When the integer = <87> (seconds) the result should be <00:01:27> When the integer = <3674> (seconds) th...more >>

The first select runs fine. select * from dbo.f_myFunction ('12/31/06', '24','sv') This one errors out - syntax error select * from dbo.f_myFunction (DateAdd(yy,-2,'12/31/06'), '24','sv') Do I have a syntax error? or is it not possible to pass in DateAdd as part of my argument list? ...more >>

From this message board I copied the following code on running SQL Scripts from Access to update my SQL Server database. -------------------------------------------------- Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const TristateUseDefault = -2, TristateTrue = -1, TristateFal...more >>

select 2, 3 union all select 4, 5 union all declare @myint1 int declare @myint2 int select @myint1 = 1, @myint2 = 2 I am getting following error in above query.. Server: Msg 156, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'declare'. why I cannot use UNION ALL and...more >>

This is more of a "does anyone see something I'm missing" post versus a real problem. What I'm doing is modifying a script I found in BOL. The script iterates through all the tables in a database and performs a SHOWCONTIG on all the tables. For those tables at a certain level of fragmentati...more >>

I have some stored procedures that contains mulitple select statements used to populate datasets. In some cases, the first select contains summary info and the remaining ones detailed info. I would also like to be able to get just the summary info within other stored procedures using a exec ...more >>

Hello, I'm doing some work converting an Access Database to SQL and I've come into a roadblock. I can successfully import the table from Access to SQL, but when I try to put a key on 3 columns it always gives me the below error. 'Sales Data - Dom' table - Unable to modify table. ODBC ...more >>

Dear Team, I have the underlisted Current Data to convert to the Intended output using sql, please help. Current Data -501.00 -501.00 -502.00 -502.00 -503.00 -503.00 ...more >>

I am working on a table hierachy and needed a bit of input. I am looking to create a list of phrases which could include millions of phrases. I am also thinking about creating another table which could have millions of rows, each row with a unique group/user/category and phrase. Thus, to keep...more >>

i have a text column that i only want specific info from. i cannot use SUBSTRING because the data sometimes starts in different positions. is there a way to count delimiters ? or maybe a way to say always read from a pattern, like PATINDEX but return what is left and not just the position. th...more >>

Hi, I am trying to join two tables Clients and Benefits. A row in Table Clients can join to one or more rows in Table Benefits. Table Benefits has the Benefits (and prior history) listed for a client. The tables are joined on the field ClientID. I would like to limit it to join to just the...more >>

I would like to pass the linked server name as parameter as in the following query but it does not work; any suggestions? DECLARE @Linkedserver nvarchar(50) SET @Linkedserver = 'MyLinkedServer' SELECT * FROM OPENQUERY(@Linkedserver, 'SELECT COLUMN1 FROM TABLEa');...more >>

Any suggestion on how to update a nvarchar field (account number) to mask the full number.. Example: account number in the database is currently stored as 1234567-21-01.. I need update/mask the number to be stored as XXXX567-21-01? Any suggetions that would help a sql beginner would...more >>

Hi, I need to get data from one SQL 2k database and load in SQL 2005 database. We have decided to use BCP as as we envisage using other databases also for the load process. Currently we do a BCP out and then do a BCP in....because of this we need a landing area where bcp files are stored...more >>

Hi, I've created the following FORMAT file.... 9.0 19 1 SQLCHAR 0 11 "" 1 MPAccountNumber "" :::::::::::: 19 SQLCHAR 0 09 "" 19 RewardCriteriaAmount "" .... that I use with this BULK INSERT stmt: BULK INSERT [FFF].[dbo].tb_All_Sub FROM 'C:\Sample File.txt' WITH (formatfile = 'C:\f...more >>

I have a database MyDB with 10+ stored procedures containig a table reference Table1. Is there a way we can search and replace Table1 with Table2 across all stored procedures using a T-SQL in sql server 2000?...more >>

I receive a text file with no field terminators defined, i.e. values are identified by their position, say as in a file with three columns: id(3),FirstName(10)LastName(10), with a value of: '001Joe Smith ' (ignore the quotes, it is used as an illustration to show the beginning and ...more >>

Hi, any help in this t-sql problem would be greatly appreciated. I can't seem to work out why this stored procedure is not working. --------------------------------------------- ALTER PROCEDURE MyProcedure @Location varchar(100) = NULL, @MinPrice int = NULL, @MaxPrice int = NULL, @or...more >>

Hi, Completely stumped by this subquery, so if anyone can assist with a solution it would be appreciated. I have so far written this query: DECLARE @TheYear Varchar(4) SET @TheYear = '2007' SELECT Month(AssignDate) AS 'Month#', DATENAME(month, AssignDate) AS 'Month', Count(*) AS 'Count...more >>

Hi. I have a report with accounts in a list, and all activities belonging to each account in a table , at the buttom of the list I would like to show all accounts in my report, but only the top 10 activities for each account. I tried to solve this filter in sql but i have this running and i...more >>

I have the following values in a field named (sn_font). Opaque=1;Font=Times New Roman;FontSize=10;FontBold=0;FontItalic=0;FontUnderline=0;TextColor=0,0,0;BackColor=255,255,204;Printable=1 I need to make a select statement that will give a result such as "0". If I need to select only the...more >>

Hello I'm having a slight problem. I have a table where Semesters of a school is specified with a SemesterId, StartDate and EndDate. And when the student register an application (?) he specifies a date when he can move in (for registering an appartment). He can either move in during a semester...more >>

I backup database A on server A. Then the backup file will be restored database A on server B. Can I do it? How to compare 2 db? and how to make db A has strukturs same with db A quickly, without overwrite its data? because i often have the problems. I handle 2 server. Someday I develop DB on ...more >>

e.g. Column A ------------ John Peter Raymond Joe CONVERT TO John, Peter,Raymond,Joe with a comma between each record. I need to use CURSOR to loop through all records, needn't I? ...more >>

Hi, I am using SQL-2000. I want to copy one of my table to temporary table. How can I ? I found following code , but it is not working. Create Table ABC select * from recp Any solution will be sincerely appreciated. Hardik Shah ...more >>

Hi, I am using sql 2003 with vb.net 2003, I have created following table - 1) acMast - ac_id,name,balance 2) trans - ac_id,tdate,crdb,amount,opbal I have created a/c ledger (statement of a/c of the party like following) report using crystal report (2003) Ledger Sheet of XYZ from date :...more >>