create table by code

Hi

I developed a program. This program will use a SQL server database. How can I create the database using code. I think I need to use sql scipt like this:

create database customers

But where should I write the script and How can I make VB code implement the script.

thank

Open a connection to the server using a SQLCommand and issue the statements for creating a table / database. The appropiate syntax for creating the script can be seen in the SQL Servers BOL.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

The title says you want to create a Table, but your question is asking about creating a database, no sure which you want since they are different, but...

There are always multiple ways to do stuff, so you might want to explore a couple different ways. In code, the two that come to mind are using a SqlConnection object to run your script. The script would be run using the SqlCommand object. Both of these are part of the System.Data.SqlClient namespace which offers many tools for connecting to and running commands in SQL Server. (Note: Depending on the complexity of your script, you may need to parse it into multiple commands. For example, the SqlCommand object can only take a single batch (i.e. the stuff separated by GO when you use the query window, so you have to run those separately.)

Another option for work with database in code is SQL Management Objects or SMO. Rather than using T-SQL scripts as you would do with System.Data.SqlClient, SMO exposes the objects in SQL Server and lets you manipulate them like any other programatic object. You can find examples of working with database in SMO here, and working with Tables in SMO here.

The is no one right way, use the method that makes the most sense for you.

Mike

|||

Thank you very much.

I used the following code. It worked in the first time. But when I debugged the program for the second time, I had this error " database customs already exists"

How can I solve this problem?

And can you give me a sql script to create one database with one three-column table?

thanks

Dim cnString As String = "Data Source=.\sqlexpress;Integrated Security=True;Pooling=False"

Dim con As SqlClient.SqlConnection = New SqlClient.SqlConnection(cnString)

Dim cmdAs SqlClient.SqlCommand = New SqlClient.SqlCommand()

con.Open()

cmd.CommandType = CommandType.Text

cmd.Connection = conGet

cmd.CommandText = "create database [customs]"

cmd.ExecuteNonQuery()

con.Close()

|||Once the database is created, you need not attempt to create it again.

To create a table, look into the CREATE TABLE command...

http://msdn2.microsoft.com/en-us/library/aa258255(SQL.80).aspx

Further, you might want to look into using Sql Server Management Studio (or the Express version) to run TSQL scripts.

http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en
|||

In addition you can check the existance before actually creating the table using some query like following:

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'SomeTableName'

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Jens K. Suessmeyer wrote:

In addition you can check the existance before actually creating the table using some query like following:

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'SomeTableName'

Jens K. Suessmeyer

http://www.sqlserver2005.de

It should be even simpler to use the OBJECT_ID('SomeTableName') and check it against NULL. Though this is not restricted to tables only, ie

SELECT OBJECT_ID('SomeTableName')

could also match any other object.

create table by code

Hi

I developed a program. This program will use a SQL server database. How can I create the database using code. I think I need to use sql scipt like this:

create database customers

But where should I write the script and How can I make VB code implement the script.

thank

Open a connection to the server using a SQLCommand and issue the statements for creating a table / database. The appropiate syntax for creating the script can be seen in the SQL Servers BOL.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

The title says you want to create a Table, but your question is asking about creating a database, no sure which you want since they are different, but...

There are always multiple ways to do stuff, so you might want to explore a couple different ways. In code, the two that come to mind are using a SqlConnection object to run your script. The script would be run using the SqlCommand object. Both of these are part of the System.Data.SqlClient namespace which offers many tools for connecting to and running commands in SQL Server. (Note: Depending on the complexity of your script, you may need to parse it into multiple commands. For example, the SqlCommand object can only take a single batch (i.e. the stuff separated by GO when you use the query window, so you have to run those separately.)

Another option for work with database in code is SQL Management Objects or SMO. Rather than using T-SQL scripts as you would do with System.Data.SqlClient, SMO exposes the objects in SQL Server and lets you manipulate them like any other programatic object. You can find examples of working with database in SMO here, and working with Tables in SMO here.

The is no one right way, use the method that makes the most sense for you.

Mike

|||

Thank you very much.

I used the following code. It worked in the first time. But when I debugged the program for the second time, I had this error " database customs already exists"

How can I solve this problem?

And can you give me a sql script to create one database with one three-column table?

thanks

Dim cnString As String = "Data Source=.\sqlexpress;Integrated Security=True;Pooling=False"

Dim con As SqlClient.SqlConnection = New SqlClient.SqlConnection(cnString)

Dim cmdAs SqlClient.SqlCommand = New SqlClient.SqlCommand()

con.Open()

cmd.CommandType = CommandType.Text

cmd.Connection = conGet

cmd.CommandText = "create database [customs]"

cmd.ExecuteNonQuery()

con.Close()

|||Once the database is created, you need not attempt to create it again.

To create a table, look into the CREATE TABLE command...

http://msdn2.microsoft.com/en-us/library/aa258255(SQL.80).aspx

Further, you might want to look into using Sql Server Management Studio (or the Express version) to run TSQL scripts.

http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en
|||

In addition you can check the existance before actually creating the table using some query like following:

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'SomeTableName'

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Jens K. Suessmeyer wrote:

In addition you can check the existance before actually creating the table using some query like following:

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'SomeTableName'

Jens K. Suessmeyer

http://www.sqlserver2005.de

It should be even simpler to use the OBJECT_ID('SomeTableName') and check it against NULL. Though this is not restricted to tables only, ie

SELECT OBJECT_ID('SomeTableName')

could also match any other object.

create table and fields

I have maked this little code but dont know the way to insertt new table and fileds in database DATABASE01...

Private Sub Command1_Click()

Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection
objConn.ConnectionString = "driver={SQL Server};server=DVD377-14D9E48C\SQLEXPRESS;uid=;pwd=;database=master"
objConn.Open
objConn.Execute "CREATE DATABASE DATABASE01"

''''
'"CREATE table Mytable"
''''

''''
'"CREATE filed1" in text format
'"CREATE filed2" in text format
'"CREATE filed3" in text format
'"CREATE filed4" in date format(DD/MM/YYYY)

''''

objConn.Close

End Subu should not be creating a database from a front end code. however, to create tables open another connection object by another connection string using the newly created database name. and then use the "Create Table" SQL. Check BOL for syntax etc.

CREATE TABLE and CONSTRAINTS

well very new to sql,as I am...
I am posting this query,please explain me what it is,

Code: ( text )

1. CREATE TABLE [dbo].[DOORD_ORDER] (

2. [ORDER_NBR] [int] NOT NULL ,

3. [TOTAL_SEQ_NBR] [int] NOT NULL ,

4. [DELIVERY_IND] [char] (1) NOT NULL ,

5. [CONTACT_LST_NME] [varchar] (30) NOT NULL ,

6. [CONTACT_FRST_NME] [varchar] (40) NOT NULL ,

7. [CONTACT_TYP] [varchar] (30) NOT NULL ,

8. [CONTACT_TXT] [varchar] (100) NOT NULL ,

9. [OR_CDE] [char] (1) NOT NULL ,

10. [DROPOFF_DTE] [datetime] NOT NULL ,

11. [PICKUP_DTE] [datetime] NOT NULL ,

12. [LST_OPR_ID] [char] (8) NOT NULL ,

13. [LST_MNT_TSMP] [datetime] NOT NULL ,

14. [WAIT_INSTORE_IND] [char] (1) NOT NULL

15. ) ON [PRIMARY]

16. GO

17. ALTER TABLE [dbo].[DOORD_ORDER] WITH NOCHECK ADD

18. CONSTRAINT [PK_DOORD] PRIMARY KEY CLUSTERED

19. (

20. [ORDER_NBR]

21. ) WITH FILLFACTOR = 90 ON [PRIMARY]

22. GO

Hirak

This query creates a new table called [dbo].[DOORD_ORDER] and then adds a constraint to the table in this case designates the primary key [ORDER_NBR].

Mary|||well mary,is this oracle?

we didnt do like this in oracle,right?
please tell me what type of db query is this
hirak

Quote:

Originally Posted by mmccarthy

Hirak

This query creates a new table called [dbo].[DOORD_ORDER] and then adds a constraint to the table in this case designates the primary key [ORDER_NBR].

Mary

|||

Quote:

Originally Posted by hirak1984

well mary,is this oracle?

we didnt do like this in oracle,right?
please tell me what type of db query is this
hirak

Actually it does look like Oracle to me. It's been a while since I've used it though so I could be wrong. It could also be SQL Server. Sorry Hirak I haven't used either in a few years and they all just blend together now.

Mary|||you need not be sorry mary because you are right.

this is a sqlserver query I found out now.
I am sorry to post it in oracle forum,because then I didnt have an idea,what it was.
I dont have necessary privileges,please transfer it to the sqlserver forum

Quote:

Originally Posted by mmccarthy

Actually it does look like Oracle to me. It's been a while since I've used it though so I could be wrong. It could also be SQL Server. Sorry Hirak I haven't used either in a few years and they all just blend together now.

Mary

create table

i have this code: this is working in mysql but not in sql server 2000. could someone help me.
CREATE TABLE Project (ProjectCode varchar(100),MapNumber varchar(100),Status float(3,2));
i've got this message:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.
there is no float in SQL. Try decimal instead. you can also get rid of the colon at the end.
|||thank you very much.|||

What kind of ProjectCode is a VARCHAR(100)? Ditto for MapNumber?!?
You need to use proper datatypes and domains to ensure you don't have garbage data.

|||

harrha19 wrote:

i have this code: this is working in mysql but not in sql server 2000. could someone help me.
CREATE TABLE Project (ProjectCode varchar(100),MapNumber varchar(100),Status float(3,2));
i've got this message:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.

There is Float data type in SQL Server what is wrong is you are setting precison which you cannot do with Float in SQL Server You can only set precision and scale in Decimal and Numeric. Float is seldom used in SQL Server but most T-SQL relational algebra and calculus functions are in Float. Hope this helps.

Create Subcription via VB Code

Could you point me in the right direction to samples to create a Subcription?
--
EdgarLook in the books online, they have a snippet that shows how to do this.
-Lukasz
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"mresanchez" <mresanchez@.donot-spam.com> wrote in message
news:FAC8AC06-F962-4CA5-8D16-3B31282F8996@.microsoft.com...
> Could you point me in the right direction to samples to create a
> Subcription?
> --
> Edgar

create stored procedure in IF-structure

Hi everyone,

I'm currently struggeling in creating some SQL script to create stored procedures. I found the following example on MSDN:

Code Snippet

USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info2' AND type = 'P')
DROP PROCEDURE au_info2
GO
USE pubs
GO
CREATE PROCEDURE au_info2
@.lastname varchar(30) = 'D%',
@.firstname varchar(18) = '%'
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname LIKE @.firstname
AND au_lname LIKE @.lastname
GO

The thing is, I want to change and use it like this:

Code Snippet

USE pubsIF NOT EXISTS (SELECT name FROM sysobjects


CREATE PROCEDURE ...

USE pubs

GO

ALTER PROCEDURE au_info2 ...

But that does not seem to work.. I get the following error:

Code Snippet

Incorrect syntax near the keyword 'PROCEDURE'

Any idea's? Any help is appreciated!

Kind regards,

Frederik

The CREATE statement needs to be the first statement in the batch so you can't have it in after an IF clause.

I guess you could get round this by doing the following:

IF NOT EXISTS.....

EXEC('CREATE PROCEDURE au_info2 AS.....')

HTH!

|||Very dirty, but it works! I need it to avoid some errors when

replicating and such.. Thx!

Create Replication in Code

Does anybody have any samples of how to enable replication and create
publication using sql-dmo objects? I am trying to create replication
in a program and all I can find are ways to create the subscription.
Anything that might get me started would be great.
Thanks.
Shane Lim
On Tue, 01 Feb 2005 10:57:58 -0700, Shane Lim <gslim@.blizzardice.com>
wrote:

>Does anybody have any samples of how to enable replication and create
>publication using sql-dmo objects? I am trying to create replication
>in a program and all I can find are ways to create the subscription.
>Anything that might get me started would be great.
>Thanks.
>Shane Lim
Edit I am tring to do Merge Replication for Pull Subscriptions.
|||On Tue, 01 Feb 2005 11:03:52 -0700, Shane Lim <gslim@.blizzardice.com>
wrote:

>On Tue, 01 Feb 2005 10:57:58 -0700, Shane Lim <gslim@.blizzardice.com>
>wrote:
>
>Edit I am tring to do Merge Replication for Pull Subscriptions.
Ok I got it working. Thanks you very much Paul Ibison!!
'Paul Ibison is our Savior!!!! From
http://www.mcse.ms/archive95-2004-7-899774.html
Now all I need is a way to get a list of the current publications. So
I can give my user a list to select from to replicate too.
|||Shane,
my special powers might not be enough here - this is a
script for transactional, while you're after a merge one.
I have some similar scripts on
http://www.replicationanswers.com/Scripts.htm but they
aren't merge either. I think Hilary has one for merge - I
seem to remember him posting one up fairly recently. He's
the one.
Rgds,
Paul
|||On Wed, 2 Feb 2005 02:22:46 -0800, "Paul Ibison"
<Paul.Ibison@.Pygmalion.Com> wrote:

>Shane,
>my special powers might not be enough here - this is a
>script for transactional, while you're after a merge one.
>I have some similar scripts on
>http://www.replicationanswers.com/Scripts.htm but they
>aren't merge either. I think Hilary has one for merge - I
>seem to remember him posting one up fairly recently. He's
>the one.
>Rgds,
>Paul
Paul Actually it was quite simple to create the merge publication from
your transactional example. I pretty much just changed all the objects
to there equivilent in the merge objects. It created a publication for
me just fine. I am doing subscriptions today but in my initial testing
it seems to work just Dandy. Although I am sure I will be purchasing
the not while surfs up book as soon as its available. But since the
beta is due before then I will just have to make due like so. Thanks
again.
Shane Lim

Create Procedure in an IF block?

I am writing some code generation stuff and I am trying to get a script
like this to work:
IF (something)
BEGIN
CREATE PROCEDURE Whatever
AS
SELECT 1 as one
END
But it complains about this, so I am guessing that I can't put the
create prodcedure in an IF block.
Does anyone know of a work around for this?It's generally not a good idea to dynamically create stored procedures;
why are you trying to do that? Perhpas there's a better way to solve
the problem you're trying to do.
Stu
cmay wrote:
> I am writing some code generation stuff and I am trying to get a script
> like this to work:
> IF (something)
> BEGIN
> CREATE PROCEDURE Whatever
> AS
> SELECT 1 as one
> END
>
> But it complains about this, so I am guessing that I can't put the
> create prodcedure in an IF block.
> Does anyone know of a work around for this?|||cmay wrote:
> But it complains about this, so I am guessing that I can't put the
> create prodcedure in an IF block.
> Does anyone know of a work around for this?
If your procedure is not too complex to declare in a string, you could
create a variable that includes the CREATE PROCEDURE command and then
execute it with sp_executesql:
IF (1=1)
BEGIN
DECLARE @.sql nvarchar(1000)
SET @.sql = 'CREATE PROCEDURE Whatever
AS
SELECT 1 as one'
EXEC sp_executesql @.sql
END|||Stu wrote:
> It's generally not a good idea to dynamically create stored procedures;
> why are you trying to do that? Perhpas there's a better way to solve
> the problem you're trying to do.
> Stu
> cmay wrote:
You can use dynamic sql
IF (something)
BEGIN
exec(' CREATE PROCEDURE Whatever
AS
SELECT 1 as one')
END
But procedures are generally permenent object and why are you
interested to create them on the fly?
Regards
Amish shah|||cmay (cmay@.walshgroup.com) writes:
> I am writing some code generation stuff and I am trying to get a script
> like this to work:
> IF (something)
> BEGIN
> CREATE PROCEDURE Whatever
> AS
> SELECT 1 as one
> END
>
> But it complains about this, so I am guessing that I can't put the
> create prodcedure in an IF block.
> Does anyone know of a work around for this?
What is the real purpose of this? Using T-SQL to generate code sounds
utterly painful to me. As pointed out in another post, you would have to
use dynamic SQL, but only do this if you like to hurt yourself.
If the purpose is simply to write an installation script, I recommend that
you write the installation script in a client language: Perl, VB, VBscript
or whatever.
For more information on dynamic SQL, see
http://www.sommarskog.se/dynamic_sql.html.
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|||I am using a Code Generation program that creates a script for the
necessary stored procedures.
I guess I could put them in a big string, but I would have to make sure
I escaped all my single quotes.
Erland Sommarskog wrote:
> cmay (cmay@.walshgroup.com) writes:
> What is the real purpose of this? Using T-SQL to generate code sounds
> utterly painful to me. As pointed out in another post, you would have to
> use dynamic SQL, but only do this if you like to hurt yourself.
> If the purpose is simply to write an installation script, I recommend that
> you write the installation script in a client language: Perl, VB, VBscript
> or whatever.
> For more information on dynamic SQL, see
> http://www.sommarskog.se/dynamic_sql.html.
>
> --
> 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|||cmay (cmay@.walshgroup.com) writes:
> I am using a Code Generation program that creates a script for the
> necessary stored procedures.
> I guess I could put them in a big string, but I would have to make sure
> I escaped all my single quotes.
Ah, if you are using some program to generate the input script, putting
the CREATE PROCEDURE in dynamic SQL is a fair game. Of course you need
to double all the single quotes, and if the procedure itself employs
dynamic SQL, the result can be about unreadable. But as long as the result
is not meant to be read - who cares?
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

Create Procedure in an IF block?

I am writing some code generation stuff and I am trying to get a script
like this to work:

IF (something)
BEGIN
CREATE PROCEDURE Whatever
AS
SELECT 1 as one
END

But it complains about this, so I am guessing that I can't put the
create prodcedure in an IF block.

Does anyone know of a work around for this?It's generally not a good idea to dynamically create stored procedures;
why are you trying to do that? Perhpas there's a better way to solve
the problem you're trying to do.

Stu

cmay wrote:
> I am writing some code generation stuff and I am trying to get a script
> like this to work:
> IF (something)
> BEGIN
> CREATE PROCEDURE Whatever
> AS
> SELECT 1 as one
> END
>
> But it complains about this, so I am guessing that I can't put the
> create prodcedure in an IF block.
> Does anyone know of a work around for this?|||cmay wrote:
> But it complains about this, so I am guessing that I can't put the
> create prodcedure in an IF block.
> Does anyone know of a work around for this?

If your procedure is not too complex to declare in a string, you could
create a variable that includes the CREATE PROCEDURE command and then
execute it with sp_executesql:

IF (1=1)
BEGIN
DECLARE @.sql nvarchar(1000)
SET @.sql = 'CREATE PROCEDURE Whatever
AS
SELECT 1 as one'
EXEC sp_executesql @.sql
END|||Stu wrote:

> It's generally not a good idea to dynamically create stored procedures;
> why are you trying to do that? Perhpas there's a better way to solve
> the problem you're trying to do.
> Stu
> cmay wrote:
> > I am writing some code generation stuff and I am trying to get a script
> > like this to work:
> > IF (something)
> > BEGIN
> > CREATE PROCEDURE Whatever
> > AS
> > SELECT 1 as one
> > END
> > But it complains about this, so I am guessing that I can't put the
> > create prodcedure in an IF block.
> > Does anyone know of a work around for this?

You can use dynamic sql
IF (something)
BEGIN
exec(' CREATE PROCEDURE Whatever
AS
SELECT 1 as one')
END

But procedures are generally permenent object and why are you
interested to create them on the fly?

Regards
Amish shah|||cmay (cmay@.walshgroup.com) writes:
> I am writing some code generation stuff and I am trying to get a script
> like this to work:
> IF (something)
> BEGIN
> CREATE PROCEDURE Whatever
> AS
> SELECT 1 as one
> END
>
> But it complains about this, so I am guessing that I can't put the
> create prodcedure in an IF block.
> Does anyone know of a work around for this?

What is the real purpose of this? Using T-SQL to generate code sounds
utterly painful to me. As pointed out in another post, you would have to
use dynamic SQL, but only do this if you like to hurt yourself.

If the purpose is simply to write an installation script, I recommend that
you write the installation script in a client language: Perl, VB, VBscript
or whatever.

For more information on dynamic SQL, see
http://www.sommarskog.se/dynamic_sql.html.

--
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|||I am using a Code Generation program that creates a script for the
necessary stored procedures.

I guess I could put them in a big string, but I would have to make sure
I escaped all my single quotes.

Erland Sommarskog wrote:
> cmay (cmay@.walshgroup.com) writes:
> > I am writing some code generation stuff and I am trying to get a script
> > like this to work:
> > IF (something)
> > BEGIN
> > CREATE PROCEDURE Whatever
> > AS
> > SELECT 1 as one
> > END
> > But it complains about this, so I am guessing that I can't put the
> > create prodcedure in an IF block.
> > Does anyone know of a work around for this?
> What is the real purpose of this? Using T-SQL to generate code sounds
> utterly painful to me. As pointed out in another post, you would have to
> use dynamic SQL, but only do this if you like to hurt yourself.
> If the purpose is simply to write an installation script, I recommend that
> you write the installation script in a client language: Perl, VB, VBscript
> or whatever.
> For more information on dynamic SQL, see
> http://www.sommarskog.se/dynamic_sql.html.
>
> --
> 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|||cmay (cmay@.walshgroup.com) writes:
> I am using a Code Generation program that creates a script for the
> necessary stored procedures.
> I guess I could put them in a big string, but I would have to make sure
> I escaped all my single quotes.

Ah, if you are using some program to generate the input script, putting
the CREATE PROCEDURE in dynamic SQL is a fair game. Of course you need
to double all the single quotes, and if the procedure itself employs
dynamic SQL, the result can be about unreadable. But as long as the result
is not meant to be read - who cares?

--
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

Create Partition using ASSL

Hi,

Is there any sample code about create partition using ASSL (Analysis Service Script Language)?

thanks,

There is no ASSL. There is a DDL - data definition language for Analysis Services objects.

It is XML-based. Try in the SQL Managment Studio to right click on your partition and script it into the XMLA editor. You will see DDL definition of the partition. You can modify it, change the partition ID and name and send back to the server. You will see new partition being created as result of that.

Thanks.
Edward

This posting is provided "AS IS" with no warranties, and confers no rights

|||thanks a lot. :)

Create more than one detail

Hi K.Babu,

I have a problem how to make more than one detail in Crystal Report to one record, such as employer's information like this :

Code :0001
Name : AAAA
Address : BBBB

//Detail -1
Career :
1. ...
2. ...
3. ...

Hobby : music
Age : 27

//Detail -2
1. ...
2. ...
3. ...

Is it possible to print for the employee from Crystal Report 8.5
If yes, how can I do ?

Thank you.Your question is not very clear, but I'll do my best...

Have you tried using groups? I'm not sure how to set it up since I've never used it. Try doing a search for it.|||I have been tried using groups that is reference code_employee but I dont know how to create details for 2 tables. I mean that the first detail for first table and the second detail for second table. How to create that ?
Thank you...

Create Mining Structure and Mining Model with code

Dear friends,

I encounter a serious problem.

I would like to develop an application that can create Data Mining structures and a mining model in SQL Server 2005 with VB.NET. I tried the code from book Data Mining with SQL server 2005 in chapter 14 but did not work. Any good idea?

Please help me.

Best regards,

Manolis

Can you post the errors you're seeing?

Some of the AMO objects/methods changed slightly in the final release (this book was based on pre-release APIs). The C# sample here should help you diagnose the errors: http://www.sqlserverdatamining.com/DMCommunity/Downloads/Links_LinkRedirector.aspx?id=78.

|||

The errata for chapter 14 (and others, ironically, including how to get to this site) is at http://www.wiley.com/WileyCDA/WileyAncillary/productCd-0471462616.html

|||

Thank you very much for your help.

The errors that I can see in the code that you gave in your answer are the following and they are more or less the same as I had previously

I tried the code but initially I have encounter the following problems.

1. In any line that have the declaration As Server, As Database like in

Public Function CreateDatabase(ByVal srv As Server, ByVal databaseName As String) As Database gives me the problem that type Database is not declared the same type Server is not declared and it does not give me any option.

2. In addition to that for As DataSource, As RelationalDataSource, As RelationalDataSourceView, As ScalarMiningStructureColumn, As DataSourceViewBinding, gives me the problem that type is not declared.

3. Finally in mc = New MiningModelColumn("Yearly income", Utils.GetSyntacticallyValidID("Yearly income", Type.GetType(MiningModelColumn))) is not accesible in this context because it is 'Private'.

I have some more problems but I thing that by solving the above that I referred I will solve the rest.

Thank you any way.

Best regards,

Manolis

Create Mining Structure and Mining Model with code

Thank you very much for your help.

The errors that I can see in the code that you gave in your answer are the following and they are more or less the same as I had previously

I tried the code but initially I have encounter the following problems.

1. In any line that have the declaration As Server, As Database like in

Public Function CreateDatabase(ByVal srv As Server, ByVal databaseName As String) As Database gives me the problem that type Database is not declared the same type Server is not declared and it does not give me any option.

2. In addition to that for As DataSource, As RelationalDataSource, As RelationalDataSourceView, As ScalarMiningStructureColumn, As DataSourceViewBinding, gives me the problem that type is not declared.

3. Finally in mc = New MiningModelColumn("Yearly income", Utils.GetSyntacticallyValidID("Yearly income", Type.GetType(MiningModelColumn))) is not accesible in this context because it is 'Private'.

I have some more problems but I thing that by solving the above that I referred I will solve the rest.

Thank you any way.

Best regards,

Manolis

---

PhD student

I think you need to add a reference to Microsoft.AnalysisServices (shows up as Analysis Management Objects in the Add References dialog) and then you need to add the statement

using Microsoft.AnalysisServices;

to the top of your c# file

Create merge publication programically?

Hi all,
Can any one give me a sample code for creating merge publication?
Thank you very much!
TonyHere is a snippet of code that I got after creating my publication in SQL Server Enterprise Manager, then had it generate the scripts for me. This is for a merge replication with dynamic filters (table filtered based on their username) and anonymous pull subscriptions allowed. Note: This does NOT create the first snapshot, as it doesn't create the code for this...but I'm sure it exists.

The best way to get this code is to do all you want in EM first, then export it and use it in your code. Good luck...replication is a pain.

-- Enabling the replication database
use master
GO

exec sp_replicationdboption N'MyDatabase', N'merge publish', true
GO

use [MyDatabase]
GO

-- Adding the merge publication
exec sp_addmergepublication @.publication = N'MyDatabase-MyPublication', @.description = N'Merge publication of MyDatabase database from Publisher MyServer.', @.retention = 60, @.sync_mode = N'character', @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'true', @.enabled_for_internet = N'false', @.centralized_conflicts = N'true', @.dynamic_filters = N'true'
exec sp_addpublication_snapshot @.publication = N'MyDatabase-MyPublication',@.frequency_type = 4, @.frequency_interval = 1, @.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0, @.frequency_subday = 1, @.frequency_subday_interval = 5, @.active_start_date = 0, @.active_end_date = 0, @.active_start_time_of_day = 500, @.active_end_time_of_day = 235959
GO

exec sp_grant_publication_access @.publication = N'MyDatabase-MyPublication', @.login = N'sa'
GO
exec sp_grant_publication_access @.publication = N'MyDatabase-MyPublication', @.login = N'kellera'
GO

-- Adding the merge articles
exec sp_addmergearticle @.publication = N'MyDatabase-Master_Table', @.article = N'Master_People_Table', @.source_owner = N'dbo', @.source_object = N'Master_People_Table', @.type = N'table', @.description = null, @.column_tracking = N'true', @.pre_creation_cmd = N'drop', @.creation_script = null, @.schema_option = 0x00000000000000F1, @.article_resolver = null, @.subset_filterclause = N'vchUID=USER_NAME()'
GO

exec sp_addmergearticle @.publication = N'MyDatabase-MyPublication', @.article = N'Master_Table', @.source_owner = N'dbo', @.source_object = N'Master_Table', @.type = N'table', @.description = null, @.column_tracking = N'true', @.pre_creation_cmd = N'drop', @.creation_script = null, @.schema_option = 0x00000000000000F1, @.article_resolver = null, @.subset_filterclause = N'dtExpiration >= GETDATE()'
GO

-- Adding the article subset filter
exec sp_addmergefilter @.publication = N'MyDatabase-MyPublication', @.article = N'Master_Table', @.filtername = N'Master_Table_Master_People_Table', @.join_articlename = N'Master_People_Table', @.join_filterclause = N'Master_Table.Installer=Master_People_Table.iPers onID AND Master_People_Table.vchCategory LIKE ''Installer''', @.join_unique_key = 0
GO

Create login and user

Hi,

I’m new in this.

I’m trying to create a user with the following code, but in the yellow part it throw an error saying the user can’t be created. Please if somebody can say what the problem is or correct the problem for me I will be thankful:

Public Sub CreateUser()

Dim conn As New ServerConnection("STATION01\SQLEXPRESS", "admin", "a9078564")

'conn.DatabaseName = "VideoDB"

Dim myServer As New Server(conn)

If Not myServer.Logins.Contains(TextBox2.Text) Then

Dim db_login As New Login(myServer, TextBox2.Text)

db_login.LoginType = LoginType.SqlLogin

db_login.Create(TextBox3.Text)

Dim sys_database As New Database(myServer, "VideoDB")

Dim db_user As New User(sys_database, TextBox2.Text)

db_user.Login = TextBox2.Text

db_user.Create()

If CheckBox18.Checked Then

db_login.AddToRole("securityadmin")

db_user.AddToRole("db_owner")

Else

Dim SelectPrivilege As New ObjectPermissionSet

SelectPrivilege.Select = True

Dim InsertPrivilege As New ObjectPermissionSet

InsertPrivilege.Insert = True

Dim UpdatePrivilege As New ObjectPermissionSet

UpdatePrivilege.Update = True

Dim DeletePrivilege As New ObjectPermissionSet

DeletePrivilege.Delete = True

Dim db_table As New Table

db_table.Schema = "admin"

db_table.Name = "customers"

If CheckBox1.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox2.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox3.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If CheckBox4.Checked Then db_table.Grant(DeletePrivilege, TextBox2.Text)

db_table.Name = "capturepicture"

If CheckBox1.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

db_table.Name = "items"

If CheckBox5.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox6.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox7.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If CheckBox8.Checked Then db_table.Grant(DeletePrivilege, TextBox2.Text)

If CheckBox9.Checked Then

db_login.AddToRole("securityadmin")

db_user.AddToRole("db_securityadmin")

db_user.AddToRole("db_accessadmin")

db_user.AddToRole("CreateUser")

End If

db_table.Name = "properties"

If CheckBox10.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox11.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox12.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If RadioButton1.Checked Then

db_user.AddToRole("TransactionsFullAccess")

Else

db_user.AddToRole("TransactionsRestrictedAccess")

End If

If CheckBox15.Checked Then db_user.AddToRole("Promotions")

If CheckBox18.Checked Then db_user.AddToRole("History")

End If

MsgBox(db_login.Script)

MsgBox(db_user.Script)

End If

End Sub

Regarding the samples located on http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=195387&SiteID=1 you did just fine beside the logon Type. Which error do you get during the creation of the user ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

The only thing I see wrong with your code is four lines above the highlighted line, where the code is:

db_login.Create(TextBox3.Text)

Now, everywhere else you're referring to TextBox2.Text, but in the create of the login you're using TextBox3.Text. It may be failing due to that mismatch. Otherwise the code looks correct.

|||

Hi,

In the textbox2 I refer the username in the textbox3 I refer the password.

I change the blue part and know it creates the user, but know the problem is in the tables privileges. It throws this error:

object Microsoft.SqlServer.Management.Smo.Table does not live under Server.

Public Sub CreateUser()

Dim conn As New ServerConnection("STATION01\SQLEXPRESS", "admin", "a9078564")

Dim myServer As New Server(conn)

If Not myServer.Logins.Contains(TextBox2.Text) Then

Dim db_login As New Login(myServer, TextBox2.Text)

db_login.LoginType = LoginType.SqlLogin

db_login.Create(TextBox3.Text)

Dim sys_database As Database = myServer.Databases("VideoDB")

Dim db_user As New User(sys_database, TextBox2.Text)

db_user.Login = TextBox2.Text

db_user.Create()

If CheckBox18.Checked Then

db_login.AddToRole("securityadmin")

db_user.AddToRole("db_owner")

Else

Dim SelectPrivilege As New ObjectPermissionSet

SelectPrivilege.Select = True

Dim InsertPrivilege As New ObjectPermissionSet

InsertPrivilege.Insert = True

Dim UpdatePrivilege As New ObjectPermissionSet

UpdatePrivilege.Update = True

Dim DeletePrivilege As New ObjectPermissionSet

DeletePrivilege.Delete = True

Dim db_table As New Table

db_table.Schema = "admin"

db_table.Name = "customers"

If CheckBox1.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox2.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox3.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If CheckBox4.Checked Then db_table.Grant(DeletePrivilege, TextBox2.Text)

db_table.Name = "capturepicture"

If CheckBox1.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

db_table.Name = "items"

If CheckBox5.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox6.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox7.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If CheckBox8.Checked Then db_table.Grant(DeletePrivilege, TextBox2.Text)

If CheckBox9.Checked Then

db_login.AddToRole("securityadmin")

db_user.AddToRole("db_securityadmin")

db_user.AddToRole("db_accessadmin")

db_user.AddToRole("CreateUser")

End If

db_table.Name = "properties"

If CheckBox10.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox11.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox12.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If RadioButton1.Checked Then

db_user.AddToRole("TransactionsFullAccess")

Else

db_user.AddToRole("TransactionsRestrictedAccess")

End If

If CheckBox15.Checked Then db_user.AddToRole("Promotions")

If CheckBox18.Checked Then db_user.AddToRole("History")

End If

MsgBox(db_login.Script)

MsgBox(db_user.Script)

End If

End Sub

|||

Hi,

Now it Works. Thanks for the help.

Code:

Public Sub CreateUser()

‘stablish connection with sqlserver authentication

Dim conn As New ServerConnection("STATION01\SQLEXPRESS", "admin", "a9078564")

Dim myServer As New Server(conn)

‘Verify if the user already exist

If Not myServer.Logins.Contains(TextBox2.Text) Then

‘Create the login

Dim db_login As New Login(myServer, TextBox2.Text)

db_login.LoginType = LoginType.SqlLogin

db_login.Create(TextBox3.Text, LoginCreateOptions.None)

Dim sys_database As Database = myServer.Databases("VideoDB")

‘Create the user

Dim db_user As New User(sys_database, TextBox2.Text)

db_user.Login = TextBox2.Text

db_user.UserType = UserType.SqlLogin

db_user.Create()

If CheckBox18.Checked Then

‘Grant roles

db_login.AddToRole("securityadmin")

db_user.AddToRole("db_owner")

Else

‘Grant privileges

Dim SelectPrivilege As New ObjectPermissionSet

SelectPrivilege.Select = True

Dim InsertPrivilege As New ObjectPermissionSet

InsertPrivilege.Insert = True

Dim UpdatePrivilege As New ObjectPermissionSet

UpdatePrivilege.Update = True

Dim DeletePrivilege As New ObjectPermissionSet

DeletePrivilege.Delete = True

Dim db_table As New Table(sys_database, "customers", "admin")

If CheckBox1.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox2.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox3.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If CheckBox4.Checked Then db_table.Grant(DeletePrivilege, TextBox2.Text)

db_table = New Table(sys_database, "capturepicture", "admin")

If CheckBox1.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

db_table = New Table(sys_database, "items", "admin")

If CheckBox5.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox6.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox7.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If CheckBox8.Checked Then db_table.Grant(DeletePrivilege, TextBox2.Text)

If CheckBox9.Checked Then

db_login.AddToRole("securityadmin")

db_user.AddToRole("db_securityadmin")

db_user.AddToRole("db_accessadmin")

db_user.AddToRole("CreateUser")

End If

db_table = New Table(sys_database, "properties", "admin")

If CheckBox10.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox11.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox12.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If RadioButton1.Checked Then

db_user.AddToRole("TransactionsFullAccess")

Else

db_user.AddToRole("TransactionsRestrictedAccess")

End If

If CheckBox15.Checked Then db_user.AddToRole("Promotions")

If CheckBox18.Checked Then db_user.AddToRole("History")

End If

End If

End Sub

create login alias DBO

Hi:

When I set up databases for my users, I create them a user and alias it as the DBO. They can then use this user in their code.

The problem is that if a user is aliased as a DBO, they can change the size of their database, create jobs, backup their database, set up replication (from their own sql server), etc...

Is there a different way to create their login (with minimal administrative effort) to give them access to create objects in their database, without allowing them access to these other tasks?

Thanksyou can use the database role'db_ddladmin'. This role is allowed to create, modify and drop all database objects, but cannot issue security-related commands (grant, ...).|||Originally posted by jora
you can use the database role'db_ddladmin'. This role is allowed to create, modify and drop all database objects, but cannot issue security-related commands (grant, ...).

And all objects will belong to this user: like tom.newtable. It needs to be careful - there is possibility to have couple tables with the same name but with different owners.

BOL:For example, if user Andrew is a member of the sysadmin fixed server role and creates a table T1, T1 belongs to dbo and is qualified as dbo.T1, not as Andrew.T1. Conversely, if Andrew is not a member of the sysadmin fixed server role but is a member only of the db_owner fixed database role and creates a table T1, T1 belongs to Andrew and is qualified as Andrew.T1. The table belongs to Andrew because he did not qualify the table as dbo.T1.|||I agree about the owner of the objects. As I understood the mail though (and I could be wrong offcourse) tommy only used the dbo user so the login was able to create objects. Thefore I see no problem, the user can still use his login in his/her applications.|||Thanks to all of you for your input. I love this forum, everyone is so helpful!

Ok, moving forward, may be able to use that type of login. However, I can see where it would mess up object owners.

For now, I will start by denying users to certain functions I want to lock out. For example, DENY BACKUP loginname.

Does anyone know how to deny a user from creating a job? I can not find it in BOL.|||I just remove the guest user from msdb, myself. So long as no one needs to save DTS packages there, it should work out ok.|||MCrowley, you rock! Great idea, I actually have denied access to create DTS packages on the server. One question, however, if I remove the quest user from the MSDB, will users still be able to DTS from their OWN machine? For example, they schedule a package on their own machine that pulls down updates, or whatever. Also exporting tables from their own database?

I'm going to test now.

Thanks!|||I removed the Guest login from the MSDB database, and the user (aliased as DBO of their own database), and still change the size of their database, log, and can still backup their database. It throws errors, that it can't write to the MSDB database, and the user is not a "user" in the MSDB, but it still works. It does, however, disallow them from creating jobs on the server!

Any other ideas on how to deny users changing the size of their database and backing it up? I tried:
USE DBNAME
GO
DENY BACKUP DATABASE TO LoginName
But since the login is aliased as a DBO, I get the error:
There is no such user or group LoginName.|||I just ran a test. You can take the users out of the db_owner role and put them in the db_ddladmin and db_securityadmin roles. You can even create your own special "db_owner" role that includes these for ease of administration. This will grant them the ability to create tables as dbo, but as Snail pointed out they will more than likely create tables in their own schemas only. The developers are going to have to be trained to specify the owner while creating objects. As this is possible when you add the user to the db_owner role, anyway, there should be only a few problems to start that gradually go away.
Hope this helps you in your problem.|||MCrowley, thanks for the info, I will experiement with that moving forward. I do have a bunch of databases that are already in place, and I can't make a drastic change like that right now.

Remember that these users are aliased as the DBO, they are not the DBO, sa is the DBO. I need to figure out how to stop users from backing up, restoring, and changing the size of their db.

Thanks|||In the MSDB I denied the public role access to the sp_add_job, and this made it so they can not add jobs to the server! Great!

Now, I just have to figure out how to prevent them from backing up and changing the size (or recovery model) or their database.|||Preventing them from changing the recovery model should be easy enough.
revoke exec on sp_dboption from public

This may have some uninteded consequences, as Enterprise Manager most likely uses this procedure to display information as well as change it. If your users are using EM for all their DBAdmin needs, then they will be getting all sorts of errors when the look for database properties. This may not be a problem for you,a s you are trying to restrict this stuff, anyway.

In order to prevent the backing up of databases/transaction logs, you are going to have to take these guys out of db_owner. I do not know of any other way of restricting that functionality (which, naturally does not mean a way doesn't exist...).

Create Linked Report method

Does anyone have a sample of a CreateLinkedReport method they are using? I am using Reporting Services 2005 and Visual Basic 2005. I need the VB code and how I need to call it. I'd like to change one parameter, and then send it to select recipients using a specified schedule.

Thanks, Iris

Is there anything wrong with the BOL sample code?|||

I'll try that example.

Also, when I try to click Create Linked Report from a master report that I created, I get an internal error. In SS SP2, it shows that this problem was fixed. When I installed SP2 today, it did not fix the problem. I am using a Stored Procedure with one parameter of int value.

Any ideas?

Thanks, Iris

Create file if does not exist but use if it does

I have the following SQL code. How can I put a check in here to see
if the file already exists and create it if not but insert the data
into it if it does? Thanks for your help
--[ Declare Variables ]--
DECLARE @.ThisWeek as smallDatetime
DECLARE @.ThisQtr as Integer
DECLARE @.ThisYear as Integer
DECLARE @.ThisYrQtr as Varchar(4)
DECLARE @.CmdStr as Varchar(1000)
--[ Populate the Variable with Month/Quarter/Year info ]--
Select @.ThisWeek = Mondate, @.ThisQtr = Qtr, @.ThisYear = [Year],
@.ThisYrQtr = Cast( (SubString( Cast([Year] as Char(4)), 3, 2) +
'Q' + Cast([Qtr] as Char(1)) ) AS varChar(4))
>From dbHistory.dbo.tbQtrNdx
Where Mondate = fn_Mondate(GetDate())
--[ Build the SQL script to execute ZZZ123 archive ]--
Select @.CmdStr = Select * Into dbHistory.dbo.tbZZZ123_' + @.ThisYrQtr +
' ' +
'From dbHostdata.dbo.tbZZZ123 ' +
'Where Mondate = ''' + master.dbo.fn_formatDate(@.ThisWeek, 'mm/dd/
yy') + ''''
Exec (@.CmdStr)
--[ Build the SQL script to execute Bills archive ]--
Select @.CmdStr = 'Select * Into dbHistory.dbo.tbBills_' + @.ThisYrQtr +
' ' +
'From dbMetrics.dbo.tbBills ' +
'Where Mondate = ''' + master.dbo.fn_formatDate(@.ThisWeek, 'mm/dd/
yy') + ''''
Exec (@.CmdStr)Why not use
if EXISTS (SELECT ...)
BEGIN
-- Update record
END
ELSE
BEGIN
-- create new record
END
-- End Else
taxidermist@.cableone.net wrote:
> I have the following SQL code. How can I put a check in here to see
> if the file already exists and create it if not but insert the data
> into it if it does? Thanks for your help
> --[ Declare Variables ]--
> DECLARE @.ThisWeek as smallDatetime
> DECLARE @.ThisQtr as Integer
> DECLARE @.ThisYear as Integer
> DECLARE @.ThisYrQtr as Varchar(4)
> DECLARE @.CmdStr as Varchar(1000)
>
> --[ Populate the Variable with Month/Quarter/Year info ]--
> Select @.ThisWeek = Mondate, @.ThisQtr = Qtr, @.ThisYear = [Year],
> @.ThisYrQtr = Cast( (SubString( Cast([Year] as Char(4)), 3, 2) +
> 'Q' + Cast([Qtr] as Char(1)) ) AS varChar(4))
>>From dbHistory.dbo.tbQtrNdx
> Where Mondate = fn_Mondate(GetDate())
>
> --[ Build the SQL script to execute ZZZ123 archive ]--
> Select @.CmdStr = Select * Into dbHistory.dbo.tbZZZ123_' + @.ThisYrQtr +
> ' ' +
> 'From dbHostdata.dbo.tbZZZ123 ' +
> 'Where Mondate = ''' + master.dbo.fn_formatDate(@.ThisWeek, 'mm/dd/
> yy') + ''''
> Exec (@.CmdStr)
>
> --[ Build the SQL script to execute Bills archive ]--
> Select @.CmdStr = 'Select * Into dbHistory.dbo.tbBills_' + @.ThisYrQtr +
> ' ' +
> 'From dbMetrics.dbo.tbBills ' +
> 'Where Mondate = ''' + master.dbo.fn_formatDate(@.ThisWeek, 'mm/dd/
> yy') + ''''
> Exec (@.CmdStr)
>

Create Excel Output

I successfully use the code below to output a report to a web page or to a
PDF file (which opens in Acrobat Reader) but when I try to create an Excel
file, it looks like the result is the binary for an Excel file being
displayed in a HTML page. Where can I get informarion on how to modify the
render method settings to create an Excel file and open it in Excel? I've
tried Googling variations of "render" but am not finding what I need.
Wayne
============================ Dim strRenderType As String = Session("RenderType")
Dim rs1 As New myAccount.rs.ReportingService
rs1.Credentials = New System.Net.NetworkCredential("myRSServer", "myPW", "")
Dim results As Byte(), image As Byte()
Dim streamids As String(), streamid As String
' Render the report to HTML4.0
results = rs1.Render(Session("ReportPath"), strRenderType, _
Nothing,
"<DeviceInfo><StreamRoot>/WebApplication1/</StreamRoot></DeviceInfo>",
Nothing, _
Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, streamids)
Response.BinaryWrite(results)You have to set the content-disposition header for your response to attachment.
Thanks
Tudor
"Wayne Wengert" wrote:
> I successfully use the code below to output a report to a web page or to a
> PDF file (which opens in Acrobat Reader) but when I try to create an Excel
> file, it looks like the result is the binary for an Excel file being
> displayed in a HTML page. Where can I get informarion on how to modify the
> render method settings to create an Excel file and open it in Excel? I've
> tried Googling variations of "render" but am not finding what I need.
> Wayne
> ============================> Dim strRenderType As String = Session("RenderType")
> Dim rs1 As New myAccount.rs.ReportingService
> rs1.Credentials = New System.Net.NetworkCredential("myRSServer", "myPW", "")
> Dim results As Byte(), image As Byte()
> Dim streamids As String(), streamid As String
> ' Render the report to HTML4.0
> results = rs1.Render(Session("ReportPath"), strRenderType, _
> Nothing,
> "<DeviceInfo><StreamRoot>/WebApplication1/</StreamRoot></DeviceInfo>",
> Nothing, _
> Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, streamids)
> Response.BinaryWrite(results)
>
>|||Tudor;
Thanks for the response but I am not familiar with the content-disposition
header. Where/how do I set that?
Wayne
"Tudor Trufinescu (MSFT)" <TudorTrufinescuMSFT@.discussions.microsoft.com>
wrote in message news:6606291C-832F-43A5-9D2F-1137DD2CEEEA@.microsoft.com...
> You have to set the content-disposition header for your response to
> attachment.
> Thanks
> Tudor
> "Wayne Wengert" wrote:
>> I successfully use the code below to output a report to a web page or to
>> a
>> PDF file (which opens in Acrobat Reader) but when I try to create an
>> Excel
>> file, it looks like the result is the binary for an Excel file being
>> displayed in a HTML page. Where can I get informarion on how to modify
>> the
>> render method settings to create an Excel file and open it in Excel? I've
>> tried Googling variations of "render" but am not finding what I need.
>> Wayne
>> ============================>> Dim strRenderType As String = Session("RenderType")
>> Dim rs1 As New myAccount.rs.ReportingService
>> rs1.Credentials = New System.Net.NetworkCredential("myRSServer", "myPW",
>> "")
>> Dim results As Byte(), image As Byte()
>> Dim streamids As String(), streamid As String
>> ' Render the report to HTML4.0
>> results = rs1.Render(Session("ReportPath"), strRenderType, _
>> Nothing,
>> "<DeviceInfo><StreamRoot>/WebApplication1/</StreamRoot></DeviceInfo>",
>> Nothing, _
>> Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, streamids)
>> Response.BinaryWrite(results)
>>|||Tudor;
I did some Googling and found information about that header. Thanks again
for the pointer.
Wayne
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:O$%23BJMS4FHA.3460@.TK2MSFTNGP12.phx.gbl...
> Tudor;
> Thanks for the response but I am not familiar with the content-disposition
> header. Where/how do I set that?
> Wayne
> "Tudor Trufinescu (MSFT)" <TudorTrufinescuMSFT@.discussions.microsoft.com>
> wrote in message
> news:6606291C-832F-43A5-9D2F-1137DD2CEEEA@.microsoft.com...
>> You have to set the content-disposition header for your response to
>> attachment.
>> Thanks
>> Tudor
>> "Wayne Wengert" wrote:
>> I successfully use the code below to output a report to a web page or to
>> a
>> PDF file (which opens in Acrobat Reader) but when I try to create an
>> Excel
>> file, it looks like the result is the binary for an Excel file being
>> displayed in a HTML page. Where can I get informarion on how to modify
>> the
>> render method settings to create an Excel file and open it in Excel?
>> I've
>> tried Googling variations of "render" but am not finding what I need.
>> Wayne
>> ============================>> Dim strRenderType As String = Session("RenderType")
>> Dim rs1 As New myAccount.rs.ReportingService
>> rs1.Credentials = New System.Net.NetworkCredential("myRSServer", "myPW",
>> "")
>> Dim results As Byte(), image As Byte()
>> Dim streamids As String(), streamid As String
>> ' Render the report to HTML4.0
>> results = rs1.Render(Session("ReportPath"), strRenderType, _
>> Nothing,
>> "<DeviceInfo><StreamRoot>/WebApplication1/</StreamRoot></DeviceInfo>",
>> Nothing, _
>> Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, streamids)
>> Response.BinaryWrite(results)
>>
>