This is sort of a follow up question on an earlier question I had. Suppose I have these rows: 3/4/2004 17:49:00 3/4/2004 17:49:00 3/4/2004 17:49:00 3/4/2004 17:49:00 -- Kind regards, Stijn Verrept....more >>

Hi Sql gurus, I want to main maintain stock availability using a view, e.g : Create View Stock_Availability As Select Agency,SparePart, QtyIn = (Select ISNULL(SUM(Qty),0) From T59Invd T59 Where T59.Agency=M64.Agency and T59.SparePart = M64.SparePart), QtyOut ...more >>

I have a stored procedure called spU_ZipUpdate: CREATE PROCEDURE dbo.spU_ZipUpdate AS UPDATE tblLinkInfo SET LL_Zip = LEFT(L_Zip,5) GO I have tried to add some statement to make it as follows: CREATE PROCEDURE dbo.spU_ZipUpdate AS UPDATE tblLinkInfo SET LL_Zip = LEFT(L_Zip,5) GO UP...more >>

I want to import (INSERT INTO) from a table A into another B. B has a Counter_ID col that has a certain value which has to be a unique integer. How can I 1. get the value (probably a MAX) and then add 1 for every row I import from A? Thanx? Jakob ...more >>

Hello - I am new to triggers and have a few basic questions I'm hoping you can help with. For reference I am using SQL Server 2000. I need to create a trigger that will force one column in a updated row to a hard coded value if another column in that same row is equal to a different hard code...more >>

hi expertss I had some problem in writing sql stored procedure.I know the following stroed procedure "exec MASTER..xp_cmdshell'dtsrun /sbiztune- 3 /usa /padmin /npkg1'" it is working fine.but I don't want to hardcode database name,username,password..can u help me how to read that in ...more >>

hi guys, I had some proble in writing sql stored procedure.I know the process how to call the dts package in sql server stored procedure. it is ass follows: "exec MASTER..xp_cmdshell'dtsrun /sbiztune- 3 /usa /padmin /npkg1'" it is working fine.but if i hardcoded the database,user,pas...more >>

I'm running a query that has a couple of subqueries that are using OPENQUERY to retrieve data from another server. The query that is being run is a SELECT query, but I'm receiving the following error and not sure how to get around this. Server: Msg 7320, Level 16, State 2, Line 1 Could ...more >>

We have a sql table which is a se2quential log file with a structure like this: RTEDateTime - DateTime row was entered PrimaryId - User ID Description - Description of Activity What is the simplest way to find the most recent row for each user ID? (ie returning RTEDateTime,PrimaryID,Descri...more >>

After installing MS SQL Server, I would like to be able to create the database, user accounts and the tables without user interaction. So, I thought of creating a batch file with the SQL commands that will run after installation of MS SQL Server but I didn’t find any helpful documentations....more >>

When I run "select @@IDENTITY from MyTable", it will return the number of rows of MyTable. And each row will be the number of last inserted value. Why it produces so many rows, instead of one row? Please advise. Thanks!! ...more >>

In SQL Query Analyzer, file | open can open the SQL file in the file dialog box, and then we can execute the statement. What if I want to do command line to execute the sql statement? But we need to put the SQL file in a specific location? please advise. Thanks!! ...more >>

I have the following query that I'm trying to get SUM values. In the SELECT clause I can calculate horizontally. This works fine. ItemPrice.ProPrice * dbo.SaleGroups.Quantity AS LineTotal But when I try to get a SUM total of LineTotal, it says there's no such column How do I get a grand total I...more >>

I have a nvarchar column set for 15 characters called ZipCode and want to use the first five characters of that data to Update another nvarchar column set for 5 characters. The below statement does not work - what am I doing wrong? UPDATE Zip5 SET LL_Zip = L_Zip (Len,5)...more >>

Can someone please help me to understand how to run a stored procedure from C# code? My stored procedure has three select stat?ments: select <root> select somedata for xml auto select </root> Thanks ...more >>

Hi: I'm writing a VB.NET application and I have several date fields that may be null. I'm getting a "System.data.sqltypes.sqltypeexception sql date time overflow" when I call stored procedures with data loaded as follows: if (condition) datevalue1 = Nothing when I pass this - data...more >>

TableA (A list of clubs) has the following Fields: ClubID Int - PK Club Name varchar Members int ....... TableB (Registrations): RegID int (identity) PK Event varchar ClubID int ....... I want to get a count of how many clubs are registered for at least one event and I want the S...more >>

Hi, My tables first 4 columns are standart: TableA -------------------------- R_ID int [defalut value: udf_SET_RID] W_ID int identity B_ID int D_ID int M varchar(10) N int .... TableB -------------------------- R_ID int [defalut value: u...more >>

Hi I hope someone can help me solve my problem. I trying to extract data that has today's ex_date. declare @c_dt char(12) select @c_dt = (SELECT CONVERT(char(12), GETDATE(), 10)) print @c_dt go declare @c_dt char(12) select (CONVERT(char(12), s1.ex_dt,10)) as date, s1.ex_dt, s1.na...more >>

I want to insert records into a table named CustomerFeedback from each entry in another table named FeedbackQuestions and also insert a constant variable that is passed to the stored proc and I am getting errors. Below is my code for the procedure. Can anyone help? Thanks. USE [Marshall] G...more >>

I have a field in a database that contains dates in the following format: 20040205 It is a text field and I need to convert it to a date so I can compare and control a dataset. Any suggestions would be greatly appreciated... I have tried to use GetDate(20040205), but it just gives me an ...more >>

I have recently been having a problem with several stored procedures. They will work perfectly for a while and then for no discernible reason, they will fail when called from the ASP interface. They always throw the same error "Undeclared tag ID 4 is used in a FOR XML EXPLICIT query". ...more >>

Hi all, Is there a built-in stored procedure that returns a datatype name from xtype and vice versa? Thanks, Joe ...more >>

I have a stored procedure that makes use of temp tables and it runs fine in the query analyzer. It returns a set of records. However when I call the stored procedure from ASP the recordset is not open. Simple selects work fine but when I make use of a temp table it breaks down. Any Ideas?...more >>

OK, I am lost on this one. I have a two fields with dates in them, both are of the type datetime. In several records the dates in these two fields are the same, but when I use the DATEDIFF function to give me the days between either of those fields and todays date, I get different answers. ...more >>

I have 2 temp tables that I perform an INNER join with in order to get similar records. But I am going to a "% RATE" Calculation so I also need to get a distinct count of the denominator, i.e. the distinct count of records from the 2nd temp table. How would I do this? My query as follows y...more >>

I have a DLL I created that is called when an insert trigger occurs on a particular table. I call the dll through T-SQL and all works fine. This DLL uses a 3rd party connection API to send data to an AS400 QUEUE. My issue is each time a trigger action calls the DLL a new connection is made ...more >>

Oops: create view MyView as select col1+col2+col3 AS subtotal1 , col5+col6 AS subtotal2 , (subtotal 1+subtotal2) * taxrate AS grandtotal from MyTable -- Tom --------------------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Ser...more >>

In MS Access, I could create an expression and use its results in another expression very easily but I can't figure out how to do this when I am creating a View in SqlServer2000. For example, col1+col2+col3=subtotal1, col5+col6=subtotal2, (subtotal 1+subtotal2) * taxrate=grandtotal Could so...more >>

Hi All, I needed help either with a query or stored procedure. I have the following situation. I need to write a procedure which will run every week and will populate data as given below. DDLs: CREATE TABLE [dbo].[Table1] ( [ClaimNumber] [varchar] (16) NOT NULL , [SupplierID...more >>

During the conversion of an MS Access database into a SQL Server 2K I encountered the following problems : 1. The DTS was not able to read a date value which held a year 4000 such like 4000-1-1. Obviously there must be a maximum for the maximum year in a datestamp field. How can I solve this ?...more >>

Throughout the stored procedure, items are added and deleted from a table variable. Near the end, the procedure should delete duplicates from the table variable. When using the method below, I get the error: Server: Msg 137, Level 15, State 2, Line 19 Must declare the variable '@Mods'. ...more >>

hi is there anyway to display a numeric value in currency format using sql.. example 123.4556 should display $123.46 thanks....more >>

I am trying to find out if there is any difference (in terms of performance) between a partial update or a full update of a row in sql server. Currently we have store procedures that take care of updates for a table (partial or full). Is it worth to have that logic in the data access layer for pa...more >>

I have a Stored procedure where I am updating the table on a remote server(using linked server) with BEGIN TRAN,it roll backs perfectly for the remote table also if do rollback explicitly,so do need a BEGIN DISTRIBUTED TRANSACTION for this kind of situation?? BEGIN TRAN UPDATE T SET C...more >>

I have the following table with data: I would like to get results like this: User - StartTimeVisit - EndTimeVisit - Website - Hits So when a user visits www.google.com for 10 minutes then www.VBO.be for 4 minutes and then www.google.com again for 5 minutes it wil display something like: ...more >>

I need help on how to approach this I have a table that contains trip information. ---table trip_information---- trip_start_dat trip_end_dat dest_start_dat dest_end_dat I have a 2nd table that contain holidays ---table holidays_information---- holiday_dat I have a 3rd table that contai...more >>

Hi group, I am having a problem with the agent: it appears that any job not scheduled before a certain date does not get to run, and there is no error message either. Observed first time last night. During the night there is a scheduled job which starts a few jobs which do not have any sch...more >>

Hi, We have to solve an issue where we have several productive databases which should be updated frequently (with human help), let's say all two years. As this can be (more or less easily) done using change management for the db schema it's seems to be little more tricky when it comes to the a...more >>

I have 2 tables with columns: ID,countryID and date. I would like to delete all rows in table T1 which id and country id exists also in table T2 after specific date. This works (I think) but is there any other query with better perforamnce, some kind of join? DELETE * FROM T1 WHERE T1.ID ...more >>

How would I accomplish this? I'd like to have the SQL server send an email each time a NEW record is inserted into a particular table, where the value of a particular field equals "No". Thanks!...more >>

To all: Is there a way to run a sql script (File.sql) located on a drive in query analyzer? Kind of sqlplus > @file.sql in Oracle. Thanks, Tunji ...more >>

Hi all Is there a way that I can get the Enabled status of a trigger in my database? I have looked at the sysobjects table and there is a status column, but it's just a number that is not necessarily the same for each trigger. I am disabling and enabling triggers for trouble-shooting purpos...more >>

Is there any way to declare a variable so that I can set the value in one trigger (that sets a status to indicate a duplicate record) and have it read into a second trigger (that writes that status to an audit trail?) I've tried a couple of different ideas to get this status to write correctly and ...more >>

Hi All, I tried to use T-sql command alter table alter colum column_name to change a column datatype (small int to int) It returned an error that some other objects using that column, I used the enterprise manager it's OK is there any tricks to alter a column without using Enterprise Manager ?...more >>

Hi, I have an SP on SQL7.0 which works correctly and does the following: CREATE TABLE #tmp (Phone_Number varchar(15), Post_Code_Area varchar(3)) INSERT INTO #tmp (Phone_Number, Post_Code_Area) SELECT PhoneNo, Post_Code_Area FROM MyTable CREATE INDEX [IDX_PN] ON [#tmp]([PHONE_NUMBER]) ON [PR...more >>

Hi anybody can help i did write some query's in ms access now i need to use them in MS SQL but this doesn't work: SELECT Personeel_geg.Personeel_number, Personeel_geg.Personeel_curr_salaris, Personeel_geg.Personeel_datum_in_dienst, (IIf(Year ([Personeel_geg].[Personeel_datum_in_dienst])= ...more >>

Hello, I am attempting to create a trigger on a table that inserts records in several other tables once the primary table has a new record inserted into it. For Example: If someone creates a new job, I want the database to automatically create a record in another table with the new jo...more >>

How I Can get Rows From 20 th Row (Unknown) To 30 th Row (Unknown) in a 1000nd (Unknown) Rows of a Query or a Table ex: Col1 Col2 Col3 Col4 Row1 Row2 Row3 - - RowX How I Can Get rows from RowX to RowY There Is --- No Serial No Here --- RowY - ...more >>

I know this is a bit off-topic for this forum. However, many of you = also work out of your homes so I suspect you'll have some good = suggestions for me! -- Does anyone have a good recommendation for a home office phone system? = I'm about to toss my Siemens Gigaset out of the window as ...more >>

Hi All, I have been struggling with the following query. I got help earlier by you and was trying to follow the same logic but without any luck. I have two tables. DDLs: CREATE TABLE [dbo].[Table1] ( [SARNO] [char] (6) NOT NULL , [VENDORCDE] [char] (5) NOT NULL , [LAB...more >>

Hello Sql Gurus, I have small problem about the following issue. create table sample1 ( ssn int not null primary key, score1 int, score2 int, score3 int ) Insert into sample1 values (1,10,20,30) Insert into sample1 values (2,10,20,NULL) Insert into sample1 values (3,10,NULL,NULL) I...more >>

Hi below is my query.. SELECT trans_detail.account_number, trans_detail.cost_center_code, trans_detail.fund_number, budget_category.category, budget_category.sub_code, fund.fund_number, SUM(CASE WHEN trans_detail.type_entry BETWEEN '11' AND '14' THEN trans_detail.trans_amount_gl ELSE ...more >>

Could someone tell me the command, or utility to use to import a tab-delimited csv file into a table. We want to start dumping our security event logs into a database. I have the script that dumps the logs but now I need to put the dumped file into a SQL database. Thanks!...more >>

I have posted this question here some days ago, and got 2 answers. The problem remains. How can I configure a database so when I generate an SQL script for a UDF (User-Defined function), it sets ANSI_NULS and QUOTED_IDENTIFIER to ON before calling CREATE FUNCTION. I tried setting these prop...more >>

I have four tables where, table1 is the parent to table2, table3 and table4... BUT!! (I didn't create this structure, please forgive me) table2 has a relationship with table3 and table4 on the z_id, but table2 doesn't have a parent/primary field to relate to. The only indication that there is ...more >>

Hello I posted a similar message earlier, but it has not appeared in the newsgroup. This NG may be having problems --Anyway- I have several lookup tables that have an ID column and a description column. I want to be able to create an SPROC to update these values. I was hoping to do something li...more >>

Hello everyone, So,this is my problem I have a table of the following form ZIP City Market Address 85069 Ingolstadt E-Neukauf Whites 15 85051 Ingolstadt E-Neukauf Seconds 23 85051 Ingolstadt Muller Marias 42 85049 Ingolstadt E-Center Johans 33 85049 I...more >>

Hi, i configured in sql server a transactional replication (pushabo). the replication starts automatically once every day. it is also possible with the enterprise manager to start the replication (synchronise) manually. I am looking for a solution, to start the replication from a webap...more >>

if a field has a value of .015 and is made into 1.5, how can just the value '1' be casted as '1%' ? so only the number in front of the decimal is used? tia, dk...more >>

How to Query for Outputting the record set from 50th row to 100 th row (X th Row To Y th Row) in a 1000 (Z th) rows of table/Or Query Ex: I Have one table with CityID ,CityName ,PinCode, Type 1 Bangalore 3435343 Mega I need only Mega(TYPE) citys that has to be from Xth row ...more >>

I am trying to debug a stored procedure using the debug feature in QA. When I enter required parameters and click execute, the code executes all the way and does not give me the opportunity to step through the code or set a breakpoint. Is there something I am doing wrong? Thanks Emma ...more >>

Hello I have several lookup tables that have the same structure. ID column and Description I would like to create a SP that I can pass in the table name and then return the values so they may be edited I was hoping this would work, but it won' @lkpTable varchar(30 A SELECT COLUMN(...more >>

Hi, We have SQL server 2000 sp3a running on a windows 2000 server. I wish to run dts.exe to execute a DTS package, and am using xp_cmdshell to do this within a stored proc. It works for me (in Sysadmins role), but not for non-sysadmins. I have set up the proxy account in the SQL server ag...more >>

Hey guys I was wondering if someone can help me with my asp code. The following is how the results of a stored procedure look like when displayed in a html table. This information has to be used for reporting so I need to do some more work to it. I need the Classification only to appear once and...more >>

Hi, I need to update a single field in a table by converting the DBV (currency) field value into a local currency field using the Exchange Rates table and the joined fields Country and Month to find a month rate. My query is below and it generates a missing operator syntax error. It ...more >>

Hi I have a small datetime column called ctime and would like to be able to select anything from todays date only so current date when ever it is run does anyone know how to do this nothing I have tried has worked :( thanks for any help Jerry ...more >>

I create my database table with a text field of nvarchar(), added some japanese kanji characters and so on. Everything works great, I can insert kanji and retrieve kanji and display them just fine from my c# application, however if I try to search for kanji using a WHERE = '' or a WHERE like '' ...more >>

Hello, We have recently changed the user id of the SQL Server Agent, as the previous user was local rather than domain. The user has been set up as an administrator. We are getting the following error "[364] The Messenger service has not been started - NetSend notifications will not be...more >>

are any pre-built auditing solutions available within the sql-server community ? microsoft or shareware or 3rd parties ... for example , a collection of stored-procedures one could add to a database and then make use of within an asp.net application ? (solution probably needs to include som...more >>

Hello! Does anybody know a way to get the SPID of the process that is invoking a job ? I am trying to process alert messages. I want to send them per smtpmail, so I need the SPID to get the correct message in the errorlog. Kind regards Manfred...more >>