The below code inserts 2 different records into a table, but the binary checksum produces the same number for each insert, thus thinks the records are the same, this is suppose to be impossible ??? @@version return Microsoft SQL Server 2000 - 8.00.850 (Intel X86) Aug 7 2003 11:07:42 ...more >>

Hello, I've found interesting situation with UPDATE(column) function inside triggers Here is an example of code: -- SET NOCOUNT ON CREATE TABLE [test] ( [id] [int] NOT NULL , [col1] [int] NOT NULL , CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED ( [id] ) ON [PRIMARY] ) ON [PR...more >>

I've got a SQL Server database. Nearly finished. It's going to go on a single non networked machine. One day somebody might get access to it over ADSL (probably TS), but for now it's a single user no lan. The machine will actually be running the MSDE. Windows XP Home. I'm quite happy, for ...more >>

Hi, I want to create a substring from a string that only contains the numeric values within that string. So a string eg. could look like this: xxxxx 999xx or like this xxx 99 or like this xxxx 9 x and I want to return the values 999 and 99 and 9 from these strings. I have no clue ...more >>

How do I get the properties of a check constraint? Properties like Check existing data on creation, Enforce constraint for replication, Enforce constraint for INSERTs and UPDATEs. How does EM gets them? Costin ...more >>

Pardon the simple question.... does transact-sql have an equivalent of rownum in oracle? Is there an idea of a rownumber? ie. select * from table where rownum <= 10 thanks ...more >>

I have a standyby server that restores logs automatically using an agent job. When I need to use this standyby database I run the following command: RESTORE DATABASE DBNAME WITH RECOVERY Is there any command that will put the database back into the mode that will allow it to continue acc...more >>

Hi, this statement works: DECLARE @Username varchar(20) SET @Username = 'xxx' IF NOT EXISTS (SELECT NAME FROM SYSUSERS WHERE NAME = @USERNAME) EXEC [pubs].[DBO]. sp_grantdbaccess @Username ....but when I pass the database as a parameter (instead of 'Exec [pubs]' with 'Exec @Database +) ....more >>

SQL Server 2000 Hi. I'm trying to set up remote debugging for my SQL Server, so I can step through from within VS.NET. The application is running on my PC, while the server is on another PC on the same LAN segment. The error I get is: "Cannot debug stored procedures because the SQL Se...more >>

Hi Friends, I have a database withh 450 table, each more than 2-3 FK -PK at the time of DB design they didn't uncheck the box for ENFORCE RELATIONSHIP FOR REPLICATION How can I have a script to uncheck them all? should I go by one by inside the table and uncheck them ? ( OMG this should ...more >>

Hi all, I am running SQL Server 2000 that is accessed through Access 97 via ODBC. I am not an expert on SQL, so in order to filter my data on the server I use fields in a user table, which I access using SUser_SID. That works fine as long as every user is logged-in only once. If a user chooses...more >>

I have a table named RetailProducts and a table named RetailInvoicesDetail. What I want to do is reduce the RetailProducts table field called ProductQty by the field named Qty in the table named RetailInvoicesDetail. They would match on ProductID and only be where QtyPosted = 0. Then, I want th...more >>

How to display it ? ...more >>

I read something from the book talking about global variable but seems not a 'REALLY' global variable. It seems only global to the current connection, or am I wrong ? Do I mix that with global temporary variable ? ...more >>

The following query returns the latest Order date for each customer from the Northwind..Orders table SELECT MAX(OrderDate),CustomerID FROM ORDERS GROUP BY CustomerID We have a similar table that contains an additional identity field and we would like to use this identity field to help det...more >>

Ok - here is another question. I have a list that can be printed out in one column of company names and addresses, but they would like to have this in 2 column format. They don't want me to rewrite anything, just add a column. IE: company 1 company2 Addr ...more >>

Hi, I have a dts file (RSExecutionLog_Update.dts) and I need to schedule it to be run every night. How can I use sql server agent to do that for me? I guess I have to import the dts file into DTS service first? Thanks, Alan ...more >>

Hi, Is there a way to see which indexes in a table that are used and which are not or is there way to see how many times an index has been used? Thanks //Malin ...more >>

SQL 2000 Here is my table structure moveid, move_type Sample Data moveid move_type 1 AA 1 BB 1 CC 2 BB 3 BB 3 CC 4 ...more >>

While working on a test copy of a production database, I tried to run this: Update Positions Set Sec_class = null (I'm playing with various updates to discover how long different things take, and this was an intermediate step. The DDL is below.) Anyway, this statement ran for 45 minu...more >>

I have a request to print reports from a web page automatically, on demand from the user. Basicallly, the user selects information from my page to print, the user then wants the report to load and print (and close) without any further user intervention. If possible, I'd even prefer the entir...more >>

Hi, I have not touched any SQL for a while but find myself needing to refresh my memory. I think that I remember being able to produce either a stored procedure or view, that evaluates an expresion and returns a pre-defined value ie, if x >50 'Big' , 'small' into an extra column. I ...more >>

Can I do something like ***************** Use Northwind Declare @IndexName nvarchar(50) Declare @IndexKey nvarchar(50) Select @IndexName = index_name, @IndexKey = index_keys exec sp_helpindex Customers Print 'Customers' + @IndexName + ', ' + @IndexKey ***************** The error occurs... ...more >>

Hello all, I have run into something very bizzare and something I have not come accross before. We have a data feed that goes into a legacy mainframe system (not sure which platform) every month. Unfortunately or fortunately, we are on a SQL Server 2000 environment and I can create a .t...more >>

Mostly and "academic" question. I have MSSQLSERVER and SQLServerAgent running as domain accounts. I am able to implement this sucessfully if both services are running with the same identity. When I use two different domain identities for MSSQLSERVER and SQLServerAgent; SQLServerAgent will sta...more >>

This seems to be a pretty dumb question (and might be). I'm using VB .NET 2004 with MS SQL on the back end (could be any version but I'm testing on a Windows SBS 2003 server). I need to be able to insert a row into a table and have a unique identifier (record number) for each row. Sounds ...more >>

I have an application which connects to the database through ODBC. Is there a way I can read OLD value of transactions that has not been commited? (Just like ORACLE does). ...more >>

I need a function that converts Hex to Dex. What I can't seem to understand is why Excel and Word have this function, but its not available in SQLServer. Anyone Help me out?...more >>

Hello group, I have easy question. In my query I want get all rows from last 7 days. if I use: Select * from myTable where myDateTime > DATEADD( day,-7 , GETDATE()) then DATEADD(day,-7 , GETDATE()) return full date with hour, minute etc. I need return only date without hours, ...more >>

Help! This is the result of a query I have. Product is from tbl_products MonthYear is from tbl_MonthYear Total is from tbl_SalesData (contains MonthYear and Product) How can I get Monthyear 2003_05 to 2003_07 for products 1-4, even though there are no matching...more >>

I'm working on a bit of a complicated UPDATE... FROM query to update one table, based on data in several other tables. In a nutshell, I've got a query similar to: UPDATE <tablename> SET <column> = <value> FROM <tablename> INNER JOIN <another tablename> ON <criteria> WHERE <other crit...more >>

I have this select statement: strSql="SELECT tblStores.storeId, tblStores.storeName, tblStores.storeAddresse, tblStores.storeCity, tblProvinces.sName, tblCountries.cName, tblStores.storePCode, tblCategory.categoryName, tblStyle.styleName, tblType.typeName, tblOffers.offerId, tblOffers.off...more >>

Hello I have some sp that make some selects, updates or inserts to a diferent SQL database. I use dbname.dbo.tablename. Is it possible to have the database name in a variable so I can change the name any time if the database changes the name? for example declare @dbtempname as nvacrchar(20) ...more >>

am i allowed to have foreign/primary key relationships b/t tables in two different databases? appreciated, matthew...more >>

Hi all, How can I change a column datatype from datetime to int using SQL?? Thanks, Ivan ...more >>

Obviously I can't do this, so short of making another permanent column in my table, how can I index just the left part of a varchar column? Create Index "MyIndex" on [dbo].[MyTable]("Left(TableColumn,15)") Thanks. ...more >>

I have a table with 2 columns and 8 rows, I am trying to return it as 4 columns with 4 rows like so: (Row1)Val1 (Row1)Val2 (Row5)Val1 (Row5)Val2 (Row2)Val1 (Row2)Val2 (Row6)Val1 (Row6)Val2 (Row3)Val1 (Row3)Val2 (Row7)Val1 (Ro...more >>

I have encountered this situation a couple of times recently about whether to add a third relationship table all the time. I am just wondering which one is the best and practical solution. Here is an example, (sorry I do not have a DDL, just pick this up since this example is quite common). T...more >>

How can I convert a yymmdd date into a mmddyy date Thanks in advance...more >>

Hello I was wondering if you guys could help me with doing a simple search of like 6 columns of a table. I need to order by "Rank", how is that done? been looking in BOL and not sure if i should use containstable or contains or what. "string" passed to sp as runs query would be something li...more >>

The Following stored procedure must do the following three operation: Is it possible? (1)Check if user have 5 tickets or more records in database. (2)Sort Num1 through Num6 from less to greatest and stored than in the database columns Num1 through Num6 in that order. if this is possible How? I...more >>

I need to perform a search for all zip codes within "x" miles of a given zip code using Microsoft SQL Server 2000. Ideally, this would be a User Defined Function that would accept two parameters: an origin zip code and a radius (as an integer value). I have a table called ZipCodes that, among ...more >>

Select 2/4 =0.0 ? how would I cast this to get 0.5 ? thanks ...more >>

i need to do this: strSQL = "Update tab set col1 =1 where f1 = 'x' " objConn.Execute strSQL A need the value of x to be read from a csv file help thanks a lot...more >>

Hi ! Can anyone help me with a quite simple question ? I want to add two values into the SQL database, using data type that can be combined with other values to make a new href. adding them works fine, but binding them together won't work. I now use "char" datatype, and I think that is the re...more >>

I am a newebie that needs to query a database of addresses, using zip code as the criteria. Problem is that there are 700 zip codes in this case. Does anyone have any suggestions on how to do this? It would take for ever to do Select * from table1 where zip = 'xxxxx' or 'xxxxx"... Than...more >>

I have seen the following kind of table update and I have a question. "You may wish to update records in one table based on values in another table. For example: UPDATE supplier SET supplier_name = ( SELECT customer.name FROM customers WHERE customers.customer_id = supplier.supp...more >>

Greetings ! We're surprise to see no reply to our original question about Object/Relational mappers. So no one as such architecture? We need this for our software since we made such a layer years ago but it doesn't perform as we expected. We're then looking for a commer...more >>

I have program that updates a database, work fine except it wouldn't update text datatype fields. When I used varchar it worked but truncated to 8000 char. I am seting datatype = "System.Data.SqlDbType.Text"; for text and datatype = "System.Data.SqlDbType.VarChar,255"; for other values. Al...more >>

Hi as I run following statement an error appearred. ****************** (Server: Msg 2812, Level 16, State 62, Line 3 Could not find stored procedure 'xp_cmdshell'. (0 row(s) affected) ****************** use test create table #errorlog(line varchar(2000)) insert into #errorlo...more >>

Each sales rep in the company keeps their sales totals in a separate workbook. I have a summary woorkbook that pulls the data from each of sales workbooks so the manager can look over the data. I want to be able to pull the information from this summary sheet into an SQL database. Using SQL ...more >>

Hi I am trying to ftp a file from my sql server to a remote server using FTP exec master..xp_cmdshell 'ftp –i –s:d:\ftp.txt' and my ftp.txt file contains open ftp.slip.net --ftp site full name <username> <password> lcd d:\ cd htdocs put test.txt quit Does anyone know a s...more >>

Hello, I have this stored procedure that I am trying to get to work that takes a select statement and creates and temp table from the select passed in. But I get this error. Invalid object name '#tmpCustomerResults' Can someone help me. Here's my SP ALTER PROCEDURE dbo.DH_RunQue...more >>

Hi, i want to know if there's a limit for the created temporal tables on a particular server, database or user session. Using SQL2000. I'm having temporary tables result limit error. Thanks...more >>

Google isn't helping much so I ask you, the experts.... I have a web .Net application that needs to store, manipulate, and report back event scores that are recorded as times. Think of it as a "Track & Field" type events. People run through events and I need to record their times. At the en...more >>

Hi How can I move the result of a store procedure in SQL server 2000 to a table? Any help would be greatly thankful. ...more >>

Help! I'm having difficulty reading SQL Server numbers into my MicroFocus COBOL copybook. I don't know how to define the copybook so that the numbers are read in correctly. The numbers are defined in SQL Server as decimal(10,0) and another one as "Integer". The COBOL program fetches t...more >>

Hi, what is the T-SQL code to show the index information for table(s)??? Thanks Ed...more >>

All: Can anyone explain in "plain English" what the differences are among the following statements? My understanding of what is happending under each line... "select blah from blah WITH(HOLDLOCK)" --acquires and holds a shared lock until completion --of the transaction "select b...more >>

Following is my table structure: create table StartStop (StartStopKey int identity(1,1), StartStopDatetime smalldatetime, StartStop bit) insert into StartStop values ('2004-11-01 00:00:00',0) insert into StartStop values ('2004-11-01 00:01:00',0) insert into StartStop values ('2004-11-01 00:...more >>

I have set up a Linked Server to the AS400 (ODBC), now I am trying to save a View that uses the Linked Server. The View runs fine and returns data very fast. but when I try to save it I get this message. ODBC ERROR: [Microsoft] [ODBC SQL Server Driver] [SQL Server] The operation could not b...more >>

Hi, We've recently had to take over another team's system. They've been disbanded, and of course we've no documentation. This system has literally thousands of stored procedures and many hundreds of tables, and has been increasing in size (objects and data) since first developed in 2000. A...more >>

I want to use the cmd kill {spid} in a SP. I cant figure out how. Is kill equal to delete the row in tbl sysprocesses? //jozzler...more >>

I have a table in a database that will be used by three seperate companies. Each company needs to be able to view and update their own data, but not see any of the other companies data. I need to see all three companies data. I was thinking of using a 'View' to do this: Create View Compa...more >>

I have a table of following structure CREATE TABLE [PersonInfo] ( [id] [int] IDENTITY (1, 1) NOT NULL , [name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [emailid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [phone] [varchar] (15) COLLATE SQL_Latin1_Gene...more >>

another easy newbie question: I am writing a SP to update a complex view. Do I update each underlying table individually or can I update the view itself? The FE is Access and I know that views are for all practical purposes not updateable from Access. So if I have 5 or 6 tables involved, it d...more >>