Hi, I'm just starting out with SQL. I'm wondering why this nested query doesn't work. I'm sure it's something simple but I'm not sure what. Hope somebody can shed some light. Thanks in advance Ant SELECT myDate, SUM(deposit) FROM ( SELECT CAST(DATENAME(MONTH,date)+ '' + DATENAME(Y...more >>

will a insert/update/delete trigger be fired if there's no any row affected by the insert/update/delete statement?...more >>

How do you know which table is the intermediate or translation table in a query using multiple joins. For example: SELECT T1.ID FROM T1 LEFT OUTER JOIN T2 on T1.ID = T2.ID LEFT OUTER JOIN T3 ON T1.ID = T3.ID WHERE (T2.ID IS NULL) AND (T3.ID IS NULL) T1 --- A B C D E T2 -...more >>

Suppose that I have a table that represents the chain-of-command hierarchy of a company. Lets say that the first column of the table represents the employee name and the other represents the employee's boss. Lets also assume that one employee can have several bosses. What I would like to do...more >>

Hello MVPs: we have several tables with many records(about 10000000,developement Database is MS SQLSERVER 2000 Enterprise.). the data is quite static,and it is not change frequently.We have many batchs running at a fixed schedule.mainly we query against these tables. is t...more >>

Hello MVPs: we have several tables with many records(about 10000000,Database is MS SQLSERVER 2005.). the data is quite static,and it is not change frequently.We have many batchs running at a fixed schedule.mainly we query against these tables. is there any solutions to impr...more >>

Hi, I have a column in SQL DB and the column contains the information like: <ProductDescription>This TV is good. </ProductDescription> This TV is sold out. <ProductDescription>This TV is bad. </ProductDescription> This TV is not selling well. (By the way, I am NOT talking about the XML...more >>

Hi, I have an issue with my query. 1. I have 1 stored proc which have execution calls to multiple stored procs within it. 2. I want to wrap that main stored proc in the transaction and rollback if there are errors execution calls to other stored procs. I don't believe my code is account...more >>

I have a stored procedure that runs along the following lines Begin tran update a ...... insert a ...... Commit tran and when these stored procs run concurrently, we see deadlocks at times . How can I rewrite this query to avoid deadlocks ? ...more >>

I am using the stored procedure method of paging where you select your results ordered into a temporary table then select the page you want based on a unique id. I am try to maximize performance so I wanted to know.... 1. Is this the best way to perform paging performance wise? 2. If so... ...more >>

I need to upload multiple excel files from a directory to sql server very night. I will not know the flile names but the layout are the same. The process needs to do an insert if the key is not there, otherwise, do an update.... Any examples? DTS or VBScript? Can DTS be setup so it will proces...more >>

I am new to SQL Server. I want to use the DCount function ( Iam trying to enter the occurence of a particular field in another field) in a Update query but it gives me an error message. Is there a way out? Thanks in advance....more >>

Instead of writing 8 or 10 different selects for each individual Dept, as in the sample 2 below. I would like to end up with one SQL statement the "passes the departments in" based on a departments list and get the top 5 per dept in a single recordset. I know it's possible, but my ...more >>

I have a few questions about sql licensing...i've read the licensing info on MS site and it's kind of fuzzy. 1. per cal vs per processor. if I purchase one cal for an user, does that mean that cal can access any number of sql server? 2. in a per cal scenario, if a phone system software is c...more >>

Hi Friends, SQL 2000 I resotred a backup of distribution db as d21 and now when I am tryiong to delete this db , it does not let me to do so, saying , database in replication. How can I delete the d21 database ? The repplication is on and going now. Thanks, Patrick ...more >>

I am trying to import a flat file of semi-colon delimited values into a table. The table has many foreign keys that reference other tables. The flat file has the data all explicitly stated instead of the ID numbers. I want to make it so that if a new value is encountered, a new entry in tha...more >>

Hi all, I have an initial parameter = 'TST0001' I want to write an INSERT statement to automatically take the initial parameter 'TST0001' and keep adding 1 to it until it get to 'TST9999'. Now, my table should store data like these: TST0001 TST0002 ..... .... TST0010 TST0011 .... .....more >>

I have a table that has an image datatype column and I also have a contant type column in that same table to define which type of data is stored in that table. In this case a screen shot or may be a word document may be stored in that table....! I am trying to use that image datatype field ...more >>

Hi, I have two tables i.e. AgencyStats1_2005 and AgencyStats1_2005_missing The two tables have a common field i.e ORINumber. The table AgencyStats1_2005_missing has all the offense values which need to be used to update the offense field of table AgencyStats1_2005. I am writing the followin...more >>

Does anyone have experience with this, offer any suggestions. Thanks in advance, Bryan...more >>

Consider Table1 ID Name and Table2 EntryCode EntryDesc I want a query to retrieve all combos. So if Table1 has ID Name 1 Jane 2 Fank and Table2 has EntryCode EntryDesc One First Two Second Three Third I want ID EntryCode 1 ...more >>

I'm putting together a resultset in an sql string (it will be used in asp.net) where I'm drawing from a few different tables, and I do have a relation with the tblDeal and tblSalesrep ID : ''''''' strSQLQuery = "SELECT d.salesrep_id,s.fname,s.lname, s.boardtotal_note,s.BoardTotal_BonusPerc, s...more >>

Let's pretend I'm running a big fat batch in a giant stored procedure that's chewing on about half a million rows, and meanwhile, some clown in MIS decides it would be a neat idea to reboot the big momma SQL Server in the middle of the work day. What might happen? Think the MSSQLServer ser...more >>

I have a couple of simple questions about parameters in Stored Procedures: 1- Can I use an integer parameter to define the maximum number of rows to return using the TOP Clause? SELECT TOP @MaxRows ? 2- How do I declare and use a boolean parameter? 3- Is there a way to construct a WHER...more >>

Hi All, When I run sp_who2, I see there are many sleeping processes. Instead of killing one by one, I was wondering if there is any way to kill all sleeping processes programmatically (MS SQL Server 2000). Thanks a million in advance. Best regards, mamun ...more >>

Hi , I want to know how to find out the who last modified any stored procedure in the database. is there any way? thanks in advance Bhaskar ...more >>

I have the following table: CREATE TABLE [dbo].[Applicant] ( [ClientID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [ApplicantID] [bigint] NOT NULL , [PositionID] [int] NOT NULL , [JobID] [bigint] IDENTITY (1, 1) NOT NULL , [DatePosted] [datetime] NULL , [FirstName] [...more >>

I am trying to monitor drivespace on several servers. I am creating code on a monitoring server that will go out and test drive space and page me if limits are reached. I have set up linked servers, and tested: Exec LinkedServerName.master..xp_fixeddrives -- It works great. The problem...more >>

SELECT TOP 100 * FROM ProductsChangeLog ORDER BY created DESC --- Server: Msg 536, Level 16, State 3, Line 1 Invalid length parameter passed to the substring function. The 'created' column is datetime with NO NULLS. Maybe there is a bad entry that SQL server is tripping on, but I have no idea...more >>

I have 2 tables that both contain around 500,000 records a piece. There are about 180 column in each table and each row averages about 20k to 50k of data. After adding some indexes to the tables my sp's are running much faster now. In the selections for the Indexes I have unique, clustered,...more >>

I have a ntext field of data. I was trying to use the REPLACE function to change the carriage returns to spaces, but have not any luck. Can anyone make any suggestions? Thank you, JLFleming...more >>

Below is what I currently have in query. I have a question for the line that has an asterisk before it. Is there a way to show if a card number has shown up more than once on a given settlement _batch_num? I have no clue what to put as the COUNT CASE since I am searching over a period of ...more >>

I can't remember the name of the process but i am thinking of putting in a time stamp column so i can prevent a lost update. In prose when the row is read an modified check to see if the time stamp is the same on update as it was when it was read. Here is what i am unsure of. Does the upd...more >>

I have a urgent need to for processing some EDI files to be loaded into SQL Server and also outputting data in SQL Server to EDI. Can somebody kindly share a sample EDI file and an scripts you have built to process this file inbound into SQL Server I would greatly appreciate the help. Also,...more >>

Hi In SQL Server 2005 there is a function for Database Mirroring which will syncronize between 2 sql server databases . the more details are explained in the following link. http://www.databasejournal.com/features/mssql/article.php/3440511 the same option can be done thru SQL Server 2k...more >>

I've found a syntax error reproduced in this small example USE Northwind select * from Orders o inner join ( select od.OrderID from Products p inner join [order details] od on od.OrderID = o.OrderID -- this doesn't work -- on od.OrderID = 10251 -- this works ) rb on o.OrderID = ...more >>

I just need to know how effective this query is? CASE WHEN sum([DEBIT AMOUNT]) > sum([CREDIT AMOUNT]) THEN sum([DEBIT AMOUNT]) - sum([CREDIT AMOUNT]) ELSE 0 END [OPEN DEBIT AMOUNT] Should I rather to the sum() functions in a subtable? e.g. SELECT CASE WHEN [DEBIT AMOUNT] > [CREDIT AMO...more >>

Can I change a double byte (nchar) to 2 single byte (char)? ...more >>

Hi, I have a trigger where I would like to insert the new inserted value into another table. How can I do that? I tried with this: insert into CallsSync select * from Inserted but I get an error because I have text fields inside the source table. Thanks...more >>

Hey Guys Still very new to asp/sql but trying to learn stuff as I go along. I'm trying to create an update form where it pulls in an existing record, I can update it, then submit the changes. The problem i'm having is when i click to update the record I get this error message Cannot ...more >>

Because sp_executeSql supports parameters not longer then 4000 characters, I split sql statement in 2 parts: (my sql statement is 6000 characters long) declare @sql nVarchar(4000) declare @sql1 nVarchar(4000) SELECT @paramlist = '@xDMLz int,@xDMLk int,@xdatumZ datetime,@xdatumK datetim...more >>

I can't seem to get the nesting correct for an IF THEN condition inside a function. My intent is to return the results (a table) of one of two dfferent complex select statements. And, really, I am porting this SELECT over from a working stored procedure. I wanted the convenience of being able ...more >>

Hello All, I have a vb.net application that connects to local SQL Server 2000+SP3 DB server has 1.5 GB RAM, if server just restarted then at end of first day memory consumption for the service is 600 MB , fine, even though at night no one still connected to the server , memory does not release...more >>

Hey all, Just when I thought I was starting to get the hang of T-SQL, I try a project that now (after several failed attempts) has me thoroughly confused. I'm trying to create a procedure for a 'search agent' that will execute users' saved searches and generate email notifications via xp_smtp...more >>

Hi, What is the fastest way to insert about 1000 rows from one SQL Server into another over linked server? I'm using SS2000 Enterprise edition SP3 on Win2000 Advanced Server SP4 Thanks in advance Nikola Milic ...more >>

Command line parameter passing Does anyone know if it is possible to pass a parameter to a script run from the isql (Query Analyzer). I have a large .SQL file that does a setup of a database and I would like to pass it a parameter that is A name of another database to create some views to. ...more >>

Hi, I'm trying to clean up some old mess. We've got an sql server that keeps reporting deadlocks. There is a loging application that keeps inserting and deleting records from a table. The delete statement will always delete only 1 row. The table is without primary keys and indexes. My qu...more >>

I have table Order with value column, date column, .... Is it possible to select all orders while sum(value)<1000 order by date column. Something like: SELECT * FROM orders while sum(value)<1000 order by date Hope you know, what I mean. Regards,Simon ...more >>

Hi friends, Sorry for my not very good English. I am new in sql server. I am using sqlmoney to represent the price. First of all I am not sure is it good idea or I have to use decimal? And my second question is haw to cut the last 2 digits of sqlmoney (I mean for example 12345.1200 t...more >>

Hi all, I'm posting this so it'll be archived in the great google repository, and hopefully be useful to someone. I spent awhile troubleshooting what turned out to be a completely misleading error arising from a simple typo in a query. Here's the crux: --- create table foo (id int) ...more >>

I have two tables: Table1: IDTable1 IDTable21 IDTable22 Table2: IDTable2 Data1 IDTable21 and IDTable22 are connected with IDTable2 Is it possible to write a query to display Data1 for IDTable1 and IDTable2 ? I know that it is possible to do, but on...more >>

Hi, I have a problem wille debugging the SPs. Its showing error when i run the service on my loginname. 'Error id 5; Access Denied' . Then I add my login in admingroup and then restared the service. Now the service is ok, but debugging is not working, instead of debugging, it just runs the whol...more >>