Hi,
What is the best way to generate a constant String from a Table (Only 1 row)
.
I have some sql statements returning a few rows. I want to also return a row
with a Constant String along with this sql statement..
e.g.
Select * from orders
Union
Select 'End of Order Select' from '
I can try selecting it from orders table, but this should return only one ro
w.
Any ideas ?
- AnandSorry for the trouble, found out the answer that I can just do
Select 'End of Order Select'
"S Anand" wrote:
> Hi,
> What is the best way to generate a constant String from a Table (Only 1 ro
w).
> I have some sql statements returning a few rows. I want to also return a r
ow
> with a Constant String along with this sql statement..
> e.g.
> Select * from orders
> Union
> Select 'End of Order Select' from '
> I can try selecting it from orders table, but this should return only one
row.
> Any ideas ?
> --
> - Anand|||hi Anand
This will work fine if you are note selecting any value from a table. If u
require a value
from a table along with a constant value, then u require to do like this:
SELECT TOP 1 'Const Value', <COLUMNS> FROM <TABLE>
you query can be modified as
Select * from orders
Union ALL
Select 'End of Order Select'
try using UNION ALL if u definately want the text to be displayed.
in case of UNION the second table will not display a value if the same value
exists
in the main table.
Hope this gives u a better picture
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"S Anand" wrote:
> Sorry for the trouble, found out the answer that I can just do
> Select 'End of Order Select'
> "S Anand" wrote:
>|||A few comments:
> Select 'End of Order Select'
Use square brackets of double-quotes around the produced column name instead
. The column you produce
in the result is an identifier and non-standard identifiers are delimited wi
th double-quotes in SNAI
SQL (and SQL Server) and SQL Server also allow double-quotes. Why SQL Server
allow single quotes for
identifiers in this particular case is beyond my understanding, very strange
..
Don't do SELECT *. It might just have been an example, but imagine of the ta
ble structure changes
and you add or remove columns. The UNION won't work.
Also, don't expect the SELECT with a constant to come last unless you do an
ORDER BY for the UNION.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S Anand" <x@.hotmail.com> wrote in message
news:81178FBE-0B61-411E-8836-D01240F81227@.microsoft.com...
> Sorry for the trouble, found out the answer that I can just do
> Select 'End of Order Select'
> "S Anand" wrote:
>
Showing posts with label generate. Show all posts
Showing posts with label generate. Show all posts
Sunday, February 19, 2012
Sunday, February 12, 2012
Best way to generate script
Hi!
I have to make a program that copy a database structure. Right now i'm using SMO but it take a lot of time just to copy all tables. Here is what i'm doing:
ServeurLocal.SetDefaultInitFields(GetType(Table), "IsSystemObject")
For Each uneTable In BDConfig.Tables
If Not uneTable.IsSystemObject Then
NouvelleBD.ExecuteNonQuery(uneTable.Script)
End If
Next
We have over 700 tables and it take me more than 5 min to copy all the tables and i haven't copy all the view and stored proc yet. The problem seems that i execute one by one each script. So i was wondering is there a way to stock all the script in some kind of object and then when the FOR is done execute this big script. Is it possible to create an object script which we can add all the script for each table ?
Thank and sorry about my bad English ^_^
I found how do to it. I have to use an object Transfer. Then i generate a script for all my object and execute this script.|||I know you've found another way to address the problem, but here's a bit of code that can create the script you were actually attempting to create, and will put the tables first, the views second and the stored procedures last in the script:
Dim scrDBScript As Scripter
Dim objSMOObjects(1) As SqlSmoObject
Dim intObjCount As Integer
intObjCount = 0
Dim srv As Server
srv = New Server("MyServer")
'Define a Database object variable by supplying the server and the database name arguments in the constructor.
Dim db As Database
Dim tbColl As TableCollection
Dim tb As Table
Dim vwColl As ViewCollection
Dim vw As View
Dim spColl As StoredProcedureCollection
Dim sp As StoredProcedure
db = New Database(srv, "AdventureWorks")
tbColl = db.Tables
For Each tb In tbColl
objSMOObjects(intObjCount) = tb 'Script each table
intObjCount += 1
Next
vwColl = db.Views
For Each vw In vwColl
objSMOObjects(intObjCount) = vw 'Script each view
intObjCount += 1
Next
spColl = db.StoredProcedures
For Each sp In spColl
objSMOObjects(intObjCount) = sp 'Script each stored procedure
intObjCount += 1
Next
scrDBScript = New Scripter(srv)
scrDBScript.Options.FileName = "c:\DBScript.sql"
scrDBScript.Options.IncludeHeaders = True
scrDBScript.Options.AppendToFile = True
ReDim Preserve objSMOObjects(intObjCount - 1)
scrDBScript.Script(objSMOObjects)
This may be something you would want to try another time, but shouldn't perform as badly as you're currently experiencing.
|||Thanks for your answer. Actually i have some problem trying to copy my DB. I have tried using a Tranfer Object and also like your method. My problem is that we have a lot of views where the query is using others views. I have the same problem with stored proc that call other stored proc. The problem is when i'm trying to execute the script for example for the views. I've got error because some views can't be created because some views are missing. In fact they are not missing they are just not created yet.
Maybe i'm not using the best way to do what i need to do. I have web application and in some page the user can create a copy of the DB that he is using. So all i want i to copy all the DB in a new one. Maybe i shouldn't use SMO to do that.....i don't really know what is the best way to do it.
|||I just played with some code and got a successful copy of AdventureWorks using the Transfer object. Here's my demo code - see if it accomplishes what you're trying to do.
Dim srv As Server
srv = New Server("TestServer")
'Define a Database object variable by supplying the server and the database name arguments in the constructor.
Dim db As Database
db = srv.Databases("AdventureWorks")
Dim strDBName As String
strDBName = "TestDatabase"
Dim dbCopy As Database
Dim dbFG As FileGroup
Dim dbFile As DataFile
Dim dbLogFile As LogFile
dbCopy = New Database(srv, strDBName) 'Instantiate the new database
dbFG = New FileGroup(dbCopy, "PRIMARY") 'Instantiate the PRIMARY filegroup
dbCopy.FileGroups.Add(dbFG) 'Add the FileGroup
dbFile = New DataFile(dbFG, strDBName + "_Data") 'Instantiate the data file within the filegroup
dbFG.Files.Add(dbFile) 'Add the File
dbFile.FileName = "D:\MSSQL.1\MSSQL\Data\" + strDBName + "_Data.mdf" 'Define the actual file system name
dbFile.Size = 25.0 * 1024.0 'Define the size of the file
dbFile.GrowthType = FileGrowthType.Percent 'Define the Growth Type
dbFile.Growth = 25.0 'Define the Growth Percent
dbFile.MaxSize = 100.0 * 1024.0 'Define the Max database size
dbLogFile = New LogFile(dbCopy, strDBName + "_Log") 'Instantiate the log file (no filegroup for log files)
dbCopy.LogFiles.Add(dbLogFile) 'Add the log file
dbLogFile.FileName = "D:\MSSQL.1\MSSQL\Data\" + strDBName + "_Log.ldf" 'Define the log file system name
dbLogFile.Size = 10.0 * 1024.0 'Define the size of the log file
dbLogFile.GrowthType = FileGrowthType.Percent 'Define the Growth Type
dbLogFile.Growth = 25.0 'Define the Growth Percent
dbCopy.Create() 'Create the database
'Define a Transfer object and set the required options and properties.
Dim xfr As Transfer
xfr = New Transfer(db)
xfr.CopyAllObjects = True
xfr.CopyAllUsers = True
xfr.Options.WithDependencies = True
xfr.Options.ContinueScriptingOnError = True
xfr.DestinationDatabase = strDBName
xfr.DestinationServer = srv.Name
xfr.DestinationLoginSecure = True
xfr.CopySchema = True
'Script the transfer. Alternatively perform immediate data transfer with TransferData method.
xfr.TransferData()
I've found another way to make my copy and it's much faster than using the Transfer object. Using the Transfert Object it took me 30sec to copy all the tables (715 tables). Now it took me 15sec to copy all the BD(715 tables + 315 view, 236 stored proc, 32 function). Insteand of doing a copy i've created a Backup. Using the backup Object i save the backup on my drive. Then i create a the New DB and then i restore the backup on this new DB. It's a lot faster and i have no error when i'm using this method.|||
Backup/restore is the most trouble-free way to get a copy of an existing database because nothing is recompiled or verified by SQL Server.
For example, if a view or stored procedure references a column that no longer exists, you cannot generate and execute a script because it will fail. The object(s) in question is invalid in the source database and will continue to be invalid in the new database. This method provides an exact copy of what you have.
I don't have experience with the scripting that you are referring to but it would seem to me that if the scripting method supports dependencies, then your dependency information is missing or corrupt in the database. For example, if MyProc1 executes MyProc2, SQL Server will only have dependency information IF MyProc2 was created first followed by MyProc1. If that is true, when you generate a SQL Script (at least through Enterprise Manager's Generate SQL Script wizard, dependency information will be used to properly sequence the objects in the script.
Best way to generate script
Hi!
I have to make a program that copy a database structure. Right now i'm using SMO but it take a lot of time just to copy all tables. Here is what i'm doing:
ServeurLocal.SetDefaultInitFields(GetType(Table), "IsSystemObject")
For Each uneTable In BDConfig.Tables
If Not uneTable.IsSystemObject Then
NouvelleBD.ExecuteNonQuery(uneTable.Script)
End If
Next
We have over 700 tables and it take me more than 5 min to copy all the tables and i haven't copy all the view and stored proc yet. The problem seems that i execute one by one each script. So i was wondering is there a way to stock all the script in some kind of object and then when the FOR is done execute this big script. Is it possible to create an object script which we can add all the script for each table ?
Thank and sorry about my bad English ^_^
I found how do to it. I have to use an object Transfer. Then i generate a script for all my object and execute this script.|||I know you've found another way to address the problem, but here's a bit of code that can create the script you were actually attempting to create, and will put the tables first, the views second and the stored procedures last in the script:
Dim scrDBScript As Scripter
Dim objSMOObjects(1) As SqlSmoObject
Dim intObjCount As Integer
intObjCount = 0
Dim srv As Server
srv = New Server("MyServer")
'Define a Database object variable by supplying the server and the database name arguments in the constructor.
Dim db As Database
Dim tbColl As TableCollection
Dim tb As Table
Dim vwColl As ViewCollection
Dim vw As View
Dim spColl As StoredProcedureCollection
Dim sp As StoredProcedure
db = New Database(srv, "AdventureWorks")
tbColl = db.Tables
For Each tb In tbColl
objSMOObjects(intObjCount) = tb 'Script each table
intObjCount += 1
Next
vwColl = db.Views
For Each vw In vwColl
objSMOObjects(intObjCount) = vw 'Script each view
intObjCount += 1
Next
spColl = db.StoredProcedures
For Each sp In spColl
objSMOObjects(intObjCount) = sp 'Script each stored procedure
intObjCount += 1
Next
scrDBScript = New Scripter(srv)
scrDBScript.Options.FileName = "c:\DBScript.sql"
scrDBScript.Options.IncludeHeaders = True
scrDBScript.Options.AppendToFile = True
ReDim Preserve objSMOObjects(intObjCount - 1)
scrDBScript.Script(objSMOObjects)
This may be something you would want to try another time, but shouldn't perform as badly as you're currently experiencing.
|||Thanks for your answer. Actually i have some problem trying to copy my DB. I have tried using a Tranfer Object and also like your method. My problem is that we have a lot of views where the query is using others views. I have the same problem with stored proc that call other stored proc. The problem is when i'm trying to execute the script for example for the views. I've got error because some views can't be created because some views are missing. In fact they are not missing they are just not created yet.
Maybe i'm not using the best way to do what i need to do. I have web application and in some page the user can create a copy of the DB that he is using. So all i want i to copy all the DB in a new one. Maybe i shouldn't use SMO to do that.....i don't really know what is the best way to do it.
|||I just played with some code and got a successful copy of AdventureWorks using the Transfer object. Here's my demo code - see if it accomplishes what you're trying to do.
Dim srv As Server
srv = New Server("TestServer")
'Define a Database object variable by supplying the server and the database name arguments in the constructor.
Dim db As Database
db = srv.Databases("AdventureWorks")
Dim strDBName As String
strDBName = "TestDatabase"
Dim dbCopy As Database
Dim dbFG As FileGroup
Dim dbFile As DataFile
Dim dbLogFile As LogFile
dbCopy = New Database(srv, strDBName) 'Instantiate the new database
dbFG = New FileGroup(dbCopy, "PRIMARY") 'Instantiate the PRIMARY filegroup
dbCopy.FileGroups.Add(dbFG) 'Add the FileGroup
dbFile = New DataFile(dbFG, strDBName + "_Data") 'Instantiate the data file within the filegroup
dbFG.Files.Add(dbFile) 'Add the File
dbFile.FileName = "D:\MSSQL.1\MSSQL\Data\" + strDBName + "_Data.mdf" 'Define the actual file system name
dbFile.Size = 25.0 * 1024.0 'Define the size of the file
dbFile.GrowthType = FileGrowthType.Percent 'Define the Growth Type
dbFile.Growth = 25.0 'Define the Growth Percent
dbFile.MaxSize = 100.0 * 1024.0 'Define the Max database size
dbLogFile = New LogFile(dbCopy, strDBName + "_Log") 'Instantiate the log file (no filegroup for log files)
dbCopy.LogFiles.Add(dbLogFile) 'Add the log file
dbLogFile.FileName = "D:\MSSQL.1\MSSQL\Data\" + strDBName + "_Log.ldf" 'Define the log file system name
dbLogFile.Size = 10.0 * 1024.0 'Define the size of the log file
dbLogFile.GrowthType = FileGrowthType.Percent 'Define the Growth Type
dbLogFile.Growth = 25.0 'Define the Growth Percent
dbCopy.Create() 'Create the database
'Define a Transfer object and set the required options and properties.
Dim xfr As Transfer
xfr = New Transfer(db)
xfr.CopyAllObjects = True
xfr.CopyAllUsers = True
xfr.Options.WithDependencies = True
xfr.Options.ContinueScriptingOnError = True
xfr.DestinationDatabase = strDBName
xfr.DestinationServer = srv.Name
xfr.DestinationLoginSecure = True
xfr.CopySchema = True
'Script the transfer. Alternatively perform immediate data transfer with TransferData method.
xfr.TransferData()
I've found another way to make my copy and it's much faster than using the Transfer object. Using the Transfert Object it took me 30sec to copy all the tables (715 tables). Now it took me 15sec to copy all the BD(715 tables + 315 view, 236 stored proc, 32 function). Insteand of doing a copy i've created a Backup. Using the backup Object i save the backup on my drive. Then i create a the New DB and then i restore the backup on this new DB. It's a lot faster and i have no error when i'm using this method.|||
Backup/restore is the most trouble-free way to get a copy of an existing database because nothing is recompiled or verified by SQL Server.
For example, if a view or stored procedure references a column that no longer exists, you cannot generate and execute a script because it will fail. The object(s) in question is invalid in the source database and will continue to be invalid in the new database. This method provides an exact copy of what you have.
I don't have experience with the scripting that you are referring to but it would seem to me that if the scripting method supports dependencies, then your dependency information is missing or corrupt in the database. For example, if MyProc1 executes MyProc2, SQL Server will only have dependency information IF MyProc2 was created first followed by MyProc1. If that is true, when you generate a SQL Script (at least through Enterprise Manager's Generate SQL Script wizard, dependency information will be used to properly sequence the objects in the script.
Best way to generate next sequence number of a table.
Hi,
Can you tell me the best way to get a next unique sequence
integer number which fits in a table. My application supports multi users
and at a single point of time users may add a new row to the table. But it
should fit to generate a unique next available integer value.
For eg: my table contains EMPID int, EMP_Name varchar(50)
EMPID is a primary key.
Can any one suggest me the best way to do it?
Thanks in advance.
VenkatHi,
create table Employees
(
EmpID int identity(1, 1) not null, --this will increment with each
insert by one.
Emp_Name varchar(50)
)
HTH
Peter
"Venkat" <stammana@.palantirsolutions.com> wrote in message
news:eb$gxjFSGHA.4300@.TK2MSFTNGP14.phx.gbl...
> Hi,
>
> Can you tell me the best way to get a next unique sequence
> integer number which fits in a table. My application supports multi users
> and at a single point of time users may add a new row to the table. But it
> should fit to generate a unique next available integer value.
>
> For eg: my table contains EMPID int, EMP_Name varchar(50)
> EMPID is a primary key.
>
> Can any one suggest me the best way to do it?
>
> Thanks in advance.
>
> --
> Venkat
>|||After doing an insert, you can get the most recently inserted id with:
SELECT SCOPE_IDENTITY()|||I would re-think this design. Where is the check digit so I can
validate it? Why is the employee identifer a varying length integer
issued internally where it cannot be verified?|||> Why is the employee identifer a varying length integer
> issued internally where it cannot be verified?
can you suggest a better alternative?
Can you tell me the best way to get a next unique sequence
integer number which fits in a table. My application supports multi users
and at a single point of time users may add a new row to the table. But it
should fit to generate a unique next available integer value.
For eg: my table contains EMPID int, EMP_Name varchar(50)
EMPID is a primary key.
Can any one suggest me the best way to do it?
Thanks in advance.
VenkatHi,
create table Employees
(
EmpID int identity(1, 1) not null, --this will increment with each
insert by one.
Emp_Name varchar(50)
)
HTH
Peter
"Venkat" <stammana@.palantirsolutions.com> wrote in message
news:eb$gxjFSGHA.4300@.TK2MSFTNGP14.phx.gbl...
> Hi,
>
> Can you tell me the best way to get a next unique sequence
> integer number which fits in a table. My application supports multi users
> and at a single point of time users may add a new row to the table. But it
> should fit to generate a unique next available integer value.
>
> For eg: my table contains EMPID int, EMP_Name varchar(50)
> EMPID is a primary key.
>
> Can any one suggest me the best way to do it?
>
> Thanks in advance.
>
> --
> Venkat
>|||After doing an insert, you can get the most recently inserted id with:
SELECT SCOPE_IDENTITY()|||I would re-think this design. Where is the check digit so I can
validate it? Why is the employee identifer a varying length integer
issued internally where it cannot be verified?|||> Why is the employee identifer a varying length integer
> issued internally where it cannot be verified?
can you suggest a better alternative?
Best way to generate Excel from SQL Server?
I have a requirement to generate a moderately complex spreadsheet containing
multiple worksheets and formulas from within SQL Server. I'm interested in
what is the recommended way of doing this. I will need to organize the data
within a stored procedure, generate the excel output and save it in a file,
which the application, which is web-based, will ship to the client's browser
to hopefully pop up into Excel. I've looked into generating HTML and XML
output, which can be loaded into Excel and functions as a spreadsheet, but i
t
would be better if I could generate an xls file in native Excel format. That
is because the end user should be able to work with these files over time,
and I would not want to have to deal with incompatibility issues.
Other options I'm considering:
1. Analysis Services - I need to research this more
2. Reporting Services - Ditto
3. Writing a procedure in C# to take the logic outside of SQLServer and
thereby having better access to Office API.
Thanks for any recommendations.Hi Steve,
Without thinking over so much this kind of task seems very affordable
by means of DTS along with some custom tasks done with VbScript (or
something like that)
Let me know your doubts or concerns with this.
Regards,
"Steve Elliott" wrote:
> I have a requirement to generate a moderately complex spreadsheet containi
ng
> multiple worksheets and formulas from within SQL Server. I'm interested in
> what is the recommended way of doing this. I will need to organize the dat
a
> within a stored procedure, generate the excel output and save it in a file
,
> which the application, which is web-based, will ship to the client's brows
er
> to hopefully pop up into Excel. I've looked into generating HTML and XML
> output, which can be loaded into Excel and functions as a spreadsheet, but
it
> would be better if I could generate an xls file in native Excel format. Th
at
> is because the end user should be able to work with these files over time,
> and I would not want to have to deal with incompatibility issues.
> Other options I'm considering:
> 1. Analysis Services - I need to research this more
> 2. Reporting Services - Ditto
> 3. Writing a procedure in C# to take the logic outside of SQLServer and
> thereby having better access to Office API.
> Thanks for any recommendations.|||I've had a project with similar requirements.
What I did was encapsulate my complex queries in stored procedures.
I called these stored procedures via classic asp and rendered the output in
excel format.
I then created an excel macro for the user to do any requisite formatting.
"Steve Elliott" wrote:
> I have a requirement to generate a moderately complex spreadsheet containi
ng
> multiple worksheets and formulas from within SQL Server. I'm interested in
> what is the recommended way of doing this. I will need to organize the dat
a
> within a stored procedure, generate the excel output and save it in a file
,
> which the application, which is web-based, will ship to the client's brows
er
> to hopefully pop up into Excel. I've looked into generating HTML and XML
> output, which can be loaded into Excel and functions as a spreadsheet, but
it
> would be better if I could generate an xls file in native Excel format. Th
at
> is because the end user should be able to work with these files over time,
> and I would not want to have to deal with incompatibility issues.
> Other options I'm considering:
> 1. Analysis Services - I need to research this more
> 2. Reporting Services - Ditto
> 3. Writing a procedure in C# to take the logic outside of SQLServer and
> thereby having better access to Office API.
> Thanks for any recommendations.|||there are lot way ways to get report into Excel.. the simple way is just
create the query to create view then open excel sheet then import the view
into excel by using Data Menu then Getexternal Data.
hope this will help u.
Regards
S Kaliyan
"Steve Elliott" <SteveElliott@.discussions.microsoft.com> wrote in message
news:C2E9EF12-A3F4-477B-A882-113412E03845@.microsoft.com...
> I have a requirement to generate a moderately complex spreadsheet
containing
> multiple worksheets and formulas from within SQL Server. I'm interested in
> what is the recommended way of doing this. I will need to organize the
data
> within a stored procedure, generate the excel output and save it in a
file,
> which the application, which is web-based, will ship to the client's
browser
> to hopefully pop up into Excel. I've looked into generating HTML and XML
> output, which can be loaded into Excel and functions as a spreadsheet, but
it
> would be better if I could generate an xls file in native Excel format.
That
> is because the end user should be able to work with these files over time,
> and I would not want to have to deal with incompatibility issues.
> Other options I'm considering:
> 1. Analysis Services - I need to research this more
> 2. Reporting Services - Ditto
> 3. Writing a procedure in C# to take the logic outside of SQLServer and
> thereby having better access to Office API.
> Thanks for any recommendations.
multiple worksheets and formulas from within SQL Server. I'm interested in
what is the recommended way of doing this. I will need to organize the data
within a stored procedure, generate the excel output and save it in a file,
which the application, which is web-based, will ship to the client's browser
to hopefully pop up into Excel. I've looked into generating HTML and XML
output, which can be loaded into Excel and functions as a spreadsheet, but i
t
would be better if I could generate an xls file in native Excel format. That
is because the end user should be able to work with these files over time,
and I would not want to have to deal with incompatibility issues.
Other options I'm considering:
1. Analysis Services - I need to research this more
2. Reporting Services - Ditto
3. Writing a procedure in C# to take the logic outside of SQLServer and
thereby having better access to Office API.
Thanks for any recommendations.Hi Steve,
Without thinking over so much this kind of task seems very affordable
by means of DTS along with some custom tasks done with VbScript (or
something like that)
Let me know your doubts or concerns with this.
Regards,
"Steve Elliott" wrote:
> I have a requirement to generate a moderately complex spreadsheet containi
ng
> multiple worksheets and formulas from within SQL Server. I'm interested in
> what is the recommended way of doing this. I will need to organize the dat
a
> within a stored procedure, generate the excel output and save it in a file
,
> which the application, which is web-based, will ship to the client's brows
er
> to hopefully pop up into Excel. I've looked into generating HTML and XML
> output, which can be loaded into Excel and functions as a spreadsheet, but
it
> would be better if I could generate an xls file in native Excel format. Th
at
> is because the end user should be able to work with these files over time,
> and I would not want to have to deal with incompatibility issues.
> Other options I'm considering:
> 1. Analysis Services - I need to research this more
> 2. Reporting Services - Ditto
> 3. Writing a procedure in C# to take the logic outside of SQLServer and
> thereby having better access to Office API.
> Thanks for any recommendations.|||I've had a project with similar requirements.
What I did was encapsulate my complex queries in stored procedures.
I called these stored procedures via classic asp and rendered the output in
excel format.
I then created an excel macro for the user to do any requisite formatting.
"Steve Elliott" wrote:
> I have a requirement to generate a moderately complex spreadsheet containi
ng
> multiple worksheets and formulas from within SQL Server. I'm interested in
> what is the recommended way of doing this. I will need to organize the dat
a
> within a stored procedure, generate the excel output and save it in a file
,
> which the application, which is web-based, will ship to the client's brows
er
> to hopefully pop up into Excel. I've looked into generating HTML and XML
> output, which can be loaded into Excel and functions as a spreadsheet, but
it
> would be better if I could generate an xls file in native Excel format. Th
at
> is because the end user should be able to work with these files over time,
> and I would not want to have to deal with incompatibility issues.
> Other options I'm considering:
> 1. Analysis Services - I need to research this more
> 2. Reporting Services - Ditto
> 3. Writing a procedure in C# to take the logic outside of SQLServer and
> thereby having better access to Office API.
> Thanks for any recommendations.|||there are lot way ways to get report into Excel.. the simple way is just
create the query to create view then open excel sheet then import the view
into excel by using Data Menu then Getexternal Data.
hope this will help u.
Regards
S Kaliyan
"Steve Elliott" <SteveElliott@.discussions.microsoft.com> wrote in message
news:C2E9EF12-A3F4-477B-A882-113412E03845@.microsoft.com...
> I have a requirement to generate a moderately complex spreadsheet
containing
> multiple worksheets and formulas from within SQL Server. I'm interested in
> what is the recommended way of doing this. I will need to organize the
data
> within a stored procedure, generate the excel output and save it in a
file,
> which the application, which is web-based, will ship to the client's
browser
> to hopefully pop up into Excel. I've looked into generating HTML and XML
> output, which can be loaded into Excel and functions as a spreadsheet, but
it
> would be better if I could generate an xls file in native Excel format.
That
> is because the end user should be able to work with these files over time,
> and I would not want to have to deal with incompatibility issues.
> Other options I'm considering:
> 1. Analysis Services - I need to research this more
> 2. Reporting Services - Ditto
> 3. Writing a procedure in C# to take the logic outside of SQLServer and
> thereby having better access to Office API.
> Thanks for any recommendations.
Subscribe to:
Posts (Atom)