Creating SQL Server Stored Procedures in Deployment

Hey guys,

I'm having a problem. I've been given a task to complete. I was given a database, and asked to wrap a website around it with certain functionality. I did this, and added seven stored procedures in the proccess. Everything works, Business Logic Layer, Data Access Layer, error validation, even screwed around with SQL-injection protection. Lovely, yes?

However, when my little website gets tested, it's going to be plugged into a fresh database - the exact same database I was given, only with different data in the tables. My stored procedures won't be in that database. I can detect if my stored procedure doesn't exist easily enough by catching the error at runtime and checking the code.

I would like to create the stored procedure inside that catch block. I just don't know how.

The easy answer is just to use embedded SQL in my application instead of stored procedures. This isn't a commercial application, it's just a task I've been given to test my abilities. But embedded SQL is icky. I'd rather do it properly.

With the help of the executenonquery u may create the stored procedures which is not the best way. stored procedures,functions or triggers creation means generally people will implement in database not through front end. Not only in this case u can perform ddl commands through executenonquery but max no one will prefer.

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

Creating DB using command line

Hi All,

I was given a task of coming up with the script to recreate an existing database using a command line. I would use this script in case when the server is down and I can't get to Query Analyzer or EM to recreate it. I am not sure where to start. Any ideas are greatly appreciated.

Thanks.If the server is down, how would you be able to create a database? Other than that, look up the OSQL utility.|||I'm assuming they have another server with sql server installed...

And you should have sql server client tools installed on your desktop so you can connect to any server...no need to log on to a particular box

And how do you want o create a "new" database?

I'm assuming from a backup...is the backup copied to another box?

If not, then when the server is down, you are sol|||It is a clustered SQL Server, so dev will stand for prod. Yes, the backup is being copied to a drive on a dev box.|||OK, so create a *.sql file that does the restore and either create a bat file, or type this in the command line

osql /U <login> /P <password>/i <filename>.sql

simple enough|||Yes it is. Thanks a lot.

Creating Custom Sequence Container

I have a package that is going to have roughly 20 sequence containers in it. Each of these containers is going to have the same start task and the same end task. The data flow task(s) in each sequence container will differ. So I was thinking I would create a custom sequence container that would implement the common steps I need in each of my containers. I started to create a task that would inherit from Sequence, but I found that it is sealed. Bummer.

What is involved in creating a sequence task by inheriting from Task? I see that Sequence implements IDtsSequence. But looking at this interface, it seems like I would have to build the logic to execute the workflow contained in my sequence.

Any pointers?

-Darrell

Darrell,

What you're talking about here is task reuse right? So implementing a new type of container won't help you and you cannot do it anyway as far as I am aware.

The only unit of reuse in SSIS currently is a package so you will need to hive off that repeatable functionality into another package and call it using the Execute Package Task.

There are huge improvements to be made in the area of reusability and reuse of tasks rather packages is my numero uno for vNext. Read more here: http://blogs.conchango.com/jamiethomson/archive/2005/02/05/929.aspx

-Jamie

|||

I don't think I explained my issue well enough if you think that a new type of container won't help. I want to create a container that when it "starts" will update a status in a table and when it "ends" it will update the status again. I planned to use a custom property in my custom container object so that each instance of my container can differentiate itself in the status table. What my container "contains" would vary with each instance in the package. Hence, the control needs to be a container.

Bummer I can't make my own.

-Darrell

|||

Darrell Davis wrote:

I don't think I explained my issue well enough if you think that a new type of container won't help. I want to create a container that when it "starts" will update a status in a table and when it "ends" it will update the status again. I planned to use a custom property in my custom container object so that each instance of my container can differentiate itself in the status table. What my container "contains" would vary with each instance in the package. Hence, the control needs to be a container.

Bummer I can't make my own.

-Darrell

Containers in the context that we are talking about them do not and should not affect external data. That is not what they are for. They are to affect control-flow, nothing else.

Updating tables can be done very easily using the Execute SQL Task. Use the right tool for the job is I guess what I'm saying here.

If you want to achieve the thing that you are looking to achieve with what you refer to as a custom property, all you need to do is scope a variable to the Sequence container that holds that container's "ID". Each of your 20 sequence containers has an identically named variable scoped to it with a different value.

Hope that helps.

-Jamie

|||To be clear, sequence containers are not extensible, you cannot write your own. You could perhaps write the the start and end processes as task, that require minimal setup. This would make for faster development, and also encapsulat ethe logic in a reusable unit and also make them easy to maintain, that is just change the task code and redeploy, not maintain n packages or n instances of the task in a package.|||

Thanks, that's what I ended up doing. I created a new Task which has my start code and my end code. It has a property which indicates which MainPipe to execute in what would be the body of the sequence. It loads the TaskHost that contains the MainPipe at runtime and passes in the variables that are neccessary. It is not as elegant as it would be if I could subclass Sequence, but it seems to work pretty well for what I need. It reduces the number of tasks in my package from around 180 to 30. Also, as you poitned out, it allows me to modify my start and end code one place in my custom task and then just redeploy.

Thanks,
-Darrell