When I choose the tables, generatel SQL script. it insist me to generate the "create table" script also. Any Simple method which i can script trigger rule (for all tables) only ? Thx ...more >>

I have a varchar(5) column called SeatNo which I would like to sort numerically followed by alphabetically. Is this possible? E.g. At the moment "SELECT * FROM TableName ORDER BY Seat" sorts by... Seat === 1 10 17 2 23 3 A B H I would like... Seat === 1 2 3 10 17 2...more >>

I cannot make the result set ORDER BY LastName and FirstName: CREATE VIEW dbo.viewApp_Web_ContactDetails AS SELECT TOP 100 PERCENT dbo.tblApp_Contact.Salutation, dbo.tblApp_Contact.FirstName, dbo.tblApp_Contact.LastName FROM dbo.tblApp...more >>

Is it possible to create a unique constraint to a column from another table? For example: tb_current: current_names -------------- aaa bbb tb_new: new_name -------- ccc Now I want to create a constraint on tb_new.new_name to be unique with respect to tb_current.current_names. Howev...more >>

I would like to look at both records returned as duplicates. I am using the following script to find the duplicates but can it only returns one record not both. I would like to see the entire record so that I can make a determination on more than just the criteria that I selected as the basis of ...more >>

Hi, I need to know how to retrieve the login name of the user into a local variable so that I can insert it into a table. I want to do something like this: DECLARE @USERNAME varchar(50) DECLARE @OTHERFIELD varchar(50) SET @USERNAME = EXEC sp_who ???or something like this??? INSERT I...more >>

Hello, SQL 2000 - please try this in the QueryAnalizer: declare @d smalldatetime, @s varchar(200) set @d = '20051201 11:59:00' set @s = @d select @d, @s set @d = '20051201 12:00:00' set @s = @d select @d, @s .... and then tell me why the first select shows 2005-12-01 11:5...more >>

This works: DECLARE @STR VARCHAR(31) SET @STR = '1052.40' SELECT CAST(@STR AS NUMERIC(28,2)) But when i use a thousand seperator, it does not DECLARE @STR VARCHAR(31) SET @STR = '1,052.40' SELECT CAST(@STR AS NUMERIC(28,2)) in the regional settings, '.' is thousand and ',' is decimal ...more >>

Hello, This statement fails in my app: BULK INSERT [CASOPSA_TB042_INSTELLING_WERKGEVER] FROM 'D:\Temp\Xtb042_instelling_werkgever.TXT' WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'char', TABLOCK, FIELDTERMINATOR = '\t', ROWTERMINATOR = '\0', MAXERRORS = 0) with...more >>

Hi, I need some information regarding obtaining the user associated with a pid/process in the Sql Sever. I am trying to create a trigger on Delete on one of the tables in a database.. Is it possible to get the user associated with the process(pid) who is deleting rows from a table in that tri...more >>

I have a date column that contains date. How do I get week numbers within each month. I need the week to start on Monday to Sunday? ...more >>

I do a transactional INSERT on a table (using VB COM+ as middle tier calling SPs) and then immediately do a SELECT on that same table (using VB COM+ to call the SP and return data for display) to display info from the table. On my development machine everything works fine, but I've noticed on the...more >>

Hello, We ran into a problem recently when trying to execute stored procedures similar to the following ones : (SQL server 7 SP4) --code start USE pubs Go CREATE PROCEDURE dbo.a_caller As CREATE TABLE #auth (line_sort integer identity(1,1)) EXEC dbo.a_called_1 Select * From #auth...more >>

Hi, a little bit of background info; the T-SQL I'm writing is against a proprietary database schema, I have triggers which are being called upon through events in a client application over which I have no control and the schema can change because of patches/new functionality The pseudo code...more >>

HOWTO Build single query to query a remote SQL Server\ Please help, I need to build a single query that in turn queries a remote SQL Server, and return the data I need. Here is what I have so far: ============================= set NOCOUNT on exec sp_executesql N'exec sp_addlinkeds...more >>

I have the drop table #temp at the end of my stored prodedure but I think it got executed half way and left the #temp there. I try executing the drop table #temp but it says there is not #temp table. But if I run the alter procedure again then I get the err message that the #temp table alre...more >>

I've encountered an error while performing a database backup. I can't seem to find this error anywhere: BACKUP failed to complete the command master.dbo.sp_sqlexec;1 Does anyone have any insight into this error? Thank You!...more >>

Hi, I’ve the following SELECT with a WHERE clause which throws a result with no NULLs for B.idPersonal. How do I get all A.idPersonal=B.idPersonal though B.idPersonal=Null? SELECT C.Descripcion as TipoCF, B.Nombre, B.Apellido, E.Descripcion as Provincia, D.Partido, D.Localidad, D.Direccion, A....more >>

Case looking for a null in a column fails.... Although I look only for that column when it's named such it fails. I have two @field values that map to the TAC column. (column names TACC & TACD mapped to TAC) My other colum names are Co25,AmtP25,Hist25,Act25,Co50,AmtP50,Hist50... etc.... ...more >>

Hi, I have to upload Image(.jpg) file into SQL table. My Images are stored on shared drive. like \\xyz\sample.jpg. how can i upload this impages from shared drive into table ? Thanks -Kris...more >>

Hi, I am trying to export data into EXCEL sheet using DTS, It is working fine for single user, however it fails with error messages when tried by multiple users at the same time....So is it limitation of DTS as it does not run simultaneously ? ...more >>

I have the table below and need to count number of LeadHeaderId receive with Each hour. Any help would greatly appreciate. The business rules and results desire are show below. Thank you. if exists (select * from dbo.sysobjects where id = object_id(N'[LeadHeader]') and OBJECTPROPERTY(id,...more >>

is it possible to declare cursor for dynamic sql ? thanks ...more >>

For a SELECT on a given table, how to lock only the lines in the result set, and not all the lines used in the query (I use SET ROWCOUNT to limit the number of results) ? I tryed ROWLOCK but it locks all the lines matching the query, even the one that are discarded by rowcount. Any worka...more >>

Hi, I've got a stored proc called concurrently with different parameters. In this proc, I would like to update a row of a statistic table but ONLY if the update statement will not be blocked by a lock. Is there any way to achieve this in SQL2000 ? ...more >>

hi, can anyone tell me why the top one returns results and the bottom one doesn't? thx a bunch SELECT productName, unitPrice FROM tblProducts GROUP BY productName, unitPrice HAVING unitPrice > AVG(unitPrice) SELECT productName, unitPrice FROM tblProducts WHERE unitPrice > (SELE...more >>

Hi, I'm currently building a client/server calendar system with access as the primary front end and sql server 2000 at the back end. I also have a web-front end that hasn't been finished yet. I've come up against an internationalisation issue with time's. Users are already using th...more >>

Hi, I had registered an SQL server (same network) in the past through EM and everything worked fine until yesterday. If I try to register that server today, I get an error message: SQL server does not exist or access denied ConnectionOpen(Connect()) Can anybody give me some clues on how I can ...more >>

I have to following Access2003 Code that needs to run in SQL 2000 SELECT extend, Source, Count(PersonalID) AS RecordCount, Count(IIf([Literature1Quantity]>0,([Literature1Quantity]),Null)) AS Item01, Count(IIf([Literature2Quantity]>0,([Literature2Quantity]),Null)) AS Item02, Count(...more >>

Hello, I need to include scripts in my setup program that will create a database, table etc and populate it with some necessary system data and sample for the user. The first part is simple enough, but does anyone know of a way of including data values in Enterprise Manager's script gene...more >>

Hello, I need to detect whether the computer has SQL Server 2000 SP3 installed before attempting to connect to it, for example during my custom setup program. What is the recommended way of doing this? TIA, A. ...more >>

My company recently upgraded from SQL 7 to SQL 2000. We also updated our application servers from Windows NT to Windows 2003. Now when we get the error [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID X) was deadlocked, the part that comes next "on {unprintable" shows up ...more >>

If I understand your question... In the Enterprise manager, goto the formatting tab and uncheck Generate Create and Drop for the Objects. Then on the Options tab check Triggers. That should gen just the Trigger scripts. Matt Barbour Technology Specialist – Communications Sector. ...more >>

Since I'm fairly new to SQL - I need help making the following into a case statement: -- Set Reporting date if the policy is a changed policy UPDATE STAGE_PHX_FACT_POLICY SET REPORTING_DATE = CHG_EFF_DATE FROM STAGE_PHX_FACT_POLICY WHERE CHG_EFF_DATE IS NOT NULL -- Set Reportin...more >>

I've used BCP before, so I understand how to use it for the most part. But, I'm trying to do something different. I created a fmt file from my table. But, what I want is to only import say column 50. So, this is how I changed it. 8.0 50 50 SQLCHAR 0 510 "\t" 41 COUNTY ...more >>

Im having trouble witht he following query SELECT a.BHYear_OrigSortCode, RIGHT('00000000' + a.BHYear_OrigAccount,8) as 'value' , Case c.BTYear_TransCode WHEN '99' then '17' WHEN '01' Then '99' WHEN '04' THEN '99' WHEN '17' THEN '99' WHEN '18' THEN '99' WHEN '19' THE...more >>

I need to add a check contraint to a table in SQL Server 2000 that would do the following if the value of [type] = 1 then [year] cannot be null is this possible to do? because for the other values of [type] i want the [year] to be null thanks for any help! ben...more >>

Hi, I'm appending selections from 2 tables together and putting the results in a 3rd table. Some of the columns in the tables can be NULL or ' '. I would like to convert anything that is not 0-9,a-z,A-Z in all positions into something, say "~". I'm new to SQL Server. I've tried a Case...more >>

Hi I'm trying to call a stored proc from within another of my stored procs, passing the results of a select statement as the parameters - but it's not working. Here's what I'm trying to do: CREATE PROCEDURE [MoveRecordsToArchive] AS EXEC [ARCHIVEInsert] ( SELECT * FROM [CU...more >>

hi i have customized dataset. i want to insert into database. how can i insert on a single round trip. how can i pass dataset to stored procedure?? ...more >>

I had created several Trigger rules in current database and it will be active on 1st Nov,2005, Can I disable them now ? and then make them active on that target date ?? Thanks a lot ...more >>