I am trying to run a stored procedure but I get the following error: Server: Msg 248, Level 16, State 1, Procedure sp_MarriageLookup, Line 34 The conversion of the varchar value '2820103430' overflowed an int column. Maximum integer value exceeded. Stored Procedure: Unifirst919.dbo.sp_Mar...more >>

Need suggestion pls. Which one of the following statements regarding Data Transformation Service packages is FALSE? Choice 1 Packages can be scheduled to run unattended. Choice 2 Packages can import Oracle's native database structures to SQL Server. Choice 3 Packages c...more >>

Hi all Is it advisible to have a trigger in the system table? I need to keep a trigger in the SYSJOBHISTORY of msdb. I need some sugesstion. Thanks in advance Anand....more >>

I need a little help, I have written this SQL query - CREATE OR REPLACE VIEW PRODUCT_COUNT AS SELECT SUM(f.CUSTOMER_COUNT) NUMBER_OF_CUSTOMERS, t.DATEIN, f.PRODUCT_KEY, p.FULL_DESCRIPTION FROM TIME t, SALESFACT f, Product p WHERE t.TIME_KEY = f.TIME_KEY AND f.PRODUCT_KEY = p.PRODUCT_KEY ...more >>

Which component coordinates the 2 Phase Commit process? Thanks. ...more >>

So my ADO.NET application (using OleDb provider) sometimes has query timeouts on INSERTs and the only way to get it working again is to reboot the computer. This happens very infrequently, typically weeks or months apart. I'm wondering if this is happening when the database is increased in s...more >>

I'm trying to understand how SQL Server implements Dynamic SQL. I would think that dynamic SQL is SQL generated on the fly in a client application and then passed to SQL Server through OLEDB as a string for execution. Is this what it is or is it something that can be implemented in T-SQL? Plea...more >>

As a general rule, which one of the following should the columns participating in a nonclustered index exhibit? Choice 1 They must be used in retrievals based on an exact value match. Choice 2 A foreign key column in a decision support system Choice 3 They must be the tar...more >>

What effect do Savepoints have on a situation in which a distributed query fails when attempting to modify data on multiple servers? Choice 1 Savepoints on all the OLE DB compliant databases are applied, but data on other types of remote servers are rolled back. Choice 2 Save...more >>

Which one of the following cursor types is a Fast Forward cursor converted to if remote tables on linked servers are referenced in its SELECT statement? Choice 1 Dynamic Choice 2 Disconnected Choice 3 Keyset Choice 4 Forward_Only Choice 5 Static ...more >>

Hi this is one of the question in a certification exam: Dim oConn Dim sSQL Set oConn = CreateObject("ADODB.Connection") oConn.open "Provider=SQLOLEDB.1;Data Source=dwserver; InitialCatalog=somedb;user id=sa;password=xxx;" sSQL = "sp_DoSomeWork" oConn.execut...more >>

Which SQL Server 2000 tool contain the "Transact-SQL Debugger?" Choice 1 Query Analyzer Choice 2 Profiler Troubleshooter Choice 3 Enterprise Manager Choice 4 isql Choice 5 SQL Profiler Thanks a lot. ...more >>

Hi, I am new to SQL server and unsure of the best way to build output from multiple tables and ouput with a single procedure. The requirement is to build a single porcedure which combines data from 2 tables which are in a one to many relationship. The output requires matching records in the m...more >>

Hi all. I apologize in advance if this a newbie question but my SQL expertise is only a little bit better than rudimentary. I have several columns in several different tables in a SQL Server 2K database that I want to rename. All of these columns have data in them so its not as simple as d...more >>

I need to UPDATE the [ShippingMethod] field. I can't get the syntax correct. Can someone give me a clue here? thanks! UPDATE Orders SET ShippingMethod = CASE WHEN 'UPS Ground (No Saturdays)' THEN SET ShippingMethod = 'UPS' END WHEN 'UPS 2nd Day (No Saturdays)' THEN SET ...more >>

I have two tables (DDL included at end) that contain the following rows... ord table ordid paydate amt ----------- ------------ ----------- 123 1/2/04 100 123 1/5/04 -10 124 1/6/04 80 125 1/8/04 200 125 1/8/04 ...more >>

On Sql server 7.0... I'm running a query against a table that has the field "patient_age". Data: Patient_age varchar nulls allowed. When a patient is between birth and 30 days old, the data will be '0D' to '30D' Then '4W' to '11W' Then '3M' to '11M' Then '1' to 'whatever' The table ha...more >>

Hi all, we have a #table with more than 10,000 record. we have two solutions for catch duplicate #key (#key is primary key field) error when update or insert solution 1: using select first then using insert: Create procedure dbo.InsertValue @errorcode int output, @key int, ...., .... a...more >>

I have two tables in my DB for an e-commerce app. This allows for 1 order to have multiple products. I want to select the OrderID from the Orders table but only when all the corresponding records in the OrderDetails table all have their Despatched value = 0. Can anyone offer any help please? Sim...more >>

I am facing the following error.Anybody knows the reason...? SqlDumpExceptionHandler: Process 223 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. ...more >>

the one of the records fields Little example Table ID (int, auto indentity(0, 1) Name varchar(30 My SP takes in the Name value as a parameter, but I would like the stored procedure to return the ID field if the record was added and return -1 if not. Thanks...more >>

I am using SQL2K as backend and vfp6 as frontend. How can I save my selection of records into cursor and then make it available in my layout's data environment ? Any suggestion ? Thx ...more >>

is there a way to add a column to a table using a variable as the column name? as I would like to add a couple of columns with the same prefix eg set @species = 'cod' alter table #totlen ADD @species +'wt' decimal(10,2) null alter table #totlen ADD @species +'len' decimal(10,2) null Than...more >>

Hello, I would like to do something like this but this doesn't work. SELECT userName, ( SELECT avg(sales) as avgSale FROM sales WHERE sales.userID = users.uersID ) FROM users WHERE age > 42 Result should be: userName avgSale Mike 3999 Tony 200 Th...more >>

Hi, I have a Stored Procedure that uses the OPENROWSET command. Everything runs fine when the Stored Procedure is run via SQL Query Anaylser however when the same command is executed via ADO (on an ASP page) the following error occours:- Microsoft OLE DB Provider for SQL Server error '800...more >>

Sql Server doesn`t allow use of CURRENT_TIMESTAMP from within a user-defined function. It does allow this in a stored procedure. Is there somethin I àm missing or is it a bug ? Here is a sample (a very simple and dumb one) function where this problem appears: CREATE FUNCTION yesterday() RE...more >>

Hello, I am using insert to insert a record into a database, and when I run a select * query I am expecting (and requiring) this record to be the last record in the result, but it is going into the top part. What can I do to control this? ...more >>

I would like to store mailing-list preferences in an Int field using bitwise operators, but I'm having a bit of difficultly. The field is named 'EmailFlags'. The values for the lists are: 1=ListA, 2=ListB and 4=ListC. Questions: 1). If I want to set the preference value to include listb...more >>

Hi, folks My friend was asking by interviewer the next question. There is GUID that users will be able to choose company/or few comanies , then from combobox will be selected one of these comparisons (<,>,>=,<=,like) and then one more parameter let me say like age . The interviewer asked...more >>

I would like to include the following SQL statement in a stored procedure - but with an integer parameter/variable passed to the TOP clause (in place of 5 in the code below). SELECT TOP 5 * FROM products WHERE productid NOT IN (SELECT TOP 5 productid FROM products ORDER BY productid) ORDER...more >>

I have an ASP page that uses vbscript running against a SQL Server 2000 database (version 8.00.818). This procedure creates several temporary tables, all of which were working fine until I added the most recent temporary table. The code that creates this table is: SQLQuerySelect = "select ...more >>

Hi I have some experince with ASP and databases in General, however Stored Procedures are new. I need to call a stored procedure and have bene told I need to to the following: declare @gmnv varchar(20) EXEC GMW_NV_Create @gmnv OUTPUT EXEC GMW_NV_SetValue @gmnv, 'contact', 'CURE' ...more >>

Why doesn't it work? =========================== USE Northwind exec master.dbo.sp_executesql N'select top 1 * from @tabella', N'@tabella varchar(20)', @tabella = 'Customers' =========================== How can I passtha param @tabella in my statement to pass it to sp_executesql? ...more >>

I'd like to know if there a script that would allow me to find out what database an object (table, stored proc) belongs to....more >>

Quick and simple. I am running SQL2K on Win2K. I'm pretty sure the answer is no, but... Can you do a search against a table without bringing it in through a recordset and using the .find method. I.E. Do Until rst.EOF rst.Find "ID = ABC" rst.MoveNext Loop I understand ...more >>

Hi everybody, Is there a way of viewing more than 255 bytes in the enterprise manager or the query analyzer of the last TSQL Command batch executed? Thank's in advance. - Richard *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded f...more >>

How do you open a specific type of cursor rather than accepting the default type which I think is dynamic? Please give an example. Thanks! ...more >>

When viewing the job history in sql server 2000, after you click on the step ID, log data is displayed in a window at the bottom of the GUI screen displaying the error message. I am raising a custom error message via a T-SQL statement using RAISE ERROR to write to this window. This works for j...more >>

I was wondering if anyone knew of a good TSQL site or class for more advance development. I was looking specifically for something that does something along these lines; 1: Shows the tables involved. 2: Shows the desired output. 3: Leaves it up to you to figure it out. Any ideas??? TI...more >>

id int identity primary key, EmployeeID int, TranDate datetime, TranCategory char(3 Table includes many different entries for each EmployeeID, and can be from any number of categories. I'm trying to write a query to get the newest entry for each EmployeeID where the TranCategory = Any help app...more >>

Hello ALL, I have some procedures in SQL and them will insert data in a Oracles tables, using Linked Server. When I try to execute one of my stored procedures, I got this error: Server: Msg 7391, Level 16, State 1, Line 1 The operation could not be performed because the OLE DB provider 'MSDAO...more >>

I use the "CONVERT(DATETIME, Col1, 101) AS Col1" to convert the source string data and that is how my dateype is showing in my sql server environment: 2002-11-19 00:00:00.000 Question 1: How can I have this showing the actual time instead of only zeros? Question 2: How can I have showin...more >>

Hoping someone can help me with this problem. I'm using ado to connect to a SQL Server database. I recently changed my connection string from : var m_sDSN = "Data Source=mydsn;User ID=user;Password=pw"; to var m_sDSN = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=...more >>

MSSQL 2000 SP3 and Im trying to add a column to a temporary table as the real table is huge SELECT DataT.* INTO #ptable FROM DataT ALTER TABLE #ptable ADD NEWCOLUMN int UPDATE #ptable SET #ptable.NEWCOLUMN = 1 The update reports back Server: Msg 207, Level 16, State 1, Line 3 Invalid...more >>

I'm struggling with understanding how a SP ROLLBACK will happen when a trigger fails. If in the calling SP is wrapped in a transaction and a simple SQL call is made that envokes a trigger - but the trigger fails - will the calling object be aware that the trigger failed or do I have to build l...more >>

Hello ALL, I have this error code. DECLARE @SQL VARCHAR(1000) DECLARE @INC_LINHA INT SET @SQL = '(SELECT MAX(LINHA) FROM ''' + @TOTAL_PATH + ''')' SET @INC_LINHA = EXEC(@SQL) IF @INC_LINHA IS NULL OR @INC_LINHA = '' SET @INC_LINHA = 1 ELSE SET @INC_LINHA = @INC_LINHA + 1 So, I need t...more >>

Hi there, Sorry for not posting any DLL, the example is too simple. Suppose I've got a single table with two columns, Number and Item: Number, Item 1, null 1, null 2, null 2, null 2, null 3, null 3, null etc. I want to write an update query which results in the following data: N...more >>

Hi, I'm trying to get two decimals from [Res_Time] after converting the data type: Select AVG(Convert(decimal(10,2), (datediff(hour, ([date_Created]-.333), ([date_closed]-5.0417))))) AS Res_Time from cube.dbo.cube_Siebel_SR where.... Instead I get 6 decimals like 51.941176 Any sugge...more >>

Hi All, I have accidentally deleted some of the table in the database,=20 anyone could tell me how to recover the deleted table? I have a full backup of the database (1 week before) and the transaction log , so what action should I take to recover it?=20 Thanks for your advice!! Br, Ray...more >>

Hi, I have two tables containing minutely readings: Create Table IncOnOff (OnOffId int identity(1,1) primary key, [Datetime] smalldatetime, OnOff bit) Create Table TagMin (Tagkey int identity(1,1) primary key, [Datetime] smalldatetime,Value real) insert into IncOnOff Values('2004-0...more >>

Hello all Pretty basic, but I can't figure out the right syntax for getting an image file (.jpg) into the database, and then reading it back out again as a file. All my handy SQL books skip over this bit I'm building an ASP.NET app, and want to store small gif's and jpg's in the database itself...more >>

I have a table which I need to retrieve a particular field's value and then increment this field and return the incremented field to the calling program. I thought the best practice would be to use a scalar function. However, I have found out that a function cannot update tables. Therefore, I am ...more >>

SQL 2000 (8.0.760) I have the following trivial function. The contents of the returned table are not material . create function dbo.test (@testdate datetime) returns table return select * from dbo.AUDIT where CREATED_DATE >= @testdate go As far as I can tell, the following should be va...more >>

Hello, I am in need of a way to use a stored procedure/trigger to execute a script. Here's the situation. About 3 weeks ago Microsoft released an update to IE 6 that eliminated the ability to pass a username and password through the URL in a website. My company used this feature to al...more >>

How can I get the remainder of a value using a SELECT statement in SQL Server. I had hoped I could use a MOD function, but that does not appear to be the case. SELECT MOD(5,2) giving me a remainder or result of 1. Thanks Steve...more >>

Run this in query analyzer: select GetDate() as 'DATE', LTRIM(GetDate()) as 'DATE 2' Notice what happens to DATE 2. Why?...more >>

Hi. I am getting the following error running one of my SPs: ------------------- Server: Msg 7391, Level 16, State 1, Procedure sp_SignUpMain, Line 1520 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [OLE/DB provider r...more >>

I get a message An error occurred during the move data process: -14 when Microsoft SQL Server 2000 Analysis Services SP3 Setup.exe reaches 99%...more >>

Hi, Can someone tell me or point me in the right direction regarding user defined functions ? Within Ent Manager I can't find an example or help within the MS product. I thought Master or Northwind woul have one! How do create one? I assume if i created a function ConvertX i would us...more >>

Hi all Can anyone explain me why I can't able to get the following SQL to run declare @test as int set @test = -1 select A= case @test when -1 then 'dgffg' when 0 then null else @test end Whereas below can execute..... declare @test as int set @test = -1 ...more >>

Hi, guru' As far as I understand, MS SQL2000 makes good full text search in English but ignores all Russian words. Is there a way overcoming this trouble Thanks and breg...more >>

SQL Server 2000. I am calling an extended stored procedure that returns an error code (0 or 1) and error text on failure. On failure I would like to record the error into a log, but I cannot figure out how to retrieve the text into a stored procedure variable The extended stored proc is xp...more >>

Hi, I need to format a report in following layout: Day Withdrawal Deposit Balance =20 *** ********** ******* ******* 1/1 1000 1/4 -200 ?? 800 1/5 +500 ?? 1300 =20 1/6 -250 ...more >>

Hi, I can't seem to get this to work but everything looks good to me: IF (@DisciplineCode IS NOT NULL ) OR (@DisciplineCode <> 'ALL') SET @X = @X + 1 Any suggestions would be great. Khurram ...more >>