I used SET CONCAT_NULL_YIELDS_NULL OFF do ignore NULL in concatation. I have two fields: chapterauthor authors Here's my query: SELECT ([chapterauthor] + [authors]) AS CT FROM Items ORDER BY CT Both Authors and ChapterAuthor can be null. I am trying to figure out if I can ignore the n...more >>

I used SET CONCAT_NULL_YIELDS_NULL OFF do ignore NULL in concatation. I have two fields: chapterauthor authors Here's my query: SELECT ([chapterauthor] + [authors]...more >>

Hi everyone, I have some trouble writing a SQL stored procedure that can do the following: We have data in one table in numeric form, but we want to sum the data in this table based on the values of two different alpha fields. To illustrate, let me write the following example: Table with t...more >>

Nothing fancy; just a trigger on a sharepoint table that supposed to write a record to another SQL table. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER TRIGGER [TV_UpdateFileSyncProgress] ON [dbo].[Docs] AFTER INSERT AS BEGIN SET NOCOUNT ON; BEGIN IF EXISTS (S...more >>

Can I create an index on a variation of a column that isn't actually in the table? I have a ParcelNumber column, with values like 123 AB-670 12345ABC 000-00-040 12-345-67 AP34567890 The blanks and non-alphanumeric characters cause problems with users, because sometimes they're there, ...more >>

Very new to SQL Server. Was asked to copy some tables from a Prod to a Test database in the same instance. (This system is not yet in production.) I used the Microsoft course book guidance for course 2072 A, the lab exercise in Module 9, page 33 to give me some guidance. In the exercise they ...more >>

Dear All, Plz help me in optimising the following query, Reduce repeatable reads from the table via select ,ythe table sare not having referntial integrity constarints ,relations CREATE proc Rolex136Sync as DECLARE @date varchar(50),@ydate varchar(50) print CONVERT(char(11),(GETDATE()-1),10...more >>

Consider this SQL: SELECT my_field FROM my_table WHERE my_field IN ('value2', 'value1', 'value3') Simple enough, but is there anyway to specify that the result should be ordered exactly like the "IN" clause states? So when this recordset comes back, I want it like this: my_field ------...more >>

I managed to get it installed OK, along side MS Visual Studio 2005 (with which I received it). During the install, I made sure I installed everything. I have developed a number of applications using MySQL v 5 and PostgreSQL, but I have not worked with MS SQL. Playing with it after installin...more >>

I have a field that may be null which is valid, and I am finding something I didnt expect when working with nulls. SELECT NULL/4.0 will return NULL (which I expect), however, when I test it with a case it does not: SELECT NULL/4.0 AS 'TEST1', TEST2 = CASE NULL/4.0 WHEN NULL THEN 'HURAY' ELSE ...more >>

I using MS SQLServer as a secure method of setting up system tasks and processes for automated running. The intent is that all logins (ids and passwords) are in a secure database table and are not sitting around in batch files on the server. Some of the tasks make use of network authenticatio...more >>

Hi everyone, I am trying to access a client's SQL query anaylzer to run a SQL script, and I can not find it anywhere. I have not used SQL in a few years, but I would always see a menu in the program list, or go to Enterprise Manager. I could not find any menu listings for SQL. I went to ...more >>

Hello, I need to select the minimum between the result of a function and a number, but i can't find a smart way. By now I'm doing like the following, but I think is very expensive because it evaluates the function twice: select case when (myfunction())<100 then (myfunction()) else 100 end ...more >>

Dear Group. Wondered if any of you has any suggestion for the following? Trying to install SQL Server 2005 Eval on a 'clean' machine. Well, my mistake was probably that I had installed Visual Studio 2005 Professional Eval before which installed an MSSQL Express instance. Since then I didn't...more >>

Query #1: select <list of fields> from C join B on C.b_key = B.b_key join A on B.a_key = A.a_key where A.o_key = <some value> Query #2: select <list of fields> from C where b_key in ( select b_key from B where a_key in ( select a_key from A where o_key = <s...more >>

I am trying to import a data file, which is tab delimited, using BULK INSERT. I have used BCP to create a format file, since the destination table has around 20 columns, but the data file has only three. Here's the problem: The columns I am trying to import comprise ID (an int identity colum...more >>

Hello, Any help is greatly appreciated. I've recently upgraded my MSDE 2000 to SQL Server 2005 Express Edition on my Windows 2003 server SP1. I've noticted that running a single one table query against the new sql engine causes the sqlservr.exe process to consume 98% of CPU on the serv...more >>

Is there anything I can do from the Enterprise Manager console or from within a JDBC connection to achieve the same effect as WITH(UPDLOCK)? Yes, I could change all of my SQL statements to include the lock... but isn't there any way to set or tweak something in SQL Server so that I won't ha...more >>

hi,everyone! I meet strange thing when I create a maintance job for backup all database. and this backup job just stands "Executing job step '(step 1)'" for a very long time . SQL server startup account is this account for network bakcup after I issue sp_databases sp, I found my ful...more >>

I hope this is the right place to ask this. Does anyone know places that offer SQL Server 2005 certification training and testing (MCTS, MCITP) in South Florida (Palm Beach County and surrounding area)? I've tried searching around on the web, and it's hard to find what I'm looking for mixed in...more >>

Hi, all. We have a couple of pathological sql servers that have lots and lots of page faults per second, up to 4000. Our client programs are written in C#/.NET 1.1 and utilizes connection pooling. Some of the client programs seems to log in hundred of times per second, as reported by perfmo...more >>

I have some users on Citrix, running an Access front end with ODBC attached tables. In sp_who. on a normal workstation they'd usually show once or maybe more but all with the same hostname so counting actual number of users is easy, group on loginame,hostname. When someone connects from a C...more >>

hi all i hava eetable that is , eename sal _______ ________ suresh 100000 ramsesh 100000 raja 100000 susjssj 100000 dkddkd 100000 jfdjfdjfd 100000 so i want to delete from second to Fourth ro...more >>

Hi Having a problem with a ms sql 2000 server. The script below was created i SQL manager 2005 lite and gives a syntax error near '(' ALTER TABLE [dbo].[Community_ActivityLog] ADD CONSTRAINT [PK_Community_Errors] PRIMARY KEY CLUSTERED ([activity_ID]) WITH ( PAD_INDEX = OFF, IGNORE_DUP_...more >>

Hey I want to get some data from some tables but I want every field except the primary key field returned. Is this possible? I.e SELECT * (except primary) FROM table1, table2, table3 Any ideas?? ...more >>

Hello, I have a stored procedure that deletes duplicates in one table: ..... AS BEGIN DELETE FROM mytable WHERE Id IN( SELECT Max(id) from mytable group by date, idsens having count(*) >1 ) END sometimes it happens that I have >2 rows with duplicated values. How can I ...more >>

I've got SQL Server 2005 Ent Ed 9.0.1399 and want to change my secondary Log Shipping server into a primary. I've tried locating the sp_change_secondary_role, but can't seem to find it. Has it been replaced with another stored procedure? If so, which are the procedures to run? On SQL Server 2...more >>

Hello, I am learning SQL Server 2005. I need to know how to make a backup of a database. I tried (according to my book): BACKUP DATABASE DemoSQLServer TO DISK = "G:\DemoSQLServer.bak" But I got error: Incorrect syntax near 'G:\DemoSQLServer.bak'. Please help. Thank you very much. /RAM/...more >>

This is a real challenge. I hope someone is smart enough to know how to do this. I have a table TABLE1 [Column 1- 2001] [Column 2- 2002] [Column 3- 2003] [Column 4 - 2004] [Column 5 - 2005] [Column 6 - 2006] [Column 7 - Slope] [2001][2002][2003][2004][2005][2006] [Slope] [1] ...more >>

Hi, I've the following problem. I must delete a column DEFAULT from a table, but I must do it with a script, independently from the server where it'll be executed. Locally I've tried with: ALTER TABLE [dbo].[PlanningDettaglio] DROP CONSTRAINT [DF__PlanningD__OreSt__688C6DAC] GO ALT...more >>

Hi, I have several independent, system components that write audit events to a database locally via ODBC and remotely via TCP, and I have a requirement to audit when the database is available for modification. First, does SQL Server have a concept of starting/stopping specific database inst...more >>

Hello, I want to share my experiences about using insert into exec which may help others . Using SQL Server 2000, SP3 . Two Proceduers - Parent SP caliing a Child SP (nested ) . No Explicit transactions . I have defined a # table in Parent SP and calling a Child SP like this insert ...more >>

Now i m working in a existing project. I have to do some updations. In that project database table contain a table(usergroups) field name as 'Name'. When i am trying to insert a new record in (usergoups) table like as follows' insert into UserGroups(Name,modusr,moddt) values ('sam',131,'7/2...more >>