I'm trying to create a text file that I can send to rightfax for automatic delivery. Here is what I have so far. declare @startstring char(80), @endstring1 char(80), @endstring2 char(80), @endstring3 char(80), @endstring4 char(80),...more >>

I'm trying to write a VBScript to work with SQL-DMO. What this VBScript does is to restore database and its transaction log files. How can I specify the WITH NORECOVERY, WITH RECOVERY, or WITH STANDBY before running this script. Thanks. TC ...more >>

Hi Everyone, I'm still with this problem and now I have little bit of time to find the solution. After any user import data to a table I want to have that table as dbo owner. I dont want to give my users full administrator permisssions. I try to setup in each database access as db_owner, db_...more >>

Is it legal syntax to insert a preliminary WHERE clause inside a CASE WHEN statement when trying to alias field names according to a criteria. I want to make an aliased field called 'inactive' that looks for order types less than 3, but are attached to rows with a date stamp of yesterday: i...more >>

Folks, What is the recommended best practice for the following? Let’s say we are in the pubs database and we want to return some employee information via a stored proc with an optional parameter of last name. The issue at hand is how do you structure the stored proc to allow multiple last ...more >>

I am trying to find an efficient way to get the number of times that each row in a table is selected. I want to avoid using triggers. Any ideas? Thanks John jpd0861@msn.com ...more >>

The table listed below is sampled every minute with a [CounterDateTime] [char] (24) format. I would like to create a script that will average the MarketValue, FirstMarketValueA, and MarketCount fields in hourly format using the same counterIDs. Please help me create a script for a...more >>

Is there a way I can customize the error message coming back from a uniqueness contraint violation on insert? Right now I use a trigger to enforce uniqueness and throw an error with my own message - works fine but I would sort of prefer using the built-in constraint if possible. Can't have t...more >>

Greetings I'm running; - win2k - SQL Server 7.0 with SP4 - "SQL Server" ODBC driver version 2000.85.1117.00 We have an sql server 7.0 database that gets updated every day by a small app (also running on the database server machine). It uses the 'SQL Server' ODBC driver that was installed ...more >>

We have ported our product from Personal Oracle 8 to MSDE, and from NT to XP. The product is a set of ~15 Windows applications totalling about 450 KSLOC that interact via COM and manipulate the same database of about 150 tables. Some tables are accessed by only one app, but others are access...more >>

I am working with three tables to produce certain results: Consider the following SQL select: SELECT A.Col1, A.Col2, A.Col3, A.Col4, A.Col5, B.Col6, B.Col7, B.Col8 from Table1 A, Table2 B, (select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8 from TEMP_TABLE) C where A.Col_fk = B.Col_pk and...more >>

I have stored procedure which at the end returns a value: if @@error=0 and @quantity=0 return 1 else return 0 But I get an error: A RETURN statement with a return value cannot be used in this context Why? regards,S ...more >>

When using query analyzer i need to know what database the scripts is running on and store that name into a variable. is there a global database name variable? I know about @@servername but no @@databasename :( ...more >>

The following situation appears: two data bases DB1 and DB2 exist. The data base DB2 is fed with the DB1 information, that is to say, some transformations in the DB1 data take place to be protected in DB2. This task is easy to solve programming a DTS, nevertheless, the single transformation ...more >>

Hi, Is there any list available of style codes used in formatting dates e.g Convert(Varchar(11),GetDate(),106) I cant find these style codes or this method documented in BOL. I want to format a date like this: 09 August 2005. How? Thanks. hals_left ...more >>

I have a master table which I have been updating in multiple queries to create a new row. It has a primary key, call it WorkID. I can't make it autoincrement. And it is more complicated because.... WorkID can also appear in two other tables, as references to the master record, but I need to...more >>

Given two views, I need to join them based on the number of rows returned as follows: ViewA 1+ rows, ViewB 0 rows: ViewA LEFT JOIN ViewB ViewA 0 rows, ViewB 1+ rows: ViewA RIGHT JOIN ViewB ViewA 1+ rows, ViewB 1+ rows: ViewA INNER JOIN ViewB Easy enough to implement in a stored proc with a ...more >>

I have a database with a bunch of objects owned by a user named, "tims". I am logged into SQL Query Analyzer as the tims user, but I can't locate objects unless I use I qualify them with the owner name. For example, exec sp_select_corridors #doesn't work exec tims.sp_selelct_corridors #works...more >>

How can one change the database context within a stored procedure? In other words, something like this .... Declare @DB varchar(20) set @DB = (Select databasename from databases where activedatabase = 1) Use @DB Select * from Table1 This code doesn't work, but I'm sure there's a wa...more >>

Hi, i'm new to SQL2K. I see in my connect string (ASP) a IP-address. I can choose between a public or a internal IP. If i use the public IP, where can i find examples on how to query the database from a local pc in a Visual Basic 6 program ? Or is this not possible? Gérard. ...more >>

I'm importing large text files (12Gig). I know there are rows in the data that will not parse correctly. What I'm trying to figure out is how to set up the import and when there is an error parsing a row, that row either gets saved to another table or text file and then continues to import t...more >>

Hi. I have a situation where I have a table containg data, but several columns can be used to designate that the data is "hidden". E.g. if we delete a record, it isn't actually deleted but a datetime field called deleted_date is changed from null to the current date-time. Therefore I mad...more >>

I am trying hard to avoid writing a cursor to drive a report, but I can't see a set-based solution. Can some of you? The table ScanLog records the time, operator, and machine at which a certain operation was performed as entered by a barcode scanning application. CREATE TABLE [dbo].[ScanL...more >>

Is it possible to move an instance sql server from one drive to another, i.e. C: to D: without changing connection properties referenced in DTS packages, etc.? T.I.A....more >>

How come you can't do the 1st one but can do the second claim_ref_id dataType is int DECLARE @claim_ref_id int SELECT @claim_ref_id = distinct claim_ref_id FROM TableName(nolock) WHERE claim_ref_id not in (SELECT claim_ref_id FROM ViewName(nolock)) DECLARE @claim_ref_id int SELEC...more >>

I am trying to restore backup from database1 with move to database2. RESTORE DATABASE Database2 FROM DISK = '\\uncpath\database1.BAK' WITH MOVE '_Data' TO 'LocalPath\database2.MDF' , MOVE '_Log' TO 'LocalPath\database2.LDF' , REPLACE , DBO_ONLY , NORECOVERY I am getting the error: ...more >>

Hi. I have a text file with several fields. One of these sometimes contains newline characters. Any fields that do include newline characters are enclosed with double quotes. What Bulk Insert command line do I need to use to impot the file without it treating the enclosed newline characters...more >>

I have a stored procedure that excepts @orderby as a parameter and executes following sql statement: select * from titles order by case when @orderby = 'title' then title end asc Is there anyway I can add second parameter @direction and control...more >>

I'm in the process of converting a Access Database to MS SQL and have = made the memo fields in the Access database text fields in MS SQL. = However, when I use a SELECT statement in a ADO application, the text = fields data is not returned. The memo fields are all under 4000 = characters.=20 ...more >>

Hi, I'd like to do a bulk copy insert inside a dts package but the table i'm inserting to has more columns than the text file. I know you can change the format file to take care of this, but how do you insert actual values into those extra columns instead of just NULLS? The values for those ex...more >>

Hello All, I am looking for a SQL code indenter . The one I found http://sqlinform.com is good but somewhat limited. Can you guide me to some other links. (preferably freeware) With warm regards Jatinder Singh ...more >>

s...more >>

1. cannot hv tran in functions 2. cannot include SPs in select queries or in from clause whereas functions can be included. (exception using OPENQUERY for including SP in from clause) What other differences?...more >>

Ok, I have several tables that receive INSERTs. I have a certain 'view' that I've created that I created like the following: CREATE VIEW V1 WITH SCHEMABINDING AS SELECT t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id, t6.amount_type, SUM(CASE WHEN ...more >>

I have a specific SQL Server coding problem. I have an 8 character field that is an integer. eg: 16776976 In hex this is FFFF10 This represents the Red, Green and Blue hex values for a colour, in reverse order: ie. 10 is red, FF is green, FF is blue I need my SQL Server stored pro...more >>

I have been asked to produce an example of a cursor and then to point out why it isn't required to use on. I am having difficulty creating my example and I was wondering if someone could help me with the syntax to write one. Basically I want to select the FavouriteSport table (below) into a ...more >>

Select Columns from table where col1='value1' and col2='value2' and col3='value' In the above select statement, if col1='value1' is false then there is no need of checking for the other conditions Will SQL Server skip further condition if one condition become false or check for all regardless...more >>

I have large, slow updates. No one else is in the database when I'm loading recs. Would TABLOCKX or NOLOCK help speed performance? TIA! -- Greg C ...more >>

Dear all, I would like to know in what table the backups are stored. I mean, I see in the SQL Server log registry these lines: Database backed up: Database: DATA1, creation date(time): 2005/07/26(17:11:49), pages dumped: 63699, first LSN: 2724:195:1, last LSN: 2724:197:1, number of dump ...more >>

I wanted to build my own custom version of Enterprise Manager using vb.net. Where can I get the icons/pictures that enterprise manager uses (eg the icons used in the treeviews for database, table, user, stored procedure, user-defined function etc) ...more >>

Hello, I have a query that returns 2 rows, which I need to combine into 1 row. The query looks like this: SELECT t.CName, t.AName, t.ACurrency, Sum(t.NumPayments) AS 'Payments', CASE WHEN t.TCode = 'debit_batch' THEN SUM(t.LAmount/t.ExchangeRate) END AS 'Amount (incl. commissi...more >>

I'm going over some triggers in an old database we have and have come accross the following... .... IF (@ROWCOUNT = 0) RETURN .... Can a trigger ever fire if zero rows were affected? Should I remove this from the trigger? Thanks. ...more >>

I'm using MS SQL Server 2000. Here is the table definition: CREATE TABLE BigTable ( Column1 int IDENTITY(1,1) NOT NULL, Column2 nvarchar(10) NULL, Column3 nvarchar(100) NULL, Column4 nvarchar(4000) NULL, Column5 nvarchar(2) NULL, Column6 smalldatetime NULL, Colu...more >>

I've 2 tables. Tb1 F1 F2 --------------------- 1 Adi 2 Ytk 2 Yuk Tb2 F1 F3 ------------------ 1 5 1 6 2 7 3 2 I wanna join 2 table above, So I get data like it: F1 F2 ...more >>

Hello there I have numbers that i would like to present it as standart currency: 0.00 and still has it as number So far i could do this only by convert it to text. and it's not good because i'm export it afterword to Excel. I've tried to convert it to Decimal(10,2) and it didn't do the jo...more >>

We have had an unusual experience recently. A literally tremendous amount of transactions occurred on a table. This table had indexes placed on it. We had uncovered the transactions from the log file through ApexSQL Log. The symptoms were that each record in the table was being deleted and th...more >>

My company is running a large Clipper application using Advantage Database Server 6.2 as the backend. We have .DBF tables. We have 6 major retail markets with over 200 tables in each folder (market). I am looking for an easy (if that is possible) way to convert these .DBF tables into SQL Serv...more >>

is there any way to return records from a stored procedure without having to create a table or temporary table structure for the records. What I mean is, is there any way to do something like: select * from sp_my_procedure. What is the closest we can get to that kind of simplicity? ...more >>