|
Yes I understood that, but the question is from what property of a SQLException did you take the number? If you took it from ErrorCode, it is not the number for the error, but only a handle to the result which will change every time.
Is the number you posted taken from Number property of a SQLException? Refer to: SqlException Members[^]
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
I would have thought this is a client issue, the error and the text of the error is available in VS and can be reported through normal error trapping. I know this as we get timeout errors every day, they are reported and someone gets slapped for running parallel processes.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
if i use asc or desc then it gives error, iwant asc and desc both ?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <author,,name>
-- Create date: <create>
-- Description: <description,,>
-- =============================================
ALTER PROCEDURE GetPurchaseOrders
-- Add the parameters for the stored procedure here
(
@Order_no bigint,
@VendorId int,
@FromDate datetime,
@ToDate datetime,
@Sort_by varchar(15),
@Sort_order varchar(4)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT p.Order_No, v.Vendor_Name, Order_date from
Purchase p
join
Vendor as v
on v.Vendor_ID=p.Vendor_ID
where (@Order_no is null or @Order_no=p.Order_No)
And
(@VendorId is null or @VendorId=p.Vendor_id)
And
(@FromDate Is Null OR @ToDate Is Null
OR (@FromDate Is Not Null AND @ToDate Is Not Null
AND p.order_date BETWEEN @FromDate AND @ToDate)
)
And
p.delivered='N'
ORDER BY
CASE @Sort_order
WHEN 'Asending' THEN
CASE @Sort_by
WHEN 'Order No' THEN ORDER_NO
WHEN 'Vendor' THEN v.Vendor_Name asc
WHEN 'Date' THEN p.order_date asc
END
WHEN 'Descending' THEN
CASE @Sort_by
WHEN 'Order No' THEN ORDER_NO DESC
WHEN 'Vendor' THEN v.Vendor_Name DESC
WHEN 'Date' THEN p.order_date DESC
END
END
END
GO
“You will never be a leader unless you first learn to follow and be led.”
–Tiorio
"Coming together is a beginning, staying together is progress, and working together is success." Henry Ford
|
|
|
|
|
You cannot have a case statement for the sort. You can either have multiple selects, create dynamic sql, or let your application sort the data.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I'd do this in 2 steps, the first being to put the results into a temp table with an additional column named 'sort_by' in the Select list. This column will be the value to sort by, but you'll have to cast to a common denominator, so to speak (looks like it cold be numeric, character, or datetime). So, you'll have the following new field for your query:
select *,
CASE @Sort_by
WHEN 'Order No' THEN cast(ORDER_NO as varchar)
WHEN 'Vendor' THEN cast(v.Vendor_Name as varchar)
WHEN 'Date' THEN cast(p.order_date as varchar)
END [sort_by]
into #temp
from ....
So the results of the query will then be in a temp table, let's just call it #temp. Then all you would need is an IF to select all the records from #temp to apply the ASC or DESC sort:
IF @Sort_order = 'Ascending' THEN
BEGIN
select * from #temp order by [sort_by] asc
END
ELSE
BEGIN
select * from #temp order by [sort_by] desc
END
If you don't want the [sort_by] field in the result set, replace 'select *' with a field list of each field except the [sort_by] field. Don't forget to drop the temp table, or use a table variable instead.
Keep It Simple Stupid! (KISS)
|
|
|
|
|
But is this method fast because on every click on search button in asp.net, it will generate a temp table.
“You will never be a leader unless you first learn to follow and be led.”
–Tiorio
"Coming together is a beginning, staying together is progress, and working together is success." Henry Ford
|
|
|
|
|
The performance of my previous suggestion would depend on how large you anticipate the dataset to be. If you really wanted to shy away from using a temp table, you could wrap the first query inside another query that will do the sorting with no temp table. The idea would be kind of like this:
IF sort_ascending THEN
BEGIN
select t.*
from (select ..., case when ... [sort_by] from ...) t <--- this is the main query
order by t.[sort_by] asc
END
ELSE
BEGIN
select t.*
from (select ..., case when ... [sort_by] from ...) t <--- this is the main query
order by t.[sort_by] desc
END
The 'select t.* ...' query just selects all the fields of the main result set and then applies the ordering to it.
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Hi All,
Im using SQL Reporting Serivces for the first time.
I ve placed Line in Group for designing purpose and header text also in the group..Bt the line displays only for a First group..I have given repeat header in the group property
the samne problem if i keep in header also
I need to display the line for all pages in Header.
|
|
|
|
|
Hi, i Have a table which contains some fields, and i want to search all the data from the table by a specific criteria such as category or passport num.or Name. and my asp page has an control which uploads a CV in word format and stores into a folder lacation given by me. but i want that CV should display when i search for the specific criteria along with the details of the candidate. I'm new to the Technology Plz help me. for writing the code.
B.B.Nayak
|
|
|
|
|
Presumably you have passport number, name etc in specific fields, so a satndard select should do it for you.
As for the rest, you either write the code yourself and ask specific questions or hire someone to do it. This is not a free code shop.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Select me From NeedHelp.Your.checkbook where [your knowledge] = IsNull
The world is a stage and most of us are desperately unrehearsed.
—Sean O’Casey, Playwright
|
|
|
|
|
hi i have a dataset which consists of one tables. i need to filter this data set and store it in another table. how can i do this?
|
|
|
|
|
Easy
Select * into newtable from oldtable where 1 = 2 -- create empty table
insert into newtable select * from oldtable where ....
Although you can do select * into newtable from oldtable where.. this is bad practice as it locks the system tables for the duration of the query.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
|
Hello all,
Is there an easy way to copy a record from one database to another database (that have the exact structure) in sql server 2000 using c# ?
The record have some Foreign keys with the other tables in the DB.
The only I could think of is to export the record to a xml file, and then import that xml file to the destination db.
thank,
berlus
|
|
|
|
|
I am using SQL server 2008 in one comp and if i want to bakup the database and use it in sql 2005 it is showing some errors like format not compatable.
Is there a way to fix it and work it on 2005.
Santhapur
|
|
|
|
|
Restoring a backup to SQL Server 2005 which is taken from SQL Server 2008 isn't possible. The format is simply different.
One way to overcome this is to build an extra SQL Server 2008 instance, restore the backup there and then for using for example Export/Import you transfer the schema and the data from 2008 to 2005. There are several tools and options which you can use for the transfer (SSIS, scripting the database, bcp etc)
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
|
Restore a SQL Server 2008 Database into SQL Server 2005
If you've tried to back up a database in SQL Server 2008 and then restore it into SQL Server 2005, you know that the database backups are not backward compatible. However, with SQL Server 2008 Management Studio, you can script data and schemas in SQL Server 2005 mode. This 10-Minute Solution takes you through the steps to back up the Northwind database on SQL Server 2008 and restore it to SQL Server 2005.
The Problem:
Transferring databases from SQL Server 2008 to SQL Server 2005 is not possible with standard backup and restore facilities.
The Solution:
Leverage the scripting wizard in SQL Server 2008 to script data as well as schemas in SQL Server 2005 compatibility mode.
Using the "Generate SQL Server Scripts" Wizard
The Northwind database is no longer shipped as part of the SQL Server installation, but you can download it from go.microsoft.com. The data is scripted as INSERT statements.
To create the scripts, you have to run the "Generate SQL Server Scripts" wizard, which you can run within SQL Server Management Studio (once Object Explorer is connected to the appropriate instance) by right clicking on the database and selecting "Tasks –> Generate Scripts."
Figure 1 shows the initial dialog to the wizard. Click "Next" and complete the following steps in the wizard to back up the Northwind database on SQL Server 2008 and restore it to SQL Server 2005
Figure 1. Initial Dialog to Generate SQL Server Scripts Wizard: To create your scripts, you have to run the "Generate scripts" wizard.
Click "Script all objects in the selected database" (see Figure 2), and then click "Next."
Figure 2. Select Database Dialog: Click the "Script all objects in the selected database" option.
Amend the script options: Specifically, set "Script for Server Version" to "SQL Server 2005" and set "Script Data" to "True" (see Figure 3). (SQL Server 2000 is also supported.) If you are putting the database on a new instance for the first time, make sure the "Script Database Create" option is set to "True." Click "Next" when you are happy with the options.
Figure 3. Choose Script Options Dialog: Set the "Script Data" option to "True."
Select "Script to file," select the file name, and choose "Single file" (see Figure 4). Click "Next" for a summary (see Figure 5).
Figure 4. Output Option Dialog: Select "Script to file," select the file name, and choose "Single file."
Figure 5. Script Wizard Summary: Review your selections for the wizard.
Now click on "Finish" to get progress messages while the script runs and completes (see Figure 6)
Figure 6. Generate Script Progress Dialog: Click "Finish" and you will get progress messages.
If the generation process fails, then you can use the "Report" option to see why.
When the scripting is completed, look for the following lines:
CREATE DATABASE [Northwind] ON PRIMARY
(NAME = N'Northwind', FILENAME =
N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\northwnd.mdf' ,
SIZE = 3328KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
(NAME = N'Northwind_log', FILENAME =
N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\northwnd.ldf' ,
SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
You will need to amend the paths to a valid path for the computer on which you are running. You also will need to comment out the following lines like this:
--EXEC sys.sp_db_vardecimal_storage_format N'Northwind', N'ON'
--GO
Once you have made these changes, you can run the script in SQL Server 2005 Management Studio to recreate the database in your development environment. You can now test data against SQL Server 2008 and SQL Server 2005.
The Inevitable Limitations
Of course, this technique is not without its limitations. Here are a few to bear in mind:
The data is insecure, as it is in clear readable text. So if you are using real data, you should delete the file created once you have loaded it into SQL Server 2005. You can regenerate the file from the SQL 2008 backup, if necessary.
If you have a database with a large amount of data, the script file, of course, will be huge.
SQL Server 2008 specifics in the source database will not be migrated.
|
|
|
|
|
Hi all,
Iam wrking in sql reporting services .In that i want to find the COUNT OF male and female in report .I have used an expression fielD to find count of male and female but it returnS both the count of male and female ..IE (TOTAL ROWS VALUE)
I have pasted my expression FIELD
=Count(Fields!GENDER.Value="MALE")
=Count(Fields!GENDER.Value="FEMALE")
PLS ANY ONE GIVE SOLUTION FOR THIS
|
|
|
|
|
could you use
=SUM(IIF(fields!GENDER.value = "MALE", 1, 0))
=SUM(IIF(fields!GENDER.value = "FEMALE", 1, 0))
|
|
|
|
|
Thanx a lot .it worked well
|
|
|
|
|
hello guys ! i need a report where i need data from two tables.here is the scenario.i have a table Ip-to-country which contains most ipnumbers ranges and th country which i appended a new field for iddcode.the second table is text_status tables which contains field like mobilenumber and other stuffs not needed for now.I want to do a report where it would be easier to have iddcode field in the text_status table which would be the iddcode of the mobilenumber.
what is chalenging to me is how to insert the corrresponding iddcode for the mobilenumber.
knowing that iddcode are from 1 digits to 4. ex 1 for us, 44 for uk, 359 for bulgaria and 1441 for bermuda.
as you can see bermuda and us start by one.so i'm confused about the way to go to know for which country a mobilenumber is for.thanks for reading this.
Ps: i even tried to substring the mobilenumber but at the end the problem is still the same.
Need some help please.thanks
eager to learn
|
|
|
|
|
Probably more than one way to go about something like this, and none of which are really very clean.
Basis of anything is, that if 1441 matches the start of the mobile number then it can't be 1 for U.S.
You could match these back in 4 iterations, starting with the 4 digit codes first, then exluding what you had matched when you do your 3 digit matches and so on.
You could code this on the fly, but would overcomplicate it really, could you add another field onto your text_status table, and update it with the iddcode where you get a match on all 4 digit iddcodes.
Then update the field with all matching 3 digit iddcodes where the field is still null, then 2 digit, then 1 digit.
|
|
|
|
|
thanks for your answer.it's makes me see other things that did appeared to me at first.i'll get back to you
eager to learn
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
