Database Newsgroups: SQL Server database programming

TRY.CATCH question

Farmer - 25 Feb 2006 10:24 PM - 3 messages

According to SQL 2005 BOL, A TRY.CATCH construct cannot span multiple batches. A TRY.CATCH construct cannot span multiple blocks of Transact-SQL statements. For example, a TRY.CATCH construct cannot span two BEGIN.END blocks of Transact-SQL ...

Pls, help: Bill of Materials and SQL 2005 recursion problem

Farmer - 25 Feb 2006 8:15 PM - 13 messages

/* Hello everyone, Thank you for taking your time to look at my problem. In our busy lives it's hard to find time, therefore, I really appreciate your effort. The problem I am tryiing to resolve is this. In the Bill of Materials structure, any idem can own components, therefore, becoming an assembly. ...

Insert Line Feed in Function Return Value

Alan Z. Scharf - 25 Feb 2006 7:54 PM - 5 messages

Hi, 1. I have a function that concatenates string values as text scalar output. 2. The function, to be used in a report query, builds a string which reads '12/31/1999 US$ 12/31/2001 EUR.' 3. I want this to appear in an Access ADP report as ...

Move MSSQL 2000 -> MSSQL 7 (HELP!)

Rob - 25 Feb 2006 5:07 PM - 4 messages

I need to put a database that is on MSSQL 2000 to a machine that is running MSSQL 7. Is this possible? There should be nothing in the 2000 db that wouldn't go back to 7. It was ...

Dubious SQL Login?

Mike Labosh - 25 Feb 2006 4:41 PM - 8 messages

I want to centralize server logs. All these server services (IIS, ISA, etc) all know how to log to ODBC, and I want to exploit this so that I have a central place to analyze logs instead of trapsing through the file system ...

Assign ID to distinct names

Green - 25 Feb 2006 3:32 PM - 8 messages

I have a table called NAMES with 2 columns - id, name. I have a bunch of names in the table but id is null for all. I need to develop a script that will assign a unique id to each distinct name. ...

TSQL syntax

smk23 - 25 Feb 2006 2:56 PM - 5 messages

I am trying to update a table with the following statement: UPDATE OccRadiology SET OccRadiology.Ipsil= ( SELECT [CodeLookUp].[CodeID] FROM CodeLookUp WHERE OccRadiology.Ipsil = '' + CodeLookUp.Code+'' AND CodeLookUp.CType = 93) This works fine as long as there is a match on OccRadiology.ipsil (varchar) ...

select top problem

dhnriverside - 25 Feb 2006 2:12 PM - 4 messages

Hi guys I'm having a problem creating a SELECT TOP statement. So far here's what I've got... SELECT TOP 4 * FROM Discs ORDER BY SoldThisMonth DESC This should give me the 4 bestselling discs this month, except it actually ...

Database patching.

Colin Dawson - 25 Feb 2006 10:45 AM - 4 messages

Hi Everyone. The company that I work for have a database, which needs patching every so often from one version to the next. We've been trying different methods of upgrading the database from one version to the next. But each idea that ...

How to Create an XSD/XML Schema file?

dsijta - 25 Feb 2006 3:17 AM - 6 messages

I am trying to load the following XML file into a database table. I receive the file from an external source exactly as follows: <list type='full' level='state' val='CA'> <ac val='714'> ...

Server Properties

mason - 25 Feb 2006 3:03 AM - 3 messages

What's a good way to tell whether the server connected is local or not? How to find out the communication protocal being used, TCP/IP for example? Thanks. ...

The mystery of

Daniel Manes - 25 Feb 2006 1:56 AM - 13 messages

Okay, here's my prediament I have 4 tables: 1. CarModel (car_id, year, make, model) - list of all cars in the database 2. CarAttribute (attribute_id, attribute_name) - list of all car attributes (e.g., type, size, weight, engine type, engine size, engine ...

Trigger and what caused the table change.

Anthony - 25 Feb 2006 1:54 AM - 2 messages

Can someone tell me if there is a way to tell what caused a trigger on a table to fire? I want to track the field change and what stored procedure call caused the change. Thanks, Anthony ...

Qustion about join 2 tables???

Wing - 25 Feb 2006 12:46 AM - 3 messages

Hi everyone, I have problem to join 2 tables together to show the selected results into a datagrid. table1 hosts all customer personal information. table2 hosts all the trasaction records for each customer table1 has fields such as "customerID", "CustomerName" and "TelNumber". ...

clr references

Paul Durrant - 24 Feb 2006 11:23 PM - 2 messages

is it possible to add other references to a project, in particular i need to add system.directoryservices ...

how to return name of day 'Monday' from a date?

Rich - 24 Feb 2006 11:21 PM - 3 messages

Hello, Does sql Server (2k) have a function for returning the name of a day like in ..Net date.DayOfWeek.ToString returns say Monday. Or do I have to use a Case Statement with Datepart? Thanks, Rich ...

update stored procedure help

AkAlan - 24 Feb 2006 11:15 PM - 5 messages

I have converted Access tables over to SQL and am having a problem with a couple of datetime columns. My problem is with columns that hold only time values, if the time "0:00" was in the Access table then only the date ...

delete trigger for multiple row deletes

Keith G Hicks - 24 Feb 2006 10:44 PM - 7 messages

I'm racking my brain trying to figure this one out and I have a feeling it's simpler than I'm making it out to be. I need a trigger that will prevent delete of certain rows but not all. For example, I have a customer table: ...

Cannot Debug SQL 2005 Stored Procedures in VS 2005

EagleRed@HighFlyingBirds.com - 24 Feb 2006 10:35 PM - 4 messages

I am having trouble in consistently debugging T-SQL stored procedures in SQL 2005 using VS 2005 Pro. I have enabled debugging on the connection in the Server Explorer and enable CLR Integration in the Surface Area Configuration tool. At one point I could step into the sproc but now I cannot. What has ...

Stored Procedure Differences

Mike Collins - 24 Feb 2006 9:22 PM - 2 messages

I need a stored procedure to either return all items in a table or filter on for certain items. Is it bad coding to write the stored procedure as one and decide which to run based on if a filter parameter was passed in? Would that ...

Question about GroupBY and Aggregate functions.

smwall - 24 Feb 2006 9:03 PM - 4 messages

Ok I know this is totally a noob question but I'm working with a table of the following structure ID (int), FK (int), Date (datetime) I would like to select the ID & Max(Date) grouped by the FK. I don't ...

Find the first day the account passes a threshold

Thom Anderson - 24 Feb 2006 8:27 PM - 2 messages

Using daily transactions, I need to find the first day an account deposits more than 20 dollars (+ or -) please see the DDL below, a sql statement, and the desired output. Thanks create table #bankaccounts ( accountid int not null ...

Join Condition <> problem

jereviscious - 24 Feb 2006 8:21 PM - 2 messages

Hi All, I would appreciate any help you could give. I'm being as detailed as possible.. This is the first table in the join and the data in that join.... It appears to be a duplicate name, but it shouldn't affect the query... ...

Query help please

PRESENT321 - 24 Feb 2006 8:19 PM - 11 messages

I would like to query this table: Name date Fred 1/1/06 Mary 1/1/06 Mary 2/2/06 And get distinct Name with the earliest date: Fred 1/1/06 ...

SELECT Query Help Needed

MrBiggles909 - 24 Feb 2006 8:17 PM - 3 messages

Hello all. This is the most advanced thing I've tried in SQL and it's got me stumped. Hopefully someone can help out. I have two tables: 'inventory' and 'inventory_sold' (I'll refer to them as 'i' and 'is') ...

Determining last time a table was used?

Wade - 24 Feb 2006 8:10 PM - 3 messages

Hi all, I have recently join a new team, and am in the process of trying to clean up one of their databases. There are tons and tons of SQL objects that are not used and that should be removed, however no one remembers which ones should ...

Attachment using extendet store procedures

viktor - 24 Feb 2006 8:09 PM - 3 messages

I am trying to send SMTP email with attachment using cdosysmail and getting error on this line: EXEC @hr = sp_OAMethod @MailID, 'AddAttachment', NULL, @FileSource Error message: Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded. ...

Returning a success or fail value from a stored proc

RSH - 24 Feb 2006 7:43 PM - 2 messages

I have a VB6 project that is calling my Stored proc like so: strSQL = "spRenameDatabase '" & strOldname & "','" & strNewname & "'" cn.execute(strsql) While this works I would like to return a test message upon completion from the Stored proc. ...

Performance issue with joining the text field

culam - 24 Feb 2006 7:41 PM - 3 messages

I ran this simple statement that contain 63,000 records for integration_errors and 250,000 records for ps_vwRN_contacts. The joined field, integration_msg, is a text field. I used convert() to convert text to string for comparison. There is no index on the table. It took 1 1/2 hours ...

.NET Assembly, sql 2005 + sp_oaCreate

Ron West - 24 Feb 2006 6:57 PM - 3 messages

I've got an assembly (in house, writen in c# 1.1 framework) that I've gotten to work under SQL 2000, but I've I can't get it to work under 2005. Steps I've done to get setup: ...

DELETE rows involving auto-increment fields

juventus - 24 Feb 2006 6:38 PM - 5 messages

I tried to delete a couple of records from a table which has a ID field which is auto-incremented(IDENTITY) . But, after deletion... when I tried to insert new records, the ID field gets incremented from previous next value (before deletion). Is there any way to get around ...

substring

Kat - 24 Feb 2006 6:27 PM - 5 messages

I am using SQL2000. Have a field called departments. Some of the department names have parentheses after the name with further information (in the same field) I want to exclude the parentheses, and their contents and just display the department name. ...

Duration Datatype

Papanii Okai - 24 Feb 2006 6:27 PM - 21 messages

Hi, I am storing specific metadata of certain media files in sql server. I would like to know if there is a way to store the time duration of a media file (some sort of time datatype). I know i could simply use varchar ...

NText Output Parameter or Alternative

shawn_dube - 24 Feb 2006 6:24 PM - 4 messages

Ive seen similar posts before but they have not really hit the issues I need answered. Simple example header and then ill give you my real world usage: --------------------------------------------------------- Create proc TestXML ( @Xml ntext, @oXmlResponse ntext output ...

Determin if this is Standard or Ent edition

moondaddy - 24 Feb 2006 6:04 PM - 2 messages

How do I tell if an install of sql server is standard or enterprise edition? ...

Sql Timeout

Arne Garvander - 24 Feb 2006 5:48 PM - 4 messages

I program in vbscript and VB.net I know I can set a timeout on my command object. Can I set a timeout in my connection string also? ...

Cannot get "LIKE" to Work in My Query

Chaplain Doug - 24 Feb 2006 5:44 PM - 3 messages

Excel 2003. SQL Server 2000. I have an Excel module that grabs some data from an SQL database using an ADODB connection. If I use the following in the SQL query, it returns no data: (WHERE INVOICENUMBER LIKE 'MS*') ...

Select query - all distinct from one column, highest for another - please help

thegoogster - 24 Feb 2006 5:37 PM - 6 messages

Hello I need some help. I'm trying to create a query on the following table (example data): Artist Title Release Upload A1 T1 01/03/2006 01/01/2006 ...

ISNULL? COALESCE? etc?

Rick Charnes - 24 Feb 2006 5:09 PM - 7 messages

In my SELECT statement, I want to insert a space character between column 'street_number' and 'street' if 'street_number' is not null. I'd like to find a way to do it other than repeating 'street_number' inside a CASE: SELECT CASE WHEN street_number IS NULL THEN street ELSE street_number + ...

ListCompatibilityLevels (DMO) Problem in Yukon

Vaibhav - 24 Feb 2006 4:55 PM - 4 messages

Hello, The ListCompatibilityLevels ( Property of SQLDMO.SQLServer2 Object) does not work properly for YUKON. It does not report the 'Database compatibilty level 90'. Please Help. TIA Regards Vaibhav Tole. ...

Blocking 101

wnfisba - 24 Feb 2006 4:36 PM - 2 messages

Pretty new to SQL Server and support multiple applications on SQL Server. Just encountered a situation where "blocking" was occurring. Can someone provide me more information or a link explaining Blocking and how I can use SQL Server Enterprise Manager to determine who and what is ...

delete multi records

js - 24 Feb 2006 4:26 PM - 2 messages

hi, I found there are few records have duplicate key in my databaes, for example, title table, have server same title, how to write a query, can delete teh duplicate entries but only keep one. how to do this? Thanks in ...

List Dates Between Min & Max Dates

Galahad - 24 Feb 2006 4:23 PM - 4 messages

I'm trying to find a way to list the dates (XXXX-XX-XX) between a Min & Max Dates Example. Min Date: 1/1/2005 Max Date: 5/1/2005 Dates between would be 2/1/2005,3/1/2005,4/1/2005 ...

NOLOCK SQL Query Safety

Eric - 24 Feb 2006 4:18 PM - 3 messages

Hi, I have a question that has to do with the safety of a NOLOCK SQL Query. The situation includes a database that is hit very heavily by a workflow engine. I have a request to build a app that will query the same tables as the ...

Proper way to IF THEN in a dynamic SQL Stored Proc

RSH - 24 Feb 2006 3:55 PM - 3 messages

The code below does not work, I get an error: Msg 203, Level 16, State 2, Procedure spRenameDatabase, Line 68 The name 'master..xp_cmdshell 'Copy "D:\Microsoft SQL Server\_01TT2.mdf" "D:\Microsoft SQL Server\_01TT2_Renamed_To__01TT2rsh.mdf"'' is not a valid ...

question about looping through a record set

danielle.m.manning - 24 Feb 2006 3:40 PM - 3 messages

I am wondering if someone can help me write a stored procedure to solve this problem without using a cursor, which I gather should be avoided if at all possible from what I've read here. My stored proc needs to return a list of statuses from a table for a ...

2005 slower than 2000?

br - 24 Feb 2006 3:19 PM - 8 messages

Admittedly I'm evaluating 2005 in a virtual machine but I'm comparing it to an older machine: 2005 thinks it has 1Gb and 1 cpu on a host which has 2GB, 2 2.4Ghz Amd64's, raid0. it runs in boosted priority when active. ...

SQL Query - Select

Alan - 24 Feb 2006 3:01 PM - 2 messages

Hello, I am working with SQL Server 2000. I have a table with the following structure: ID int TimeStamp datetime RValue float ...

Optimization with ORs

querylous - 24 Feb 2006 2:57 PM - 15 messages

Hi- I notice that many times when I use an "or" in a query with an and select from table where col1=x and (col2 = y or col3=z) It results in terrible performance. It seems to me that there is no way to ...

Manually determine execution plan?

querylous - 24 Feb 2006 2:26 PM - 9 messages

Hi- is it possible (and does it ever make sense), to manually determine a query execution plan, instead of letting the optimizer do it. There are certain times, when I think "well, I [think I] know that the best place to ...

Graphical representation of database

HolySaphAngel - 24 Feb 2006 2:09 PM - 6 messages

Greetings to all! I have a question. I have an existing database in SQL Server. What I want to do is create a map of the database graphically (show all relationships, have comments about what values equal what and what ...

get diff between two dates

samuelberthelot - 24 Feb 2006 1:45 PM - 4 messages

Hello, Here is my table: [UserLogFailed] { UserName varchar LoginTime datetime Locked bit } If the user attempts to log in my front end app and his password is not correct, I create a userlogfailed record for this username. Before creating this record, I must check if there are already at least ...

CLR procedure calling webservice

Ludo - 24 Feb 2006 1:39 PM - 2 messages

Hi, I want to create following procedure to call a webservice. build ok execution not ok. When i do it in a seperated program it works. in the clr procedure not. It always end with 'System.InvalidOperationException' occurred in ...

Killing active connections before detaching a database

RSH - 24 Feb 2006 1:38 PM - 7 messages

How can I kill all active connections to a database before detaching it? I am calling sp_detach_db from an application but I keep getting the error "Cannot detach because there are one or more active connections." Thanks, Ron ...

using alias column in a select statement

Robert Bravery - 24 Feb 2006 1:15 PM - 3 messages

HI all, If I have a select statement select Claim.rasclmnumber, ClaimFinancialPayment.Ammount AS Payment, (SELECT SUM(amount) FROM claimpaymenthistory ...

group by clause

Manish Sukhija - 24 Feb 2006 1:08 PM - 3 messages

Hi all, i have 2 tables in pubs database, first one is titles and second one is sales, i want to get sum of quantity which is in sales table along with title_id ...

sum() in a select statement

Robert Bravery - 24 Feb 2006 12:00 PM - 9 messages

HI all, I have the following selectstatement SELECT Claim.rasclmnumber, ClaimFinancialPayment.Ammount AS Payment, ClaimDeductables.Ammount AS Liability, (SELECT SUM(amount) ...

collation error

Jaco - 24 Feb 2006 11:53 AM - 6 messages

Hi I am building a database using the Latin1_General_CI_AS collation. Everything seems to work fine but I am stuck with this one view. When creating the view I get this error: Server: Msg 446, Level 16, State 9, Procedure qfmFieldUserNames, Line 4 ...

SELECT query problem

Mats-Lennart Hansson - 24 Feb 2006 10:40 AM - 5 messages

Hi, I have a problem and hope someone can help me. If my table loos like this: Group Name Status ...

subquery confusion

CharlesA - 24 Feb 2006 10:21 AM - 10 messages

Hi folks, I've got a table that is roughly like this (this is illustrative of the basic problem I want to solve and is not 100% industrial grade DDL and this is on purpose for this post only) CREATE TABLE [Accounts] ( ...

Selecting similar records from two temporary tables

annoyedtuna - 24 Feb 2006 10:00 AM - 2 messages

In an SQL Server 2000 stored procedure, I have two temporary tables whose structure is identical, some records are common to both but some are not. eg... TEMPTABLE1 UserID, UserName 2, bill 7, ben 9, bertie 17, brian 18, billy ...

multiple selects

Peter Neumaier - 24 Feb 2006 8:47 AM - 6 messages

Hi NG, I'm having following stored procedure: SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO ALTER PROCEDURE foo AS SET NOCOUNT ON CREATE TABLE #tmpTBL1 (...) INSERT INTO #tmpTBL1 SELECT foo FROM tblFoo CREATE TABLE #tmpTBL2 (...) INSERT INTO #tmpTBL1 ...

Writing files from stored procedures in SQL 2005

offline - 24 Feb 2006 8:18 AM - 3 messages

Hi, so we need a solution how to write content from blob/image fields to file system from stored procedures. As i understand i can program in c# in sql 2005 and extend it like this. So my idea would be to write a dll and then register it with SQL server, and ...

INSERT performance concidaration

yan - 24 Feb 2006 7:08 AM - 5 messages

Hi, I am running sql srv 2000 sp3a on win2003 srv dual 2.8 CPU 3GB RAM. I have 2 new tables with a 1:M relation that are about to be inserted 30 to 100 times a second. To easy the work need to be done by sql srv I was thinking to assure ...

UPDATE!

Mr Ideas Man - 24 Feb 2006 6:20 AM - 6 messages

Hi all, I am trying to update two tables using a stored procedure.. The first update acts as required, however the second update doesn't appear to do anything ? Any thoughts? Cheers, ALTER PROCEDURE [dbo].[updte_asmt_v1_employee] @username VARCHAR(20), @password VARCHAR(20), ...

Case Problem!

Mr Ideas Man - 24 Feb 2006 4:41 AM - 4 messages

Hi all, I am trying to create the following stored procedure.. I am still not all that familiar with the Case statement so any help would be appreciated...!!! I am getting a syntax errors... Is there a better way to do this? ...

SQL Query - Is this possible?

John Dinning - 24 Feb 2006 2:14 AM - 18 messages

I would like to create one query that select all rows from a small table and adds an extra column. The value in the extra column is the result of other query, but the query called depends on the value of a field in the original table. ...

CREATE NONCLUSTERED INDEX - > QUESTION

ReTF - 23 Feb 2006 11:41 PM - 3 messages

I have one column tha have many values that repeat, and I need use join with this column, I should create an NONCLUSTERED INDEX to improve performance? Thanks ...

How to replace a string in TEXT column with case sensitive?

Andreas Klemt - 23 Feb 2006 11:31 PM - 2 messages

Hello, I have a table like this id int mytext text I would like to replace a string in mytext with case sensitive like this UPDATE myTable SET mytext=reaplce(mytext, 'xxx', 'yyyy') But this doesn't work for TEXT. How can I do this? ...

Execution plans differ

Derrick Pope - 23 Feb 2006 10:55 PM - 2 messages

I have a stored procedure that is taking too long to execute. In trying to diagnose the problem and come up with a solution, I executed the query the stored procedure runs in query analyzer and it ran very fast. I compared the ...

Designing a Code Table

JY - 23 Feb 2006 10:55 PM - 7 messages

I have a language table 'CODE_LANGUAGE' where we defines languages. CREATE TABLE [dbo].[CODE_LANGUAGE] ( [language_id] [smallint] NOT NULL PRIMARY KEY CLUSTERED, [language_name] [varchar(20)] NOT NULL ) ON [PRIMARY] GO I need to design a table for Location codes "CODE_LOCATION". This table need ...

joe celko book

ichor - 23 Feb 2006 10:26 PM - 17 messages

my friend gave me a copy of the 1st ed of sql for smarties. is the third ed much different. i havent read the 1st ed but i would like to read the 3rd ed if its much better. i use sql server 2000 and will upgrade to 2005 soon. i am an ...

One of Contained in field search?

Larry Charlton - 23 Feb 2006 10:07 PM - 3 messages

Does anyone know how to do a search where you have a list of values you want to know if they are contained in a field? I have a field with a select statement from profiler. I'd like all the rows ...

Listing permissions on Stored Procedures via query.

Horst - 23 Feb 2006 9:35 PM - 5 messages

How do I list the permissions on a stored procedure with a query? I'm trying to remove Public from some xp_* procedures but I'd like to see what Public is assigned to first. Thanks much. ...

Simple error handling question

RSH - 23 Feb 2006 9:32 PM - 4 messages

I have a simple T-SQL execution block. I need to see if this executed without an error because I have another procedure to run if it was successful. This is my current code: SET @query = 'master..xp_cmdshell ''Copy "' + @LogPath + @CurrentDB + ...

HTML in SQL

Blasting Cap - 23 Feb 2006 8:51 PM - 3 messages

I have the following VB program running on a web server, that grabs a text file that's pulled from our SAP system (more or less a spun-off report). It then "wraps" HTML code around it, and splits it out so that ...

Help with error

News - 23 Feb 2006 8:36 PM - 2 messages

Hi, I was changing datatypes and in the process had an error message, cant remember what it was but I could not change the datatype until I dropped the PK on the table. After all, data type was changed, I tried to re-add PK and ...

T-SQL Syntax Question

mason - 23 Feb 2006 8:32 PM - 4 messages

Dumb question: what does it do to enclose an identifier between brackets ([ and ])? Escaping special characters? Also, I thought || (two vertical bars) was universally supported as a string concatenation operator, and that doesn't seem to be the case in MSSQL... ...

How to read database compatibility level from c#

James E. Clarke - 23 Feb 2006 8:25 PM - 2 messages

I'm trying to access the db compatibility level of a database from c# code. If you execute sp_dbcmptlevel with no parameters, the current level is returned to the output stream. My problem is how to get at this from c# ...

passing SELECT result to OS

Rob - 23 Feb 2006 8:25 PM - 4 messages

In Windows, I have a folder, lets say 'ABC'. I'd like this folder to be renamed only a daily basis, based the current datestamp, through T-SQL. So far, I've come up the following T-SQL stmt: SELECT RTRIM(LEFT(DATENAME(month, getdate()),3)+DATENAME(day, ...

How do I check the last modified time of an object in sql 2000?

Raymond - 23 Feb 2006 8:25 PM - 4 messages

Hi Guru, I wonder is it possible to know the last modified time of any objects in sql 2000? something like windows file explorer. For example, I am working in a group where other members can modify the table, stored procedures, ...

Are cursors extremely expensive?

Kyle Jedrusiak - 23 Feb 2006 8:23 PM - 4 messages

We're using a cursor in a trigger. Is using a cursor so expensive that we should special case out if the record count = 1 vs > 1? Kyle! ...

carriage return and line feeds

Greg - 23 Feb 2006 7:56 PM - 13 messages

I have a value of 7.5 when I ask for the length (after I have trimmed it) the value 5 is returned. Visually this is a 3. Someone suggested there may be a carriage return and line feed (Char(10) and Char(13)) in the column. ...

Accessing Different Servers

Scott - 23 Feb 2006 7:50 PM - 8 messages

If I have 2 registered servers, what syntax allows me to access both servers from the same query window? For example, if I say "use mydb1" and mydb1 resides on server1, I can execute sql against it. I'd like to execute another statement after finishing with mydb1 that ...

Free tool to compare two databases

Mark - 23 Feb 2006 7:32 PM - 5 messages

Hi, Is there any free tool that compares the schema and the data of two SQL Server databases ? Thank you ...

SP that returns multiple rowsets

John A Grandy - 23 Feb 2006 7:32 PM - 3 messages

For a SP that returns multiple rowsets , do you just follow one SELECT statement with another ? Or is it more complicated than that ? ...

retrieve all the jobs

MittyKom - 23 Feb 2006 7:17 PM - 2 messages

Hi All Is there a script or an easy way to get a list of all the jobs that are scheduled on my sql server with their steps. I have tried to quey the MSDB db tables. Thank you in advance. ...

How to reference a local temporary table in the @query parm of xp_sendmail

Cismail via SQLMonster.com - 23 Feb 2006 7:12 PM - 3 messages

Hi, Can anyone tell me if a local temporary table can be referenced in the @query parameter of the extended procedure xp_sendmail and if so, how? When I execute the following statements in Query Analyzer, I get the error message provided below. ...

Query

mvp - 23 Feb 2006 7:09 PM - 3 messages

I have two tables Table 1 is as following Id 1 2 3 Table 2 is as following. Here Id is the foreign key. Id Name 1 David 1 James 1 Larry ...

Padding partial table name with @variable value

b_dba - 23 Feb 2006 6:18 PM - 3 messages

Is there a way to this in SQL declare @partial_table_name varchar(128) set @partial_table_name = 'mytable' select * from customer into tbl_cust_@partial_table_name Above i am inserting the values from customer to table tbl_cust__@partial_table_name. I want to pass the @partial_table_name value ...

query performance question regarding ISNULL

Jiho Han - 23 Feb 2006 6:09 PM - 11 messages

I have a sql statement: SELECT A1.C_JOBBIDID, A2.ACCOUNTID A2_ACCOUNTID, A1.PROD_PR_GRP_C, A1.WON, A2.DESCRIPTION A2_DESCRIPTION, A1.OPPORTUNITYID, A3.USERFIELD5 A3_USERFIELD5, A2.STATUS A2_STATUS, A1.SPEC_PR_PROD_C, A1.SPR_ITEM_ALLOW_Q, A1.DISTPRICE1ST, A1.SUBPRICE1ST, A1.EXPDATE1ST, A1.DISTPRICE2ND, A1.SUBPRICE2ND, A1.EXPDATE2ND, ...

ADO VB/SQL help

JP Bless - 23 Feb 2006 5:42 PM - 22 messages

I am using VB6/SP5 MDAC 2.8 SP1... I had this code running on DAO for a long time and want to move the code to ADO... I get a clueless error "-2147467259 Automation error - Unspecified Error" when I run this code. The code is derived from two "simple" tables ...

Display result in hours, weeks and month

Test Test - 23 Feb 2006 5:29 PM - 6 messages

Hello! I have data stored in a fld which is a datetime difference in hours. I need to display it in 'Hours' (if it is less than 24), 'Weeks' (if it is greater than 168) and in 'Month' (if it is greater than 720). Is there ...

Using a UDF with argument in another UDF

JY - 23 Feb 2006 5:08 PM - 5 messages

I have a user-defined function which returns me current payroll period information for a pay frequency: CREATE FUNCTION [udf_GetCurrentPayrollPeriod] ( @a_pay_frequency smallint ) RETURNS TABLE AS return ( select pp.period_number, pp.start_date, pp.end_date, pp.ext_period, pp.pay_frequency ...

Help: CLR access to File Info

KurtK - 23 Feb 2006 4:57 PM - 7 messages

I'm trying to write a simple managed procedure that will echo back the name of a file (local to the SQL server) if it exists or 'File not found" otherwise. I created the assembly with PERMISSION_SET = EXTERNAL_ACCESS. However, when ...

Nonclustered index question

kaczmar2 - 23 Feb 2006 4:29 PM - 5 messages

I have a table that has around 100,000 records in it, growing at a rate of 200 records/day: CREATE TABLE [Main] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [Code] [char] (17) NOT NULL , ...

Is it possible to query a server's DBCC SHOWFILESTATS from one server to another?

Matthew - 23 Feb 2006 4:29 PM - 2 messages

I am trying to write a single query that will go out a collect all the DBCC SHOWFILESTATS. The trick is that the query has to attach to the remote server, then run the DBCC SHOWFILESTATS, and pull that information back. ...

DDL Trigger

Joe L - 23 Feb 2006 4:05 PM - 3 messages

SQL 2005 I have a table that is referenced by a view. Whenever a column is added or dropped from a table I want to update the view to include or remove the column - so I thought, 'DDL Trigger!' ...

Using SQL Server for Realtime Updates

vanisathish - 23 Feb 2006 4:04 PM - 2 messages

Hi, In my Web based NMS software i need to update the status of equipments realtime(every 1 sec). Currently i have a COM+ DLL called from my ASP pages to do the updation..This poses some performance issues, when more ...

Problem with Multilanguage and escape characters

GuillaumeD - 23 Feb 2006 3:06 PM - 8 messages

Hi, I have a problem with a request concerning chineese data on a nText field... Here is a sample: TABLE : T FIELD : TXT as nText I insert this value on my table : ? [???] ??????????? Now i'd like to query my table to retrieve rows containing an exact match to ...

sorting in a cursor

Insert Image File

OLEDB vs ODBC

changing ordinal position of a field

Server management Studio

How to use regular expressions on T-SQL?

Help on Query

Varchar Local Variable Question

Comma seperated IDS in SELECT query with IN.

transaction problem

suggest on sub query improvement.

sql server 2005 error handler

Optimize the query performance?

UPDATE failed because the following SET options have incorrect settings: 'ARITHABORT'

create trigger based on value of 2 fields

Cast varchar to decimal

Query Question

previous value in trigger

Need a help on a SQL Query

Using an alias in WHERE clause?

When to use Distinct vs Group By

List() Function

Math in a column

Help with float data type

Help with most efficient column sorting technique

Strange rollbacks happening

Active Directory / Groups + MS SQL (yikes???)

Insert running slow

Printing the results of a dynamic query

Q: SQL Server 2005 and Schemas

Date and time stamp

email using

Help with exception list from another table

Calling a user function

Optimizing daylight savings query by not using UNION

how to retrieve full text of procedure definition from syscomment

temp table dilemna

Datagrid/Stored Procedure Issue

Import / export question

UPDATE statement optimization?

VAlidate

Opinios: SQL DB Dev MCP Exam 70-229

SqlServer 2005 Express Question

CLR Stored procedure to access OleDb datasource, how?

updating rows using cursor

Restoring database from SQL-2000 to SQL-7

question about process order

performance difference between query analyzer and .NET code.

first day of week by Week Number

Help with Best practie and design

2005 using CLR for UDF accessing DirectoryServices

Internet Access

SQL 2005 - sys.dm_exec_sessions in stored procedure

Audit Add Windows Login?

What is the difference of these join

Automatically create rows

Moved SQL DB to SQL2005 and it is slow

Problems in selecting record.

return data from two tables

Output to serial port

deadlock problem

create primary keys on a view

use production server for read-only search?

Very strange BULK INSERT problem

View all table relationships

Validate AD user/password in stored procedure

quoted_identifier

Corrupt Function?

Bulk Insert of multiple, associated tables

Need help with a Case statement

DISTINCT PROBLEM

need help with percentage not calculating correctly

comparing the results of two stored procedures.

Parsing a field

Debugging stored procedures in SQL 2000

Requesting querry to view structure

message queues

Turn Off Defered Name Resolution ?

Database Access

How can I suppress a "Bulk Insert" diagnostic message

SQL Question

Order By

Select next TOP

SQLCLR crashes unexpectedly while debugging

INNER JOIN using OR condition

Directory Not found Error

Group By magic anyone?

When was a View last altered?

T-SQL Assistance

help connecting via vbscript to sql 2005 express edition

trying to report 'delta' amounts in query

Sql 2005 Identity Column

Transform from rows to columns

Column Comparison

HELP...Splitting a string in T-SQL

Upsizing Access databse to MSDE

Select statement

Help with nested inner joins

How to combine different row into one row

Code review tool for Stored procedures

Receive notification on several clients at the same time

sqs2005 CASE WHEN THEN

Ideas Needed Please...

export SQLServer2000 DB to Access

help with a 'simple' sql-statement

What is the best way to protect the data?

[SQL2000] permissions to use view based on tables from many databases

create trigger doesnt work

help with a User-Defined function to return a string from multiple records

izzit got for loop in transact-sql stored procedure

Copying users from one database to another

Excel to SQL

Stored procedure return val?

Weird Performance Problem

How to tell how many times an SQL executed in v7 or v2000?

unicode file bulk insert with Chinese characters

How to write synatax for implementing primarykey in sqlserver2005

Synchronize data between 2 instances of SQL Server Express?

Database Documentation is important

ASP 3.0 Error on SQL Server 2005

How to update multiple rows with different values in stored porcedure

Newcomer needs help with a SQL Query

how to avoid redundany in tables

Avoid Cursor: detect change in a timeline hierarchy

Need to do generic INSERTS

New entries in db query

full text search on doc files

if exists help

SQL 2000 Reporting Services Dev Edition

Grouping Problem

need help- case...when not working

Exceptions calling basic CLR stored procs...

Collation Conflict

SP's or Views

One for the advanced programmers!

Trigger performance (inserted/deleted)

using IN()???

query where Case sensitive

Loop / Cursor help

Does SQL Server creates some temporary table during query execution?

what's up with SQLCLR then

generate performance-column

Read file info via Transact SQL....

BCP out contains unwanted NULL(0x00)

Stored procedure parameters are missing when debugging in query analyzer

is there no way ?

need to retrieve value from trigger to dataset

Outloook 2002 & SQL Server 2000

drop all FKs in a database

is this wrong ?

Unable to connect to database via webservce

OLE DB provider "SQLNCLI" for linked server "linkedserver" returned message "No transaction is activ

Creditcard Validation script

Works but needs tweaking for faster processing

Select * from databse .. table where (what are the two periods for

INSERT...SELECT and OUTPUT question

Primarykey Fields

Need advice how to write a program to receive an image via email and put it into the SQL Server data

Update Customer Data..

Do I need second license?

Full-text search