Copying procedures in SQL from one server to another using VBA in excel

Is there any sample code or help on trying to copy/replace views or prodecure in SQL or Oracle? Im using VBA in Excel...

You can use SMO to do that and in SQL Server SMO/DMO section you can find , here , an example to copy a table between different instances; i think you can adapt it for sp.

|||SMO/DMO will certainly work as ggciubuc has mentioned if you'd like to continue using VBA, however there are a number of other tools out there that will make it easier.

Management Studio has the generate scripts menu item that will script out the database object so you can run the script against another server.

Red-Gate and other tools manufacturers have compare utilities that examine the database objects from one server and compare them to the objects of another server, scripting out the differences and even applying them if you wish.

Data Transformation Services (DTS) in SQL Server 2000 has a task that allows you to migrate database objects from one server to another.

HTH...

Joe

Copying large amount of data form MS Excel to MS Sql Server

Hi ,
I have got a senario in which large amount of data should be copied from MS excel to MS SQL server. which is the best option to do so. Because when I use recordsets the process is very slow and it effects the performance. Any one please suggest me.

Thanks
Anish

Quote:

Originally Posted by AnishAbs

Hi ,
I have got a senario in which large amount of data should be copied from MS excel to MS SQL server. which is the best option to do so. Because when I use recordsets the process is very slow and it effects the performance. Any one please suggest me.

Thanks
Anish

HI Anish,
I am working on sql2005.in this right click on thedatabase
-->go to all tasks .
---->select import data.
--->select msexcel
---->enter the exel file path
---->select all data in exel
---->click on finish.

i think it is also works in sql2000. in 2000 you have to do it enterprise manager|||hi,
if the no of records are greater than 67000 in excel,it is not possible to copy to sql server.you need to split that excel into different files.
if you can copy that excel to MSaccess you can send large data to sql server
through dts only.|||Hi,

Use the Import/Export wizard to import the excel file into you destination database.

Cheers.

Copying grid results with column headers

Is there any way to copy grid query results with the column headers? When I
copy and paste the results into Excel now I have to manually type the
headers so I know what I'm looking at.
David,
if you are referring to Query Analyser, you can change the output format to
"Results to File...". On Tools, Options, Results, you can select to take the
column headers and select csv, column aligned etc formats. If this is a
regular process, you might like to do this in a DTS package.
HTH,
Paul Ibison
|||Do you know if there's a way to enable copying the column headers from the
grid results though? I may want to actually see the results visually before
deciding I want to copy it, making the Resuts to File a less appealing
option. If it's a long running query, running to grid first then again to
file would double the time. I'll paste the results into Excel.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OaanEGlSEHA.3768@.TK2MSFTNGP11.phx.gbl...
> David,
> if you are referring to Query Analyser, you can change the output format
to
> "Results to File...". On Tools, Options, Results, you can select to take
the
> column headers and select csv, column aligned etc formats. If this is a
> regular process, you might like to do this in a DTS package.
> HTH,
> Paul Ibison
>
|||David,
not directly as far as I know. 'Results to text' will give you visual output
with column headers, but it usually won'y format correctly in Excel.
Regards,
Paul Ibison
|||Why not go into EXCEL and IMPORT DATA - use a NEW SQL CONNECTION and get to your DATABASE.
Then from in there you can load a table, execute a query, etc.
It will load right into the EXCEL grids - headings and all - and if you like what you see, save as XLS or CSV or TDF or whatever you want - EXCEL does them all.

Copying grid results with column headers

Is there any way to copy grid query results with the column headers? When I
copy and paste the results into Excel now I have to manually type the
headers so I know what I'm looking at.David,
if you are referring to Query Analyser, you can change the output format to
"Results to File...". On Tools, Options, Results, you can select to take the
column headers and select csv, column aligned etc formats. If this is a
regular process, you might like to do this in a DTS package.
HTH,
Paul Ibison|||Do you know if there's a way to enable copying the column headers from the
grid results though? I may want to actually see the results visually before
deciding I want to copy it, making the Resuts to File a less appealing
option. If it's a long running query, running to grid first then again to
file would double the time. I'll paste the results into Excel.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OaanEGlSEHA.3768@.TK2MSFTNGP11.phx.gbl...
> David,
> if you are referring to Query Analyser, you can change the output format
to
> "Results to File...". On Tools, Options, Results, you can select to take
the
> column headers and select csv, column aligned etc formats. If this is a
> regular process, you might like to do this in a DTS package.
> HTH,
> Paul Ibison
>|||David,
not directly as far as I know. 'Results to text' will give you visual output
with column headers, but it usually won'y format correctly in Excel.
Regards,
Paul Ibison|||Why not go into EXCEL and IMPORT DATA - use a NEW SQL CONNECTION and get to
your DATABASE.
Then from in there you can load a table, execute a query, etc.
It will load right into the EXCEL grids - headings and all - and if you like
what you see, save as XLS or CSV or TDF or whatever you want - EXCEL does t
hem all.

Copying grid results with column headers

Is there any way to copy grid query results with the column headers? When I
copy and paste the results into Excel now I have to manually type the
headers so I know what I'm looking at.David,
if you are referring to Query Analyser, you can change the output format to
"Results to File...". On Tools, Options, Results, you can select to take the
column headers and select csv, column aligned etc formats. If this is a
regular process, you might like to do this in a DTS package.
HTH,
Paul Ibison|||Do you know if there's a way to enable copying the column headers from the
grid results though? I may want to actually see the results visually before
deciding I want to copy it, making the Resuts to File a less appealing
option. If it's a long running query, running to grid first then again to
file would double the time. I'll paste the results into Excel.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OaanEGlSEHA.3768@.TK2MSFTNGP11.phx.gbl...
> David,
> if you are referring to Query Analyser, you can change the output format
to
> "Results to File...". On Tools, Options, Results, you can select to take
the
> column headers and select csv, column aligned etc formats. If this is a
> regular process, you might like to do this in a DTS package.
> HTH,
> Paul Ibison
>|||David,
not directly as far as I know. 'Results to text' will give you visual output
with column headers, but it usually won'y format correctly in Excel.
Regards,
Paul Ibison|||Why not go into EXCEL and IMPORT DATA - use a NEW SQL CONNECTION and get to your DATABASE
Then from in there you can load a table, execute a query, etc
It will load right into the EXCEL grids - headings and all - and if you like what you see, save as XLS or CSV or TDF or whatever you want - EXCEL does them all.

CopyFromRecordSet In Excel 2003

I want to use the CopyFromRecordSet however I am using a stored procedure that returns a recordset. Here is the code, but the recordset never opens. I have commented out the Execute

Here is a code snip, any Ideas? TIA Mike

Dim Con1 As New ADODB.Connection
Dim Cmd1 As New ADODB.Command
Dim Rs As New ADODB.Recordset

Con1.Open

Cmd1.ActiveConnection = Con1
Cmd1.CommandType = adCmdText
Cmd1.CommandText = "MyStroredProc"
Cmd1.Execute
Rs.Open Cmd1

Worksheets("T1").Range("B1").CopyFromRecordset Rs

I believe it should be like

Con1.Open

Set Cmd1.ActiveConnection = Con1
Cmd1.CommandType = adCmdStoredProc ' Please verify spelling for adCmdStoredProc, since I do not have documentation with me
Cmd1.CommandText = "MyStroredProc"
Set Rs = Cmd1.Execute

|||

VMazur,

Thank you for the code snip however when I query the recordset state, it is still closed or 0 and I generate an error. I did use 4 as the commandType

Any other thoughts would be appreciated.

'Open the connection
Con1.Open

Set Cmd1.ActiveConnection = Con1
Cmd1.CommandType = 4 '' Stored Proc
Cmd1.CommandText = "REPORT_procASDCStatus"
Set Rs = Cmd1.Execute

'Rs.Open Cmd1 '' Tried this also
Worksheets("T1").Range("A1").CopyFromRecordset Rs <<Fails with message that rs is not open

MikeD

|||What happens when you call Execute method? Does it generate any error or not?|||

The issue was Set NO COUNT ON in the SP. This was missing in the SP.

As soon as I added this, the recordset opened and I could do copy from.

Thank you Mazur

|||

Hi Mazur,

There was no error message, only issue was there was no data transfer. As soon as we set NOCOuNT ON, data started transfering.

This question is resolved.

Thank you

Mike

CopyFromRecordSet In Excel 2003

I want to use the CopyFromRecordSet however I am using a stored procedure that returns a recordset. Here is the code, but the recordset never opens. I have commented out the Execute

Here is a code snip, any Ideas? TIA Mike

Dim Con1 As New ADODB.Connection
Dim Cmd1 As New ADODB.Command
Dim Rs As New ADODB.Recordset

Con1.Open

Cmd1.ActiveConnection = Con1
Cmd1.CommandType = adCmdText
Cmd1.CommandText = "MyStroredProc"
Cmd1.Execute
Rs.Open Cmd1

Worksheets("T1").Range("B1").CopyFromRecordset Rs

I believe it should be like

Con1.Open

Set Cmd1.ActiveConnection = Con1
Cmd1.CommandType = adCmdStoredProc ' Please verify spelling for adCmdStoredProc, since I do not have documentation with me
Cmd1.CommandText = "MyStroredProc"
Set Rs = Cmd1.Execute

|||

VMazur,

Thank you for the code snip however when I query the recordset state, it is still closed or 0 and I generate an error. I did use 4 as the commandType

Any other thoughts would be appreciated.

'Open the connection
Con1.Open

Set Cmd1.ActiveConnection = Con1
Cmd1.CommandType = 4 '' Stored Proc
Cmd1.CommandText = "REPORT_procASDCStatus"
Set Rs = Cmd1.Execute

'Rs.Open Cmd1 '' Tried this also
Worksheets("T1").Range("A1").CopyFromRecordset Rs <<Fails with message that rs is not open

MikeD

|||What happens when you call Execute method? Does it generate any error or not?|||

The issue was Set NO COUNT ON in the SP. This was missing in the SP.

As soon as I added this, the recordset opened and I could do copy from.

Thank you Mazur

|||

Hi Mazur,

There was no error message, only issue was there was no data transfer. As soon as we set NOCOuNT ON, data started transfering.

This question is resolved.

Thank you

Mike

copy/paste grid from QA - no field names!

for debug purposes i tried to copy paste some Query Analyser output into Excel... the data pastes fine, but i can't get the field-names to copy/paste.

is there a trick?

izyNot by itself...

YOu could save it as text...you get the column names that way...

Or you can Go

SELECT 'Col1', 'Col2', 'Ect'
UNION ALL
SELECT Col1, Col2, Ect
FROM your Table|||text! stupidly simple really.
thanks for the help,

izy|||Try results to Text & Tab delimited.
Under Tools --> Options --> Results.

It works OK for me.

Tim S

copy/paste bugs

try copying a connection manager from one package to another

it does not work

e.g. excel connection manager

it loses the name of the connection manager and also the file you are pointing to

so it is basically no different to 'add new connection manager'

also

try copying the name of the connection manager to another connection

select connection, hit F2 (edit) ctrl C,

try pasting!

in order to paste you have to right click on the selected text and select copy from the context menu

this is not a bug

this sloppy, poorly tested software

So get busy posting these bugs over at http://connect.microsoft.com/sqlserver/feedback.

copy to Excel from output grid

When i save the query analyser output to Excel, it always messes up the
display for datetime columns ? Why is that ? and how can i copy and paste
without do all the conversions,etc. ? Right now to show the actual datetime
in excel, i have to format the cells to text and then paste again..Hassan,
Unfortunately, you have to create an Excel-understood string
yourself. Here's an example of how you would do it with the
OrderDate column in Northwind's Orders table:
select
convert(varchar(30),OrderDate, 101)
+ space(1)
+ convert(varchar(30),OrderDate, 8)
from Northwind..Orders
This is for the US local month/day/year. If you need day/month/year
instead, use format 103 instead of 101. It's worth testing this a bit,
since quite a few factors can influence how dates are interpreted.
Steve Kass
Drew University
Hassan wrote:

>When i save the query analyser output to Excel, it always messes up the
>display for datetime columns ? Why is that ? and how can i copy and paste
>without do all the conversions,etc. ? Right now to show the actual datetime
>in excel, i have to format the cells to text and then paste again..
>
>
>|||Don't copy/paste. Instead, from the menu options, choose the output to be
Text, Comma Delimited instead of Grid, Column Aligned. Then save the output
as csv or just copy and paste this into Excel, which can handle this format
better than the fixed-width, Column Aligned format.
Sincerely,
Anthony Thomas
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e67sHppIFHA.2744@.tk2msftngp13.phx.gbl...
When i save the query analyser output to Excel, it always messes up the
display for datetime columns ? Why is that ? and how can i copy and paste
without do all the conversions,etc. ? Right now to show the actual datetime
in excel, i have to format the cells to text and then paste again..

copy to Excel from output grid

When i save the query analyser output to Excel, it always messes up the
display for datetime columns ? Why is that ? and how can i copy and paste
without do all the conversions,etc. ? Right now to show the actual datetime
in excel, i have to format the cells to text and then paste again..Hassan,
Unfortunately, you have to create an Excel-understood string
yourself. Here's an example of how you would do it with the
OrderDate column in Northwind's Orders table:
select
convert(varchar(30),OrderDate, 101)
+ space(1)
+ convert(varchar(30),OrderDate, 8)
from Northwind..Orders
This is for the US local month/day/year. If you need day/month/year
instead, use format 103 instead of 101. It's worth testing this a bit,
since quite a few factors can influence how dates are interpreted.
Steve Kass
Drew University
Hassan wrote:
>When i save the query analyser output to Excel, it always messes up the
>display for datetime columns ? Why is that ? and how can i copy and paste
>without do all the conversions,etc. ? Right now to show the actual datetime
>in excel, i have to format the cells to text and then paste again..
>
>
>|||Don't copy/paste. Instead, from the menu options, choose the output to be
Text, Comma Delimited instead of Grid, Column Aligned. Then save the output
as csv or just copy and paste this into Excel, which can handle this format
better than the fixed-width, Column Aligned format.
Sincerely,
Anthony Thomas
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e67sHppIFHA.2744@.tk2msftngp13.phx.gbl...
When i save the query analyser output to Excel, it always messes up the
display for datetime columns ? Why is that ? and how can i copy and paste
without do all the conversions,etc. ? Right now to show the actual datetime
in excel, i have to format the cells to text and then paste again..

copy to Excel from output grid

When i save the query analyser output to Excel, it always messes up the
display for datetime columns ? Why is that ? and how can i copy and paste
without do all the conversions,etc. ? Right now to show the actual datetime
in excel, i have to format the cells to text and then paste again..
Hassan,
Unfortunately, you have to create an Excel-understood string
yourself. Here's an example of how you would do it with the
OrderDate column in Northwind's Orders table:
select
convert(varchar(30),OrderDate, 101)
+ space(1)
+ convert(varchar(30),OrderDate, 8)
from Northwind..Orders
This is for the US local month/day/year. If you need day/month/year
instead, use format 103 instead of 101. It's worth testing this a bit,
since quite a few factors can influence how dates are interpreted.
Steve Kass
Drew University
Hassan wrote:

>When i save the query analyser output to Excel, it always messes up the
>display for datetime columns ? Why is that ? and how can i copy and paste
>without do all the conversions,etc. ? Right now to show the actual datetime
>in excel, i have to format the cells to text and then paste again..
>
>
>
|||Don't copy/paste. Instead, from the menu options, choose the output to be
Text, Comma Delimited instead of Grid, Column Aligned. Then save the output
as csv or just copy and paste this into Excel, which can handle this format
better than the fixed-width, Column Aligned format.
Sincerely,
Anthony Thomas

"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e67sHppIFHA.2744@.tk2msftngp13.phx.gbl...
When i save the query analyser output to Excel, it always messes up the
display for datetime columns ? Why is that ? and how can i copy and paste
without do all the conversions,etc. ? Right now to show the actual datetime
in excel, i have to format the cells to text and then paste again..

Copy SS2005 query headers & data to Excel

Hello,
Using SS2005, is there a way to copy both the headers and the data when you
run a query and paste the results in Excel?
Thanks in advance,
Steven
Steven K0 (stroy@.api.com) writes:
> Using SS2005, is there a way to copy both the headers and the data when
> you run a query and paste the results in Excel?
Yup! Tools->Options->Query Results->SQL Server->Results to Grid. Find
the checkbox "Include column headers when copying and saving the results".
If memory serves, headers are only copied when you select cells from at
least two columns.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Copy SS2005 query headers & data to Excel

Hello,
Using SS2005, is there a way to copy both the headers and the data when you
run a query and paste the results in Excel?
--
Thanks in advance,
StevenSteven K0 (stroy@.api.com) writes:
> Using SS2005, is there a way to copy both the headers and the data when
> you run a query and paste the results in Excel?
Yup! Tools->Options->Query Results->SQL Server->Results to Grid. Find
the checkbox "Include column headers when copying and saving the results".
If memory serves, headers are only copied when you select cells from at
least two columns.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Copy SS2005 query headers & data to Excel

Hello,
Using SS2005, is there a way to copy both the headers and the data when you
run a query and paste the results in Excel?
--
Thanks in advance,
StevenSteven K0 (stroy@.api.com) writes:
> Using SS2005, is there a way to copy both the headers and the data when
> you run a query and paste the results in Excel?
Yup! Tools->Options->Query Results->SQL Server->Results to Grid. Find
the checkbox "Include column headers when copying and saving the results".
If memory serves, headers are only copied when you select cells from at
least two columns.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx