Hi, I wonder if someone can help me with a date Query, I have read a few threads how to find out the number of working days between 2 dates but what I want to do is add 15 working days to a specific date, can anyone help I am using SQL Server 2000. Thanks PD...more >>

We are storing PDF Images in a table in Database. I need to extract the binary data and pass it on to the application code to export (PDF Image) to another format (TIFF) to feed another system. I have gone over this article listed below. Is there another way to extract PDF binary data from ...more >>

Dear reader of this message! I'm looking for info about what is new in cursor on MS Sql Server 2005 compared to MS Sql Server 2000 Maybe some of you have a good link about this topic? Anyway, have a great day all of you :-) ...more >>

I have been chasing problems with stored procedures taking longer and longer to run, progressively getting worse. I have looked at parameter sniffing as a possible problem, trying remedies that are documented, but with no luck. For example a stored procedure is running pretty well , and then...more >>

Hi all, I have a webpage where users can search for some text in a textbox. I'd like to have a feature that displays to them their last 5 or 10 queries they've made. I don't know what structure I should have for the table that stores that info :S LastSearches -------------------- search_i...more >>

I have the following update query UPDATE Employee SET Deactivated = 1 FROM Employee AS Employee_1 INNER JOIN Assignment ON Employee_1.SSN = Assignment.SSN CROSS JOIN Employee WHERE (Assignment.SCHOOLID = '0') AND (Assig...more >>

Can anyone explain to me how does a vew work internally? If a view is a replicate of a table, will it be faster to query from a table then a view? Thanks ...more >>

hi, I am trying to run a SQL command that checks if a stored procedure exists, and if so updates it. problem is the command contains a couple 'GO'. these don't work well with: "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spMySproc]') and OBJECTPROPERTY(id, N'Is...more >>

Hi, It seems that I've run into a Dynamic SQL limitation. I've many Varchar(8000) variables holding a Dynamic SQL sentence (@var1, @var2 ... @varN) When my SP executes the sentence: "EXEC (@var1 + @var2 + ... + @varN)" the execution fails as if the resulting SQL sentence has sintax errors....more >>

I'm trying to copy tables from my test machine to the production server. The table in my test sql instance has a table I created in the gui, from which I extract this ddl: USE [GranSqlBase] GO /****** Object: Table [dbo].[Po_SubCats] Script Date: 09/06/2006 16:29:12 ******/ SET ANSI_NUL...more >>

Hey MS Sql Server 2005 My trigger below isn't sending emails, the problem is in this code "@recipients= 'select userEmail from inserted', ", I replaced that select statement with a real email address and then it works Any suggestions on what I should do make the select statement work is...more >>

Is it possible to add some fields to the new table created by a select into? If yes, how can I do that? I've tried : select *, cast(mynewfield as varchar(1)) into ##mytable from rhum030p But it didn't work... ...more >>

Is it possible to obtain the column name of a record with a certain field value. For example: Column Names: id, date, UpsideDown Sample Values: 1, 9/6/2006, Y 2, 9/6/2006, N I'm hoping to be able to write a query which would have a result set like such: Column Names: id, columnName, ...more >>

I'm trying to use the CONCAT function in a VIEW to concatinate two fields, a first name and a last name. I can only get the function to partially work and am not sure what I'm doing wrong. I used the following line in my View { fn CONCAT(dbo.tblCustomerContact.LastName, dbo.tblCustomerCont...more >>

I need to strip duplicates from the following recordset...as you can DISTINCT will not work in this case. Thanks for any direction. CLASS INSTRUCTOR1 INSTRUCTOR2 Math101 Smith Brown Math101 Brown Smith Math201 Wilson Young Ma...more >>

I have a series of tables in the database that hold orders for of the months i.e. Tbl: June06 OrderNo, Amount 0001 100 102 150 Tbl: July06 What I want to do is, when a user passes in the month say July06, I should query from table call June06. Instead of putting the ...more >>

Hi, I have an invoice and invoice status table. An invoice can go through difference statuses and I want to track them. On one query I want to return the invoice information and it's latest status. But when I try the following it fails: Select Invoice.*, (Select Top 1 * from InvStatus...more >>

I was recently called in to take a look at a database from another vendor as a "second opinion". They are having issues with data retreival speeds etc. The first thing I noticed was that there were a number of duplicate tables. The tables have different owners but the same names. I'm not sure ...more >>

I am working on a SQL Server database in which there are no primary keys set on the tables. I can tell what they are using for a key. It is usually named ID, has a data type of int and does not allow nulls. However, since it is not set as a primary key you can create a duplicate key. This w...more >>

Here's my sample initial recordset: CLASS INSTRUCTORS ---------- ---------------------- Math101 Smith Math101 Brown Math201 Wilson Here's what I need to transform the records into: CLASS INSTRUCTOR1 INSTRUCTOR2 ---------- ...more >>

Gurus I would like to search a table and then search the results of the original search, but my syntax is not correct: Select * from tempERCP, ( Select * from tempERCP //get a subset of all records Where Diagnosis like '%pseudocyst%' or Diagnosis2 like '%pseudocyst%' or Diagnosis3 l...more >>

Hello all, I have a column of data that has the names like this: Smith, Jane A. . I want to know if I can query the name column have the output like Jane A Smith. TIA ...more >>

If I have 2 users running an import application at the same time I randomly get an error saying that the uniqueID is already in the database. The strange thing is that the uniqueID is generated from the same instance of the import application that is reporting the error. Here is the scenario. ...more >>

Hi: If an error occurs in a transaction, I would like to permanently save the error (insert a record in a table) before I roll back. By default the rollback will also rollback that inserted record. Is there any way not rollback that record? I want to save that error somewhere. The trick is...more >>

I have a table that tracks the status of an order as it is being processed. As the order moves through the system, each station that handles the order updates it's location. My table layout is as follows: Table Name: status statusId (int - identity) orderId (int) stationDate (datetime) ...more >>

Hello I have three tables (Item Table) ItemId,Name 1,Item1 2,Item2 (ItemDetail Table) ItemDetailId,ItemId,UDFId,DetailValue 1,1,1,DetailValue1 2,1,2,DetailValue2 1,2,1,DetailValue3 2,2,2,DetailValue4 (UserDefinedField(UDF) Table) UDFId,UDFName 1,ItemCl...more >>

Sorry if this questions sounds elementary, but I am new to SQL. I am trying to create a query that help do some calculations based off some data. I am looking to sum specific rows for the "Acct" column. For example, I am trying to add "Acct" numbers "266" and "255" and then group the results ...more >>

Hi All: I have a table holding project information that links back to itself in a parent-child relationship. So a project can have sub projects which in turn can have sub projects of their own. The table structure (simplified) is Project_ID, Project_Description, Parent_Project_ID Exampl...more >>

Consider the simple XML Hierarchy below it is stripped of various attributes. 'Prop' is short for property as in a form of real-estate and each property is comprised of other property. For instance, ABC is made up of 123, 456 which is made up of 789. The number of nodes that make up a single ...more >>

I am trying to create a table in my database with GoDaddy. I'm getting this message when I paste in my text to the Query Analyzer: Error -2147217900 Line 14: Incorrect syntax near '('. BEGIN SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON CREATE TABLE [dbo].[MemberInfo]...more >>

I am trying to insert into sql server2000 a field from a form that is of type varchar, and insert into a table which has the field defined as smallmoney length 4. I have tried a number of variations based upon examples from the web, but none have worked in my case so far. I understand that I n...more >>

Given the following table: CREATE TABLE Item ( ItemId int NOT NULL , GroupId int NOT NULL , IsDefault bit NOT NULL ) ON PRIMARY How can I create a constraint which ensures that no more that one record for each GroupId is flagged as IsDefault=true? Thanks, T.H. ...more >>

I have created a large fixed-formatted file in DTS that will be sent to a vendor. It wraps when looking at it in Notepad; its over 1400 bytes pre record. Does anyone know of a share-ware product that exists that would allow me to view this large file and also allow me to ensure that it is a...more >>

I've a stored proc which has a raise error: _________________________________________________________ <proc 1> ----sql stmts---- if <condition1> begin exec <another stored proc>proc2 raise error ('some msg', 16,1) return end ----rest of stored proc 1---- ______________________...more >>

One of our developers is using VB to connect from a client machine to one of the Sql Servers (2000) and execute an executable which then in turn tries to create a temp folder. This is failing on the creation of the temp folder because of permissions. Here are my questions: 1. The executabl...more >>

I'm fairly new to SQL Server 2005 and have recently migrated my Access DB to SQL Server. For the most part, I have no problem creating many Stored Procedures and getting them to work properly. Where I'm stuck right now is on a very basic and frankly, embarassing problem. I can't seem to fi...more >>

Someone can help me? *----------------- | SELECT ... FROM TB_X WHERE FIELD1 IN (@PARAM1) @PARAM1 = 'A','B','?', *----------------- I want to create a QUERY (procedure) like this: SELECT ... from tb_x WHERE fieldx IN (@Param1) this parameter (@Param1) can come to procedure like this...more >>

Using the following query, I get back three records. I should be getting back four records. If I run the query using only the fourth join by itself, I will see the record that is missing. How can I rewrite this query to get back the four records I know I am supposed to get back? I hope this en...more >>

I get the following error on my SQL insert statement : 'Only one expression can be specified in the select list when the subquery is not introduced with EXISTS' insert into MachinesSold( OwnedByID, DivisionID, Family, Product, SerialNumber, ...more >>

Can anyone tell me why I can't add a column that is not null without a default value using this script? ALTER TABLE [dbo].[TableName] ADD [ColumnName] [int] NOT NULL SQL Server throws this error, but I would like to know why. ALTER TABLE only allows columns to be added that can contain n...more >>

hey all, why pick one type over the other as far as defining a field type? thanks, rodchar...more >>

Hi All, We have custom application running on SQL Server 2000, about 100 users, on WIn Server 2003. The RAM installed is 1GB, very often that the server the client getting kicked when trying to connect to the server from custom app. Then we add to 2GB, now its getting better. SO, I need a...more >>

1. t1.k = t2.k 2. t1.k = isnull(t2.k, 'SomeValue') 3. t1.k = case when t2.k is null then 'SomeValue' else t2.k What's the performance different between 2 and 3? and 1?...more >>

Hi All, Want to write a SQL UPDATE statement to trim the parentheses in a column named Prod_Model and a table called tbl_MASTER. Any ideas?...more >>

Our powerful SAN is being beaten to death by a less than optimal schema, lotsa' medocre TSQL code, and messed up indexes - successful Dot.com moving to fast to allow for doing things any other way. So, we rely on the brute force of our hardware to sustain these inefficiencies. I've tried to ...more >>

I have some dynamic sql exec statements...but I would like they don't return their results. create proc... exec('select top 2 * from t') exec('select top 2 * from t1') .... exec('select top 2 * from t2') select properresults from table go I would like to show only last query. I...more >>

Hi folks, i was wondering if someone could help me please. Basically i have started developing in SQL server recently - i am attempting to write a report on time differences between records. So in my table i have many records. each record has a unique ID/ datetime value / type the ty...more >>

I am joining to a PAYMENTS Table but rather than getting all the payments, I need just to get the last payment...MAX(paid_on)... What is the syntax to do this??? I appreciate your help in advance. This is the BEST SQL forum that I have EVER found and you people make that possible! wnfis...more >>

I have a stored procedure in SQL Server 2005 that contains: DROP SCHEMA AppUser DROP USER AppUser but I only want to do this if there is an AppUser schema or AppUser user. In T-SQL, how to I script: If there is a schema called AppUser ==> DROP SCHEMA AppUser If there is a user called AppU...more >>

Hi guys, Can i update table filed with some stored proc value like this update ABC set feg= exec sp_def 'abcdse' I've tried this but it's giving error what should i do?...more >>

I apologize if this is not the right newsgroup for this question. Please let me know the relevant group if that is the case. I am using a SQLServer 2000 database. There is a big view which is used through out our legacy codebase. We wanted to add some UI session-specific logic to the whole code....more >>

I have little confusing about Backup files(bak) and transaction (TRN) files I know the purpose of backup file and know how to restore backup files but whts the purpose of transaction file backup? If we have both log and backup files then why do we need transaction files? I also want to kno...more >>

I am moving a database from 2000 to 2005, and have a problem with the REVOKE statement. When I run : "REVOKE ALL ON [LoginHistory] TO [AppUser]", I get "The ALL permission is deprecated and maintained only for compatibility. It DOES NOT imply ALL permissions defined on the entity.". How ...more >>

Is it possible to create a linked server with MySQL 5? I am using the MySQL Connector/ODBC v5 Driver and attempted to create the linked server using: EXEC sp_addlinkedserver 'MySQL', 'MySQL', 'MSDASQL', Null, Null, 'Driver={MySQL Connector/ODBC v5 ...more >>

this command does work like expected: cmd.CommandText = String.Format("GRANT EXECUTE ON dbo.[{0}] TO [{1}]", procname, userorgroup); this throws a syntax error near 'TO': cmd.CommandText = "GRANT EXECUTE ON @ProcName TO @UserOrGroup"; cmd.Parameters.Add(new SqlParameter("@ProcName", procn...more >>

I've seen some pretty creative SQL statements that locate first/last/missing elements in a series but I haven't been able to adapt any of them to work speedily with my data set. Here's the problem: We have a table of services for our clients (about 2 million rows). The rows are simply the c...more >>

Hi guys, i've a strange problem that i define below I've 2 tables in which from first table i want to take data from a specific filed and i want to update data of specific field of second table. It's very easy to update data of one column but how should i updat...more >>

Hello all. I have uploaded a table into sql management studio express. However, when I right click on the table and try and open it, I get an error message saying; "SQL Execution Error. Executed SQL statement: select columnName1, columnName2 etc.... Error source: Microsoft. VisualStudi...more >>

Hi, I have had to take our developers out of the sysadmin role on our servers due to security clamp downs. But because of this when they create a table it puts their userid to the table rather than DBO even if they are in the db_owner role. I know I can run some code to change this but is t...more >>

Hi All, I require top bring back Calls_Answered from a table and i also require to bring back Query_No from another table within a different database. Is joining two tables from two different databases the right thing to do in this case? If so i have a slight problem... I have no commo...more >>

Hi! I'd like a tool that will generate Insert statements for my whole table with data. What tool is best for this job ? Jarod ...more >>

hi all, i need some normalization materials ...more >>

Hi, I am looking for a script that can give me the list of server rôle attach to a login and the relative SQL user. Like this : LOGIN SQL_USER SERVER_ROLE ----------------- ---------------- ---------------------- sa dbo sysadmin a_login ...more >>

I added to the database figures like 10.235 instead of rounding first to 10.24 I want to rectify it not by reassigning the new value based on the old value What method can I use? Thank you, Samuel ...more >>

Hi, Can we write a UDF or SP that accpets parameter arrays like COALESCE function? I mean the caller be free to pass any number of parameters. Thanks, Leila ...more >>

I'm trying to handle deadlock situations gracefully, but am a bit confused about where exactly deadlocks can happen. I assumed that I may or may not get a deadlock exception when executing a command (using VB.NET 2005, with SQL Server Desktop Engine). So for example: theReader = theCo...more >>

Hi, sql server 200 sp3a. We have several stored procedure that require to retreive some Ids. I would have liked to have a generic function which returns a table with those Ids and then from all the stored procedures I would be able to do some thing like .. WHERE Id IN (SELECT Id FROM fn...more >>

Hi, all I have a stored procedure to create table to a database from time to time, if the time is short ,it runs OK, but if the time is longer than 10 hours, it will turn out the following error: 17207: udopen: OS error 32 occured while create/open physical device d:\webhisdb\data\dbtemp_Lo...more >>