Creating a table in Access from an SSIS package

I need to run a make-table query against an Access database out of an SSIS package. I tried to do this with an OLE DB Command Task but it fails to create the table even though the task execution comes back successful. Any thoughts?

Did you tried with a script task ? Use the excel connection from the Connection Manager to connect to your Access DB and execute your create table query from a OleDbCommand object.

I've not tried that method, it just a thought.

sql

Creating a Stored Procedure

Is it possible to create a stored procedure that execute a delete command from a table whose name is specified as a parameter of the stored procedure?

Thank you

Yes. You would need to use dynamic SQL for that.

CREATE PROC....@.tblvarchar(25)AS...Declare @.sqlvarchar(100)--Build your SQLSET @.sql ='DELETE FROM ' + @.tbl +' WHERE <condition>'--Execute your SQLEXEC(@.sql)-- You can also use sp_ExecuteSQL. Check out BOL for more info.

|||

A problem with dynamic SQL though, is that by default the caller has to have delete permissions on the table. Will all callers have that permission? That could be a significant vulnerability in your application and database server.

What version of SQL Server are you using? If it's 2005, you could use the EXECUTE AS option in the stored procedure to run with another principal's permissions. That could go a long way towards making this more secure.

Don

|||

Thank you for the info.

A valuable one.

Creating a SQL MAX command

Hello All,

I'm currently in the process of developing a new PO system for work. What I need to accomplish is a SQL MAX command to find the largest PO number and then add 1 to that number. I'm then saving this in a session state so users can create multiple items for that same PO number. Here's what I have so far:

1protected void Page_Load(object sender, EventArgs e)2 {3// connection string to your database4 SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=Purchasing;Integrated Security=SSPI;");56// create command object to execute query7 SqlCommand mySqlCommand = mySqlConnection.CreateCommand();89// set command text to the INSERT sql query10 mySqlCommand.CommandText ="SELECT MAX(PONumber)FROM ItemMaster;";1112// open connection13 mySqlConnection.Open();1415// execute query1617int newPO1 = (int) mySqlCommand.ExecuteScalar();18int newPO = newPO1 + 1;1920// close connection21 mySqlConnection.Close();2223//Response.Write(newPO);24 Session["newPO"] = newPO.ToString();2526 }

I copied and modified the ExecuteScalar() command from another thread in another fourm, but continue to receive this error:

System.InvalidCastException: Specified cast is not valid.

Source Error:

Line 30: // execute queryLine 31:Line 32: int newPO1 = (int) mySqlCommand.ExecuteScalar();Line 33: int newPO = newPO1 + 1;Line 34:

I'm not sure what i'm doing wrong, any help to point me in the right direction would be greatly appreciated.

Thank you in advance

Instead of using (int), try Convert.ToInt32

Convert.ToInt32(mySqlCommand.ExecuteScalar());

|||

That did the trick, only thing is my logic must be incorrect somewhere. For each line item I add its adding another digit to the PO number. What I need it to do is create a new PO number and stick with that one throughout the session...but then when the user finishes the PO and wants to start a new one thats when it adds 1 to the max PO number. Any Ideas? I thought putting the code in the Protected void Page_Load would do the trick but apparently not.

|||

Hi GIZM,

I have some code here..

If u find this is useful mark it as answer

qlConnection con=new SqlConnection();
con.ConnectionString=System.Configuration.ConfigurationManager.AppSettings["strcon"];
con.Open();
SqlCommand cmd = new SqlCommand("select max(fdcatid) from maincate", con);

//SqlDataAdapter ad = new SqlDataAdapter(cmd);
int f0 = (int)cmd.ExecuteScalar();
int f1 = f0 + 1;
con.Close();
Session["f1"] = f1.ToString() ;
Response.Write(f0);

|||

Hey Raja,

That looks to be a lot like what I have in my code, I just cant figure out where to put it. As of right now, I have it on page load, and then later in the page I have a insert statement to add lines to the data table. Every time a user would update a line item I guess the page is reloading causing the PONumber to increase. I only want the number to increase after the page is redirected to my confirmation page and then back, or if the browser is closed and reopened.

|||

I wonder if I could pull this off using the "is post back" thing?

Creating a new database

I created a database using the following command :

create database krish on (Name='krish', filename='C:\Program Files\Microsoft SQL Server\MSSQL\data\krish.mdf', size=25, maxsize=50,filegrowth=5%)

This actually created the database. Now, I wanted to view this database info being stored inside the SQL Server system tables. I looked at "sysdatabases" table and found an entry as expected for "krish" but I could not trace where the info corresponding to the size of the database was stored ie.25MB . (I looked ad "sysdevices" but couldn't find any entry for the newly created database).

In which table is it stored ?

Any help is appreciated.select size
from krish.dbo.sysfiles|||Thanks Hans. That worked

Creating a Maintenance Plan via the Command Line

Hello All,

I've searched high and low for documentation on this to no avail.

Basically my goal is to create a maintenance plan in SQLSERVER2005 via the command line. I need to create this plan in a way that it can be seen in the list of Maintenance Plans in the Management Studio Interface. I went into the SSIS designer and created my plan. I now have a DTSX file. I tried the dtutil.exe utility, however i never saw my maintenance plan in the list of plans.

I ran dtutil.exe and did /FILE to /SQL but i don't see the plan listed or in a way a user could modify it, which is of the utmost importance to my clients.

How do i get my file to turn into a real running Maintenance Plan that is seen in the list of Maintenance Plans via the Management Studio Interface and is editable by clients?

Other things to keep in mind, i'm attempting to create these via Installshield MSI installer. So i need to do it via command line, or file system-wise. No interface or user interaction.

Please Adivse.

Hi Brendan,

You can use the stored procedures sp_add_maintenance_plan, sp_add_maintenance_plan_db and sp_add_maintenance_plan_job to create maintenance plans, add the databases to it and associate the jobs with the same. Refer documentation on these from Books Online.

Thanks,
Kuntal

|||You have to take help of SQLCMD in this case where using the statements referred above as a Stored procedure, this will do when you want to deploy as the package.|||These three SP's i understand. But how do i reference the file? Or how do i give the plan the 'meat' of what it needs to do? From what i can tell i went to the designer created my dtsx file, and that code in that file need to be input somewhere... can that be done through a sproc as well?|||Anyone?|||

BOL states:

The sqlmaint utility performs a specified set of maintenance operations on one or more databases. Use sqlmaint to run DBCC checks, back up a database and its transaction log, update statistics, and rebuild indexes. All database maintenance activities generate a report that can be sent to a designated text file, HTML file, or e-mail account. sqlmaint executes database maintenance plans created with previous versions of SQL Server. To run SQL Server 2005 maintenance plans from the command prompt, use the dtexec utility utility.

|||

Yeah i wish i could run it like that, but i need it to run in the exact fashion as if it were created in the GUI. Maybe i should explain this better...

I need a programatic way, either through sprocs or command line, to create a maintenance plan, that will show up in the GUI, under maintenance plans, with my name, and have all the steps i specify in the design screen.

Above i see that i run these;

sp_add_maintenance_plan : adds my plan. ok great.

sp_add_maintenance_plan_db : adds a databse that i want to run the plan against, ok great.

sp_add_maintenance_plan_job : ok, adds a job to run my plan. great.

But where does the 'meat' , the steps i designed in the SSIS designer go? all i want to do is add a new maintenance plan that shows in the GUI and has all my steps in the plan. No the steps in the job, the steps in the plan.

I'm sorry, for some reason i cannot wrap my head around this. Thanks for your patience. But i'm not a SQLSERVER admin, i'm an install guy. Any further help would be greatly appreciated.

-b

|||

You can create the SSIS package to perform this maintenace task and you can deploy that SSIS package to multiple servers, http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx#ERGAE fyi.

Drop me an email using my site (contact us) below and I can talk you through the steps.

|||Emailed...|||Anybody have a solution to this? I'm sure it's quite simple, there is just no direct example or i'm not doing this correctly. I'm getting rather desperate here... any help would certainly be appreciated.|||

Brendan

Sorry I didn't get any email from you, could you please send it to smaster@.sqloogle.co.uk.

Brendan Stewart wrote:

Emailed...

Creating a Maintenance Plan via the Command Line

Hello All,

I've searched high and low for documentation on this to no avail.

Basically my goal is to create a maintenance plan in SQLSERVER2005 via the command line. I need to create this plan in a way that it can be seen in the list of Maintenance Plans in the Management Studio Interface. I went into the SSIS designer and created my plan. I now have a DTSX file. I tried the dtutil.exe utility, however i never saw my maintenance plan in the list of plans.

I ran dtutil.exe and did /FILE to /SQL but i don't see the plan listed or in a way a user could modify it, which is of the utmost importance to my clients.

How do i get my file to turn into a real running Maintenance Plan that is seen in the list of Maintenance Plans via the Management Studio Interface and is editable by clients?

Other things to keep in mind, i'm attempting to create these via Installshield MSI installer. So i need to do it via command line, or file system-wise. No interface or user interaction.

Please Adivse.

Hi Brendan,

You can use the stored procedures sp_add_maintenance_plan, sp_add_maintenance_plan_db and sp_add_maintenance_plan_job to create maintenance plans, add the databases to it and associate the jobs with the same. Refer documentation on these from Books Online.

Thanks,
Kuntal

|||You have to take help of SQLCMD in this case where using the statements referred above as a Stored procedure, this will do when you want to deploy as the package.|||These three SP's i understand. But how do i reference the file? Or how do i give the plan the 'meat' of what it needs to do? From what i can tell i went to the designer created my dtsx file, and that code in that file need to be input somewhere... can that be done through a sproc as well?|||Anyone?|||

BOL states:

The sqlmaint utility performs a specified set of maintenance operations on one or more databases. Use sqlmaint to run DBCC checks, back up a database and its transaction log, update statistics, and rebuild indexes. All database maintenance activities generate a report that can be sent to a designated text file, HTML file, or e-mail account. sqlmaint executes database maintenance plans created with previous versions of SQL Server. To run SQL Server 2005 maintenance plans from the command prompt, use the dtexec utility utility.

|||

Yeah i wish i could run it like that, but i need it to run in the exact fashion as if it were created in the GUI. Maybe i should explain this better...

I need a programatic way, either through sprocs or command line, to create a maintenance plan, that will show up in the GUI, under maintenance plans, with my name, and have all the steps i specify in the design screen.

Above i see that i run these;

sp_add_maintenance_plan : adds my plan. ok great.

sp_add_maintenance_plan_db : adds a databse that i want to run the plan against, ok great.

sp_add_maintenance_plan_job : ok, adds a job to run my plan. great.

But where does the 'meat' , the steps i designed in the SSIS designer go? all i want to do is add a new maintenance plan that shows in the GUI and has all my steps in the plan. No the steps in the job, the steps in the plan.

I'm sorry, for some reason i cannot wrap my head around this. Thanks for your patience. But i'm not a SQLSERVER admin, i'm an install guy. Any further help would be greatly appreciated.

-b

|||

You can create the SSIS package to perform this maintenace task and you can deploy that SSIS package to multiple servers, http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx#ERGAE fyi.

Drop me an email using my site (contact us) below and I can talk you through the steps.

|||Emailed...|||Anybody have a solution to this? I'm sure it's quite simple, there is just no direct example or i'm not doing this correctly. I'm getting rather desperate here... any help would certainly be appreciated.|||

Brendan

Sorry I didn't get any email from you, could you please send it to smaster@.sqloogle.co.uk.

Brendan Stewart wrote:

Emailed...

Creating a directory command question

Hi All,

What is the SQL command for creating
a direcotry c:\mydata\data1 on my server.

Thanks in advance
John S

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"John S" <nospam> wrote in message news:41edfe77$1_1@.127.0.0.1...
> Hi All,
> What is the SQL command for creating
> a direcotry c:\mydata\data1 on my server.
> Thanks in advance
> John S
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

See xp_cmdshell in Books Online. There are security issues with using it, so
it's not a good idea to let end users execute it, but for admin/DBA tasks
it's fine.

Simon

creating a database

Hi;
If I have a schema for creating a database, is there a command line
application I can run and pass it the filename of the schema and it will then
create that database? If so, can someone point me to a url showing the
command line syntax for it?
And, what registry entry can I use to determine where this program is - as
some people do not install in C:\Program Files\...
thanks - dave
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
Maybe this helps. http://support.microsoft.com/kb/325003
Use the variable %programfiles%
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"David Thielen" wrote:
| Hi;
|
| If I have a schema for creating a database, is there a command line
| application I can run and pass it the filename of the schema and it will
then
| create that database? If so, can someone point me to a url showing the
| command line syntax for it?
|
| And, what registry entry can I use to determine where this program is - as
| some people do not install in C:\Program Files\...
|
| thanks - dave
|
| --
| thanks - dave
| david_at_windward_dot_net
| http://www.windwardreports.com
|
| Cubicle Wars - http://www.windwardreports.com/film.htm
|
|
|||osql is the tool I need.
I am retecient to use %programfiles% because we used to put Sql Server on d:
while programfiles was on c: - so on those systems at least, that would not
find it.
Is there anything Sql Server writes to the registry with it's location? I
think there was and I used it in a program where I used to work - but I don't
have access to that code.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
"Dave Patrick" wrote:

> Maybe this helps. http://support.microsoft.com/kb/325003
> Use the variable %programfiles%
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "David Thielen" wrote:
> | Hi;
> |
> | If I have a schema for creating a database, is there a command line
> | application I can run and pass it the filename of the schema and it will
> then
> | create that database? If so, can someone point me to a url showing the
> | command line syntax for it?
> |
> | And, what registry entry can I use to determine where this program is - as
> | some people do not install in C:\Program Files\...
> |
> | thanks - dave
> |
> | --
> | thanks - dave
> | david_at_windward_dot_net
> | http://www.windwardreports.com
> |
> | Cubicle Wars - http://www.windwardreports.com/film.htm
> |
> |
>
>
|||You could look at 'Path' and or 'SQLPath' found below but you really
shouldn't need the fully qualified path;
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup
since the installation of client tools appends to the system path variable.
C:\Program Files\Microsoft SQL Server\90\Tools\binn\;C:\Program
Files\Microsoft SQL Server\80\Tools\Binn\

Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"David Thielen" wrote:
| osql is the tool I need.
|
| I am retecient to use %programfiles% because we used to put Sql Server on
d:
| while programfiles was on c: - so on those systems at least, that would
not
| find it.
|
| Is there anything Sql Server writes to the registry with it's location? I
| think there was and I used it in a program where I used to work - but I
don't
| have access to that code.
|
| --
| thanks - dave
| david_at_windward_dot_net
| http://www.windwardreports.com
|
| Cubicle Wars - http://www.windwardreports.com/film.htm
|||thank you - that looks perfect.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
"Dave Patrick" wrote:

> You could look at 'Path' and or 'SQLPath' found below but you really
> shouldn't need the fully qualified path;
> HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup
> HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup
> since the installation of client tools appends to the system path variable.
> C:\Program Files\Microsoft SQL Server\90\Tools\binn\;C:\Program
> Files\Microsoft SQL Server\80\Tools\Binn\
>
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "David Thielen" wrote:
> | osql is the tool I need.
> |
> | I am retecient to use %programfiles% because we used to put Sql Server on
> d:
> | while programfiles was on c: - so on those systems at least, that would
> not
> | find it.
> |
> | Is there anything Sql Server writes to the registry with it's location? I
> | think there was and I used it in a program where I used to work - but I
> don't
> | have access to that code.
> |
> | --
> | thanks - dave
> | david_at_windward_dot_net
> | http://www.windwardreports.com
> |
> | Cubicle Wars - http://www.windwardreports.com/film.htm
>
>
|||You can use sp_helpfile to return the file locations.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:FC83AF0A-B244-4B64-A83F-36A21A6ADECA@.microsoft.com...[vbcol=seagreen]
> osql is the tool I need.
> I am retecient to use %programfiles% because we used to put Sql Server on
> d:
> while programfiles was on c: - so on those systems at least, that would
> not
> find it.
> Is there anything Sql Server writes to the registry with it's location? I
> think there was and I used it in a program where I used to work - but I
> don't
> have access to that code.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>
>
> "Dave Patrick" wrote:
|||You're welcome.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"David Thielen" wrote:
| thank you - that looks perfect.
|
| --
| thanks - dave
| david_at_windward_dot_net
| http://www.windwardreports.com
|
| Cubicle Wars - http://www.windwardreports.com/film.htm