HI My problem is that I have a table with date column in it. I want to retrieve the date value in a particular format which is of date type. For this I used the CONVERT function but I am getting the following error: The conversion of a char data type to a datetime data type resulted in ...more >>

Hello, I am Working on the below scenario and need your help: I have stored data in this format. User Activity ID Hours ReportDate ===== ===== === ==== ======== varma XYZXYZXYZXYZXYZX AP 8 ...more >>

When a field has a max length of 4000 and an average length of 500 to 1000, is it more efficient to use VARCHAR(4000) or VARCHAR(MAX)? Updates are not very frequent compared to reads, but when they happen they will often increase the length of the VARCHAR field....more >>

Where is my post? who deleted it? Regards Purva Telang "Purva" <purva@suviinfo.com> wrote in message news:... > Hi all > > I have following situation. > > table a contains sales data about a product. > table b contains marketing data about the products. > > now suppose table a contain...more >>

I have an excel spread sheet that contains a header and three different sections (delineated by text statements on separate lines between the data). Header One Some Data by Name $23,598.00 $23,598.00 $23,598.00 $23,598.00 $23,598.00 $23,598.00 $23,598.00 $23,598.0...more >>

So, I have this: SET @bServiceUpdateSuccessful = ~CAST(ISNULL(NULLIF(@ret, 200),0) as bit) that I have inherited. Would I be correct in assuming that when @ret=200 it will set @bServiceUpdateSuccessful to 1? If so, is there anyway to make that be 'like 2%' so that, perhaps, 204 and 200 would...more >>

I need to determine what API that a specific application is using to interface with SQL Server. In other words, the Books Online topic "Building SQL Server Applications Overview" has a table that lists the "general database APIs". The application does not use stored procedures. It might be u...more >>

Is it possible to implement? select catelog, (value at top 15) as the15thValue from table group by catelog TIA...more >>

In what case should you use WHERE and when should you use HAVING. Example. SELECT MainID, Sum(mySumField) as Total FROM MY TABLE WHERE MainID=1234 GROUP BY MainID or SELECT MainID, Sum(mySumField) as Total FROM MY TABLE GROUP BY MainID HAVING MainID=1234 Which one is preferred, or...more >>

I have to extract emails from a table that we have on our website, in order to run some reports on them. Right now, I have the following as an "edit" to determine if I have a "legitimate" email address: WHERE (a.u_email_address LIKE '%@%' and a.u_email_address LIKE '%.%')and ...more >>

CREATE TABLE table1 (sopnumber int) CREATE TABLE table2 (sopnumber int, DistType int) INSERT INTO table1 VALUES (1) INSERT INTO table1 VALUES (2) INSERT INTO table1 VALUES (3) INSERT INTO table2 sopnumber, DistTypeVALUES (1,10) INSERT INTO table2 sopnumber, DistTypeVALUES (1,15) INSERT IN...more >>

what is the syntax for renaming a table in sql server? i know how to do this from management studio (right-click on table name and rename it..) but i want to do this programmatically. tia, jt ...more >>

Hello, I have been researching this problem all day and finally decided to ask for your assistance. I am receiving the below error when I try to delete a row in the Primary table. I have one child record in the tblUser that is related to the row I want to delete. I still want to keep the...more >>

Hi, Is there any function to convert Julian date to Standard date. example: julian date = 167 I want to convert into standard date format i.e 06/16/2006. Thanks! Reddy...more >>

OK I've been wracking my brains out for about 1/2 an hour trying to figure out the best way to replace this kludgy cursor I inherited with a more efficient set-based approach. I've simplified the DDL and sample data below, but here's the gist of it: 1. Items (in this case books) are order...more >>

I would like to put forth that Sql Server REALLY NEEDS before triggers. (I am currently working with Sql Server 2005, but on a 2000-compatible database) I am trying to do something that would take about 3 lines of code with a before trigger, and i am running into what is a programming and mai...more >>

After our recent upgrade, all of our sps that contained queries that joined to a table-valued udf performed significantly worse (i.e. from < 3sec to > 40sec). I have since replaced the udf with temp tables (within the sps), but I'm still curious about why there was such a dramatic difference ...more >>

I am looking to use Cursors to do a compare of the current record and the one after that to check if a variable in Record 1 is less than the same variable in Record 2. I need to continue this to the EOF. If the variable not less I need to flag that record....more >>

I am trying to do a stored procedure to delete & demote records. I have a table with Client/Matter #'s where sometimes the matter number needs to be deleted and so all matters above need to be demoted. For instance, my client number is 113245, and I have matters 1, 2, 3, 4, 5, and so on. M...more >>

I'm trying to use the EXEC command to concatenate a dynamic SQL statement to which I would pass a single parameter, a ticker symbol. Here's the procedure: ALTER PROCEDURE [dbo].[proc_OpenExcelWksht] ( @ETF varchar(3)) AS BEGIN -- -- SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', ...more >>

Hi there, We have table ARTran with RefNbr column that contain duplicate numbers I tried to erase all the numbers and give them my sequence number look like this Remember RefNbr Char(10) PK 1) Step one make RefNbr = null Update ARTran set RefNbr = '' where CustId = 'CABGRE50' Setp 1...more >>

I have written 2 CLR stored procedures. I have a local copy of our database to which I was able to successfully deploy them. When I changed the connection string to our production database, it fails when I try to deploy. It gives me an error... EXTERNAL ACCESS ASSEMBLY permission denied on ob...more >>

I've suddenly been getting this when trying to insert rows from a stored procedure in QA: INSERT failed because the following SET options have incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER'. If I run the following before hand (which is also being run in the application I'm building), ...more >>

SQL Server 2000 SP4 Database has table with image field, it stores text to be viewed through Stephen Lebans RTF2 control. tbl_criteria ( ct_text_1 image, ct_auto int ) I've spent a while formatting the first field and would not like to update all records to have the image field = this...more >>

I'm trying to puzzle out a problem. I am looking at an app that uses two ADO connections to a SQL2K DB. One writes items to an Error table and the other updates the schema, including recreating(DROP/CREATE TABLE) that Error table, in a transaction. During the transaction, the first connect...more >>

How do I query to find the unmatched records from both of these two queries? I want to know what ABA_NR in Table1 but not in Table2 and vice versa. Select ABA_NR FROM Table1 Where SURV_YY_DT = 2006 Group by ABA_NR Select ABA_NR FROM Table2 Where SURV_YY_DT = 2006 Group by ABA_NR Th...more >>

Hi, I have the following select select RIGHT(REPLICATE('0',7) + CAST(Val AS VARCHAR(7)),7) AS Val from dbo.tempTable where prod_name = 'TEST' but it returns 00000-2 instead of -000002. How can I correct this. The value in the table is -2. Thanks...more >>

Hi to All! If i replace NULL with the value of any column say column with image type can database size gets small or not. because in my case it didn't happen. Regards, Farid *** Sent via Developersdex http://www.developersdex.com ***...more >>

Hello, I've been working on creating unit test in SQL for some stored procedures. I have a stored procedure that basically returns a recordset sorted based on the @SortCol paramater. See below: Alter Procedure [dbo].[MyStoredProcedure] @param1 VarChar(40) , @param2 Integer = Nul...more >>

After I have added a new column to a table my UDF wasn't selecting the new column. I had to recreate it in order to pull new column. What is a way to refresh SQL to have it working with new columns every time I modify structures? Thanks...more >>

All, I have set a transaction lock on an Sql Server 2005 database as below: transaction = connection.BeginTransaction(IsolationLevel.RepeatableRead); I keep the connection and transaction open while processing data (done deliberately). When I launch another application I can read the re...more >>

I need some advice. Basically, we have two databases: One is Oracle 9 for OLTP and the other is MS SQL for reporting. We plan to export all changes in Oracle to text files, FTP to the MS SQL, and then load into MS SQL. I need some guidance on best practice: 1. When Oracle exports files t...more >>

What is the syntax to call a stored procedure from outside of MSSQL and 1: How and where in the call do you define the login and password when fully qualifing the server, database, and tables to do a select? 2. The data selected then has to be inserted into another set of table on another s...more >>

Hi everyone, I'm new to SQL language. I have to insert a string in to a database tabel which contains single qoute('), double qote("). When I write the insert statement it gives errors. Please someone help me on this. INSERT INTO [COMMENT_DATABASE.MDF].[dbo].[Comments] ([Publishe...more >>

there is the list of all the tables of your database on the left you can see the names of the table, and on the right, you can see the date created ... right? my question is: is there a way to sort/order this list to see somehow a "modified date" for the tables? i mean i don't really need ...more >>

Hi, I have date field values in the format "2007-03-25 10:20:30". What is the format that needs to be set for SET DATEFORMAT <format>? Is there a format that will consider that date and the time? Thanks JP ...more >>

Hi, I want to UNION few tables and perform delete from, but it said syntax error at "(". How can I fix this. DELETE FROM (Select * from tblA UNION select * from tblB) as tbl WHERE X='something' Thanks. magix ...more >>

Hi, I want to UNION few tables and perform delete from, but it said syntax error at "(". How can I fix this. DELETE FROM (Select * from tblA UNION select * from tblB) as tbl WHERE X='something' Thanks. magix ...more >>