Many of us sometimes got stuck when our application requires flexible search mechanism where one or all of the search parameters can be NULL. These type of queries requires you to implement a mechanism which takes care of all possible combinations of input to the stored procedure. Following...more >>

I have a statement in my stored procedure that has the following as an example - INSERT #TempTableOutput EXEC (@select + @from + @where + @order) How could I then loop through the records of #TempTableOutput to update some blank fields that were created in the intial select statement?...more >>

Hi, One of my apps synchronise two databases. In some case, the number of elements could be important ( > 10 000) for my area ... Elements are insert, update or delete depending on what happened on the first database ... In order to accelerate SELECT on some tables, indexes has been created...more >>

hi i have a stored proc, pointed by a synonym i wish to execute it vía: cmd.commandType= adStoredProc cmd.commandText= "s_MyStoredProc" cmd.parameters.refresh ---> to get the collection the last line, can't retrieve the Parameters[] collection if i execute the stored proc directly the ...more >>

I have a table with an identity field (RecordNumber, int), among other fields. Our vendor would like us to produce a tilde(~) delimited file of this table. They'd like us to present the values in the identity field as a 15 digit long value with preceding zeros. For instance, an identity val...more >>

I have Table with Primary and Foreign key fields of varchar type. Foreign key field references to the same table. Table contains Financial Accounts data. Accounts can be independent and can be like children of another account. So, if for instance Foreign key field contains NULL then this acc...more >>

This list all rows with a one or more dup. select BillNumber from pineiro_lec_new group by BillNumber having count(*)>1 How can I return the total number of rows with dups? ...more >>

I need to take the data from the following table and populate a new table with row number colmun based on an objid EXAMPLE I have the following table OBJID FinClass RcvDate TransID AMT 1 1 01/01/1990 34 17.00 1 ...more >>

Hello All, I'm new to all SQL so please forgive me if I've missed something obvious. With SQL 2005. I have a C# stored procedure which works fine. I then modified the C# code to call into an unmanaged (C++) DLL. I've set this up to use PInvoke to get into the unmanaged DLL (which is ca...more >>

hey all, i'm going over a Data Tutorial that showed the following statement: SELECT ProductName, UnitPrice FROM Products WHERE UnitPrice <= @MaximumPrice OR @MaximumPrice = -1.0 According to the documentation if @MaximumPrice = -1.0 then all records will be returned. Huh? How does tha...more >>

Is there a compelling reason to preface column names with table names? E.g. Author -------- AuthorID AuthorName Instead of... Author ------- ID Name I was agreeing with the prefacing this morning because it would distinguish each column clearly when doing a SELECT * when join ta...more >>

Thank you for quick response on my original posting and I have different question. How do you write SQL queries in French, Gemran and Italian? Do you use command or period for decimals? Do you say: INSERT INTO SomeTable (DoubleColumn1, StringColumn2) VALUES (123...more >>

I have been talked into (convinced) to preface all column names with the table name: Author ------ AuthorID AuthorName Book ---- BookID BookTitle AuthorID (assume only one author per book for this example, please) The reason is valid and I agree with it (although it doesn mean mo...more >>

Hello, I have been asked to log the "computer" startup and shutdown time to a SQL server database. I have created a Kixtart computer shutdown script that, during computer shutdown, writes an entry to a text log file on a server share as well as to a SQL server database. As this event is a ...more >>

When I do a full backup on a database, i guess this error Msg 9987, Level 16, State 1, Line 1 The backup of full-text catalog 'PhraseologyIndex' is not permitted because it is not online. Check errorlog file for the reason that full-text catalog became offline and bring it online. Or BACKUP ...more >>

I am new to internalization and I have ran into a problem recently. I have a SQL query generator which does something like: 1. Read double value from textbox 2. Convert it to double for some calculations. 3. Convert it back to string from double. 2. Attache the value to...more >>

In an SP I have a SELECT that returns only one row and one column (if it exists) and places the result in a parameter that I use later in calculations like, SELECT @int = ISNULL(IntegerValue,0) FROM TableName WHERE FirstColumn = 'One' AND OtherColumn = 'Red' AND UniqueID = '123...more >>

I use a data layer for almost all my db calls I use a business layer for almost all my generally needed rules both are in a series of classes, loginfunctions for user authentication and authorization settings, chartfunctions for rules concernng global stuff, datafunctions, and ... I've read...more >>

Hello I have a programmer who wants to retrieve a list of items from three different tables, returning them in one result set. The problem is the sort orders are going to be different for each query. The row counts returned are very small, but this will be used on a high volume web site. ...more >>

I have a database with hundreds of tables. Sometime I want to find tables with particular text in the name. Is there a way for me to use system tables to search for text in a table name. The query would be something like "Select * from TableNames where tableName like '%DEF%' Thank yo...more >>

I need to select the last 3 rows for each code group - by date. I am thinking some along the lines of select top 3 * from #tmp1 where code = 'a' order by vDate desc But this will return only the rows for code 'a'. How do I retrieve the rows for codes 'b' and 'c'? I am thinking something...more >>

Hi, Has anyone encountered the following error when trying to create and indexed view. Create failed for Index 'IDX_RHFROMPAUD'. (Microsoft.SqlServer.Express.Smo) Cannot create index on view 'MYRIAD2.dbo.RHISTORY_FROM_PAUD' because column 'Quantity' that is referenced by the view in th...more >>

Let's say that I have a table, Table1, which has two fields: Field1, Field2. I want to write a query that will return those two field values in one field with a carriage return/linefeed between them. For example, using VB syntax: SELECT Field1 + vbCrLf + Field2 FROM Table1 How could I acco...more >>

Thankfully, this is in a test environment, and is not causing me a live issue, but it got me thinking: I've got one database on my server that I noticed was in the middle of a large transaction that it had been working inside for several hours. At this point, I knew that the output from the tr...more >>

Hello, In sql_server 2005, I would like to convert a binary in string and vice-versa like this 73C5E(binary) -> '73C5E' nchar(5) and '73C5E' nchar(5) -> '73C5E'. I have to make multiple comparisons between binaries and strings and I must use ansii strings somewhere else. is anyone can ...more >>

I have a table that potentially can hold billions of rows and hence I was wondering if I can use a BIG INT instead of INT as the primary key. Are there any adverse effects on SELECT statements (performance) becuase of the larger size (8bytes) of the BIG INT? Thanks in advance. ...more >>

Dear all, I need to define an auto-increment column which will be used as a primary key . If I defined that column field as INT and then configurred the Seed value to 100. What will happen if the auto increment field goes over the INT type ? how can it be reaaly uinique with the time ? ...more >>

Hi, As you can see the following two tables have different data. So the IDs will vary. Can you let me know the sql query to update table1 Thanks ----------------- Table1 IndexID Name ParentIndexID 1 BR DATED NULL 2 BR IPE NULL 3 BR NYMEX NULL 4 DUBAI NULL 5 F10MEDCC NULL 6 F10MEDFC...more >>

Hi All, please let me know if there is any way i can rollback the update query? i am using sql server and my auto commit option is on......... please help... ...more >>

Dear Members Hi, I am a little confused about making backup of a very large database. let me explaine Suppose that we have a very large database. In our database there is a bulky table called "TTable". It is placed in a filegroup. Every day new bulk data is added to TTable. Remaining tables in...more >>

Hi all. A new litle chalenge. I cant seem to get the pipe sign as fieldseparator (|). Gets the following error using -t| -t\| *** '-CDanish_Norwegian_CS_AS' is not recognized as an internal or external command, operable program or batch file. **** -- Thanks all Regards Geir...more >>

Hi, I have multiple tables that have an Insert applied to them when a Record is saved. I can't get into the front end so I must use a trigger to prevent a record from being entered under certain circumstances. If tbl1 is the main table, & tbl2 is the detail table, & I want to roll back t...more >>

/* Grouping Values Need to group amount on following logic 1. Grouping should be based on +0.02 or - 0.02 2. If a value is selected into a group , it should not be considered in any other group In the example below 10.04 is within .02(-) of 10.02 and also with in 0.02 (+)...more >>

Hi all. I am using this comand to export to a .txt file (to avoid usin SSIS). How do I get the fieldnames in the .txt file. I just get the data itself. Any ideas? EXEC master..xp_cmdshell 'BCP multicase..vekRptItegraFilExport out K:\MultiCase\Filexport\SQL2005Eksport\ProduktFile.txt -c ...more >>