I have a table Create Table Payments { paymentid int, customerid int, amount int, date datetime } What I want is the sum of the amounts of the last payments of all customers. Now the last payment of a customer is not necessarily the one with the highest paymentid for tha...more >>

Hi, I'm wondering if there's any advance order by 'function'/workaround, which is reasonably efficient for MS SQL Server, without resorting to some third party indexing/search engine to achieve the following. The mechanism is to record each instance of a pattern match and order by rows with...more >>

I have a need to become familiar with SQL Server 2000 for work. Needless to say I am new to SQL Server any version, but not IT in general. My employer has provided me with the SQL Server 2000 Personal disk from the SQL Server 2000 Enterprise kit as this is reported here on the MSDN web site to...more >>

Hi All, I have question and that is-- if i have a database that is implemented in MSDE Now i want the same database in MSSQL. What are the steps i do need to follow for this. Thanks in Advance Hoque...more >>

Hi, This may be incredibly stupid question but how do you execute a sql statement from within a dts transform data task. I'm transfering data from one table to another one on the same database on sqlserver 2000. Most of the rows are copy columns but some require some manipulations done on ...more >>

hey! Im looking for a db tutoral which covers accessing a Microsoft SQL database on windows. I will be using VC++ if it matters. NO details just a basic introduction! Oracle DB access using C++ is also of interest! Thanks! -- Majk ...more >>

On the re-boot required for critical patch KB824105 MS SQL Server failed to recgnize the log on for the account it is registered under, and all other accounts when manually cahnged. It seems to have completely lost all ability to recognize and logon. SQL is totally hooped - no ODBC at all at...more >>

I have a need to insert rows into an Audit type table when values change in certain fields in a table. I thought I could do this via a trigger. However, on requirement is to include in the audit both the old and new value. Is there a "simple" way to do this? I know I could query the table ...more >>

I know this is super-basic, but I'm a newbie and I can't get it to work... I'm trying to call a stored procedure that has a datetime as one of its parameters. How the heck do I get a datetime?? I'd even settle for knowing I was declaring variables correctly... DECLARE @Date datetime (right...more >>

I have a book catalog where some books have more than one author, hence I've made a one-to-many table where I have the book isbn matched up to the author id. I have a table called books that has the book title, etc. and an author table that has the author info. I join these to on the book_to_a...more >>

I realize that FK cannot be created across databases. The question I have is what are the alternatives? In my thinking I have two choices. 1) First choice (which I read in a different post) is to use a stored procedure as a constraint to go check the table in the other database. 2) Second...more >>

Hello all, I've got a query which suddently became very slow. It now takes about 10 secs instead of 2 secs. I've got to identical DB (one is for test and the other is production). The query is slow only in production. When running this query in both DB and looking at execution plan, stat...more >>

Hi, I changed the login for MSSQLSERVER service for 6.5 box to "This account" from "system account" and then again changed back to "system account". Now I cann't connect thru Enterprize Manager to my server. All my services r running and I can connect to my database thru an application as bef...more >>

Hi! In order to debug a program written for us I'd like to log all queries to a database/a specific table in MS SQL Server 2000. Are there any options to get a logging like this and how do I enable it? -- Marco Dieckhoff icq# 22243433 GPG Key 0x1A6C95BA -- http://www.frankonia-bruno...more >>

Hi, I'm planning to purchase Sql Server2000 St. Edt. and install it on windows 2000 server machine. I would like to know please if it is advisable to work on the same machine that host both servers or should I work on a separate one??? MTIA, Grawsha...more >>

We just implemented a full-text index on our product master table, however the users are now screaming because they cannot search on some of the special characters that are commonly found in our product descriptions, specifically the #, %, and period (.) These characters are not in the Noise f...more >>

Hi, I've written a TSQL procedure which adds 2 columns to 2 tables. One table is in the database where the TSQL procedure is written. The other table is in another database. (Alter table databasename.dbo.tablename ADD column char(6). I can not save the stored procedure since that column is ...more >>

Hi, When i eg. manually ad entries to a table and, cancels the insert Ms SQL increment the counter on the ID anyway. Is there a way to avoid this behavior? Regards Anders ...more >>

I am trying to convert a complex function from Oracle to SQL Server and have come across Oracle's Instr() function. I see SQL Server has CHARINDEX() which is similar, however it does not provide some key functionality I need. Here is an example of the Oracle code: if Instr( sTg , cDelim, 1, ...more >>

hi there Created sproc - it stops dead in the first line Why ???? Thanks in advance CREATE PROCEDURE [dbo].[test] AS insert into timesheet.dbo.table1 (RE_Code, PR_Code, AC_Code, WE_Date, SAT, SUN, MON, TUE, WED, THU, FRI, NOTES, GENERAL, PO_Number, WWL_Number, CN_Number) SELECT ...more >>

Using InstallShield Developer 7.04. Does anyone know of a good way to detect if SQLDMO is installed? TIA mcpoo...more >>

Does anybody know what might cause the following message to show up in the SQL Server Error Log?: Time out occurred while waiting for buffer latch type 2, bp 0x12260f80, page (5:77914), stat 0x40d, object ID 7:421576540:0, waittime 500. Continuing to wait. I've read several articles about w...more >>

Is there any function that would change a NULL value to zero? I am writing a view that need to calculate the net inventory by subtracting the export qty from import qty. However, in case of no export, 1-Null=Null. Thanks. ...more >>

I have two tables in SQL 6.5 database with identical fields and indexes. One contains the data of August 2003 and other July 2003. Now the august table is larger ( about 40000 more rows ) than the july table but i've noticed that the same queries perform much faster on the august table than the j...more >>

How to do the following using sql statements? I would like to be able to have the following table Everytime I insert a new record the AutoIncrementKey field will increase by one but I want the PriKey to be in the order of the datetime as shown below. INSERT TABLE DateTime = '1/1/2001 12:00:00'...more >>

Hi all, I have a problem converting datetime to integer (and than back to datetime). Depending whether the time is AM or PM, same date is converted to two different integer representations, which holds as true on reversal back to datetime. AM Example: declare @DI integer; declare @DD da...more >>

We have done a complex software with VB using SQL Server through ADO. Now our customer is planning to switch from SQL Server to Unix/Aix Oracle 8 DB. I'm a newbie both in Oracle and Unix and wonder how this will affect our software. Would changing the connection string be enough (I quess not)...more >>

What is the best online documentation either on the Web or installed with SQL Server that I could use to get started? I want to jump in and start creating tables, etc for applications. Also, what is the most recommended text for converting my DBA experience from other database to SQL Server...more >>

thought my question was misinterpreted. actual table can be smaller or larger. but, within a procedure need to turn a created #temptable similar to(examid is in asc order): (id, p/f, examid, examname) 1 f xxxx xxxxx 1 f xxxx xxxxx 1 f xxxx xxxxx 2 p 3 ...more >>

Hi all, I am having real problems allowing a client to connect to sql 2000 through enterprise manager.. I have tried all sorts but to no avail: From our local network all works fine.. Using the adsl line.. Even if i try and connect to the external IP and use login details..(As opposed to co...more >>

I have a utility I wrote which can backup/restore my database as well as schedule backups. This has always worked pretty well except when I try to backup to a USB Flash Disk. When I use one of these the backup fails. If I backup to the C:/ drive then copy it over to the disk it works fine but...more >>

I am looking for an API to flush all data in memory held by SQL Server to disk. Also, is there a tool for SQL Server like eseutil for Exchange that lets you correct a SQL database?...more >>

Dear techs, Hmm...I have a little problem using DTS. I made a simple DTS package where it extracts data from my database using a Stored Procedure and then exports it into a list. But everytime I export, it overwrites the file ('caused by statically entering the location of the file in En...more >>

Hello Newsgroup, I have written a very short program to get information from a whole table out of a database. the problem is that other people couldn't work on this table during the process. It seems that my program locks the whole table. I used the DataReader from the .NET Framework. Can you...more >>

Hello Where could I find a good SQL tutorial on internet ? I try to do something like this : Select *, DISTINCT Col1, Col2 from Table1, I mean : agregate all the rows of table 1 where the group (Col1, Col2) have the same values. of course, my SQL request doesn' work !! thank you ! Tommy ...more >>

What are some sound solutions to capture the query runtime via a script/proc? The info returned from Statistics time is hard to process. The goal is to run a statement(s) and capture and track the execution time to check on the availability of SQL Server. The data can be analyzed to determin...more >>

Hi all, i've just set up an mysql server on my computer and now i want to connect to a mysql database with an ASP page but i can't find the correct connection string. I always get an error that the datasource can not be found. ConnString = "Driver={MySQL};SERVER=mysqlhost;DATABASE=myDB;UID=us...more >>

Hi, I have a User-defined function "Concatenate_NoteTexts" which I use in a query (SQL Server 2000). On my local development machine it is called like this: SELECT dbo.Concatenate_NoteTexts(Introducers.IntroducerID) as NoteTexts FROM tblIntroducers I want to run the same code on ...more >>

Hi there Application : Access v2K/SQL 2K Jest : Using sproc to append records into SQL table Jest sproc : 1.Can have more than 1 record - so using ';' to separate each line from each other. 2.Example of data 'HARLEY.I',03004,'A000-AA00',2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','G...more >>

Hi I have a DTS package that pulls data from oracle and inserts it into SQL. During this transfer, any data that has trailing spaces, loses those spaces in SQL. i.e. it's been trimmed. Any way to set DTS not to trim the data ? Thanks Steve ...more >>

Hello, I've the following problem. I've to read out the data of a custom application. I think (I'm not sure) this application is using a Paradox DB to store it's information. I don't know it exactly cause I'm not familar with paradox at all, but I've found files like *.db, *.mb, *.px, *.xg*, ...more >>

hi, Is there an equivalent function in SQL server as to MYsql's mysql_error() for easy findings of SQL errors? Couldn't find it in the manual.... If no : What if the recommended action to take? My SQL statement DIE's when I use it on my php page, but works fine in the MS SQL query analyzer...more >>

#temptable (studentID,p/f,examid,examname): 1 p 2 f 100 bio 2 f 101 mTH 2 f 102 CHM 2 f 103 spts 2 f 104 LIT 2 f 107 geo 2 f 109 phys 2 f 110 eng 2 f 111 hist 3 p 4 f 100 bio 4 f 102 chm 4 f 109 phys 4 f ...more >>

Hello I would like to agregate several rows : I would like > C1 C2 C3 > ---------- ---- ---- > VINET 5 10 > VINET 5 11 > VINET 5 12 > TOMSP 5 13 > TOMSP 5 13 > TOMSP 6 18 > TOMSP 7 19 > TOMSP 6 19 > TOMSP 7...more >>

Hi, I have a query that takes 1 min 50 secs to execute in Query analyser (SQL Server 2000). When i execute this query from vb using ADO i get the 'Timeout Expired' error. I have tried setting the Connection.Timeout property to the ADO connection object but it doesn't work. Can anybody tell me...more >>

Hi All: I am getting an error when trying to open a recordset in SQL Server 2000. The error states that the transaction log is full. Is there any way I can clear out or empty the transaction log, or get rid of it alltogether as it is not really needed? Any help would be appreciated. Tha...more >>

i have a master table with around 15 columns and i am trying to find the appropriate primary keys and indexes for the table. To make the records unique, i need to include two datetime columns ( start and end dates ) and two integer columns ( attributes of the record ) to make up a composite p...more >>

Hi All: I am getting an error when trying to open a recordset in SQL Server 2000. The error states that the transaction log is full. Is there any way I can clear out or empty the transaction log, or get rid of it alltogether as it is not really needed? Any help would be appreciated. Tha...more >>

Hello I would like to obtain all the fields of a table where the two fields C1 and C2 from this table are distinct, what is the request ? I can't find it ... sorry, and thank you ...more >>

This one is stumping me.. please help. I put together a DB with lots of tables, sprocs, etc.. Now I want to wrap it up into one nice little .SQL which will generate everything for a user. To that end, I went into Enterprise Manager, and selected "Generate SQL script". All is well until so...more >>

Hi all, I have a problem. I am running a Transform Data Task between 2 sqlserver tables on the same database. Most of the transformations are straight copy columns. However there are a few that require an entry corresponding to a datediff between 2 dates in the source table. I can't get thi...more >>

Ok, I thought this one would be easy. I have a stored proc: master.dbo.restore_database_foo This is on database server B. Database server A backs up database foo on a daily basis as a scheduled task. What I wanted to do was, at the end of the scheduled task is then call the stored pr...more >>

I'm trying to backup several databases to UNC folders. I'm able to do it manually for each database, but when I set up the maintenance plan it fails because SQL tries to use the SQL administrator account to push the data. The million dollar question is how does one change which account is run...more >>

How would I copy an entire database to another (using a different name, of course) on the same server? I am new with working with databases, so any help would be appreciated. Thanks....more >>

We're trying to automate inserting system DSNs using a .reg file. The file works up to a point, but so far I have not found a way to insert the proper password. The authentication method is SQL server, not NT. The relevant section of the file looks like [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.I...more >>

Hi There is an application that runs on sql server. The application selects/updates some few tables frequently Once there is even a select on this table .It blocks other users sometimes for very long. Is there anything that can be done to reduce this? The table has 18000 rows and does not ...more >>

is it possible to do: (A) declare @numberofitems Int @numberofitems = select max(itemorder) from store, department, etc. and pass the @numberofitems to a #tempStore table, like: (B) (store, department, @numberofitems,...) I got itemorder but not the number of items in each department A...more >>

Hi, I'm a newbie in this SQL Server development. I have a database with 7 tables that are linked to each other and have created clustered index for the primary keys and non-clustered index for the secondary keys. All my indexes are on the same filegroup, what is the effect of this becuase t...more >>

Hi there I am hoping for some direction in posting this. We have a web appication that is running on ASP>NET framework with IIS that will access a 2 gig sql data base. The issue here that we can not get the application to connect with SQL at all. Here are the errors we are getting Exception D...more >>

Hi All ! Is it possible to get rid of these dash symbols which are underlining the column name when recordset is returned after query execution ? For example, using isql.exe: SELECT 'blah' go produces the following results: ---- blah What I want to achieve is just blah I kn...more >>

How would I write a trigger that updates the values of a Description column to upper case for even IDs and to lower case for odd IDs? I need this trigger to fire for INSERT and UPDATE events....more >>

Hi, We have an ODBC/Visual-C++-6 -bases application (currently working with both MS-Access and Postgres) and want it to also cooperate with MS-SQL-server and MSDE. Since we don't want the user to wade through the ugly ODBC-setup, we have our own dialogs (e.g. the usual file dialog for MS-Acces...more >>

I've been getting 3-4 notifications per week from Norton's Personal Firewall to the effect that somebody tried to get into my system to insert this thing....and I don't even have cable or DSL - just dialup. Anybody else getting this? Could Norton be a little oversensitive in this area? ...more >>

i didnt think my sql qeury was that complicated that it would crash my web app. all im trying to do is filter data between two tables. heres my query <cfquery name="GetResults" datasource="#datasource#"> SELECT * FROM Content, Content_Sites WHERE C...more >>

Hello, I have query joining several tables, the last table is joined with LEFT JOIN. The last table has more then million rows and execution plan shows table scan on it. I have indexed columns on which the join is made. If I replace LEFT JOIN with INNER JOIN, index is used and execution takes...more >>

A lot of our data is in a database originally provided to us by a vendor. We can add tables to the database (although we prefix them with our own company name), and stored procedures and our own triggers, as well. However, we have been hesitant to change the vendor's tables (despite serious fl...more >>

#temptable (order, name, activity and count): 1 mark1 null 0 1 mark1 movie 5 1 mark1 game 4 1 mark1 game 3 2 mark2 null 0 2 mark2 sports 6 2 mark2 game 4 2 mark2 game 3 2 mark2 candy 2 3 mark3 null 0 3 mark3 movie 2 3 mark3 game 4 ...more >>

Do you know what RAID type is housing the transaction log ? ...more >>

Which of the following does NOT cause the execution plan of a query to be recompiled ? - new column is added to a table accessed by a query OR - index used by a query has been dropped from the database OR - query perfoms a join to return data from multiple tables OR - significant amount of da...more >>

Hi, I have a problem regarding the perfomance of a stp in combination with character translation. The following happens. We have an automated installation script (nt command file) for creating all stored procedures in a database. At first we used osql in this script, but with osql the i...more >>

Just wondering if you could help me on this one. I'm not sure if my transaction logs are behaving oddly or what. I've successfuly managed to shrink my transaction logs from 7GB down to 1MB and now I find it strange that the log file doesn't seem to increase its size. The timestamp of the logfile...more >>