EXEC sp_start_job Remote Server

I am trying to create a centralized job monitoring system that can be
managed by some operators to restart a job if it fails. Everything
will be running on one system but I need to invoke remote jobs on
remote system. I am having trouble starting a job on a remote system
even if I have the permissions to do so.
[Code]
DECLARE @.SQLCMD VARCHAR(MAX)
SET @.SQLCMD = ''
DECLARE @.SystemNameVARCHAR(50)
DECLARE @.JobNameVARCHAR(50)
DECLARE @.StepNameVARCHAR(50)
SET @.SystemName = RemoteServer
SET @.JobName = Explode
SET @.StepName = BadStep
SET @.SQLCMD = '' + @.SystemName + '.msdb.dbo.sp_start_job @.job_name =
''' + @.JobName + ''', @.step_name = ''' + @.StepName + ''''
PRINT @.SQLCMD
[/code]
This will generate the following
RemoteServer.msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name =
'Bad'
If i run this command as an adhoc command, it will execute on the
RemoteServer and start the job at the step. However for the SP i am
writing this does not work
[Code]
EXEC @.SQLCMD
EXEC msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name = 'Bad'
[/Code]
This error appers.
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'msdb.dbo.sp_start_job @.job_name =
'Explode', @.step_name = 'Bad''.
If I try to run it this way, I get the following error message.
EXEC msdb.dbo.sp_start_job @.server_name = 'dbdev4', @.job_name =
'Explode', @.step_name = 'Bad'
Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
Line 67
The specified @.job_name ('Explode') does not exist.
Any ideas
Thanks
-Matt-
Hi Matt
"Matthew" wrote:

> I am trying to create a centralized job monitoring system that can be
> managed by some operators to restart a job if it fails. Everything
> will be running on one system but I need to invoke remote jobs on
> remote system. I am having trouble starting a job on a remote system
> even if I have the permissions to do so.
> [Code]
> DECLARE @.SQLCMD VARCHAR(MAX)
> SET @.SQLCMD = ''
> DECLARE @.SystemNameVARCHAR(50)
> DECLARE @.JobNameVARCHAR(50)
> DECLARE @.StepNameVARCHAR(50)
> SET @.SystemName = RemoteServer
> SET @.JobName = Explode
> SET @.StepName = BadStep
> SET @.SQLCMD = '' + @.SystemName + '.msdb.dbo.sp_start_job @.job_name =
> ''' + @.JobName + ''', @.step_name = ''' + @.StepName + ''''
> PRINT @.SQLCMD
> [/code]
> This will generate the following
> RemoteServer.msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name =
> 'Bad'
> If i run this command as an adhoc command, it will execute on the
> RemoteServer and start the job at the step. However for the SP i am
> writing this does not work
> [Code]
> EXEC @.SQLCMD
> EXEC msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name = 'Bad'
> [/Code]
> This error appers.
> Msg 2812, Level 16, State 62, Line 1
> Could not find stored procedure 'msdb.dbo.sp_start_job @.job_name =
> 'Explode', @.step_name = 'Bad''.
Try:
EXEC ( @.SQLCMD )
or
EXEC RemoteServer.msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name =
'Bad'

> If I try to run it this way, I get the following error message.
> EXEC msdb.dbo.sp_start_job @.server_name = 'dbdev4', @.job_name =
> 'Explode', @.step_name = 'Bad'
> Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
> Line 67
> The specified @.job_name ('Explode') does not exist.
> Any ideas
> Thanks
> -Matt-
John

EXEC sp_start_job Remote Server

I am trying to create a centralized job monitoring system that can be
managed by some operators to restart a job if it fails. Everything
will be running on one system but I need to invoke remote jobs on
remote system. I am having trouble starting a job on a remote system
even if I have the permissions to do so.
[Code]
DECLARE @.SQLCMD VARCHAR(MAX)
SET @.SQLCMD = ''
DECLARE @.SystemName VARCHAR(50)
DECLARE @.JobName VARCHAR(50)
DECLARE @.StepName VARCHAR(50)
SET @.SystemName = RemoteServer
SET @.JobName = Explode
SET @.StepName = BadStep
SET @.SQLCMD = '' + @.SystemName + '.msdb.dbo.sp_start_job @.job_name =
''' + @.JobName + ''', @.step_name = ''' + @.StepName + ''''
PRINT @.SQLCMD
[/code]
This will generate the following
RemoteServer.msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name =
'Bad'
If i run this command as an adhoc command, it will execute on the
RemoteServer and start the job at the step. However for the SP i am
writing this does not work
[Code]
EXEC @.SQLCMD
EXEC msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name = 'Bad'
[/Code]
This error appers.
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'msdb.dbo.sp_start_job @.job_name =
'Explode', @.step_name = 'Bad''.
If I try to run it this way, I get the following error message.
EXEC msdb.dbo.sp_start_job @.server_name = 'dbdev4', @.job_name =
'Explode', @.step_name = 'Bad'
Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
Line 67
The specified @.job_name ('Explode') does not exist.
Any ideas
Thanks
-Matt-Hi Matt
"Matthew" wrote:

> I am trying to create a centralized job monitoring system that can be
> managed by some operators to restart a job if it fails. Everything
> will be running on one system but I need to invoke remote jobs on
> remote system. I am having trouble starting a job on a remote system
> even if I have the permissions to do so.
> [Code]
> DECLARE @.SQLCMD VARCHAR(MAX)
> SET @.SQLCMD = ''
> DECLARE @.SystemName VARCHAR(50)
> DECLARE @.JobName VARCHAR(50)
> DECLARE @.StepName VARCHAR(50)
> SET @.SystemName = RemoteServer
> SET @.JobName = Explode
> SET @.StepName = BadStep
> SET @.SQLCMD = '' + @.SystemName + '.msdb.dbo.sp_start_job @.job_name =
> ''' + @.JobName + ''', @.step_name = ''' + @.StepName + ''''
> PRINT @.SQLCMD
> [/code]
> This will generate the following
> RemoteServer.msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name =
> 'Bad'
> If i run this command as an adhoc command, it will execute on the
> RemoteServer and start the job at the step. However for the SP i am
> writing this does not work
> [Code]
> EXEC @.SQLCMD
> EXEC msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name = 'Bad'
> [/Code]
> This error appers.
> Msg 2812, Level 16, State 62, Line 1
> Could not find stored procedure 'msdb.dbo.sp_start_job @.job_name =
> 'Explode', @.step_name = 'Bad''.
Try:
EXEC ( @.SQLCMD )
or
EXEC RemoteServer.msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name =
'Bad'

> If I try to run it this way, I get the following error message.
> EXEC msdb.dbo.sp_start_job @.server_name = 'dbdev4', @.job_name =
> 'Explode', @.step_name = 'Bad'
> Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
> Line 67
> The specified @.job_name ('Explode') does not exist.
> Any ideas
> Thanks
> -Matt-
Johnsql

EXEC sp_start_job Remote Server

I am trying to create a centralized job monitoring system that can be
managed by some operators to restart a job if it fails. Everything
will be running on one system but I need to invoke remote jobs on
remote system. I am having trouble starting a job on a remote system
even if I have the permissions to do so.
[Code]
DECLARE @.SQLCMD VARCHAR(MAX)
SET @.SQLCMD = ''
DECLARE @.SystemName VARCHAR(50)
DECLARE @.JobName VARCHAR(50)
DECLARE @.StepName VARCHAR(50)
SET @.SystemName = RemoteServer
SET @.JobName = Explode
SET @.StepName = BadStep
SET @.SQLCMD = '' + @.SystemName + '.msdb.dbo.sp_start_job @.job_name = ''' + @.JobName + ''', @.step_name = ''' + @.StepName + ''''
PRINT @.SQLCMD
[/code]
This will generate the following
RemoteServer.msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name = 'Bad'
If i run this command as an adhoc command, it will execute on the
RemoteServer and start the job at the step. However for the SP i am
writing this does not work
[Code]
EXEC @.SQLCMD
EXEC msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name = 'Bad'
[/Code]
This error appers.
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name = 'Bad''.
If I try to run it this way, I get the following error message.
EXEC msdb.dbo.sp_start_job @.server_name = 'dbdev4', @.job_name = 'Explode', @.step_name = 'Bad'
Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
Line 67
The specified @.job_name ('Explode') does not exist.
Any ideas
Thanks
-Matt-Hi Matt
"Matthew" wrote:
> I am trying to create a centralized job monitoring system that can be
> managed by some operators to restart a job if it fails. Everything
> will be running on one system but I need to invoke remote jobs on
> remote system. I am having trouble starting a job on a remote system
> even if I have the permissions to do so.
> [Code]
> DECLARE @.SQLCMD VARCHAR(MAX)
> SET @.SQLCMD = ''
> DECLARE @.SystemName VARCHAR(50)
> DECLARE @.JobName VARCHAR(50)
> DECLARE @.StepName VARCHAR(50)
> SET @.SystemName = RemoteServer
> SET @.JobName = Explode
> SET @.StepName = BadStep
> SET @.SQLCMD = '' + @.SystemName + '.msdb.dbo.sp_start_job @.job_name => ''' + @.JobName + ''', @.step_name = ''' + @.StepName + ''''
> PRINT @.SQLCMD
> [/code]
> This will generate the following
> RemoteServer.msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name => 'Bad'
> If i run this command as an adhoc command, it will execute on the
> RemoteServer and start the job at the step. However for the SP i am
> writing this does not work
> [Code]
> EXEC @.SQLCMD
> EXEC msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name = 'Bad'
> [/Code]
> This error appers.
> Msg 2812, Level 16, State 62, Line 1
> Could not find stored procedure 'msdb.dbo.sp_start_job @.job_name => 'Explode', @.step_name = 'Bad''.
Try:
EXEC ( @.SQLCMD )
or
EXEC RemoteServer.msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name ='Bad'
> If I try to run it this way, I get the following error message.
> EXEC msdb.dbo.sp_start_job @.server_name = 'dbdev4', @.job_name => 'Explode', @.step_name = 'Bad'
> Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
> Line 67
> The specified @.job_name ('Explode') does not exist.
> Any ideas
> Thanks
> -Matt-
John

Excel Import to sqlserver database

Hi all,

this is thiru from India, hope i shall get answers here for my questions.

1. I need to import an Excel spread sheet to a remote sql server database through ASP.Net web application. I brief the process im following now please go through it.

Import Process:

a. select a fiile(.xls) and upload it to server.
b. using M/S Odbc Excel driver, and the uploaded excel file as datasource,
c. query the excel sheet to populate a dataset.
d. iterate through the rows of the dataset(I could not bulk copy the excel data, because
have to check the database, if record exists then update, else insert) to import to the
SQL Database

Performance issues:
1. I have to import spreadsheets having upto 60,000 records or even more at a time.
2. Is this a good option to use a webapplication for this task (I use this approach because
my boss wants to do so).
3. some times the excel file size grows up to 7 mb(Though i shall adjust config settings,
uploading and then querying a 7 mb file shall be an ovverhead i think.)
4. is there any possibility to get the datasource with out uploading the file to the server (Like
modifying the connection string as "datasource=HtmlFileControl.PostedFile" instead,)(I
tried this but it gives me "unspecified error").

please analyse my problem and suggest me a possible solution.
I thank all, for your efforts, of any kind.
have a nice time,
........thiru

hi,

you can directly import excel

to sql server import table

do the processing from the import table

to the staging table.

then from the staging table load it directly to the destination table.

i suggest you use SSIS or DTS or you can linked server

to excell and do the processing with sql server.

the problem with your approach is that you might encounter a dataset limitation.

there is no restartability feature. if your application breaks in the middle of the load process

where half of the data has been loaded it would be a great nightmare.

the first above mentioned approach is used widely in BI and datawarehousing.

here's some explanation on how it works:

1. you import the data to an import table. the import table shall be used

for processing the data .this liberates the datasource from heavy processing

and migrates the proceessing to SQL. if the import fails you can truncate the import table

and re import the data. this can done easily without harming the production.

the idea here is to first load everything into sql server.

2. stage the data to the staging table from the imports. here you do data cleansing

the burden nows reside on the sql server which is not a burden at all but a big problem to asp

from your previous design. Resatrtability. truncate the stage table and do the processing from either

step 1 or 2.

3. bulk insert from the stage to the production table. here your very sure that you are inserting a very clean data

with great speed.

|||Hello,
Thanls for your efforts, I have proceeded a long way through the application and my most recent challenge is this:

please let me find some help.

For a couple of weeks i am struggling with an excel import and export application to sqlserver as i have to do it through an external application and not through the Export Import Utility in sql server.
My most recent problem is:
The understanding of field values by sql server and excel.
1. I need to import into a table whose fields are varchar type.
2. I have to use as datasource- an Excel sheet(.xls file).
3. Some fields in excel contain numbers (Length up to 10 or
more digits).
4. For this I first format the cells'(in Excel file) datatype
into "Text".
5. Even then after importing into a table(say tblTemp) whose
schema/structure, is

CREATE TABLE [tblTemp] (
[rowId] int IDENTITY(1,1),
[Account Number] varchar(30) not null ,
[Mobile Number] varchar(30) null,
[Name] varchar(100) null
)
, and to import into this table im using select into query
with datasource as the excel file(Data Source=ExcelFile.xls),
the table fields show exponential values(may be float
datatype).
Shall any one suggest me how shall i get values from excel sheet with datatype varchar and import into the above said table as varchar values (with out any exponential types).|||

I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:

Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Here are some other tutorials that may be of interest:

Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory

Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema

The spreadsheet has a schema and you want to make sure it is formatted properly.

Regards,

Dave

|||

David Hayden wrote:

I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:

Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Here are some other tutorials that may be of interest:

Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory

Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema

The spreadsheet has a schema and you want to make sure it is formatted properly.

Regards,

Dave

I noticed that the codings are in C#.... is there any codings examples doing the same process using VB?

|||

hi thiru

how r u ?

iam facing same problem if have solution please suggest me

thanking u

with regards

purushotham.T

|||The easiest way I found of importing data from an excel sheet to sql server is saving the sheet as a tab delimited text file and then importing it to sql server. View this microsof issue :http://support.microsoft.com/kb/236605
Hope this helps

Excel Import to sqlserver database

Hi all,

this is thiru from India, hope i shall get answers here for my questions.

1. I need to import an Excel spread sheet to a remote sql server database through ASP.Net web application. I brief the process im following now please go through it.

Import Process:

a. select a fiile(.xls) and upload it to server.
b. using M/S Odbc Excel driver, and the uploaded excel file as datasource,
c. query the excel sheet to populate a dataset.
d. iterate through the rows of the dataset(I could not bulk copy the excel data, because
have to check the database, if record exists then update, else insert) to import to the
SQL Database

Performance issues:
1. I have to import spreadsheets having upto 60,000 records or even more at a time.
2. Is this a good option to use a webapplication for this task (I use this approach because
my boss wants to do so).
3. some times the excel file size grows up to 7 mb(Though i shall adjust config settings,
uploading and then querying a 7 mb file shall be an ovverhead i think.)
4. is there any possibility to get the datasource with out uploading the file to the server (Like
modifying the connection string as "datasource=HtmlFileControl.PostedFile" instead,)(I
tried this but it gives me "unspecified error").

please analyse my problem and suggest me a possible solution.
I thank all, for your efforts, of any kind.
have a nice time,
........thiru

hi,

you can directly import excel

to sql server import table

do the processing from the import table

to the staging table.

then from the staging table load it directly to the destination table.

i suggest you use SSIS or DTS or you can linked server

to excell and do the processing with sql server.

the problem with your approach is that you might encounter a dataset limitation.

there is no restartability feature. if your application breaks in the middle of the load process

where half of the data has been loaded it would be a great nightmare.

the first above mentioned approach is used widely in BI and datawarehousing.

here's some explanation on how it works:

1. you import the data to an import table. the import table shall be used

for processing the data .this liberates the datasource from heavy processing

and migrates the proceessing to SQL. if the import fails you can truncate the import table

and re import the data. this can done easily without harming the production.

the idea here is to first load everything into sql server.

2. stage the data to the staging table from the imports. here you do data cleansing

the burden nows reside on the sql server which is not a burden at all but a big problem to asp

from your previous design. Resatrtability. truncate the stage table and do the processing from either

step 1 or 2.

3. bulk insert from the stage to the production table. here your very sure that you are inserting a very clean data

with great speed.

|||

Hello,
Thanls for your efforts, I have proceeded a long way through the application and my most recent challenge is this:

please let me find some help.

For

a couple of weeks i am struggling with an excel import and export

application to sqlserver as i have to do it through an external

application and not through the Export Import Utility in sql server.
My most recent problem is:
The understanding of field values by sql server and excel.
1. I need to import into a table whose fields are varchar type.
2. I have to use as datasource- an Excel sheet(.xls file).
3. Some fields in excel contain numbers (Length up to 10 or
more digits).
4. For this I first format the cells'(in Excel file) datatype
into "Text".
5. Even then after importing into a table(say tblTemp) whose
schema/structure, is

CREATE TABLE [tblTemp] (
[rowId] int IDENTITY(1,1),
[Account Number] varchar(30) not null ,
[Mobile Number] varchar(30) null,
[Name] varchar(100) null
)
, and to import into this table im using select into query
with datasource as the excel file(Data Source=ExcelFile.xls),
the table fields show exponential values(may be float
datatype).
Shall

any one suggest me how shall i get values from excel sheet with

datatype varchar and import into the above said table as varchar values

(with out any exponential types).|||

I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:

Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Here are some other tutorials that may be of interest:

Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory

Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema

The spreadsheet has a schema and you want to make sure it is formatted properly.

Regards,

Dave

|||

David Hayden wrote:

I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:

Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Here are some other tutorials that may be of interest:

Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory

Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema

The spreadsheet has a schema and you want to make sure it is formatted properly.

Regards,

Dave

I noticed that the codings are in C#.... is there any codings examples doing the same process using VB?

|||

hi thiru

how r u ?

iam facing same problem if have solution please suggest me

thanking u

with regards

purushotham.T

|||The easiest way I found of importing data from an excel sheet to sql server is saving the sheet as a tab delimited text file and then importing it to sql server. View this microsof issue :http://support.microsoft.com/kb/236605
Hope this helps

Excel Import to sqlserver database

Hi all,

this is thiru from India, hope i shall get answers here for my questions.

1. I need to import an Excel spread sheet to a remote sql server database through ASP.Net web application. I brief the process im following now please go through it.

Import Process:

a. select a fiile(.xls) and upload it to server.
b. using M/S Odbc Excel driver, and the uploaded excel file as datasource,
c. query the excel sheet to populate a dataset.
d. iterate through the rows of the dataset(I could not bulk copy the excel data, because
have to check the database, if record exists then update, else insert) to import to the
SQL Database

Performance issues:
1. I have to import spreadsheets having upto 60,000 records or even more at a time.
2. Is this a good option to use a webapplication for this task (I use this approach because
my boss wants to do so).
3. some times the excel file size grows up to 7 mb(Though i shall adjust config settings,
uploading and then querying a 7 mb file shall be an ovverhead i think.)
4. is there any possibility to get the datasource with out uploading the file to the server (Like
modifying the connection string as "datasource=HtmlFileControl.PostedFile" instead,)(I
tried this but it gives me "unspecified error").

please analyse my problem and suggest me a possible solution.
I thank all, for your efforts, of any kind.
have a nice time,
........thiru

hi,

you can directly import excel

to sql server import table

do the processing from the import table

to the staging table.

then from the staging table load it directly to the destination table.

i suggest you use SSIS or DTS or you can linked server

to excell and do the processing with sql server.

the problem with your approach is that you might encounter a dataset limitation.

there is no restartability feature. if your application breaks in the middle of the load process

where half of the data has been loaded it would be a great nightmare.

the first above mentioned approach is used widely in BI and datawarehousing.

here's some explanation on how it works:

1. you import the data to an import table. the import table shall be used

for processing the data .this liberates the datasource from heavy processing

and migrates the proceessing to SQL. if the import fails you can truncate the import table

and re import the data. this can done easily without harming the production.

the idea here is to first load everything into sql server.

2. stage the data to the staging table from the imports. here you do data cleansing

the burden nows reside on the sql server which is not a burden at all but a big problem to asp

from your previous design. Resatrtability. truncate the stage table and do the processing from either

step 1 or 2.

3. bulk insert from the stage to the production table. here your very sure that you are inserting a very clean data

with great speed.

|||Hello,
Thanls for your efforts, I have proceeded a long way through the application and my most recent challenge is this:

please let me find some help.

For a couple of weeks i am struggling with an excel import and export application to sqlserver as i have to do it through an external application and not through the Export Import Utility in sql server.
My most recent problem is:
The understanding of field values by sql server and excel.
1. I need to import into a table whose fields are varchar type.
2. I have to use as datasource- an Excel sheet(.xls file).
3. Some fields in excel contain numbers (Length up to 10 or
more digits).
4. For this I first format the cells'(in Excel file) datatype
into "Text".
5. Even then after importing into a table(say tblTemp) whose
schema/structure, is

CREATE TABLE [tblTemp] (
[rowId] int IDENTITY(1,1),
[Account Number] varchar(30) not null ,
[Mobile Number] varchar(30) null,
[Name] varchar(100) null
)
, and to import into this table im using select into query
with datasource as the excel file(Data Source=ExcelFile.xls),
the table fields show exponential values(may be float
datatype).
Shall any one suggest me how shall i get values from excel sheet with datatype varchar and import into the above said table as varchar values (with out any exponential types).|||

I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:

Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Here are some other tutorials that may be of interest:

Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory

Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema

The spreadsheet has a schema and you want to make sure it is formatted properly.

Regards,

Dave

|||

David Hayden wrote:

I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:

Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Here are some other tutorials that may be of interest:

Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory

Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema

The spreadsheet has a schema and you want to make sure it is formatted properly.

Regards,

Dave

I noticed that the codings are in C#.... is there any codings examples doing the same process using VB?

|||

hi thiru

how r u ?

iam facing same problem if have solution please suggest me

thanking u

with regards

purushotham.T

|||The easiest way I found of importing data from an excel sheet to sql server is saving the sheet as a tab delimited text file and then importing it to sql server. View this microsof issue :http://support.microsoft.com/kb/236605
Hope this helps

Excel Import to sqlserver database

Hi all,

this is thiru from India, hope i shall get answers here for my questions.

1. I need to import an Excel spread sheet to a remote sql server database through ASP.Net web application. I brief the process im following now please go through it.

Import Process:

a. select a fiile(.xls) and upload it to server.
b. using M/S Odbc Excel driver, and the uploaded excel file as datasource,
c. query the excel sheet to populate a dataset.
d. iterate through the rows of the dataset(I could not bulk copy the excel data, because
have to check the database, if record exists then update, else insert) to import to the
SQL Database

Performance issues:
1. I have to import spreadsheets having upto 60,000 records or even more at a time.
2. Is this a good option to use a webapplication for this task (I use this approach because
my boss wants to do so).
3. some times the excel file size grows up to 7 mb(Though i shall adjust config settings,
uploading and then querying a 7 mb file shall be an ovverhead i think.)
4. is there any possibility to get the datasource with out uploading the file to the server (Like
modifying the connection string as "datasource=HtmlFileControl.PostedFile" instead,)(I
tried this but it gives me "unspecified error").

please analyse my problem and suggest me a possible solution.
I thank all, for your efforts, of any kind.
have a nice time,
........thiru

hi,

you can directly import excel

to sql server import table

do the processing from the import table

to the staging table.

then from the staging table load it directly to the destination table.

i suggest you use SSIS or DTS or you can linked server

to excell and do the processing with sql server.

the problem with your approach is that you might encounter a dataset limitation.

there is no restartability feature. if your application breaks in the middle of the load process

where half of the data has been loaded it would be a great nightmare.

the first above mentioned approach is used widely in BI and datawarehousing.

here's some explanation on how it works:

1. you import the data to an import table. the import table shall be used

for processing the data .this liberates the datasource from heavy processing

and migrates the proceessing to SQL. if the import fails you can truncate the import table

and re import the data. this can done easily without harming the production.

the idea here is to first load everything into sql server.

2. stage the data to the staging table from the imports. here you do data cleansing

the burden nows reside on the sql server which is not a burden at all but a big problem to asp

from your previous design. Resatrtability. truncate the stage table and do the processing from either

step 1 or 2.

3. bulk insert from the stage to the production table. here your very sure that you are inserting a very clean data

with great speed.

|||Hello,
Thanls for your efforts, I have proceeded a long way through the application and my most recent challenge is this:

please let me find some help.

For a couple of weeks i am struggling with an excel import and export application to sqlserver as i have to do it through an external application and not through the Export Import Utility in sql server.
My most recent problem is:
The understanding of field values by sql server and excel.
1. I need to import into a table whose fields are varchar type.
2. I have to use as datasource- an Excel sheet(.xls file).
3. Some fields in excel contain numbers (Length up to 10 or
more digits).
4. For this I first format the cells'(in Excel file) datatype
into "Text".
5. Even then after importing into a table(say tblTemp) whose
schema/structure, is

CREATE TABLE [tblTemp] (
[rowId] int IDENTITY(1,1),
[Account Number] varchar(30) not null ,
[Mobile Number] varchar(30) null,
[Name] varchar(100) null
)
, and to import into this table im using select into query
with datasource as the excel file(Data Source=ExcelFile.xls),
the table fields show exponential values(may be float
datatype).
Shall any one suggest me how shall i get values from excel sheet with datatype varchar and import into the above said table as varchar values (with out any exponential types).|||

I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:

Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Here are some other tutorials that may be of interest:

Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory

Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema

The spreadsheet has a schema and you want to make sure it is formatted properly.

Regards,

Dave

|||

David Hayden wrote:

I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:

Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Here are some other tutorials that may be of interest:

Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory

Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema

The spreadsheet has a schema and you want to make sure it is formatted properly.

Regards,

Dave

I noticed that the codings are in C#.... is there any codings examples doing the same process using VB?

|||

hi thiru

how r u ?

iam facing same problem if have solution please suggest me

thanking u

with regards

purushotham.T

|||The easiest way I found of importing data from an excel sheet to sql server is saving the sheet as a tab delimited text file and then importing it to sql server. View this microsof issue :http://support.microsoft.com/kb/236605
Hope this helps

Excel Import to sqlserver database

Hi all,

this is thiru from India, hope i shall get answers here for my questions.

1. I need to import an Excel spread sheet to a remote sql server database through ASP.Net web application. I brief the process im following now please go through it.

Import Process:

a. select a fiile(.xls) and upload it to server.
b. using M/S Odbc Excel driver, and the uploaded excel file as datasource,
c. query the excel sheet to populate a dataset.
d. iterate through the rows of the dataset(I could not bulk copy the excel data, because
have to check the database, if record exists then update, else insert) to import to the
SQL Database

Performance issues:
1. I have to import spreadsheets having upto 60,000 records or even more at a time.
2. Is this a good option to use a webapplication for this task (I use this approach because
my boss wants to do so).
3. some times the excel file size grows up to 7 mb(Though i shall adjust config settings,
uploading and then querying a 7 mb file shall be an ovverhead i think.)
4. is there any possibility to get the datasource with out uploading the file to the server (Like
modifying the connection string as "datasource=HtmlFileControl.PostedFile" instead,)(I
tried this but it gives me "unspecified error").

please analyse my problem and suggest me a possible solution.
I thank all, for your efforts, of any kind.
have a nice time,
........thiru

hi,

you can directly import excel

to sql server import table

do the processing from the import table

to the staging table.

then from the staging table load it directly to the destination table.

i suggest you use SSIS or DTS or you can linked server

to excell and do the processing with sql server.

the problem with your approach is that you might encounter a dataset limitation.

there is no restartability feature. if your application breaks in the middle of the load process

where half of the data has been loaded it would be a great nightmare.

the first above mentioned approach is used widely in BI and datawarehousing.

here's some explanation on how it works:

1. you import the data to an import table. the import table shall be used

for processing the data .this liberates the datasource from heavy processing

and migrates the proceessing to SQL. if the import fails you can truncate the import table

and re import the data. this can done easily without harming the production.

the idea here is to first load everything into sql server.

2. stage the data to the staging table from the imports. here you do data cleansing

the burden nows reside on the sql server which is not a burden at all but a big problem to asp

from your previous design. Resatrtability. truncate the stage table and do the processing from either

step 1 or 2.

3. bulk insert from the stage to the production table. here your very sure that you are inserting a very clean data

with great speed.

|||

Hello,
Thanls for your efforts, I have proceeded a long way through the application and my most recent challenge is this:

please let me find some help.

For

a couple of weeks i am struggling with an excel import and export

application to sqlserver as i have to do it through an external

application and not through the Export Import Utility in sql server.
My most recent problem is:
The understanding of field values by sql server and excel.
1. I need to import into a table whose fields are varchar type.
2. I have to use as datasource- an Excel sheet(.xls file).
3. Some fields in excel contain numbers (Length up to 10 or
more digits).
4. For this I first format the cells'(in Excel file) datatype
into "Text".
5. Even then after importing into a table(say tblTemp) whose
schema/structure, is

CREATE TABLE [tblTemp] (
[rowId] int IDENTITY(1,1),
[Account Number] varchar(30) not null ,
[Mobile Number] varchar(30) null,
[Name] varchar(100) null
)
, and to import into this table im using select into query
with datasource as the excel file(Data Source=ExcelFile.xls),
the table fields show exponential values(may be float
datatype).
Shall

any one suggest me how shall i get values from excel sheet with

datatype varchar and import into the above said table as varchar values

(with out any exponential types).|||

I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:

Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Here are some other tutorials that may be of interest:

Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory

Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema

The spreadsheet has a schema and you want to make sure it is formatted properly.

Regards,

Dave

|||

David Hayden wrote:

I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:

Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Here are some other tutorials that may be of interest:

Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory

Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema

The spreadsheet has a schema and you want to make sure it is formatted properly.

Regards,

Dave

I noticed that the codings are in C#.... is there any codings examples doing the same process using VB?

|||

hi thiru

how r u ?

iam facing same problem if have solution please suggest me

thanking u

with regards

purushotham.T

|||The easiest way I found of importing data from an excel sheet to sql server is saving the sheet as a tab delimited text file and then importing it to sql server. View this microsof issue :http://support.microsoft.com/kb/236605
Hope this helps