Running a DTS package that has worked until now... delimiters are now present in the data and the package crashes... anyone know how to prevent ? I've attached snippets of the code so you could reproduce the error if necessary. I believe I must reconstruct strSQL1, but how ? -- The tabl...more >>

Hi, Is there any way to get around the fact that Group By SQL functionality is not supported in conjunction with FOR XML AUTO. Does this mean one has to get the info out of the DB and then convert it manually? Any thoughts, or pointers would be most welcome. Many thanks Colin B ...more >>

Is there a way to determine when the procedure was last accessed?. Thanks Vijay ...more >>

Hello - Anyone know of a way to configure an ODBC driver to be read-only? We have SQL Server database using merge replication. A user wants to import into Excel to "manipulate" data. Of course, my protests were a waste of time... I'd like to give him an ODBC driver that is read-only. S...more >>

Hi, I have inherited two databases, one running on the client, one running on the server. A copy of the data relating to one unique id is copied across from the server to the client using stored procedures. The client may then disconnect, work locally, and then update the server at a later date...more >>

Sorry for the cross post but I really would need an answer to this as soon as possible. Two questions: 1. How can I know how many times and what dates etc a database file with automatic growth either in percent or in megabyte has grown? 2. If I specify to automaticly grow by 10 percent....more >>

I have inherited a problem and although I have managed to trace it to this stored proc I have been unable to figure out why it is not working. The proc returns information for an end of month report but the debtors closing balance is not working properly. I have included the proc and would app...more >>

When are the indexes updated after an update statement? After each record is changed or after the whole update finishes? Suppose I have a table with 2 million rows and the first index, called Idx1, on the table is on CpnyID, a char(10) field. About 350000 records have a CpnyID = '2' ...more >>

Does anyone know how to delete a primary key with T-SQL? triocchu ...more >>

Hello I am currently trying to save some Question/Answer pairs to a DB and would like some advice on how best to do it. I am currently using a schema similiar to this Question varchar(255) -- hope the question isn't longer Answer varchar(255) -- same as above Chksum int -- a computed value ...more >>

Hi, How to add a new field and set it to NOT NULL? Like this: ALTER TABLE [tNew] ADD [Locked] [bit] NOT NULL? But I got an error: ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column 'Locked' cannot be added to table 'tNew' b...more >>

I have a table1 with many columns named like a1, a2, a3...and another table2 has one-one relation with column name of table1, such as a1-ID, a2-Name. Now how can I use this relation to implement name changing? such as: select a1 as ID, a2 as Name from table1 this is hard-coding, I just want ...more >>

hi, I have a problem.. i want to update the identity table and change it to increment by 400. I wrote this code but they won't allow me to update it.. update tb_item set itemid = itemid + '400' where itembarcode = itemid it said: Server: Msg 8102, Level 16, State 1, Line 1 Cannot ...more >>

Hi, I have a User Defined Data Type, and I cannot change the owner using sp_changeobjectowner. Does anyone know why, and or whether there is a work araound. Thanks in Advance JonesG ...more >>

TIA, I am working with a COTS software program that generates event logs whenever a user performs an action in the application. Unfortunately, there is no event which gets logged to indicate the application has been rebooted, so if a user logs in, and the system goes down, I end up with a lo...more >>

How can I to use one field return from a stored procedure ( it return more then one data fields) as a parameter in another stored procedure. p_getBeginAndEndDate return begin and end date and I need use the begin date as a parameter in another procedure p_getShortHours @id, @begindate ...more >>

hi, i have 2 tables. table A structure: trn_dt as date, seq_01 varchar(50) seq_02 varchar(50) qty_in numeric(10,2) table B structure: trn_dt as date, seq_01 varchar(50) seq_02 varchar(50) qty_out numeric(10,2) table A records: trn_dt seq_01 seq_02 qty_in 12/14/03 001...more >>

I have the following procedure that I'm trying to create and use that will assign a variable depending on day of the week, however, I keep getting an error about the equal sign... DECLARE @BDay INT, @EDay INT SELECT CASE WHEN DATEPART(dw, GETDATE()) = 3 THEN @BDay = 7 AND @EDay = 2 ELSE ...more >>

sql svr 2k sp3 i want aggregate statistics calculated on a monthly basis ... my Orders table contains these fields OrderID varchar DateOfOrder datetime QuantityOrdered integer I would like to know total number of distinct orders placed in each month, ...more >>

Hello, I am a newby with store procedure. My question is Is it possible to declare a local variable and assign a default value on the same line like in .NET DECLARE @intCheck INT = 0 Or do I have to do this DECLARE @intCheck INT SET intCheck = 0 Thank you Marc ...more >>

Hi, I have a database server name as 01devsql. My select statement is as under: select * from 01devsql.dbname.dbo.tblname The statement returns an error Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '01'. How do I make the select statement work. Reg...more >>

I have two tables. The first a list of items an a unique GUID to identify them (itemGUID). The second table has two columns with GUID's, one is a unique GUID for its items, the second is the Key value from the first table. The second table can have many items tied to one itemGUID from the first ...more >>

Hi there Is it possible to create triggers that run successfully when running bulk UPDATE statements? I guess the question is if I run a UPDATE foe multiple records will the trigger fire once, or once for each affected record? If it runs once how do I handle local variables - since there ...more >>

I am working on a stored procedures that access the database and then generates a long HTML string and returns it to the caller (ASP code). Most likely the HTML string will be longer than 8000 characters (max for varchar data type) but I don't seem to be able declare a local variable of typ...more >>

source is a csv file. and I want to import them into a table in sql server 2k. source file looks like below. 20030101,13:10,ABC,0.3,200 20030102,10:10,XYZ,0.2,300 the destination table is designed like; CREATE TABLE [dbo].[tblWeather2] ( [date] [smalldatetime] NOT NULL , [position] [va...more >>

Hi, How can I find out how to query Active Directory from SQL Server? thanks ...more >>

Hello I have a database tracking object attributes having the following table: ObjDataId int IDENTITY ObjId int CONSTRAINT FK_ObjId Reference ObjTable( ObjId) Attr_1_Id int CONSTRAINT FK_Attr_1_Id Reference Attr_1_Table( Attr_1_Id) Attr_2_Id int CONSTRAINT FK_Attr_...more >>

It tells me client for Oracle not installed. I found an installation of the client but it's 200MB!!! Is this really it?!! tia Zarko ...more >>

I have a VP app. that uses some queries from SQL Server 2000. I'm noticing in SQL Profiler that the event "Audit Logout" is using alot of cpu at times. How can I decrease these CPU times ? ...more >>

I have this Users table UserID UserName DisplayOrder 1 Billy 3 2 Jon 1 3 Maria 5 4 Adam 2 5 Paula 4 I display the usersname based on the DisplayOrder: select Username from Users order by DisplayOrder Suppose ...more >>

Need to know the syntax for reading/writting text information from/into a SQL image field. I thought the getchunk method would read the text value however all I get back are question marks... ...more >>

Hi, I trying to find a way of storing a 2 column by 2048 row file into a database table. I want to store the data in such a way that i can recall the data and graph it. Example of the data from a file. 200, 0.134 201, 0.345 202, 0.435 and so on Does anyone know how i can store ...more >>

Does anyone have any db design or stored procedure examples for reporting an organization's hierarchy (or chain of command) ? I have customer table that has a customer_id field and a reports_to field. I need to generate a report sorted by top level customer #1, followed by everyone report...more >>

I select a date from a DB, and I display those results to a web page. rather than come up with a crazy JavaScript function to format the date, I'd liek to do it on teh SQL side. So... How can I get a date to spit out of SQL like this: ---- instead of '11/15/2003 05:01:00.00' ...more >>

Greetings ALL, How can I script DB objects dynamically using TSQL - I need to get the same result as I would have got If I have used the Server Manager and Scripted the Objects. TIA, Dummy & Dummy ...more >>

Hi, I would like to create a database diagram from an SQL script. Can that be done? Where is the information on diagrams stored? Are there stored procedures that can accomplish this? Thanks in advance, Peter Strøiman ...more >>

I want to move from last selected record to the first in a table and do some modifications for selected row. My code : -- Declare the cursor. DECLARE Index_cursor CURSOR FOR SELECT FIRM,LETO,GKOM,POZI FROM GKOV WHERE FIRM =@sFirm AND LETO = @sLeto AND GKOM = @sGkom OPE...more >>

I have tried several different formats to send email via a trigger with the same result. I have used [@recipients = ] an assigned variable, a field name, or have inserted an actual email address like 'hey_you@anywhere.com', but the same error appears when the trigger executes. This is the error ...more >>

Hi. I have a ASP.NET website where I need to restrict data access based on the ID of the user logged in. Instead of creating a query for each time I need to access the database I have read that stored procedures are faster because they can be optimized by SQL Server. I have 3 related qu...more >>

Hi, Is there a way to convert ANSI varchar data into nvarchar (UNICODE) data? Thanks a lot, Vad ...more >>

I wanted to know if it is possible to store html in SQL that when called onto a page, renders the html as a result of a query. For example someone selects a FAQ question, the result would be, instead of just text, a html formatted view of the data. ...more >>

I need to get the maximum date for every item number. And I need to see what the document number is for that record. This is what I have but it wants the DOCNUMBR in the Group By. I don't want to group by the DOCNUMBR, I just want to see it for whatever record is the max. Please help. SEL...more >>

I'm trying to move a 7.0 database on SERVER A to a new SQL 2000 database on SERVER B. I have my mmc console setup so I can access both servers etc... I run the wizard, but it doesn't work. I just get errors. Has anyone had any luck with this ? What6 is the preferred method for MOVING a dat...more >>

I tried to rename the Alias cy to py (see below query from Northwind DB), but it give me "Invalid object name"error. Can I do this? I end up copied and pasted the select statement (twice) into the py and cy area. SELECT cy.idx, cy.cy, CASE WHEN py.fee = 0 THEN NULL ELSE cy.fee/py.f...more >>

Hello All, I have an inspection system. Three tables are as shown below, my inspection, inspection violations, and violations tables. I am trying to create the report below that, which has the inspection dates shifted to columns. It is not like a regular rotate table query. Any help how t...more >>

Hi all, I backup database in Enterprise Manager (All Task->backup). but after restoring (into another db server), all identity columns are set to "NO" :(. what should I do to reserve "YES" in identity columns? should I use T-SQL or do I miss something? thanks anthony ...more >>

Hi My problem is : SELECT rownum = IDENTITY(int,1,1), table1.* FROM table1 INNER JOIN table2 ON blablabla WHERE blablabla ORDER BY table1.column1,...,table1.columnX Result is like following : rownum table1.column1,........table1.columnEND 1000 aaaaaaa...more >>

I want select two fields back from a table where the input prarmeter is a varchar. CREATE PROCEDURE p_getSessionBeginAndEndDate @session varchar(5) AS SELECT RegularSesBegin, Ses1Begin, EndExams FROM RegistrarDates R WHERE R.session = @session GO The question is if the last char in ...more >>

Hi all! Is there a way to store selected table row in some kind of data type? example: SET DataType = (SELECT * FROM TABLE WHERE id = MyId) Then (if exists), how to call selected values? Regards Milos ...more >>

Which of the commands below has better performance in Sql Server 2000 1) If Not Exists(Select Cliente.CD_Cliente From Cliente Where Cliente.CD_Cliente = @CD_Cliente) ou 2) If (Select count(*) From Cliente Where Cliente.CD_Cliente = @CD_Cliente) = 0) I am using this commands inside of Sto...more >>

how do i access the nth record in an ordered query where n is a variable? for example, i can do this: select max(t.tradeDate) as trddt from (select top 900 tradeDate from tradeDate where tradeDate >= '1/1/00') t to access the 900th trade date after 1/1/00. however, i would like t...more >>

I've the following query:- SELECT dbo.Invoices.ClientID AS ClientID, dbo.Invoices.InvoiceDate AS [Date], SUM(dbo.InvoiceDetails.Total) - dbo.InvoicePaidAllocatedSelectedDates(dbo.Invoices.InvoiceNo, CONVERT(DATETIME, '2003-12-16 23:59:59', 102)) AS Balance FROM ...more >>

Is there a way to create a table with and identity seed value from the maximum value of another table? The field in the other table's field is a numberic field. The transaction will go something like below, but the 111 in seed needs to be pulled from the max of another table. Thx, Scott B...more >>

I removed/deleted a database from my local server, and now I canot get into my server group because it is saying 'Cannot open user default database. Login failed' I guess I deleted the default database, but now I cannot even create a new server registration, nor can I access the properties pan...more >>

Can anyone please explain how to fix the simple (but failed as shown below) act of passing an integer argument into an ESP? The xp query: declare @ticket_type int set @ticket_type = 1 exec xp_zPrint @ticket_type xp usage: int iTICKET_TYPE = (int) srv_paramdata(srvproc...more >>

can i pass table variables in/out of a stored procedure? i am having trouble getting this to work. this seems like it would be a better approach then temp tables or insert/exec if it is possible. (still seeking the best way to take advantage of the set- base language yet keep my code mod...more >>

how do you read a text file in and update certain records within a table? I would prefer to use a sql script or stored procedure, but a DTS would be alright. Thanks...more >>

I am trying to set up a linked server to Oracle in SQL 2000 server. I set it up on my local instance of SQL and it worked fine. On our production SQL Server, I get the following error: Error 7399 OLEDB Provider MSDAORA reported an error. When I trace it in Profiler, I get the following informatio...more >>

Can someone help me find the problem in my format file for a BULK INSERT statement? I have 40 (fixed-width) columns of data type VARCHAR in a text file. My format file goes like this: 8.0 40 1 SQLCHAR 0 2 "\0" 1 COL_01_02 "" 2 SQLCHAR 0 6 "\...more >>

when we say sp_helpconstraint <my_constraint> it just displays the same name. how to see the text. How do we decide whether primary key should be clustered or nonclustered. which is faster clustered/nonclusetered index and what type of field we should select for clustered/nonclustered ...more >>

I'm trying to get a query working but having trouble when data is missing. My table format is: id1, id2, year, data1, data2, data3, data4 1, 1, 2000, 1, 3, 3, 4 1, 1, 1990, 1, 4, 3, 4 1, 2, 2000, 4, 3, 2, 1 1, 2, 1990, 3, 2, 2, 8 1, 2, 1980, 6, 7, 9, 9 I'm trying to work on pulling in th...more >>

Not sure if this is possible but thought I would ask... I have a table with the following fields: Company Fee Year The range in years go from 2000-2003. I would like to create a query with this formula: (Fee, current year) / (Fee, previous year) Is that something I can do in SQL? I c...more >>

I need to run a cursor through some records to do some work but the select statement I use needs to be determined dynamically depending on some business logic to pull different records into the cursor. I have written a stored procedure that returns the recordset that I need dynamically but I can...more >>

Hello, Can anyone help me out on what software works the best for version documentation such as changes made from on version to the next as well as changes made on the database side? Any help would be greatly appreciated! Thanks in advance...more >>

Hi, Is it possible to create a trigger or other monitoring function within SQL server that logs all executing stored procedure to an audit table. I would like to add the current user (we're using integrated security), current datetime and the stored procedure name and parameters to this table....more >>

Hi there, Does anybody know of a script to extract a numeric value from within a string? I'm trying to make something myself, but it's taking more then I figured it would. I have a column, where currencyvalues are embedded in a string(don't ask me why!). I need to extract the moneyvalue from the...more >>

Is it possible to have a job start whenever SQL Server is started? --- Posted using Wimdows.net NntpNews Component - Posted from SQL Servers Largest Community Website: http://www.sqlJunkies.com/newsgroups/...more >>

I have a .vbs file on the SQL Server (2000) that I need to execute from a stored proc. The .vbs file tabkes 2 parameters. How is it possible to call this .vbs file (including the parameters which are sql variables) from within the stored proc? Thanks a lot. ...more >>

-- below is an aspect of SQL select statements using 'not in' that may lead to unexpected results (at least for me) -- I wonder where this behaviour is defined as such by the ANSI SQL standard or is a 'feature' of Microsoft SQL -- -- I am using the 'pubs' database in the SQL Query Analyzer to sho...more >>

Hi, I new user, and i need import data from text file. This ok, but i need this: -tranform text file - ok -import data - ok - see table existing, if no create, else insert. - see one by one registry existing in table, if yes, update, else insert. I use DTS with activex in vbscript. I nee...more >>

I need advice on database design please using FP2000 and Access 2000 on a WIn2K Pro running IIS. The project involves being an teacher's agency, where we take on new teachers and assign jobs to that teacher. So, the agency will log in - post jobs for a particular teacher - then the teach...more >>

I have inherited a SQL database and two of the tables have constraints, the table I am inserting into will accept NULLs if no value is passed, so I have set up the SP as so: @DIR_SEC_EMP_ID VARCHAR(10) = NULL, My belief was that if no value was passed the variable would have the default of ...more >>

I am fairly new to sql programming, would i be able to at a certain time of the day get the server to runn a stored process that runs a query and then enter some results into a record of anothe rtable? Are their any tut's on the net for this? Willa ...more >>

i would like to use a stored proc to run a package if possible, but if not, is it possible to run a job from a sp??? basically, i have a package that loads and transforms data and i would like to do this from a call from a web page (non-asp)...any ideas???...more >>

Hallo everyone! This might be a simple and stupid question for you guys but it is annoying me bigtime ;-) can someone please help me?? I´m trying to wrtie a simple function who looks like this: CREATE FUNCTION durchschnitt() RETURNS DOUBLE PRECISION AS ' BEGIN SELECT AVG(einkaufsprei...more >>

Hello, We are having problems with distributed transactions between SQL Server 2000 (sp3a) and SQL Server 7.0 (sp4) and the option XACT_ABORT. The error we receive is: "Server: Msg 8525, Level 16, State 1, Line 2 Distributed transaction completed. Either enlist this session in a new trans...more >>

Hi I have run in to a tricky problem which is I have 2 tables which look something like this : Table - Product prod_ID ' which is the unique key for this tabel prod_shortDescription Table - Photo photo_ID ' which is the unique key for this tabel prod_ID photo_URL ' path to where this ima...more >>

I have included a UDF that I am having problems with (see below). The UDF ALWAYS returns 0 regardless of the parameter passed i.e. there should be occasions where 1 is returned. I am new to UDF's so I apologise for any fundamental mistake; any help appreciated. Thanks in advance. Tony ...more >>

Dear All, How can I get a return value from xp_startmail to determine if it is OK? BOL States.. Syntax xp_startmail [[@user =] 'mapi_profile_name'] [,[@password =] 'mapi_profile_password'] Return Code Values 0 (success) or 1 (failure) Result Sets xp_startmail returns...more >>

Hi, Is there some way to concatenate the output of a select query to a single record. for ex. in SQL Anywhere there is a function LIST select list(tname) from tab; would give a comma seprate list of all the values in tname column. is there something quivalent in SQL SERVER or s...more >>

I have a table called Test following are Columns and Data column1 column2 1 Test1 2 Test2 3 Test3 4 Test4 I want output as Test1-Test2-Test3-Test4 How to do in SQL Query...more >>

Hi ! I want to read the logical_name of my backup in a variable ! How can i do it ? Greetings, Daniel...more >>