We are buying a new db server that will support the future growth of our
company. Our current database load can easily be handled by a single
processor server, but I would like to buy a server that can handle a much
heavier load in the future.
1) I am wondering if I get a dual processor server, can I install a single
processor license of sql server for the time being?
2) Will this only utilize one processor?
3) Can I add an additional processor license in the future as when we need
more processing power?
4) Also, what is the latest projected relase date for SQL Server 2005?
5) Is any pricing availible for this yet?
6) If I get SQL Server 2000 license now, will there be any upgrade options
to 2005 when it is availible?
Any advice is greatly appreciated.
--DanThe SQL Server 2000 Licensing FAQ at
http://www.microsoft.com/sql/howtobuy/faq.asp states the following:
Q. Do I have to license all of the processors in a server?
A. You only have to acquire licenses for processors that are
accessible to any copy of the operating system upon which SQL Server 2000 is
installed.
So that should take care of questions 1 through 3. :-) You could physically
remove one of the processors from the new system and run it that way until
you have need, then purchase the second processor license and install the
second processor.
For #4, I believe our official response is in the summer of 2005.
For #5, I don't think any of the pricing decisions have been made yet.
For #6, there's usually some kind of upgrade pricing, but again, I doubt
that decision has been made yet.
--
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:6E68F23D-3143-4385-8876-06CF474C7299@.microsoft.com...
> We are buying a new db server that will support the future growth of our
> company. Our current database load can easily be handled by a single
> processor server, but I would like to buy a server that can handle a much
> heavier load in the future.
> 1) I am wondering if I get a dual processor server, can I install a single
> processor license of sql server for the time being?
> 2) Will this only utilize one processor?
> 3) Can I add an additional processor license in the future as when we need
> more processing power?
> 4) Also, what is the latest projected relase date for SQL Server 2005?
> 5) Is any pricing availible for this yet?
> 6) If I get SQL Server 2000 license now, will there be any upgrade options
> to 2005 when it is availible?
> Any advice is greatly appreciated.
> --Dan
begin 666 ts.gif
J1TE&.#EA`0`!`( ``````/___R'Y! $`````+ `````!``$```(!1 `[
`
end|||Just to add a little to the discussion. MS licensing currently allows you
to buy with software assurance. This lets you upgrade to the newest version
when it comes out provided it's in the timeframe provide by the SA license.
"Stephen Dybing [MSFT]" <stephd@.online.microsoft.com> wrote in message
news:eHlqD#u6EHA.3236@.TK2MSFTNGP15.phx.gbl...
> The SQL Server 2000 Licensing FAQ at
> http://www.microsoft.com/sql/howtobuy/faq.asp states the following:
> Q. Do I have to license all of the processors in a server?
>
> A. You only have to acquire licenses for processors that are
> accessible to any copy of the operating system upon which SQL Server 2000
is
> installed.
>
>
> So that should take care of questions 1 through 3. :-) You could
physically
> remove one of the processors from the new system and run it that way until
> you have need, then purchase the second processor license and install the
> second processor.
> For #4, I believe our official response is in the summer of 2005.
> For #5, I don't think any of the pricing decisions have been made yet.
> For #6, there's usually some kind of upgrade pricing, but again, I doubt
> that decision has been made yet.
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Please reply to the newsgroups only, thanks.
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:6E68F23D-3143-4385-8876-06CF474C7299@.microsoft.com...
> > We are buying a new db server that will support the future growth of our
> > company. Our current database load can easily be handled by a single
> > processor server, but I would like to buy a server that can handle a
much
> > heavier load in the future.
> >
> > 1) I am wondering if I get a dual processor server, can I install a
single
> > processor license of sql server for the time being?
> > 2) Will this only utilize one processor?
> > 3) Can I add an additional processor license in the future as when we
need
> > more processing power?
> > 4) Also, what is the latest projected relase date for SQL Server 2005?
> > 5) Is any pricing availible for this yet?
> > 6) If I get SQL Server 2000 license now, will there be any upgrade
options
> > to 2005 when it is availible?
> >
> > Any advice is greatly appreciated.
> > --Dan
>
>
Showing posts with label load. Show all posts
Showing posts with label load. Show all posts
Friday, March 9, 2012
dual processor license
We are buying a new db server that will support the future growth of our
company. Our current database load can easily be handled by a single
processor server, but I would like to buy a server that can handle a much
heavier load in the future.
1) I am wondering if I get a dual processor server, can I install a single
processor license of sql server for the time being?
2) Will this only utilize one processor?
3) Can I add an additional processor license in the future as when we need
more processing power?
4) Also, what is the latest projected relase date for SQL Server 2005?
5) Is any pricing availible for this yet?
6) If I get SQL Server 2000 license now, will there be any upgrade options
to 2005 when it is availible?
Any advice is greatly appreciated.
--Dan
The SQL Server 2000 Licensing FAQ at
http://www.microsoft.com/sql/howtobuy/faq.asp states the following:
Q. Do I have to license all of the processors in a server?
A. You only have to acquire licenses for processors that are
accessible to any copy of the operating system upon which SQL Server 2000 is
installed.
So that should take care of questions 1 through 3. :-) You could physically
remove one of the processors from the new system and run it that way until
you have need, then purchase the second processor license and install the
second processor.
For #4, I believe our official response is in the summer of 2005.
For #5, I don't think any of the pricing decisions have been made yet.
For #6, there's usually some kind of upgrade pricing, but again, I doubt
that decision has been made yet.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:6E68F23D-3143-4385-8876-06CF474C7299@.microsoft.com...
> We are buying a new db server that will support the future growth of our
> company. Our current database load can easily be handled by a single
> processor server, but I would like to buy a server that can handle a much
> heavier load in the future.
> 1) I am wondering if I get a dual processor server, can I install a single
> processor license of sql server for the time being?
> 2) Will this only utilize one processor?
> 3) Can I add an additional processor license in the future as when we need
> more processing power?
> 4) Also, what is the latest projected relase date for SQL Server 2005?
> 5) Is any pricing availible for this yet?
> 6) If I get SQL Server 2000 license now, will there be any upgrade options
> to 2005 when it is availible?
> Any advice is greatly appreciated.
> --Dan
begin 666 ts.gif
J1TE&.#EA`0`!`( ``````/___R'Y! $`````+ `````!``$```(!1 `[
`
end
|||Just to add a little to the discussion. MS licensing currently allows you
to buy with software assurance. This lets you upgrade to the newest version
when it comes out provided it's in the timeframe provide by the SA license.
"Stephen Dybing [MSFT]" <stephd@.online.microsoft.com> wrote in message
news:eHlqD#u6EHA.3236@.TK2MSFTNGP15.phx.gbl...
> The SQL Server 2000 Licensing FAQ at
> http://www.microsoft.com/sql/howtobuy/faq.asp states the following:
> Q. Do I have to license all of the processors in a server?
>
> A. You only have to acquire licenses for processors that are
> accessible to any copy of the operating system upon which SQL Server 2000
is
> installed.
>
>
> So that should take care of questions 1 through 3. :-) You could
physically
> remove one of the processors from the new system and run it that way until
> you have need, then purchase the second processor license and install the
> second processor.
> For #4, I believe our official response is in the summer of 2005.
> For #5, I don't think any of the pricing decisions have been made yet.
> For #6, there's usually some kind of upgrade pricing, but again, I doubt
> that decision has been made yet.
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> Please reply to the newsgroups only, thanks.
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:6E68F23D-3143-4385-8876-06CF474C7299@.microsoft.com...
much[vbcol=seagreen]
single[vbcol=seagreen]
need[vbcol=seagreen]
options
>
>
company. Our current database load can easily be handled by a single
processor server, but I would like to buy a server that can handle a much
heavier load in the future.
1) I am wondering if I get a dual processor server, can I install a single
processor license of sql server for the time being?
2) Will this only utilize one processor?
3) Can I add an additional processor license in the future as when we need
more processing power?
4) Also, what is the latest projected relase date for SQL Server 2005?
5) Is any pricing availible for this yet?
6) If I get SQL Server 2000 license now, will there be any upgrade options
to 2005 when it is availible?
Any advice is greatly appreciated.
--Dan
The SQL Server 2000 Licensing FAQ at
http://www.microsoft.com/sql/howtobuy/faq.asp states the following:
Q. Do I have to license all of the processors in a server?
A. You only have to acquire licenses for processors that are
accessible to any copy of the operating system upon which SQL Server 2000 is
installed.
So that should take care of questions 1 through 3. :-) You could physically
remove one of the processors from the new system and run it that way until
you have need, then purchase the second processor license and install the
second processor.
For #4, I believe our official response is in the summer of 2005.
For #5, I don't think any of the pricing decisions have been made yet.
For #6, there's usually some kind of upgrade pricing, but again, I doubt
that decision has been made yet.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:6E68F23D-3143-4385-8876-06CF474C7299@.microsoft.com...
> We are buying a new db server that will support the future growth of our
> company. Our current database load can easily be handled by a single
> processor server, but I would like to buy a server that can handle a much
> heavier load in the future.
> 1) I am wondering if I get a dual processor server, can I install a single
> processor license of sql server for the time being?
> 2) Will this only utilize one processor?
> 3) Can I add an additional processor license in the future as when we need
> more processing power?
> 4) Also, what is the latest projected relase date for SQL Server 2005?
> 5) Is any pricing availible for this yet?
> 6) If I get SQL Server 2000 license now, will there be any upgrade options
> to 2005 when it is availible?
> Any advice is greatly appreciated.
> --Dan
begin 666 ts.gif
J1TE&.#EA`0`!`( ``````/___R'Y! $`````+ `````!``$```(!1 `[
`
end
|||Just to add a little to the discussion. MS licensing currently allows you
to buy with software assurance. This lets you upgrade to the newest version
when it comes out provided it's in the timeframe provide by the SA license.
"Stephen Dybing [MSFT]" <stephd@.online.microsoft.com> wrote in message
news:eHlqD#u6EHA.3236@.TK2MSFTNGP15.phx.gbl...
> The SQL Server 2000 Licensing FAQ at
> http://www.microsoft.com/sql/howtobuy/faq.asp states the following:
> Q. Do I have to license all of the processors in a server?
>
> A. You only have to acquire licenses for processors that are
> accessible to any copy of the operating system upon which SQL Server 2000
is
> installed.
>
>
> So that should take care of questions 1 through 3. :-) You could
physically
> remove one of the processors from the new system and run it that way until
> you have need, then purchase the second processor license and install the
> second processor.
> For #4, I believe our official response is in the summer of 2005.
> For #5, I don't think any of the pricing decisions have been made yet.
> For #6, there's usually some kind of upgrade pricing, but again, I doubt
> that decision has been made yet.
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> Please reply to the newsgroups only, thanks.
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:6E68F23D-3143-4385-8876-06CF474C7299@.microsoft.com...
much[vbcol=seagreen]
single[vbcol=seagreen]
need[vbcol=seagreen]
options
>
>
dual processor license
We are buying a new db server that will support the future growth of our
company. Our current database load can easily be handled by a single
processor server, but I would like to buy a server that can handle a much
heavier load in the future.
1) I am wondering if I get a dual processor server, can I install a single
processor license of sql server for the time being?
2) Will this only utilize one processor?
3) Can I add an additional processor license in the future as when we need
more processing power?
4) Also, what is the latest projected relase date for SQL Server 2005?
5) Is any pricing availible for this yet?
6) If I get SQL Server 2000 license now, will there be any upgrade options
to 2005 when it is availible?
Any advice is greatly appreciated.
--DanJust to add a little to the discussion. MS licensing currently allows you
to buy with software assurance. This lets you upgrade to the newest version
when it comes out provided it's in the timeframe provide by the SA license.
"Stephen Dybing [MSFT]" <stephd@.online.microsoft.com> wrote in message
news:eHlqD#u6EHA.3236@.TK2MSFTNGP15.phx.gbl...
> The SQL Server 2000 Licensing FAQ at
> http://www.microsoft.com/sql/howtobuy/faq.asp states the following:
> Q. Do I have to license all of the processors in a server?
>
> A. You only have to acquire licenses for processors that are
> accessible to any copy of the operating system upon which SQL Server 2000
is
> installed.
>
>
> So that should take care of questions 1 through 3. :-) You could
physically
> remove one of the processors from the new system and run it that way until
> you have need, then purchase the second processor license and install the
> second processor.
> For #4, I believe our official response is in the summer of 2005.
> For #5, I don't think any of the pricing decisions have been made yet.
> For #6, there's usually some kind of upgrade pricing, but again, I doubt
> that decision has been made yet.
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Please reply to the newsgroups only, thanks.
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:6E68F23D-3143-4385-8876-06CF474C7299@.microsoft.com...
much[vbcol=seagreen]
single[vbcol=seagreen]
need[vbcol=seagreen]
options[vbcol=seagreen]
>
>
company. Our current database load can easily be handled by a single
processor server, but I would like to buy a server that can handle a much
heavier load in the future.
1) I am wondering if I get a dual processor server, can I install a single
processor license of sql server for the time being?
2) Will this only utilize one processor?
3) Can I add an additional processor license in the future as when we need
more processing power?
4) Also, what is the latest projected relase date for SQL Server 2005?
5) Is any pricing availible for this yet?
6) If I get SQL Server 2000 license now, will there be any upgrade options
to 2005 when it is availible?
Any advice is greatly appreciated.
--DanJust to add a little to the discussion. MS licensing currently allows you
to buy with software assurance. This lets you upgrade to the newest version
when it comes out provided it's in the timeframe provide by the SA license.
"Stephen Dybing [MSFT]" <stephd@.online.microsoft.com> wrote in message
news:eHlqD#u6EHA.3236@.TK2MSFTNGP15.phx.gbl...
> The SQL Server 2000 Licensing FAQ at
> http://www.microsoft.com/sql/howtobuy/faq.asp states the following:
> Q. Do I have to license all of the processors in a server?
>
> A. You only have to acquire licenses for processors that are
> accessible to any copy of the operating system upon which SQL Server 2000
is
> installed.
>
>
> So that should take care of questions 1 through 3. :-) You could
physically
> remove one of the processors from the new system and run it that way until
> you have need, then purchase the second processor license and install the
> second processor.
> For #4, I believe our official response is in the summer of 2005.
> For #5, I don't think any of the pricing decisions have been made yet.
> For #6, there's usually some kind of upgrade pricing, but again, I doubt
> that decision has been made yet.
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Please reply to the newsgroups only, thanks.
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:6E68F23D-3143-4385-8876-06CF474C7299@.microsoft.com...
much[vbcol=seagreen]
single[vbcol=seagreen]
need[vbcol=seagreen]
options[vbcol=seagreen]
>
>
Wednesday, March 7, 2012
dtsx load id list from xls
How to write a dtsx that loads a list of ids from an xls and incorporates
this list into sql ?
For example,
update table1 set column1 = false where id in ( {the list of ids } )On Mar 12, 7:30 pm, "John Grandy" <johnagrandy-at-gmail-dot-com>
wrote:
> How to write a dtsx that loads a list of ids from an xls and incorporates
> this list into sql ?
> For example,
> update table1 set column1 = false where id in ( {the list of ids } )
These directions will get an excel spreadsheet into a table that you
can then do: 'update table1 set column1 = false where id in (select id
from NewlyCreatedImportTable)':
1. Create a new Integration Services project.
2. Right click in the 'Connection Managers' tab and select 'New
Connection...' from the drop-down list.
3. Select the 'EXCEL' type and the 'Add' button.
4. Select the 'Browse...' button to browse for the Excel file and
select the 'Open' button.
5. If the first line of the Excel file contains the column names,
select the radio button to the left of: "First row has column names"
then select the 'Ok' button.
6. Right click in the 'Connection Managers' tab and select 'New OLE
DB Connection...' from the drop-down list.
7. Select the 'New...' button and select a Server name from the drop-
down list and select 'OK' and select the 'OK' button again.
8. Drag and drop a 'Data Flow Task' control from the Toolbox onto the
'Control Flow' tab/pane.
9. Select the 'Data Flow' tab/pane.
10. Drag and drop an 'Excel Source' control from the Toolbox onto the
'Data Flow' tab/pane.
11. Right click the 'Excel Source' control and select 'Edit...'
12. Below 'OLE DB connection manager:' select the connection manager
created in steps 2-5.
13. Select the sheet number from the drop-down list below: "Name of
the Excel sheet:" and then select 'OK.'
14. Drag and drop an 'OLE DB Destination' control from the Toolbox
onto the 'Data Flow' tab/pane.
15. Drag the green arrow from the 'Excel Source' control to the 'OLE
DB Destination' control.
16. Right click the 'OLE DB Destination' control and select the
'Edit...' button
17. Below 'OLE DB connection manager:', select the connection control
created in steps 6-7.
18. Select the 'New...' button and edit the sql query for the table
name and column names of the new table to be created (NOTE: SSIS is
very picky on the conversion types so
change datatypes very carefully, if at all).
19. Select the 'Mappings' option from the left-hand-side and map the
Input Columns and Destination columns as desired.
20. Select the 'OK' button and select 'F5' to execute the new package.
Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer
this list into sql ?
For example,
update table1 set column1 = false where id in ( {the list of ids } )On Mar 12, 7:30 pm, "John Grandy" <johnagrandy-at-gmail-dot-com>
wrote:
> How to write a dtsx that loads a list of ids from an xls and incorporates
> this list into sql ?
> For example,
> update table1 set column1 = false where id in ( {the list of ids } )
These directions will get an excel spreadsheet into a table that you
can then do: 'update table1 set column1 = false where id in (select id
from NewlyCreatedImportTable)':
1. Create a new Integration Services project.
2. Right click in the 'Connection Managers' tab and select 'New
Connection...' from the drop-down list.
3. Select the 'EXCEL' type and the 'Add' button.
4. Select the 'Browse...' button to browse for the Excel file and
select the 'Open' button.
5. If the first line of the Excel file contains the column names,
select the radio button to the left of: "First row has column names"
then select the 'Ok' button.
6. Right click in the 'Connection Managers' tab and select 'New OLE
DB Connection...' from the drop-down list.
7. Select the 'New...' button and select a Server name from the drop-
down list and select 'OK' and select the 'OK' button again.
8. Drag and drop a 'Data Flow Task' control from the Toolbox onto the
'Control Flow' tab/pane.
9. Select the 'Data Flow' tab/pane.
10. Drag and drop an 'Excel Source' control from the Toolbox onto the
'Data Flow' tab/pane.
11. Right click the 'Excel Source' control and select 'Edit...'
12. Below 'OLE DB connection manager:' select the connection manager
created in steps 2-5.
13. Select the sheet number from the drop-down list below: "Name of
the Excel sheet:" and then select 'OK.'
14. Drag and drop an 'OLE DB Destination' control from the Toolbox
onto the 'Data Flow' tab/pane.
15. Drag the green arrow from the 'Excel Source' control to the 'OLE
DB Destination' control.
16. Right click the 'OLE DB Destination' control and select the
'Edit...' button
17. Below 'OLE DB connection manager:', select the connection control
created in steps 6-7.
18. Select the 'New...' button and edit the sql query for the table
name and column names of the new table to be created (NOTE: SSIS is
very picky on the conversion types so
change datatypes very carefully, if at all).
19. Select the 'Mappings' option from the left-hand-side and map the
Input Columns and Destination columns as desired.
20. Select the 'OK' button and select 'F5' to execute the new package.
Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer
Sunday, February 26, 2012
DTS2000 error
Hi
When I try to use DTS 2000 Packages in SQL2005 CTP I obtain this error:
When I try to use DTS 2000 Packages in SQL2005 CTP I obtain this error:
Could not load file or assembly 'Microsoft.SqlServer.Dts80, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. Impossibile trovare il file specificato. (Microsoft.SqlServer.DtsObjectExplorerUI)
Anyone can help me?
ThanksWhich CTP version are you using?
Look in your DTS\binn folder (by default this is at "C:\program files\Microsoft SQL Server\90\DTS\Binn") and see if there are any files of the names "Microsoft.SQLServer.DTS8*.dll"
I don't have "Microsoft.SqlServer.Dts80.dll" but I do have "Microsoft.SQLServer.DTS8HelperObjectModel.dll", "Microsoft.SQLServer.DTS8HelperUtility.dll" & "Microsoft.SQLServer.Exec80PackageUtil.dll"
and in "C:\program files\Microsoft SQL Server\90\DTS\Tasks" I have "Microsoft.SQLServer.Exec80PackageTask.dll".
Do you have these files, or similarly named files, on your system?
I am on the June CTP by the way. All of these files are version number 9.0.1187.0 which leads me to think that you may be on an earlier version.
You have the choice of whether to install the DTS2000 runtime or not when you install SSIS. Do you remember if you chose to install it?
-Jamie
Wednesday, February 15, 2012
DTS to truncate excel and load fresh data
I have done DTS that export data from SQL to .xls, it works perfect, my problem is my table from SQL get truncated everytime before i load data but my .xls file always come with previous records which I don't want. i.e. if my Sql table had 3 rows , when i finish to execute the dts, my .xls come with 3 rows, when I exec again, my table get truncated and my .xls add another 3 rows. How can I solve thisCan you try again?
I'm not sure I follow you...
Can you put the steps you do down in bullets...and what the final; result would be
1. I DTS in to a table
2. ect
I expect: x|||You want to "truncate" the records in the excel spreadsheet... correct ?|||1 - I truncate TableA in SQL
2 - I select few fields from TableB and insert into TableA
3 - I select everything from TableA and extract it to .xls (here i need to clear all fields in this .xls if there was any so that I can populate this .xls with new results)|||Thanks I got the answer. One more problem, when I drop the .xls table everytime I create it again and load it with data then it leave cloums which had last data and put data on the last line. Lets say I had 4 lines before and dropped the table and reated it again, and load data, it will load data from the 5th line onwards, how can I resolve this|||I'd like to know how you managed to truncate your Excel file. When I execute my DTS package, data keeps getting added to the Excel file, appended on to existing data. I want to wipe everything clean first. How do you do it?|||I'm also facing the same kind of situation..plz let me know if you've found a solution|||I have done this by recreating the Excel spreadsheet. The following VBScript code should do this for you.
'************************************************* *********
' Visual Basic ActiveX Script
'************************************************* *********
Function Main()
Dim oFSO
Dim xlApp
Dim wkbNewBook
Dim strBookName
Const xlNormal = -4143
Const READ_WRITE = -1
' ****************
' Remove existing Excel Workbook
' **************
Set oFSO = CreateObject("Scripting.FileSystemObject")
If Not oFSO.FolderExists("C:\folderName") Then
oFSO.CreateFolder ("C:\folderName")
End If
If oFSO.FileExists("C:\folderName\FileName.xls") Then
oFSO.DeleteFile ("C:\folderName\FileName.xls")
End If
' ****************
' Create new Excel Workbook
' **************
' Create object
Set xlApp = CreateObject("Excel.Application")
' Add new workbook to Workbooks collection.
Set wkbNewBook = xlApp.Workbooks.Add
' Specify path to save workbook.
strBookName = "C:\folderName\FileName.xls"
' ****************
' Format new Excel Workbook
' **************
With wkbNewBook
.Sheets(1).Select
.Sheets(1).Name = "SheetName"
.Sheets(1).Range("A1").FormulaR1C1 = "Field1Name"
.Sheets(1).Range("B1").FormulaR1C1 = "Field2Name"
.Sheets(1).Range("C1").FormulaR1C1 = "Field3Name"
.SaveAs strBookName, xlNormal ,,,READ_WRITE
.Close
End With
Set wkbNewBook = Nothing
Set xlApp = Nothing
End Function
##############################33
Originally posted by msenoelo
I have done DTS that export data from SQL to .xls, it works perfect, my problem is my table from SQL get truncated everytime before i load data but my .xls file always come with previous records which I don't want. i.e. if my Sql table had 3 rows , when i finish to execute the dts, my .xls come with 3 rows, when I exec again, my table get truncated and my .xls add another 3 rows. How can I solve this
I'm not sure I follow you...
Can you put the steps you do down in bullets...and what the final; result would be
1. I DTS in to a table
2. ect
I expect: x|||You want to "truncate" the records in the excel spreadsheet... correct ?|||1 - I truncate TableA in SQL
2 - I select few fields from TableB and insert into TableA
3 - I select everything from TableA and extract it to .xls (here i need to clear all fields in this .xls if there was any so that I can populate this .xls with new results)|||Thanks I got the answer. One more problem, when I drop the .xls table everytime I create it again and load it with data then it leave cloums which had last data and put data on the last line. Lets say I had 4 lines before and dropped the table and reated it again, and load data, it will load data from the 5th line onwards, how can I resolve this|||I'd like to know how you managed to truncate your Excel file. When I execute my DTS package, data keeps getting added to the Excel file, appended on to existing data. I want to wipe everything clean first. How do you do it?|||I'm also facing the same kind of situation..plz let me know if you've found a solution|||I have done this by recreating the Excel spreadsheet. The following VBScript code should do this for you.
'************************************************* *********
' Visual Basic ActiveX Script
'************************************************* *********
Function Main()
Dim oFSO
Dim xlApp
Dim wkbNewBook
Dim strBookName
Const xlNormal = -4143
Const READ_WRITE = -1
' ****************
' Remove existing Excel Workbook
' **************
Set oFSO = CreateObject("Scripting.FileSystemObject")
If Not oFSO.FolderExists("C:\folderName") Then
oFSO.CreateFolder ("C:\folderName")
End If
If oFSO.FileExists("C:\folderName\FileName.xls") Then
oFSO.DeleteFile ("C:\folderName\FileName.xls")
End If
' ****************
' Create new Excel Workbook
' **************
' Create object
Set xlApp = CreateObject("Excel.Application")
' Add new workbook to Workbooks collection.
Set wkbNewBook = xlApp.Workbooks.Add
' Specify path to save workbook.
strBookName = "C:\folderName\FileName.xls"
' ****************
' Format new Excel Workbook
' **************
With wkbNewBook
.Sheets(1).Select
.Sheets(1).Name = "SheetName"
.Sheets(1).Range("A1").FormulaR1C1 = "Field1Name"
.Sheets(1).Range("B1").FormulaR1C1 = "Field2Name"
.Sheets(1).Range("C1").FormulaR1C1 = "Field3Name"
.SaveAs strBookName, xlNormal ,,,READ_WRITE
.Close
End With
Set wkbNewBook = Nothing
Set xlApp = Nothing
End Function
##############################33
Originally posted by msenoelo
I have done DTS that export data from SQL to .xls, it works perfect, my problem is my table from SQL get truncated everytime before i load data but my .xls file always come with previous records which I don't want. i.e. if my Sql table had 3 rows , when i finish to execute the dts, my .xls come with 3 rows, when I exec again, my table get truncated and my .xls add another 3 rows. How can I solve this
Subscribe to:
Posts (Atom)