Hello, How do I insert a large text file into a single 'text' column in a single row? My understanding is that I can insert up to 2GB of data. Is there a simple T-SQL insert command that I can use to reference this large text file on disk? I know how to insert other data, but I can't find a...more >>

Hi, I have a question regarding the VIEWS. I was wondering if I filter a VIEW using the WHERE clause, SQL server fetches all the data from the view before filtering the data or it combines the VIEW source with the WHERE clause. This a sample: MyVIEW: SELECT * FROM customers Select State...more >>

Hi All, can anyone help me??? I have an application that access my SQL Server. When I debug it with Profiler I saw a line like this: EXEC SP_CURSOREXECUTE xx,...... How can I Know which T-SQL command generate this execution? Thanks Bonato Pierantonio ...more >>

Hello , Is it possible to have a conditional where statement, conditional in the field selection not in the value selection ? SELECT field FROM Table WHERE condition1=value AND case when @variable=1 then condtion2=value end So only when @variable has the value of 1,condition2 should particip...more >>

Hi, I need to compare 2 databases say A and B if there are any changes in the table tbl1 in DB A then i need to make the same changes in table tbl1 in DB B. eg: if a column is added in tbl1 (in db A) then i need to add a column in tbl1(in db B) also. i.e i need to syncronize the Databa...more >>

Hi All, Sorry to post this message in the Group. How do I BACKUP all the subscribed news groups (News Group Account, All Downloded Message Header and Messages) in my outlook express 6.x so that I can restore them in a PC which is not connected to Internet. Thanks Prabhat ...more >>

Hi, I'm trying to change database owner from the job (sp_changedbowner 'sa'). Job is executing under administrator domain account (member of sysadmin) privilegies. Sometimes it completes successfuly, but sometimes it doesn't change database owner!?!?! At the same time, I'm always able to do it ...more >>

I am having locking problems with symptoms nearly identical to those described in KB articles 260652 & 828096. They are entitled: "Nested Loop Join that uses a Bookmark Lookup with PreFetch May Hold Locks Longer" and "Key Locks are help until End of the Statement for Rows that do not Pass Filte...more >>

I have two SQL Server Servers, both MS SQL Server 2000. From one, linked to the other (sp_addlinkedserver 'SecondServerName', 'SQL Server'). I can sucessfully execute the following code "Selectv @count = Count(*) From SecondServerName.Sales_Rep.dbo.Mfg". But if I place the query in a variable ...more >>

is it neccessary to create maintainence job (dbcc dbreindex) for system db such as master, tempdb, msdb and model? At least I don't think we need dbcc dbreindex for tempdb and model. is that right? And for BACKUP Job, I don't think we should include tempdb in backup job, is that right? ...more >>

Hi All, I have a table "EMP" having the folowing DATA in my SQL Server 2000. EMPID GIFTTYPE ----------------------- 1001 U 1002 NULL 1003 U 1004 D 1005 NULL ---------------------- If I write SELECT EMPID FROM EMP WHERE GIFTTYPE <> 'U' Then I am ...more >>

I need to export one of my SQL Server 2000 tables to a text file. At which point, someone takes this file and inputs some data and gives it back to me. I then must import this and its updated data back to my original table. Right now I'm writing a VB program that does this, but I have the feeli...more >>

I am trying to compare the last 8 characters of a column in two different table to see if they compare. I know has something to do with the LEN and RIGHT functions, but not sure...more >>

Hello.. I am trying to write a stored procedure to run the following query: Select * from tblTXN where term_id like 'ABC%' But i want to send the condition as an argument (which is in this case = "ABC") So, how should I write the query in the SP to accept the ABC only as an = argument?...more >>

This is so simple in Oracle but I can't figure it out in MS. All I want to do is pass a parmeter during runtime. Something lik Select name, hiredat from employe where hiredate between @startdate AND @enddat Any guidance appreciated....more >>

I am wanting to create a SQL Job that updates 'currency' columns in a table. Being a novice I assume that I could create a simple Job Update statement such as: UPDATE customers SET draft_total = total - agent_commission WHERE agent_commission > '0.00' AND agent <> '48000' But it error...more >>

Can anyone give me some insight as to why this is not updating? By the way the "Note" column is a text column so I have been resorted to using an Instead of trigger. I have checked to make sure the condition is met so it heads into that branch but for some reason wont update. Below is my quer...more >>

Is is possible to tranform columns of a table into rows of another table ? ...more >>

I need to set the colors of pie chart elements to specific "style guide" values. I do not see documented how to do this in RDL, and cannot find a way to set this in the user interface. How can I set chart element colors to specific values? Thank you, Jim Lacenski Premera Blue Cross...more >>

I have the following delete trigger on my parent table which basically cascades through all of my child tables deleting any child data. I also have delete triggers on the child tables that I do not want to fire if the parent record is deleted, so I'm disabling the delete triggers on all of my child...more >>

Hello, I need to know in the code last value of the IDENTITY field I am pulling an autoval field from syscolumns: SELECT * FROM syscolumns WHERE autoval is not null I need to be able to pass that autoval value to an int or bigint variable. Just doing CAST(autval as int) doesn't seem to p...more >>

Here is the link: http://support.microsoft.com/default.aspx?scid=kb;en-us;284440 Bye, Delbert Glass ...more >>

Hi I create a view dynamically, using a stored procedure, based on logged user and filter parameters. I need to create an index on this view in order to loop through objects in sql database for external table linking in an access db (2000) when I try and create the index I get the following re...more >>

How can I export a database schema to a script that can be run on another server that will recreate the database schema? -- James ...more >>

I have a datafile that is comma delimited and has double quotes around the data. For example the last name column may have a value of "Smith" instead of Smith. I am using BCP to import the data but keep receiving error messages no matter what changes I make to the format file. This must be a common ...more >>

We use external drives that can be taken from one server and added to another. We do this right now when we have large backups (150gig)that we need to move between servers. In case of a disk failure we do have a standby server that we ship our trans logs to, but that could be up to an hour...more >>

I am trying to implement a trigger that fires an update to a linked server. Since the server is linked, I have a prefix of 4.. "Server_Name.Database_Name.dbo.Table_Name.Field_Name" When I attempt to save the trigger I get a message stating that the maximum number of prefixes on an obj...more >>

Is there a table that I can access through a query that will allow me to look up the job log history of jobs run by the SQL Server Agent? I have a DTS package that runs nightly (via a job) and would like to create a query to pull success/fail information from the job history. Thanks, Chet ...more >>

Why does this union not work, each statement seperately works fine, but a union causes the error in the second statement: The column prefix 'ALARM02' does not match with a table name or alias name used in the query.?? strSQL = 'insert into myTable select MASTER.DEVICE as device, ALARM01.EV...more >>

Hello All: I am looking for ideas on implementing key word searches for an inventory Database. Any/all suggestions, links to discussions and code samples would be welcome. Thanks in advance Habib ...more >>

Greetings, All. I'm having a problem using top n in SQL 7. It doesn't work when I use it on my own database but does work on the system and example databases. When I execute "select top 10 from sysobjects" againts the Northind database or the master database I get a list with 10 lines. When I...more >>

I have table: Numbers City Name 1 2 1 3 2 2 etc. I would like to fill out the "Numbers" filed with numbers starting from 1, so the result would be: Numbers City Na...more >>

Hi all I am trying to connect to my SQL server from my Asp Page. The sql server is on my machine WinXP and is called (LOCAL). Currently, it is registered as using Windows authentication. If I try to change it to SQL authentication, when I press OK on that edit screen it tells me login failed for u...more >>

I don't know if this is the right group to post this to.... I am using Access 2002 to work with MS SQL 2000 tables in a DB. One of the tables that I am working with looks something like this... User ID Name Date 1 John 2/4/2004 2 j37dhewys ...more >>

hi, I want to do some thing like: Update @table1 set col1=(select col1 from table2 where table2.col2=@table1.col2) where @table1.col2=table2.col2 here @table1 is a table variable. I can not get thro this update.Please help. regards, harsh ...more >>

Hi all, I'm using ADO to write an application that connects to SQL Server, but the user is allowed to define their connection settings. I use NetServerEnum (with dwServerType = SV_TYPE_SQLSERVER) to get my list of available servers, which is fine. However currently, I then get a recordse...more >>

I have a simple select statement that I want to count the number of records by date as follows: select eventdate, count(casenbr) from tblcase group by eventdate My problem is that I want to just group by the date and not the time. Is there an easy way to do this? ...more >>

Hi, Does anyone know why would a dbopen call fail intermittently? I am running SQL 2000 and connect to database from a Windows service (RPC server/C/C++) on the local machine (use "machine-name,1433" to connect). What is weird is that most of the time the call succeeds, but occasionally it fai...more >>

I'm new to SQL Server but am pretty proficient with vba and Access dao development. I am trying to use roles to dynamically control my forms with the UDF that I created to report back which role a user is grouped into CREATE FUNCTION dbo.GetRol @Role_Name varchar(12) = ' ) RETURNS varcha...more >>

Hi All, I am trying to develop a process to scan errorlog periodically for errors and. The process should be smart enough to detect last scan date and continue scanning from there. One of the other criteria is to skip the scan and report error if the file size is over 100mb. I can use sp...more >>

Hi, All I connect to a MS SQL Server 2000 with SqlConnection (ADO.NET & C#) with this connection string. Server=SUN; Database=master; User ID=sa; Password=test; Connect Timeout=1000; And Submit these statements. 1- ALTER DATABASE [MyDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE 2- RESTORE...more >>

Ok, so I made that up, but here's the problem. I'm trying to make a grouping query on Table1 with fields Cost and Code. The goal is for the highest costs codes to sort to the top and all related codes to fall right below. I want to group by Cost and Code like this. Can this be done ? THAN...more >>

Does anyone know if there's a way to run external .sql files from within query analyzer ? Eg. sp_execscript "c:\foo.sql", without actually opening up foo.sql and clicking "run"....more >>

Hello All, Trying to get the select statement below to return 1 line per invoice(invnumbe). Due to the grouping, it returns more then 1. Of course, I need all of the selected fields in the query results.. Any help would be appreciated. Thnks, Rob SELECT MAX(scandate) sdate, MAX(poin...more >>

I have table company and table products. I would like to write the select, something like this SELECT c.companyName,products=(SELECT p.productName+';' FROM products p WHERE p.companyId=c.companyId) FROM company c and the result should be: companyName products ______________________...more >>

I have created a stored procedure using cursors that does inserts and updates to tables. It completes successfully, however it always returns no column name -------------- NULL Is there a way to not have it return this?? Thanks, Fran...more >>

Hi everyone I am a new guy learing SQL Server 2000. I create a new view having the SQL statement as below and I get the error from SQL Server saying that: "The Query Designer does not support the UNION SQL construct." Would anyone have any advice for me on how to get around this problem. Thank you...more >>

Hi, I am in a project where we are changing the database design radically of an old product (used to be access+VB3, now VB6 + SQL server). Basically its an accounting software and the problem is that there are to many databases right now; one that is independent of fiscal years, and one more f...more >>

How do you create a column formula that uses a value from another table. Have a table called WIP with a column for OH and Labor and a table called Department that has a field named Overhead. OH needs to be WIP.Labor * Department.Overhead. Overhead is the same for all records in Department. Ca...more >>

Hello All, I'm brand new to SQL programming. I'd like to have a datetime field in a table that shows when a record is added. Is there some simple formula that I can add to the field when I'm designing the table, or do I have to do this with a trigger that fires on insert? Thanks, Brian ...more >>

I have a data transformation that involves tranferring data from 1 SQL table to and identical FoxPro table. However, in the FoxPro table date columns can contain "blank date" values whereas SQL can either be NULL or 1/1/1900. The foxpro table will not accept NULLS and to put 1/1/1900 is not an o...more >>

I am not sure hoe to call the following, can anyone help please? CREATE FUNCTION dbo.Split(@String nvarchar(4000), @Delimiter char(1)) RETURNS @Results TABLE (Items nvarchar(4000)) AS BEGIN DECLARE @INDEX INT DECLARE @SLICE nvarchar(4000) -- HAVE TO SET TO 1 SO IT DOESNT E...more >>

I need to update a temp table based on the value of the row Im on in one column and the value of another column in the next row. I know it sounds strange so Ill explain as I go and show the desired results; --the regular table create table t1 (c1 int identity(1,1),c2 int,c3 datetime) ...more >>

We have a table here that uses auto num to track records. Obviously these numbers should be in sequential order but due to the occasional system problems there are gaps in the sequence order. I need to find the gaps (missing numbers) in the sequence. Any help is greatly appreciated. Thanks, ...more >>

Hello, I have an app that used as a tool for our collections dept that has different forms for Receivables overdue 31 days, 45 days, 60 days etc..... Last night I had an additional param thrown at me. Certain accounts will be tagged to be different. Example: customer a is tagged for ini...more >>

Is there a way to fire a trigger or stored procedure at a certain time? I want to fire a store procedure off every monday morning, but I do not know if that is possible. Any help would be greatly appreciated. ...more >>

Hi I Need to Update a table with multiple values i. i have a table with 2 Columns and 20 rows, those values must to be changed with a query. the code i have tried is it: UPDATE mark SET name = SELECT DISTINCT mar FROM table1_tmp$ WHERE code = id_mark WHER exists (SELECT name FROM ma...more >>

Lo all, OK - I have an application running 24/7 where users in 4 hospitals enter data every 1 hour. At the end of the day it calculates totals, adjusts a couple of settings for the following day and clears down etc. The overnight job consists of about 7 dts' - firing consecutively on succe...more >>

I want to make a copy of my database (stored procedures included) into a file, and send this by email there is an option in SQL server to make this?? Thanks JAndro...more >>

Hi, How can I select do this in one select statement. There is the business rule and the result want show below. Thank you very much in advance. drop table #Address go CREATE TABLE #Address ( Agent_id INT NULL, Person_id INT NULL, PersonTp_id ...more >>

I am using SQL Server 2000 SP3. Can anyone explain why the following example is so slow. I have about 5000000 records in 'result' table and about 19000000 records in 'resultsub' table. DELETE FROM result WHERE QueueID = 4262062 The above statement takes over ...more >>

I currently have many Cobol pgms that accesses the db like so: EXEC SQL SELECT * INTO :DCLSalary-History FROM Salary_History WHERE Individual_Key = ...more >>

I am writing a database report. Is there a function that can be used in a SQL query that will determine if a value for a column is NULL, then keep it, if not, replace it with another column's value? I can use the IF(ISNULL(column1),"",STRING(column2)) in the report itself, but I need to hav...more >>

I have a problem and don't understand why I cannot get this to work. I will include the sp below but the gist of it all is I have a field that is an equation in string format such as Value a/Value b and I have 3 seperate tables tblFCF1, tblFCF2, tblFCF3 that each hold information that holds numbers ...more >>

Hi... I have this small three table People People_Department Department -peopleID -People -Depa...more >>

Please forgive the long posting, but any help with this issue would be gratefully recieved We have inherited a system that uses VB6 and VC++6 components hoste in COM+. They make calls to stored procedures in SQL Server 2000 Rather than rely on IDENTITY columns in the database tables, the applic...more >>

Hi, I am pretty new about stored procedures things. I always believe in the past they were evil for the sake of DB independance. A few years back I worked in a place where we all use ANSI sql to retrieve anything we need. Also sometimes I needed to instantiate heaps of objects to finally rejec...more >>

i have 10 records in a tabl Field1 varchar(10) NOT NUL Field2 varchar(10) NOT NUL Field3 smalldatetime NOT NUL how can i delete the record with the oldest date ...more >>