|
How i can import excel to an existing table in SQL server
|
|
|
|
|
By using DTS, in SQLServer 2000. or Analysis Services in 2005.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
Hi,
Did u get the answer, if you are satisfied please give your rank.
Thanks,
Aleem Mohammad
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
Hopefully you're using SQL Server 2005.
Make sure you have SSIS enabled (running):
Start|All Programs|Microsoft SQL Server 2005|Configuration Tools|SQL Server Surface Area Configuration|Surface Area Configuration for Services and Connections|Intergration Services
Click ok. Close the Surface Area window.
Next launch SSMS and in the object explorer select the DB you wish to import into.
Right click|Tasks|Import
This should start the import wizard. From here you should be able figure things out.
HTH
The world is a stage and most of us are desperately unrehearsed.
—Sean O’Casey, Playwright
|
|
|
|
|
SSIS would be an easy way as Jerry Hammond guided. If you're not able to use it for some reason, you can save the excel sheet as CSV file and then use bcp utility[^]
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Yes, what jerry has suggested is right. The DTS is called as "Analysis Services" in SQL Server 2005.
If you are using SQL Server 2000 then select the Database and in tools u will find DTS packages then use import or export whichever u want. Do little much research to schedule it as job, as mentioned by myk that is called "BCP".
If you want you can have a small UI also to execute your DTS package from VB or C# code.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
|
Hi,
I have stored proc in which an SQL query is being executed which may timeout some times. I got that error using the code it is showing one number but when I searched in google for the same, but google is showing another number for query time out.
Now my question is how can I get that the query is timed out. Will that error number be in some limit from this number to this number is for query time out or it changes from OS to OS or does it depends in SQL server version.
Please give me hints about it sot that I can show the appropriate message to the user in exception. I would be very much thankfull to all of you.
Thanks,
Aleem Mohammad.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
modified on Friday, November 21, 2008 9:10 AM
|
|
|
|
|
What was the error number you got and from which SQL Server version?
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi,
The error number that I am getting from the program is : 2147184992 and in google I am getting another number.
Which should I take. And how should I resolve this issue.
Thanks,
Aleem Mohammad.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
That number sounds more like the HRESULT.
When the exception occurs, have a look at Number and Message properties of the SqlException.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi,
Thanks for the answer, whats my dialomma is if the number changes for another machine when we deploy it, then our code can not show the proper message thats why I am asking for the accurate way to catch the query timed our exceptions.
Can u please help me in this.
Thanks a Lot,
Aleem Mohammad.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
