Dear all, I launch a statement and then I see its parameters using sp_who2. That's fine and I agree with that but then if I use sp_lock it pulls a lot of rows and if I take any ObjID with select object_id(n) always get a NULL as value when I suppose that might returns an object name. Ha...more >>

I have a table with 3 columns: number, name, rowid (and identity column). I want to return the name of the first row (first meaning lowest rowid) when there are more than one row with the same number. Given the table looks like this: number name rowid 102 bob ...more >>

Hi All I have following questions regarding C# Assembly and Threading. Let me know the precise answer or lead me to the proper materials. 1. Is memory leakeage possible in .Net Manager Code ? 2. Is memory leakage possible in .Net Unmanaged Code ? 3. How can I find the what % of memory i...more >>

I'd like to audit the changes to a table and I'd like to keep track of the sql statements used to change the table. I know how to get the system user and the app name, but is it possible to recover the sql as well? Thanks for any help. -John...more >>

Some of our developers want to automate some system for bringing over a copy of the production database to their workstations to use for test purposes. I want to break this down into two separate questions: 1) What is the most efficient way to bring the current database state over to test mach...more >>

I have used profiler for quite some time to understand what is going on in an application and to test performance. However I do not know how to judge Reads, Writes, and CPU when the numbers are mixed. For example, a redesigned query has these numbers in profiler after a few runs CPU read...more >>

I’m using SQL Server 2000 and am trying to establish a foreign key in a table that already exists. So I tried executing: Alter Table Book Foreign Key (PublisherCode) References Publisher It gives me the error: "Incorrect syntax near the keyword ’Foreign’ " Any idea why this is happenin...more >>

How would I round the result of col1to the nearest 10th with the format "0.4" for example? The value of col1 (which is an average) is 0.423 in this case but I'm getting the resulting value of ".40000" rather than the format I want of "0.4". SELECT Round(avg(cast(col1 as decimal(4,1))),1) ...more >>

How would I round the result of col1to the nearest 10th? The value of col1 (which is an average) is 0.423 in this case but I'm getting the resulting value of ".0" rather than .4. If I use 0.00 as the second argument, I get ".00". SELECT avg(COALESCE(col1*1.0,0.0)) as col1, FROM table1...more >>

We are getting deadlocks when running this code from a stored procedure many times simultaneously with 30 concurrent requests. From our understanding, repeatable read in this case should lock the single row returned from the SELECT TOP 1 statement for the length of this transaction and not al...more >>

does anybody have any clue why sometime my UDF gets hung (164k times to call that function and returns a single calculated value), but sometimes it works perfect?and when it got hung, i drop it and recreat, then it works again... looks like to me some backend is not released... thanks....more >>

I have an integer field that I want to make an identity field, with about 500 records where this field is null. Is there an easy way to populate this field with numbers that don't already exist in that field? Then can I make this an identity field starting at one number higher than the maxim...more >>

I am having trouble inserting a record into SQL using an ADO recordset. Here's the line in question: rs!ServerMessage = Data It appears that when my data string is longer that 255 characters, it only inserts the first 255 characters into the table and throws the rest away without any err...more >>

Okay, in Sql 2000, when I wanted to generate scripts, I could easily have both the create and drop commands scripted into the same document with one step. How do I do that with Sql 2005. I can generate the script for creating the table and for dropping the table, but I cannot see the flag wher...more >>

Has anyone come across the need for performing a day difference count between 2 dates adhering to a 30/360 day convention? Is there a System Function, Custom Function or Proc which can achieve this ? ...more >>

Hi all, Please let me know if I should post ddl and sample data with this but I thought the idea is very trivial. In a stored proc, @zip_code and @address are being passed. They could be passed in as null. I have following select and I was wondering if there is a **better** way of doi...more >>

Hi All I'm trying to display a html report from a store procedure but the string is to long. I try to cut the sting in parts but no luck. CREATE PROCEDURE createReport @orderNumber int AS declare @lineDesc01 varchar(8000) declare @lineDesc02 varchar(8000) declare @lineDesc03 varchar(8...more >>

We are currently addressing many performance issues in an existing application. One of the items we are investigating is adding clustered indexes to tables that do not have them. Currently, about 90 tables out of 200 don't have clustered indexes. The long-term solution is to analyze each ...more >>

Hello group, Hi everyone I localize aplication to polish language and I want change collation in database. How change Collation in data base? My database name MyDB I want collation : 'Polish_CI_AS' And my second question: How set default collation for server (for new databases) if the coll...more >>

Hi, When modifying a table through the Enterprise Manager, I can press Shift + Enter keys on the keyboard to start a new line in a certain field . I tried to emulate the same functionality using T-SQL as follows: INSERT INTO Table1 (field1) VALUES('first line' + char(13) + char(10) + 's...more >>

When I log of I do: HttpContext.Current.Session.Clear() FormsAuthentication.SignOut() The problem is that it clears the variables that I set up in my Session_Start function in my Global.asax file. One of the variables I set up is: Session("Start") = DateTime.Now.ToString Th...more >>

Hi, I'm trying to consolidate a column from the 'many' side of a relationship into a single NVARCHAR scalar value so that it can be displayed in a client application (no editing is required). I figured that I would be best using a user function called from within the originating stored p...more >>

Hello, Is there a way to get the error message in a scheduled task? I've a backup scheduled with two steps. The first step is the backup procedure and the second step is a t-sql code which fires an email to the backup operator. How can i set the error in the email message if the schedul...more >>

I would like to select the top X items in a table in a stored proc with the count X being passed in. When I use the statment: Select top @TopCount * from Table1 in the stored proc with "@TopCount" getting passed in as a var. The error is "Incorrect syntax near @TopCount". Thanks, Mar...more >>

Hi, I have 3 tables in the database. 1. Registrations 2. Employees 3. Clients ----1---- regid classid studentid reg001 cls001 emp004 reg002 cls001 clt003 reg003 cls001 clt002 reg004 cls002 em...more >>

Hi I am trying to remove the space between the words to make it one single word. ETK SETTLEM ESCROW ACCOUNT I used this command: REPLACE(String,' ','') Thank you for any help ...more >>

I am currently in the process of converting some Access functionality to T-SQL. What I need to do is connect to a FoxPro *.dbc, use the FoxPro data to manipulate data in a SQL table and then export that table to Excel. Can anyone tell me if this is possible. ...more >>

Hello all, I have the following stored procedure to create a linked server. ~~~~~~~~ CREATE PROCEDURE CreateLinkedServer @serverName sysname AS EXEC sp_addlinkedserver @serverName, N'SQL Server' GO ~~~~~~~~~ My problem occurs when I call this from a ....more >>

is there a SQL string function that can verify an existence of a character in a string? example. str='This%is a=test*of(no+value' somefunction(str,'%=*(') I want the function to return if any of the special characters was found and return its value. For the older timers... An equivale...more >>

Hi, how can i execute a StoreProcedue in SQL2000 every time i received a replay message with a specific subject, and pass some arguments like the subject. thank in advanced, Renato Ribeiro ...more >>

Im trying to write some sql which inserts these rows into another table called move. Unfortunately i am struggling trying to get the insert statement not to insert record 5 because the ToURN has been used before in a previous record(1). Does anyone know how I would write the sql to do this. ...more >>

Hi, I have a process data acquisition system running with SQL 2000 Enterprise Manager. I have a large hard drive Read activity. By using the performance monitor, I found that this reading activity is clearly associate with DBCC logical scan bytes/sec counter and is mostly twin with Physic...more >>

I have a scheduled batch update running on a sql 2000 server that invokes a user defined function which parses a varchar field value & eliminates any non-numeric characters (sorry for the sentence run-on). This function works fine when used on fields on that server. However, when used to parse...more >>

HOWTO Validate a user's login id and password against an LDAP from SQL. Please help, I want to build a stored procedure to validate a user's login id and password against an LDAP compliant server, but I do not know if this can be done with pure SQL Server 2000 (or later) transaction SQL. C...more >>

Hello, Iam having problem using SQLXML bulkload from sp_OA procedures. When I execute the script from .vbs, it works fine. But when i use sp_OA procedures, it fails on line EXEC @Return = sp_OAMethod @SQLXMLBulkLoad, 'Execute',null, @vMappingSchema ,@vXMLFile with error OLE Automation E...more >>

I have 2 tables, for simplicity I am displaying only few fields Article ----------- id title layoutID type layout -------- id type I just added a 'type' field to the article table and I would like to update it with the type in the layout table based on layout ID. something like ...more >>

I have an insert statement which insert recrods from one table into another. My problem is I am trying to introduce a where clause which doesn't insert records which have the same values in the FromURN and TOURN columns. In the example below I don't want record 4 to be inserted because it is ...more >>

Hi, I have a database that it have and int type with identity (1,1).when I add data from sql everything is ok but when I want to add data from fro example Delphi it says that field ID must have a value and auto numbering doesn't work. Please tell me how can I solve my problem. Regards Ali...more >>

Hi I am basiclly a beginner at ASP. Firstly I have an SQL ststement which works: 'strSQL = "SELECT tblContacts.* FROM tblContacts WHERE emailAddress = '" & strEmail & " ' " basically it looks at a table and finds rows which include an emailaddress = to my variable strEmail I get c...more >>

I am building an application to handle text files from ftp sites and load them into SQL Server tables as well as management functions. My goal is to use T-SQL stored procedures (and avoid DTS, if possible, since DTS ftp functions are not robust enough for my project tasks). Has anyone had e...more >>

Hi all, I was wonder if any one knows if its possible to create run and drop a stored procedure from within another stored procedure. You see i want to have a table which has serveral stored procedures stored in it as strings then use a stored procedure to extract one of these and create ...more >>

Hi, We have a table here containing over 18 million rows and is updatred to the tune of about 50,000 rows per day. Once a month I would like to run a script that will delete any rows older than 18 months. All easy, I here you say. Well when I do the following: select count(*) from MyTable ...more >>

Hi All System allows more than one .mdf in a data base. in such cases, can any one tell me where exactly data resides? Is it similar to multiple .ndf files which use Robin method? or something different Thanks in advance R.D...more >>

I have the following table and I'd like to delete rows which have the same values in the fields MergeFromURN and MergeToURN RecNo MergeFromURN MergeToURN 1 500 600 2 100 300 3 100 ...more >>

Dear fellows, It's easy, what 'indexed views'? are? I mean, purpose, use, and so on. Has anyone ever used or have any experience with indexed views? Regards,...more >>

hi i created an explicite transaction which contains 2 update statements both of these two update statements act on a single table with the name of "TABLE1". these table has two column "name varchar(20)" & "account (int)". this table also has a check constraint on the "account" column whi...more >>

Hi all! I have a question regarding the normalization rules(I think, the first). As is, I have the following tables: TABLE Customer(CustomerID, CityID, CustomerName) TABLE City(CityID, CountryID, CityName) TABLE Country(CountryID,CountryName) Where a country can have one or many cities ...more >>

Does anyone have suggestions, other than using "set xact_abort on," on how to handle rolling back a transaction after it's timed out while using connection pooling? ...more >>

I've made a table with ddl like this: CREATE TABLE TEMP_B ( [RecID] [int] IDENTITY (1, 1) Primary Key, [CODE] char(1) NULL , [BIDANG] char(20) NULL , [TAHUN] char(4) NULL ) Then, when data reach 2000 records, I delete all rows with syntax: DELETE FROM TEMP_B Now, how to reset va...more >>

hi i have the following create table #station_mix ( station varchar(100) --several other columns ) insert into #station_mix values('qtq') insert into #station_mix values('nws') insert into #station_mix values('stw') insert into #station_mix values('tcn') insert into #station_mix values...more >>

There's some stored procedure in my db. Its purpose is to reserve items (records) in a table which contains mix of reserved and available (free) items. Whether item is reserved or not depends on a value of "reserved" column. A client (web application) calls this SP, passing one parameter: a numb...more >>

What is best? What is right? --This way: ---------------------------------------------------------------------- CREATE TABLE Districts ( District char(2) NOT NULL, Constraint PK_District Primary Key(District) ) -- Items Padrão -- INSERT INTO Districts VALUES('AC') I...more >>

Is there any way to use Bitwise AND in a WHERE clause? EG: SELECT * FROM Directory WHERE DirectoryFeatured & 4 This just gives me an incorrect syntax error? DirectoryFeatured is an int field. Regards, Chris ...more >>

I want to make Store procedure. it has function for import data from foxpro. but I want it can be flexible to set location data. CREATE PROCEDURE #SP_Import_BC @Lokasidata varchar(100)='E:\agr_absen', @Tg1 smalldatetime, @Tg2 smalldatetime AS -- if exists (select 1 from sysobject...more >>

I'm sure this has a simple answer, but I can't find it. I have a VB6 desktop app, using MSDE, but I can't RESTORE the database. I get "cannot get exclusive lock in the database" (I don't remember the exact wording"). But it's a single user app, with no one else using the database I'm ...more >>

Hi, How can I disable a trigger or triggers without delete it or them? I am using SQL2000+SP2 Thanks Michael ...more >>

I have a column which has values as 1 2 2.1 3 4 2.1.1 2.1.1.1 2.1.2 2.1.3 2.2 2.1.4 2.3 2.99 2.99.1 2.1.5 2.99.1.1 2.1.6 2.100 2.100.1 2.101 2.102 2.1.7 and i need to get this sorted as 1 2 2.1 2.1.1 2.1.1.1 2.1.2 2.1.3 2.1.4 2.1.5 2.1.6 2.1.7 2.2 2.99 ...more >>

If i make a view like it: SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data Source="E:\ADO_SQL";Extended properties=DBase III' )...dav_B Rowset_1 So I always get error message ("...........SET ANSI__NULL or SET ANSI_warnigs") Is it true that I can't make a view l...more >>

Hi, I need to detach a DB, but need to force any current connections to be disconnected as I am getting an error saying that I cannot detach the DB as it is being used. What is the best way of doing this? I am using DMO to detach at the moment. Thanks ...more >>

UPDATE TDAV SET FOTO=(SELECT FOTO FROM #temp_FOTO WHERE NOID=TDAV.NOID) Where FOTO is Image field. This syntax is error. How to Update Image field? ...more >>

declare @d smalldatetime declare @e varchar(8) set @d='1/8/2005' set @e=............? I wanna set @e with format 'YYYYMMDD' of @d. How? ...more >>