How can I return a Scalar value by calling PROCEDURE 2 from PROCEDURE 1. Something Like: SET @nextDataVersion = EXEC(pr_GetNextDataVersion 2) (this does not work!) ...more >>

Hi, This stored procedure isn't working and I'm not sure why. ANy suggestions would be greatly appreciated. CREATE PROCEDURE dbo.[proc_update_urn] @case_id as int, @service_area varchar(3) AS begin declare @max_id int set @max_id = (select max(sd_s_id) from tbl_surveillanc...more >>

hi! i need (preferably 0:)) short explanation about difference between declare cursor and declare cursor *with hold*. if i'm working in multiuser regime, and need to pay attention to locks, do i *have* to use declare with hold? -- cvetic ----------------------------------------------...more >>

Is it not possible to use UPDATE within a function? I get the following = error message for the following function: Evan - - - - - - - - - - - - - - Server: Msg 443, Level 16, State 2, Procedure fn_GetNextDataVersion, = Line 10 Invalid use of 'UPDATE' within a function. CREATE FU...more >>

Hi, I have both NT authentication on the two servers, and I'm getting the following error when I try to execute the statement: select * from Main1a.GondorMain.dbo.bds Error Message: Server: Msg 18452, Level 14, State 1, Line 1 Login failed for user '(null)'. Reason: Not associated with a ...more >>

I have the following query that works fine when run manually, but I don't know how to create a view of the equivalent data. When I uncomment the CREATE VIEW it errors out. --CREATE VIEW vwCPA --AS DECLARE @TypeA nchar(10) DECLARE @TypeP nchar(10) DECLARE @TypeC nchar(10) SET @TypeA=...more >>

DELETE ttt FROM #TaxIT3bFinal ttt INNER JOIN ( SELECT cli_code , ProductDesc FROM #TaxIT3bFinal (NOLOCK) WHERE fundDesc <> 'N/A' GROUP BY cli_code , ProductDesc HAVING COUNT(*) >=1) AS xxx ON xxx..cli_code = ttt.cli_code AND xxx.ProductDesc= ttt.ProductDesc WHERE fundDesc = 'N/A...more >>

Hi! I am new to this group, but I am running into a problem that I cannot seem to find a solution for. Table: The table is an Employee Health Table. The field Last_PPD is a char type because the Nurses would like to put NA, or NC, or None in the field. Objective: I need to convert Last_PPD...more >>

The company I'm contracting at has a guideline that table columns should be of type char if less than 20 characters, otherwise varchar. This guideline was just changed to a requirement. In my opinion, the choice between char and varchar should consider variability of data size as well as need ...more >>

Hi All, I have a problem storing a binary image coming from an xml file. The image has 39,000 characters. I my table I define the field as image but only store 0x. What is the problem? I try to store as text and doesn't work either, if I try varchar it works but store part of the length. How ...more >>

Hello all: I have an application that uses threading to access my SQL Server 2000 database. It can produce, at most, 20 threads at a time. Yesterday and this morning, I was happily testing this app and it performed beautifully. Monitoring the threads via Enterprise Manager, Current Activity, ...more >>

Hi Is there a way to detect any instances of sql server on the network. Like all the Microsoft configuration tools do... If there exists one, is it also possible to use it within the .net environment? Regards Stephan...more >>

I am using this Query in application , its working fine with varchar column types when i use COLLATE in QUERY but its not working for Datetime columns types, any suggestions? SELECT TOP 10 * FROM #TempExt WHERE eFolderID NOT IN (SELECT TOP 20 eFolderID FROM #TempExt ORDER BY service_reque...more >>

In SQL Server 2000: I have two tables. The first has user information in the form: UserID | Fname | Lname | State | Zip 37 | Jim | Jones | NY | 19040 38 | Tom | Fredrick | PA | 88888 39 | Bill | Thomson | CA | 99999 The second has some survey information in the form: UserId ...more >>

i am using the below code but its not working .. what could be the reason? CHARINDEX(@SPName,@DateAndIntCols) to CHARINDEX function i am passing to variables, cant i pass variables to CHARINDEX?...more >>

I'm actually stuck! Can't believe it... so I'd appreciate some help. What I'm doing is enabling users of an ASP.NET 1.1 Web application to update a table in a SQL Server 2000 database. To make their life easier and to automate the enforcement of validation rules, I'm enabling users to maintai...more >>

I'd like get some opinions on this matter before I go Godzilla in the next meeting. My application fills a database during the day, while a webservice reads from the data. At the end of the day I update statistics, defrag indexes, do a shrink file and make it read only. Then I create a ne...more >>

Hello, I have a vehicle database 100,000 records with Year, Make, Model, Body,Vin, SRP text fields. What I would like to do, is delete duplicates records with the lowest SRP field, example below. Looking for any possibilities with Access or MS SQL Server. 91 Ford F-150 Truck...more >>

I have the following SQL query: "SELECT Fname AS First, Lname AS Last, Email, Address, City, State, Zip, " + "Company, Phone, users_surveys.Datefield as Date, " + "CASE WHEN questionID = 1 THEN answer ELSE NULL END AS Reseller, " + "CASE WHEN questionID = 2 THEN answer ELSE...more >>

In query analyzer I wrote the code below. (Initially minus the debugging lines.) I had no problem running the create script. Then I went to debug with the TSQL Debugger. No matter how I tweaked, the debugger would only insert to the base table, G_Survey, not the join table. Bordering on...more >>

Hi, I had a table in my database which will update every month ... so we used to update the table every month and stored the archieve tables in a seperate database. --ID is the primary key for this table and all historical of the record will have the same ID Now I have to combine all ...more >>

I am scripting a stored procedure and I need to change the perspective to a database that was just created in the script...kind of like this: use master go @db varchar(50); begin create database @db go use @db -- DOESNT WORK How do I script this?? Thanks, Ron ...more >>

What is this statement missing ? SELECT id FROM Cases WHERE Cases.tempcaselogid = CaseComments.tempcaselogid I get this error: Server: Msg 107, Level 16, State 3, Line 1 The column prefix 'CaseComments' does not match with a table name or alias name used in the query. I need to use "...more >>

When appending data from one table to another... If the structure is the same, do you need to list all fields in the query insert into t1 ( f1, f2, f3 etc..) Select f1, f2, f3 etc from t2 Thanks in advance, Bob. ...more >>

We have a Result table with fields called QuestionID and Answer. How can I get a query result where the result is group by the answer and its percentage ? Thanks. For ex: Results table: QuestionID Answer 1 aaa 1 aaa 1 ddd 2 ...more >>

I have the following task, doing it by hand would take just an increidible amount of time, perhaps a query can be done to update an entire table ? This is the scenario. Table A has a field called "UserId", this will be my source "Id" Table B has a field called "OlduserID", this is the "Id" t...more >>

I have patient data - am interested in patients receiving a certain diagnostic procedure. Need to find out on an average in the group of patients I am analyzing, on which visit they receive this procedure. Suppose there is a consumer X, who visits doc A - on the 5th visit the doc administers ...more >>

I'm not a coder type (yet) and well I'm trying to use Enterprise Manger to produce a report from 2 tables. Bottom line is I'd like to return a row for only the most recent date. Does anyone have some example code for selecting the most recent date? Thanks in advance! ...more >>

Hi All, I have a problem storing a binary image coming from an xml file. The image has 39,000 characters. I my table I define the field as image but only store 0x. What is the problem? I try to store as text and doesn't work either, if I try varchar it works but store part of the length. How...more >>

I've got data that was imported from an Access database into a SQL database. I've found the import didn't populate one field it should have, so would like to now update that field by linking the two DB's and running an update statement. Can someone help point me in the right direction? Thank...more >>

How can I pass random amount of params in a SP? ...more >>

I am receiving following error while doing merge replication between sql2k5 sql servers. Can any one suggest what iam doing wrong. TIA Message 2005-12-01 17:33:01.528 [26%] [0 sec remaining] Applied script 'Customer_2.cft' 2005-12-01 17:33:01.731 {call sp_MSsetconflicttable (N'Customer', N...more >>

I have been trying to query the Top 100 selling items for each category to be one result set...all day. I thought that this should work... SELECT TOP 100 productid, categoryid, sum(sales) as [Sales] FROM dbo.Sales WHERE categoryid ='01' GROUP BY productid ORDER BY sum(sales) DESC UNION S...more >>

I'm having a dickins of a time with a particular query and am hoping someone here can help me. Using the following example; declare @SearchDate datetime set @SearchDate = '30 Nov 2005' declare @t1 table (t1id int, t1desc varchar(10)) insert into @t1 (t1id, t1desc) values (1, 'Ed') inser...more >>

I have created a couple of new foreign key relationship on my table using enterprise manager. I want to script these changes and send it to my client so that they can update the relationships on their end. how can I do it? ...more >>

I'm in the process of encrypting my database and need to add a new column and then populate it w/an encryped string from an existing column. While I've begun to do this in my testing environment, I've noticed my log file to grow substantially. In fact, the following was logged on my test db:...more >>

Is there a way to flow updates only to an Access mdb from SQL DTS. We have a sql table that is a copy of an Access table, all adds/updates/deletes are made to the sql table and replicated to the Access table. The problem is the only options for replicating the data to the Access table are a....more >>

Hello, I need to facilitate updating a data warehouse table with a DTS package that updates an accounting table for premium amounts. I will do a one time run of all the accounting records and after that would like to 'grab' just the previous 2 months worth of data (on a nightly run, so tha...more >>

I have an update statement as follow: UPDATE T2 SET T2.field1 = T1.field1, T2.field2 =T1.field2 FROM table1 T1 JOIN table2 T2 ON T2.TAG_ID = T1.TAG_ID WHERE T1.ERROR_STAT <> 1 AND (T2.field1 <> T1.field1 OR T2.fiel...more >>

I have 2 tables with 500K and 1Million records and need TRUNCATED/LOADED daily. I cannot truncate thru a link server. Ex. TRUNCATE TABLE linkedServer.DBName.dbo.tablename I am forced to use a DELETE command, but it takes too long. Does anybody have a better idea or a way to truncate a table...more >>

Can anyone point me to script to delete all objects owned by a user before I delete the user from a database? Thanks. -- Michael Tissington http://www.oaklodge.com http://www.tabtag.com ...more >>

Even though this is not a programming question but I thought that this is the place where it makes sense to ask. I am looking into a small storage solution for SQL Server 2000. Storage can be up to 1 TB of usable space. Company is not looking into investing big dollars into big SAN solution...more >>

Has SQL-DMO been updated for SQLExpress?. I read that SQL-DMO was backward compatible.. does it mean, I would have to redistribute sqldmo for SQLExpress ??.. TIA....more >>

Since the "God of DB design" spoke to me on this forum yesterday, it got me wondering if theres a better way to accomplish my goals. I work in a place that has been using mainframes since the beginning of time. Unfortunately when they imported a particualr DB into MSSQL, they didnt change the ...more >>

I am trying to create the Temp table using dynamic SQL and referring that temp Table outside the Dynamic SQL , but the code the not recognising the Temp table ...my code is like this... SET @sTempSQL = 'CREATE TABLE #Temp(' + @ColumnDef + ')' EXECute sp_executesql @sTempSQL SELECT * FROM #Te...more >>

i have table and in that i have number of records which are duplicated. i want to delete the duplicate records from this table. pls help me. ...more >>

Hy people, i hava a problem with add a SP with a tempory table inside in a SQLDataAdapter in VS .NET 2003. Is this the point of sintax that occur the error: (...) if object_id('tempdb.dbo.#tempprod') is not null drop table #tempprod create table #tempprod (seq int, sit varchar(6),Atividad...more >>

This is my resultset Value Name 1 A 101 A 2 B 10 B 70 B But I want it in this way from SQL Server A B 1 2 101 10 70 ...more >>

Hi We are using MSDE and VB.Net for our development. We have written an Update Query which returns -1 as No. of Rows Affected (in some Forms only) remaining it will return the actual no. of affected rows. Its a Valid Update Statement and it runs in the Query Analyzer. I n...more >>

I have heard that for updating a view we need to use "InsteadOf" trigger in SQL 2k. Can any one give me a sample for this. i.e., I just want to know the way to use InsteadOf triggers for updating a view. Regards Pradeep...more >>

I have a DTS that import from Excel in SQL server table some records. If i run the DTS from EM all OK! I want to run the DTS in a Stored Procedure with this command: EXEC MASTER.DBO.XP_CMDSHELL "DTSRUN /S SERVER1 /U sa /P password /N UPKG_IMPORT_EXCEL I have an error -2147467259 Failur...more >>

hi, In User Stored Procedure, can i send tablename as a parameter. Thanx ...more >>

Hi, can anyone tell me how to update identitiy values. i have table with identity column and i have to update some of the rows and make their identity value different. -- Sanjay...more >>

Guys, I have a table with following data Table Name :TblRecursive Sample records: ID, Name, ParentID 1, A, 0 2, B, 1 3, C, 2 4, D, 2 5, E, 1 Using the above data I just want to generate a result as below A A\B A\B\C A\B\D A\E Can you help in writing a query for this? Reg...more >>

Hi, This works: INSERT INTO test (valueField) VALUES (1); This does NOT: INSERT INTO test (valueField) VALUES (1),(2),(3); Why and how do i make it work? i want to insert multiple records with one insert statement.. if i insert each one seperately then it takes too long.. i guess i...more >>

Here’s a story. I’m in a warehouse and I’m to normalize 8M records The employee dimesion is composed of the following fields Employee_key, employee_no, center_id, date_hired and other fields What I want is to 1. Select disctint employee_no, centerid, datehired Plus 2. the “top...more >>

Due to being overworked, underpaid and generally hopeless anyway when it comes to scripting I find myself seeking the advice of other smarter beings: I'm trying to create a job that automatically transfers PC asset info into my Helpdesk database. My source table includes the usual 'Make', 'Man...more >>

hi pals, I would like to ge the output parameter using ODBC API, say SQLExecDirect("EXEC SP_New") . How could I get the output parameter value from my application? note: forgive me if this group is not a relevant one. Thanks and Regards, shahul. ...more >>

I have a couple of integers m=5 and y=2005 where m can equal 1 to 12 I want to concatenate these to 2005/05/01, 2005/11/01 etc Covert(varchar(4),y) + '/' + convert(varchar(2),m) + '/01' how do i get the leading 0 before the 5 Thanks in anticipation ...more >>

Does this: SELECT a.ID FROM MyTable a LEFT JOIN dory_notification b ON a.ID_Account = b.ID_Account WHERE a.ID_Account = 10216744 AND a.ID_Contract = b.ID_Contract equals to this: SELECT a.ID FROM MyTable a LEFT JOIN dory_notification b ON a.ID_Account = b.ID_Account AND a.ID_Contract...more >>

Hello. I've a problem Whit a SQL Server 2005 query. I've two tables Carico and Scarico that are: - Id - IdProdotto - Data - Quantita. I want to know the giacency. Then my query is: SELECT Sum(Tot) FROM (SELECT Sum(Carico.Quantita) as Tot FROM Carico WHERE (IdProdotto = 2) UNION SELE...more >>