I have a production application that I am building some upgrades using a second (empty) copy the database. A few of the upgrades included changing the datatypes of a few fields from varchar to int(all the values in this column are already numbers) I am not trying to move the data from the pro...more >>

when i work with Aceess Db i have SQL statment that cout the visitor: update Tbl1 set fldvisit = fldvisit +1 where fld2='abc' when i move to SQL Server it's not working what to do? ...more >>

I'm not a SQL expert. I want to be able to write a stored procedure that will return 'people who bought this product also bought this...'. I have a user table that links to a transaction table that links to a transaction items table that links to the products table: (User Table) UserID Oth...more >>

I am loading data from table A into table B. Certain columns in B have check constraints. I'd like for any rows from A, which violate constraints, to be inserted into a third table, C. When the process is finished, I'll have only good rows in B, and exeption rows in C. I am investigating INSTE...more >>

My host does not provide a good web based management tool for the sql server database I am using. They queue imports and exports so it takes several hours before things are finished. They provide this web based access and access through asp or asp.net. Does anyone know how I could tunnel a co...more >>

I want to connect over the internet to get data from clients.What would be the easiest method and the most stable methos to do it 1.Use a Point to Point prtotcol from the client pc and then upload the data to our sql server 2.Export the data from the clint pc in XML format and then Enter it in...more >>

Hello all, I'm kind of struggling with numeric data types... I have read alot of material, and think I have a 'decent' grasp on the various number data types and the effects of the scale and precision. However, I want to store a value of '1.0', and every variation I try still truncates the ...more >>

Microsoft site says yes If yes, then if I publish my site on a win2k server, will it still work? These questions assume that a yes answer does not involve jumping through too many hoops or having to spend an entire day or longer poring through MSDN. God I sound jaundiced don’t I! Any h...more >>

SQL 2000 SP3 My database is still showing Loading .... after doing a restore and the restore showing it to be successfull. I have restarted the service as well. Help ! ...more >>

The query: SELECT BTbl.PKey, BTbl.Result FROM BTbl INNER JOIN ATbl ON BTbl.PKey = ATbl.PKey WHERE (ATbl.Status = 'DROPPED') AND (BTbl.Result <> 'RESOLVED') Returns no rows. If I do: SELECT BTbl.PKey, BTbl.Result FROM BTbl INNER JOIN ...more >>

Hello all, I belive, my problem is probably very easy to solve, but still, I cannot find solution: declare @i int declare @z int create table bubusilala ( [bubu] [int] NOT NULL , [gogo] [int] NOT NULL , [lala] [varchar] (3) NOT NULL ) insert into bubusilala...more >>

I just found an odd bug and was wondering if anyone else has seen this. Any templpate file in the directory 'C:\Program Files\Microsoft SQL Server\80\Tools\Templates\SQL Query Analyzer' that has the NTFS compression turned on (that is, colored blue in Windows Explorer) does not display in the ...more >>

I need to migrate data from one sql database to another. The second DB is a newer version of the "old" database with mostly the same tables and fieldnames. In order support some reporting queries in the "new" version I needed to change the datatype of a few fields from varchar to int(the data ...more >>

I'm not a newbie, but I've not been able to find out how to do this particular task, and BOL isn't being too cooperative. My application sits on a SQL Server backend (SQL Server 2000). The application is a Document Distribution system. Some subscribers have elected to be notified by e-mail ...more >>

Hi I am just started at a new position. This organization has a number of database servers with mission critical databases. However, I have two database server, 1 a test server and the other a catch all db server that contain a hodge podge of databases. On the test server, there are 54 ...more >>

Dear friends, I wonder if exist a way to make crosstab queries in SQL Server like those in Access without "external" programming. Does the SQL Server supports the "TRANSFORM" SQL-extension? Thanks in advance, Sotiris. ...more >>

Here's a simple question for you: Is it better to maintain a single open connection to the database for the entire duration of the users session, or to connect, perform an operation and then disconnect each time the user wants to do some work, within that session? If so, why? Thanks...more >>

Hi, I need to lock a table so that Inserts are prevented as well as deleted and updates. At present I'm thinking this might do it: SELECT * FROM myTable WITH(UPLOCK) but then again I'm not sure whether this will cover the insert case. Thanks, Robin ...more >>

What is the purpose of the "N" preceding the parameter values in the SQL examples? Here is an example copied from Books Online, SP_ATTACH_DB: EXEC sp_attach_db @dbname = N'pubs', @filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf', @filename2 = N'c:\Program File...more >>

Hi I have a weird problem I want to cursor thru the values in a temporary table and use the values to do a select statement to insert into another temporary table...This select statement uses a like clause something like where....when I take off the insert still nothing comes back from the sel...more >>

Hello all, I have a multiple text files with an odd row terminator. If you were to examine it in VB it would be like a "CrCrLf" instead of just "CrLf". In HEX it is DDA instead of just DA. When I am trying to import into my table using BULK INSERT I use "\n" as the row terminator but that is pu...more >>

SQL 2000 I have server1 with two filegroups. I want to backup FileGroup Primary on Server1 and Primary has a physical file called MyData.mdf. I have server2 with the same database and two filegroups BUT the Physical names are different. The Primary FileGroup is in physical file OurData.mdf...more >>

I'm using a stored proceedure which should update a number of rows in a table depending on a key value supplied (in this case 'JobID'). But what's happening is when I call the proc from within the program, only one row gets updated. So When I call the proc from Query Analyser, all rows get ...more >>

Hi All, How will be the career oppurtunities for DBAs on multiple databases..i am a coredba on MSSQL for past 5years and i have a great desire to work on Oracle as a dba..i have enough skills for that.. But the issue is i have just 1 year experience as a DBA in multiple databases.. In se...more >>

Hi, I have a client/server app. that uses a windows service for the server = and asp.net web pages for the client side. My server class has 3 methods = that Fill, Add a new record and Update a record. The Fill and Add = routines work as expected but unfortunately the update request falls at = ...more >>

Hello does anyone know how to apply this patch on msde. I am getting the rootkit.h in some of my servers and this patch requires to run some querys. thanks. ...more >>

Hi folks, Is it possible to store Binary Files in MS SQL 2000 ??? Say I have a 100K PDF or a 150K word document. Is it possible to store this in a field in MS SQL and pull it out somehow? We're using ColdFusion on the server on Apache. Thanks, Ringo ...more >>

Hey, I need to update a row in one table with a row (most but not all columns) from another table. This sounds like there should be a really easy way to do this. I've seen there's FROM in the UPDATE statement but haven't found any similar examples. Basically, I need something like this...more >>

Three tables (all new, no data) will receive data from dBase and be transposed into them...All three have auto generated IDENTITY columns and pk and fk constraints. Can someone provide me with an understandible sample? Thanks, Trint ...more >>

I want to take the contents from a table of appointments and insert the appointments for any given month into a temp table where all the appointments for each day are inserted into a single row with a column for each day of the month. Is there a simple way to do this? I have a recordset that ...more >>

We have a three tier application running on two tiers. 1&2 running on one server and a seperate server for the databases. On the database server we are running Windows 2000 Server with 3Gb of RAM and Dual 1.8GHz Xeon Processors and SQL Standard 2000. The memory for the SQL server never seems...more >>

Can anyone recommend a good SQL Server book or class for Beginner-Intermediate, please? Thanks ...more >>

Inspired/challenged by Hans Lindgren's stored procedures of these same names posted on SQLServerCentral, I created these. Note that they produce strange results on non-hexadecimal strings, and may have issues with byte-ordering in some architectures (but Itanium is little-endian like x86 and x...more >>

Hello, I want to delete duplicate rows in a table when no primary key is defined. For eg: If we have table1 with data as below, Suma 23 100 Suma 23 100 I want to delete a row from this table and retain only one row. I tried deleting self joins and exists operator. But it is deleting ...more >>

I have this stored procedure that loops through a table and updates a couple of fields. For some reason one of the fields is not being updated. If I run the same code from query analyzer, it works fine. Let me know if anyone can figure out why @lastscandate would ever be NULL. If it is null i...more >>

I'm having some trouble coming up with the correct select statement. Lets say I have the following two tables: ---------------- ---------------- Orders OrderItem ---------------- ---------------- PK OrderID (int) PK OrderItemI...more >>

Hello All, I wrote one code for sending mail by using Asp.Net+C#, My code is as follows. private void btnSend_Click(object sender, System.EventArgs e) { try { MailMessage email = new MailMessage(); if(rblHtml.Checked) { email.BodyFormat=MailFormat.Html; } if(rblText.Ch...more >>

We connect via terminal services to an excel spreadsheet that has an ODBC link to an access database using a pivot table, when we try to refresh the data within the excel spreadsheet we receive the following error "the microsoft jet database engine cannot find the input table or query....." t...more >>

Hi, How can I make a view that gives it's data from az XML file(it has URL)? Thanks alot ...more >>

Prompt me, what for need "primary key" in the database table?...more >>

Hello everyone I have a question, something that's been bugging me for some time now. Let's look at a query SELECT A1, (SELECT SUM(EventValue) FROM Events WHERE Person = A1 AND EventType = 1) AS EVENT1, (SELECT SUM(EventValue) FROM Events WHERE Person = A1 AND EventType = 2) ...more >>

The Methods & Tools newsletter has released in its archive section the article "Database Modelling in UML". This article offers a step by step approach to transform a UML object-oriented class model in a purely relational database. http://www.methodsandtools.com/archive/archive.php?id=9...more >>

I need to copy data from one column that has a max size of 45 characters to a column that has a max size of 20 characters. What is the syntax to say fill the first 20 characters out of 45 from column 1 into column 2? jeff -- Message posted via http://www.sqlmonster.com...more >>

I'm pulling my hair out. After several attempts I got the sp_OAMethod to execute without error. Unfortunately the DTS package isn't executing. It also isn't returning any error. What could I be doing wrong? Any help would be appreciated. This is the EXEC @hr=sp_OACreate 'DTS.Package', @oP...more >>

This is a shameless plug. My new book, SQL PROGRAMMING STYLE is out now. Joe Celko's SQL Programming Style by Joe Celko Save 20% and get Free Shipping if you order by Friday May 6. Place your order now or learn more below _______________________________________ If you're looking for ...more >>

Hi , am new to sqlserver 2000.I want to connect to my sqlserver which is a remote machine.I want to connect to sqlserver by sqlserver authentication.Even though i added the available sqlserver in my client as it shows, it is connected only by windows[nt) authentication.Why it is showing so ?...more >>

I am getting the following error when running a command in Query Analyzer. Msg 50001, Level 1, State 50001 xpsql.cpp: Error 5 from CreateProcess on line 675 Here is the command that I am running that generates this error message: xp_cmdshell "@ECHO test message > c:\temp\newtemp.txt" ...more >>

hello all, my configuration is: Sql 2000 SP3, OS win2003 server when i try a query with enterprise manager the error message appaers [Microsoft][ODBC SQL Driver] timeout expired i verify all components ( query time out settings ....) but nothing change PLEASE HELP ME !! Thank you...more >>

SQL 2000 We took SQL Server offline last night and defragged the SAN. Should we reindex or will be be okay ? Thanks, Craig ...more >>

Hi guys We have a following problem. For security reasons in each table in our DB we have addition field which is calculated as hash value of all columns in particular row. Every time when some field in particular row is changed we create and call select query from our application to obtain...more >>

Hello -- I have a field with date values like: 20050714. When using SQL Server to convert it to a Date in a View, I use the following: CAST(MyDate as DateTime) That works great. The problem is that some rows have the value: 00000000 When it hits one of these, I get a SQL Server Enterpris...more >>

We are busy designing a generic analytical system at work that will hold multiple analytic types over time. This system is being developed in SQL 2000. Example of table IDENTITY int ItemId int [PK] AnalyticType int [PK] AnalyticDate DateTime [PK] Value numeric(28,15)...more >>

hey guys--i need some expert advice! we have a Dell PE 2650 with 2GHz and 2MB RAM with 2000 Server. on it we have about 6 MSSQL DB's. a couple of the DB's are the back-end for a decent sized web app (5-10 users on average, 20 users at most). many times, the cpu skyrockets on our db server when...more >>

I have a database that looks something like this: Table: Creature Fields, CreatureId {PK}, ... Table: Bug Fields: BugId {PK}, CreatureId {FK}... Table: LadyBird Fields: LadyBirdId {PK}, BugId {FK}... Every creature in the system is in the Creature-table. If the creature also is a bug...more >>

I have a table with columns c1, c2, c3, c4. if all nulls or blanks. Status = 0 if c1 assigned but no c2, c3, and c4, then status = 1 if c2 assigned but no c3 and c4, then status = 2 if c3 .. then .. if c4 .. then .. I want to have one SQL to get the status like (ignored checking for blank...more >>

How can i get the details of the error in sql server 2000 such where it occurs in which line. I need something like i get in sql query analyzer. -- Message posted via http://www.sqlmonster.com...more >>

Hello, this probably isnt the best place to ask but I can't find a more suitable sql newsgroup so I hope y'all dont mind too much. I have 2 tables; Cellar and Colour CELLAR contains the wine name, its year and the no.of bottles. Wine Year Bottles Chardonnay 87 4 Fume Blanc ...more >>

I need to build a *.sql script that will remove a database (let's call it "DB1") and replace it with a brand new empty database (let's call it "DB2"). Caveat: I don't want to be left with database "DB1" having it's files confusingly named "DB2.mdf" and "DB2_log.ldf". These two files should a...more >>

I am having problem to apply updates into this function below. I tried using cursor for updates, etc. but no success. Sql server keeps telling me that I cannot execute insert or update from inside a function and it gives me an option that I could write an extended stored procedure, but I don't ...more >>

Can someone show me how to write a query to convert this: Year, Account, Qtr1, Qtr2, Qtr3, Qtr4 2004, 12345, 100, 200, 300, 400 2005, 23456, 200, 300, 400, 500 to this: Year, Account, Quarters, Amount 2004, 12345, Qtr1, 100 2004, 12345, Qtr2, 200 2004, 12345, Qtr3, 300 2004, 12345, Qt...more >>

Hi We have developed and deployed a database which contanis very sensitive information. Our client is now concerned about the security of the back ups. In essense, if someone in the organization can get hold of the backup of the database, he can simply restore it on any sql server in the worl...more >>

I want a user to be able to search for names in a table of clients using a single parameter, but be eable to use "and" or "or" IN the parameter. Like this: @NameSearch nvarchar(100) AS DECLARE @PartA nvarchar(100), @PartB nvarchar(100) CASE When @nameSearch Like '%' + ' OR ' + '%' ...more >>

I have a database where each row has a set of keywords separated by commas eg . ROW 1 ID,<other columns>,"green,hat,man,boat" ROW 2 ID,<other columns>,"green,boat,seaside" ROW 3 ID,<other columns>,"red,hat" I want users to be able to find similar records to the one they are looking it (thi...more >>

Could someone please give me an idea of the differences between SQL Server and Oracle, backup and recovery options. Thanks ...more >>

Hello, I'm not sure if it is right group for this question, but I hope that I'll get help here... I'm using MSDE to store data for my application. And I'm having such problem. I'm creating a table in DB for string connection information: CREATE TABLE sql ( action_id int NOT NULL defaul...more >>

I am aware of the problems with using identity. I would like a bit more information on implementation. Concurency is a concern because this is a net application. Do I do this at the application level, in a trigger, or using another method? I want to make sure that the method I choose is rob...more >>

Hi all, I am rather new to database design and modelling concepts in general and was hoping for some advice on a problem I am trying to solve. I have designed a piece of software that creates a tree with pluggable nodes. Each node class can have 0 to n distinct classes plugged into it to de...more >>

Hi all, it's possible from a SQL Server (stored procedure) call (execute) a VBA procedure in a MS- Access database? Thanks, Roberto -- Posted via Mailgate.ORG Server - http://www.Mailgate.ORG...more >>