Hi All, I wanted to query the xml returned by the eventdata() function in a ddl trigger to view it in result set. I made that code but it returned null, any help please? create trigger DatabaseEvents on database for ddl_database_level_events as --select eventdata().value('(/EVENT_INST...more >>

Hi, I have a table containing 7 fields, Mon, Tues, Wed,Thur,Frid,Sat and Sub, all bit types. I need to workout the next run date based on the above fields having a value of 1, example: Today is Wed 24/08/2005 Mon =1, Tue =0, Wed=0, Thurs=0, Frid=0, Sat=0 and Sunday=0 What is the simpl...more >>

Is there anyway to accomplish automating backups on a small network that has only sqlExpress. I know there is no agent and I can't schedule backup jobs but can I exec a stored proc from a batch file that I can fire from the windows scheduled tasks? Thanks in advance. Bob. ...more >>

I have the following table: [code] CREATE TABLE [COUNTS] ( [sDatabase] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Universe] [int] NULL , [VoiceMessages] [int] NULL , [AMMessages] [int] NULL , [Disconnects] [int] NULL ) ON [PRIMARY] GO INSERT [COUNTS] (sDatabase, ...more >>

Hi all, I'm looking for equivalent to LIMIT statement which is in mysql. -quote- The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be non-negative integer constants (except when using prepa...more >>

I am trying to develop a stored procedure and UDF which will create a holding table to allow the users of my application to store their timesheets for the current week and was looking for some ideas as to how to best put it together. The stored procedure will need to: 1. Take as input the ...more >>

Hi All If I have a statement like Print @sres I cannot see that in output. I can only see select statement output only .. select id as row_test_number from MKdata_set where id = @iTestRowId id shows up fine. What can be the problem. Please let me know. I am using SQL Query Analyze...more >>

Hi everyone, Quick and easy question: Why is it that in Query Analyzer, a float datatype field will show trailing 0's and trailing 9's but in Enterprise Manager (same table same field), it will be formatted correctly. For example, in Query Analyzer, it will show 58.399999999999 and in Ente...more >>

HELLO "A severe error occurred on the current command. The results, if any, should be discarded" Have anyone solved or worked around this error message? SQLServer2K - SqlClient .Net 2003...more >>

Hi All In my stored procedure I have to compare 2 strings. The strings are like this... I am using SQL Server 2005. String1: aa|bb|cc|dd String2: aa|bb|cc|xx How can I write a function in my stored procedure which takes these two strings as arguments, compares them token by token ( | ...more >>

Ladies/Gentlemen I currently use CAST(DATEDIFF(DAY,0,getDate()) AS DATETIME) to get the Date with a 00:00:00.000 Time, but I need to know the best way to set the dates time portion to 23:59:59.997. Mark Moss ...more >>

The Build Query button on the General tab of the Execute SQL Task stays disabled if I set the Connection Type to ADO.NET (even if I select a valid connection). If the Connection Type is OLE DB then the Build Query button is enabled again. Why? -- Michael Hockstein...more >>

As a newcomer, I have a VB.NET application which resembles a form with approximately 130 checkboxes (as options) I have two tables to update once the Submit button is clicked. One table holds a form generated number (primary key) for the request, the EmployeeID (from a dropdown list) and the ...more >>

Hi, I have an application that is in production and connects to a SQLServer 2005 Express database. On one of the customer's computer, when opening the connection, an exception is raised: The type initializer for 'System.Data.SqlClient.TdsParser' threw an exception. System.Data.SqlCli...more >>

I've built a trigger in SQL2000 as follows it will work with either of the update statements but not both together. What am I doing wrong?..Thanks. if exists (select * from inserted) begin if exists (select * from deleted) begin if (Select ShprNo FROM inserted) = ('QRP') And (Select Del...more >>

Hi All I have a table like this... data_set(int seq_id, int set_id, data varchar(50)) data is like this ( There are 2 sets of data in it) 1, 1, 'aaaa' 2,1,'bbbb' 3,1,'cccc' 4,2,'pppp' 5,2,'qqqq' 6,2,'rrrr' For my stored procedure the input is 2 setid'd , I have to proceed in my ...more >>

Hello, Is there a way to replace all occurences of multiple characters in string? For instance, I need to eliminate all occurences of "-", "/", "\". Right now I'm doing it like this: REPLACE(REPLACE(REPLACE(String, '-', ''), '/', ''), '\', ''). It's awkward, especially if I have to replac...more >>

Hello everyone, I have a major design challenge here. I have 2 million records in a table that gets hit alot. I need to create statistics on subsets of the 2 million records. Each subset represents a long event, and I need to show 'up to the minute' stats on each subset. Right now the use of o...more >>

Quick question -- is it possible to (easily) create a report in PDF format from a stored proc? I am currently using VSReport Designer 7 (from componentone) in VB6 and am hoping to find a way to create these same reports directly from SQL Server. Or is there an easier way to do this (through ...more >>

Hello, I am trying to get the max date out of the following query. There could be several records that are the same except for the date_time field. I want to return the max date of these two(or more) records along with the rest. Not sure of how to go about this? select rcompl.*,fastener_datab...more >>

Hi All How do I see value of a variable in a stored procedure. PRINT does not show anything. Only select statement shows the values 3. Just started learning this.... DECLARE refid_cursor CURSOR FOR SELECT id from my.data_set where test_setid = @setid1 OPEN refid_cursor FETCH refid_c...more >>

Hi, I have a linked server set up on an instance of SQL server thats on an active-active cluster and pointing to an instance thats running on the other node of the cluster. When I try to use the four part query the error message I get is Server: Msg 7399, Level 16, State 1, Line 1 OLE ...more >>

in sql server 2000 when you issue a alter table to add new column, can you specifiy the order? meaning i want that column to be between existing columns, not at the last ...more >>

I am using C# to execute a SQL Server 2005 stored procedure and in that stored procedure I have a Try Catch Block. In the Catch block I do some processing then the last thing I do is call RAISERROR so that the exception will be thrown back to my C# client. However, this is not what is happenin...more >>

I am trying to extract data from a DB2 (zos) table where someone has stored packed decimal data into a charactrer column. Anyone have a suggestion?...more >>

I have two tables. The first one, [Products], contains product information about products: ISBN (the primary key), author, page count, list price, etc. The second table, [ProductSetTitles], tells me which product ISBNs are sets, and which title ISBNs belong to each of those sets. This is done...more >>

Each record in my table contains two numeric columns that represent a numeric range from start to finish. The starting number of the next record should be +1 of the finish number from the previous row. There are instances where this isn't the case and there may be a gap of 1 or more. Does anyone...more >>

Hi, I've been trying to view/download the webcasts from these links for weeks and no luck. http://www.microsoft.com/events/series/msdnsqlserver2005.mspx http://msdn.microsoft.com/sql/community/Webcasts/default.aspx When will they be available, again? Whereelse can I view/download them? ...more >>

Howdy SQL Gurus, I am having issue with my sql log. I am running Win2000 Server +SP4 & MS SQL 2000 Server + SP4. I have hourly transaction log backups and nightly sql backup as well. The sql server is using Full Mode. Right now I had set Datafile & Transaction file --> Automatic grow fi...more >>

Hi all, Why is dtproperties table returned in both queries below? Is this not a system table? I know I can filter out that table in results just not sure why it's being returned. SELECT Name as "Table Name" FROM Lerg_Update..sysobjects WHERE xtype ='U' SELECT TABLE_NAME FROM INFOR...more >>

Hi Friends, SQL 2000 How can I have the aggrigatied string in a single statment? What I mean is: I have a table like MID mvalue ============= 1 "abc" 2 "ddee" 3 "zxc" I want to have: "abcddeezxc" as a result in my final query I saw somewhere that youcan do ...more >>

I have heard that joing CHAR to VARCHAR uses more resorces the CHAR to CHAR how true is this?? we have one table that has a CHAR(12) field and the temp table defined has VARCHAR(12). I have heard that you should not do this and i do not but this is not my code and i am trying to tell them that...more >>

Running a profile of a process we have - (SQL Server 2005, Standard) I get the following: EventClass: User Error Message TextData: Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0 I'm some 9000 l...more >>

I am working on SQL Server 2000. I am trying to create a Case Statement where I do something like the following: I want to set up the case statement to check if a value falls within a specific range then sum the unit amount as the type of data it represents. SELECT COMPANY, FISCAL_YEAR, ...more >>

Hi all, I have created an Outlook Add-in which basically populates a custom WinForm with some data retrived from the backend SQL Server 2005 database. The SQL Server is installed on the same computer as the development environment (VS 2005). The add-in code compiles OK and I have created...more >>

Hi, I'm trying to create a stored procedure that will sum a column, then use that value for another calculation that will update the table. This is what I want my table to look like Subject Visit Strength KCalPerWeek TotalKCal Percent of Total KCa ------------------------...more >>

Hello All, I am having trouble using the substring function with a NOT IN subquery. It returns the error Invalid length parameter passed to the SUBSTRING function. It works fine using IN but fails with NOT IN?? If I remove the LEN function it works as well, the problem seems to be in LEN functi...more >>

Hi Apologies if this is the incorrect group for this post. I have a Windows Service running that populates a database and a Web Service that accesses the same SQL 2000 database. One of my tables within the database records the filenames of files within a number of folders. If a folder cha...more >>

Hi, my problem is as follows... I want to run a while loop with a select...into command inside it however i want the table name of the table that the data is being inserted into to change along with the while loop (i.e.count variable.) Say for example, SET @count = 1 WHILE @count <= 10 ...more >>

Hi, I'm in the process of converting a function from oracle to SQL Server. I have an insert statement in oracle function. but in sql server i learned that we cannot write insert statements in functions. Is there any turn around for this problem? How can I implement an insert statement in sq...more >>

Hello, I have such SQL query in my asp app: SELECT * FROM HardwareList WHERE IP LIKE 'PCP004033' OR Name LIKE 'PCP004033' OR OS LIKE 'PCP004033' OR Placement LIKE 'PCP004033' OR HWID LIKE 'PCP004033' OR User LIKE 'PCP004033' OR Domain LIKE 'PCP004033' OR Locale LIKE 'PCP004033' OR Hardware ...more >>

I have just set up an off site SQL server which we want to use as a back up system. During the normal work day there are only 5 tables in the main database that are critical. I may be well off base here, but that’s what this forum is for, to help and guide Scenrio The In-House Server...more >>

Hi all, I would like to write a stored procedure that starts automatically when SQL Server starts, and that logs certain events. I log to a trace file, and I enabled the failover file option: EXEC @rc = sp_trace_create @traceid OUTPUT ,@options = 2 -- TRACE_FILE_ROLLOVER ,@trace...more >>

I am looking for a piece of t-sql that will return the last date for the month for the month 6 months into the future. eg. it would return '2007-01-31' if the sql was run at any date in August. It would return '2007-02-28' if run in any date in September etc. Thanks ...more >>

Hello everyone, The function in this page does nearly what I want, except rather than populating the table, i need it returned as a string, for example, Diane / Peter / Nancy. http://www.paragoncorporation.com/ArticleDetail.aspx?ArticleID=9 Could anyone please help? I'm just picking up ...more >>

I made a ddl trigger on database for event group which is ddl_table_events. The question is: can I know inside the trigger logic (after "as" keyword) which event type of this event group has fired the trigger? my code: use AdventureWorks go create trigger TableEvents on database for d...more >>

create table dbo.Numbers ( Number1 integer not null, Number2 integer not null, ) go INSERT INTO dbo.Numbers VALUES (2, 3) INSERT INTO dbo.Numbers VALUES (4, 8) INSERT INTO dbo.Numbers VALUES (7, 5) INSERT INTO dbo.Numbers VALUES (1, 2) go This doesnt work, why? D...more >>

I have some parameters which i extract from url .. they are http%3A%2F%2Fwww.shopadidas.com%2Fproduct%2Findex.jsp%3FproductId%3D2177093%26shopGroup%3DR%26cp%3D2019627.2039609.2012800%26parentPage%3Dfamily%26colorId%3D http%3A%2F%2Fwww.circuitcity.com%2Fccd%2Fhome.do%3FWT.mc_n%3D54879%26WT.m...more >>

hi, how to find out all the store procedure have 'H' inside it? thanks. ...more >>

Dear all, I have 2 long strings , @str1 ='avbcd....ddde_++^&&*' @str2 ='hfdaksfhid...._+_)_))0999*&^^^%^' if I use the following insert statement to add data to the table, it is OK. insert tmptable (mydata) select ('avbcd....ddde_++^&&*hfdaksfhi...more >>