hi guys, I just wanted to know how to do a row level locking when it comes to some thing like this. I do a begin transaction and I change a row. Now for another user that whole table is locked, is there any way to lock only the modified row using some kind of SQL Server setting b...more >>

How do i find all the computed columns in all tables and also what the computation is ? Thanks ...more >>

how do i write a query that deletes every # record? # is an integer every 3 record, something like this Delete * from table1 where id=id+3 ...more >>

I have a proc with a Dynamic SQL statement as follows: CREATE PROCEDURE dbo.sp_GenerateDataCaptureTerminalOffsets @batchID int, @transactionIDs varchar(255) AS BEGIN SET NOCOUNT ON DECLARE @SQL varchar(512) SET @SQL ='SELECT TransactionID,BatchID, GetDate() FROM SalesTransacti...more >>

Hi, I'm trying to pass a stored proc a list of values in a comma seperated string: ("3125,3126,3129...") The values represent an integer column in a table. Then I want to use the string to exclude the values from a SELECT as in: CREATE PROCEDURE myProc @transactionIDs varchar(255) ...more >>

I read that ROWVERSION is new for SQL Server 2000, which is formerly known as a TIMESTAMP in SQL Server 7.0. The data in a TIMESTAMP column is automatically inserted or updated every time the data in a row is changed. My first question is in Visual Basic .NET, when I call to retrieve value ...more >>

I read that ROWVERSION is new for SQL Server 2000, which is formerly known as a TIMESTAMP in SQL Server 7.0. The data in a TIMESTAMP column is automatically inserted or updated every time the data in a row is changed. My first question is in Visual Basic .NET, when I call to retrieve value ...more >>

Hello, I tried to write a system function (global, basically is what I was shooting for) and following instructions here ( http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01l1.asp ). However, I can't access the source code for the function, even though...more >>

Hi, I have a stored procedure that needs to start a job stored in sql = server. How can I check if this job is already started before trying to = start it? Right now, users are getting the "can't start job because it = is started" error, and I'd like to get rid of that. Thanks! --Michael...more >>

Hi All, Is there a way to write the results of a query to a file and save it to the disk? I know that the SQL Query Analyzer gives us the option of 'results to file'. I want to know whether I can write a script which can query the database and redirect the results to a file, so that I can o...more >>

Hi, how do I detach master db? what are the steps? ...more >>

I shouldn't be trying this unsupervived :) but I want to learn. In the stored procedure below I want to add a new table refence. More specificly I want to add CODE.SIZE to thsi stored procedure. This is a new field I want to add to my crystal report. Thank you very much, G CREA...more >>

Hello All, I need to make a select in a table, but i need to do a order by by parameter. Exemple: SELECT * FROM CUSTOMERS ORDER BY @COLUMN This select is a stored procedure. @Column is the name of the column in my table. @Column i need to send by parameter. I need to make a dynamic s...more >>

Is there a way to see the Selects and Updates that are being executing in my Stored Procedures? I can see the Stored Procedures that are executed, but I need to see exactly what the Update and Select looks like to track why my records are not getting updated. Thanks, Tom ...more >>

Is it possible to create a relationship between two tables in different databases or between a table and a view which is based on a table in another database? If this is not possible, is it possible to setup RI between two databases using the triggers? Thanks. ...more >>

I have several rows that got imported into floats with the -1.#IND value. I want to run a query to find out which rows it is but I always get a divide by zero error. I tried select * from mytable where mycolumn/1 = 0 ...more >>

TblA TblB ------------------------------------------------- ColA int Identity ColB char(5) ColB char(5) FK ColC char(6) ColD char(50) I would like to add rows to TblA where TblA/ColB would be populated from TblB and ColC and ColD would be li...more >>

Hello, Is it possible to create a variable that has carriage return and line feed embed inside. Say I have 3 fields: field1, field2, field3. I want to return a string with carriage returns and line feeds between field1, 2 and field2, 3. Thanks in advance...more >>

Hi, iam using this script to connect to the default instance of a SQL Server (7 or 2000) strcomputer = wscript.arguments(0) Set oServer=CreateObject("SQLDMO.Sqlserver") oserver.loginsecure = true oServer.Connect strComputer The problem is: When i try to connect to a server that has many...more >>

I am trying to write an SQL query that will only select null dates. At the current time in the designer pane it will not let me have that selection. I have tried ISNULL, "", ''," ", etc. This is to select only records that have not been printed. Any help would be appreciated, Terry...more >>

I am programmatically trying to terminate the connection to a database that was connected and attached by another program (I have administrator privledges). I am trying to accomplish what you do in the Enterprise Manager when you click the "CLEAR" button in the end task dialog box to end all ...more >>

I have no idea how to submit this bug report to Microsoft, and I'm sick of searching the site and ending up in the same place all the time. So hopefully somebody can fwd this to them or one of their developers may stumble across it. Quote from Documentation: ISNUMERIC Determines whether...more >>

Hey all, I have a website front-end which accesses a sqlserver back-end (gasp! bet you haven't heard that before... ;) ). Anyways, I've noticed a bizarre anomaly... When I open my website and do a couple things (update data, access data, etc...), I notice that the stored proc command remains ...more >>

I have the following updates statements in my stored procedure which caused a deadlock. Should I take the (NOLOCK) statement out of the update statements? Is there some else I can to help resolve this deadlock? Thanks, Update SRA_FlowMaster Set Status = 'I' From SRA_FlowMaster ...more >>

I have a stored procedure that creates a temporary table, populates it with one record, and then returns that record to an ASP. I've read that by prefixing the the tablename with a #, that only the connection that created it can access it. An IIS server will be making the connection and uses ...more >>

Have an intersting situation in creating a UDL file. Have made plenty of them through the UDL creation app but in this case I need to create on manually. SO ... I did as all the MS KB articles suggest by opening a text file, adding the needed connection strings and associated text and then sa...more >>

-- Thanks ... J. Kusch...more >>

Hi there at the forum, I have a table with the following structure CREATE TABLE [dbo].[Demand] ( [ArtNr] [varchar] (20) NOT NULL , [Plandate] [datetime] NOT NULL , [Dispo_element] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [AmountReq] [decimal](18, 3) NULL , [Am...more >>

Hi to everybody, I'm trying to write a vbs (into a DTS in SQL Server 2k) that reads the content of a table located in SQL Server 2000 and then tries to INSERT it into another table in ORACLE. I've created the connections to Oracle and SQL Server 2K and they work fine given that I can manipul...more >>

Hi I am busy transposing data , that look like this 1 - 1 1 - 2 1 - 3 to 1 - 1 ; 2 ; 3 I have it working so far. I know there is other ways to do it with the case statements but this code I'm doing works almost 100% . I do the following select condition 1 left outer join sele...more >>

I have a query below that find a 'SV Redemption' and then picks it out if the 'SV Redemption' has a 'SV Redemption Reversal' after it. Is there a way to add onto this query and have only those that after the 'SV Redemption' and 'SV Redemption Reversal' doesn't have a 'SV Redemption?' I think it...more >>

The below SQL works well in updating the Price of an Order Item belonging to an Order. However, the following CASE statement works but doen't reflect the needed logic. Update tblOrderItem SET Price . . . . CASE WHEN C.PrePayTotal > P.DiscountPrice THEN P.DiscountPrice Else P.RegularPrice ...more >>

I am trying to identfy in a SQL statement if a sepcific SQL job is running or not. I dont care what step is it on. Ex. I have a job that is schedule to run every 30 mins. For what ever reason if the previous executed internval has not completed I dont want the current interval to execute...more >>

I have a table that needs to get generated on a regular basis and then get DROPed. My question is, on a DROP TABLE, does the transaction log record just the drop OR does it record the drop and all the records in the table??? in reality, I do not need this table to be transactioned at all on a D...more >>

Ok, here is the general gist of what I am trying to do and keep in mind; it might not be the best solution… I am open to new ideas, suggestions or best practice advice… My company will need to push data to another company via a web-service the other company has setup to receive data… ...more >>

Hi all, We have a stored procedure that is in production systems pretty much all over the world, we know have a customer who is experiencing deadlocks with the procedure 3 or 4 times per day. This is the only reported instance of this problem. General stats 100 Users across 10 Citrix ...more >>

I add 3 records into a table, the sequence is record 1, 2, and 3. When I use the Query Analyser to query the table, it shows incorrect sequence, sometime 2,1,3 or sometime 3,2,1. Why not 1,2,3? Thanks....more >>

Hi i have table like this CREATE TABLE [dbo].[Test] ( [MasterId] [int] NOT NULL , [ParameterName] [varchar] (32) COLLATE Czech_CI_AS NOT NULL , [Data] [varchar] (255) COLLATE Czech_CI_AS NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Test] WITH NOCHECK ADD CONSTRAINT [PK_Test] PRIMARY ...more >>

Hi all, My colleague and I are struggling with a rather annoying problem. The situation is as follows: We've two database servers (one primary and one backup) with SQL-server 2000 installed. We've been trying to implement "log-shipping" on this server using the example scripts from the SQL ...more >>

I have problem in that I admittedly, I do not understand how Sql Server 2000 and datasets work. I have an ADO.Net dataset in my C# app that contains data that I need to do an update or insert into a Sql Server 2000 database table using a stored proc. I am not using the CommandBuilder. Do I ...more >>

Any help appreciated on this one as I have scoured the internet and got no joy! I have a complex set of triggers and stored procedures that should result in changes to my _Company table being replicated to an equivalent table in a different database on a different SQL 2000 Server. This wor...more >>

Dear all, According to a date introduce I would need to obtain all the periodDesc of the following table: CREATE TABLE [dbo].[tbl_Periods] ( [sinStudyID] [smallint] NOT NULL , [strStudy] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [boldone] [bit] NOT NULL , [sinPerio...more >>

I have a table. And in the table, I have a field that stores the status of something. I declare this status field as a "bit" data type in the sql server. When I retrieve the value in my asp.net (in visual basic) code, is this data a string, integer, char or object? Visual Basic doesn't seem t...more >>

Hi there, Currently I working on a project that requires users to enter a username and password before they can use one of our internal administration system. The system is built using VB6.0 and SQL server 200o on Windows 2000. Can someone please suggest a way of encrypting and decrypting...more >>

I am trying to UPDATE a table in a Database which I get dynamically. My code looks something like this: CREATE PROCEDURE [CopyToDest] @Product_code [varchar](4), @Dest_company [varchar](20) AS DECLARE @ExecStr nvarchar(1000) DECLARE @ExecParamsStr nvarchar(1000) Set @ExecS...more >>

Hi, This is no function in SQL Server to get only the date part of a datetime variable, sometime, and usually most of the time, I would like to have a query to get the result of only some specific day's, and I am not sure what could be the best way to do this query. For example, if I want to ...more >>

Hi Could someone please give me the correct TSQL statement, for the below "pseudo". USE db_a INSERT INTO tbl_1 VALUES(USE db_b SELECT * FROM tbl_2) given that tbl_1 and tbl_2 is identical. Any hints appreciated Regrds. Mr. Smith ...more >>

Hi everybody! I have a table storing clients' phone numbers. It has two columns and looks like this: ClientKey PhoneNumber ------------------------------------------ 1 123-4567 1 123-4580 1 123-4595 2 345-...more >>

Hi We have a work schedule table in our database that comprises of an Employee ID, a Start Date/Time and a Finish Date/Time. We are trying to design a query that will return the record every Monday that it is operational. I.e. if a record existed with the following data: Employee ...more >>

How to send an array list as an input variable into store procedure? I have a list of UserName witch I would like to store into table through store procedure. Hrcko ...more >>

Hi I've a view in MS SQL 2000 with following structure and data sample, and I want to get the following result ,how it's possible with a select statement? table1: ( Code1 ,Price1 , Code2 ,Price2) , Code1+Code2 is uique Code1 Price1 Code2 Price2 percent ------ ------- ---...more >>

A customer lost all their data yesterday and wants to use the log file to retrieve it. Is that possible? The way they lost their data is by executing an update statement that should have applied to 1 record but was applied to the entire table, basically they forgot the where clause. Thanks,...more >>

I'm designing a database that will contain customer, vendor and employee information. My goal is to eliminate the duplication of data and facilitate the accurate classification of a particular person as possibly an employee, customer and/or vendor or any combination of the three with one per...more >>

Hi Can someone tell me what I am doing wrong here? I am not sure I am using count in the right context. Thanks. declare @ServiceID int declare @WorkTypeID int declare @PriorityID int declare @GeogID int if count Select CA.ContractID, C.Contract, CA.CoverCodeID,C.AgentI...more >>

Is there a way to determine if an existing connection has invoked a specific application role? I need to know so that I don't re-invoke it and get an error. ...more >>

would like to know as she can make the update of a count (*) in a table. The example that I am to try to do is this: update dados2 set field1= select count (*) from dados1 where dados1.field1= 1234 the result should be the value of the count (*) of the dados1 table and that value to be de...more >>

In the BOL for SET ROWCOUNT, it says: "It is recommended that DELETE, INSERT, and UPDATE statements currently using SET ROWCOUNT be rewritten to use the TOP syntax." However, TOP Doesn't see to work with a variable. For example, SELECT TOP @MyNum * FROM MyTable Is it ok to use ROWCO...more >>

I have the following queries which probably violates all sorts of rules. I'm doing year over year changes and can't seem to get the entire calucation done in one query. So I'm pulling data from one query (SQL2) and using it in another query (SQL1). You'll see real numbers being used in SQL1 (ie...more >>

Hi, I'm currently looking at ways of representing a tree structure using SQL. While looking for nested sets, I found an article by Vadim Tropashko who talks about Nested Intervals. http://dbazine.com/tropashko4.shtml In order to better understand this approach, I'd like to get a working ...more >>

Hello, I need help with some StoredProcedure execution and Transactions. Here is the scenario: 1) I set Autocommit to OFF 2) I Prepare and Execute an Update Statement without Commiting it. Let's call this Update Statement UpdStmt1 3) I now need to execute a StoredProc where I have an Update ...more >>

CREATE PROCEDURE UsersGroupsIDInsert @UserName nvarchar(50), @GroupID int AS Create Table #UsersID(UserID int, GroupID int) Insert Into #UsersID SELECT UserID FROM Users Where UserName = @UserName Select GroupID From Groups Where GroupID = @GroupID GO What's miss...more >>

Hello there I have sql server database. In there i have two tables with indexes. When i run simple select between these two tables i get hash join in the execution plan. The reason of the hash join is a result of selecting the wrong indexes on the execution plan. Whay it use the wrong in...more >>

Hi, how should I go about designing my database (table, field, collate) if I'd like it to support chinese, japanese and other non-english characters? I can't find any articles or best practises! Morten...more >>

My data file looks like this: Period, MCO, HMO, MeciareMem, MedicaidMem, CommMem, TotalMem 2Q02, Aetna Aetna Health of CT, 0, 0, 37947, 37947 2Q03, Aetna Aetna Health of CT, 0, 0, 41110, 41110 but I have thoustands of rows. What does my query need to look like to get the...more >>

/** l'm trying to improve my batch processing routines by creating a generic method which l can apply on most of my jobs.Reason Being l'm working with large Data Sets This is what l'm trying to achieve 1.Get The Rowcount of the Source 2.Split It Into manageable batches 3.Loop through th...more >>