I know I can't select fields unless they're included in an aggregate or a group by, but that's the closest I can come up with to explain what I need. I've got the following table ("T"): AID FID VAL 500 910 x 520 910 y 520 950 z 500 950 a 500 990 b 520 990 c I want one VAL for each d...more >>

I am getting error 15007 when trying to create a new user. Why? This is a silly error, I know the user doesn't because I am trying to add them but this is the error I get when trying to add any user to the database. What the heck am I doing wrong? Thanks in advance! Chris ...more >>

Hi, Would someone be able to point me in the direction of some T-SQL for a trigger that I can use to write a BLOB to the server's disk. Thanks, Alex...more >>

Hi, I'm not very familiar with ntext fields, and I was wondering how efficient they are if I have a table with those fields. I don't know how SQL Server stores those fields internally, but I can see in the definition of the table the length is 16. Does it mean it's just storing a pointer to...more >>

I seem to be having difficulty importing data from an Access db when it's other than on the server. I can create a linked server if the db is on the server, but otherwise not (as per BOL). How should I move the file to the server? Does SQL Server contain any facility for moving a file acros...more >>

Hi, I have a user defined function that opens a cursor and builds a delimited list of values. However, when trying to compile it...it whines with an error 444 saying "Select statements within a function may not return data to a client". Yet my select is not trying to return data to the client....more >>

Hi all, Is there a way to have an SQL query retrieve a specific range of records from a normal result set? For example - Let's say I retrieve all records from california and the result set totals 50. To improve performance on the interface in I would like to reduce the amount of p...more >>

let me ask a question: -- how can we get number of rows that a cursor can fetch? ...more >>

let me ask a question: -- how can we get number of rows storing in a cursor ? ...more >>

under sa account, I run following script, exec sp_executesql N'GRANT EXECUTE ON dbo.xp_cmdshell TO joe' now, I loginid using joe/joe run following script: xp_cmdshell ' dir *.exe' I got error: Msg 50001, Level 1, State 50001 xpsql.cpp: Error 87 from GetProxyAccount on line 60...more >>

I am now all of a sudden getting an error in this sql connection string, saying that the Provider keyword is invalid: <add key="MM_CONNECTION_STRING_isox" value="Provider=SQLOLEDB;SERVER =xx.xx.xx.xx;UID=xx;PWD=xxx;DATABASE =xxxx;"/> it has worked in all my dotnet and dreamweaver asp.net c...more >>

Hi group, Can anyone tell me how to perform these steps just using T-SQL? I performed these steps in EM and watched what was happing in SQL Profiler, but either I'm filtering too many things out in Profiler, or SQL Server (2000) is lying to me. Steps in EM: - Import Data - Source = Ser...more >>

Greetings guys! Not sure where the best place is to post this, so bear with me. Is there an article or some consensus somewhere about common column names such as: LastName, FirstName, MiddleName, Address1 or Street1, City, State, Zip, etc. in terms of their field length, type and field name? ...more >>

I need to insert some rows into a table from another table only if they don’t exist in the first table, and I can’t quite figure out the syntax. It's very possible I'm making this more difficult than it has to be; in that case, my apologies in advance. Here’s some DDL: CREATE TABLE Product...more >>

I have several tables in a database that I need to preserve with static data, there is one table that I would like to empty the data from but retain the fields and properties within it. Can anyone help with a sql script that will allow me to do this. TIA John ...more >>

I ran across something that is somewhat odd and I'm wondering if the behavior is by design or if it is a bug. Trying to run the following in the Northwind database: select * from customers where customerid in (select customerid from categories) Returns all rows in the customers table. Howe...more >>

Is there an easy way to copy indexes from one table to another. i.e. select * into TableA_Copy from TableA And then copy all the indexes that exist on TableA to TableA_Copy. Thank you for your help. ...more >>

I frequently read here that cursors are never the correct solution. I have an app that allows end users to open an order and select what rental equipment on that order to take off rent and schedule for pickup.... The following procedure is how i currently handle this..... Whithout using...more >>

Hello I have an old problem but now it became ugly.. I have an object (for example a function) that is used in some views. If I am looking on dependencies, it shows me the 8 views whcih depends on it. But if I edit the function in Query Analyser and run the script (no modification on the script), ...more >>

When I initiate a simple update on a single row in a table, I am generating dozens of index scans for reasons I don't understand. The form of the query i update table1 set col0=0, col1=1, col2=2, col3=3,..,coln=n where colx= colx is a clustered primary key. Only a single row is updated for each ...more >>

what type of datatype would i use to store large binary files such as a exe or MPEG movie in a database column? thanks ...more >>

Hi I have a query and in that query a subquery Sometimes that subquery don't give any results. In that case my output is always NULL How can I make that it is not NULL but 0 if the subaury don't return any results Thanks JJ...more >>

I'd like to execute a system stored procedure, sp_fkeys, from Query Analyzer and dump the results into a temp table or table variable which I will use later in my process. How can I do that? I've tried the following with no luck... DECLARE @fkList TABLE ( PKTABLE_QUALIFIER sysname collat...more >>

hello, I'm looking to sort a list of records. These are oil pipeline numbers with the first digits indicating their size in inches. Anyway, here's some DDL... drop table pipelinetemp go create table pipelinetemp (line varchar(25)) go insert into pipelinetemp values ('1.5"-1040-XX') in...more >>

From SQL Server BOL (sp3) "Note: Deferred Name Resolution can only be used when you reference nonexistent table objects. All other objects must exist at the time the stored procedure is created. For example, when you reference an existing table in a stored procedure you cannot list nonex...more >>

Is it possible to use multiple columns in a delete statement where clause and IN clause as seen below. I cannot find any information on it. DELETE Commodity WHERE (InvoiceNum, ShipmentKey) IN (SELECT I.InvoiceNum, S.PKeyShipment FROM Invoice AS I, Payments AS P, Shipment AS S WHERE I.PK...more >>

Hi, I've created a sp that used dynamic sql and embedded with linked server = name. It was tested working @ the query analyser. But when that was placed in the schedule job to be run, it give error = that login/password was incorrect. Both my servers maintained difference set of login/passw...more >>

We have migrate about 20 db on one server to SQL 2000/Win2003. What things should I tell the programmers to look for to update their db to be more in sync with SQL2000? I would gather that some ways of doing things in SQL7 should be change to lessen the load on the SQL2000 performance. TIA ...more >>

Use master --This works exec xp_sendmail 'scott_riehl@b-f.com', @message='test message' --This does not work exec xp_sendmail 'scott_riehl@b-f.com', @query='select [name] sysusers' results are "ODBC error 4604 (42000) There is no such user or group 'sa4'." and the 'sa4' changes from...more >>

I have a job which runs continuously, and imports data from flat files which arrive from time-to-time. I want to be able to: a) ensure that the same application cannot be started if it is already running in a database b) raise an alert/email if the job fails for some reason The job is actu...more >>

How can I say: show me the rows in table A that don't exist in table B? (table A and B share the same structure.) I thought I knew how to use NOT EXISTS correctly, but I guess not......more >>

my db has a License table. when created, the License record is little more than a skeleton record -- it only contains some basci identifying info. at various junctures, the License record is updated for various reasons. the updates continue indefinitely. one such juncture is "license issuan...more >>

I am using the built in PWDCOMPARE function in SQL server... declare @password as char(7) set @password = 'password' Select PWDCOMPARE(@password, PWDENCRYPT(@password),0) as encryptedcompare from profiles I get 0 everytime! (Even though the string 'password' is a valid value in the DB...more >>

Hello there I have table with companies My client enter the same company twice with diffrent id. I would like to delete the wrong one The problem is that This table is related to many other tables so if i will delete the wrong company i will delete also all the relate data on the other t...more >>

Hi all I need a bit of advice on how to perform a specific task. I have an XML document that contains values that I would like to import into a table, but the table may already contain some of the values and I don't want to insert duplicates. What is the best method to get the records into ...more >>

Hi everyone, Is it possible to, somehow, reserve identity value before inserting the row. tia Zarko ...more >>

I have a query which needs to count # of orders processed through the end of the previous month for the past 12 months. That means, if I run the report today, I need all orders processed from February 2002 Through February 2003. I dont care about any in March 2003. Thus, if I run teh rep...more >>

Dear Friends Please suggest what is Microsoft Reporting service and how i can take the best use of same. Your guidence will help me a lot. Best regards Sharad...more >>

Hi I have the foll openquer select * from OPENQUERY(PROGLINK, 'select number, sum(quantity + overage - notdeliv - short - damaged from history where id = "s0912070" group by number' how can I assign a column alias to the "sum" section. If I run the query it returns two columns "number" and "...more >>

hope someone can help... we have a number of db tables that are highly normalized. One of these tables (file) holds the ID to a file. All file IDs are stored here. The rest of the columns are dynamicly added by the system, each one of these columns represents the retrievalble index information...more >>

I have successfully created a linked server to an excel file. I can easily query but I get an error when i try to create a table which should create a new sheet in the workbook. The linked server has been created using jet.oledb.4.0 as microsoft describes in one of its articles. The strange is ...more >>

Hello. I use SQL Server 2000 (MSDE). I have a table TAB with a field A of type FLOAT. I also have a view UQV_TAB_IX which fetches non-null values from the table. Now, I cannot create an index on this view - I'm getting such message: Msg 1933, Level 16, State 1, Server SERVERNAME, Line 1...more >>

Subject: Re: String Qurey With L'Oreal From: "Tom Moreau" <tom@dont.spam.me.cips.ca> Sent: 3/24/2004 9:02:39 AM Tom gave me the answer to how to query for L'Oreal. How Do I do it if I'm passing stings contained in a record set that is aquired dynamically? The fields are all ...more >>

I need to know how to make this query work. I do not know how to overcome using a single quote in a string query. Please HELP!!!!!!!!!!!!!! SELECT DISTINCT att_BRAND FROM dbo.Rank GROUP BY att_BRAND HAVING (att_BRAND = N'L'Oreal]') Thanks, Greg...more >>

Exists question aren't original I know, but anyway. I've seen lots of examples of checking for temp tables etc, this is the situ: sproc creates temp table using user id eg: declare @SQL1 as nvarchar (2250), @SQL2 nvarchar (100) @SQL1 = 'create table #tmpTable' + cast(@@SPID as nvarchar)....more >>

How would I calculate age of a patient as of the 15th of a month. Here's the query I have for age: SELECT CASE WHEN DATEADD (year, DATEDIFF (year, MemDOB, GETDATE()), memDOB) > getdate() THEN DATEDIFF (year, MemDOB, getdate()) - 1 ELSE DATEDIFF (year, MemDOB, getdate()) END as 'Age ...more >>

Hey all, Quick and easy. What are the differences, benefits, and drawbacks of these two ways of setting a new object: Dim OBJECT as New ObjType ----------------------- - VS ----------------------- Dim OBJECT as ObjType Set OBJECT = New ObjType Thanks in advance, Gerard G...more >>

Hi, I have got a query that is grouping the data by week nos, however displaying the week numbers is meaningless in the report. The week numbers are being calculated by using the date part function in sql 2000. week sales 1 5 2 6 3 7 I need to display the date r...more >>

SQL2000 SP3 + Win2000 SP4 + Access 2000 SP3 In VBA you can use the eval() function to 'evaluate a string' eg In a Table you have these fields/columns Start_Value Operator Amount 100 * 2 so eval([Start_Value]&[Operaotr]&[Amount]) is the same as saying 100*2...more >>

I have a stored procedure that update records in one table from another and I am geting some records updated and other are not. To try and find out what is going wrong I created a simple update query as follows:- UPDATE dbo.stock SET SalesWK2 = 1 even with this some of the records update...more >>

I have a table with a primary key that may be edited. There are 2 (secondary) tables whose primary keys depend on the first table's primary key, and a third table whose primary key depends on that of one of the 2 secondary tables. A perfect situation to use the built-in cascading foreign key const...more >>

Is it possible to have a SP return a hyperlink? I'm trying to use a SP to return a set of values and need one to be in the form of a hyperlink. These values are being loaded into a dataSet in C#/.NET. The way I'm trying it now I get an error saying "Syntax error converting the varchar v...more >>

Hey all, I have an issue with SQL 2k on Win 2k Server. My connection object(Dbs) doesn't have .CursorType as a property, and when I just type it in despite theintellisense saying it's not there, I get a runtime error saying, "Object doesn't support named arguments". After assigning ...more >>

Hi, I hope somebody could advise on me on the following. I have two tables that represent a parent-child relationship. In table B I have a foreign key to table A. In otherwise table B could be deemed to be the child table (or the many side of the relationship) and table A could be said to b...more >>

is there any way to construct a looping mechanism over the inserted or deleted tables in a trigger. if there were multiple rows inserted, how would i be able to traverse the rows one at a time?...more >>

In a web application I need to call a stored procedure that lists items in a shopping cart. The Sproc is pretty complicated as it needs to do compatibility checks between the items in the cart, and makes use of temporary tables. It has one parameter, the order ID. The problem lies when the rec...more >>

I need to execute a stored procedure in query and use the result set, sorta like this.. SELECT * FROM (exec mystoredproc @Param1=2394) as table Is there anyway to do this. I also posted this same question yesterday, but I cannot see it TIA, MikeL...more >>

SERVER: Win2000 SP4 + SQL Server 2000 SP3 Client: Win2000 SP4 + Access 2000 SP3 (using Access Data Project) How can you insert dummy columns in a union query (sproc) in sql server? eg in an Access Query I would: SELECT columnA, columnB, sum(columnC) as columnC, sum(columnD) as ...more >>

My application is working on several databases on the same SQL Server instance. I'd like to write a query making a join between tables located in two different databases (one of theses tables having 1500000 records). Does any body knows if there is a performance penality in making query bet...more >>

Hey all. I've been trawling the groups and the net regarding the age-old question of storing images in the filesystem vs. SQL Server, but there's one aspect that I haven't been able to find a satisfactory answer to yet, hence why I'm asking directly. Applying the rule of thumb I've seen adv...more >>

Hi Sql Gurus, 1. Is it possible to find the number of words stored in a table column storing either fixed length (CHAR or NCHAR) or variable length (VARCHAR or NVARCHAR) data ? If yes, please elaborate with example ? 2. Is it possible to capture and return the number of words returned...more >>