Hi, I finish the log shipping wizard without problem and the log backups are created and copied to destination but they are not restored to destination db. I use stand-by mode and make changes to source db but the changes are not restored to destination. When I manually restore the log backups, ...more >>

I have an app to download data from mainframe daily. I am looking for a method to avoid user run the stored procedure 2 times per day. I am thinking to have a SystemConfigure table to store Last_update_Date field. If the data equal today then do nothing, if less then download from mainfra...more >>

Hello, I have the following two tables tb_ProductIssue issueID productID statusID Name ============================================ 1 10 1 Error 256 2 12 2 Can't install 3 10 3 Constant Reboot tb_status statusID status_name 1 Open 2 Closed 3 Pending I want t...more >>

Sorry that's the best subject title I could come up with, I'm not sure what I'm trying to do would be called (except for maybe crazy & stupid). I'm just wondering if someone has the time to comment on this. In general, I have two tables. The first table holds a list of items that belong to a...more >>

I've read that it can be done, but I am just a beginner and I need it in simple steps. What I need to do is recalculate a MS Project file when a particular column changes. I know how to (I think) create a trigger to start it. I read that I need to use "Remote Stored Procedure" and "Open...more >>

Hi, My database has around 2000 huge stored procedures and multiple triggers etc. I need to find which other stored procedures are calling a stored proc name AA (for e.g.) Is there any SQL query or a system command to find this out. I believe sysdepends does not show all the places where...more >>

with msde 2000: I can see stored prodcedures for editing and also views, how can see a function to edit it ? ...more >>

(VB.NET) I created a dataset, added a table to it, and added a column of type = DATE: dtbMeasures.Columns.Add("MeasureDate", GetType(Date)) I then add the datatable to a dataset dsMeasures.Tables.Add(dtbMeasures) And pass the XML returned from the dsMeasures.GetXML() function to a = sto...more >>

I have a select that reads about 10-20 records each time and displays it on my screen. I now need to get one field from the master record. Is it normally better to do 2 reads - 1 for the 20 records and then another for the one field I need or to do a Join. Both work fine. But for the joi...more >>

I have a query that needs to get data out of 15 different tables. I've created a single select query that joins all of these tables and it works fine but it obviously returns dozens or even hundreds of rows. Another way to do this would be to make multiple queries which would return a fraction o...more >>

I am writing a very simple polling application and I am trying to return the results in which it will count the number of responses for a given survey. What I wrote was: SELECT SurveyChoice.Choice, Count(SurveyVote.FK_SurveyChoiceID) AS Quantity FROM SurveyVote RIGHT JOIN SurveyChoice ON Su...more >>

Can i get a query to list only failed SQL Agent jobs ? ...more >>

I need to update one temporary table with the lastbuyprice value. The criteria for update is the matching of ItemID. The ItemID stores inside the temporary table is something like this: ItemIdwithnorevision Rev A123 B B123 NULL C123 ...more >>

I am trying to call an SP within another SP, but the result being returned from the inner SP is Null, whereas I should expect it to return a 1 or 0... Here is the code: ALTER Proc Returns_RecordPostClean @SerialNo int, @LineID int, @PostCleanDate smalldatetime, @PostCleanResult ...more >>

I have a stored procedure which will output a table. How can I use a sql statement to get the result of the sp? Thanks a lot ...more >>

Is there some elegant way for an SP with a parameter that may be populated or not to exist without an IF...THEN construct? For example, this is what I started with CREATE PROCEDURE (omitted from now on) @paramOne Int SELECT Columns FROM Tables WHERE Table.ColumnOne = @paramOne but t...more >>

hello what is the function that calculate the number of days in a given month ? 10x ...more >>

Hi, Quick question, I have a table ("log") which stores transactions. It has two fields, User and ID. I want to update table "booking" where rows in "log" match those in "booking". For example, log has entries ID User ------------- 1, USERa 2, USERb 2, USERa 3, USER...more >>

Hi, I tried to rename one of the stored procedures by using sp_rename 'Hello', 'World' after I execute the above command The stored procedure name itself changed to 'World' but when I edit the stored procedure in Query Analyzer, the text is still Alter Proecedure Hello as .... .... I...more >>

How to create view to search records between 1:00pm yesterday an 7:00am today?...more >>

Hi , i need some help about triggers , updated columns and cursor for an updated table i wrote the code shown below , doesnt work . I couldnt find my error , it blocks sql server ------------- create trigger ABC_Update on abc for update as if update(colA) begin -- declarations declar...more >>

For the following query I am trying to visualize how the where clause causes the rankings to occur. WHERE s.totalsales <= t.totalsales can someone please explain this? SELECT s.state, st.stor_name,s.totalsales,Rank=COUNT(*) FROM (SELECT t.state, t.stor_id, SUM(s.qty) AS TotalSales FR...more >>

Hi All, I have the table structure as follows ->Deliveries (table) |__OrderID (Guid) |__DeliverDate (DateTime) (ALLOW NULLS) Index is on DeliverDate (ASC) 60000 records look at the query plan for the following and is using the proper index: SELECT * FROM Deliverie...more >>

I did create #tblTemp on sql but can not use it with "outside" bcp routine from command line: Error = [Microsoft][ODBC SQL ...][SQL Server]Invalid object name '##tblTemp'. Any idea why? -- gok ...more >>

Why would one want to put a unique index on a column versus a unique constraint? Thanks in advance. Mark ...more >>

Pretend the examples below are tables... With fields (F1, F2, F3,...) I want to take Table1 and make it like Table2 Anyone wanna venture the SQL code? Thanks, my skills just aren't far enough along to think it out. Travis FROM THIS [TABLE1] F1 F2 12345 VA7X 12345...more >>

What's the best book in market for using Microsoft DTS and SQL Programming ? I would like to advance my skill set and become excellent with this product. Please advise ?...more >>

Is there an easy way to search the contents of stored procedures? I am new to the databse that I am working with and am looking for a specific table name and want to know which stored procedures deal with that table. I can open them all up and search through them, but with a huge number of ...more >>

Can we query sql server to see if a user is logged in? Thanks!...more >>

using copy * into <temp_table> will strip all triggers, permissions and so on. Is there a way to make a table copy on sql side with exact structure replica wout data? -- gok...more >>

I'm running a DTS daily that needs to create a file with a unique name, as yesterday's date. (Ex: 20050223) I figured out how to get the date part of it: CONVERT(char(32),DATEADD(DAY,-1,GETDATE()),112) = 20050222 but I can't seem to get it to create a table with that unique name each day. ...more >>

Hi, Does somebody knows an alternative for the function last() in access. I've a lot queries which uses last, but there is no alternative in sql server. I used min/max but that did not gave me the right results. I need to know the last row in a 1-to-many table situation. Is there somethin...more >>

Is there a performance bonus between these 2 sql statements? Using JOIN: SELECT * FROM A, B WHERE A.ID = B.ID; Using nested SELECT: SELECT * FROM A WHERE A.ID = (SELECT ID FROM B); Thanks ...more >>

I have a field stored as NTEXT(16). I need to replace some of the content in this field. I know the replace function doesnt work. I see there is UPDATETEXT - is this the ONLY way to do it ?? 'cos it looks quite complicated? thanks, Justin ...more >>

I created an instead of insert trigger which checks to see if the "key" of the inserted record already exists. If it does, it copies the existing record to another table, deletes it and inserts the new one. The problem I get is when the insert statement coming into the trigger looks like the one ...more >>

... I know i have asked this before and the response i got is run sp_help_job.. Please bear with me as Im not a SQL guru . I would like to run a script in QA and the output should give me the list of jobs that are currently running. I have around 100 SQL Agent jobs on a server and instead of ref...more >>

Is it possible to obtain list of handles returned by sp_xml_preparedocument? What I want is to manually release all xml documents allocated by sp_xml_preparedocument without server restart. At some point server runs out of memory. We might have bugs in our code(not calling sp_xml_removed...more >>

I am excited writing my first stored procedure. I created small stored procedure to return total sales of given salesman and it runs in query analyzer. What I used in query analyzer is: DECLARE @TotalSales money EXEC CalculateSales @TotalSales output,'ADAM' select @TotalSales as SalesBySal...more >>

Hello, all. We've got a table that holds the begin and end date for allowing people into a voting app. Here's the DDL, CREATE TABLE [tblVotingPeriod] ( [pk] [tinyint] IDENTITY (1, 1) NOT NULL , [beginVote] [smalldatetime] NULL CONSTRAINT [DF_tblVotingPeriod_beginVote] DEFAULT (getdate()),...more >>

Hi all, I have a table with 6 million rows which takes up about 2GB of memory on hard disk. So we have decided to clean this table up. We have decided to delete all records that have syncstamp and logstamp field values less than the value correspoing '20040131'. This will probably delete 5...more >>

Hi there, I'm trying to delete some rows from a table using an xml document (in memory) and OpenXML in SQL Server. I have the following code, but it will only delete the first record. Any ideas? CREATE PROCEDURE [dbo].[CompanyBusinessUnit_DeleteCompanyBusinessUnit_Delete] @CompanyHeirarch...more >>

I have a table that looks like: CREATE TABLE [dbo].[UserAgenda] ( [StartTime] [datetime] NOT NULL , [EndTime] [datetime] NOT NULL , ) ON [PRIMARY] GO and I would like to find a time gap for the amount of time I am looking for. For example, I want the next available hour in the list or ...more >>

I am looking to write a SQL script that will show me for each table, in all the databases on a server, which users / groups have access. In addition, I want to break the access into whether it is SELECT, UPDATE, INSERT or DELETE permissions. Is this possible? ...more >>

update mytable set content = replace(content,char(13),null) I thought that would get rid of embedded carriage returns in my nvarchar column. The carriage-returns are gone, true. :-( Timo ...more >>

I am executing a stored procedure from a VB application, and would like to return "intermediate" results from the stored procedure. Something along the lines of: --begin stored procedure SELECT . . . --display "done with select" in VB app UPDATE . . . --display "done with update" in VB ...more >>

Hi Folks, there are a lot of messages regarding COLUMN_UPDATED() calls in trigger here. After a few hours I figured out how to automate the process. Because the group helped me a lot I post the solution for further reference: CREATE FUNCTION dbo.FN_COLUMNUPDATED (@COLUMNS_UPDATED Binary(8...more >>

I have a software system that needs to give the user to develop basically an if statement. So IF PERSON_AGE > 15 AND MONTHLY_PURCHASE_AMT > 1000 THEN GIVE THEM 1500 POINTS. Here is the deal. I need to do it both during a back end process and dynamically when a person comes to the counter. T...more >>

Hello all, I am looking for more ideas on bullet proof style Data Bases and or applications for my site. I possibly could do some barter trading with some advertizing. I need to following for the application and it should be ablet o cross reference with other applications too. User Name...more >>

Hi, I create a temporary table. for example: create table #t1 (ID_T1 integer) Next I would like to detect If the table already exists: exec sp_table #t1 if @@ROWCOUNT > 0 --Table #t1 exists else --Table #t1 doesn't exist But the sp_table stored proc.works only for non-temporar...more >>

I need to bind only 10 search results to a web page. So I need only select 10 records at a time from a table whose keys are not sequential. I know we could pick, say, the 11th to 20th records by using a cursor to step through the selected results. However, is there a way to do the same thing w...more >>

This statement won't parse, it says that "The table Crd is ambiguous". I tried several variations, but I can't get it to work. I want to update the Crd table from a different field in the same table with a period shift (the period shift has been simplified for this example). update Crd set ...more >>

Hi guys, I have a table the contains a "text" field, I create a trigger on it to copy the inserted record to anther indenticial table The trigger code is: CREATE TRIGGER Newevent ON dbo.CAL AFTER INSERT AS Declare @rec VARCHAR(50) select top 1 @REC = RECID from inserted insert ...more >>

Is there a way I can log events to a table in my database that will write a log whenever a script is run on the database?...more >>

Why does this syntax work for bit but not for smallint ? -- This works OK - Setting = BIT CASE (dbo.tblAssessment.Setting) WHEN 1 THEN 'Internal' WHEN 0 THEN 'External' END AS Setting, -- This fails - error converting value 'N/A' to column of datatype smallint -- Credit = SMALLINT ...more >>

how to select rows in a table where either one of the column in the table is having a null value...more >>

Hi all, I have a single table with one field: CREATE TABLE [dbo].[class_number] ( [clientpartnercode] [char] (4) COLLATE Latin1_General_BIN NOT NULL ) ON [PRIMARY] GO Which contains values as: 014, 015, etc. However there are also values such: 311, 281 etc. I would like to synchronise...more >>

Hi All, Just a quick question but is it at all possible to assign the permissions of a stored procedure to a sp_executesql statement, dont really want to go down the road of loading the sp_executesql into a temp table or setting permissions on the underlying tables. Any advice would be g...more >>

Hi When trying bcp with the following query : bcp sun..testtab out dmnHeaderStatus_%_REAL_YYYYMD%.Dat /S sunTEST24 /T -f test2.fmt -w. I want to save my data in unicode format so i am using -w and also using a format file test2.fmt. When executing this i am getting -w overwrites -f and ...more >>

Hi, Is there any query in SQL Server2000 that will export all the tables from Current Database to MS Access database with all data? Thanks in advance Madhivanan ...more >>

Hi We have a application on a clients site that crashes after a while giving an error message about loosing SQL connection. This is the only client site this is happening on so we would asume that it has to do with the network and not our application. Do anyone know of a database connect...more >>

From what I've read in the docs, ado.net currently supports opening sql server ce tables in table-direct mode and performing Seek operations on them (using SqlCeDataReader), but not on the full-blown sql server. Is this (will this be) still true with ado.net 2.0 & sql server 2005? ...more >>

I am using Visual Studio .NET 2003 with SQL Server 2000. I am trying to insert the date and time into a SQL database by using hour(now). I am having a hard time trying to figure out which datatype to use in SQL to store this value. I have tried using datetime, char, nchar, text and nothing see...more >>

I want to sum the records in a table (u) which have 'P.M.' in a field value and test against a fixed limit something to the effect of : case when SUM(substring(u.sTime,1,4)='P.M.') <= 64 Then .... except it should work :) any suggestions would be appreciated ...more >>

Hi folks I am connecting to a SQL Server 2000 (SP3) from a client Enterprise Manager. It connects to it, well. But if I try to open an existing DTS package (located under Local packages) it throws an error "DTS Designer Error The specified module could not be found" and it closes. Same error...more >>