I need some inputs for SQL Server for, 1. Locking of records and tables. I know table locking is supported but would like more inputs on the same on how it should be done. Also I would like to know more on row/record level locking. How to lock and unlock ? 2. Also some tips on performance from...more >>

I have installed an application made with VB6 on a computer, and have installed MSDE 2000. I've copied the .ldf and .mdf files in the appropriate folder. However, the login information that is contained in the application doesn't allow me to access the database. I was able to access the databa...more >>

Hi All, I am executing a DTS Package from with in a Stored Procedure Like: SET @CMD = 'DTSRUN /S ' + @SERVERNAME + ' /E /N ' + @DTSNAME + ' ' + @GLOBLEVARIABLELIST + '' EXECUTE @ERR_NUM = master..xp_cmdshell @CMD The 1st Time when I call Stored procedure from My Code. It is Not Executin...more >>

Hi I am wanting to to a bulk insert task in a DTS package. This bulk insert must transfer data from a flat file that has 27 columns delimited by the ~ sign into a table that is only 7 columns. So i have to selectively choose the columns i want using a format file. Until now i have not gotten t...more >>

Is possible create query, which contains binary sum of column. Something like SELECT binarySUM(myColumn) from myTable ...more >>

Hi, Is it possible to change SQL server authentication mode to mixed after installation? Thanks in Advance ...more >>

Some weeks ago, I figured out how to update a column (type image) with the contents of a file using t-sql. I seem to remember it being short and sweet - but I can't find the syntax now for the life of me. Anyone know how to do this? Thanks, Mick. ...more >>

Hi All, I am executing a DTS Package from with in a Stored Procedure Like: SET @CMD = 'DTSRUN /S ' + @SERVERNAME + ' /E /N ' + @DTSNAME + ' ' + @GLOBLEVARIABLELIST + '' EXECUTE @ERR_NUM = master..xp_cmdshell @CMD The 1st Time when I call Stored procedure from My Code. It is Not Executin...more >>

Ineed to install a Windows 2003 32 bit Enterprise Edition in cluster that only will run a SQL Server 2000 Enterprise Edition I want to know what is the best configuration for the "Allocation unit size" The SQL2000 take the allocation unit size configured in the OS or we can change that value ...more >>

Hi, what is the difference between xfloat (numeric) and decimal type, and when should I use xfloat (numeric) and decimal? Thanks, Lasse ...more >>

Re-posted as still not solved. Please read from bottom up, any help much appreciated. Wayne, Dan & Roji, Thanks for your advice guys. Unfortunately I've tried everything you suggested and the SP still fails. Obviously I can now be <more> certain that the options were being changed for e...more >>

I'm trying to resolve a performance issue on my app. I've got an ASP App using com+ components coded with vb6. Both ASP pages and COM components reside on a server. On another server I've got my SQL database. When I test my page with this configuration I've got really important performance ...more >>

We are building a website for both an American and Asian market that will be SQL 2000 on the backend, and I am looking for any special schema/design issues I need to be especially aware of when it comes to Asian characters (double-byte). For example, are there special type char definitions I need...more >>

I have an install which works on 99.9% of machines (98, 2000, XP) On one Windows 2000 (SP4) machine my install gets an error when trying to register sqldmo.dll the error is 0x80040154 (Class Not Registered). I run depends on the machine and everything is in place. Obviously I'm installing...more >>

Hello, I'm using MSSQL 2000 with SP3 I have a table with float amounts like this 7/04/2004 11:38 48 18549 14/04/2004 11:39 60 34084 19/04/2004 0:45 60 25399 21/04/2004 9:10 60 16328.5 28/04/2004 10:51 60 16399 28/04/2004 18:40 60 13562.5 29/04/2...more >>

How can I check if #tt ( a temp table ) exist ot not? Thanks ...more >>

Hello, I use Unicode in my program to enter data in Chinese into a nvarchar field. When inserting or updating records, my application does not use Unicode escape sequences but rather the Unicode characters themselves. Thus, my app may have SQL statements like: UPDATE table SET field1 = ...more >>

Hi All, In my sproc I need to select several top records of some query. The number of records is variable. Since SELECT TOP... doesn't support variable number of records, it seems I have to use SET ROWCOUNT option. The problem is that my query executes the user-defined function so setting R...more >>

I have a job that gets created with about 1300 steps in it, everytime it reaches step 835, even if I rework the steps so the are not the same but a completely different set of steps (this was a job with 2700 steps) it always hangs on step 835... The job just sits on that step for hours, I can ...more >>

Iam using lot of adhoc queries. Can any one suggest me what can i do to improve the performance. appreciate any pointers Thanks!!! ...more >>

Hello to everybody, I hope one of you guys / girls can help me out because I am pretty sad right now: couldn't find an answer on the net... We got an MS SQL Server 2000 on a Windows 2003 Server and one of the tables in my master-database is "out of integers". Well... we imported a bunch of ...more >>

How can I do if, for example, an hacker develop an application that, through http URLs does 1000 requests a second to my pages that do intensive queries? Any help appreciated. Regards. -- Fabrizio Maccarrone -- YAMSSQLU (Yet Another MSSQL User) ...more >>

Note 1 to 4 is all simply what I expected, 5 is not: 1) Create a table create table numbers ( val int ) 2) Insert some datta insert numbers values ( val ) values (1) insert numbers values ( val ) values (13) insert numbers values ( val ) values (19) 3) select the data select * from...more >>

Hi folks! :O) I wanted to know if these functions were reliable? What about using them to compare string in an other language such as french? What about there execution time? because I have the feeling that we shouldn't trust them... :O/ -- Best Regards Yanick Lefebvre ...more >>

SQL 2000, sp3a How do I run a job on server 1, and if it completes successfully, fire = off a job on server 2? Thanks! --=20 Kevin Hill President=20 3NF Consulting www.3nf-inc.com/NewsGroups.htm www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs....more >>

I am getting this "Connection is busy" error every time this simple statement is execute "Set rsEligStatus = comSelectEligStatus.Execute" Here is the code I am using in a Visual Basic program. I an using SQL Server 2000 with service pack 3 SQLStr = "select MemberID,EligIdentifierID,IdentEffDate...more >>

Hi, Probably an easy solution, but I can't find it. Trying to find a way to create a SQL script that either completes successfully or stops executing the rest of the code in the source file when an error occurs. Most of these scripts update a single table in some fashion. Occassionally a sc...more >>

Hello all, I would like to find out the distinct entries in this table. Basically, I think I want to turn a horizontal table vertical (not sure though) CREATE TABLE [dbo].[TestTable] ( [c1] [char] (10) NULL , [c2] [char] (10) NULL , [c3] [char] (10) NULL ) ON [PRIMARY] GO insert int...more >>

I have a trigger that will add log each time there's an update in a specific table. The problem is that I don't want that trigger to add log if the value of the record, that is being updated, is the same. For example: tblEmail -------- If before update, the value is: me@domain.com, and after ...more >>

I seem to be having problems with temp tables when accessing through an approle If I connect a web application (built with ASP front and VB middle) to the database, use sp_setapprole, and run a simple stored procedure it works. If I add the two lines "select 'output' as columname into #t" and "se...more >>

I have an asp form that posts to a SP with an update statement. I am trying to do a Change Order system where only a few of the fields will be updated at any given time. I am trying to use CASE or isnull to only update the table if the Request.Form("") is not null. My sp is: CREATE PROCEDUR...more >>

Can someone show me how to put the log size info into a table to report on?...more >>

Hey all, When I am running queries through Access to SQL, all the queries are fine, except those which have more than one join(left or right). One join is fine, but multiple joins of 2 or more, take forever to resolve. I understand that SQL is not designed for this, I was just wonderi...more >>

Hi I am trying to save an e-mail address with an '(appostrophy) in it to a varchar field and database refuses to take it. Can any body help Thanks Anu...more >>

Hi, I have a MS SQL database containing HW inventory of IBM and Compaq servers. I want to join many tables in to single one. Here are three tables for example having different number of rows and fields. Table 1 Table 2 Table 3 PK Host PK Hard_Disk PK CPU -------...more >>

Hi, I have a MS SQL database containing HW inventory of IBM and Compaq servers. I want to join many tables in to single one. Here are three tables for example having different number of rows and fields. Table 1 Table 2 Table 3 PK-ID Host_Mane PK-ID ...more >>

Hi Anyone know if it's possible to include Isolation Levels in an OLEDB connection string for SQL Server 2000? I have a 3rd party app that provides a editable textbox for connection string properties ie: Initial Catalog=pubs, and MUST specify the default locking type in the same textbox. I read ...more >>

Hi, I have a MS SQL database containing HW inventory of IBM and Compaq servers. I want to join many tables in to single one. Here are three tables for example having different number of rows and fields. Table 1 Table 2 Table 3 PK-ID ...more >>

I need to read out value of the field type "text" into a varialbe in SP. Having trouble to do it with simple SELECT as well as with READTEXT This is how I was trying to approach it: CREATE PROCEDURE my_SP (@id int, @body text=NULL) AS -- Does not work: operator = cannot be used with text...more >>

I have seen other people post that using identity isn't the best solution. We would like to create our own "identity" field in our table. I don';t know if we should use a table of values which indicates the next number to be used or just select the max() of the table at hand. The big question i...more >>

Every time I click a Database in Enterprise Manager I get an error message stating "A runtime error has occured. Do yo wish to debug ? Line 306 Error: unspecified error" This is only when TaskPad is the default view. Any Ideas ?? SQL 2000 SP3 Thanks, JIM ...more >>

Hello, I have a C++ / MFC app which uses CDatabase and CRecordset to connect to a ODBC data source for a SQL Server 7 database. The application worked properly with a MySQL database, but after swapping it for SQL Server problems emerged. In particular, any calls to CRecordset::AddNew() and CR...more >>

Is there a way to output header and trailer info when exporting a text file when using a DTS package? Any suggestions? Has anyone done this before?...more >>

Hi , Not sure if I'm in the correct NG here and not sure if this is a web development question or sql. I have an online form that inserts the data into a sql database as it should and works fine. I have been asked if the contents of each individual transaction can be emailed dynamically back to...more >>

Hi I am working on a project which is developing an application which will run on both SQLServer 2000 and Oracle 8i and 9i platforms. The views are developed on a Oracle platform and then translated into SQLServer I have been investigating differences to be aware of when creating views for thes...more >>

i just imported a table which is only about 500 rows and 25 fields large. it's just an address book basically. somehow my database went from 9MB to over 200MB!! anyone know what may have happened? thanks! i'll be taking an MS course on SQL soon. hope the answer isn't TOO obvious :)...more >>

Hey all, I have a situation where Mortgage ID's need to be formatted with padded zeros. I am upgrading legacy code, and the code is looking for a Mortgage ID with padded zeros (00000008 not 8). In Access, in the Design mode of a table, you can simply put 00000000 in the Format field an...more >>

hi. i'm having a little difficulty with this one...basically, i have a list of products and a list of invoices with products in the invoices. i'm trying to create an sp that will sum all the invoices for every account we have at the product level, even if they never ordered any of the prod...more >>

I thought I understood SP's, but apparently not. I have an SP that looks like this...in part: PROCEDURE ..... ( @VALUE varchar... ) DELETE ....blah SELECT * from table WHERE key=@VALUE GO I want a recordset returned. The above does NOT return anything. By removing the DELETE it ...more >>

I have a stored proc which fails with error code 1934 whilst trying to insert data into a temporary table This stored proc always SUCCEEDS when called from query analyser This stored proc always FAILS when called by a SQL Agent job I believe the description for error code 1934 is [Microsoft][O...more >>

Are the following identical in terms of speed/optimization, if not when would each be faster, or is one faster all the time select a.columnA1, b.columnB from tableA a join (select * from tableB where columnW = 'yes') b on (a.columnJ=b.columnJ ---OR-- select a.columnA1, b.columnB from tableA ...more >>

Hello I've two diff database with the same name and structures running on 2 diff servers. Is there a way when a record is updated in Server 1 then it should be replicated in server 2 Please note that replication process is not allowed in the server. Is there any other options of doing the same....more >>

Hello I am writing a DB for an online Catalog. The DB for the Catalog itself exists already. So now I want to implement Useraccounts. Not every User should see all artikels I thougt I could put a priority on each Artikel. So then I whant to make different Userrolls in the DB Userroll 1 should on...more >>

Hi there Is there a way of having a variable FROM table/view, as in IF (SELECT Count(*) AS Counter FROM @qView WHERE @qViewIDName = @qViewID) > 0 print 'these records exist in ' + @qVie Please keep in mind that the criteria may cause 0 records to be returned, so I didn't manage with a tempora...more >>

Im using the Row Count example from: http://www.aspfaq.com/show.asp?id=2120 But I have a Order By problem. The code works fine if I have Order By p.IDno but I want to change it to p.Projectname or p.CreateDate, Asc and Desc depending on the variables @Sortcolumn and @Sortorder. The problem is ...more >>

Hi - here's a nice challenge I have an html file in c:\temp (created by sp_makewebtask) I would like to import into a table, tbemailtemp, which has one field, HTMLDoc. The whole file must go in the first record, using a command in a stored procedure. The file length of the HTML doc is not fixed. ...more >>

Hi All, I have a simple table of transactions, and I want to select the top 3 transactions (ordered by value) for each distinct transaction date. I've been trying to do a SELECT TOP 3 * .... etc with a GROUP BY clause at the end, but it seems that the TOP works *after* the group by, ie it gro...more >>

hi, is it possible to be any difference between these two statements: select count(*) from mytable and select count(column1) from mytable ? assuming that i have a primary key defined on column1 tia *** Sent via Devdex http://www.devdex.com *** Don't just participate in USENET....more >>

Hi, I am inserting a record to a table that has a Text column. I know that I cannot used the Insert statement if I would wan to insert a large amount of data into it. So, I used the WriteText() function but it seems that it only inserts the first 256 characters of the data I am trying to insert...more >>

Erland Sommarskog, SQL Server MVP. says that we ought try not to use Dynamic SQL queries So I tried that with the code below and it worked But hoe do I change the direction of the ORDER BY without using a ynamic SQL quer regard Greg Frazer /************************* declare @col1 int,@Dir v...more >>

The data from the below table, i want to insert into another table excluding the zero qtys,ie i want to insert only non_zero data into another table. please solve my problem.. iss_quantity received_qty moved_qty alloc_qty ------------------------------ -------------------- 14 ...more >>