Hi, I have a table with productIDs, productShapes, and productColors columns. I'd like a query that results in: productShape A Count(products regardless of color) Count(Blue products) productShape B Count(products regardless of color) Count(Blue products) productShape ... ...more >>

OK, This should be very very basic, but I cannot find any documentation on SQL create user. I am using sql server 2000 BOL does not have a CREATE USER statement, that I can find. What am I missing, where is this information and what are all the possible options for creating a new user in ...more >>

i`m wondering how fast r stored procedures which define their insert, select, where, from ... clauses as strings vs normal procedures? at least i`d think dynamic sql gets compiled on every run...more >>

hi, whats the best way to get say results from 41 to 60 from a query? thanks...more >>

I have towo database servers one is SQL 2000 and the other one is SQL 2005, I want the DB’s on the two servers to interact as the applications currently running needs to interact with both of them … Pls suggest an effective and efficient way to write queries so that tables in the two se...more >>

Hi Everybody, I need to return a values in a specific format I have a Table which has Start_date and End_Date as Start_Date End_date '1/28/2006' and '1/31/2006' I want to return values as Start_date End_Date 1/28/2006 1/29/2006 1/29/2006 1/30/2006 1/30/20...more >>

Hi, I have created the following SP and indexes, but the execution plan for SP shows that query optimizer always uses 'index scan'. -------- use northwind go create proc usp_search @country varchar(100)=null, @city varchar(100)=null as select customerid,companyname,country,city from cus...more >>

I wish to store the contents of an RTF control in our VB application in our database. Is ntext, text or image the best data type for storing rich text data? Thanks, Chris ...more >>

Hi Is it possible to SELECT INTO a temp table (#) within a VIEW, for some reason, it brings up a 156 error ? Kind Regards Ricky ...more >>

Hi ! i am a begineer in sql server as far as programming. I would like to have a trigger that runs on insert or modification and update the "last updated on" column. i was assuming i can do sth like create trigger tgname on table for insert , update as begin new.last_updated_on = no...more >>

Is this possible. To explain myself, here is my situation. I created an Excel Spreadsheet that uses an ODBC link to a SQL database. In order for anyone to use this file on their PC, that same ODBC link must be setup. Is their some code I can use within a Select Statement that will allow me...more >>

I have a table gVendor that has a field IsActive. It just shows if vendor is active or not. When I update gVendor informatoin I leave IsActive out and hanlde it in an other SP. The problem is that eventhough I don't update that fields it resets to false (0). I can't figure out why. Thank yo...more >>

I am using SQL Server 2005. I setup a sample Job that is setup to send an Email when the job finishes. I was able to send a Test email successfully but everytime I run the job, the job concludes successfully but there is a message in the View History that reads "NOTE: Failed to notify: 'USER...more >>

Server version is: Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Been having some challenges with the use of an indexed view. We've worked most of them out, but this one hit us today, and I haven't got a clue. Dropping the view instantly made the error go away, so we're positive the er...more >>

Why is it required to use 2 part naming for indexed views? I want to use 3 part naming with my SQL statement for the view. It seems to me that it should be possible to have 3 part naming with an indexed view because the database resides within the same server instance. The upkeep for the in...more >>

I have a table with a field of varying numbers. What select statement can I use to find out which number occurs most and how many times it occurs?. Ex if the number '123' is in the table 1000 times and '1234' is in the table 765 times, how can I determine that the number '123' is in the table ...more >>

Another T-SQL basic problem: This IF statement does not resolve... SELECT Tref= IF LEN(Tref)=1 AND ISNUMERIC(Tref)=1 Tref ELSE Tref=99 FROM MyTable Please help! Regards Clarkie ...more >>

Hi All I want to get only the 2006-01 from select rtrim(convert(char(9),getdate(),111),1) .. I have tried datepart( ) etc and i cant get it to work. Is there an easy way to do this? Thank you in advance. ...more >>

Our database is on a server provided by the web host. I would like to backup the data from this database more often than our web host performs backups. But since our database shares space on its server with other companies, we are not permitted to use the Enterprise Manager Backup utility. (T...more >>

Hi there, I have installed Sql server 2005 developer on my machine which already has a Sql server 2000 installed on. Now i am trying to query the Sqlserver 2005 data(Ex: from Person.Address located in AdventureWorks database) in Sqlserver 2000 query analyzer: When i try as Select * fro...more >>

How can select the first record if there are multiple records returning for one select system. Example update main_table b set amount=( select amount from main_table a where a.a1=b.a1 and a.a2=b.a2) where a.a2='xyz" the subquery is returning multiple records and i want to pick up only...more >>

Just curious if anyone else has seen this issue: We had a situation where the XML being retrieved from an XML Explicit query was malformed. After looking at the resulting XML from the query being ran with different sets of paramaters we were able to identify that the building of the XML was...more >>

I have a stored procedure which was written by someone else. It uses temporary tables and cursors. I have to re-write the stored procedure without using temp tables. Any ideas on how to proceed? Here is the old stored procedure. ***************************************************************...more >>

I am having a bit of trouble with SQLMail. I set up a profile and test sent a message and everything worked great. The I created a Job that I wish to have an email sent to myself upon the completion of the scheduled job. The problem is in the Job Properties/Notifications panel when I select...more >>

I get this error when I delete a row and then press the save data button in the data grid view control. Any Hints to fix ?...more >>

Copy and paste the code below into Query Analyzer. Run it. Note the output. Then uncomment the last line. Run it. Note the error. WHY, WHY, WHY? If "SELECT @Customer" works, why won't SQL Server execute the string? How can I make this work without a whole lot of monkey-business? I'v...more >>

I am working with code I did not write for a web app that used to run on an Access database. Due to performance problems I'm trying to move the app to a different server running SQL Server 2000 SP4, running on Server 2003 with MDAC 2.8 SP2. On the new server, one of the queries is failing in a...more >>

I get this when I delete a row then save data in a data grid view control. Hints to fix ?...more >>

Hi all, I have written many stored procedures in our database to import data from another database. Now I need to create a report that lists the source table and column names and their corresponding destination table and column names. I think it will be difficult and time-consuming for me ...more >>

I have a table that stores the access logs for a web site. For the sake of this question, the only relevant fiels are [date] and [time] (yes, I know they are reserved words), which are character fields. CREATE TABLE weblogchar ( [date] varchar(20), [time] varchar(20) ) --Sample data...more >>

Hi, Check out the below stored procedures... create procedure prUpdateFiles @path varchar(500) as CREATE TABLE #tempList (Files VARCHAR(500)) INSERT INTO #tempList EXEC MASTER..XP_CMDSHELL 'dir @path /b ' select replace(@path,'server03','iomeganas') + '\' + files from #tempList drop tab...more >>

Hi, Is there a performance difference between using a "WHERE IN" clause and using "INNER JOIN"? For example, let's say I have 2 tables customersTable and productsTable which are subset tables of a bigger table called customersProductsTable. What is better and why? Example 1: SELECT * ...more >>

I have a web app w/a form that takes user data and inserts into a customers table. Now if I try to insert a record w/identical 'customerName', this is the error: INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_transactions_customers'. The conflict occurred in database 'C...more >>

I'm trying to find the number of Mondays since 6/1/2005. Any suggestions? THanks...more >>

Hi, Is there a way to retrieve a list of file names using T-SQL where I provide a path and SQL returns a list of file names for that particular folder? If not, what other methods are available? Any help is appreciated. Curt ...more >>

Hi, Need some help here. Is there a way to get a list of file names from the file system using T-SQL? Or is there some other method (DTS or Setting up a linked server) which will accomplish this? I need to provide a list of file names from a folder where I will provide the path statement and ...more >>

Can somebody provide a solution for this please. I have a SQL report exporting to Excel. All is well when the dataset for the report is not empty. when the dataset is empty, the excel file created does not contain the column headings of the table. looks like the entire table object is no...more >>

hi everybody. A customer of mine has had a disk "crash" few days ago. I wrote for him a dwh application and of course the backup was not working... now some "hardware guys" said that it is possible to recover most of the files. what I need to restore would be: 1- the sql server db 2- the...more >>

My SQL server is SQL 2000. The DTS package uses SQL anthentication to transfer data from database A to database B. It fails. The error message shows Windows NT user or group 'A\Guest' not found. In DTS package, on Copy SQL Server Objects Task Properties screen, if I don't select Create des...more >>

Greetings! I have created a stored procedure on one of our SQL Server 2000 databases. Its not too complex but does involve a correlated subquery. When run in Query Anaylzer (QA), the procedure returns after 40-45 seconds with the correct recordset. Set nocount on has been set just to ...more >>

Hello, I need help with my function. I need a date difference (actual datetime and another datetime in db) in hours It worked perfectly with DATEDIFF....But now I found out that I should count just the "working "days (Mo-Fri). is there any function or mechanism how to do the same but just wi...more >>

Hi I'm trying to create a CLR-TVF which should do some stuff (in my sample it's just getting the syscolumns name column for the database _ODS). I’ve got this error: An error occurred while getting new row from user defined Table Valued Function : System.InvalidOperationException: Da...more >>

Hello, I have a stored procedure which creates a temporary table, inserts some information, displays it and then finally drops it. The code works fine, but I am starting to be a little bit worried. What happens if ten people visit the page at the same time. Will the SQL Server wait until th...more >>

into SP I have one table with one nvarchar field, with for Example this text "iTotalUser/iTotalClick" I not need this text value but result of operation, beacause "iTotalUser and iTotalClick are two integer column into my table, You now how I can? I need this operation, because I want use one...more >>

Is there a way to set NoCount on globally across the database rather than writing it in each and every stored procedure?...more >>

I am developing an inventory database in SQL Server. I realize there are many commercial (as well as some non-commercial) inventory offerings, but my client has specific requirements that would necessitate significant customization of any off-the-shelf application. In the end, we decided it ...more >>

1min 40secs to do a select * on a table with 10 columns and 13 rows from within Management Studio. I guess they do need to keep these hardware manufacturers in business..... :-) Michael ...more >>

HI all, I need help on some SQL logic. use sql2000 I have a two tables related. The parent has cascading deletes set. Which obviously means when deleting a parent all children are deleted as well. Which is what I want. Also the user has opertuninty to delete indivisual child records, With on...more >>

Being new to sql programming, I have a small problem. I am trying to create a stored procedure, but I am not sure if I am going in the right direction. The problem : Table Heading - HeadingID (PK) - strHeading Table Rang - RangID (PK) - strRang Table Details - DetailsID (PK) - ...more >>

Hi, Is it possible to do alter a column and setting the starting seed from a variable? If not, is there a workaround....more >>

Hello! I need some help with this DISTINCT problem. What am I doing wrong? My T-SQL is lousy, I know... SELECT DISTINCT (Deliver) FROM (SELECT Deliver=CASE WHEN DelivDate >'2006-12-31' THEN '1999-12-31' ELSE DelivDate END FROM TTransaction) Result: Server: Msg 170, Level 15...more >>