Hi, I am trying to automate the process of checking error log. Below is the script that I use... set nocount on create table #temp_read_errorlog (error_log varchar(500),row varchar(10)) insert into #temp_read_errorlog exec master..xp_readerrorlog select distinct error_log from #tem...more >>

Hello, I am hoping you can help me with the following problem; I need to process the following steps every couple of hours in order to keep our Sql 2000 database a small as possible (the transaction log is 5x bigger than the db). 1.back-up the entire database 2.truncate the log 3.shrink th...more >>

Hello, I am hoping you can help me with the following problem; I need to = process the following steps every couple of hours in order to keep our = Sql 2000 database a small as possible (the transaction log is 5x bigger = than the db). 1.back-up the entire database 2.truncate the log 3.sh...more >>

Hi everybody ! I have stored procedures which make copy of tables from one SQL Database to another SQL Database I got into timeout frequetntly. For 5000 records, a copy made from one table to another table in the same database took about 2 seconds. The same copy made with two differents da...more >>

How can I encrypt all the user Stored Procedures with a single script or command. Thank You! Toeen ...more >>

I want to access binary data field in SQL SERVER 2000,its length more than 255.How to access the data by db_library?...more >>

How do I write a Insert,Update trigger on system table Sysobjects. ...more >>

We have a file whose contents we want to bring into a temporary table. The first record in the file is a header record followed by the details. We want to skip the header record when we use the -F option it skips the first detail record as well. The header record is of different format and shorte...more >>

I'm building a dynamic stored procedure with a number of values passed to it. I noticed some weird behavior on one of them. I want to test against minimum and maximum values and I think my problem is a limited understanding of the type conversion in T-SQL. Example parameter definitions look ...more >>

I am having trouble with placing an exec within a SELECT statement to a stored procedure. Example: DECLARE @spName varchar(40) SET @spName = 'sp_MySP' SELECT * from myTable WHERE myId IN(EXEC @sp_MySP) I keep getting a syntax error , near EXEC Firstly, is it possible to run an EXEC ...more >>

What I am trying to do is setup my stored proc so I can pass in a parameter and change what it is sorted by at exec time. @sort1 varchar(20) Select * from @table_var order by case @sort1 where 'FINAME' then FINAME ...more >>

Newbie here - I have some might fancy dynamic HTML code in my new stored procedure but although the procedure is running without errors, it is not doing some of the functionality I expected. I saw in one example that someone used a 'PRINT' command to send out debug output. I tried this - but w...more >>

***** Why don't you just add a UNIQUE constraint on EmployeeID? Why does the EmployeeID need to be reemployed? Are EmployeeIDs that valuable? ***** The reason for this is these fields are actually part of a table that manages application users. The EmployeeID is actually a foreign key that ...more >>

Hi, This one *really* beats me! To illustrate the problem I will use the "Order Details" table form Northwind as an example. Only OrderID, ProductID and UnitPrice are used for clarity. Below is a fragment of the table. What I want to accomplish is to return an identical formatted table b...more >>

ASP 3, SQL Server 2000 I have a table called TKT_Ticket, and for each row in that one, there are 0-many in the table called TKT_History. Because of programming requirements, I have found it necessary to add a new row to the History table each time a new row is added to the Ticket table. That'...more >>

How does one drop all indexes in a SQL Server database. Is there a script for this? Thanks- Mike Wilmot...more >>

I get this in query analyzer after inserting records "Warning: Null value is eliminated by an aggregate or other SET operation." Lasse ...more >>

Hi, Running windows 2003/iis6/sql server 2000 working on an online bookstore. I have 2 tables Titles isbn char(10) pk title varchar author varchar ....other fields not relevant to the question Searches searchid int identity pk userid int isbn searchdate I want to retrieve isbn, title, a...more >>

Thanks, Pedro Rodrigues pedro@markdata.pt ...more >>

Given the following table.... ******************************************************************* CREATE TABLE dbo.Table1 ( UserID int NOT NULL CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED, Fname char(10) NOT NULL, CompanyID int NULL, CompanyUserID int NULL ) **************************...more >>

I was wondering if it is possible to alter an existing column through ALTER TABLE to be an identity column or can that only be done through adding a new column / creating a new table? Thanks. Jon ...more >>

Due to the nature of the business I'm working at, I have multiple databases which contain the same table schemas but must be separated due to contract issues. Anyway, in order to centralize interfaces to these tables I tried to utilize the Inline Table functions but found that there is a ...more >>

Hello there I have some procedure that should work toghether. If it's been stuck on the middle all the process should be failed. For this i use Ttansaction: On the beginning of the procedure I add BEGIN TRAN [tranName] and on the end COMMIT TRAN [tranName] If an error occured in the pro...more >>

I have a dynamic sql stored procedure, that is used to select data from the table for all the Orders that fall between a begin date and an end date. The orderdate field in the table has a datatype of datetime. When I run the stored procedure below, it returns me no records even though there...more >>

I have this table: CREATE TABLE #TempDiscount ( TD_ID [smallint] IDENTITY (1, 1) NOT NULL, TD_Date SmallDateTime, TD_Out bit, TD_Afternoon bit, TD_Rank TinyInt) I want to group it like this: select min(TD_Date) as StartDate, Max(TD_Date) as EndDate, (select TD_Afternoon from #TempDis...more >>

Can anyone point me to a script that will delete all indexes on all tables (non-system) in a given database? Thanks- Mike Wilmot...more >>

is it possible to specify two scenarios when using a case statement in sql: i need to sum t2.amount when t1.tran_type_id = 7 AND t2.gl_account_id = 40 but sql doesnt seem to like this syntax: SUM(CASE t1.tran_type_id, t2.gl_account_id WHEN 7, 40 THEN t2.amount ELSE 0 END) as ar_cob...more >>

I do not want to return any records from exec but i want to know if any records exists. My query is dynamic sql. Is it possible to accomplish.? use pubs go set nocount on declare @query as varchar(300) set @query= 'select * from sales' exec (@query) print convert(varchar,@@rowcount) ...more >>

I am trying to transform a row into multiple rows as efficiently as possible. Here is an example: (Column header above a sample record, comma deliminated) Origin, Destination, Begin Date, End Date JFK, LAX, 01/01/2003, 02/01/2003 I need to transform this information so that there is a rec...more >>

Hi, I need help on combining the result from 2 selects...they have no relationship to each other. -- The result is only 1 column use northwind select regiondescription from region where regionid = 2 result: Western --The result has 3 columns use northwind select employeeid,lastna...more >>

Hey everyone: Got a problem that I am trying to solve and can't find anything on it that is relevant. I've got a view that returns some rows, of those rows some of them need to be updated, which I do in a stored procedure with a temporary table. I then select everything from the temp table....more >>

Hi, How can I qualify a table including the server name. I need to use a UNION between 2 tables from two SQL 2000 servers. Server1 Table1 Server2 Table2 -- Thank you, Doru ...more >>

I know there's got to be an easy way to do this, but I can't find the syntax... I want to select a list of items into a single column (a cursor, really), so that what I have would be the equivalent of: CREATE TABLE #tmpFoo (foo varchar(100)) INSERT #tmpFoo VALUES ('variable number 1') INSE...more >>

hello, I am using a view that is doing a JOIN on 3 table. The problem is that I need to specify a WHERE clause. here is my view : VIEW01 = SELECT dbo.member.name, dbo.member.smartPunter, dbo.member.pincode, dbo.member.suspend, dbo.member.online, dbo.member.phone, dbo.member.email, dbo...more >>

Hi all, Have a table, say tab1(any schema, few rows) and I want to execute this query through DTS select * into tab2 from tab1 select * from tab2 and store the result in a text file. I am using following object. 1. An ExecuteSQL task against the SQL Server connection for your SELECT ...more >>

In MySQL you can do the following to rename a column: "ALTER TABLE [Table_Name] CHANGE COLUMN [ColumnName] [NewColumnName]" The same statement fails in SQL Server. Is there a way to change a column name in SQL Server? Thanks, Brian ...more >>

BlankHi Friends, I would like to see the database name dynamically. Please let me know how can I achieve this ? Thanx in advance. --Amit ...more >>

I am trying to copy data from 1 record to another record in the same table. We have 1 record with data in 4 of the fields that we want to copy over to another record, where those fields are empty. Instead of populating all of this data manually, is there an easy way of doing this with a sc...more >>

The largest variable you can define for dynamic SQL is 4000 bytes. What do I do if I want to execute a statement using sp_executesql that is larger then 4000 bytes? -- BV. WebPorgmaster - www.IHeartMyPond.com Work at Home, Save the Environment - www.amothersdream.com ...more >>

I want one of my joins to join the result of a stored procedure. The stored procedure accepts a datetime parameter that I want to come from some field from the existing join. It's like this, my stored proc is: --------------------------------- create [...etc] my_stored_proc @dteDatePa...more >>

I'm having a problem with the below Update Query. I'm getting an error message that tells me there is a syntax error by the key word Inner. Can someone help? UPDATE proddta.F4072 INNER JOIN proddta.F4094 ON proddta.F4072.ADICID = proddta.F4094.KIICID SET proddta.F4072.ADFVTR = 146800 WHERE ...more >>

When I open the stored procedure xp1 in the Enterprise Manager it just show "delete from xtable". Why is that it doesn't show the next SQL statement i.e. "INSERT INTO XTABLE VALUES (4, 'local') create table xtable ( id int, name varchar(20)) insert into xtable values (1, 'First') insert i...more >>

BlankHi Friends, I would like to see the database name dynamically. Please let me know = how can I achieve this ? Thanx in advance. --Amit...more >>

I'm continuing to work on subtotals by date range. David Portas yesterday prodided me with the following, which is great way to do subtotals by month! <<SELECT DATENAME(MONTH,MIN(fldEventTime))+STR(YEAR(min(eventTime)),5),count(*), fldEventTime FROM tblAuditLog GROUP BY YEAR(fldEventTime...more >>

Hi, I am aware of the ability SQL has to encrypt the SQL of a Stored procedure, that would stop somebody doing a Sp_helptext against it. How good is this though, and is it good enough to use in a 'box' product for commercial use. Other MS products such as word, and older windows had pwd f...more >>

Hello, I access 4 tables, and "determine" the candidate rows to include in the result set (accomplished through nested subqueries). I now need to compare each row of this result set to all the other rows in the result set to determine which of the rows are "correct". I can declare a...more >>

Alright, I need to guarantee uniqueness for a value in a column... however... Whether or not the value is unique depends on the value of another column Basically there are two columns I care about "EmployeeID" and "RecordStatus". EmployeeID stores the EmployeeIDs, while RecordStatus is a bit tha...more >>

Hi! I have a table with Password as one of the columns. Does SQL Server support any kind of encryption/Decryption which i can apply while storing/retrieving the data into/from this column. Also is there any way so that i can have a filter sort of SP. ie. Any command on my db should pass t...more >>

I'm receiving the following error message when trying to exec an extended stored procedure. All variables have been declared. @ticketno is an int field, so I'm trying to cast as nvarchar. -------- Server: Msg 170, Level 15, State 1, Procedure emailupdate, Line 34 Line 34: Incorrect syntax nea...more >>

Hi, I heard from Itzik Ben-Gan that one guy solved the probem , just could not remember what is its name of sp I think if you have let me say two databases with the same structure you can put your stored procedure into the master database and make it avaliable to serve like a system procedure...more >>

I am using ADO 2.5, vb6 and Sql server 7. I have the following stored procedure Create stored procedure FindItem ( @Description nvarchar(50) ) As Select * from parts where parts.description = @description Return When i use the command object the stored procedure works as expecte...more >>

When passing large string (varchar) parameters to an extended stored procedure (SQL Server 2000), is just a pointer to the string passed to the XP? Or is there copying of the large string going on, placed on the stack etc and a pointer to that is passed to the XP? I need to pass a large var...more >>

A few questions regarding adding to a view. What values are added to the original table? What happens when 2 columns are combined to form 1 in the view? How about when calculations are performed on a number before moving it to the view?...more >>

I am using the debugger in Query Analyzer. The stored proc has date as one of the parameters. I entered the date in many formats ('20030101' and 01/01/2003) but it keeps saying casting error in the date format. What is the format to be used for an input date parameter in the debugger? Thanks,...more >>

Hi I am kind of new to this work and have hit a bit of an issue. Using SQL Query Analyzer I am retrieving data out of one system that is from a field of size 4, the format is 90.0 so a number to 1 decimal place. I need to get this date into a format that is three characters and a whole numbe...more >>

I'm attempting to return a table with the following code: SELECT order_type, ship_date, count(value) AS OS1 FROM ##bbot_os1 WHERE value = 'Y' AND box_desc = 'FULLCASE' GROUP BY ship_date, order_type However, when I do this, anything with a count(value) ...more >>

I have a table like this: ID-IDCompany-DataStart 1-1-01/01/2000 2-1-12/12/2001 3-2-02/04/2002 4-2-06/08/2001 5-2-07/02/2002 6-3-01/04/2003 7-4-05/01/2002 8-4-02/04/2002 I want to get the IDCompany (Distinct) with oldest Datastart. tnx...more >>

Is it possible to access SQL from IIS/ASP without creating a DSN?...more >>

I am using MSSQL 7. In triggers I write, I want to breakdown an error message to multiple short messages on multiple lines .... i.e. begin select @errno = 30003, @errmsg = 'Error line 1!' Error line 2 Error line 3 .... goto error end How do I accomplish it? Thanks fo...more >>

question: what's the easiest way to pass a set of records to a stored procedure? cursor variable? input parameter of type table? (can't seem to get this to work) global temporary table? much appreciated!...more >>

Hi, When you have a lot of stored procedures it seems like being able to produce a diagram showing the dependencies/relationships would be one of the best ways of documenting them. It would give an immediate overview of the code and make it much easier to understand. Ideally I think you coul...more >>

Hi, Regarding to my previous posting I tested some more with stored procedure and errors. When I call Store Procedure SP2 from SP1 and en error occures in SP2, this error was not returned to SP1 (@@error = 0). SP2 begins with a transaction and commits or rollsback. On a failure I use rai...more >>

Hi All, I'm trying to pass a varible (the table prefix) to a stored proc, dynamically build the select, then do a select on that table. There is another small hitch here in that the table is in a different database but on the same server. Something like (I know this is entirely wrong it's ...more >>

With a good tip from David Portas in an earlier post, I have successfully used the OLE automation exented stored procedures (e.g. sp_OACreate, sp_OAGetProperty) to gather disk space information. However, I see there is a Drive Collection available. I found the VB example below in MSDN. ...more >>

Hi, does anybody know how to trick the SQL server into=20 recompiling the views? The problem is this: I use serveral views, which select -=20 among others - all columns of one table (select items.*).=20 However, after adding two new columns to the items table,=20 the result set delivered b...more >>

Hi all, Have a table, say tab1(any schema, few rows) and I want to execute this query through DTS select * into tab2 from tab1 select * from tab2 and store the result in a text file. I am using following object. Microsoft oledb provider for SQL server Transform data task text...more >>

Hi all, Here is my query: SELECT RSG.SortOrder, RSG.RevSumGroupName, RSD.RevSumDivisionName, RSC.RevSumCategoryName, SPL.SummaryPricingLevelName, BPL.BudgetPricingLevelName , FQ.FiscalQuartername, 'Amount'=(isnull (QH.Amount,0))/1000,BPL.BPLType,Annuity=IsNull (BPL.IsAnnuity,0) FROM WI...more >>

Hi, table with two fileds field1 field2 data datetime How can I select the latest copy of unique data from field1? Assuming data+datetime is always unique and there are duplicated data and field1 Thanks ...more >>

Using SQL Server 2000, how do I get a disk's total space? I can find solutions for getting free space but for more useful monitoring I want to know what the total disk size is as well. Clive...more >>