Dear all, I am building a n-tier application which store history data in an SQL server 2005 database. I am uinsg Visual Studio 2005 with store procedure for handling all my database request. So far I get store procedure with few parameters, no more than 5 actually. Now my problem is that ...more >>

Hi, The following select statement gets the values from the Profiles table. Select Upper(USERNAME) , ProfileName, Company, Division, Region, Territory, 0, 'HOffice' , 1 FROM Profiles WHERE UserName = 'user_FirstName. user_lastname@COMPANY.COM' AND ProfileName = 'New Orleans North-396' I a...more >>

Hi, I've been working on a project that provides interaction between a SQL Server 2000 database and an Oracle 10g database. On the Oracle side, it is possible to define a return value from a function or an output parameter in a stored proc that consists of a complex record structure. I'm wo...more >>

This is a failed attempt (error at the SELECT statement) DECLARE @AuditXML as varchar(4000) SET @AuditXML = SELECT * FROM tblModule WHERE ModuleID = 'Admin' FOR XML RAW Thanks, Dean S ...more >>

I just tried converting a view to a function, but I get no output. This view works fine (output = 6 rows): SELECT * FROM dbo.View_PROD_SUPERSESSION_EX WHERE (SUPERSESSION_ROOT_ITEM_NUMBER IN (SELECT SUPERSESSION_ROOT_ITEM_NUMBER FROM dbo.Vi...more >>

'SQL in VB program ________________________________________________________ table example; EmpID, Date, MornIn, MornOut, AftIn, AftOut, Total ________________________________________________________ record example 1, 7/15/2007, 8:00, 12:00, 12:30, 4:00, 8.0 _________________...more >>

Hello, In my procedure I declare table variable as @ mytable, now I want to execute SELECT * FROM @mytable using sp_executesql stored procedure. Could you give me a hint how to do that? Thanks, GB ...more >>

I have a table with a column type text. Some of the row columns have 8000 characters of text with many lines (defining lines as text between line feeds). Disregarding linefeeds/carriage controls (removing them). In separate rows, I'd like to list every block (defining a block as 80 continue...more >>

I am using the following triggr for update. if I use this trigger and try to update one of column in table manually it gives the following error mesage. Error Message--> The row value(s) updated or deleted either do not make the row unique or they alter multilpe rows. >CREATE TRIGGER [...more >>

I am running a sync program that synchronizes my SQL Server 2005 database with a MySQL database online for integration with our newsletter / email program. The sync only synchs tables, not views or queries, so what I would like to do is set it up so that SQL Server executes a command that: ...more >>

this is my code and I keep getting an error that says Incorrect Syntax Near ',' Incorrect Syntax Near 'Properties' take a look at the code and see if you can figure out what it is. Thanks, Luke CREATE PROCEDURE usp_retstransfer @APN nvarchar(max), @Address nvarchar(250), @B...more >>

I have two tables Article (ArticleId, ArticleTitle, ArticleBody, DatePosted) ArticleRating (ArticleRatingId, ArticleId, Rating) I am trying to write a SELECT statement that will return the following rows ArticleId, ArticleTitle, DatePosted, TimesRated, AverageRating The TimesRated is a...more >>

Hi! I have followind. SQL Server 2005 express with some data and SQL Server 2000 on a web with other data. I need to update some data in SQL2000 periodically (every few minutes) I can schedule windows job to execute sqlcmd to execute t-SQL file. How do I move data from 2005 to 2000 by me...more >>

I need some generic trigger to log table data changes in insert/update/delete operations. With "generic" I mean that I want something general for every table. I need to store changes in an Audit Table, specifying old & value (in case of updates), delete key values, etc. Any ideas? Thank...more >>

Does anyone know of a way to create a database trigger that cannot be deleted? Except maybe with a password or something. I need to create one that only I could delete even if the datafiles are taken and attached to a different server. Thanks!!! ...more >>

I know this is covered before but I still don't know how to resolve. It obviously seems like a stupid microsoft bug. This code is working for remote servers but for the local instance of sql express I get only the name of the local machine. (There is no instance of sql server with the machi...more >>

Hi All, I have a table with some columns. My project is when I upade any of the column then I want a trigger to execute in a column named DATE_UPDATED. i have the following trigger but when I update any column it doesn't allow me to update the table manually. Where is the problem. e.g i...more >>

I am looking to extract contact information from a SQL Server database/table and create contacts for the users to see in their Outlook public folders. I would like to perform all of the functionality within a SQL stored procedure. Contacts may also need to be updated and deleted. I cannot s...more >>

hello I apologize for asking so many questions, but I didn't want to polute the forum with several threads 1) * My textbook claims that in relational model all connections between tables are implicit. I know the meaning of a word "implicit", but I don't understand its meaning in this pa...more >>

I have a table which stores Invoice records. The VB6 program which uses this table as a data storage inserts a new record when any existing record is edited. The InvoiceVersion field is increased by 1. For instance there is a record, containing values: ID Subtotal Tax Total Invoice...more >>

Hi all, I have a stored procedure in SQL2000 to do the following data loading. Input table1: Year, CompanyId, L1, L2, L3.... L300 (300 L fields) (example 2001, 3333399, 100, 200, 300...) Input table2: FieldName (example FieldName= 'L1' or 'L2'...'L300') Output table Year, CompanyId, F...more >>

I have a stored procedure that updates a system table. The system table has only ONE row in it containing various application defaults etc... and hence my update statement does not use a 'where' clause for the update of said table. One of the fields in this system table is a next contract numb...more >>

I have a stored procedure that updates a system table. The system table has only ONE row in it containing various application defaults etc... and hence my update statement does not use a 'where' clause for the update of said table. One of the fields in this system table is a next contract n...more >>

--find those ratings for which the average age of sailors is the minimum over all ratings select temp.rating, temp.average from (select s.rating, avg(s.age) as Average from sailor s Group by s.rating) as temp where temp.average = (select min(temp.average) from temp) run this query on sq...more >>

I am using MS-SQL2000. My query is like SELECT A.EntraceID, CASE WHEN ( ISNUMERIC( A.EntraceNO ) = 1 ) THEN RIGHT( SPACE(255) + A.EntraceNO, 3) ELSE A.EntraceNO END AS EntraceNO, A.DateApply, CASE...more >>

Hi, In short: To cover a bug in "Visual Studio Team System for Database Professionals" I have to specify the default value for a timestamp column. How can I specify that? Explanation: I am using Visual Studio Team System for Database Professionals. I guess there is a bug there and it g...more >>

I'm a confused about something. I have a lot of objects that give indications as to whether a (2000) server is under memory pressure, such as Available Bytes, Page Life Expectancy, Buffer Cache Hit Ratio and various other Page objects. But what I'm confused about is how I distinguish O/S m...more >>

I am reporting against a SQL 2000 database. On one report I want to change the datetime field. If the datetime stamp is after 5pm (17:00:00) then I want to increment the date by one and set the time to 8am (08:00:00). I only want to do this for reporting purposes, not change the actual dateti...more >>

Hi All: I need to make my database unavailable when i am loading the db. This happens daily and the db is not connected to any online app, to make the app unavailable. It is queried thru an Access and hence available all the time. The load happens daily for an hour in the morning. Is there a...more >>

My client sent me 234 tables all named POS_xxx They are the same file just daily snapshots. I need to merge all 234 files. What is the easiest way to do this? I don't want to have to type out insert into new_table select * from pos_1 etc etc Thank you, CJ ...more >>

hello I only started with SQL and databases, so I realize I'm probably missing something very obvious and I'd appreciate if you could help me understand it better. 1) I understand the HUGE benefits from using primary key as a unique identifier of each row, but when looking at definiti...more >>

I have an existing stored proc that returns 3 result sets from a single invocation. I prefer not to modify the routine or repeat the logic somewhere else. However, SQL Reporting Services can only use the first resultset from a returned dataset. What I would really like to do is encapsulate ...more >>

I have a field in my select statement, and if the value is for example 1, then I want Field2 to be added to the select, whereas if the value is 2, then I want Field3 to be added. Here is an example in pseudo code : Select Field1 as 'NewField' if Field3 = 1 else Select Field2 as 'NewField'...more >>

In 2 K you have the option to suppress system objects...it's in the connection properties Where can I do the in SQL 5 K Anyone know? Thanks-in-advance Bob Sweeney ...more >>

Hello folks! I have a few stored procedures. Some of them select data from a database which is NOT the db that contains the sp. So if I'm in MyDatabase and trying to get authors from the pubs db I'd write the sp as Create proc GetAuthors As Select * from pubs.dbo.Authors --(i.e., ...more >>

Hi, If I do a dbbc checkDB am 100% guaranteed that the database has not been corrupted (or is in the process of being corrupted etc). The idea is that I run dbcc checkDB then if there are any warnings etc then I can always restore the previous day's backups. This is in a worst case scen...more >>

I observed some odd behaviour in a VIEW this morning which I have never seen before. Can anyone explain why this is happening? (Using SQL2K). Thanks wBob USE tempdb GO CREATE TABLE dbo.temp1 ( id INT PRIMARY KEY, test_value VARCHAR(10) NOT NULL ) CREATE TABLE dbo.temp2 ( id INT PRI...more >>

I have a database which has many table with 'updatedon' field of datetime type. These are updated on the client side. I want then to be updated on the server through triggers. And possibly with only one procedure called by all the table. 1> How can I dynamically find out the table contains upd...more >>

how can i get the description for an error wuth given sql_message_id ? i found that master.dbo.sysmessages give somr of them but not all on a maintainance failure with error 22029 couldnt be find on that table - any idea where? thnaks i nadvance peleg...more >>

We are planning to use a hash or checksum calculation to compare against a previous calculation to determine if the data has changed in our database. Do you know what types of algorithms are available to us on MS SQL Server (2000 and 2005)? We are looking at either MD5 or maybe CRC32. Thank...more >>