Hi, Any Suggestions on identifying the File System (FAT / NTFS)of the current hard drive on which the SQL Server is Running? Rao...more >>

Hi All? I know that My SQL Server 2000 (Default Installtion) DB is Case insensitive. But How Do I Find that? Is there any Environment Variable? And I have One Doubt... Does the Search oc Character Field Differ if that is RUN on a Case Sensitive DB or on a Case IN Sensitive DB? If Yes what...more >>

Hello, does anyone know if there's a document about patterns and practices when it comes about programming stored procedures? I've tried to find on MSDN site, but I could not find anything. Thanks ...more >>

Databases were migrated via detach/attach from SQL Server 2000/Windows 2000 to SQL Server 2000/Windows 2000 and now scheduled backups and even manual backups say they complete successfully but the backup only runs for like 10 seconds and a backup file is not created. The file pops up in the di...more >>

Hi All, I have one table with Millions of records with a Varchar Column which is Indexed. Suppose I will Search on that Column using the Below SQL, which one will result faster output? a) select col1, col2, col2, col4, col5 from table1 where col2 like 'prab%' b) select col1, col2, col2...more >>

The following code works fine to select, but derails at line 3 (the JOIN) with a syntax error if run as a deleting statement. Can anybody tell me why? I know I'm going to feel dumb when I get the answer.... Select pjinvdet.* -- DELETE From pjinvdet JOIN pjpent ON pjinvdet.project = pj...more >>

Hello, I have vstudio.net 2003 which came with SQL Server Developer. The sleeve that the CD in appears to wrong, as the product key is not working. The text on the back of the sleeve reads: 'Windows Server Enterprise 2003 Media Assy' How do I get the license? Thanks...more >>

I have an application that uses SQLServer 2K at my site but allows for a "suitcase" model using MSDE on the client's laptops. I have a table that is keyed on three fields: STUDENTID, COURSECODE, PREPCODE with additional fields of MarkQ1, MarkQ2, MarkQ3 and MarkQ4. I would like to have a S...more >>

Repost and added with more info. I have a set of database which is replicated from production box. That means all objects are inherited from production box including the data. This is the problem situation. We have a client application that keep receiving data from server application and in...more >>

Hello, Are there any programming tricks to have a faster Sql Server response over a VPN using ADO in VB.NET? Regards, Alpha ...more >>

Hi, I am not sure how the Transaction Log works. I created a scheduled job to make a full back up of the database every night and a transaction log back at the noon everyday. After the backup, should I also shrink the Transaction Log? or I can set up the AutoShrink function? If I ch...more >>

Hi, Are there any difference(in performance) between these two type of join: SELECT Customers.CustomerID,orders.orderid FROM Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID & select c.customerid,o.orderid from customers c,orders o where c.customerid=o.customerid ...more >>

Hi NG DevEnvironment: WinXPpro, SQL2000Dev(SP3)-Mixed mode ProductionEnvironment: Win2Kserv, SQL2000Ent(SP3)-Mixed mode I use the DTS wizard to export data from a table to a fixed lentgh file, and save the DTS. Created with connection User=sa. The DTS works great, and is saved to ...more >>

Hi everybody !!!! How can i return the results from the query in columns results??? Exemple: func | date_time 1 29/10/2004 08:00:00 1 29/10/2004 12:00:00=20 1 29/10/2004 13:00:00=20 1 29/10/2004 18:00:00 to func date_time1 | date_time2 ...more >>

I'm trying to write a form that will insert records into two tables using 2 separate stored procedures. I am trying to do this all within one transaction in Visual Studio 2003 and VB.Net. This is a very common header/detail arangement with header and detail linked by order number. The deta...more >>

Hi Friends, I setup SQL mail profile and I am able to send mail using xp_sendmail Now I have problem with using operators for sending mail. It is complainig about that mail agent is not started. I checked the SQL Mail and it doesn't show green arrow on it. How should I start SQL mail? I ...more >>

Hi all, I am using ADP as a front end to SQL Server2000. I am using ADO to create view1 with a view of 4 columns. Then I drop view1. Then I create view1 again with only 2 columns. If I exist out of the form and look at view1 at this time, it will have header for 4 columns (just like the ori...more >>

Hello, Is there a way to get a "random" sql server generated temp table name that has not been used before? This way the same stored proc could be called from different threads and not conflict? Regards, mekim...more >>

I'm confused about covering indexes. Inside Microsoft SQL Server 2000, Delaney 2001, page 827, says this: An index that contains all the referenced columns is called a 'covering index' and is one of the fastest ways to access data. ... For example, suppose that the Employees table has a c...more >>

Hi, I have to import a data file into my table. My table has 4 columns & the datafile has 3 columns. I am using the following format file with fixed length. 7.0 3 1 SQLCHAR 0 5 "" 1 order_number 2 SQLCHAR 0 7 "" 2 item_number 3 SQLCHAR 0 15 "" 3 type 4 SQLCHAR 0 0 "\r\n" 0 info_...more >>

I have a table with photo information. One column is Date_Taken. How can I select the last 50 rows based on the Date_Taken column? If I use SET ROWCOUNT 50 SELECT * FROM Photos, I get the first 50 rows in the table. -- Walt...more >>

Hi, I have a table that I am doing a bunch of bulk insert statements. I have a field that is nullable. I need to make that value be the default value when it is not supplied in the file. this is by bulk insert statement DECLARE @SQL VARCHAR(1000) DECLARE @vchFileLocation VARCHAR(50) D...more >>

Hi Group I'm trying to generate a table where I need to break records out based on taxes. I am trying to do a case on my tax type, then Insert from 1-3 records depending on the tax type. Can I generate multiple insert statements, based on a selected row inside of a case statement?? i.e. ...more >>

Hi, I'm defining several tables in SQL Server. It is a matter of specifying a SQL table column of length 15 or 16? I've heard that it's better to use a multiple of 4 in any column length and even variable declaration during programming. e.g. 32 rather than 30. 64 rather than 60. rega...more >>

Hi, I have a question about the report of SET STATISTICS TIME ON. I run this query: SET STATISTICS TIME ON GO SELECT Customers.CustomerID, Orders.OrderDate FROM Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID The report is like this: SQL Server parse and compi...more >>

I've got a simple query running on MS SQL 2000 SP3 : SELECT N.CUSTNMBR,S.XTNDPRCE FROM SOP30200 N RIGHT OUTER JOIN SOP30300 S ON N.SOPTYPE = S.SOPTYPE AND N.SOPNUMBE = S.SOPNUMBE WHERE N.DOCDATE BETWEEN '10/18/2004' AND '11/30/2004' and LEFT(S.ITEMNMBR,3) IN ('A4T','TRA', 'SEV','AVA') AND...more >>

I want to send an e-mail message with attachments from a job in SQL Server. I chose not to use xp_sendmail b/c I don't have outlook (or any MAPI client) on the production server and I don't want to put it on there. Also, my production server does not have an SMTP server but can contact one. ...more >>

hello there I always got around with creating only one primary key, but I was wondering if i could create a composite primary key for a table. I want two fields to make one ? I tried with just selecting a field to be PK then went to the second one and it doesn't work from EM. any thoughts ?...more >>

Hi again, Sorry again for the similar post. Running this I get 267 count Select Nature, Nature2, Risk_factors, Risk_factors2, Risk_factors3 from tempercp where (Nature = 'pancreatitis'or Nature2 = 'pancreatitis') and Risk_factors <> 'iodine allergy' ************...more >>

What library is the bcp_init function in?? The DB-Library or something else ?? I read that the next version of SQL server (after 2000) will not support the DB-Library and am worried because these bcp APIs are easy to use for what I have to do here. Thanks!...more >>

I see how to have a deletion of a row of one table cause the deletion of a row in another table through the use of a Foreign Key. What I do not understand is how to have the change in data in a column that is not a FK , cause the deletion of a row in another table without using Stored Procedure....more >>

Hi Is it possible to specify in the connection string for the SQLOLEDB-provider that you want all queries issued using this connection to be allowed to read uncommitted transactions (dirty reads) - i.e. the same as specifying the WITH (NOLOCK) hint on each query or using "SET TRANSACTION ISOLA...more >>

Hi folks! :O) I'm trying to code a stored procedure that will enumerate all the stored procedures of a database (like sp_tables), but I unable to separate the system's procedures from the user's procedures. ex : --- select o.name from sysobjects o where o.xtype = 'P' --- ...more >>

Hi, I'm in the process of taking a serious look at my mistakes <g> in designing my databases, and trying to correct them. One of the main concerns I have is with respect to primary keys. I have an application that is used in 4 locations (soon to be 7 locations) within a company. The da...more >>

Hi Sql Gurus, When I am querying the data from a remote server its working fine.. But when i am trying to insert into temp table at my end from the remote server the following error occurs. [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets] ConnectionCheckForData (CheckforData()). S...more >>

....that was just regular table data before? WTH ...more >>

--I am aware that sql server converts any outer join where a criterion is placed on the outer joined table into an inner join. This confuses a lot of our user group and I need to explain. Could someone offer the reason why --Below is a simple script showing the outer join being translated int...more >>

https://www.passports.gov.au/Web/Forms/Passport/Adult/AdultPassport_0.aspx ...more >>

I need a query that will compare results of two queries, and pull out the data that does not match. Can anyone give me some examples, or resources? Thanks, -Ben...more >>

I have detail table: CREATE TABLE dbo.Details (ID int IDENTITY (1, 1) NOT NULL, DocID int, ItemID int, Quantity int) Now I have to find all documents that have identical details sets (ItemID and Quantity) Example: Doc 1: ItemID, Quantity 1, 10 2, 5 3, 1 Doc 2: ItemID, Quantity 1, 20...more >>

Hi all, Probably a simple question. Not sure what is wrong with this query? The part: "and Risk_factors <> 'iodine allergy'" doesn't seem to be working? Still returns records with this in it. SELECT Nature, Nature2, Risk_factors, Risk_factors2, Risk_factors3 FROM tempercp WHERE ...more >>

OK... On the local server I have a table with 1,000,000 records. I am selecting 200 records from this table, and joining on an id column to a table with 2,000,000 records on the remote server. The query should only retun about 200 records. When I look at the query plan, I see that the remote q...more >>

Hi, How can I get the number of a row in a select query and put it in an field as an expression for example Alum_ID Alum_Name 356 Mark 357 Mark2 * This is the thing that I dont know how to do it * Alum_ID Alum_Name 1 | 3...more >>

Is there a way to create foreign key contstraints with additional conditions on fields in the referenced table? For example: ALTER TABLE STUDENTS ADD CONSTRAINT FK_STUDENTS_STAFF REFERENCES STAFF(TYPE) WHERE TYPE = "T" In the tables below, I would like to ensure that each student is assi...more >>

Hi, This is how the data (date values) is stored in the table. Please see below. I need to retieve only valid date values. So, in this case, the valid dates are 12011999 and 11212000. The Isdate function is not working here as it it returning 0 for all three rows. Please help me what is the ...more >>

Hello I am having an issue that I've been having for months, whereby I can't get *ANY* extended stored procedure to run on the PC other than the one I developed it on. The ones I have tested have all got exactly the same SQL server configuration (default installation of MSDE). "sp_addexten...more >>

sp_grantdbaccess ADDS 'nt authority\network service' instead of 'network service' I need to add 'network service' to this db instead of 'nt authority\network service'. I need to GRANT SELECT, INSERT, UPDATE, DELETE on 'network service', but I can only grantdbaccess for 'nt authority\network ...more >>

Hi, Can I put use any cursors inside a user-defined function? or is there any limitation using cursor in a UDF Thanks Ed...more >>

Can someone help out with creating a View? This is my table... CREATE TABLE [TableA] ( [TableAId] [int] IDENTITY (1, 1) NOT NULL , [DemoId] [int] NOT NULL , [Amt] [varchar] (10) NOT NULL , [AorB] [varchar] (1) NOT NULL ) There will be a max of two records per DemoId, one will have A...more >>

I have so far got the statement shown below. The problem I am having is that I need to alter the entry for Number Processed to be a count of sch_id where delivery_status=2. Any ideas guys? select sch_id, 'Number Processed'=count (sch_id), 'Total Number'=count(sch_id) from distribution_li...more >>

Hi All, I'm trying to create a script to remove the identity from a column in a table. I don't won't to remove the column just the identity. Please help ...more >>

Hi, I divide query optimization process to two main phases: 1) Analyzing queries and trying to write them better 2) Identifying the best indexes for queries Can we delegate the second phase completely to Index Tuning Wizard and just concentrate on rewriting queries or still it is worth to ana...more >>

Hi, I have set up an Active Directory, Certificate Services on Windows 2003 Server. I am running SQL 2000 Server. AD and Certificate Services were installed correctly. My goal is to be able to use SSL when connecting to SQL Server via Query Analyzer. I also want to keep the SQL Server insta...more >>