I have a query like: SELECT "ProjectMembers"."RoleText", "ProjectMembers"."Description", "ContactTable"."Name1", "ContactTable"."Name2", "ContactTable1"."Name1", "ContactTable1"."Name2" FROM "ContactTable","ProjectMembers","ContactTable" "ContactTable1" WHERE ("ProjectMembers"."ProjectNDX"=...more >>

Hi All I've just installed MSDE and I want to know how could I work with it. Years ago I've worked for a while with SQL Server and I used Enterprise Manager but it didn't come with MSDE. Which tools could I use ? Also, I need to connect a java application with MSDE. I can't use domain ...more >>

Hello, I have the following problem. I wrote a batch file that runs a sql script on SQLServer 2000. This script must be executed on several databases. This works fine. The problem is that in my database is a table that holds a databases i have to update with this script. What i want is ru...more >>

Let me see if I can explain my situation clearly. I have a table with the columns: answer_id, question_id, member_id, answer - answer_id is the primary key for the table. - question_id relates to another table with questions for a user. The table holds the question and the possible choice...more >>

Hi, I am trying to use Enterprise Manager Wizard to create a new Publication database but I got the following message. Error 2812: Could not find stored procedure MyDB.dbo.sp_MSadd_logreader_agent There seems that some replication stored procedures. Any help? Thanks in Advance...more >>

Hi, we just started receiving the following error messages about 3 weeks ago. We don't get them all the time, or even on the same pages necessarily, but we get them from time to time and our sql server connection is slower in the last three weeks also. Our database is remotely hosted, and the pr...more >>

Please excuse what is probably a no-brainer, but here goes. Is there any difference, in terms of performance or any other pertinent factor, between: SELECT * FROM tblCustomers INNER JOIN tblCustomerOrders ON tblCustomers.fldCustomerID = tblCustomerOrders.fldCustomerID and SELECT * FROM...more >>

Hi, I am writing an application that uses MSDE to store data. Both application & MSDE run on the same computer. I want to regulate the operations done on the DB by the user. For example, I don't want to allow "standard" users to delete records, update certain fields, etc... I can regulate th...more >>

Hello, I have a table with a Day field, defined as smalldatetime. I am filling it from a CSharp application with the following code: DataRow r = dtStaDays.NewRow(); r["Station_ID"]= station_ID; r["Day"] = sd.Date; r["Range"] = rangeTide; etc. Howeve...more >>

Hi, I am total newbie to SQL Server. My new host tells me I can connect through AccessXP or 2002. I have Access 2000. Can I not connect with that also? Thanks, JA ...more >>

Please help SQL Server 2000 Standard I am trying to restore databases on my server and am unable to get them to restore using the database option. For example, the location of my backup file is: D:\MSSQL\BACKUP\MY_DB\MY_DB_ddmmyy.bak But when checking the properties of the restore on...more >>

Hi all! I need your help to realize algorithm for stored proc or trigger. tool: MS SQL server 2000, T-SQL TABLE: [unique_id] [mynumber] [week] [unique_id] - bigint,primary key, identity auto-increnment [week] - int, 1-53, week number [mynumber] - int, 1 - 7, for every week, dail...more >>

Hi my prob is like this.. DECLARE @id int SELECT * FROM Table1 WHERE ID = @ID if @ID is null , I want to select all the records, that ID is null Thanks Dish ...more >>

I have a VB.NEt Winform app deplyed on ServerA. I have a DTS package on ServerB. When I try to load the package with: package.LoadFromSQLServer(serverB, uid, pwd, DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, , , , packageName) Where serverB, uid, pwd are variables set in the program...more >>

I'm trying to access Visual Fox Pro Data from Sql Server 2000, Can anyone have any Idea ? Thanks ...more >>

Hi, I restored my Windows XP system and discovered that I cannot access a MSDE DB that was saved in D: drive (I restored my primary partition in C). How can I restore it? Thanks a lot....more >>

I just bought the book, Build Your Own Database Driven Website, by Kevin Yank, and I'll start working on a database soon. Any other books, etc., you can think of that a know-nothing newbie should read? Is the book above a good one? Thanks! Steve H...more >>

Hello, I have experienced that some of my tables occupies an extremely large amount of pages but with few rows. An example is a table with 37 rows over 22000 pages !. The columns are simple integer and char. I fixed the problem by introducing a clustered index. Now it only uses 1 page. But can...more >>

Hi All, I'm wondering if anyone can point me at some online resources that demonstrate how to run a stored procedure against an SQL Server 2000 server at 6am each day? Any help much appreciated! Much warmth, planetthoughtful ...more >>

Below is a stored procedure i am working with and i am trying to drop the yesno_holding table if it exists but how do i add it back? Meaning i want it to drop if it exists but i want to add it back if it doesnt exist. the reason why i have to drop it is because when i run the stored procedure it...more >>

Hi! I know that you can enable/disable a trigger through the alter table command. Once a trigger is disabled, is there a way to determine whether it is disabled or enabled? I see there is a status column in sysobjects ...is that what I should be using? thanks in advance for any help Karen ...more >>

Hello all, We are in the process of upgrading our SQL physical server (with SS2k). In the process we will change the OS form NT4 to W2K. What is the best way to copy all my databases and SQL logins, roles, jobs, alerts, etc. from my actual (old) SQL Server to my new one? Thanks for your ...more >>

To All, I have a SQL2KSP3a database(<1GB) running on a 4x3GB physical CPU with 4GB of ram. It is Windows Server 2003 with hyper-threading turn on. There are ~420 .Net users/cxns (fat client, no web/app servers) with connection pooling and ~1 trx/sec. The database growth is neglegeable and a...more >>

I'm a SQL Server newbie. I just installed the free SQL Server 2000 desktop engine on my Windows XP system. The installation, done from the command line, did not install any icons for the Query Analyzer, Enterprise Manager, and other things you get with a full SQL Server 2000. (I've done "full" i...more >>

Ok, I work for an organization which receives numerous contracts from numerous clients. Now, with each new contract comes new flatfiles needing importing (with differing formats and fields, etc...), faulty data, etc... It's not as easy as just telling all the contractors "ok, we now need all ...more >>

Hello all. I have many connections that are sleeping. I can right click, kill, up to 2 at a time. It takes about 20 seconds to kill it. Is that normal? I tried to make a stored procedure to kill them but it ate all the power of the server, (all users yelled at me at the same time :o) ) Is ...more >>

Hi Everyone, I've been battling this for two days with no luck. I'm using SQL Server 2000. Here's the mystery: I've got a stored procedure that takes a single varchar parameter to determine how the result set is sorted. Here it is: CREATE PROCEDURE spDemo @SortField varchar(30) AS ...more >>

Hi all, I'm trying to figure out if it's possible to simply replace instances of a given string of text wherever it occurs in all tables and columns for a given database. I'm using SQL Server 2000 sp3. All the update/replace commands I've looked at so far seem to require up-front knowledge ...more >>

When I create a suscription with the option "No, the Suscriber already has the schema and data", and I modify data, I have an error like this: Could not find stored procedure 'sp_MSdel_ac_callejero'. (Source: SE110556 (Data source); Error number: 2812) How can I generate a replication rest...more >>

Hey all, prolly a simple solution, but why isn't the following string working in my execute sql step within DTS? It produces results, just not the ones I want... What am I doing wrong? select x from new_files where x like '%[^0-9]%' and x like '%[^a-z]%' It's displaying all the records? I...more >>

Hello all. I created the "student2" user account and set the password to "student2". I then went to Enterprise Manager and in the class_mgr database and in its customers table, I gave this student2 user account access to the database and set all permissions to the table. But when I log in to SQ...more >>

I am creating a table and would like to know the most efficent way to do this. One of the colums will be text datatype and can have several thousand characters in it (less than10k). The 'ntext' datatype in SQL and hold over a million characters so that isn't a problem. The question is: Is i...more >>

Just wanted to know what is a general rule of thumb when determining log file space against a database's data file. We allow our data file for our database to grow 10%, unlimited. We do not allow our log file to autogrow due to a specific and poorly written process (which we are in a three month...more >>

I have an installation of SQL Server 2000. Both MBSA and @@version show this to be the gold release. When I install Service Pack 3a, it shows no error. But after restart of the server, it's still unpatched, by both MBSA and @@version. Any idea what may be going on here? ...more >>

This is the first time that I am trying to implement replication. I came across with a problem while I was creating my publisher and distributer. I want to create the publisher and the distributer at the same machine however I got this error " SQL Server Agent on <machine name> currently uses ...more >>

I'm trying to create a login in the default domain. I know I can pull this information from xp_loginconfig, but don't see how I can use it in the context of sp_grantlogin. For example, pull the domain the user is currently logged in on and insert it into the sp_grantlogin script. Has anyone ...more >>

What would be the best practice to follow to keep track of MS SQL server changes... Stroed procs, tables, views, triggers, indexes, DTS and also jobs ect.... I am not quite sure how Source safe works with sql server. Any other way to do this... Even if its manual work, its okey.. I would appr...more >>

Hi, Im running into a strange problem here! I have created a linked server on one of my SQL Servers (local server). Lets call the linked server as LS1. I have an sproc which resides on the remote server(LS1) and uses a FOR XML query. On running this sproc from my local server using linked ...more >>

Folks, We're implementing a software based mirroring solution. This solution requires that the SQL Server service on the target server be stopped while data is being replicated from the source server. One problem we encountered while testing was that the SQL Server Service on the target ser...more >>

Hi, I've just started using ADSI to pull in info from our Active Directory tree into SQL Server 2000. I've made the link ok, and can pull in most fields (cn whenCreated etc) fine. However, there are some date fields (the one I'm interested in is pwdLastSet) that are represented as a long nu...more >>

Hello! It seems like I have some misunderstanding about the way transactions work and would appreceate your help. I want to delete records from 2 tables. Some records may not be deleted due to reference constraints in which case no effect must take place (i.e. no records must be deleted). ...more >>

I guess I don't fully understand what IIS does/is! I can appreciate the need for the host/server to be running IIS but it 'appears' restrictive for your clients to need to do the same. I have created two web projects, both with host and client apps. One is a web service and the other using .NET ...more >>

Greetings - I've been poking around online, looking for an answer to the question, "What sort of pool of technical expertise is available in <name of city>?" I'm in the initial business plan stages of a new software company/product and balancing cost-of-living/lifestyle quality issues agains...more >>

I have a log file that went out of control (147GB), I feel that doing a shrink on the file/database would be time consuming so I am thinking of recreating the database from an imported version from our test server. I know that data can flow from one SQL server to another without regard to the SQL...more >>

from the December 2004 _Redmond Magazine_, "Finding Gold in Yukon": http://redmondmag.com/columns/article.asp?EditorialsID=838 "Developers will also pick up the usual new-version enhancements to T-SQL, including error handling, recursive queries, a ROW_NUMBER function, a PIVOT relational opera...more >>

Hi, I was using enterprise manager to generate a script for my DB. I scripted only my tables and views and in Options I picked all the options EXCEPT "script Primary Keys, Foreign Keys and Constraits " ( which I was going to script seperately ). I noticed that the the generated file still h...more >>

Hello, I am setting up log shipping between two SQL servers. Everything in the setup seems to work fine. The problem I am having is that the "Log Shipping Alert Job - Backup" job is failing, event though the tlog backup copy & restore process is working fine. When I look at the histories of b...more >>

Is there a way to back up the design changes in a SQL Server database without backing up all the data? It's just test data at this point. ...more >>

Newbie here. I've got my database set up in SQL 2000, and have started an Access adp for a front end. I have 10 licenses, and at the moment the only accesses are the server through Remote Desktop and one person into the ADP. I started getting ODBC timeout errors when running a complex query...more >>

Hi all; I am trying to replicate our database by using the merge replication. >From server side I tried to connect to the workstation. I have installed MSDE to the client machine. I can see the machine name on the SQL Server Group however I wasnt able to create the conncetion between the mach...more >>

Hello, I'm new with triggers and I can not find any good example on how to do the following: I have two tables WO and PM with the following fields: WO.WONUM, VARCHAR(10) WO.PMNUM, VARCHAR(10) WO.PROBLEMCODE, VARCHAR(8) WO.LABORGROUP, VARCHAR(8) PM.PMNUM, VARCHAR(10) PM.PROBLEMCODE, ...more >>

Hey everyone -- I've been having this problem for a couple weeks and was hoping maybe someone can help out a little bit. Synopsis: This SQL agent job contains code to perform a full database backup with verification and is writing to a local disk array. I typically also use some code for...more >>

Hi everyone. I have a question that maybe some of you may be able to shed some light on. My set up involves three SQL Server Databases. First one, used by the application. Second, copy of first via log shipping. Third, contains data aggregation from the Second. Now, my issue is that I ha...more >>

Hi All, I have several questions about checking backup file : a. When SQL Server receive a command : BACKUP DATABASE test to DISK = '\\pc1\netdb\BTEST', does it verify BTEST file before ending the job ? b. Is there any special command for checking / verifying a backup file ? c. I...more >>

Hi, I am using Merge Replication for synchronizing a bunch of laptops running MSDE with a central SQL Server 2000 production server. This database is used by an application that needs to know which records have not yet been replicated, at any givem time. Here's a simple scenario: User ...more >>

hi thank you for this solution Hugo (dutch?) and it's very handy, thank you but my issue is that we created dozens of views with my function. This functions returns an integer of a date. This function has to give a lowerlimit and higherlimit back when dates are out of Calendar range. But i wa...more >>

Hi, I have a peculiar problem (or maybe not). I have SQL clients installed on XP boxes. These XP boxes are a member of let us say "XYZ" domain. SQL Server is installed on one of the boxes having Windows 2000 Server in the same network. The installation has been done on local domain account. S...more >>

The field itemdate is a datetime field in sqlserver2000 DB This works fine: Select Id From BookData Where (MONTH(itemdate) = '01') and (DAY(itemdate) = '02') and (YEAR(itemdate) = '2005') order by TitleDuplicate This does not work: Select Id From BookData Where (MONTH(itemdate) = '01') and...more >>

Hi All, I want to shrink a transaction log using : USE Master BACKUP Log test WITH truncate_only USE test DBCC shrinkfile(test_log, 0) These commands will be executed from a workstation. After shrinking, I want to see the size of the transaction log and database files. But, ...more >>

SQL 2000 Windows 2000 sp4 I have created a group called "queryonly" in Active Directory. I have added myself to this group. When I attempt to create an ODBC connection to the SQL server I get "connection failed". When I add myself explicity (ie domain\user) it connects fine. Why is the g...more >>

I have a complex query (16 table join) that until five days ago took 30 sec to run. It now takes eight hours. I restored a backup of the database from five days ago and the query plans are completely different for the same exact query. Five days ago the final estimated row count is 1.6M now i...more >>

Hello all, Over the years as I design more database schemas the more I come up with patterns in database design. The more patterns I recognize the more I want to try to design some kind of generic design patterns that can be used and shared amongst many sub-schemas. For example, the gro...more >>

I was wondering if anybody has a document that states do's and don'ts when programming for a MS SQL DB. I was under the impression that Microsoft once produced such a document. Rather than a "Best Practices" type document it was more of a "Worst Practices" document. Stating certain procedures th...more >>

I am doing a monthly report to export to an excel Doc from SQL 2000 using views. With my design it works great until it is the first of the month. As the report always checks from yesterday to the first of the current month. One view is below. The date statement checks between the first...more >>

I have a table I insert a record into to give access to a user. It uses primary keys so duplicates are not allowed, so trying to add a record for a user more than once is not allowed. In my .NET programs, sometimes it's easier to let the user select a group of people to give access to eben if ...more >>

I have SQL Server desktop installed on my laptop. I have a database on these that i created. but when i try to back it up to disk, i have no choices available to select either disk or tape. i still try to click on the 'add' button to select a file and it fails. i checked the settings and...more >>

i use set nocount on inside the stored procedure at the beginning however the Number of Rows Counted/Affected shows up when I execute the stored procedure in query analyzer using execute sprocName. I also tried to add the SET NOCOUNT ON at the beginning of the procedure and it still shows the...more >>

Because of an error in google or underlying site i can reply on my own issue. Therefore i copied the former entered message in this message. -------------------------------------REPY---------------------------------- Hi Maybe i wasn't clear. I want to dynamically check whether what the lowest da...more >>

I want to grab what is in textbox1, put in string, and send as a query to bring up report with only textbox1 occurances in table. How do I do that please? Thanks, Trint ...more >>

I have this query that I would rather not turn into a stored proc. because the client really has no budget. I was hoping there would be a way to add this statement inline to the following sql with some kind of IF statement without writing it all out. The problem is either total cost or convers...more >>

I have been using SQLDMO listavailableSQLServers . It displays all the servers but it doesnt list the local machine in which SQL Server too is running. Am i missin something? ...more >>

Hello all. I have an administrative account setup in Win XP Pro and I also have a user account named "Jim Richards" (kinda logical, huh?). I previously had to switch to the admin account to access the SQL Server Databases. Then I gave myself admin privalidges to the SQL Server to the "Jim Richard...more >>

Hello all. I have an administrative account setup in Win XP Pro and I also have a user account named "Jim Richards" (kinda logical, huh?). I previously had to switch to the admin account to access the SQL Server Databases. Then I gave myself admin privalidges to the SQL Server to the "Jim Rich...more >>

Hey...I've been cracking head about this one all day, and I'm sure it's an easy answer, but here goes: I have a column entitled Sequ which is defined as a Decimal with Precision 10 and Scale 5. In an ASP.NET page, I'm performing a calculation which is inserted into the Sequ column. I'm doing...more >>

I want to reformat my servers harddrive and install a newer operating system (Win 2003). I am running SQL Server 2000 with about 10 databases. What is the best way to backup and restore SQL Server for this so I don't lose anything. Thanks, Rick ...more >>

When I export a report to PDF format, the bookmarks pane is closed, and needs to be pressed in order for the bookmarks to open. Is it possible that Upon opening the PDF, the bookmark pane will be visible ? ...more >>

Hi, I'm trying to write a function check if a column exists on a table, and creates it if it doesn't. The line that the query analyzer is citing is noted. It seems unhappy taking variables in the ALTER TABLE command. I can think of a hack around this, but I'm hoping there's a better way? ...more >>

I have MS SQL server 2000 with MOM server. I was able to shrink the database file, but when i try to shrink the log file i fail. I use "shrink database" wizard and try to reduce the log file from 640 MB to 32 MB and i select this number in "shrink database to __" and wait. I got a success mess...more >>

Greetings All, I was wondering what would happen if I were to do a "select * from table" on a table that has about 5 million rows. Would my read block other writers to the same table? Would it block other readers? I know SQL uses optimistic lockign by default but I am not sure what this means...more >>