I want to be able to check if column 27 has been updated either on an insert or an update. If this column has been updated with a value I want to insert the following fields into a different table (InvMov) within the database: StockCode, Warehouse, TrnYear, TrnMonth, EntryDate, TrnQty and Move...more >>

Is it possible to export DTS packages to another server? If so, how? Thanks much in advance, Kelly ...more >>

I have duplicate records where the duplicate data spans multiple columns. I'm not sure how to delete the record I don't. I have a table where the duplicate row is the opposite of the original: Column 1 Column 2 Column 3 Column 4 Column 5 (Original) Data 1 ...more >>

Using the included stored procedure I am getting the following result. However I need this to return the DISTINCT forum.ID from the tables while maintaining the correct post count, topic count and last post date. Columns c_ID t_ID f_ID c_Name f_Titlle TopicCount PostCount LastPostDate Res...more >>

I'm trying to get all the PKs for all the tables in a DB. Right now I'm using: sp_msforeachtable "print '?' exec sp_pkeys @table_name = '?'" However sp_msforeachtable returns the table names as [dbo].[tablename]. The sp_pkeys proc takes tablename without dbo. or the brackets '[].' Is there ...more >>

Hi Everyone, I have a query that returns list of accounts with their tranasaction amount, date, and a running total for the balance. It looks like this: SELECT acount,transDate,Amount,(SELECT SUM(AMOUNT) FROM transactions B where B.transDate<=A.transDate and B.Account=A.Account) as Balance f...more >>

I was just curious on the pros and cons of returning a value via a return value vs a select statement in my Stored Procedure. For example, I have a SP: ************************************************************************************* CREATE PROCEDURE CopyFromTemplate ( @ClientID varCha...more >>

Hi guys, Can you suggest good books on relational algebra and database design(NF)? Thanks a lot in advance Alex ...more >>

Hi all I would like to know in a select if my varchar field contains only alphabetic caracter ?? Is there a Function that can tell me this ?? Thank Nic ...more >>

I see the term data architect a lot in this industry, and indeed, that is what many of us do. But in many U.S. states, it is illegal to hold yourself out as an "architect" (apparently of any sort) without being certified as an architect for buildings. I'm curious if others have considered this...more >>

I am writing a stored procedure where I need to use a temp table, so I decided to use a table variable: declare @a table(a varchar(2)) A large number of records will be inserted into this table, perhaps 100k maybe even more. Is this a good idea to use table variable for this purpose? Are there ...more >>

Hi, I am looking for a book that has sql server and classic asp(not .net). Is there any good book someone could recommend. Thanks. PS: Sorry for the cross post. ...more >>

I have various Stored Procedures that handle the creation and maintenance of various tables (AddNewScreenTest,UndateScreenTest,SelectedScreenTest). The problem is that I have 4 tables that are identical. CREATE TABLE [dbo].[SkillsTest] ( [ClientID] [varchar] (20) COLLATE SQL_Latin1_General...more >>

Is there an easy way to do a copy of a batch of selected records from one table to another? For example, I have the following table. CREATE TABLE [dbo].[SkillsTest] ( [ClientID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [PositionID] [int] NOT NULL , [Version] [int]...more >>

Hello I am currently trying to write a sql SP for a crystal report I am designing. The problem i am having is i do not know how to write the select statement to retrieve the data necessary for the report. The Current SQL: Table: SOP30200 SQL To Extract Date: SELECT dbo.SOP30200.SOPNUMB...more >>

Is it possible to create a view of a table by transversing the rows and than populating the rows with values from another table? ...more >>

Hi Freinds, Does SQl 2005 has still limitation of 1024 column per table? Thanks, Pat ...more >>

Hi Freinds, I have to convert an old table which has 1185 columns to SQL SQL has limitation of 1024 columns. Any idea how to approach this problem? Thanks, Pat ...more >>

I tried to upload a pdf file into a binary field using asp upload. I cannot tell if there is anything in the field using query analyzer, and I was wondering what shows in the grid when something exists there. It is just showing as <binary>...more >>

can I pass a parameter to a stored procedure that is the name of the field to update?? For example update tbl_CheckList set @FieldName = suser_sname() where tbl_CheckList.ClientId = @ClientId the @FieldName doesn't seem to work when I call it: exec ProcName 'LetterSentBy', RecIdNo If yo...more >>

When I get an error when trying to save a procedure, it refers to a line number, but that line number seems to have no correlation to the display of the procedure on the screen. Is there any way I can get Query Analyzer to show me the line numbers? If I do Edit/Go to Line, it takes me to a l...more >>

Hi, I've created a user defined function in my database that I'm currently = using in my order by statements like so. =20 Select textnum from mytable order by dbo.NumericTextSort(textnum) The function looks like this: CREATE FUNCTION [dbo].[NumericTextSort] (@value varchar(3)) =20 RE...more >>

All are welcome to try and crack this! I have three subqueuries within the main query and I have a syntax error near the keyword 'AS' and I can't figure it out. Below are two variations of the same query... the only difference is that in the second query below I pulled out the JOIN stateme...more >>

Hello, folks Desc. two tables table A CriteriaID 1 2 3 Table B UID ResultID CriteriaID 1 1 1 2 1 2 3 1 3 4 2 1 Want to build a query which...more >>

We have a table in SQL Server with 3 columns, and 1 of the columns is a varchar(2000) column. The database has a "simple" recovery model. We insert data in the database every millisecond. Every day, we delete data from the table that is older than 1 week old. We then changed the column to be v...more >>

Hello all, I'm new to sql and am trying to do something that i hope someone can help me with: I'm attempting to make a select query where an alias field value is conditional on a value in a real field, for example, if the source has name, and gender fields where gender is a number 0 or 1, ...more >>

I am not sure how to go about with if...else conditional logic in t-sql. Can someone point out where I'm going wrong with the code below (particularly the 'BEGIN NOT END IN' part)? Thanx, Bass ================================== @status int; SELECT employee_id, last_name, fi...more >>

I have a table that is named CliCore and has the following fields, RegNo - Primary Key CliFName - First Name CliMM - Middle Initial CliLName - Last Name CliDOB - Date of Birth I have another table that is named CliEvents and has the following fields, UID - Primary Key RegNo - Foreign K...more >>

Hello, If db1 on a sql server (2k) contains source data, what is the best way to access/work with this data from db2 (located on the same server)? Here is the specific scenario: db1 contains all of our source data. User1 needs her own db where she can write SPs, functions, create views, t...more >>

I have a table called 'JobSchedules' it contains datetime values for when to start a job and when to end a job. The date part of the equation is irrelevant as the schedule is meant to start and stop jobs daily. How can I do a query to return jobs with a starttime = 9:30 a.m. (for example) o...more >>

Can anyone tell me why the following deletes all the records instead of simply the ones returned in the "Exists" clause? The select in the exists by itself returns 131 records, however when run in the following context it deletes all 4474 that are in the Shades table.???? Delete FROM #TMP_...more >>

I am using sp_databases to figure out what databases are available to a specific user. Is it correct that this only returns a list of databases that the user has access too? I ask this because we just installed software on our network which only a couple users have rights to which also include...more >>

a.table field1 field2 1 a 1 b 1 c 2 b 2 c 3 a 3 b 3 c I would like for the query to pull all records in a group if the group does not have a as a memeber of the group. There are 3 groups above: 1,2,3. I woul...more >>

Hello, I have a simple question. How do I autonumber an integer column called agentID. I need to insert any rang of numbers in this column....more >>

The reason some people don't know for sure if they are going to Heaven when they die is because they just don't know. The good news is that you can know for sure that you are going to Heaven which is described in the Holy Bible as a beautiful place with no death, sorrow, sickness or pain....more >>

Hi All, I need to create a trigger on a few tables that if there are any inserts or updates to the table it writes the entire record to another table. in the triggers I have now my code is: If update(col Name) begin do something end Do I need to list every column or is there a way to sa...more >>

I have a table with 1 columns in it CREATE TABLE [dbo].[date] ( [Date_updated] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL [Day_of_week] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL [Day_of_month] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL [Day_of_year] [char] (3...more >>

Hi. I'm using Microsoft Query to create a query for Excel 2003. I'm attempting to create 3 Left Outer Joins, but upon doing so I receive the error message "Query Can Have Only One Outer Join" as described here... http://support.microsoft.com/default.aspx?scid=kb;en-us;214027#appliesto After ...more >>

Hi everyone, I could use some help on date aggregation and formatting; just starting out with SQL programming. My raw data looks like this: 04/17/2005 Location1 Discharged 04/17/2005 Location2 Discharged 04/17/2005 Location 3 Discharged 04/17/2005 Location 4 Discharged 04/...more >>

I try to insert binary data into an image column using INSERT INTO [FileData] ( [data] ) values ('0x4DD38D8D'). I run the script from QA and once the data is greater 8kbyte i get the error "Operand type clash: text is incompatible with image". I know that the QA is limited to a certain length o...more >>

Hi everybody, i've a problem calling a sql sp from my c++ component, if i voluntarily crash the sp (for example try to insert a null value where is not allow) return value are all blank and isn't possible for me to understand correctly the problem here you are sp: CREATE PROCEDURE SP_TE...more >>

Hi all, Can anyone tell me if by having a print of a variable i.e. (print @strsql) in a stored procedure slows down the overall performace, even by milliseconds as I have run some test with STATISTICS TIME ON and most of the time they are quicker with the print in, is this right? Thanks ...more >>

Hi, In my application , the connection to SQLServer is established through a DSN name, UserId and Password(both UsrId and Password are non empty) . The "SQLConnect" function is used for this purpose. The problem is that the connection to the Database is success when I give an empty UserId. ...more >>

Hello, i have an issue concerning transactions and locking which i cant figure out... Here is the situation: I have a Table (OrderTable) with one primary key (order_id) and a clustered index on it. In Query Analyzer i open two sessions(spid 54 and 55). Session with spid 54 calls a sim...more >>

We own a software package that has a SQL Server driver based on ntwdblib.dll. Will we be able to run this software on SQL Server 2005? In other words, will the DB library be supported?...more >>

/* ********************************************************************************************************************* 2005-04-22 11:34:40.78 spid4 Wait-for graph 2005-04-22 11:34:40.78 spid4 2005-04-22 11:34:40.78 spid4 ... 2005-04-22 11:34:45.78 spid4 ResType:LockOwner Stype:'OR' Mode...more >>

Hi When using FOR XML AUTO in sql server is there a way to change the document typ definition (<?xml version="1.0"?>). What I wanna do is add the encoding property so it instead of the above reads <?xml version="1.0" encoding="ISO-8859-1"?>. The reason I wanna do this is because I have char...more >>

When I open SQL Server 2000( Enterprise Manager). I face on the problem: "Microsoft Management Console:mmc.exe - Application Error". I don't instal SQL Server 2000 again. How can I do it? Please, help me! Thank! ...more >>

I was wondering whether it is possible to sort a column based on numbers and alphabets alternatively. Eg. Class Codes: (Unsorted) 111 112 113 11A 11B 11C 114 11D Sorted Class Codes: 111 11A 112 11B 113 11C 114 11D "select * from ClassCodesTable sort by [class codes] asc" ...more >>

In Books Online it says "The select list of a subquery introduced by EXISTS almost always consists of an asterisk (*). There is no reason to list column names because you are simply testing for the existence of rows that meet the conditions specified in the subquery." If I've got two tables...more >>

Hi, how to retrieve a list of dates from 2 given dates like 2004/04/22, 2004/04/27? Expected output: 2004/04/22 2004/04/23 2004/04/24 2004/04/25 2004/04/26 2004/04/27 Can I do that by a single SQL without using store procedure? Thanks RC ...more >>

(1) Join the Master and arinvinfo table and then update the 'arinvchg' table UPDATE DTS_Account.dbo.arinvchg SET acctcode = CBA.revimcode,acctname= CBA.revimname FROM DTS_Account.dbo.arinvchg arinvchg JOIN DTS_MASTER.dbo.chgbranchacct CBA ON CBA.chgcode = arinvchg.chgcode JOIN DTS_...more >>

I would like to create a trigger on a table for insert or update, that would enter the current user's login id int a specific field in the table When a record is updated or inserted, i would like to record the user id of who did it. Thanks for any help Walt ...more >>

hi! i want to backup my database. its data file size is 46MB and log file size is increased upto 2GB. now i want to take its backup on one CD. so i shrink the database but only decreased the virtual log file size not the physical size. i also used the command to shrink the log file size to ...more >>

Hello group, I use SP to update value in myTable, MyTable have a few columns (about 15) and I want update not all field. Sometime I need update column 2,3,5 and next time 4,5,11. If parametr is not defined leave current value My question is how set as default value of parameter equal current v...more >>

hello, I am using SQL DMO objects to connect to an SQL Server and retrieve information about it. Using some properties of the "SQLServer" DMO object, I can get the version of the SQL Server installed. However, I want to be able to detect whether a SQL server installation is a pure SQL server in...more >>

I installed the MS SQL beta2 samples. Trying to open any CLR programming example (HelloWorld etc.) with Bussines Inteligence Development Studio causes an error with following message: "The application for project ...\HelloWorld.csproj is not installed. Make sure the applicationfor the pr...more >>

Hi, I have two tables like this, Create table TableA (col1 int, col2 varchar(2)) Create table TableB (col1 varchar(2000)) insert into TableA values(1, 'A') insert into TableA values(2, 'B') insert into TableA values(3, 'C') insert into TableA values(4, 'D') insert into TableA values(5,...more >>

hi, Currently we move a couple of SQL Server 7.0 to SQL Server 2000 and right now we habe big problems with a OPENQUERY Statement The SQL Server 7.0 and Windows 2000 is a german version, the MS-SQL Server 2000 and Windows 2000 is in english The Query on the MS-SQL Server 7 works...more >>

Hi, I have two tables like this, Create table TableA (col1 int, col2 varchar(2)) Create table TableB (col1 varchar(2000)) insert into TableA values(1, 'A') insert into TableA values(2, 'B') insert into TableA values(3, 'C') insert into TableA values(4, 'D') insert into TableA values(5,...more >>

Hi, I have two tables something like this, create table myTable1 (col varchar(2000)) insert into myTable1 values(1,2,3,4,5) create table myTable2 (col1 int, col2 varchar(20)) insert into myTable2 values (1, 'A') insert into myTable2 values (1, 'A') insert into myTable2 values (1, 'A') ...more >>