Is there a way to generate a sql-script of an excisting table as an insert command. Got a sqldatabase containing a table I am using as a reference table. Easy to create a script that generates a copy of the database. But, that script is creating empty tables, and for the reference-table I ...more >>

Hi there I'm trying to execute a statement built form a concatenated string using sp_executesql. On executing this with then param an error message "Invalid Object Name Inserted ' occurs However when executing the '@SQLSTRING' standalone in the trigger it works??? Please help, what am i...more >>

hi, i have read many time in this newsgroup where the experts say you should not store calculated fields and it should be handled at the frontend. But if i want to calculate cummulative (cummulative of 6-7 years)everyday,then running a query which calculates the cummulative for past 6-7 years...more >>

Have an employee_tbl ( employee_id,, Manager_id), need a procedure or function whose outcome will list the company hierarchy (top to buttom regardless of the level depth) of Who reports to Who based on data in Employee_tbl. i.e. John Doe Jane Doe -----| kerry ---- | Bugsie--|Joe ...more >>

I might not be explaining this correct. I have a table right now that has 7 rows. I want to delete 1 of the rows. Then I want to go back and do the rollback to undo the transaction of the delete. If it works the way I understand, I should do a select statement and see the 7 rows. Delete the 1 ...more >>

I was thinking of a stored procedure that would select the top 20 rows in a table and count words on a text column, returning the top 'n' words in descending order of occurrence - not counting some common words (like on, in, at, the, for, from, now, etc...) Any ideas or routine ready to use...more >>

Hi, I want to find the total tasks completed and tasks planned, for a particular department, for each month specified in tblMonthEndDate, which has one column being the end of each consecutive month. In a separate table, tblTasks, I have the following columns, TaskID, DepartmentID, Plann...more >>

How are these placed exactly in the program. Do you run at 1 time or different? Example: begin tran delete from enc where epsid = '23232' commit I ran the above then ran the following to undo: begin tran rollback tran That was it. I am not entirely sure how to put the transactio...more >>

Hi I am looking for a solution to get an incremental row number along my other select arguments after joining more than one tables. Thanks in advance. Ashok ...more >>

how do i call a stored proc that looks like this from ADO.NET? ALTER PROCEDURE new_pzGetIDList ( @foo varbinary(8) ) i have it as a System.Byte[4] in C# ...more >>

Hi All, I'm trying to fix all the orphans users for all the databases in my sql server 2000 using the code above, but I'm getting an error that the second cursor already exist. Can I do a cursor inside another one? How can I fix this problem? Any ideas? Tks in advance JFB DECLARE @DBN...more >>

How are these placed exactly in the program. Do you run at 1 time or different? Example: begin tran delete from enc where epsid = '23232' commit I ran the above then ran the following to undo: begin tran rollback tran That was it. I am not entirely sure how to put the transactio...more >>

in sql server a stored proc selects a varbinary(8) how do i get to this in ado.net? ...more >>

Hi, I need to know the default values of the parameters of an stored procedure for a db-wrapper I am doing. The information seems to not be in the syscolumns table and it is not accesible from DMO either... so... does somebody know how to get this info? Thanks a lot... José Araujo ...more >>

From a VB.Net application, I would like to use SQLDMO to see if a DTS job of a specified name exists, i.e. I want to see if there's a DTS job named "FRED". Unfortunately the richness of the SQLDMO is hand-in-hand with complexity of the same, I can't figure out where to find it. (1) Can it ...more >>

Is there a clear/easy function in t-SQL that will convert an INT to a hexadicimal string? If so, what is it and what is the syntax? If not, has anyone written a stored proceedure or user defined function that I can use? Where would I find the code? Any help would be greatly appreciated....more >>

It happens more often than not. A stored procedure starts out at reasonable size and then the requirements change so the proc gets patched. Eventually, the only one who can read it is the one who wrote it. I am asking for a Style Guide. Indent levels, fence matching, but most important to me ...more >>

[code] /* Establish a test connection to remote SQLServer in J# using JDBC */ package JSharpConnTest; import java.sql.*; public class JSharpConnTest { private static ResultSet rs; private static Connection conn = null; private static Statement stmt = null; private static final Strin...more >>

I am refactoring stored procedures that use dynamic sql. The reason the store procedures use dynamic sql is because the data that is need comes from another MS SQL database that resides on the same server instance. The following is a code example from a stored procedure: declare @theDatab...more >>

If I have stored proc that returns an number of rows....how can I put this into either a) a temporary table OR b) a cursor so that I can use it in a join with another select? Eg: I have a stored proc called as follows: exec dbo.spsAttendanceClassesMissing 'A', 2005, 1, 'Feb 15 2005 12...more >>

Hi! I would like to know which decimal separator is used on a computer, from a stored procedure. My stored procedure will insert a decimal value into a string, that must be formatted in a special way, and insert the string in a table. The problem is that decimal values in the string MUST HA...more >>

What would the best way be to programmatically determine if SQL Server is installed? Looking in a specific folder on the C: drive would not be useful, since it might be installed in a non-standard location. And scanning all available hard disks is not very desirable. The best solution wo...more >>

My application uses a View stored in a database. I have queries that join this view with other tables. I'd like to eliminate the view. For example, if the view was defined by: CREATE VIEW dbo.AcctBalance AS SELECT Acct_ID, SUM(Amount) AS Total FROM dbo.Sales GROUP BY Acct_ID My VB code (...more >>

Hello there I have huge place with huge database It's log file is growing very fast Is there a way to shrink it from time to time or remove old changes? ...more >>

Hi I'm using SQL 2000,how is it possible to control a value of column to get the ollowing result? Emp (EmpId PK,EmpName,EmpCode) EmpId EmpName EmpCode ------- --------- --------- 100 John 3 101 Anne 5 102 Robert null ...more >>

I have two columes F1 and F2 Both are NUMBER but were orginally imported from a TXT file. Is there any way to ENSURE, that there are no spaces and such inside the fields to ensure proper matching? i.e. if I am trying to match F2 with F2 and one records is "1" and the second is "1 " (space ...more >>

First off, let me say that I know that this is a cross post, but I was not really sure where this fell in the spectrum of newsgroups and I am really stuck and could use the help, so I posted it to both this one and the datamining ng. I do not normally crosspost, but I did not want to miss the a...more >>

Hi, I need to add a column to a table that is being replicated. Can I just do the ALTER TABLE ADD xxxx bit then make a call out to sp_replAddColumn? When I do so, the sp fails saying the column exists. The manual says that it adds the column to the publications, but does not suggest it ...more >>

I know this is a reach, any way to encrypt things using the SHA1 algorithm in SQL? Thanks ...more >>

I have a able with f1 and f2 both numbers There are dups in the table What I want is to return F1 when there is more than 1 distint f2 records with the same F1 information. so assume this table f1 f2 1 1 2 2 2 3 4 4 4 4 4 4 It would return on...more >>

Is there a way I could return a single row (summation) for the entire year? I belive that I could use compute sum, but isn't there another way to not get all these rows? Is my query constructed in such a way that I can't get there from here? Can anyone suggest a differnt format. The fol...more >>

I got myInvTable, myCompanyTable, I need to update CompanyAdd1,CompanyAdd2,CompanyAdd3,CompanyAdd4,CompanyTel,CompanyFax,CompanyContact. How can I write it by one SQL statment ?? Thanks a lot ...more >>

Before I do performance check for stored procedure, I always clean the caches using the following commands. DBCC DROPCLEANBUFFERS Go DBCC FREEPROCCACHE GO But sometimes I think it's annoying as it basically purges all the caches from the SQL server. So if I just t...more >>

I have code that builds up a list of all tables requiring a column size change and then executes the alter table command in dynamic sql via a cursor. problem is that sql server will not allow column to grow in size (char datatype) if there are any PK or indexes on that column. what is the best...more >>

Hi, I am trying to write a stored procedure where it brings back distinct names. I basically need to bring back every record (roughly 2500), in some instances their may be more than one instance of a name for a property i.e. Linda Abbott, 41 Amroth Road, Caerau, Homeless Support Team, , Ala...more >>

I know this is somewhat off topic, but what type of hardware would you recommend for the following. Server with 100 users (usually 50-80) online at same time, accessing a sql 2000 database that is 14 gigs. What would you recommend for cpu type/speed, ram amount, and raid configuration ...more >>

Hi, If I am adding 3 columns of data type real in a select clause and if any one of those columns is a null I get a null back instead of reslult of an addition applied to non null columns. So I used isnull(field1,0) to convert it to zero so I get some value back instead of a null. i.e. s...more >>

Is it possible to create a custom word breaker for SQL server? I've found documentation for sharepoint and other technologies but SQL server appears to be different and there are very few articles about customization (beyond editing the junk word lists) for it. ...more >>

Dear Friends! I have a stored procedure which uses few tables. One of them is a very big table which has more than 3 crores of records. whenever that stored proc is executed invariably blocks would come in my database. Now, here I want to know is there anything like in oracle, sql server has...more >>

I have a SQL 7 server which has a DTS package in it that sends mail using CDONTS. Basically, if an order is received, the customer's e-mail address and so forth is retrieved from within the database, and a summary invoice is attached and mailed to them. Is there a way to program the DTS so that...more >>

I realize that xp_SMTP_Sendmail doesn't have query support like xp_sendmail. Are there any workarounds to this? I want to query a table and send the results as an attachment. Sample code would be great. Thanks to anyone who could help. ...more >>

Our SQL Server 2000 has had the following 2 messages show up in the SQL Server Logs in last 2 days. IO is frozen for snapshot IO is thawed. Can anyone give me any information/cause on them? Thanks. David ...more >>

I have a query but when I order by one of the elements I get this error: Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) [Microsoft][ODBC SQL Server Driver][SQL Server]ORDER BY items must appear in the select list if SELECT DISTINCT is specified. /BlueDot2005Beta/Intranet...more >>

Looking for opinions: What are some of the preferred methods for maintaining data integrity during updates. In other words, how to prevent one user from overlaying data that another user just saved. If two users are looking at the same data on two different PCs. User 1 changes data and save...more >>

Is there a way to determine if a trigger fired due to an insert or update operation. I need to know this for the following reason. I have fields in my tables that I want to initialize only when a record is inserted. If the record is later updated, I do not want these fields changed. I also...more >>

I'm using a query similar to the following example. To my best understanding, the subquery would return NULL (thus t1.AsOfDate = NULL). Should I get an error? I'm using a similar query for some duplicate scrubbing. DECLARE @t1 TABLE (AID INT, BID VARCHAR(5), AsOfDate DATETIME) INSERT @t1 ...more >>

Why after this trigger has completed is the value in tmp null? Its driving me nuts! tbl_Criteria just has 1 column with 1 row ct_accountant_1 varchar(30) CREATE trigger tg_Accountant on dbo.tbl_Criteria For insert,update as SET NOCOUNT ON SET ANSI_NULLS ON SET ANSI_WARNINGS ON if ...more >>

I am trying to give limited privileges to a login account on a table, but it doesn't seem to work. I have a link table in Access 97 and my SQL server is version 7. Some columns are supposed to be read only and some columns are supposed to be read-only. I use the script below revoke all pri...more >>

Hello everyone, I have a problem. I need to insert records from table2 to table1. In table1 there is a record id which serves as the key of the table. Each time a record needs to be inserted, a record id is created. This record id is the max(recordid)+1. I wrote the following stored pr...more >>

I have a stored procedure in SQL Server which is a simple SELECT, with a simple WHERE. The table on which I want to do the SELECT is pretty big, 1.300.000 registers. The SELECT is simple... SELECT Table.* FROM Table WHERE Table.Field = @strField where @strField is a varChar(12) passed a...more >>

I would like to put together a T-SQL script using Table A that has all of the table names. The T-SQL will read the table names and generate the sql statements to indexes for these tables. Table A will be in the same database as the tables that would like to create the indexes. I know...more >>

Hi, I have a stored procedure that is giving me problems. When I execute it I am getting the following error Server: Msg 248, Level 16, State 1, Procedure sproc_GetOrderID, Line 24 The conversion of the nvarchar value '10021620059' overflowed an int column. Maximum integer value exceeded....more >>

Hi there One more question: how do I run a DTS package from within a SP? The DTS is called dts_LisImport and the server is ADC-SRV1 I tried "execute DTSRun dts_LisImport" and got "Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'DTSRun'....more >>

I am having a sorting issue that I don't understand. The following SQL sets up a table with a few lines of data and then executes a select with Order by clause. I believe that the sort results are incorrect and that the rows that have the "@" sign should actually be at the bottom of the resu...more >>

Hey all, Lets say I have stored proc "x" and "x" pulls it's data from a certain table. However, on that table there are 3 indices and each index contains the field specified in "x"s WHERE clause. How does SQL Server decide which query to use? IOW, I guess my root question is, what is the be...more >>

Hi, Selecting a non-existemt field or function in a select using SQLHelper dows not throw an exception, how come? Dim DataReader As SqlDataReader dim m_SQLString as string = "select NonExistentField from MyTable" Try DataReader = SqlHelper.ExecuteReader(m_ConnectionString, CommandType....more >>

I have a system with a lot of deadlock searches appearing in the log, but they usually do not find any deadlocks. message in the log: End deadlock search 21022 ... a deadlock was not found. How is deadlock search triggered? Is it configurable? I think performance is degrade by the deadlock ...more >>

Hello I need to runa procedure which deletes all data older than 3 months on the successful completion of backups in a Datbase Maintenance plan. Any ideas - please help :) Melanie *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get re...more >>

Hi experts, I have created a SQL cursor for records processing in a stored procedure. I probably can use the @@Cursor_Rows function in order to obtain total rows of record contained inside the cursor. But once I declare the cursor as FAST_FORWARD, it always return me -1. I need to declare t...more >>

Hello Our client would like to write all table data changes to one audit table (i.e. INSERT?UPDATE/DELETE) of every record in one table column numbered COLUMN1-COLUMN50, including UserID, DateTimeStamp, TableName and Type of change (I,U,D) Is there a way to do this? The Audit Table would ...more >>

Hi all, i have SP where i need to pass the tablename and the date as the parameter i started like this CREATE PROC SP_DEL_STG_TAB @TABLE_NAME varchar(50) , @PRESENT_REC_LD_DT DATETIME AS EXEC ( 'DELETE FROM '+ @TABLE_NAME +' WHERE LOAD_DT =' + @PRESENT_REC_LD_DT) GO ...more >>

Hi there, Two tables: tblTestImport Reference varchar (10) PK, PlannedEnd smalldatetime, CompletionStatus smallint, OutCome smallint, ProvSpecLData1 varchar (15) PK, ProvSpecLData2 varchar (15) tblTestImport2 Identical to above but without PK's There is a SP that deletes all from tblTes...more >>

Hi all! I have a problem.. during an update statement the database is locked by a process generated by the system stored procedure sp_droporphans...I wonder why? the statement is: update tableA set field1 = 'AA' THANKS! ...more >>

As well known a DATE or TIMESTAMP type can be converted (to VARCAHR) after selection but BEFORE writing to the ResultSet by using the CONVERT function e.g. SELECT CONVERT(char(10), MYTIMESTAMP, 101)) FROM ... WHERE .... Is there something similar if the original field is a DECIMAL/NUMERIC fi...more >>

Hi, In DMO application I can avoid displaying the error source by using SetODBCPrefix(FALSE) method. Is there a way I can do the same on ADO connection? When I iterate through eh ADO Errors collection I want to avoid displaying the error source. E.g. instead of "[DBNETLIB][ConnectionOpen ...more >>