Hi - We have two SQL 2000 Servers. We have the linked server setup and we can perform updates and inserts between the databases. But when we add a trigger and insert something into a table, the database hangs. There are NO processes blocking or being block in either database. This ONLY occ...more >>

Hello, I developed a win32 .exe CGI that connects to a clustered SQLServer to report some data. The software is written with Borland C++Builder. This is the oledb string: Name=Provider=SQLOLEDB;Password=xxx;Persist Security Info=True;User ID=xxxx;Data Source=xxxxx;Initial Catalog=xxxxx;Network...more >>

I'm looking for ideas on how to write SQL scripts for updates that are pushed out to clients for product updates. Obviously, We could just keep track of the changes on a pad or write a database that requires us to input those changes and eventually hand write the update scripts. I was wondering...more >>

I have the following query: SELECT Month, Sum(Hits) AS Hits FROM tblHits GROUP BY Month ORDER BY Month Unfortunately it only returns rows for months that have data assigned to them. How can I tweak this so that months 1-12 are returned, and Hits = 0 for months with no data in the base ta...more >>

Does a product exist that might solve the following problem? I have an application developed in VB6 that accesses data residing on a central Microsoft SQL server in our datacenter. While I would argue that this application stinks, I have been assigned to deploy it to the laptops of about 100 p...more >>

Hi, How would I go about writing the results of an SQL query to a text file? I cannot find any info in the Online help files. For example, I would like the results of: SELECT * FROM TableA To be written to the file result.txt Alex ...more >>

I want to set an alert for a specific table whenever an event has caused a deadlock to occur on the table. I understand how to set up an alert. But I don't know which error number to use for the New Alert error number property for a deadlock. Or how to specify a deadlock on a specific table. ...more >>

You may have a requirement that you cannot change where you have old apps, such as Delphi 3 apps (in my case), that must hobble along until the BIG migration money comes into migrate to something better. And those old apps may require that you keep SQL 6.5 on the backend. In my case, that's the ...more >>

Hi all I'm trying to create a summery table of rain data that holds a record for every 0.1mm of rain in the following format: Station Name, Time, Value A, 2002-12-03 14:44:41.000, 0.1 A, 2002-12-03 14:45:45.000, 0.1 A, 2002-12-03 14:49:45.000, 0.1 A, 2002-12-09 05:30:35.000, 0.1 A, 2002-12...more >>

Here's the situation, developers inherit a web app from someone, backend SQL db has about 120 user tables and the db is also being used by other apps. Developers don't have a list of user tables being used by this app, now, I need to create a new db based on this one, which would be used by t...more >>

We were running a 4 processor server with 8 GB RAM and 800GB hard disk space on Windows 2000 server and SQL Server 2000. Recently we have upgraded the server to 8 processor and added another 800GB. The OS was upgradded to Windows 2003 enterprise edition. All the processor are 2.5GHz Xeon preoces...more >>

The following query needs about 2 minutes to complete (finding dupes) on a table of about 10000 addresses. Does anyone have an idea on how to speed this up ? Thanks in advance !!! Sebastian Select Top 1000 * From addresses ab1 Where ( Select Count(*) From addresses base ab2...more >>

I have a database file LEAR_Index(yes, it hold index data) from a have have recently removed a bunch of data. It is about 120 Gb, 100Gb of which is not used. I wan=B4t to shrink the file to lean 30-40Gb. I=B4ve been trying this: dbcc shrinkfile('LEAR_Index',40000) But to no apparent avai...more >>

Hi, I have a website using a SQL Server database to store all of it's data. Retrieving data and writing basic data back is fine, however, when i go to update one table that has a text field in it the update fails if the amount of data being passed to the text field is too large. Is there a...more >>

Dear All, Please suggest some of the best practices for writing SQL server stored procedures? I'm writing a business function (stored procedure), which calls many-stored procedure one after another. I want this to be best optimized, so that speed can be very good. Suggestion in this regard...more >>

Hi All, I want to avoid using cursors and loops in stored procedures. Please suggest alternate solutions with example (if possible). Any suggestion in these regards will be appreciated. Thanks in advance, T.S.Negi...more >>

Hi All, I want to give sequence no group wise, as follows: Section EmpCode ----------------------- A 1001 A 1003 A 1009 B 1023 B 1030 C 1044 ----------------------- I want to set sequence n...more >>

I posted a link to a prior article in here, that one about high performance hierarchies, and have the first two parts of a new series. Hopefully this is of value to someone. http://www.yafla.com/papers/SQL_Server_Performance/high_performance_sql_server.htm Thanks....more >>

Greetings: I would like to ask for recommendations for a good beginning book on SQL Server. My web host offers a 100MB SQL Server database as part of the package, and I'd like to use the feature to learn. I am proficient in Access and familiar with basic connectivity to it via ASP. I don'...more >>

I have 2 tables (names A B) i need (In SQL stetments) to search from table A if rows in table B with this conditions: A.col1 = B.col1 AND A.col2 * (0.5) > B.col2 AND A.col3 + (0.5) > B.col3 Best Regards, Yuval...more >>

The place where I work is moving to MS SQL Server from Lotus Notes. I have done a lot of coding in Lotus Notes, and have, I suppose, intermediate skills in basic SQL -- queries, insert, updates, table design, etc. I have a couple of questions, however. First, stored procedures vs. functions. In ...more >>

Help, I'm stuck! I have a Customer table: ID Name -- --------------------- 1234 Christopher's Surf Boards 4321 Christina's Nails I have to build a Account table: ID GPID -- -------------------- 1234 CHRIST00 4321 CHRIST01 I've built a f...more >>

Hello I am trying to rebuild indexes on our DB, but I am running into an error I am not able to solve. Server: Msg 1105, Level 17, State 2, Line 1 Could not allocate space for object 'ft' in database 'HastaDemo' because the 'PRIMARY' filegroup is full. The statement has been terminated. ...more >>

H friends I have a strange problem. i am trying to connect to my sqlserver from client machine to server machine But i am not able to connect to the server from the client machine. The strange thing is that i am able to connect through Named Pipes but not with TCP/IP and i get error SQL Serv...more >>

Can someone please point me to some code (preferably C#, but C++ or C or VB will work) that will detect if the (local) instance of SQL Server is running on a machine or not? Many thanks! -- Rob...more >>

I am trying to edit a long text cell in Enterprise Manager but when I select "<long text>" and try to delete it, I get an error saying "cannot edit this cell". Could anyone tell me how to edit this cell? Thanks, Amanda ...more >>

can't figure out why the following locking scenario works the way it does: spid 1: start transaction select a row from table T1 with updlock result: see an Update lock for the row and index key spid 2: query for the same row as in session 1 result: the query succeeds spid 3: ...more >>

Dear group: I have removed my hard drive from my laptop (which is now toast) and have managed to recover nearly all the data from it by installing the drive into my desktop. I was hoping to reboot the dektop to see if I could load the operating system on the laptop's hard drive so I could do...more >>

I keep getting an error message "incorrect syntax near keyword case" when trying to run this: USE DEDUPE GO CREATE FUNCTION fnCleanString ( @mString varchar (255) ) RETURNS varchar(255) AS BEGIN DECLARE @mChar char(1), @msTemp varchar(255), @miLen int, @i int, @iAsc ...more >>

I created a custom DTS object that does its work by calling a webservice. When you create the object in DTS, it lets you set the user/pw you want to use to authenticate to the webservice. The problem I am having is that once I authenticate to the webservice within DTS, it always uses the sam...more >>

I just started a new job and no one seems to know the SA password. Here's my plan to change it: 1. I've run a trace for a couple of days to verify that there are no jobs or processes that are connecting as SA and that would break if I changed the password. 2. Before the changing the passwor...more >>

I would need to create multiple variables with the same format for future update, what I did is listing them separately, is there an easy way to combine them in one step? please see query below. Thanks a lot! --Current Query-- SELECT cast(0.0 as money) as balance_1 ,cast(0.0 as money) as bal...more >>

I am running SQL 2000 SP2 on a Win 2000 box. I attempted to restore a dB a couple of times and the restore failed, but the dB appears in EM twice, once with Loading and the other with Read-Only. I stopped SQL and attempted to delete the LDF & MDF files, but there was a sharing violation. H...more >>

Hi all, I have a sql puzzle (which I've solved) but seems very unwieldy. Is there a more efficient way of doing this? It reads like so: SELECT a.* FROM axindex a INNER JOIN axindex b ON a.id4 = b.id4 AND a.id1 = b.id1 AND a.id5 = b.id5 AND a.conflict_type =...more >>

I have a SQL Server 2000 table with a few fields of "text" data type that contain rich text. I have to downstream this data and the recipient cannot handle rich text. I need to figure out a way to convert it back to plain text. Any suggetions? TIA ...more >>

Hi, I have a SQL server DB for a document handeling system. For the new version of the system the database have been updated with some new tables, new stored procedures and updated stored procedures. There is no changes in the existing tables that contains data. What is the best way to upda...more >>

Hi, Our company is an independent Voice applications solution provider with number clients using our suite. We have a CT application suite which is running with Application Server and SQL Server 7 / 2000 as DB Engines at the back end. The SQL server has two databases configured: Logging ...more >>

Hello there, This is a bit of strange one. My local version of SQL Server fails to start when I boot yet if I start Enterprise Manager its started and working fine. ISQL or Query Analyzer is also not working. If I try and register the server by another SQL Server is not there. Me thinks th...more >>

I=B4m recreating some clustered indexes on my database tables, and i'm planning use DROP_EXISTING like this: CREATE CLUSTERED INDEX [idx-clusteredindex] ON [dbo].[TABLE_NAME]([COLOUMN_NANE]) WITH DROP_EXISTING, FILLFACTOR =3D 90 ON [PRIMARY] ...more >>

Hi, I have an orders database and I need to be able to write a query that groups sales by: 1. Date 2. Time of day (both am and pm) I currently have a date/time field named "Submitted" that stores just the date. However, how do I store the time? ...more >>

My company is working on a bond derivative portfolio analysis tool and we're facing a problem that I did not see adequately addressed any where in literature. I really did RTFM. I'm very experienced in relational modelling (10+ years) so this is not a case of not understanding the principles. He...more >>

Hi If I register my own proc via exec sp_addextendedproc 'xp_myproc', 'xp_mylib.dll'. How then I can look all my own procs and dll's? In which tables are this data saved? Thx....more >>

Hello, I'm currently developing an access application which uses sql server as a backend. What i'm uncertain about, is how i should setup security without creating too many sql server logins. I'm convinced that i should limit access to sql server, otherwise people can get in with other mean...more >>

Hello, folks. I have the following query that I run that pulls up the count of the = number of orders of a product. SELECT Product, Count(Product) AS Total FROM Orders_View GROUP BY Product, Year([DateOpened]), Month([DateOpened]) HAVING (((Year([DateOpened]))=3D2004) AND ((Month([DateOpen...more >>

I am running Win XP SP2 and MSDE 2000 SP3a. The user is logging on as a POWERUSER. When the user logs on the MSSQLSERVER service starts but the SQL Server Service Manager, that runs in the system tray, shows that the instance has not started. The field that displays the instance name is blank...more >>

How do you set a stored proceedure for automatic execution? -- Message posted via http://www.sqlmonster.com...more >>

I feel like the answer to this should be blinding obvious. I also feel like it's probably an exercise in an undergraduate database design course. I'm off to google for an answer, but I figure I'd throw it out here to see if there's a quick and dirty solution. I have in my database a tabl...more >>

I wonder if anyone has any hard fact based pro or contra, especially on performance, about having views as opposed to tables when the object is being accessed for read only by multiple sessions/users/spids. I am particularly concerned about the multiple instantiations of the view. Relevant thou...more >>

I have been told by a local PC club technician that 98SE cannot read NTFS drives in a network. Is this true? TIA, Jim. ...more >>

Hi SQL Gurus Could anyone please explain how the following stored procedure can be amended to retrieve Subordinates in alphabetical order ? The example below simply retrives records in the order in which they were entered. It sounds very easy but I can't sort it out ? The following cod...more >>

I have to get a stored procedure to produce a result set then loop through it and insert records into another table in the same db. What I have written is causing SQL server to hang or some other performance bottleneck. Here is the code. Anybody got any ideas? Thanks! ALTER PROCEDURE initQue...more >>

I know it is possible to establish a connection to an SQL Server over the Internet via Enterprise Manager. My question is, how secure is this connection? Is there a best practices document for this type of connection? Here is my scenario: We are looking to outsource our web hosting. Current...more >>

I am trying to use table 'Account' and table 'Balance' to get the ideal result table listed below, but my query is too cumbersome. If an account opened 10 years ago, I would have 12*10=120 rows in my query. Is there any simplied way to do this, like a do-loop thing? Thanks a million! Table Ac...more >>

Hello, I have successfully linked a Lotus Notes server to our SQL Server database using an ODBC connection. This works fine when wanting to select records eg openquery(LOTUSNOTES2, 'select * from Person' ) The problem I have is when I try to update the record I get an error eg update ope...more >>

Hello, I am trying to update variable 'Type' (which is a pre-defined variable without any value in it). Is there a way to combine Step1 to Step3 into one single Update? I tried the query at the bottom but it didn't work. Thanks a lot! --STEP1: Update TABLE1 set type = case when name lik...more >>

Using SQL Server 2000. When does SQL flush or clear the procedure cache? I am dynamically creating and dropping stored procedures (SP). Does SQL clear the cache for the SP that has been dropped? If not, when the SP is recreated (with the same name), does SQL use the execution plan from cache?...more >>

How do you declare and then SELECT a value for a local variable within stored procedure, increment the value and then use in an Insert statement? Thanks Any sites that explain this syntax for SQL Server 2000? Thanks hals_left CREATE PROCEDURE [dbo].[InsertQualUnit] @QualRef tinyint, @...more >>

I've recently inhereted an environment in which we have over 600 stored procedures. The documentation is either very poor or non-existant and am needing advice on how to determine if a stored procedure has been used. Does SQL Server have any sort of ticker that indicates when a stored proced...more >>

Hello there! I have on a machine (named SV-ISDEV) two MSSQL instances installed. The local and the SV-ISDEV\PREPROD. Now I want to unistall the PREPROD instance. Which is the correct way to do this? Do I have to run the installation again? Thanks ...more >>

Hello, I am a very complex problem in front of me. Kindly help me out in acheiving the same. Say I have a table called InfoName with two columns Name and ID InfoName Name ID OS 1 SP 2 Driver 3 fasdf ** ** *** ** **** (I AM INTERESTED IN ONLY FIRST THRE...more >>

Working on some new code, I'm coming across WHILE loops used instead of cursors. I was curious if anyone had any stats on how the speed of doing this compares to the speed of a cursor. I typically avoid cursors for performance sake, but I'm not sure how this avoids the speed hit of a cursor, s...more >>

Hi All, I'm trying to make a connection with the SQL server from one of my coleagues, What are the steps I must take to do this? I can ping the server, with success! I have a userid & password for the SQL server,.... Please help !!...more >>

Hi, Can someone please explain the following 1. Meaning of scan count as reported when "statistics io" is turned on prior to running a query. 2. In which situations could you have an identical database running on two diferent servers , with identical database server configurations, run...more >>

Hi, I have just reinstalled Reporting Services on win 2003 server that was added to a domain and has been renamed. Two strange things happen: 1) In IE I am prompted for an ID and password with a basic security prompt. 2) After supplying the credentials I get some of the report manager web pag...more >>

Ok here goes, another odd SQL Question ....as always.. How do I get ... The value of a paramater passed to say a Trigger OR The SQL Itself Like this , say I have a Trigger set on delete, Now this trigger will add a row to another table (a history table) but it need to be passed a paramat...more >>

Hi I've the following SP: ----------- CREATE PROCEDURE spServicios @numero int, @maxdif int, @resultado int OUTPUT AS BEGIN SET ROWCOUNT 1 UPDATE control SET registro = getdate() WHERE (fecha >= getdate() AND fecha < dateadd(mi, @maxdif, ...more >>

I want to change the location for the transaction log to a differnent disk Can anyone tell me how i can do this. Because sql servetr tells me "i can not delete the primary data or log file" when i try to delete the transaction log. Many regards Nico Heiligers ...more >>

Hello all. I have just built another PC (New PC)and have it connected to my Original PC via a LAN. I have my SQL Server 2000 installed on the Original PC. Both PCs use WinXP Pro OS. I believe I now have to install the Client Tools on the New PC but after that, how do I access the SQL Server th...more >>

Over the years I have always used the decimal(18,0) as the datatype for primary keys. Aside from the number of significant numbers involved, would BigInt type be better for performance or is decimal(18,0) still okay. -- Don Vaillancourt Director of Software Development WEB IMPACT INC. ...more >>

Hi, folks. I'm fairly new to SQL Server, so forgive any stupidity inherent in this question. I work at a university and a certain faculty member would like to have her students create a simple auction web site as an exercise. As part of the assignment, she wants the auction site to be able to ...more >>

Approximately once a month, a client of ours sends us a bunch of comma-delimited text files which I have to clean up and then import into their MS SQL database. All last week, I was using a Cold Fusion script to upload the cleaned up files and then import the records they contained into the data...more >>

Will the next release of SQL Server 2000 64bit sp provide performance counter? MarcM ...more >>

I have an SQL back-end with and Access front end. A table in my SQL db has an auto-num field (integer) that has exceded 70000+ entries. I can add no more to this table unless I convert that field from INT to BIGINT. Of course, Access doesn't seem to know what a BIGINT is... suggestions? ...more >>

Hei, We have 2 MS SQL SERVER 2000 installed on 2 different servers (2 separated machines). I am triing to connect them så that when one row is added to the table in the database in main server - then the same row is added to the same table in the second server database. I made the insert trig...more >>

I am using a tool called AppDetective to identify database servers and then to scan them for vulnerabilities. I've just been handed a list of machines and have been asked to determine which ones are actually MS SQLServer. (AppDetective says that all of the systems on the list have MS SQLServer...more >>

I have the security audit logging failed attempts on my MS SQL server. Is there a way to log the ip address of these attempts? ...more >>

Hi, I have two tables Table A and B, below with some dummy data... Table A (contains specific unique settings that can be requested) Id, SettingName 1, weight 2, length Table B (contains the setting values, here 3 values relate to weight and 1 to length) Id, Brand, SettingValue 1, A...more >>

.....more >>

Sorry for my bad english What is the optimization technique used by SqlServer dbms? Thanks...more >>

Hi Is there any way to store gitar codes in sql server table. or want to set specific "COLLATE". Thanks Dishan ...more >>

Hello friends, I am a begginer in MySQL. Kindly guide me how can I install and run MySQL server on my machine (Runnig Wnidows XP professional and SQL server 2000). Is it neccessary to remove SQL server from the machine before installing MySQL and what version of IIS is required? Kindly help me i...more >>

Hi, I am very new to SQL, but would like to learn it. So please bare with me... I understand that SQL is a database manipulation application. From my research, it sounds to me like first, I need to teach myself how to build a database (using Microsoft SQL server, or something like it). THEN, ...more >>

I have a dual cpu machine. How can I dedicate a cpu for sql server only? -- John Dalberg...more >>

use this-is-a-stupid-name returns an error For that matter select * from this-is-a-stupid-name.dbo.anytable returns an error I've tried single quoted, double quotes, escaping. In the SQL editor I can select the name from the dropdown and I'm OK - but I'm writing a perl script to...more >>

I have a couple questions I hope someone might be able to answer, or rather point me in the correct direction. I am an independant developer and I am working on a small CRM for small businesses. Nothing fancy, but built with c# and the .NET framework. In my study of other CRM's like Microsof...more >>

We are using the bcp utility (via APIs) to export data from a SQL table in a fixed format text file. BCP is inserting spaces for a field if the field contains a NULL. This is fine with us except at the end of the line, there are no spaces for that field just the end-of-row terminator premature...more >>

Hi I've a SP in MSDE that will update a table. How could I know if it worked to return a value to the SP caller ? Thanks in advance J...more >>

I have Enterprise Manager version 8.0 When I try to register an online databse provided by an ISP I get the following error message: "SQLServer does not exist or access denied[]ConnectionOpen(Connect()" I have tried it many times and checked user name, password, port 1433 (as required by...more >>

I am inserting records into a table - around 1 million records. I want to do the insert 10000 records at a time using TRAN. My insert statement is very simple: BEGIN TRAN T1 Insert INTO TABLE A (ColA, ColB, ColC) Select B.TBLBCOLA, B.TBLBCOLB, B.TBLBCOLC FROM tbltable B WHERE blah balh...etc...more >>

create function dbo.iso6346_char_to_number ( @char char ) returns int as begin return case upper(@char) when '0' then 0 when '1' then 1 when '2' then 2 when '3' then 3 when '4' then 4 when '5' then 5 when '6' then 6 when '7' th...more >>

Greetings All, I was hoping that someone out there has run into this issue before and can shed some light on it for me. I have a stored procedure that essentially does a mini ETL from a source OLTP DB to a the target Operational Data Store. This is still in development so both DB's reside on t...more >>

I have a SQL 7 db with a union query (view), and I'm getting the error, "The query processor could not start the necessary thread resources for parallel query execution." This union query has been in place for about two years now with no problems until just now, though I haven't changed anythi...more >>

Would it be OK to use varchar(5) instead of char(5) as the first field of a composite clustered index? My gut tells me that varchar would be a bad idea, but I am not finding much information on this topic on this when I Google it. Currently the field is Char(4), and there is a need to incre...more >>

Is there a way to allow users to see the design view of a table without having dbo permissions? Thanks...more >>