Is there anyway with Sql Server 2000 to notify me of a record being written to a table? For example, I would like to know when a record is written to a table to let me know that some status has changed and that I would need to do something to resolve the situation. I would have a service runn...more >>

Hi, i think that my question is stupid, but i will ask anyway .. I want to allow a user to create stored procedure, alter stored procedure, and drop procedure, but the same user cant alter any table, can i do that ??? Thanks -- Message posted via SQLMonster.com http://www.sqlmonster.co...more >>

I have a text column in my table. Based on what I heard from my DBA, it should take any length of text data. But this is not the case. I am inputting some large amount of text into the database. But when I try to see what is entered, I am seeing only a chunk of what I entered. I thought may ...more >>

Hi. I have the following procedure: .... BEGIN TRANSACTION SET IDENTITY_INSERT REGISTROS_TMP ON INSERT INTO REGISTROS_TMP (..., ..., ID, ....) values (..., ..., @Id, ...) SET IDENTITY_INSERT REGISTROS_TMP OFF COMMIT TRANSACTION This locks this table, and a couple more, I think. I ...more >>

I have a problem that at first sounded easy (perhaps it still is), but as I get into it I'm getting more and more confused. Here is the problem. I have a table holding transctions (we'll call the table Trx for ease of use), and this table has a Date column that records the date of the transa...more >>

Hi, I am summarizing data like this SELECT dbo_Order_Line_Invoice.Cono, dbo_Order_Line_Invoice.CustId, Year([Invoicedate]) & '/' & Format(Month([Invoicedate]),'00') AS [Year-Month], dbo_Order_Line_Invoice.VendId, Sum(dbo_Order_Line_Invoice.Sales) AS Sales_Total, Sum([Sales]-[Cost]) AS ...more >>

Hello, I am trying to calculate an age in terms of years and express the result as a float, such as 3.75 years or 5.33333 years etc. I have tried things like... CONVERT(float, DATEDIFF(d, MyStartDate, getdate()) ) /365 as YearsOld But this gives results as whole numbers like 3.0 and 5.0....more >>

Hello, I would like to calculate the diferrence between two dates and express the result as a float of the number of years - such as 3.75 or 5.33333. I am trying... CONVERT(float, DATEDIFF(d, MyStartDate, getdate() ) ) /365 as YearsOld (I realize that dividing by 365 is inaccurate, but i...more >>

I have a legacy database that was originally in mySQL. It contains many columns that contain comma-delimited lists of foreign keys. So the column may contain "802,1404,25,124" etc. All the numbers in the list point to lookup values in another table. I need to take these lists and create a r...more >>

Bonjour, j'obtiens une erreur lors de l'insertion d'un enregistrement dans une de mes tables. je ne comprend pas porquoi j'ai cette erreur ni meme ce qu'elle veut dire. l'erreur est la suivante : "Les donn=E9es cha=EEne ou binaires seront tronqu=E9es. L'instruction a =E9t=E9 arr=EAt=E9e." ...more >>

Hi, I have a table like this: Name Count(int) Tony NULL Dennis NULL Mark NULL .. .. .. How do I write an UPDATE statement (or a cursor) that will give me this: Name Count(int) Tony 1 Denni...more >>

Hi everyone, I'm trying to do a nested join query and not having any luck. The Query Analyzer gives me a "Syntax Error near 'Left'" on this query... Select PeriodId, Quantity, Balance, ResType, Label FROM ((SELECT DayDate, PeriodId, Label, ResourceType FROM ATSWeeks CROSS JOIN ATSResour...more >>

Is there code to establish if a date is valid so that I can send an error message if i get the 31st of February for instance im thinking of something like the following but don't know the code @feedback = case when (@date is ok? ) then 'DateGood' else 'DateBad' end regards Earnie ...more >>

Table A with columns (id, Adate, AType, No) Table B with columns (id, BDate,BType,No) Sample Data Table A 100, NULL, NULL, 1 Table B 100, 01/01/2005, AA, 1 100, 01/02/2005, BB, 1 100, 01/03/2005, CC, 1 I need an update statement which updates Tab...more >>

I'm trying to write a script to alter a view, and I'm having difficulty getting Query Analyzer to successfully parse the script. Here's the script... IF Exists ( SELECT * FROM dbo.sysobjects WHERE ID = object_id( N'[view_Project_Detail]' ) And ...more >>

For auditing purposes, I create a list of all the users who have access to my databases. I do this by issuing this: exec sp_MSForEachDB sp_helpuser However, this doesn't include the name of the database that the list of users is for. How can I include this info? Thanks. ...more >>

Hi. I need to know if a table has identity fields. Is there any view to get that information? -- Regards, Diego F. ...more >>

I need some help.... Can someone please, in point blank form tell me the difference between Database maintenance and Database Administration? This might seem obvious, however apparently I am not certain. thx ...more >>

Using Access 2000 as an .adp front end, a form reads records from a very wide table. A subform allows views of different sets of fields from the same record. There are three sets, each of which resides on a different subform that is switched into the subform control on the main form. After ...more >>

I have a fairly small database that is only being used by 2 people (it will be used by many more in the future). I am replicating this database to another database that is currently being used by only a few people. My automated replication process began to fail after only about a week due to...more >>

Hello. I have data in a table, like so: Sample data: claim claimline denied 1000 1 500 1000 1 501 1000 1 502 1000 2 502 2000 1 500 I want to roll up the lines with comm...more >>

I have four records, each containing a date. LineNo ShipDate RefLine -------------------------------- 1 1/30/05 <null> 2 1/1/05 1 3 1/15/05 1 I want to compare the date in line 1 to 2, then 2 to 3 Basically, the lines belong to the same order. Wha...more >>

Hi! I know there is xp_dirtree, xp_subdirs, xp_fixeddrives procedures which allows me to implement a folder select dialog. But SQL Server's Enterprise Manager already has such dialog. For example if you open "Attach Database" dialog and then press browse [...] button Enterprise Manager wi...more >>

Hi everyone, I Need to display summarized, time phased (lets say weekly), data in a datagrid. Pretty straightforward except that my summarized data may or may not be "complete". That is if I do a simple summarization query on my base data I won't get a record for every week. In my DataGr...more >>

I am excited about this new feature in SQL Server 2005. However, it seems to me there is a rather severe limitation to CTE's. That is the fact that any queries that call the CTE must call them right after the CTE definition. For example, Example #1 works, Example #2 fails, Example #3 works. I ...more >>

For all our ASP.Net applications we use a user called 'AppUser' to connect to the database, which only has execute permissions on the stored procedures. This makes sure that client apps only connect to the server via stored procedures. I keep this up-to-date by running a stored procedure t...more >>

I would like to track who made a change and when a change was made to a row. In Oracle we did this a with a "Before Insert or Update", "Row Level" trigger that would grab the connected users Name and System date and then put it in Fields: Update_Who and Update_When (which had Not Null constrain...more >>

Hi, I have the foll round((convert(decimal(4,0),d.new - d.old)/d.old)*100,0) The result I get is -54.00000000000 How can I change it to only display -54 Thanks...more >>

We are receiving a reporting database from a vendor nightly. We want to upload this data and structures to SQL Server nightly. We want it to be as flexible as possible - if the vendor adds/deletes a table/column, the import job should handle that. Apparently, the Import/Export wizard in S...more >>

Hello, I have an asp.net application that will use a SQL Server DBMS for the backend. On my asp.net form. I have a next, previous, last and first buttons on my form. Basically, this application will allow the user to page through some data one record at a time. Let's say my SQL statement ...more >>

According to the docs, SQL treats NULL as a value so you can't put a unique index on a column that allows nulls for such things as optional values. For an example, an employee table, the cell phone column is optional and can allow nulls since not everyone has a cell number. However, if they ...more >>

I'm trying to get a sum of employee certification by certification name without having to hard-code the certification name into the query. The list of certifications may change as new ones are added via an application .......... In the examples i've found, typically the query hard codes the ...more >>

Hi list, iam very new to SQL. i have a developer that is making a very complex query over a SQL2000 + SP3 database, with a lots of union queries. He recieves this error: Transaction (Process ID 97) was deadlocked on lock | communication buffer resources with another process and has been chos...more >>

I've spent hours trying to find the answer to this with no luck. One of our systems tracks connections between objects. If a user enters an object that is connected to to another object that is connect to its parent object, we have problems. We have many diffent type of objects, so I will tr...more >>

Hello all! I have a problem with creating crosstab query in MS SQL Server 2000. I spent 8 hours on searching internet to achieve my succes but without result. I would like to transform such data: MRPController WK Value C01 200505 1 C01 200505 1 C02 ...more >>

if you're sick of this topic, feel free to skip this post. i've read about half a dozen threads focused specifically on this topic, but every comment gave me questions. and the threads were all too old to comment on, so i want to make sure my young opinion is an informed opinion. the databa...more >>

Dear fellows, I would need from T-SQL job or through any friendly-user function (VB front-end app) the aforementioned value. With EXEC xp_cmdshell 'IPCONFIG /ALL' I get values such as HOST NAME or IP Adress. Problem is the following: if oneself launch IPCONFIG /ALL from a DOS session ...more >>

hi, i am using instead of trigger in sqlserver 2005, my trigger looks like create trigger [dbo].[docsUpdate] on [dbo].[Docs] instead of update as IF (Update(MetaInfo)) BEGIN bla bla bla bla [Some operation] end my table looks lke this Dirname LeafName TimeLastModified Extension ...more >>

Hi. I have stored procedure. ALTER PROCEDURE dbo.test @param1 varchar(1), @param2 varchar(2) as declare @iParam1 int declare @iParam2 int if isnumeric(@param1) > 0 begin select @iParam1 = cast(@param1 as int) end if isnumeric(@param2) > 0 begin select @iP...more >>

Dear All, I am using British English for my database. I am using the following function to convert the date CONVERT(varchar(20),'04/10/2003',23) and its returning me 2003-04-10 which I dont want. The format which I require is 2003-10-04 (yyyy-mm-dd). Any help will be much appreci...more >>

hi, How could I order by datetime type including the seconds? Now I am using order by <field> and it' getting the values of this way: 2005-08-04 03:03:42.000 2005-08-04 04:00:33.000 2005-08-04 07:31:20.000 Instead of: 2005-08-04 07:31:20.000 2005-08-04 04:00:33.000 2005-08-04 03:0...more >>

I have wrote an application using VB6 and SQL7 and it is running LIVE in client side. Due to their business growing, they requested the system is able to capture other characters like Chinese and Portugese. I have no idea how and where to start. Anyone can help? Thx. YH...more >>

Dear all, I would need to know where is the variable for the time inverted for each DTS in each launching. In what table is stored. Regards,...more >>

hi, I have a stored procedure in which i have a cursor cur1. when there is an error in the SP the cursor is not closed on exit of the SP. i am currently looping syscursors to find the currently open cursor n then deallocating it as shown below.. but i realize the user needs perm to access the...more >>

Hi, I some SP I am doing this: DECLARE @EmployeeId INT SELECT @EmployeeId = NULL SELECT @EmployeeId = [EmployeeId] FROM [Employees] WHERE [Code] = @Code Can I place this in another SP or function (What's the best?) to minimize code lines? For example I want something like this: DECL...more >>

Our business weeks run from Sunday to Saturday. Is there any way to create a query that filters a date field so that it only returns records with a date >= *last* Sunday. e.g. >=Sunday 31st July 05 and if I run the query next Tuesday it will return records >= Sunday 7th August 05 Obviou...more >>

I make a program with vb (ADO) to connect SQL Server. If I want to execute its program at other computer where there is no SQL Server, So what do i have to install (what component of sql Server that I must install)? Can I just copy files that be needed only? ...more >>

how i can skip error messeges within Procedures such as can't insert to duplicate keys thanks sam ...more >>

Hi, I was wondering whether there is a way to get the last modify date of the table schema. Let's say someday I added a column to a table, will the system capture the day? I want this information. Thanks in advance. Regards, Jason Chan ...more >>

Does anyone know how to achieve the following as my do loop contstructions are taking quite a while to execute and this would be far faster I am sure (i used to know how to do it but cannot for the life of me remember what I did and I cannot find the answer on google)? I have a column A contai...more >>

in MS Access i use vba code to run pass-through queries to run sql server Procedures now errors like can't insert to duplicate keys stop's the code with error how i can skip the error messeges within the pass-through query thanks sam ...more >>

At the moment I have a bunch of formulas along the top row of a sheet and I wish to copy them down a predfined number of rows. At the moment I activate the sheet and then select the rows I want to copy down with the row containing the formulas at the top. It would be much cooler if I could a...more >>