Hello Are any recomendation how can I avoid generating Lazy Spool which produces from 4k records 25 millions... :( Thank you, Tod...more >>

Hello, Thank you to oj who helped me get this far... I have one final question remaining with the OPENROWSET function... SELECT * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Text;Database=http://192.168.0.50/Texts/;HDR=YES;FMT=Delimited','SELECT * FROM TextFile1.csv') AS TableA When ...more >>

Whenever i save results in query analyser or even when i run osql and use the -o switch to output to a file, the results always appear in an unreadable format. Is there any way where i can view the entire row in one line as opposed to multiple lines.. So if i have 10 rows as the output of a q...more >>

Is it possible to use Arrays as Parameter in Sql Server 2000 Stored = Procedures instead of declaring individual parameters. I need something like this. Create Procedure myProc @myValues() Varchar(2000) As I need this because I want to use One procedure for saving data of my = all tables...more >>

Hello, This is a pretty basic question I know but I still seem to have trouble with it. What I am needing to do it load information from a .csv file into a SQL query or better still a Stored Procedure. Something to this effect: SELECT CustomerName, CustomerNumber, CustomerPhone FROM C...more >>

Can anyone point to methods for elminating duplicates? No trouble finding that I have them, using the query below, but what is the method for getting the ID to the duplicated records? Thx. select last_name, first_name, res_phone_area_code,res_phone_number, count(*) as Instances from p...more >>

Hi There, I am working on a remote SQL Server instance and I wanted to know if I can email the results of a query in html, I would have use xp_sendmail but the administrator wont allow it. I can get the package to work by sending an attached document no problem, being new to SQL Server I am n...more >>

Hello, I have 3 tables, a master (Tab1) with 2 children (Tab2 & Tab3). The child tables have numeric values that I am summing for report purposes. When I query Tab1-Tab2 my Tab2 sums are correct, when I query Tab1-Tab3, my Tab3 sums are correct. But when I query Tab1 - Tab2 - Tab3, my sums ...more >>

hi i started a restore operation in a simple test db, it doesnt stop, i restore the db in another one (with a different name) and it ended ok, i need to stop the first restore i did because i need to use the db, how can i so??? please i really need it carlo ps sorry for my english i hope yo...more >>

Hi i started a restore on a little db, it is still running (more than 15 mins), how can i understand what is it doing??? Carlo ...more >>

Heres a problem Im having trying to build a sql query. Table data: [LOT], [TransType], [Condition] R-1111, Production, SALES R-4444, Production, MOVE R-4444, Production, MOVE R-4444, Production, SALES R-1111, Production, SALES R-8888, Production, MOVE R-8888, Sales, TIME R-1111, Producti...more >>

All, I know that it's easy to grab data from one table and write to another one just in one sql string like: INSERT INTO tblTable1 (f1, f2, f3, LastUpdate) (SELECT f1, f2, f3, FROM tblTable2, GETDATE()); We discussed that a couple days ago, it works fine. Does anybody know how can we n...more >>

I have a table similar to this structure tbl (Group int, Value1 money, Value2 money, Value3 money, Value4 money, Value5 money, Value6 money, Value7 money, Value8 money) Column Group is NOT NULL, others are nullable. There are about 20,000 records in this table with 1,000 distinct group valu...more >>

David Portas and oj both thought it would be usefull if I posted ddl for table, function and view: I've got a table where I want to flag if the record is the most current "approved" Return 1, 2 if old approved, and 0 if not yet approved. I wrote this scalar function but an not able to use i...more >>

I have a web based application that launches sql queries using ADO from ASP/VBScript. Typically 2 - 10 queries are executed in a loop, and the result sets are sent off to Crystal Reports at the end of the run with the report being displayed in a browser window. In general this all works well is ...more >>

-- Here's a table with 1,655,509 records: CREATE TABLE AlternateCompanyLocation ( AlternateCompanyLocationKey INT IDENTITY (1, 1) NOT NULL, CompanyLocationKey INT NULL , AlternateName NVARCHAR(80) NULL, AlternateAddress1 NVARCHAR(50) NULL, AlternateCityKey INT NULL, AlternateCountryKey...more >>

I have been transfering a database to another and one of my quires keeps failing. This is the first try and the result I got in nquery anylizer, then I show the second with the corections however I don't get why I am getting either error. INSERT INTO Users (UserName, Password, Email, EmailI...more >>

I've got a table where I want to flag if the record is the most current "approved" Return 1, 2 if old approved, and 0 if not yet approved. I wrote this scalar function but an not able to use it in a view. I get the following error message: "[Microsoft][ODBC SQL Server Driver][SQL Server]Sub...more >>

Hi all, Just a quickie, can you only have ONE output parameter with a SP or can you have more than one? for example, after some help from some 'friendlies' in this group I have a stored procedure now returning a value I need, however, it would be even better if it could return TWO values. ...more >>

Hi all, need some advise on the best way to do something, i have written a script that i want to execute from an application that i am writting using ado.net. the script is below i know that i need to change the script to pass the info back to the progam, but what should i set this script as...more >>

Can anyone explain why am I getting the error: "Error 156: Incorrect syntax near the keyword ‘IN'" when attempting to define the following procedure? Thanks in advance… ---------------------------------------------------- CREATE PROCEDURE Test ( @I_0 bit, @I_1 bit, @I_...more >>

I made this tables: Users: Mov: Comis Cod_ven---------------->Cod_ven---------->Cod_ven cod_grp---------->Cod_grp cod_art comis_per cant comis I want to make the UPDATE to c...more >>

Hi, I'm about to write a user defined function that returns a table. In this table, I'd like to use a user defined type, but I'll get an errorcode telling me something about not being able to find that type (sorry, using German version, so I don't have the exact wording in English). Am I mi...more >>

Hi, I have to update one row on 10 million rows (all the rows) from a table. I need to set it to null. I've tried simply to run update mytable set mycol = null which took more than 10 minutes ( I stopped it ). I have a clustered index using another column + this column. Is there another ...more >>

Please help me simplify the steps listed below. To start the process of saving to the permament table I run steps 2 and steps 4. Next, I run a job to excecute steps (3-6) every 5 minutes. Please help me simplify this processes. Thanks, Mark -- Step 2 INSERT INTO #TmpSyspro...more >>

Hi, I am having a problem with a stored procedure that looks in an order details table and searchs for a serial number to see if it exists and if it does it returns the serial number. The calling code is using C# ADO.Net ExecuteScalar function. I have tested and it has been running for quite...more >>

Can anyone explain why this happens. I figured out how to resolve the error but I would like to know why it happens. I receive the following message when trying to insert into a partitioned view. 'Server: Msg 4436, Level 16, State 12, Line 1 UNION ALL view 'Bill_Details_Rn_tempview' is...more >>

Hi all, I have a value that is returned by a stored procedure, ie the last lines of the stored procedure are : SELECT @value RETURN So - now we're in the parent stored procedure - its just executed the other one (above) - and now I want to use the value returned - how do I do this?? I t...more >>

Hi all, I am trying to denormalize a varchar column but am uncertain on how i.e. ID Attr_Name Attr_Value 1 Product Line 1 Region California 1 Channel Internet To look like ID Product Region Channel 1 Line Califor...more >>

Hey all, I am still not very good with Action queries yet... What I need is to copy two tables: 1) Table A and B in Database GMS, Copy/Create to table C AND D in Database GMS 2) Table A and B in Database GMS, Copy/Create to table A and B in Database GMSBAK 3) Table A and B in Da...more >>

I am trying to create a select statement that filters the data on if a variable is not null I tried this but it doesn't work. Any ideas for help would be great. DECLARE @SalesPerson nvarchar(255) set @SalesPerson = 'Sales User1' select * from sometable where CASE WHEN @SalesPerson ...more >>

I'm trying to create this association table that will allow a user to have many phone numbers and only one of the phone numbers can be flagged as being the primary number. To do this I created multi column primary key using (UserID, PhoneNumberID). I tried to create a unique index on (UserID...more >>

Hi, I want to setup an instead of trigger with a table that has some Image fields. My insteadof trigger is designed to perform additional validation on user's others column input and change them on the fly, this works great for tables that has no Image or Text columns. But, since we can't ...more >>

Use Northwind GO set statistics profile on go --query0 select CustomerID, ContactName from Customers c join Orders o on c.customerID = o.customerID where OrderID = 10248 --query1 select CustomerID, ( select ContactName from Customers where CustomerID = b.Cust...more >>

Hi, Is there a way that you can check whether a server is online on the network using SQL? Thanks...more >>

howdy, I had to create a linked server between a proprietary server and Sql Server (2k). I ran sp_addlinkedserver and sp_addlinkedsrvlogin in Query analyzer per the instructions that came with the proprietary server. This ran fine and I can see the linked server in Enterprise Manager ...more >>

Hi all, I have a scenario where I have an ID for a row of data, that row also has a column (MergedTo) which 'could' hold the ID of another row which is now the relevant row. What I need to do is based upon a given ID check to see if its the relevant one (based on an 'active' column value) ...more >>

Can permissions be granted by script on ALL user tables? Thanks soc. e.g. GRANT SELECT ON ("all usertables") TO user1 GO ...more >>

Hi group, I have two stored procedures coming from Access database. First: Alter Procedure qryCombineCodes ( @txtStart datetime, @txtEnd datetime ) As SELECT [tblActual_1].ID, [tblActual_1].Emp_Name, [tblActual_1].PAYCODE_1 AS PAYCODE, Sum([tblActual_1].PAYCODE_1HOUR) AS SumOfP...more >>

Hello, in SQL Server 2000 Enterprise manager is there a way to hide system tables from the tables view? I only need user tables to be displayed, what settings and where do I have to set? Thanks, TECH http://www.tversoft.com ...more >>

Can I do a select query on a table and refer to the columns by ordinal position rather than name? Mia J. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...more >>

Does anyone know why I am getting this error: 'Server: Msg 4436, Level 16, State 12, Line 1 UNION ALL view 'Bill_Details_Rn_tempview' is not updatable because a partitioning column was not found' when trying to insert into the tables thru the view. Below is the sql for the insert, the ...more >>

The optimizer is doing table scan instead of using an index.If I directly use the dates instead of using variables than optimiser is using index.Is there a bug? I am using sql server 6.5.Any help would be greatly appreciated.Index info is at the bottom of the page /**********Query**********...more >>

Hi Is this possible and how? Ian ...more >>

Hi, This is a problem that I keep hitting my head against - and am very interested to see what anyone's opinion is. For performance reasons - and because of the complexity in the underlying table structures I have ended up writing a lot of code which concatenates multiple record strings int...more >>

Dear All Say if I had a column called Surname that has a non clustered index If I put WHERE Surname Like 'Jones' would I lose the benefit of the index. If I put WHERE Surname Like 'Jone%' would I ... And would I get the same index issues with a Clustered index ? Thanks Jum ...more >>

Hi, In my stored procedure there are 2 rows: INSERT INTO TABLE COUNT TABLE The problem is when Multi-Threads invoke the stored procedure, I need that in each thread the COUNT instraction will return different value without locking the table! Is there a way to Lock and unlock just th...more >>

We have a table that consists of a user name and their password hash. Our login query is a stored procedure that selects from user where the username and password hash are equal to the ones passed in. This stored procedure is creating a lock on the table that stops people from changing their...more >>

I have an application using SQLDMO and C#. The application will always use windows authentication to sql servers. The problems is why does an account which is connected through sqldmo can stop/start/pause a server in which that account has no privilege in stopping/starting....the server......more >>

Hi, i've got the following message poping up: Server: Msg 9002, Level 17, State 6, Line 1 The log file for database 'DM' is full. Back up the transaction log for the database to free up some log space. Now I looked in the online books and it was going on about detaching moving and reat...more >>

Can anybody give me any pointers on doing the following please: On every record in a table, I need to split an address field (ntext) using the CR as the delimiter and populate the address1, address2, address3 etc. fields with the data from the original address field. Version: SQL Server 20...more >>

I created a stored procedure like this: CREATE PROCEDURE SP AS BEGIN CREATE TABLE #T( F INT) INSERT INTO #T(F) VALUES (1) SELECT * FROM #T END When I call it this way: EXEC SP, it works ok. But when I do it like this: SELECT * FROM OPENQUERY( MYSERVER, 'EXEC SP') I receive an ...more >>

My sql requirement is to show all the employees with the names of their department, location and employment status The 4 tables I have are as follows Employees eid ename DeptID LocationID StatusID 1 anand 3 1 3 2 sagar 4 3 2 3 ashok 3 4 2 4 anil 2 2 1 5 ajay 1 5 2 6...more >>