Can i join the same table with different aliases? Plz refer the below query.... SELECT STVendorShipToCountry.StoreVersionId AS StoreVersionId, STVendorShipToCountry.VendorId AS VendorId, STVendorShipToCountry.CountryCode AS CountryCode, PVendor.VendorName AS VendorName, P1.LocaleValue AS Co...more >>

I have a problem executing the following Stored Procedure from code. The SP Text runs like this: ALTER PROCEDURE prc_InsertContractDirectStatus­_tmp @var_ContractID VARCHAR(50) insert into tblContractAuditHistory (ContractID) VALUES (@var_ContractID) The VB code which c...more >>

Hi, I would like to create a generic user defined function as follows: check_val(table_name varchar(100), col_name varchar(100), val varchar(100)) This function will then check the "val" in "col_name" column of "table_name" table and returns 0 (not found) or 1 (found). Since, dynamic S...more >>

Hi all, I have the query below which works fine. What i am after is making the WHERE condition: 'mine_id = @mine_id' conditional using a case statement So far i have tried: CASE @mine_id WHEN NOT NULL THEN accounts.mine_id = @mine_id But have had no joy. Thanks for any hel...more >>

Starting new thread... (makes my q smaller!) :) Example; I have the following transaction BEGIN TRANSACTION --default trn isolation level here SELECT SUM(points) FROM rActivitySectionToPerson WHERE personid = 6 WAITFOR DELAY '00:00:10' INSERT INTO LineItem(personid, itemid, unitCost, quan...more >>

Hello there I just inherited a data farm and I noticed that the person before me had c2 auditing turned on. I noticed that the trc files date only back to 04/05 and that the last time a fiel has been modified is yesterday. the rate of growing out of 200mb should make these files date of onc...more >>

hi guys, I have a question.. I look at my tempdb database, i have 3 data files along with 3 log files. when I look at the data files, I have one master ( MDF ) and two NDF files. I see this when I right-clicked on tempdb database on EM, select "property", then I tried to delete one of NDF...more >>

Hi, I have the following error on XP Pro machines (all updates) using Access to run the following procedures: The aim of the routine is to access a table on SQL 2000 lock it and increment the next number. The procedues runs fine on Win2K machines but stops at about the 3969 mark in XP for...more >>

I've been through BOL and can't find this. Is there a built in function or system SP for converting from one base to another? Or do I need to write my own function for this? (specifically base 10 to base 2) Thanks, Keith ...more >>

need code that would perform the same function as the MID() command in Access used to do. Data field called: Title varchar 15 the data I want always starts with 'ABC-' and ends with '[ 1:1]' in between are a mix of numeric/alpha characters that can be 7-9 digits or characters lo...more >>

I'm not a member of sysadmin so I can't look at the server properties in EM. SELECT @@VERSION says this: Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4) ...more >>

does anyone have a link to where i can get the northwinds database? I need it for sqlserver2005. Thank's kes...more >>

Hi- I have installed SQL Server 2000 and was wondering if Visual Studio is my ticket to developing a database app with SQL Server. From what I've been finding and reading on the net, it appears that VS is good for someone not familiar with SQL. I would like to steer clear of developing wit...more >>

Hi All, I am using SQL 2000/ Visual InterDev 6.0. I am getting the above error when I use Rs.MovePrevious, Rs.MoveFirst in the following Code. SQL = "SELECT * FROM tbl ORDER BY LastName,FirstName ASC" Set Rs = Server.CreateObject("ADODB.Recordset") ...more >>

OK, so I just learned how to use the bcp command line utility to export the contents of a table to a text file. I'd like to create a script that loops through each of my talbes and exports its data into a text file with the same name. What is the best avenue to to this using SQL Server 2000? ...more >>

Hello - I have a table that current has fname, lname, address, city, state, zip and I don't expect it to ever get any larger than 700,000 rows. It will be accessed as a directory. I then thought I should include country which may produce another 700,000 tops to bring it up to less than 1....more >>

Hi, for the moment i use asp classic with Access 2003. My provider ask me to upgrade to MS Sql. Are there tools in Sql7.0 to import the Access db ? My provider speaks of opening for me a Sql Db on the server. Can i, just like i do with the Access, down- an uploading a Sql db ? Gérard...more >>

Hello all, I have a small situation here We have a well entrenched method of aliasing logins to add users. We use sp_addalias in the format of exec sp_addalias 'xxx','dbo'. My question is for this alias how do you add permissions for certain databases or to access the design venue of an exi...more >>

I have one performance question: IF I write sql like this: declare @variable bit set @variable=0 .......from table1 INNER JOIN table2 ON 1=@variable then execution time is 500 milisecond If I write like this: .......from table1 INNER JOIN table 2 ON 1=0 then execution time is 2...more >>

I have a table w/ ID number and address number. I am trying to query a specific address to see if it is in a range. Here is the example table: ID Add_Num Street 23 1600 Westwood 24 1800 Westwood I will know the ID number when I query. So say I want to know if 1653 is in the range ... wh...more >>

The following is part of the SQL auto-generated by SEM for a creating a database : IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Database1') DROP DATABASE [Database1] GO If I delete Database1, and then run the database-creation script, I receive the error: Serv...more >>

THE ENVIRONMENT................................. My dev machine: WINXP PRO, SQL SERVER 2K SP3, Sql Server User global_catalog_prod is dbo to its database The prod machine: WIN SERVER 2003, SQL SERVER 2K SP1, Sql Server User global_catalog_prod is dbo to its database In both instances I gave t...more >>

I have some events that can occur on any given day(s) of the week. The user can choose any or all days via checkboxes. In a report, I need to return the days that were chosen. But I need to do it like this: If the user chooses 2 days next to each other such as Thu, Fri: "Thursday, Friday" ...more >>

Hello, Is there anyway to ignore error in function or stored procedure while calling function for multiple orders. -- - NT...more >>

Hi, I am going to create a secondary file .ndf and place the "AccountReceivable" table into that secondary file. My question is when I do the backup "Full/Transaction/Filegroup" backup, how does it work. 1. Does the Full Backup/Differentail Backup still cover all Primary and Secondary Fi...more >>

when I try to debug my sp which takes 2 datetime params I get the following message after I try to execute the debugger [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification When I click on debug I enter 5/1/2005 for @start and 5/31/2005 for @end I also tried...more >>

Hi: I am running into trouble where my stored procedure includes a statement to truncate one of my user tables. The stored procedure fails unless the user running it has DBO permissions on the database. What is the statement to grant TRUNCATE on a table to a user without having to ope...more >>

I'm sure this is a very easy question to answer but here goes anyway. I am trying to assign a value to a variable with a select statement based on a table name variable. i.e. SET @SqlString = N'SELECT @TotDue = SUM([Due to pay]) FROM ' + @NewRenewalAnalysisbyRenType PRINT @SqlString EXE...more >>

Hello, I'm trying to write a SQL stored procedure to add/change a record in a unique-keyed table and wanted to know best SQL practice approach. I'm currently using IBM's legacy language RPG-ILE (a procedural language) for all database programming, and the steps for adding a record usually ...more >>

I am trying to fix a permission issue, we have a user who connects to a DB through an AD group. when she tries to create a proc she gets an error stating that... Server: Msg 3704, Level 16, State 1, Line 3 User does not have permission to perform this operation on procedure 'dbo.Rptblah'. ...more >>

Hi, I have a very similar request to a previous post, but did not want to hijack that one as I already use a particular method but seem to have made an error as it doesn't work. Seeing the other post, I like the sp_who2 modification idea, so may just run with that, but if anyone could look ...more >>

Hi, I'm using a highly normalized Database on SQL Server 2000 to represent entrys from many tables together in one Office WebComponent. The Problem is, changing and writing data back to the database is not possible, as I'm working on data from many different tables represented by a view wi...more >>

I'm confused with the foloowing output (see below) According to "Inside SQL Server 2000", a high value in Scan Density is good, and a low value in Extent Scan Fragmentation is also good. However, I have a high value in Scan Density and also a high value in Extent Scan Fragmentation. To make...more >>

I have the following tables Table1 Emp %Sale 1 2. Table2 Project %Sales CommissionAm A 2.2 1.5 A 2.4 1.7 Employee1 has 2.3 % sales. I have to figure out from Table 2 which commission amount to apply Because emp1 has 2.3 I need to apply $1.50 How...more >>

Hi, I have a query in a stored procedure that always returns a single record. There are about 15 columns returned also. For reporting purposes I'd like to transform the output into a recordset consisting of only 2 columns. The first being the original column name and the second being t...more >>

Hi all, I've just installed SQL Server 2005 CTP but have no idea of where to go to view objects like I can in Enterprise Manager. I have read about Management Studio but can't seem to find any program group for it. Am I missing something? How can I create tables/views/triggers and the lik...more >>

Hi I did not get any response in tools so i try this group. I'm going to specify a database maintenance plan in SQL Server Enterprise Manager, but I do not know what the best maintenance choices are. The database that shall be maintained is an alarms and events database in a process con...more >>

Hi, Is there something similar to Select case(VB.Net) in SQL Server If yes, can someone provide the syntax please else alternate ways of doing this Thanks Kiran...more >>

Hi ng, is there anyway to use the statement INSERT INTO #temp EXEC (@sql) question without creating the temporary table in advance? The number of columns selected in @sql may change, so im looking for something like SELECT * INTO #temp FROM ... thx, Michael Sander ...more >>

How can I determine IP address of the user connected to my MSSQL2000 through Win application.... 10x ...more >>

Hi all. The following query takes a long time to run. Is there any faster ways to fill inn this value into a newly created field. UPDATE Table1 SET Table1.Field1 = Table2.Field2 FROM Table1 INNER JOIN Table2 ON Table2.ID2 = Table1.ID2 There are some indexes on Table1 and about 4...more >>

Hi All, I use SQL Server 2000. Before I do some critical DB Updates I wanted to know the LIST of Users logged on to the SAME DB, so that I can Inform them to Logout from the DB before I start the Process. How Do I know the List of Users that have logged on to the DB - The List should not in...more >>

Well, "Complex" is a bit of a misnomer here, but this is the idea... If I want to select based on an element having multiple values, how can I combine this simply in the WHERE clause? The harder way is: SELECT * FROM MYTABLE WHERE ELEMENT = 'A' OR ELEMENT = 'B' OR ELEMENT = 'C' OR ELEMENT =...more >>

Hello In a table called data, I have columns dataID and value. value datatype is defined as varchar with length 20. How do I (with a "select") return the value field as a float instead of varchar? I'm guessing I need either cast or convert, but I cannot seem to make it work properly. A simpl...more >>

Hello, I have been assigned the task to design the audit trail for the ASP.NET web application. I don't know what the best practices for such audit trails are. Our application one dedicated user name and password to perform the database operations. I need to capture all the operations which are ...more >>

-- Thanks ! ...more >>

hi i had read somewhere that if i want to find out if a record exists i shoudl avoid using this syntax (@@rowcount) but i cant remember why? select * from cost if @@rowcount = 0 begin print 'doesnt exist' end ...more >>

hi how can i make this piece of code shorter? thanks if exists(select * from cost_revenue where cost_revenue_id = 80) begin print 'yes' select * from cost_revenue where cost_revenue_id = 80 end else begin print 'no' select * from cost_revenue where cost_revenue_id = 55 end dont...more >>

Hi, I have DataGrid in VB.Net loaded with SQL table and I need to print that out. Can someone please help me ...more >>