What kind of Errors the SELECT statement can raise?? In what cases? How to avoid them? How to handle them? -- Message posted via http://www.sqlmonster.com...more >>

Hi, i would like to know how to convert IF statement below into stored procedure. Below are the coding for the SQL statement and the IF statement strAND = "" strSQL = "SELECT T.TDR_Id AS ResendId , T.TD_Id AS ItemRef, " &_ "Convert(char(10),T.TD_Date,103) AS RDate, U.u_first_name AS F...more >>

I'm creating an import package to read a DBF into a sql table. It's all pretty simple - no transformations or anything that ought to be difficult. Yet when I run it, I get the error: --------------------------- Copy Data from Results to Results Task --------------------------- The task rep...more >>

I've got 2 tables to collect orders. The ORDERS table collects the name, totals etc. The DETAILS table collects the products. One product per each row. When a customer places an order, their CType field may get populated with QW. If CType = 'QW', With a trigger, I need to add a product to the ...more >>

Hello All, I have a MS Access 97 application that is trying to synchronize its tables to some tables stored on an SQL Server. The table sizes are gigantic (over 100,000 records) and upload rates to the SQL Server are atrocious (takes me over half a day to upload the tables to the SQL Ser...more >>

I have not big DB (12 tables) Suppose application is requested for some data about specified clientID by means of ADO.NET i got all the relevant data about this clientID from several tables to the client, client perform tasks on this data change it maybe add something new or delete somtethin...more >>

Hi all, What is the syntax for adding a UDT that has a namespace? When I try CREATE ASEMBLY Foo FROM "C:\Foo.dll" WITH PERMISSION_SET=SAFE CREATE TYPE Bar EXTERNAL NAME Foo.SomeSpace.Bar I receive the error Incorrect syntax new '.'. If I create the same UDT with no namespace...more >>

Hi, I've a code table with 2 columns consisting of an abbreviation column and a long name column. I've an existing table which I like to look up to the code table and replace its existing column names to conform with the ones in the code table. Any idea to go about this ? Write stored pr...more >>

Hello everybody. I have stored procedure such as: CREATE PROCEDURE [dbo].[CheckUser] @login nchar(10), @password binary(50) AS (...) Now, when i call it from SQL QUERY ANALYZER command EXEC CHECKUSER 'Bob', 'Lucy' i receive info: Implicit conversion from data type varchar to bina...more >>

Hi, we have an app that is written in a "special" way. Not using SP's. Instead, dynamic SQL is used. Also, it's written in a way that in order to update single order, the app sends 4-5 SQL's; some of them INSERTs and some SELECTs. Now here is the problem: some of the procesess require u...more >>

I got this script from Database journal and I found that very useful to DBA: Create procedure netsend @dbname varchar(50) As --Script Language and Platform: MS SQL 7.0 and MS SQL 2000 --Objecttive: Before restoreing,upgrading database,database administrator is responsible to --inform al...more >>

The trigger fire correctly with insert new comment, but when user update a comment field, I got the following error: ERR_SYSERR(104):PRM:SQLExecDirect failed I create a trigger as following: DECLARE @commentsOut AS VARCHAR(2000), @acct AS BigInt, @sub AS smallInt IF UPDATE(comment1) OR U...more >>

Books online says this about "Order By": The ORDER BY clause can include items not appearing in the select list. Well, that's actually strange and surprising. This is surely not new to many of you, but I just recently saw this construction: Use Pubs Select Top 5 * From Titles Order by N...more >>

Hi all Gurus, I want to sort my database on numeric part of my string expression for example A101 A1015 A1020 A102 A103 AA101 B100 B101 B102 when I pass range like A101 to A103 result should return A101 A102 A103 (note : A1015 and A1020 should not be in the result) w...more >>

Hi, I am working on exporting data from a remote MSSQL db using la inked server. Both SQL server 2000. So far I have a SELECT against a linked server with a hard coded value in the WHERE clause and it works fine: SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date] FROM DB_NAME.dbo.TABLE_...more >>

When writing a trigger, is there any way to get the SQL Command that fired it? I want to log the commands applied to a table so that I can replay them on a secondary database at a later time. Kind regards, Jordan Bowness. *** Sent via Developersdex http://www.developersdex.com **...more >>

Hi paul we are using MS SQL Server2000 when we try to take backup we are getting this Error " Error 926 Data base MSDB cannot Open it has been marked suspect by recovery" Please if you solution to solve this problem let us know. Thanks in advance S Kaliyan ...more >>

Hello there I've got many solutions in the past from here and from other books about shrinking database log file. All the the solutions were good but they didn't solve the maim problem. It seems that every week i need to detach the databse, delete the log file, and reattacth it again. ...more >>

I have a SP that retrives several fields on a table containing cariage returns in the data (IE Office Address) Im storing the address data in a temo table and then returning the resuls of the temp table. The issue is that many of the addresses seem to get truncated even though the field in...more >>

We have a table that was imported from Access to SQL 2000. For some reason the old data had phone numbers set as Float. ??? If anyone can explain that I'd love to hear it. Anyway, I have a SQL 2000 table that has a 10 character column called Phone_No and when I perform the INSERT I get 7.123...more >>

Hello, I have to import a dataset from a mainframe to Sql Server which contains about 100 fields, and about 90 of the fields have leading and trailing spaces in the data. After I import the data to a table in Sql Server I need to update this table to remove the leading/trailing spaces. ...more >>

I have data in a table with the following fields: company year sales I would like to create a report that list the company and its sales increase from 2003 to 2004 only if there are records for both years. I was thinking about a union but I don't think that is necessary. Can anyone...more >>

Hello, All! Simple query: ---- Start Batch1---- declare @startdate datetime, @enddate datetime set @startdate = '03/01/2005' set @enddate = '03/23/2005' SELECT OrderID, OrderCode, CONVERT(varchar(12), o.OrderDate) AS InvoiceDate, o.RecTotal FROM Or...more >>

I am using the OpenXml command and I have a question (XML posted below)... I don't know how to get the values of certain elements if they columns needed are not attributes to the XML... For instance I can get the value of /attributes/items for the type attribute but if I try to get the val...more >>

I have for example the following SQL: BEGIN TRAN Delete from users where userid = 102 Update mainSettings set userCnt = UserCnt -1 COMMIT TRAN If for example the "update" statement fails, but the "delete" statement is successful - the overall transaction reports no error, and thus, m...more >>

SQLserver 2K Table A (Customer Master) CustID CustName Table B (Customer Location) CustID LocationID LocationOrderID LocationName What's the syntax to automatically add LocationOrderID in increment of 1 for table B where B.CustID = A.CustID ? For example, A.CustID = 100 and t...more >>

I have a couple of apps going here that each want to do some variation on the following. I guess that means it constitutes some kind of "database design patter". I wonder if anyone has a comment on it. Say we have a customer file on server A. Another application wants to import the customer...more >>

Greetings, I want to order by Orders then RecType, BUT leaving RecType=13 in between 30 and 40. So the sequence should be: 10, 15, 30, 13, 40 for Order=4501130 then Order=5006730. ----------------- CREATE TABLE TblA (RecType int, Orders varchar(255)) INSERT INTO TblA (RecType, Orders) VALU...more >>

I'm trying to subtract two dates (End_date - begin_date). The result should reflect the hour and minutes elapsed between the two dates. Any suggestions?...more >>

Hi, I have created a VB program to perform DTS tasks for data transfer from ACCESS database to SQL Server. The DTS are created by saving the actual DTS packages as VB file and used those bas files in VP app to run the DTS programmatically. The process works fine most of the time. But occassi...more >>

I would like to store an array of char* to a SQL Server database. Which type of data should I use in SQL Server for that ? Is there a Visual C++ code example for that ? Thanks. ra294@hotmail.com ...more >>

Hey, this is probably a pretty basic SQL question -- i'm not a SQL genious by any means. I have two tables, both identical for the purposes of this question. table a table b table b has some records which are meant to "replace" some rows from table a (same IDs) if they exist. basical...more >>

Hi folks, I am running the following statement on SQL Server 2000 Database: DELETE from tablea WHERE effective_date >=CONVERT(varchar(11),GETDATE()-3,121) AND effective_date < CONVERT(varchar(10),GETDATE(),120) tablea has effective_date defined as smalldatetime. When i run the sql, i get ...more >>

I have written a DTS package to import a table from Oracle into SQL Server. This table is defined as Data_Group (which is an employee id), Element_Code (a numberic code that tells what data this row has, ie. Name, Address, Status, etc), and then the Value. There are several elements that I w...more >>

I created a trigger to update about 12 comment fields in a table. It works fine in the test and development environment. But when I implement in production, the user cannot update those fields. Does anyone encounter this problem before? Please help! All hell are breaking loose on Monday m...more >>

Hi, I'm running SQL 2000 on Windows 2000 server with 1.5 GB of memory. I notice that SQL would run the full 1.5 GB after a month or so and the user would start getting error message about time out or not able to update database randowly on an application that uses the SQL. I reset the SQL ...more >>

Dear all, Every time I create a new object such as table or stored procedure from Query Analyzer and then I'm going to Enterprise Manager and I make a refresh option doesn't appear that object. I am wondering why this happen. In fact I make a refresh at most high level on enterprise manag...more >>

Let me start by saying if you know a better way to accomplish this task please don't hesitate to comment. Running SQL 2K sp3a Windows 2000 Server I am trying to load data from one semi-relational database to a new OLTP database (In house CRM system). Now for my challenge. I have r...more >>

I have created a view that found duplicates and would like to know if I can create a TSQL DELETE statement that will delete all but 1 of the duplicates. You will notice in the SELECT SQL below that I have to join 2 tables in order to find the duplicates as the one table I want to delete from d...more >>

Can you create a foreign key that can come from 3 or more tables? For example, I have an ADDRESS table, where the "WHOID" can be tied to 1 of 3 tables (but only to 1 table) ADDRESS TABLE - addressid - whoid ----------------------------------------- 1. EMPLOYEES TABLE - employeeID 2...more >>

I am trying to pull two different information for each month of the year. However, I am having trouble with my SQL statement...I was wondering if someone can point me to the right direction. SQL statement: SELECT DatePart(mm, Reports.IncidentDate) as MM, Count(Distinct Reports.Report...more >>

Is there away to receive sql query as string to a stored procedure parameter and execute it in the stored procedure? How can I do such a thing?...more >>

Hello all. I'm trying to use a CASE statement to return the Month name from a month number (1=Jan, 2=Feb, ect.) I have a field in my db that stores the date as epoch time. Here's the entire SQL, I get an "syntax error near =" when I run this: SELECT CONVERT(char(20), DATEADD(sec...more >>

Hi Group- I've a trigger which (under a condition) inserts records into the same table as the initiating record. How can I avoid someone else "stealing" the PK for that table? (or over-writing the data for their record when my trigger fires) Can or should I try to force a certain lock on th...more >>

Iin SQL Server 2000 I have two tables that I need to join table A and table B. The result set is a little tricky though. Table A has a set of columns that are duplicated in table B. The reason is if there is no data in these columns in table A, then that means that the data "defaults" to the ...more >>

I am currently modelling a db to store geneaology information (family tree) Each node on the tree will always have zero, one or two parent nodes, depending upon whether the details of both parents are known. I have been initially modelling this using a simple adjacency list model however I w...more >>

Wel maybe someone could help me. I'm working with an excisting database using MSSQL, where are 2 T_datetime fields. If i display these 2 columns i see the date time format as follow: Aug 5 2004 6:03PM The problem is that i only need de time format like 6:03PM or better if possible 18:03. ...more >>

In an AUDIT_LOG table, with approximately 1.6 million rows, would there be any way to extract a pattern of rows? Specifically, I am trying to select all records that have an audit_log_id of '13' which would be immediately followed by a record with an audit_log_id of '16', which in turn would b...more >>

Dear all, I've deteled inadvertently a table in one DB. Restore a backup for .MDF? Forget it, that will retrieve old objects or objects with less data. It's simply i want just to come back that table and its rows. I heard a lot of time ago that it was possible do just a restore for L...more >>

how to get the windows login username in SQL SERVER 2000....more >>