Hi, I have a few create table statements which create temporary tables that has row definitions which exceeds the 8060 size limit, this causes a warning message being generated (Message 1708). Is there a way I can disable/avoid the warning message from being printed ? Thanks, joe ...more >>

Hi, i restored a backup from a database thas has replication configured. When i restored it the system tables that the merge replication creates are restored too. I was investigating on internet and I found that i can delete it using this query: sp_configure 'allow updates', 1 go reconfigure...more >>

I want to know the differences between SQL Server 2000 stored procedures and oracle stored procedures? Do they have different syntax? The concept should be the same that the stored procedures execute in the database server with better performance? Please advise good references for Oracle store...more >>

Hello I have these tables: CREATE TABLE [dbo].[COREAttribute] ( [oid] [uniqueidentifier] NOT NULL , [CLSID] [uniqueidentifier] NOT NULL ) ON [PRIMARY] CREATE UNIQUE CLUSTERED INDEX [COREAttributeOidIndex] ON [dbo].[COREAttribute]([oid], [CLSID]) WITH FILLFACTOR = 90 ON [PRIMARY] ...more >>

I've been reading the docs and playing around, but I'm still not getting the difference. For instance, create table a(i int check(i>0)) create table a_src(i int) go create unique index ai on a(i) with IGNORE_DUP_KEY go insert into a_src values(1) insert into a_src values(1) insert into a...more >>

If i have two identical queries, with exception of top criteria Select top 1 * from photos Select top 8 * from photos why does altering the top critieria alter the order of the returned results? ...more >>

Hello, under MSSQL8 doing this "create table tblA as ( select Name from tblB );" results in error 156 (wrong syntax near AS keyword). What is my mistake? thanks and regards Mark ...more >>

I have two stored procedures ALTER PROCEDURE dbo.qryCountOne (@inputID int) AS SELECT COUNT(*) AS CountOne FROM dbo.TableOne WHERE (dbo.TableOne.value = @inputID) ALTER PROCEDURE dbo.qryCountTwo (@inputID int) AS SELECT COUNT(*) AS CountTwo FROM dbo.TableTwo WHERE (dbo.TableTwo.value ...more >>

Apologies if this is not the correct place to put this. I wrote this query to perform a fulltext search on tblPages. SELECT * FROM tblPages, FREETEXTTABLE(tblPages, *,@searchTerm) searchTable WHERE [KEY] = tblPages.PageID ORDER BY RANK DESC Where PageID is the primary key. The synax is...more >>

I'm reading a book 'Professional SQL Server 2000 Programming' by Robert Vieira there is a recommendation: "stay away from building views based on views" Why? What's so wrong with nested views? ...more >>

My update query updates about 50 columns and they all have the same format: (Im using ADP to interact with the SQL Server) UPDATE dbo.GIS_EVENTS_TEMP SET VEH1TYPE = (SELECT VEHICLETYPE FROM VEHICLE AS A WHERE(GIS_EVENTS_TEMP.CASEID = A.CRASHNUMBER) AND(A.UNITID = 1)), VEH2TYP...more >>

Hello I am using a temp table called ##temp in an SProc but often get the message that the table already exists. Could this be because the SProc is being run by more than 2 webpages at the same time? Or is it because the sProc has an error and is not getting to the drop table line? I have ...more >>

Hi All, we have a problem with the Full Text Catalog Search. We use the following SQL Statement for matching companies from a table: select company, lastname, firstname, pkcustomers, fkcustomers, location, title, fkFunktionen, TypeOfPosition from customers where (fkcustomers =3D 0 or fkcus...more >>

Hi: I'm trying to read data from Navision 2.60 ERP with own database to a SQL Server 2000 database via ODBC. All is working except just one thing, how to filter data with a date field. I have some big tables that have a date field and I just want to read data from one or few days, not all t...more >>

Hi, I cant figure out how to do this.... for example: Select name from mytab order by col1 could return Mike Dave Sue Simon Paul Fred I would like to show the row number, like in the grid in query analyser. so the orginal sort order is preserved i.e 1 Mike 2 Dave 3 Sue ...more >>

The description is as follows... The necessary network binding may already be in use. The site has been deactivated. The data field contains the error number. If anyone can help I would greatly appreciate it. Thanks! Mendy...more >>

Dear Group, I would like to create an audit table that is created with a trigger that reflects all the changes(insert, update and delete) that occur in table. Say I have a table with Subject_ID, visit_number, dob, weight, height, User_name, inputdate The audit table would...more >>

I have a C# application that creates a SqlConnection object with an ADO connection string and then sets up a SQlTransaction object for that connection. Several records are posted to different tables in the database within this transaction. All these tables uses an identity element as their pri...more >>

hi all, hope someone can help.... i'm having trouble calling an SP where the ORDER BY operator is specified as a parameter when the SP is called my SP is..... CREATE PROCEDURE dbo.sp_CustSearch (@SearchFor VARCHAR(80) , @SortOrder VARCHAR(50)) AS BEGIN SELECT first_name, last_name...more >>

I have content about projects that I store in a MS SQL database. People access this information through pages which are dynamically created in PHP. Since my content is stored in the database, how can I get google web search (at google.com) to included that content when it indexes my site? Is it ...more >>

I'm a newby and I have a quick question. I have set up this procedure to run as a job. I can create a block and have it populate the table, but I need to be alerted via email when the table is being populated. I do not know what step/s am I missing. Here is the scripts I ran: CREATE TABLE Blo...more >>

Hi Everyone I have a tricky problem with Reporting Services 2000. We can't use URLs to access reports for security reasons, so we have to use the web service. This means I have to write a viewer control (I can't wait for SQL 2005 either!). The problem I have is with rendering images. I can ...more >>

Hi. I'm creating a stored procedure that inserts a record if it's not already there, otherwise returns -1. It's like this... --- CREATE PROCEDURE createsession @username varchar(20) AS begin --Validate session not already there select 1 from a..Sessioninfo where username = @username if @...more >>

Using SqlServer : Query 1 : SELECT def.lID as IdDefinition, TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour FROM serveur.Data_tblDEFINITIONTABLEDECODES def, serveur.Data_tblTABLEDECODEAUNEVALEUR TDC_AUneValeur where def.TYPEDETABLEDECODES = 4 and TDC_AUneValeur.PERIODE_ANNEEFISCALE_ID =...more >>

Can I use osql to known the database install on a server via 'guest' account ? I had over 300 servers with sql server all around France (different version : 6.5, 7 and 8) I need to check all database on each servers from my place. Of course, also it would have been to easy, I don't have all '...more >>

hi all, have anyone else met with the following problem. we've got a SQL2000SP3a on a 2 active-active node clusters of W2K3, this cluster in using AD. I've found that whenever I use Enterprise Manager on my local computer to register the above instance, it will constantly produce "Failed lo...more >>

Hi, Newbie here. I have a mailing list program that I really like. I also have a new membership program. The membership program has mailing list signups built-in, but it isn't nearly as robust as the stand-alone mail program. If someone signs up for a newsletter when they register (members...more >>

I have several stored procedures with parameters that are defined with user defined data types. The time it takes to run the procedures can take 10 - 50 seconds depending on the procedure. If I change the parameter data types to the actual data type such as varchar(10), etc., the stored procedu...more >>

Am using SQL Server2000 on WINXP. Have been using this DB since it became available. Today, all of a suddent, the Service was not 'started,' even tho I had it marked as 'automatic.' Tried to manually start the service, and received the message that some service start and stop if there is no...more >>

I'm trying to turn a product table into an XML file to create an heirarchical menu, and I was wondering if there was any easy way to do this. The table is (simplified) below: Create table product( category varchar, subcategory varchar, name varchar) and I want the XML to represent the ...more >>

I have a SQL Server job, which runs mutiple steps. One of the steps (step 3) looks for a record in the database. How can I explicitly fail the SQL server job if the database record does not exist? ...more >>

When I need to perform an update against multi-million row table I typically specify @@rowcount, to reduce locks. e.g. set @@rowcount 1000 while exists (select * from myTable where col2 is null) update myTable set col2 = col1 + 'blahblah' where col2 is null However, my boss' sc...more >>

Hello, Within an INSERT statement where the VALUES are expressed, is it possible to evaluate one or more of the values with CASE or IF statements? Consider the following code; INSERT INTO snbat011_app_clnt (entr_cd, app_id, clnt_id, clnt_type, clnt_frst_n...more >>

Im using an ADP to connect to a SQL Sqever DB. In access it was really easy to say Inner join on table1 and table2 and update columnA from table1 with columnC from table2 where table1.key = table2.key and table2 columnB = 1 and table2 columnD = 4 I have tried all manner of beasts to get ...more >>

Deaa group, I am using SQLServer 2000 in an XP Sp2. I would like to do the following: I have a program running on a database server that generates some data which are loaded to the database. This program is used in a web application, invoked by some java program and JSP scripts. (I am f...more >>

Hi, Is it possible to avoid the last used server being in Query Analyzer 'Connect to SQL Server' dialog by default(when I click on File/Connect in Query Analyzer) - I don't want to apply 'truncate table' scripts to a production server by mistake! Thanks ...more >>

Hi, Is it possible to somehow set a SQL Server DTS job to automatically disable itself, when it encounters a fail, so that future scheduled occurrences don't happen until the problem has been fixed? I've hunted about for this on the web, but drawn a blank unfortunately! Many thanks if any...more >>

has anyone met with this before? the setting is SQL2K with SP3 on a 2 node active-active W2K3 cluster. on one of the machine, it occasionally prompts for the following error: "The log file for database "tempdb" is full. Back up the transaction log for the database to free up some log space."...more >>

Hallo, Hot to get unique, sequential number during execution of stored procedure ? I can create table with autoincrement column, add record, get ident_current and delete record each time i need the number. However its not elegant i guess. best regards pluton ...more >>

Hi, Simple question: A customer has an application using Access 2000 frontend and SQL Server 2000 backend. Data connection is over ODBC. There are almost 250 concurrent users and is growing. Have they squeezed everything out of Access? Should the move to a VB.Net frontend taken place ages ago...more >>

Is there such a date/time? I see the Created date on the list of stored procs, but really want a Date Last Updated. After changing code for 3 hours, I tend to forget which procs I've worked on, and which need to be move to production. any simple way to keep track of the last procs played with...more >>

When I try to use Autorun I get an error message that says "A previous program installation created pending file operations on the installation machine. You must restart the computer before running setup." I have restarted the computer many times, but I have continued to get the same message. ...more >>

Hi there, I would like to know how to get rows with duplicate values in certain columns. Let's say I have a table called "Songs" with the following columns: artist album title genre track Now I would like to show the duplicate songs to the user. I consider songs that have the same ar...more >>

hello group, a few days ago i used the following backup statement and everthing worked fine: sp_addumpdevice 'disk','pipedump','\\192.x.x.x\L\Data\test.bak' Backup database Test to pipedump The same statement is not working today!!!!! What's the reason? Please dont tell...more >>

Hello, how to do a select ... from select in MSSQL similar to Oracle? eg. select hour from ( select substring( daily, 9,2 ) as hour from daytab where userid = 12 ) results in "incorrect syntax near ')'" thank you and regards Mark ...more >>

Is there a script that I can run to stop a particular SQL server service on Win2003 server? I'm looking for a similar script to restart that service as well. Thanks. ...more >>

I have a table of zip codes, some with Canadian zips. I'd like to take a zip code and search for nearby zips. For example: Dim theZip As Integer = textbox1.text ....Parameter.Add(@ziplow, SqlDbType.Int, 5).Value = (theZip - 10) ....Parameter.Add(@ziphigh, SqlDbType.Int, 5).Value = (theZip ...more >>

Hi Everyone, I have a table in which their is record which is exactly same. I want to delete all the duplicate keeping ony 1 record in a table. Example Table A Empid currentmonth Previousmonth Supplimentarydays basic 158 2001-11-25 00:00:00.000 2001-10-01 00:00:00....more >>

Hi, I have some C++ code which does the following retcode = SQLFreeStmt(hStmt,SQL_UNBIND); retcode = SQLFreeStmt(hStmt,SQL_RESET_PA­RAMS); retcode = SQLFreeStmt(hStmt,SQL_CLOSE); But sometimes it cores in the third statement. I tried using "SQLCloseCursor (hStmt)" in its place but ...more >>

Could someone help me get the following SQL statement working? SELECT standardgame.gamename, standardgame.rowteamname, standardgame.colteamname, standardgame.dollarvalue, standardgame.gameid, standardgame.cutoffdatetime, standardgame.gametype, standardgame.gameowner, (100-COUNT(purchased...more >>

Hi We've got some numbers stored as Reals which are returning values in scientific notation that we need rounded down to 3 digits to the right of the decimal. ie 8.7499999E-2 needs to return 8.75. Round, cast, convert, formatnumber in the dts package all fail. Help! Thanks Moe ...more >>

Is it possible to retrieve all tables that a given one is related to via foreign keys? ...more >>

Hi guys, i have a little problem here. im attempting to write a stored procedure that compares two tables of the same data structure and adds (inserts) extra records that exist in table1 to table2. My problem is that i dont have a unique identifier between the tables. i think someone ...more >>

Good day, Can SQL Server tools (QA, EM) can be installed on a client machine from a full SQL Server installation on a server? I can't find the CD. Thanks for your time. Eric ...more >>

I have a stored proc that identifies indexes that need to be defragmented ( LogicalFragmentation > 20% ) using DBCC SHOWCONTIG. This works fine and shows me all of the candidate indexes to be defragged. I am using DBCC DBREINDEX to rebuild the indexes. What I noticed yesterday is that it seems...more >>

Since RDMBS and its language SQL is set-based would it make more sense to call a given stored process "Stored Sets" instead of current theorically misleading Stored Procedure, as a measure to prod programmers to think along the line of sets instead of procedure? ...more >>

Hi all, I have MSDE installed on an HP server as some of the utilities required a database. I have also subsequently installed SQL 2000 onto this server which is a windows 2003 box. I know it complains at the start of the installation but I was under the impression that it would still work ...more >>

Hi, When i use to work on Windows 2000 advance server and Sql 2000 I was able to do network restoration by using a mapped drive where my backup use to be lying.What i did was I assigned administrator rights to my login and in Services (mssqlserver)i added my login. Then I was able to access a...more >>

Hello all, I have two tables - Projects and ProjectStruct Table Projects contains master records of the projects, ProjectStruct allows to define a project herarchie and contains the fields PrjStructId, ProjectId, PrjStructName, ..., ParentId PrjStructParent contains a reference to the paren...more >>

I have a strange question and hope someone can shed some light on the problem. First let me give you my scenario. We are using doubletake to replicate our database server files between the local site and off site. I will name these servers srv1a (local) and srv1b (remote). It now time to...more >>