I got an if else statment in my SP Create dbo..........etc if ............... one long insert statment second long insert statment else third long insert statment [one & second insert statment , got 40 lines together] For the above sytnax, I got" syntax error near ELSE] if I remove...more >>

Hi, How to count the rows of a table (any) per 15 sec intervals of the datetime field. Does anyone could point me to the right direction? Thanks, Janos ...more >>

I use Access ADP, so I do not have SQL analyzer to tell me how long takes to execute my query. Is it possible to have my query to tell me how long take to run my query? Any information is great appreciated, Souris, ...more >>

This code is from a VB6 application Create a select statement and open a recordset where POVID is a string variable containing an ID to select the row. Note: The IDs in the version table are unique so only one row returned. strSQL = "Select GKB5_TVENDOR.FVENDORID, FVENDORNAME, ...more >>

hi, When I open the EM, it shows a solid green arrow right besides the development server, but it shows a white arrow in the production server. what is the difference? Thanks Ed...more >>

I have a long running process that i would like to have started via a web application. I suppose I have a few options and im not sure if #1 is possible. #1 - Start A Job via ASP or ASP.NET #2 - Set a Flag that a job checks when it runs on a repeating schedule so it will take action i...more >>

Hello, I'm not sure what will be the best way to solve the following problem. ID ParentID Version ----------- ----------- ----------- 1 1 1 2 1 2 3 1 3 4 4 3 5 ...more >>

Can someone please help me out. My sql is taking alsmost 16 hours to complete and is not exceptable. Am creating a cursor from a table A which has about 120,000 record. For each record if this record exists in table B then update the columns otherwise insert a new record in this table B. I ...more >>

I am having a problem call a sql server stored procedure in a test\production environment. I am getting an exception from sql server being propagated back to the web service. The exception is a violation of primary key constraint. The exception message is: 'Violation of UNIQUE KEY constraint '...more >>

you said in myearlier post that if i wanted to have 'do you want the count of distinct rows, based on the columns ID and ProductID?' yes i want to be able to do that can u tell me how thanks...more >>

CREATE TRIGGER logChanges ON RepInfo FOR UPDATE AS DECLARE @RepInfoID INT IF COLUMNS_UPDATED() > 0 UPDATE RepInfo SET LastChangedDate = GetDate() WHERE @RepInfoID = RepInfoID GO ------------------------- Trying to update LastChangedDate in a given record when any of the columns in t...more >>

HI! Ive got a trigger that makes an UPDATE over an specific column, and i want to know how to get de values from the columns of the record that was modified and, with this data, modify another columns of other tables... I'm trying to get the values of the record with this code: Selec...more >>

I have one field with data type float contains data e.g 245.5,65,36.2..etc. I need to query out all the value with decimal points only, means those value = 245.5,36.2.. How can I use transact sql to check? Thanks ...more >>

Hi, I'm having problems inserting/updating a NTEXT field using OPENXML. The field always gets a blank value with any parameters it receives. Here's the (simplified) query: -------------------------------------------------------------- declare @doc int -- Actually, it's a parameter declare...more >>

Thanks in advance for help on this issue which spans SQLXML, .NET dataset as input, and XSD type specification. I get the error "error converting datetime from character string." in my SQL stored procedure, which is using OpenXML. CREATE PROCEDURE InsertAuditDataXML @AuditDataXML ntext ...more >>

basically i am trying to write a SP which accpets 3 parameters and I want the count of distinct id and productid like this select @COUNTUNIQUETEST=count(distinct (id,productid)) FROM table A WHERE CID in (SELECT CID FROM table A WHERE DATERECEIVED>=@PERIODSTARTDATE AND DATERECEIVED<@PERIO...more >>

Hi, I have a StoredProcedure 'ProfitSP' in different databases and the ProfitSP is same in each database. If I execute ProfitSP as: Exec DB1.dbo.ProfitSP @OrderStartDate, @OrderEndDate it works fine! But if I try to create a temp table and then execute the same, it gives me error a...more >>

I can not return the rows affected just using int a = myCommand.ExecuteNonQuery(); it always return -1 WHY??? ...more >>

I can not return the rows affected by just using int a = myCommand.ExecuteNonQuery(); because the return is always -1 I have to use an Output parameter in my SP all the time.. anybody knows WHY??? ...more >>

Is it possible to add a FK constraint between two fields that are neither a PK or UNIQUE or does the "linking" field have to be a PK or UNIQUE?...more >>

why can't we use isnumeric() in a WHERE clause? Is patindex('%[^0-9]%',field)>0 the best way to find out if a string field contains data that can't be converted to numeric? ...more >>

I want to know if @MyDate (DateTime) is in the current month. This works, but it's ugly. Any ideas to make it look nicer? if (datepart(year,current_timestamp) = datepart(year,@MyDate) and datepart(month,current_timestamp) = datepart(month,@MyDate)) /* Yes*/ else /* No */ ...more >>

I am getting syntax errors on the following SQL. Can anyone help? Thanks. IF COALESCE(dbo.People.Address1,'') = '' BEGIN FullAddress = COALESCE(dbo.People.Address2,'') + Chr(13) + COALESCE(dbo.People.City,'') + N', ' + COALESCE(dbo.People.State,'') + N' ' + COALESCE(dbo.People.ZipCode...more >>

I am creating a Job using the SQLDMO interface, if the job fails I want it to email someone with an appropriate error message. On some servers this works fine and on others it doesn't, it all seems to depend on the mail profile settings. The wierd thing is that an activeX script that doesn...more >>

Hello, I want to create a list op dates using the SELECT statement. The dates range from the current date till the current date - 10 days The resultset for today should be as follows. 20050221 20050220 20050219 20050218 20050217 20050216 20050215 20050214 20050213 20050212 20050...more >>

Hi, I need advice (being a newbie) on designing a schedule Availability table. I have a schedule table which I write availabilities to this way: Based on if this is a one day event or a recurrent event, I am writing to the DB every schedule day. i.e, if this availability starts on jan 01 ...more >>

Hi there, Ive been writing the basic SQL query statements for quite a while now, but have realised how useful it would be to have a Script that i could run that would look at a database and check that all the fields were there, if they were create them, then check they they were the rite da...more >>

Is it possible to return one field in a view that has something similar to vbCrLf in Visual Basic? I want to return it as a formatted envelope address. Thanks. David ...more >>

I am building a view and I want to return a combined field of all that make up a full address (Address1, Address2, City, State and ZipCode). If any of the fields are Null it returns a null. Do I have to use something like COALESCE on each one as any of them can be null. Thanks. David ...more >>

Hi i've created stored procedure in sql server 2000 which returns a value see the stroed procedure CREATE PROCEDURE QA_Select_AdminOrRec @Role varchar(50), @username varchar(50), @pwd varchar(50) AS if exists (select * from QA_Admin_Recruiter where role = @role and userna...more >>

In a table with 400.000 rows there is a ntext field that contains html code of about 15/20 k for each row. Do you think that if I set <field> = '' should I increase performance for new insert or I have to delete rows? Any help appreciated. Regards. -- Fabri (Lattepiù chi può darti...more >>

There are basically two ways of doing this offered by .Net environment: using SqlDataClient and OdbcClient. My question is: what are the advantages of using Sql client over Odbc client? I understand that specialized classes are supposed to be optimized for transfer protocol and that Odbc...more >>

Alejandro, I have read throught BOL but I have not found anything that tells me about how sql server treats the result sets from Views. It would be good to know if SQL Server recomplies the execution plan for the View's select statement each time the View is referenced. Thanks, John...more >>

Hi This is an easy Qn. Just send me where did i go wrong ? DECLARE @HDoc int DECLARE @xmlDoc nvarchar(4000) SET @XMLDoc = '<XML><Functionality><F>1</F><R>10</R><F>2</F><R>20</R></Functionality></XML >' EXEC sp_xml_PrepareDocument @hDoc OUT, @XMLDoc SELECT FunctionalityID,Rate FROM OPENX...more >>

Our product integrates with Great Plains accounting software. Our customers may have 1 or more Great Plains databases for each of their branches. All of the databases, ours and Great Plains, resided on 1 server. We have stored procedures that use dynamic SQL to get information from a certa...more >>

I have a question regarding using the Identity/Seed values. I have migrated a number of tables form Access to SQL. In Access the identity/seed values were called autonumbers. The big problem with autonumbers in Access was that they could change at any time on you, getting renumbered or some...more >>

Good day to all. Being somewhat new to SQL, I like to use a Stored Proc to Create Table. I do not quite understand what to do. I suspect that I would have to create string then EXEC the string? Any and all help or direction would be appreciated. Thanks. Wiley Coyote ...more >>

We have a atble TAB1[ ID IDENTITY integer, NAME VARCHAR[30], .... ]. This table is present in 2 databases Db1 and Db2. We want to keep them both in sync daily. The mapping of Name -> ID should be the same in both the tables. We allow only Db1 tables to be updated. So we would like to have a prog...more >>

Hello, I have a system that I am writing to automaticly import Credit Transaction data into a SQL Server 2000 Database. I am using a VB.Net application to detect when the file arives and prep it for parsing. The file is aproximately 10Mb of relatively complex hierarchal data that is defin...more >>

CREATE procedure QrySampleDetail @CustomerName nvarchar(255), @Contact nvarchar(255) AS DECLARE @WCondition varchar(255) DECLARE @sql varchar(1000) SET @WCondition = @CustomerName IF @Contact <> '' SET @WCondition = @WCondition + 'and Contact =' + @Contact set @sql = ' SE...more >>

I assume that you can't pass back values on an if statement? I was trying to do: if not exists (Select ApplicantID as @ApplicantID from ftsolutions.dbo.Applicant where ClientID = @ClientID and LastName = @LastName and FirstName = @FirstName and Email = @Email) And got an error until I ...more >>

What is the difference between #temptables and ##temptables? I am aware that ##temptables are global temporary tables and #temptables are local. Is there any difference apart from this? Is it true that ##temptables will get dropped by itself when the session ends? What happens when they (##tem...more >>

Hi, if i had a table with two columns: one an int column (Person_id) and one a navrchar column (Person_First_name) and i wanted to write a query that would return the Person_id and Person_First_name of all the people with the same first name (where the same name appeared more than once) how w...more >>

I believe set @today=getdate will give me the current time. How can I get the system date without the time?...more >>

All: In general (I know that certain circumstances might dictate specific measures), should transactions be coded at the stored proc level or should they be managed in the calling application? What caused this question... In .NET 2.0, there is a new TransactionScope class that will enable...more >>

We have reinstalled SQL Server 2000 on a new server but select statements on datetime fields are now failing where they didn't before. e.g. SELECT * FROM CONTHEAD WHERE start_dt<='21/02/2005' and (expiry_dt is null or expiry_dt>='21/02/2005') gives the error The conversion of a char data ...more >>

Hi All, Just a quick question for anyone that may know the answer, I am performing some calculations, some count's and some stdev, when I pass the select statement through normally, I get one set of results but when I pass it through as a unicode string I get a slightly different result, th...more >>