I need to do this, please help me :) I have one table with records like this Prefix Country 595 Paraguay 591 Bolivia 593 Ecuador 594 French Guiana I need to make a query that return the first row that match the first condition, for example Select * from...more >>

Hi all, I'm writing a bunch of triggers for an off-the-shelf product to enforce some business rules not enforced by the standard product. Not ideal I know but its what I have to do. The application does not allow more than 1 record to be inserted at a time into any table. However, someone h...more >>

can i define a dynamic SQL that use the data type table. eg. insert into @table + a dynamic sql ths...more >>

Hi guys, I need to join 2 tables with 2 criterias i.e.: The 1st key is the document number and after the 1st key matches there should be a 2nd check, i.e. the year should be checked. I got no idea how to bring that into SQL. Example: Table1: Row 10: DocNumber: 1 / Year: 2004 Row ...more >>

edit: this came out longer than I thought, any comments about anything here is greatly appreciated. thank you for reading My system stores millions of records, each with fields like firstname, lastname, email address, city, state, zip, along with any number of user defined fields. The appli...more >>

I am getting this error message: Server: Msg 8163, Level 16, State 4, Line 7 The text, ntext, or image data type cannot be selected as DISTINCT. when I run this SQL in QA. Can anyone tell my why I am getting this error even though I do not have a DISTINCT keyword in my statement? Is there...more >>

Hey group.. I hope I ask this thoroughly. We are trying to write a stored procedure that needs to return a distinct result set in a pre-determined order, yet the column determining the order is not returned. Let me clarify: For simplicity sake, there is a table that is getting populated in the f...more >>

I have a table TB_History with fields id, x, y, insert_date and would like to write a SQL to do the following 1. retrieve those records, all four fields required, which are not older than 5 minutes and; 2. Only 1 record for EACH id and should be the lastest (according to the insert_da...more >>

Hello I have numerous duplicates in my database I am trying to find out how many. The duplicate is identified by a contact field and address field are the same. I have a front end app that has this info stored in a table called Contact1. In contact1 Address1 and Contact are the same. How can I...more >>

Hi, I'm viewing the setting in a table and find an expression in check contraints. ([colorId] like '\d{3}|([A-Z0-3]){3}') May I know the meaning for this expression? Thx Charles ...more >>

Hi, I know that performing "long" transaction is a bad idea and should be avoided when possible in SqlServer. I have heard something to the effect that long transactions make SqlServer do extra work when it recovers should it crash in the middle or toward the end of the transaction. My quest...more >>

hi. a little new to this so thanks for any support. i have a sql db attached to an access db. i have a sql stored procedure that creates a crosstab. i thought i'd be able to just open the sp in access and view the results but i keep getting a time out error. i used to have access email the...more >>

Hi, Im logging web site hits to sqlserver 2000, using an autoincrement primary keyed field. (the first column below) This field should go up by one with every hit, but when I view the output it is jumping around a fair bit - sometimes 2 ,others 4 or 12, but most often just one. Here is so...more >>

Hi, Does anybody know where does SQL Server store the column description of tables? Thanks, Tina ...more >>

I have a table defined as create mytable (mydate datetime) I need to populate this table with 30 years worth of dates (roughly, 10,000 entries - one for everyday for the last 30 years). I was wondering if there's a snazzy way of doing this via one INSERT INTO mytable SELECT.... statem...more >>

Hi, I'm new to stored procedures and I'm trying to do something but unsure how to. I have 3 columns: custID INT custFName VARCHAR(30) custLName VARCHAR(30) I want to create a stored procedure that inserts a record with custID being an OUTPUT, not an INPUT. In other words, how...more >>

In my stored procedure I have to selects, the first one in an exec, and the second one is just a standard exec. The user only has exec access to the stored proc, no select access to tables. The second select * from CourseInstance works fine but the select in the exec errors out with a selectp...more >>

I two databases on the same server. I'm logged in with a user that has access to both databases. The user has no select/delete/update rights to any tables. On the first database the user has exec rights to a stored procedure. That stored procedure selects * from two tables with a join. 1 ta...more >>

Hi, I am submitting the following number to my SQL table to a field with a Decimal Data Type: 1.5873015873 But when I bring back the number via ASP or check it in Enterprise Manager it records: 2 These are the settings for the field: Decimal Description: blank Default Value: (0) ...more >>

hi I would like to transfer data from one database on a server to another server. But i want to specify the order of teh tables that are to be transfered. like table a before table b. what is the best way to do this? is there any sample for it? thnx ...more >>

Hi I've a sp like this create proc usp_tableX_modif @column1_key int @column2 nchar(10) @column3 nchar(10 a begi update table set column2 = @column2 column3 = @column where column1 = @column en The question is: How can I avoid maintenance problem wich arises with th...more >>

I have a table in my database that basically represents a tree structure, Table 'CostCodes', columns as follows: ID: bigint ParentID: bigint Import (bit) InheritImportValue (bit) I have a trigger which I was hoping would enable me to check to see if any children of the updated parent,...more >>

I have this stored procedure below but when I try to run it from a webpage it returns a error message of too many arguments... SELECT DISTINC eb.TopicName, eb.AuthorFirst eb.AuthorLast, oe.ecik Responses = Count(oe.id)/4, Average = AVG(CAST( oe.Answer_Num as decimal) INTO #TempAl FROM emed_E...more >>

I have a challenging query that has me stumped. What I need to do is given a set of records containing time ranges, I need to calculate the number of overlaps per quarter hour for a specified time range. Example: The following would be the time ranges used to calculate overlaps time_start ti...more >>

Here is my Stored procedure. If I uncomment the first commented line it works like a charm. If I uncoment the second commented line I get SELECT permission denied on object 'MyTable', database 'myDB', owner 'dbo' Why? And is there a way around this. I have a stroed procedure that uses complic...more >>

Hi, There must be some trick to this or I have expectations that can't be met. Here are my syntax: SELECT territory, dist_name, SUM(CASE WHEN Date1 <= Date2 THEN 1 ELSE 0 END) AS od, COUNT(PROJECT) AS cnt, SUM(CASE WHEN Date1 <= Date2 THEN 1 ELSE 0 END) / COUNT(DIST...more >>

IM trying to write a stored procedure that takes an database and application name as input. It then looks in a sql database for the corresponding password and then sets the application role. I am having problem using a variable for the password in the following satement. Execute( 'sp_setapprole '...more >>

Hi friends i need help ! I apply the following statement FIRST: >>RESTORE DATABASE MYDB >>FROM DISK = 'E:\mydb.bkp' >>WITH REPLACE, >>MOVE 'MYDBD01' TO 'E:\MYDBD01.DAT', >>MOVE 'MYDBL01' TO 'E:\MYDBL01.LOG', >>NORECOVERY THEN: >>RESTORE LOG MYDB >>FROM DISK = 'E:\MYDB_T16.BKP' >>WI...more >>

Hi All, I need query that will determine which tables are created on primary file group or secondary file group? I know that i can exectute sp_help 'tablename' to see on which file group the only one table is. But that is very difficult to do for all tables. So any single query to find th...more >>

I have the following view SELECT dbo.Patient.*, dbo.PatientAddress.AddressID AS CAddressID, dbo.PatientAddress.PatientID AS CPatientID, dbo.Address.AddressID AS Expr1, dbo.Address.Address1 AS Expr FROM dbo.Patient INNER JOIN dbo.PatientAddress ON dbo.Patient.P...more >>

I have SQL script listed below that I use to reindex my database tables. Please help me modify my SQL script if the table index is less than 80% unorganized then reindex. Thanks, Mike DECLARE @TABLE_NAME sysname DECLARE @SAVE_STRING_TABLE VARCHAR(255) DECLARE columnsele...more >>

I really need a solution about this... We are about to launch a big database transfert that will last about 6 day. So we can't stay here for 6 day looking if there is any errors during the transfert. So is there any ways with the SQL profiler to lauch a message (by e-mail or by cell phone...more >>

I have a tricky problem. I am getting a 1934 error when running a stored procedure. The actual error message is: INSERT failed because the following SET options have incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER'. However, changing these options on the stored procedure allows t...more >>

Hi All, Is it possible to make a constraint on a bit field where only one bit is allowed to be true? I have a table with contact numbers and one field is called "IsPrimary", this is a bit field. I want to use this field allow only one primary contact number. I know how to do this from code ...more >>

I know someone posted this in the ADO.NET group a few months ago but I sure can't find it. Does anyone know what this is? It's the proc that QA runs whenever hit the check button. Thanks, Bill -- W.G. Ryan, eMVP http://forums.devbuzz.com/ http://www.knowdotnet.com/williamryan.htm...more >>

hello i am receiving the following error at weird times Could not find database ID 102. Database may not be activated yet or may be in transitio the latest incident was doing an update query of the type update tabl set column = x.column from table join select ...more >>

Is there a command that I can run in T-SQL code that will tell me whether a given trigger is enabled or disabled? Thanks! -- Joe Fallon ...more >>

Here is my problem: create table #itemprice ( ID int IDENTITY (1, 1) NOT NULL, ItemID int NOT NULL, PriceDate smalldatetime NOT NULL, Price money NOT NULL ) SET NOCOUNT ON INSERT INTO #itemprice VALUES (1, '2004-04-29', 10.0) INSERT INTO #itemprice VALUES (1, '2004-05-01', 11.1) IN...more >>

I found a problem in a table I'm reading and displaying on a web page. The problem is that the data, for whatever reason has 2 spaces in it. When I do the display on the web page, only 1 space renders in the combo box. If I run the sql from query analyzer, it shows 2 spaces. The result: "AB...more >>

Dear all I need a stored procedure, function or something like that which take a table and then, encrypt all the fields into another one. I mean, one script to be able to encrypt and another for to deencrypt Thanks in advance and warm regards ...more >>

I have a situation where a query is doing a entire scan of a clustered index instead of using a seek against non-clustered index (which would be faster). Here is a hypothetical example of the structure of my data. Assume that I have the following table with three fields and the accompanying ...more >>

is there anything I can do to get it back?...more >>

Hello, Back end - SQL Server 2000 Front end - Access 2000 I have a basic table with about 20 columns. I retrieve data via a stored procedure. If I run the stored procedure (the select statement) in Query Analyzer, the data returned is exactly what I requested. In Access...more >>

tengo varias tablas que tiene fechas en formato numerico , necesito crear una etl que me cambie esas fechas automaticamente a datetime, y que quede la tabla modificada......more >>

Hi All I was wondering if any one could tell me if deferred constraint checking (ie not checking referential integrity until a transaction is committed) is available in SQL Server 2000. If so, how it is implemented Thanks mal...more >>

Hi, I need to implement job consisting of many t-sql steps which have more than X characters (what is restriction?), so I have to implement osql in these steps. However, each of these steps will succeed regardless of if they had some error or not. How can I control if osql step really com...more >>

Hi I have saved two RTF (RichTextFormat) Data into the Database in two different tables Now i need to merge those two RTFs into one RTF. How can i do this in SQL Server Can anyone help me Regards Ramnadh....more >>

I am trying to run some Cross database queries defined in a stored procedure from the ISQL command ine like this EXEC master..xp_cmdshell 'isql.exe -S MySQLServer -E -d MyDB -s , -w 200 -Q "EXEC uspMyStoredProcedure" -o "C:\Output.csv" The output file contains this error message "Heterogeneous ...more >>

I use two dynamic SQL to contruct a SQL in a stored procedure as below. 1st: SQL for inserting record into temp table 2nd: Extract record from the temp table in 1 When I execute the stored procedure in query analzer, it returns data. However, when I execute in crystal report. It prompte...more >>

Good Morning Newsgroup PLEASE NOTE, IF YOU HAVE ALREADY REPLIED TO MY ORIGINAL POST (Dropping Indexes) THEN PLEASE DO NOT RESPOND TO THIS NEW POSTING. 'DROP INDEX' STATEMENTS WILL NOT WORK WITH UNIQUE INDEXES!! I am very new to T-SQL Programming, so I need a little bit of help. I am w...more >>

I'm checking some legacy SP code and notice that an OUTPUT parameter is declared prior to other INPUT parameters, from what I can tell this OUTPUT parameter is only used as an INPUT parameter Is it just good practice to declare any OUTPUT parameters after INPUT parameters in a SP or is it illegal ...more >>

Hi all i am having a very strange issue when trying to create a very simple dts package. I have a table (see sql below) and i have created a new sql dts package. In my package i have a sql connection to the db (this is fine) and a text file (source)(this is also fine). I then define a data pump ta...more >>

Hi I got a timeout error in running a complex SQL, part of the SQL are as below (simplified) select * fro (select * from tableA where condA) table LEFT JOI (select * from tableB where condB) table ON someConditio There are about 600,000 records in tableA, with condA, it will return about 3...more >>

I use MS Project VBA code to get data from my SQL Server database. The query below works fine sQuery = "execute stored_procedure_name 'TEST V7.1', 'XX', '012'" but if I use variables instead I get an error if the parameter contains a "." or space. Proj="TEST V7.1" Proj2="XX" sQuery = ...more >>

Hi, If I have a table like this: UserId ValueDate Amt 1 10/06/04 10 1 12/06/04 15 1 13/06/04 -13 2 11/06/04 10 2 12/06/04 14 2 20/06/04 -2 2 30/06/04 10 I'd like to sum the amount and group on V...more >>

Hello Is there any System function/procedure to check the existence of a constraint(indexes, PK,FK,UQ,etc) in the database Thanks in advance Sheetal...more >>

Good Morning Newsgroup I am very new to T-SQL Programming, so I need a little bit of help. I am writing T-SQL Programmes using SQL Server 7.0 to drop then re-create indexes in a bunch of databases. I am OK with dropping nonclustered indexes, I am also OK with creating the Indexes, howe...more >>

Hello all, after select a user in the database (using Enterprise Manager for SQL-Server 7.0), and click Permissions, all objects are listed. All tables (only) have an option DRI to be selected. What is this column / property for ??? best regards, Evandro ...more >>