Hi - I am somewhat of a novice when it comes to SQL and I was wondering if it possible to use an IF statement in a query. My query looks something like this, Select Contract.Amount, Contract.Type, Contract.Market From Contract Result Set 1200 Bilat GER 500 Cleared GER 250 Bilat NED...more >>

A weekly process runs that renames a database to "Old_database", then renames "New_Database" to "Database". Pretty straightforward. It then Drops the "Old_Database". Any way to recover that database, since the new one has been determined to be crap? According to BOL: A dropped database ca...more >>

Hi, I have a stored Procedure as in the code snippet below: CREATE Procedure [dbo].[sp_Insert_Main] @Emp_Ref_No int @Emp_Ref_Name varchar(50) @Emp_Ref_Address varchar(150) AS insert into T_PGM_MASTER values (@Emp_Ref_No,@Emp_Ref_Name ...more >>

Hi, I have a stored Procedure as in the code snippet below: CREATE Procedure [dbo].[sp_Insert_Main] @Emp_Ref_No int @Emp_Ref_Name varchar(50) @Emp_Ref_Address varchar(150) AS insert into T_PGM_MASTER values (@Emp_Ref_No,@Emp_Ref_Name ...more >>

Instead of using the standard Identity of a number, i want to generate an id like this "Q1","Q2"...."Q99292" how would I do this in SQL server? is there any example code out there? I have never worked with triggers or TSQL, just basic tables. so all the help that can be given would be appriciated...more >>

I'm debugging my database, and I need to double check the column names and data types of all the columns of a table. The table name is Invoices. Note that I'm working through ASP files from a browser across the web, not at the console. So far, I've tried: oConn = Server.CreateObject(...more >>

I have a stored proc that runs in a database. I need a way to get the name of the database in which the stored proc is running. How can I accomplish this? TIA ...more >>

Simple questsion. I have detached a SQL Server 7.0 database. Then I have successfully attached that database to another machine where SQL Server 2000 is installed. What I am supposed to do to make this newly attached database (which was 7.0 database) to work OK in SQL Server 2000 Serve...more >>

I have a full backup schedule runs at 11PM. It will run for 45 minutes. Also I have transaction log runs at every 10 minutes. Let us say my SQL Server crashes at 11.55PM. Will I have transaction logs for 11.00PM, 11.10PM, 11.20PM, 11.30PM,11.40 and 11.50PM ?. Let us say I have Fullbakup at...more >>

Hello In my ASP page I am trying to ascertain whether a randomly generated ID = starts with certain characters that are not allowed or has been used = before. When the SQL statement for latter is executed I get: Microsoft OLE DB Provider for SQL Server error '80040e31' Timeout expi...more >>

I need to see if a select statement actually returns results, but I would like to keep from running the query twice. So, my question is this: is there a variable or function that returns the number of results returned by the last query? I am doing this in a stored procedure. Example: .... ...more >>

is it possible to change the database referenece inside a stored procedure? for example, I would like that before running a specific "select" statement, the stored procedure should change the target database. - I tried the "USE" command, but it seems that it does not work inside stored proc...more >>

Hi folks, I have a linkserver for text files and I want to create a table base on a file (test#txt) but I want to include an identity column. I can included a column with a value there but not an identity column. How can I perform this? Can I do it? Tks in advance JFB SELECT ''444'' as rect...more >>

How can I set the default value to the value of another column? Let's say there is a table CREATE TABLE "dbo"."Clients" ( "ClientID" int PRIMARY KEY NOT NULL, "Name" nvarchar (100) NOT NULL, ) GO I now want to add a new column, ReportName, that by default has the same val...more >>

a) Is it possible to use a view, and within that view to call a stored procedure that does calculations? b) Or is it possible to create a sql statement that has in the select or something a stored procedure that does calculations? Thanks! ...more >>

I am converting a FoxPro application to ASP.net in C#. The original application makes extensive use of IIF() functions in SQL Select statements and I was under the impression that SQL Server could read these statements, however I'm getting an error message which states: "'IIf' is not a rec...more >>

How can i create a stored procedure that runs a sql schedule or update the date and time for a schedule job. ...more >>

I have a table called Contact with a field called contname. Contact ID Contname 0002 Jim Smith How can i parse the contact table and insert this record into another table called Leascontacts Contactid FirstName Lastname 00002 Jim Smith Thanks ...more >>

Is there a way to parse a field which is comma seperated into its elemental parts for insertion to a table. All I want to do is request a field by number. For instance .... exec ParsingFunction('City, County, State, ZipCode'), 2) will return 'County' and ParsingFunction('City, County, Sta...more >>

When I call the OpenSchema method of an ADO connection object on a SQLServer database and get a list of the stored procedures, all the stored procedures have ";1" tacked on to the end. When I actually execute a stored procedure I just use the name, and I would like to verify that the proc...more >>

The following extended stored procedure runs fine on a WIN2K computer but fails on a WINNT 4.0 computer (This extended stored procedure runs a DTS package). EXEC master..xp_cmdshell 'dtsrun /Stestserver /Ntestdtspackage / E' This is the error message that I get when I run it on the WI...more >>

Tried to post this once, but it doesn't seem to have appeared so I'm trying again. Apologies if the first one does show up and you've already responded. I'm pretty sure I've seen this on here before but can't remember how to do it. I want to generate a sequence of number starting with a part...more >>

Hi guys! I have the following query to extract a TV-schedule. The problem is that would like to know the parent category name (from SportCategories.category_name below) for the program, not the individual category name. How is this done? Thanks for any kind of help! Regards, Jonah Olsson ...more >>

Hi, I need help with an insert statement on this query. I will also need to do an update on this same query. I will be loading only changed or new data records into a temporary table then using that temporary table to insert or update the table with this query. I hope this is enough information. ...more >>

I'm pretty sure I've seen this on here before but can't remember how to do it. I want to generate a sequence of number starting with a particular number and ending with a particular number, and the number of records equal to the difference between the starting and end numbers plus one, i.e. a ...more >>

I need to compare two databases that are suppose to have the same schema, but different data i.e. qa and production. I then need to report the differences. I know there are 3rd party tools to do this, but wondered if there was some homegrown way, since my company won't spend the money. ...more >>

I have an sp that will be part of a DTS Package. It accepts two datetime parameters and is an INSERT INTO... SELECT.... The sql in the query analyzer runs in 4 seconds. The sp, when ran in the query analyzer, takes 20 seconds. What's up? Suggestions would be welcomed. ...more >>

How can I get the rowcount after an insert statement and also handle any errors created by the insert statement ? This is what I want to do, but it does not work as the 'set' statement clears the @@rowcount and vice versa. --------------------------------------------------------- insert i...more >>

I would be grateful for help with setting up a linked server. Scenario: Both servers running SQL 2000 Local server ('LocalServer') Server running on our ISP ('WebServer') We can already access the 'WebServer' and run queries etc if we log into it using Enterprise Manager or Query Analyzer....more >>

Hi All, I have this table - Dummy - with these values : Date MW1 Price1 ----------- ------------ --------------------- 20030926 NULL NULL 20030927 40.0000 NULL 20030928 80.0000 NULL 20030929 40.0000 NULL 20030930 40.0...more >>

Currently I need to design a database in SQL Server 2000, but later on my company will be porting the database from SQL server to Oracle. What are the things I should keep in mind when designing the database. I know if I create stored procedures, that will be needed to change. Let me know you in...more >>

I have tried this and it does not work. I think its trying to match it without taking into account the mask characters in the returned field. "Offeral" <jspruiell@homeandgardenparty.com> wrote in message news:18ac01c38695$2ce2b550$a001280a@phx.gbl... > Use LIKE '%Mask%' > >-----Original Messa...more >>

iam trying to check if a all or any field values for a record have changed during an update operation . If so want to copy the previous values in the record to a different table (like a history table of changes) to table T2. Iam using trigger. my question is when i update, the trigger return...more >>

I have seen a case where my query results seem to have simply joined the wrong records together. It only happened once (that I know of) and I cannot duplicate it by running the same query on a backup copy of the database. The join that came out wrong is done with a simple integer ID on b...more >>

Hi, Can anyone tell me how to execute a function which resides in linked server database. I used Exec sp_addlinkedserver 'Test' - to link the server EXEC sp_addlinkedsrvlogin 'Test' - to provide access to all local logins to the linked server. I want to update a table in local serv...more >>

Why does the following SP require something be passed in to work? create procedure [onevar] @comeback int output as select @comeback= count(q) from qna print @comeback return exec [onevar] Procedure 'onevar' expects parameter '@comeback', which was not supplied. This same SP r...more >>

Hi, I have troubles getting a distributed transaction between 2 Win2003 servers work. The Code i am trying to execute is the following: SET XACT_ABORT ON BEGIN DISTRIBUTED TRANSACTION SELECT * FROM MYLINK.pubs.dbo.authors COMMIT TRANSACTION The error i get is: Server: Msg 7391, Lev...more >>

Hopefully this is a simple question to answer Is it possible to build an index which spans 2 tables? or should I create a view of these tables and create the index on the view ? Thanks, Bri ...more >>

I apologize if this got double posted and for the terrible formatting. Is anyone else having problems posting messages over the last few days? I have a stored procedure that is going to have a lot of parameters added to it in the near future. I thought the easiest way to do this would be to d...more >>

Hi there, I'd like to know if is it possible to rollback some amount of transactions that has been done into a database. Sort of.. hummm.. I have a software. Thru this I can insert itens in my Itens Table. I've inserted 900 itens today, but I realizaed I've inputted wrong data in the ...more >>

Hello, If somebody help me I really appreciate. I create this trigger: CREATE TRIGGER Verifica_Inmobiliaria ON [dbo]. [AvisoInmobiliaria] FOR UPDATE AS DECLARE @sCmd VARCHAR(64)=20 DECLARE @sSQL VARCHAR(128) =09 SET @sCmd =3D 'DTSRun /S=20 salmarjo /N "Genera_Archivo_Clasificado...more >>

Try this in Query Analyzer. Can somebody explain the results or is this a bug? ------------------------------------- Try this in Query Analyzer. Is this a bug in the T- SQL "Right" function? set nocount on declare @num int set @num = 1000 print 'Cast as char' select right('0000' +...more >>

Hello, Is it possible to have my own primary key instead of IDENTITY? As i know there are no sequences in mssqlserver, except build in table identity field. This problem can be solved in some ways, but there are another problem also - there aren't any possibility (at least I did not found) ...more >>

Hello, Is it possible to have my own primary key instead of IDENTITY? As i know there are no sequences in mssqlserver, except build in table identity field. This problem can be solved in some ways, but there are another problem also - there aren't any possibility (at least I did not found) ...more >>

Hi The below procedure uses cursor, can it be done without a cursor. CREATE PROCEDURE test @dtTravelDay datetime, @intBusDirectRouteID AS DECLARE @dtCurrentTravelDay datetime, @intFromBusStopCode int, @strFromBusStopName varchar(20), @intEndBusStopCode int, @strEndBusSto...more >>

Hello, I have retrieved some data into a sql server table which contains some ntext fields. On one row, one particular ntext field contains some trailing spaces. When users connect to this table with ODBC from Microsoft Access then all get an error on this one row. I updated that row b...more >>

Hi I have just renamed my databases collation name but now have to rename collation all exsisting 200 tables within it does any one know of such a command to rename all the tables in one go or will I have to do them all individually :(...more >>

I have a query that I would like to be executed someway through a shortcut from the desktop of certain users. For instance they can double click an icon on the desktop to execute a query that would alter a table for them in their database. Is this possible??...more >>

Hi, I need help on understanding how INNER JOINs work, to be able to break the below puppy down. What I don't understand is how inner joins work when they are one right after another and "ON" is following later on. Thank you in advance! mv select al.ActivityDate, l.nL...more >>

Is it possible to view the error log using T-SQL? Thanks ...more >>

How many actual/real characters can fit into a varchar (8000) field. ASCII chars from 0-127 for example. Thanks...more >>

question about BCP process. The problem is when I transfer the data in date time type field the date '00000000' is not transferable, which is understandable, however it also kicking out some data with appropriate date. Any idea why it occur?...more >>

Dear All, I was wondering if someone could help me with a bit of problem. We are writing an application to take over a part of a business that currently uses a legacy system into a SQL 2000 database. One of the table the "Person" table has over 800000 records, of which a third are d...more >>

Does anyone know of a command that would display all the tables and there size from one database thankyou for any help Shau...more >>

Hi... I need to sort (ORDER BY) the data in a table alphabetically. Now it may so happen that the data could contain HTML tags embedded and the special first char "<" of the HTML opening tag is used for the ORDER BY clause and hence in the following sample of data I have, I always have <B>Tig...more >>

I read all the articles i have not miss single one Iam using SQLDMO,vb.net, dot net setup project how can i register sqldmo file via code without doing it manaually(REGSVR32) eg C:\Regsvr32 Sqldmo.dll when client installing my product he doesn't know about registering stuff.if i install...more >>

Hi I have been given over 300 text files which have scripts for building the database to builing table and stored procs...rather then running each one individually does any one know of a tool to amalgamate these into one file and run it from query analyser thankyou for any help, Shau...more >>

Hello All, I want to manipulate a string in a stored procedure which is more than 8000 characters in lengh. So it is understood that I cannot use varchar(8000). One way is to manipulate it using multiple varchar(8000) variables. But I don't want to use that method. Is there a way to m...more >>

Hi, I'm creating an auto-startup stored procedure in which I want to grant a certain user access to the tempdb. How can i do this? So far I've tried with the sp_grantdbaccess procedure, but this one only grants access to the database you're in. Since the sp I'm creating needs to be aut...more >>