I am looking to convert a mySQL database to SQL Server 2000 or 2005. I am interested in converting the structure, data, constraints, keys, etc. I have some experience with SQL server but none with mySQL. I am a C# programmer not a DBA. I am looking for some guidance on the best way t...more >>

Hi, I'm using Sql Server 2000 currently. One of the problems in my = organization is we have one SQL Database that is huge, 300+ tables, 200+ = sprocs. Apart from breaking this DB up in many smaller databases, is = there some way, current or a future version of sql server, to better = organize...more >>

Hi, I want to write a trigger INSTEAD OF on a table. In this trigger I must = regconize, if the trigger is fired by INSERT or UPDATE action, in order = to do special thing for each case. But I can not to write 2 trigges, one for INSERT, one for UPDATE, = because I may only one INSTEAD OF trig...more >>

I have a trigger that updates summary information for insert and delete. It seems that delete works if I do delete of the row dirrectly on the table DELETE FROM Entry WHERE EntryID=... This Entry Table is connected to CustomerTransaction table with relations that updates and deletes Entry ta...more >>

Are there any limitations to the NOT EXISTS clause within a SQL statement? I have a large query that is querying a table that holds sequential data for customers. It gives me a complete transaction history of any account based on a datetime stamp. The query has 5 AND NOT EXISTS clauses in i...more >>

Hi! Is there any way to estimate time of query executing? There are Display Estimated Execution Plan with some costs, but how can i estimate required time in seconds? Thanks! Best regards, Konstantin Knyazev ...more >>

I have three tables: Contact, Staff, Codes. My SQL statement is as follows: SELECT tm5user.contact.full_name, tm5user.contact.phone1, tm5user.contact.firm, tm5user.contact.city, tm5user.staff.[last], tm5user.staff.[first], tm5user.codes.[desc] FROM tm...more >>

Hello, Here are the things that I need to do for my client application that connects to MSSQL Database. 1) I need to figure out how many connections are made to the database. 2) I need to figure out how many cursors are connected to a particular database. How can I do these? I found a w...more >>

Hi, is it possible to retrieve the version of a SQL Server by using SQL? ...more >>

Hi, I've set up a partitioned 'updateable' partitioned view. The view unions identical tables which contain a horizontal slice of the data. the partioned column is a combined key. Constraints are in place on all tables to ensure the correct data goes in to the right tables. I've successfull...more >>

How to insert an integer value into to tabels? The same value must be in both tables. The problem is that integer is generated automaticly, so I don't know the specific value when I do the insert. Hrcko ...more >>

I am trying to get something lik this working: select ...... where .... and user_id = case @userid when <> 0 then @userid end I need to conditionally use user_id = @userid when @userid <> 0 Thanks ...more >>

Is there a quick query that would identify if any characters being used in an ntext column actually require the ntext, rather than just text? Perhaps a query looking for any character where the ascii equivalent is out side the "text" range?? thanks in advance. Mark ...more >>

Hi, Please share your comment or advice on this. This is urgent for me. I would like to find if searchId exists in the table2, but the tables has many rows, would you know an alternative that would do it faster? I'm going to make SearchId an index thinking that it would help but not sure if...more >>

Hi, How can i insert from a select statement where in the select part has 2 identity columns. When i insert it in a table which i create on the fly, the insert fails. ...more >>

When using RI to do cascade deletes, why is there a restriction based on multiple cascade paths ? Yes I know how to get round the problem, but our application works on the other major DBs so we're not going to change it just to work on one, so we're forced to use triggers just one this one ...more >>

Hi, I'm trying to query some data from two database (d1.tbl1 and d2.tbl2). How can I do this? I'm new to SQL. Thanks in advance, Sarah ...more >>

Greetings, My stored proc is below. It works fine, but takes around 20 seconds to complete. Could anyone please eyeball it and let me know if you have any suggestions to make it more efficient? At first it returned around 3000 records, so I stuck in the "top 10" modifier, but that didn't show a...more >>

I have 2 tables with data as. Table A FName LName Value John Brown Chris Smith P DeSouza P DeSilva Table B FullName John Brown Chris Smith P De Souza P De Silva I am trying to write a query which will update Value column in Ta...more >>

Pardon my newbieness. If I had this value in the field called Field1: Public, Johnny I want to return only the last name. I would normally do this in VBA: LEFT(Field1,FIND(",",Field1,1)-1) I'm trying to do this in T-SQL. I've discovered the SUBSTRING function. So I can do this: ...more >>

Dear all, I've got a stored procedure and inside it, a cursor is running after a couple of no massive update . In a short, it doesn't take more than 1 minute but when I launch this sp from VB front-end sometimes I obtain timeout error. It's amazing I am talking about 300 or 400 rows popula...more >>

Looking for some suggestions on writing a Stored Procedure that have several Parameters, some with 2 possible answers. Example of Parameters: One possible answer 1. @ActCompanyid= a Company id, @BegDate, @Enddate Two possible Answers 2. @Showid = # / ALL # = specific show / All = ...more >>

x-posted to: alt.comp.lang.php, alt.php.sql, microsoft.public.sqlserver.programming ********************************************************************* I am trying to associate an event with a venue through a single form that asks for venue information, contact information and event inform...more >>

I was wondering if anyone had anything in their bag of tricks that would detect any foreign key contraints on a table and break them. I am constantly dropping/adding tables as part of a MSSQL 2000 utlity function and I keep hitting an error if I drop a parent table that has FKs. Thanks. ...more >>

Hi, Following on from a recent post regarding Identity fields with David Portas, I was discussing a few issues with a collegue and an interesting issue was raised. Namely, creating an audit trail in which every row is assigned a unique, gapless sequential reference. For example, our account...more >>

I just used Enterprise Manager to create a script of my database. My goal is to take this script and run it on another server to create a copy of the database. When I ran the script to re-create the database, I got numerous errors because Enterprise Manager created the script in such a way tha...more >>

I have a CreatedDate column of type datetime. I am trying to query for CreatedDate = somedate, but not results are returned. I understand this is because the time is also included in the data will never equal a specific date. BOL recommends doing a string comparison using LIKE; however, it do...more >>

I'm currently using CFMX with an SQL Server 2000. However, I'm moving to more cost efficient solutions such as ASP.NET and some other database, which I haven't determined yet. The database will probably be MSDE or MySQL. I don't do many transactions, so either of those two choices should be...more >>

Hello, I've attached Access MDB as a linked server to SQL 2000 server. Now I want to run distributed queries. I set 'collation compatible' option, so when I reference an Access MDB table in WHERE clause, the parameter is passed to Access data provider. But I can't do the same for Datetime colum...more >>

If I have two tables: create table test1 ( TestID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, description varchar(50) NOT NULL, defaultTest2ID INT NOT NULL --default test2 row ) create table test2 ( Test2ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, description varchar(50) NOT NULL, TestID I...more >>

In a SP that joins 4 tables, the system needs to select a corresponding date from one table (lines.line_start_date or lines.line_end_date) if in the other table either date field (emp_line_activity.actual_start_date or emp_line_activity.actual_end_date) are null. However, the SP joins rows tha...more >>

Hi, If I have a column that allows NULLs and the majority of the queries that run against the table look for rows when that column is NULL. Would an index increase the performance of the query? Thanks, --AML-- ...more >>

Hello, I've been searching the groups for a way to accomplish this, but I've come up short. What I ultimately need is a way to count the number of decimal places in a floating point number (ie 37.88955 ==> 5, 0.02 ==> 2, etc). What I'm currently working on is how to convert a float to deci...more >>

This worked in Access, but I'm not sure how to convert it for use in SQL 2000: Concatenate("SELECT Abbreviation FROM TblChargesDetailed WHERE CaseNmbrID =" & [CaseNmbrID]) Basically, the Concatenate code takes a list of items in another table and strings them into a single field separat...more >>

I am trying to use getdate() function inside of my UDF function. I am getting an error message about invalid use of getdate() function. Is there a way to use functions in UDF functions? ...more >>

I'm using a view to prepare the data I want to export with DTS into a comma delimited file. The SQL it creates is: SELECT TOP 100 PERCENT LicenseNo, PropertyNo, AmountPaid, DatePaid, PrimaryFee, TransferFee, PenaltyFee FROM dbo.Fees WHERE (AmountPaid > 0) ORDER BY LicenseNo ...more >>

Dear All, Recently our database was 'hacked at' by a remote service. What I would like to do is capture the machine name (I can get that from the Process ID) of the hacker. In this case the hack was unsuccessful. I know there are ways of doing this on Server 2003, but I would like a way o...more >>

I got no solutions last time, so thought I would try and make the question more understandable (The DDL is at the bottom): (1) I have a table that uses some hierarchical SQL Server roles. (2) There are always rows in the table for the top-most role ("Complete"). (3) Sometimes, I want to overr...more >>

I'm a little confused with a JOIN that should be working but isn't. If I run the same SQL statement in MS Access (same data) I get the join results I expect. If I run the SQL statement in Enterprise Manager I get a different result (again same data). I thought both are supposed to be ANSI...more >>

Hello, I am hoping that someone can help me out with this. I am trying to debug a product that I support, the application creates its own SQL when executing reports. The report I am trying to execute requires a field that is a BLOB, see the SQL below, the result is not what I was expect...more >>

Is there a way in SQL to take one record from a table and duplicate it a certain number of times (Basically create a view that has the same record repeated) For instance, if a record is SMITH, JOHN, 1234 and I specify I need 3 duplicates then teh resulting view would be: SMITH, JOHN, 123...more >>

I am running an update statemnt against TableA Update TableA Set Column1='AAA' where column1='BBB' The command executes, but withing 5 seconds reverts back to the old value (BBB). I tried copying the table and renaming it and updating that one, same thing happens, does someonw know why ...more >>

I have taken the recommendations that I was given with my first posting of this procdure code. I greatly appreciate the help. I have written my 2nd attempt and would like to get feedback on this version for anything else that can optimize the queries. Here is the code: -- Procedure ...more >>

Hi, I want to substract an end time from a begin time in a query and have the folowing. Begin Time = 8:00:00 AM Time In = Monday, 28-03-2005 08:18:16 AM Time Out = Monday, 28-03-2005 06:20:34 PM to substract Time Out - Time In is very easy, but to substract Time Out - Begin Time is my...more >>

Dear all, I am trying to solve this but I can't work out. Set @DESV_INSERTED = (SELECT stdev(desv) FROM #DESVIATIONTYPICAL WHERE desviation_id = @desviation_id AND desv > 0) SET @AVERAGE = (SELECT AVG(DESV) FROM #DESVIATIONTYPICAL WHERE desviation_id = @desviation_id AND desv > 0...more >>

When the table below is created -the data selected is different for values > 10. Does some one know why float behaves this way ? - I am stumped create table tempdb.dbo.TestValue (ColId int, TheValue Float) Insert into TestValue Values (1, 10.25) Insert into TestValue Values (2, 10.99) In...more >>

Hi How to assign a variable value from if exist ? like declare @i int if exits( select @i = ID from table1 where ID = 100) -- do sth but I always get an error Thanks a lot for helping...more >>

Hi I'm new to SQL and I have the following problem. I've 3 tables: table1 (with columns a1, b1, c1), table2 (a2, b2, c2), table3 (a3, b3, c3). I have to build joined table with columns like this: col1 (a1 or a2 or a3), col2 (b1 or b2 or b3), col3 (c1), col4(c2), col5(c3). a1=a2=a3, b1=b2=...more >>

Hello all, Follows a simplified DDL of the table where i'm working on: CREATE TABLE Test ( TestID bigint NOT NULL , SubTestID bigint NOT NULL , Name varchar (50), Master bigint NOT NULL , CONSTRAINT PK_Test PRIMARY KEY CLUSTERED ( TestID, SubTestID ) ) What I'...more >>

Hello, I'm having a problem migrating a particular stored Procedure wich runs nested and interdependent Fetching within a cycle. There is a main cursor and from that, a var is retrieved for use as a parameter for other cursors. The most important is that the all Cursor's queryes run suc...more >>

Hello, I need some help with string functions. I have these statements: declare @text varchar(30), @quantity dec(32,12), @date datetime select @text = 'qwerty', @quantity = -123.123456, @date = getdate() select @text + ';' + convert(varchar, @quantity) + ';' + convert(char(10), @date, ...more >>

We have a table with the following schema StudentID Elective Marks 1 English 80 1 Physics 72 1 Maths 98 2 Spanish 86 2 Biology 87 3 Social 81 3 ...more >>

Hi - from within an SP (which is run automatically), how would I determine range for 'last month' - eg. this is April, so I would like my SP to be able to calculate 1 March 2005 to 31 March 2005??? My problem is for this: I have a .net web application which keeps track of time for consultants....more >>

Given a table IID bigint (Identity) GID guid (PK) ParentID guid Value nvarchar(400) with indexes on ParentID, Value Value, ParentID Now, if I put the clustered index on IID, I get great INSERT performance because new records are added to the end of the table al...more >>

Hi, is there any reason why it is not allowed to use formulas as Stored Procedure parameters? e.g.: EXEC myProc(@myVar + ' ' + @myVar2) I could make use of this feature over and over if SQL Server 2k would allow me to. Axel Dahmen ...more >>

Hello, I want to know whether I could get the Raiserror's message id in the front end's application, (coded in Visual Basic or Delphi). Thanks in advance Frank ...more >>

Using syscolumns or an equivalent, how can you find the default value, if any, of a sproc parameter? For example given this sproc... CREATE PROCEDURE spTest (@X CHAR(15), @Y DATETIME = '1/22/1975') .... How would you find that @X does not have a default, and that @Y does? Thanks in adva...more >>

Hi All, I want to BULK INSERT from a text file to SQL but I only want to insert the lines that begin with 'valid'. Is it possible? Is there a link where can i find help with this problem? Thanks! joel ...more >>

Hi, I have table with the following structure User Name, Name of Product, Date of Purchase I want to find out the number of times a particular product was purchased by anybody in the last 30, 60 and 90 days. Is there any function in SQL that will give this type of distribution? What ...more >>

...more >>

Hi, I am writing a trigger for Insert and Update, which raise an error, when column Field2 contains incorrect value(empty, or duplicate in table)... Now I edit a record, when I make the error to fire the trigger, my error message is not displayed, instead of it SQL Server shows me 2 diffenrent ...more >>

I have this error only when reading a certain file from SQL server, and not with all the other files, so it's very strange. I have a Visual FoxPro cursoradapter who gets populated with a record from a table in SQL server, and the CursorFill method of the CursorAdapter (which populates it usin...more >>

I've created a table with an Nvarchar field in SQL server. When I insert a chinese word using Query Analyser, I can use N'chinese word for updating. Howeve I can update the chinese word from web page. Can anyone help me? Thanks ...more >>