All, How are you doing debugging of this stuff? If I wrote a SP or Trigger in a few thousand lines including variables, loops. switches, error code evaluation, etc., it's hard or even impossible to use the Query Analyzer to look inside the working process like from a usual programming envir...more >>

Hi! I have written a stored procedure that uses a distributed query that looks like the following: SELECT Code, ShortName FROM OPENQUERY(LINKED1, 'SELECT Code,ShortName FROM dbo.Client') Client LINKED1 refers to a system DSN on the server that is link to a Sybase database. I can run...more >>

Hello: I'm in the middle of an implementation and have encountered error 2812 though I can see the object to exist. Here's what I'm trying to do. I have established a link server and can query tables on that linked server successfully. I also created a couple of stored procedures on th...more >>

I have ... CREATE PROC InvoiceDeleteExample2 ( @InvoiceList1 VARCHAR(255), @InvoiceList2 VARCHAR(255) = '', ... @InvoiceListN VARCHAR(255) = '' ) AS .... GO I would like.... CREATE PROC InvoiceDeleteExample2 ( SELECT * FROM table; ) AS .... GO I hope help... thx ...more >>

Hi I'm struggling to get my head around this one, so will try to explain as best I can. I have two tables... Hospital with the following columns: Hospital_ID, Procedure, Risk, Hospital_Count.. etc And data like: 013, 14, 0, 44 013, 14, 1, 54 (The hospital 013 deos not have any risk ...more >>

Help Please! Anyone know how two join muliple records into 1. I have 3 tables like the Pubs DB. Authors Titles TitleAuthors This allows for many to many relationships. I need to be able to query this database so I can import titles & authors to a system that does not deal with many...more >>

i'm constantly hearing how terrible it is for performance to use a cursor in sql server and that there is always a better way. but i can't seem to come up with a solution that would perform better than the following cursor: this is typically performed on around 8,000 records - i need to step t...more >>

Hi: In my Query Analyzer results, my text always gets cut off. How do I display or copy and paste everything? Thanks, Don ...more >>

We have this big fat momma SQL Server that has some tables that various combinations of doofuses have linked into several different versions of Access databases. Though I don't work at this end of the shop, there is apparently some Lotus Notes bafoonery happening on the same tables, and some ...more >>

Hi All, I needed some help. I have a SQL 2000 database where I have all the primary/foreign key constraints defined but cascade delete is not defined anywhere in the system. Given this scenario can I write a generic routine that will delete all data and cascade it down. For example: I have ...more >>

SHAPE {{? = CALL dbo.spContact( ?) }} AS cmdContactNew APPEND ({SELECT * FROM [Estimates]} AS cmdEstimate RELATE 'ContactID' TO 'ContactID') AS cmdEstimate In a data environment, it doesn't seem to work against an SQL Server. How do I use this in a command object in the code? If I put thi...more >>

Hi all! I have a database that contains about 500 tables that carry the ownership of dbo. Then I have about 300 views that were created by a developer and carry his ownership. The tables are shown as the dependencies of the views. I am trying to create indexed vies, but it keeps on coming up...more >>

SQL 2K I got a SQL Job which calls a stored procedure , which in turns calls many other stored procedures. Is there any way to find , when the job is running, which SP is being currently executing. Lately my job is taking more time to complete. Thanks MS ...more >>

Hi, I have a DB that I need to protect it from being attached to other SQL Servers if someone could access its files. Any help would be greatly appreciated. Amin ...more >>

I'm performing a webservice call from a SQL Server stored proc. Using the sp_OA... functions, I'm passing data to a COM object (written in C#) and executing the call to webservice by launching a function on the COM object. When calling from one box to another, I get an 'HTTP 407 - Proxy Authe...more >>

I need help constructing an INSERT query. I want to insert rows from one table to another where they don't exist in the second. The problem is, I don't have a unique field I can use to make the check against. I have a date and time and an id field. There may be 1-10 or so rows with the...more >>

I am hoping this is the right place to post, because I didn't see a NG for regualr SQL statements. Here is the problem I am having. I have this script: var1 = Request.Form("date") var2 = Request.Form("course") var3 = Request.Form("addey") var4 = Request.Form("par") var5 = Request.Form("r...more >>

Is it just me or is MS expecting everyone to be using VS.NET 2005 with NS2005? The Microsoft.SqlServer.NotificationServices.dll is built with Framework V2.#. As far as I know VS.NET 2003 does not allow switching to a newer Framework. So what is the solution, write a custom component simila...more >>

I've never used DTS and was wondering how to schedule a script to run. I notice when you open an ActiveX Script Task it's formated like this: Function Main() Main = DTSTaskExecResult_Success End Function I wrote a script that will query a database and send a mail to certain recipients w...more >>

We have a table that contains a composite primary key: id and library_id Below is the current code for inserting into a history table. Each new record get assigned the next available id number per id and library_id pair. Is there a more efficient way of doing this? Our table has over 2 mi...more >>

Can I sum columns from within a table function? I've created a function to aggregate values from several other tables. I can wrap the function in a stored procedure and do this, but can I do it at this level? Given a table function: CREATE FUNCTION dbo.AddItUp () RETURNS @MyTable TABL...more >>

We occassionally run into timeout situations on a production database server. This occurs when a someone runs a poorly-formed query against a large production table. Without the WITH (NOLOCK) hint, the query obtains a shared lock on the table for the duration of the query. During this time, m...more >>

I am tryping the xpsmtp solution to sending email from SQL (very cool BTW) and would like to receive an email if backup succeeds (confirmation) and a different email if it fails (with error message preferrably). THe job lets you setup the backup "step" to continue to next step in both these case...more >>

I have a table as follows: CREATE TABLE [Events] ( [UserName] char (64) NULL, [ComputerName] char (32) NOT NULL, [IPAddr] char (16) NOT NULL, [MACAddr] char (17) NOT NULL, [Modified] datetime NOT NULL, [Event] char (10) NOT NULL, [Currency] bit NOT NULL ) This is populated by means of a...more >>

I have a backup job and would like to receive an email if backup fails. How can I configure SQL 2000 to do this? The problem is that I have no MAPI client on that server but I do have CDO and a couple other COM objects that support simple SMTP emailing. Thanks! Jon ...more >>

I have a question about Primary Keys and uniquely identifying records. I have a Cash Register Table, Cash Register Product Table and a Cash Register Group Table. The cash register stores Products by PLU and Groups by numeric ID. The cash register table stores a record for each register I have....more >>

I have a query built into another query and I need retrieve your data: (select {fn Concat('Select ', {fn Concat({fn Concat(current_user, '.')}, 'long_to_number(''PARA_TX_VALOR'',''SGI_PARAMETRO'',''PARA_ID_PARAMETRO' ') FROM SGI_PARAMETRO where para_id_parametro = 1')})}) When I execute query ...more >>

Please excuse my newbieness. I don't know which NG is best for this question, but I'll start here. I'm connecting to a SQL database to get data for an Excel workbook. I have some tables that need to join to one that is in the SQL database. Since I'm not allowed to create tables on the ...more >>

I am working on web application that will be in several different languages, I don't want to add columns for each language because it should be possible to easily add new languages. Is there any simple solution for this? Here is db and data: -----------------------------------------...more >>

Can someone explain what this error means? The query processor could not start the necessary thread resources for parallel query execution. Here is the query tht I tried running that generated the error: Select Zufc.[Bus Line] as Business,Zufc.Servline as Service,Zufc.SubServ as SubServic...more >>

I am putting some SQL statements below that I don't understand. I ran into when I accidentily mistyped a select statement. The Select within the parenthesis don't have a space after the From in one instance. I would have expected a syntax error, but don't get it. -- *****************...more >>

I wrote this question back on Tuesday 9/14/04, but apparently it did not get posted, so I am trying again. I have an application that represents objects in a tree like structure such as: + Level 0 +--Level 1 +----Level 2 +------Level 3 .. +-------- Level n Each ...more >>

Error ('long_to_number' is not a recognized function name.) See error part function create function long_to_number (@in_column varchar, @in_table_name varchar, @in_pkcolumn varchar) returns int as...more >>

Hi, Is there a way to drop all the defaults associated with a table without knowing the default name? I have a table CREATE TABLE [dbo].[Sample] ( [SampleIndex] [int] IDENTITY (1, 1) NOT NULL , [Value] [int] NOT NULL , ) ON [PRIMARY] then I changed the table ALTER TABLE dbo.Sample...more >>

I'm getting an unspecified error, my favorite kind of error, when trying to bring up a data report in visual basic. This is the call in my report class deReports.cmdNew CInt(parameter) SHAPE {{? = CALL dbo.ContactNew( ?) }} AS cmdNew APPEND (( SHAPE {SELECT * FROM [Estimates]} AS cmdNe...more >>

Given code like this : == create table t1 (c1 uniqueidentifier) insert t1 values ('00000000-0000-0000-0001-000000000000') -- A insert t1 values ('00000000-0000-0002-0000-000000000000') -- B insert t1 values ('00000000-0003-0000-0000-000000000000') -- C insert t1 values ('00000000-0000...more >>

Very sorry for the report (if any).... still experiencing problems with posting via the msdn site. Anyways, sorry... Why is this not working properly?... I want to print each csv separated value in @txt... so the result would be like this: 123 456 789 declare @i int set @i=1 decl...more >>

I can't seem to think straight this morning, maybe you can help. First, the table.... CREATE TABLE Messages ( FBNumber int NOT NULL, MessageID int NOT NULL, MessageType char(1) NOT NULL CONSTRAINT CK_MessageType CHECK (MessageType IN ('Q', 'A')), DateSubmitted datetime NOT NULL, --CONS...more >>

Hi, I have two scripts SCRIPT_A and SCRIPT_B I always have to run SCRIPT_A BEFORE SCRIPT_B, however I sometimes run SCRIPT_A independently rather than place all code from SCRIPT_A inside SCRIPT_B and I would just like to have something like an include in SCRIPT_B that references SCR...more >>

Hi,=20 Is there a way to have the results of a stored proc programmatically open = excel and dump the result set into a workbook? Maybe DTS? Thanks Ron Sissons, DBA Information Technology Services Riverside County Office of Education 3939 Thirteenth Street, Riverside, CA 92502-0868 Telepho...more >>

I need to calculate the number of days between two dates. In most cases, they will be more than 365 days apart, so I am not sure DateDiff() will do it for me, as it seems to want to deal with days in the same year. Any ideas?...more >>

Hello, all. Is there a way to "undo" (rollback) an update/delete/insert query in SQL Query Analyser for SQL Server 2K without wrapping the code in a transaction? Is there a global variable that can be set to not commit the changes to the database until some kind of commit command is sent? Thanks....more >>

How can I update record field in insert trigger? I need something like this: inserted.myColumn = @newValue ...more >>

Hi there, I am reposting this hoping someone can help... Is there a way to determine the transaction_id from within the transaction itself? Sort of like the bind token except I need the actual transaction id that SQL Server is using in the LOG. Thanks Bob ...more >>

I'm considering moving our existing tool's data store from a binary flat file(still very fast) to a relational database, namely SQLServer. Currently, our flat file DB is loaded into memory, when a change is made on client A, client B has no way to know this and therefore doesn't reload the dat...more >>

I'm using this to extract the time part of a DateTime field: RIGHT(CONVERT(CHAR(20), DateTimeField, 22), 11) I get what I was after, but it returns as text. I want to return the time as time. What do I do? tod ...more >>

Hi all, I need to put a constraint on an image field (FileContent) and text field (Filename) like this one (Filename IS NOT NULL AND FileContent IS NOT NULL) OR (Filename IS NULL AND FileContent IS NULL) Any clue why MSSQL doesn't allow NULL check on image/text field? Any suggestio...more >>

Hi I converted a varchar (400) field to Varchar (4000). I free typed text into this field using EM and soon discovered that you have a limitation in EM for 1023 characters. I used QA to insert into this field but I can only get the same amount of characters into this field again (1023). I dr...more >>

Hi, Anyone worked with tables having many colums defining conditions? Acutally I am working with one product which is realted to energy. i.e i will get a respond from one tool saying it's temperture is 98 degrees. So I will have to match this with a table row whether it is less then or ...more >>