restrict access to database: run

Showing posts with label run. Show all posts

Friday, March 30, 2012

How to script adding a field to a table

Hi - I would like to know what sql to run in Query Analyzer to add two
fields to an existing table (I know you can do this in Enterprise
Manager - but I'd like to be able to send a script to someone to let it
happen automatically).
Column Name: IDCreated
DataType: DateTime
Length: 8
Allow Nulls: False
Default Value: (getdate())
and
Column Name: ChangeNum
DataType: bigint
Length: 8
Allow Nulls: False
Identity: Yes
Identity Seed: 45
Identity Increment: 1
Thanks for any help,
Mark
*** Sent via Developersdex http://www.examnotes.net ***Do it in EM, and press "Save Change Script" button before exiting the window
s, and you will get the
script served on a silver plate (almost).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mark" <anonymous@.devdex.com> wrote in message news:en82DkCyFHA.3000@.TK2MSFTNGP12.phx.gbl..
.
> Hi - I would like to know what sql to run in Query Analyzer to add two
> fields to an existing table (I know you can do this in Enterprise
> Manager - but I'd like to be able to send a script to someone to let it
> happen automatically).
> Column Name: IDCreated
> DataType: DateTime
> Length: 8
> Allow Nulls: False
> Default Value: (getdate())
> and
> Column Name: ChangeNum
> DataType: bigint
> Length: 8
> Allow Nulls: False
> Identity: Yes
> Identity Seed: 45
> Identity Increment: 1
> Thanks for any help,
> Mark
> *** Sent via Developersdex http://www.examnotes.net ***|||Here ya go:
create table MyTable
(
PK int primary key
)
go
alter table MyTable
add
IDCreated datetime not null
constraint DF1_Myatble default (getdate())
, ChangeNum bigint not null identity (45, 1)
go
drop table MyTable
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mark" <anonymous@.devdex.com> wrote in message
news:en82DkCyFHA.3000@.TK2MSFTNGP12.phx.gbl...
Hi - I would like to know what sql to run in Query Analyzer to add two
fields to an existing table (I know you can do this in Enterprise
Manager - but I'd like to be able to send a script to someone to let it
happen automatically).
Column Name: IDCreated
DataType: DateTime
Length: 8
Allow Nulls: False
Default Value: (getdate())
and
Column Name: ChangeNum
DataType: bigint
Length: 8
Allow Nulls: False
Identity: Yes
Identity Seed: 45
Identity Increment: 1
Thanks for any help,
Mark
*** Sent via Developersdex http://www.examnotes.net ***|||Thinking about it, go with Tom's suggestion. EM often does these things in a
less than optimal way.
Often you see EM creating a new table, copy data etc etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:%23xDANtCyFHA.1856@.TK2MSFTNGP12.phx.gbl...
> Do it in EM, and press "Save Change Script" button before exiting the wind
ows, and you will get
> the script served on a silver plate (almost).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Mark" <anonymous@.devdex.com> wrote in message news:en82DkCyFHA.3000@.TK2MS
FTNGP12.phx.gbl...
>|||Like this:
ALTER TABLE table_name ADD idcreated DATETIME NOT NULL
CONSTRAINT df_table_name_idcreated
DEFAULT CURRENT_TIMESTAMP ;
ALTER TABLE table_name ADD changenum BIGINT NOT NULL
IDENTITY(45,1) ;
Usually, when you add an IDENTITY column you will want to add a unique
or primary key constraint on that column. Although that's not
mandatory, IDENTITY itself won't prevent duplicates in all
circumstances because the auto-generated value can be overridden or the
seed can be changed. Also, IDENTITY is typically referenced by a
foreign key, for which a constraint is required.
Depending on your requirements you can add a constraint like this:
ALTER TABLE table_name
ADD CONSTRAINT ak_table_name_change_num UNIQUE (changenum) ;
David Portas
SQL Server MVP
--|||> Often you see EM creating a new table, copy data etc etc.
and sometimes it is the optimal way, is it not?

How to scheule job to run last day of the month

What are the settings to get a sql server job to always run the last day of the month? This should be quite easy!

Thanks!

SQL2000?

if so,

on the schedule tab of the job, edit the schedule, or create a new one... on the schedule type, select recurring, then click the change button on the lower right hand side.

In the Occurs section, change to monthly. Then in the monthly section that you will see to the right select the lower radio button and change the first drop down list box to "Last", the second to "Day" of every 1 month.

Set the time, and away you go...

|||Thanks! This would make a nice addition to EM Help!

How to schedule to run Integration Packages using SQL Agent/Job?

Hi Folks,

I deploying a couple of integration packages which needs to be run sequentially. Currently, I quite puzzled how come i cannot run them as a SQL Agent job. It always fail with "Executed as user: Servername\Administrator. The package execution failed. " But if I had put import these integration packages into the SQL Server 2005 Integration Services FileSystem, it does run but i need promptly as there is a dialog requesting whether to execute the packages. Is there a way to automate the running of these packages through SQL agent. ? Pls help need to get it up and running asap. Thanks first.

Yes there is. Most problems experienced when using SQL Agent to run packages are down to the package being run as a different user. Is that what you are doing?

-Jamie

|||Hi Garynkill23,

This could be due to the ProtectionLevel setting for the individual packages - that's my guess.

By default, these are set to EncryptSensitiveWithUserKey. This means as long as you personally execute the packages, your credentials are picked up and the packages execute in your security context. This is true even if you're connected to a remote machine, so long as you're using the same AD credentials you used when you built the packages. Does this make sense?

When the job you created executes, it runs under the SQL Agent Service logon credentials.

My understanding of the "Sensitive" in EncryptSensitiveWithUserKey is most likely flawed, but I cannot find a way to tell my SSIS package "hey, this isn't sensitive so don't encrypt it." Although this sometimes gets in the way I like this feature because it keeps me from doing something I would likely later regret. Anyway, my point is the Sensitive label is applied to connection strings and I cannot find a way to un-apply it (and I'm cool with that).

One of the first things an SSIS package tries to do (after validation) is load up configuration file data. This uses a connection, which (you guessed it) uses your first connection string. Since yours are encrypted with your own personal SID on the domain and this is different from the SID on the account running the SQL Agent Service, the job-executed packages cannot connect to the configuration files to decrypt them.

There are a couple proper long-term solutions but the one that makes the most sense is to use the EncryptSensitiveWithPassword Package ProtectionLevel option and supply a good strong password. You will need to supply the password when you set up the job step as well, and this should allow the package to run without needing your security credentials.

Note: You will also need this password to open the packages in BIDS (or Visual Studio) from now on... there's no free lunch.

Hope this helps,

Andy

|||

Andy Leonard wrote:
Hi Garynkill23,
There are a couple proper long-term solutions but the one that makes the most sense is to use the EncryptSensitiveWithPassword Package ProtectionLevel option and supply a good strong password. You will need to supply the password when you set up the job step as well, and this should allow the package to run without needing your security credentials.

Andy,

Why do you think that makes most sense? To me, it makes more sense to set ProtectionLevel=DontSaveSensitive and then store all your connection strings in configurations. Personally I think this should be considered best practice - its a matter of opinion of course.

-Jamie

|||

Hi Jamie,

I think the method you advocate (DontSaveSensitive) is great, and perhaps best.

My experience with this question drives my copy-and-paste answer about using a password. Here's what I've seen: Someone starts using SSIS and works through some issues as they scale the learning curve (reading good books and a great blog over at Conchango for help as they go).

Then they move it to a test or integration server, experiment with the job step type until they get a job to run when they right-click the job and click Start Job at Step... So they schedule it to run and wait. The SQL Agent scheduler fires the job and it fails with a "cannot acquire connection" (or similar) error. We know why but it seems completely baffling to them. (It certainly did to me when it first happened!)

What's worse is there's little or nothing in the log (the package cannot make that connection either).

While EncryptWithPassword method works, it also requires folks to keep track of a(nother) password.

I start here mainly because it's the quickest way I can think of to get that package up and running. Everyone understands passwords. I don't claim to be right or even advocating a best practice (at least not yet) - I'm just trying to help SSIS developers get that package running.

I welcome any feedback - from anyone. I'm still learning too! :)

Thanks,

Andy

|||

That all sounds fair enough to me Andy. I'll hold my hand up and say that I always try and drive people towards best practice - and to me that means configurations. As I aluded to, its all subjective.

Good discussion.

-Jamie

|||

Jamie Thomson wrote:

I'll hold my hand up and say that I always try and drive people towards best practice - and to me that means configurations.

I cannot argue with you. I advocate 2-pass (minimum) configurations to consulting clients. But I'm also right there to either implement it myself or show them how to implement it.

Jamie Thomson wrote:

Good discussion.

I couldn't agree more!

Thanks,

Andy

|||

As regards to your first message, I am not sure which account i was using Windows Authentication to SQL Server 2005 to run the packages which i kept under the SQL Server 2005 Integration Services - File System section. And I had to run it manually.

Then what should i do so that i can run it under SQL Agent as from what i read it requires a username/password but the packages had been saved under this option "Don't Save any sensitive data". One more question, is there any impact if i save it using this option and put it under maintenance under SQL Server 2005 Integration Services.

|||

garynkill23 wrote:

As regards to your first message, I am not sure which account i was using Windows Authentication to SQL Server 2005 to run the packages which i kept under the SQL Server 2005 Integration Services - File System section. And I had to run it manually.
Then what should i do so that i can run it under SQL Agent as from what i read it requires a username/password but the packages had been saved under this option "Don't Save any sensitive data". One more question, is there any impact if i save it using this option and put it under maintenance under SQL Server 2005 Integration Services.

As discussed above, if you have Protectionlevel=DontSaveSensitive then you will more than likely have to use configurations.

What do you mean by "put it under maintenance"?

-Jamie

|||

Hi garynkill23,

This is complicated. It touches a lot of moving parts in your SSIS package, SQL Servers, and enterprise domain. There's just no simple and easy explanation. But it is this way for good reason and that reason is to provide security.

I wrote a blog entry that talks about connections between SSIS and SQL Server - if you use a SQL Login to connect to SQL Server. This should also apply to storing credentials for any provider that requires a username and password.

Windows Authentication is simpler and safer provided your SQL Servers use one or more domain accounts for the SQL Agent service. (It also offloads SQL Server connectivity account maintenance to the Help Desk - at no extra charge.) If you use a domain account for the SQL Agent service, your SSIS connection managers can all be configured to use Windows Authentication to connect, and you simply grant the SQL Agent service domain account the access it needs in SQL Server. Does this make sense?

For example: I have a domain account named MyDomain\Andy. I log in as MyDomain\Andy and write an SSIS package that connects to a Dev SQL Server. I use the default ProtectionLevel: EncryptSensitiveWithUserKey. MyDomain\Andy has sufficient privileges on the Dev SQL Server. When I'm done, I can manually execute the package and it succeeds in connecting to the Dev SQL Server.

Next, I deploy the SSIS package to a Prod SQL Server. I create a SQL Agent job with an SSIS step that calls the SSIS package. I schedule the job to run at 2:00 AM. Again, MyDomain\Andy has sufficient privileges in the Prod SQL Server. When I right-click the SQL Agent job I just created, I can select "Start Job at Step..." and the job executes, calls the SSIS package, and both succeed.

What just happened? From the security context point-of-view, MyDomain\Andy just executed this job. This is important. The SQL Agent scheduler did not execute the job. In this scenario, that won't happen until 2:00 AM. Although this was a good test, it was incomplete.

To continue the example, let's assume the SQL Agent service on the Prod SQL Server runs under a domain account named MyDomain\SQLAgentService. When the scheduler fires the job at 2:00 AM, MyDomain\SQLAgentService will try to log into the Prod SQL Server and perform the operations specified in your SSIS package. If it lacks permissions to connect, the SSIS package will fail, causing the SQL Agent job to fail. You will get an error message similar to "failed to acquire connection". The error varies because the permissions granted MyDomain\SQLAgentService vary along with the configuration of the SSIS package:

If Logging is enabled in the SSIS package, it will probably attempt to connect to the logging provider before attempting to connect to a SQL Server - unless you're using a SQL Server logging provider. If it cannot connect to the Logging provider, it cannot log the fact that it encountered an error connecting to the Logging provider.

How to schedule report to run

Hello.
On a monthly basis I need to run a report. The report takes three
parameters: A customer ID, a Month, and a Year.
I want to run this report for every customer in our database and save each
report as a PDF on disk. Then I will store a link to the report in the
customer's "file".
Can this be done? How?
Thanks in advance,
MikeLook up Data-Driven Subscriptions in the docs. That'll be your starting
point. The link part is easy. :)
"MikeL" <MichaelLopez@.inds.com> wrote in message
news:OROPI5hOFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Hello.
> On a monthly basis I need to run a report. The report takes three
> parameters: A customer ID, a Month, and a Year.
> I want to run this report for every customer in our database and save each
> report as a PDF on disk. Then I will store a link to the report in the
> customer's "file".
> Can this be done? How?
> Thanks in advance,
> Mike
>

how to schedule job to run vbscript file

Hi All,
I wrote a vbscript file which copies one table records to another database table. I wrote error handling to send an email if error occurs when copying records from one table to another. I want to schedule a sql job and give this file to execute. Could anyone plz tell me how to schedule sql job which runs vbscript file. i selected ActiveXScript and choose vbscript and gave path. I'm not understanding what the command should be given to run vbscript file. The result of giving path is job is failing.
Any help would be appreciated...plzzzzzzz
thanks
carolThe way I've used the ActiveXScript option in the past is to physciallypaste the VBScript code into the Command textbox. AFAIK you can'ttype in a path.

how to schedule DTS package in sql 2005

Hello,

I am trying to to schedule DTS Package but this message appear .

Message
The job failed. The Job was invoked by User sa. The last step to run was step 1 (1). and this

Message
Executed as user: Computer name\SYSTEM. The package execution failed. The step failed

so how I can schedule DTS Package in sql 2005 .

Do you have logging enabled for this package? if enabled check the log file it should have more detailed information on the error.

The error you have posted is a very generic error from the job agent, and it does not give any information.

Thanks

|||

Sounds like you might have some issues with the account settings. See the following thread for tips on setting up proxies / credentials / jobs etc.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1955723&SiteID=1

|||

Thanks, all

But still the same problem even I did all thing to solve but still can't schedule DTS, but when I see th proparites of the

SQL Server Agent-->Connection and found the sql server connection grayed out and can't select or change it . so may be this is the problem but how to run the agent under sa account .

|||

Assuming you are talking about the connection infromation on the left hand side of the job properties screen, that connection information is how you are currently connected to the sql server. You would need to look at the steps tab and click edit on the step associated with the package you are trying to run. At the top of this screen there will be a place for the step name (i.e. run package x), the type (sql server integration services package) and the run as. You would select the correct proxy name from the run as drop down that ties to your sa account...

|||

Thanks very much for you reply but I still can't schedule the DTS even though when I execute the dts it's work fine and I did all thing what said here http://www.codeproject.com/useritems/Schedule__Run__SSIS__DTS.asp

|||

I am trying to schedule working DTS in sql server 2005 sp 2 . but I it failed and got this message . I searched at Internet and found this

http://support.microsoft.com/kb/904796

but I can’t understand how to solve it, any one can help please .

Message

Executed as user: ComputrName\Administrator. ...00.3042.00 for 32-bitCopyright (C) Microsoft Corp 1984-2005. All rights reserved.Started:10:16:11 ?Error: 2007-08-17 22:16:12.95Code: 0x00000000Source: Copy Data from ROOM toDBNamedboER_ROOMTaskDescription: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.at DTS.PackageClass.Execute()at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()End ErrorError: 2007-08-17 22:16:14.75Code: 0x00000000Description: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.at DTS.PackageClass.Execute()at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()End ErrorError: 2007-08-17 22:16:15.14Code: 0x00000000The package execution fa...The step failed.

|||

how to Reinstall the SQL Server 2000 tools? where to find it .

Still no solution can solve this issue .

how to schedule DTS package in sql 2005

Hello,

I am trying to to schedule DTS Package but this message appear .

Message
The job failed. The Job was invoked by User sa. The last step to run was step 1 (1). and this

Message
Executed as user: Computer name\SYSTEM. The package execution failed. The step failed

so how I can schedule DTS Package in sql 2005 .

Do you have logging enabled for this package? if enabled check the log file it should have more detailed information on the error.

The error you have posted is a very generic error from the job agent, and it does not give any information.

Thanks

|||

Sounds like you might have some issues with the account settings. See the following thread for tips on setting up proxies / credentials / jobs etc.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1955723&SiteID=1

|||

Thanks, all

But still the same problem even I did all thing to solve but still can't schedule DTS, but when I see th proparites of the

SQL Server Agent-->Connection and found the sql server connection grayed out and can't select or change it . so may be this is the problem but how to run the agent under sa account .

|||

I am trying to schedule working DTS in sql server 2005 sp 2 . but I it failed and got this message . I searched at Internet and found this

http://support.microsoft.com/kb/904796

but I can’t understand how to solve it, any one can help please .

Message

|||

how to Reinstall the SQL Server 2000 tools? where to find it .

Still no solution can solve this issue .

How to schedule DBCC INDEXDEFRAG the best way

HI
I would like to optimize indexes on two databases dbA and
dbB. The optimization must be able to be configured by a
non DBA, with a script run after the installation of the
SQL Server and the user applications. The job has to be
scheduled.
Since the requirements are 24*7 access to all tables I
cannot (?)use DBCC REINDEX, which - what I understand -
should give me the best optimization of my existing
indexes regarding diskstructure etc., because it places
locks on the tables while running.
The next best thing must then - to my knowlegde - be DBCC
INDEXDEFRAG. (Note: Update of statistics are handled!)
Unfortunately I have some troubles scheduling DBCC
INDEXDEFRAG using a job.
The index optimization configured by the Maintenance Plan
Wizard uses DBCC REINDEX so that is out of the question,
except if xp_sqlmaint has another swith that can be used
instead of -RebldIdx (I cannot find an "indexdefrag
switch" it in BOL)'
Then I build a script which explicitely mentioned every
table and every index of both dbA and dbB to be optimized.
The script looked like this:
IF EXISTS (SELECT name FROM sysindexes WHERE name
= 'PK_ACT')
DBCC INDEXDEFRAG ('dbA', [ACT],[PK_ACT])
IF EXISTS (SELECT name FROM sysindexes WHERE name
= 'PK_ACT_ASSOC')
DBCC INDEXDEFRAG ('dbA', [ACT_ASSOC],[PK_ACT_ASSOC])
etc....
Unfortunalty, the script could not be pasted in the
command box of the job step since it migth have been to
long (670 linex approx.)
I of course could place the script externally in the file
system and have a call to osql.exe scheduled, but I would
rather have the DBCC INDEXDEFRAG statement:
- placed "internally" in the SQL Server rather than in the
file system.
- be able to make a call to DBCC INDEXDEFRAG without
having to specify each and every table/index to be
optimized, but - in the same way that DBCC REINDEX are
handled by the Maintenance Plan Wizard - just
specify: "these are the databases I want to process, now
do it for me!"
I captured and tweaked a script (bottom of message) to
setup the maintenance plan and job it runs okay, but with
DBCC REINDEX instead of DBCC INDEXDEFRAG:
The interesting part is this:
SET @.strMaintenanceCommand = 'EXECUTE
master.dbo.xp_sqlmaint ''-PlanID ' + convert(varchar
(256),@.MaintenancePlanID) + ' -WriteHistory -RebldIdx
100'''
If only "-RebldIdx" could be replaced with something else.
Could it'
yours truly
Jakob Persson
********************SCRIPT*************************''
USE MSDB
Go
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.MaintenancePlanID UNIQUEIDENTIFIER
DECLARE @.ReturnCode INT
DECLARE @.SQL VARCHAR(2000)
DECLARE @.strMaintenanceCommand VARCHAR(2000)
SELECT @.ReturnCode = 0
IF (SELECT COUNT(*) FROM [msdb].[dbo].[sysdbmaintplans]
WHERE [msdb].[dbo].[sysdbmaintplans].[plan_name] = N'INDEXDEFRAG') >= 1
BEGIN
SET @.MaintenancePlanID = (
SELECT [plan_id]
FROM [msdb].[dbo].[sysdbmaintplans]
WHERE [msdb].[dbo].[sysdbmaintplans].[plan_name]
= 'INDEXDEFRAG'
)
SET @.SQL = 'EXEC sp_delete_maintenance_plan '
+ '''' + convert(varchar(256),@.MaintenancePlanID) + ''''
EXECUTE(@.SQL)
END
SET @.MaintenancePlanID = NULL
EXEC sp_add_maintenance_plan @.plan_name
= 'INDEXDEFRAG', @.plan_id = @.MaintenancePlanID OUTPUT
EXEC sp_add_maintenance_plan_db @.plan_id = @.MaintenancePlanID , @.db_name = 'DACCIS01'
EXEC sp_add_maintenance_plan_db @.plan_id = @.MaintenancePlanID , @.db_name = 'MIRROR'
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name
= N'Database Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @.name = N'Database
Maintenance'
-- Delete the job with the same name (if it exists)
SELECT @.JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'DACCIS01 and
MIRROR ''INDEXDEFRAG''')
IF (@.JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @.JobID) AND (server_id <>
0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''DACCIS01 and
MIRROR ''INDEXDEFRAG'''' since there is already a multi-
server job with this name.', 16, 1)
-- GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @.job_name = N'DACCIS01
and MIRROR ''INDEXDEFRAG'''
SELECT @.JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT , @.job_name = N'DACCIS01 and
MIRROR ''INDEXDEFRAG''', @.owner_login_name = N'SA',
@.description = N'No description available.',
@.category_name = N'Database Maintenance', @.enabled = 1,
@.notify_level_email = 0, @.notify_level_page = 0,
@.notify_level_netsend = 0, @.notify_level_eventlog = 2,
@.delete_level= 0
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO
QuitWithRollback
-- Add the job steps
SET @.strMaintenanceCommand = 'EXECUTE
master.dbo.xp_sqlmaint ''-PlanID ' + convert(varchar
(256),@.MaintenancePlanID) + ' -WriteHistory -RebldIdx
100'''
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep
@.job_id = @.JobID,
@.step_id = 1,
@.step_name
= N'Step 1',
@.command = @.strMaintenanceCommand,
@.database_name = N'master',
@.server = N'',
@.database_user_name = N'',
@.subsystem
= N'TSQL',
@.cmdexec_success_code = 0,
@.flags = 4,
@.retry_attempts = 0,
@.retry_interval = 0,
@.output_file_name = N'',
@.on_success_step_id = 0,
@.on_success_action = 1,
@.on_fail_step_id = 0,
@.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO
QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.JobID, @.start_step_id = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO
QuitWithRollback
-- Add the job schedules
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule
@.job_id = @.JobID, @.name = N'Schedule 1', @.enabled = 1,
@.freq_type = 4, @.active_start_date = 20031212,
@.active_start_time = 10000, @.freq_interval = 1,
@.freq_subday_type = 8, @.freq_subday_interval = 2,
@.freq_relative_interval = 0, @.freq_recurrence_factor = 0,
@.active_end_date = 99991231, @.active_end_time = 235959
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO
QuitWithRollback
-- Add the Target Servers
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id
= @.JobID, @.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO
QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:Jakob,
> I of course could place the script externally in the file
> system and have a call to osql.exe scheduled, but I would
> rather have the DBCC INDEXDEFRAG statement:
> - placed "internally" in the SQL Server rather than in the
> file system.
OSQL.EXE is not any more or less "internal" than SQL Server Agent. both are
client applications which logs on to SQL Server and executes SQL commands.
> If only "-RebldIdx" could be replaced with something else.
> Could it'
Xp_sqlmaint is basically just a wrapper around sqlmaint.exe, where the
sqlmaint.exe logs on to SQL Server and execute the commands as specified by
the command-line parameters. Unfortunately, there's no command-line
parameter which makes the exe execute DBCC INDEXDEFRAG commands (to the best
of my knowledge, supported by Books Online).
My suggestion is that you write a stored procedure which has first a cursor
that uses the sysobjects or INFORMATION_SCHEM.TABLES to for each table in a
cursor. Inside this cursor, you have another cursor (another loop) where you
use sysindexes and loop each index. Inside this second loop, you now have
both table and index name, so you can construct and execute the DBCC
INDEXDEFRAG command. Just make sure that you in the cursor definition
exclude system tables, statistics etc (use functions such as OBJECTPROPERTY,
INDERPROPERTY etc).
You can probably write this as an sp_ and put it in master. Then have your
own table in which you store the database name for the databases you want to
defrag (write a simple UI if this need to be configurable for an end-user).
Then in your job, you have a cursor which loop this table with database
names in a cursor (having dbname in a variable, @.db), and execute:
EXEC @.db..sp_DefragAllTable --or whatever you called the proc
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jakob Persson" <jakobpersson@.yahoo.dk> wrote in message
news:019801c3c160$b0f763d0$a001280a@.phx.gbl...
> HI
> I would like to optimize indexes on two databases dbA and
> dbB. The optimization must be able to be configured by a
> non DBA, with a script run after the installation of the
> SQL Server and the user applications. The job has to be
> scheduled.
> Since the requirements are 24*7 access to all tables I
> cannot (?)use DBCC REINDEX, which - what I understand -
> should give me the best optimization of my existing
> indexes regarding diskstructure etc., because it places
> locks on the tables while running.
> The next best thing must then - to my knowlegde - be DBCC
> INDEXDEFRAG. (Note: Update of statistics are handled!)
> Unfortunately I have some troubles scheduling DBCC
> INDEXDEFRAG using a job.
> The index optimization configured by the Maintenance Plan
> Wizard uses DBCC REINDEX so that is out of the question,
> except if xp_sqlmaint has another swith that can be used
> instead of -RebldIdx (I cannot find an "indexdefrag
> switch" it in BOL)'
> Then I build a script which explicitely mentioned every
> table and every index of both dbA and dbB to be optimized.
> The script looked like this:
> IF EXISTS (SELECT name FROM sysindexes WHERE name
> = 'PK_ACT')
> DBCC INDEXDEFRAG ('dbA', [ACT],[PK_ACT])
> IF EXISTS (SELECT name FROM sysindexes WHERE name
> = 'PK_ACT_ASSOC')
> DBCC INDEXDEFRAG ('dbA', [ACT_ASSOC],[PK_ACT_ASSOC])
> etc....
> Unfortunalty, the script could not be pasted in the
> command box of the job step since it migth have been to
> long (670 linex approx.)
> I of course could place the script externally in the file
> system and have a call to osql.exe scheduled, but I would
> rather have the DBCC INDEXDEFRAG statement:
> - placed "internally" in the SQL Server rather than in the
> file system.
> - be able to make a call to DBCC INDEXDEFRAG without
> having to specify each and every table/index to be
> optimized, but - in the same way that DBCC REINDEX are
> handled by the Maintenance Plan Wizard - just
> specify: "these are the databases I want to process, now
> do it for me!"
>
> I captured and tweaked a script (bottom of message) to
> setup the maintenance plan and job it runs okay, but with
> DBCC REINDEX instead of DBCC INDEXDEFRAG:
> The interesting part is this:
> SET @.strMaintenanceCommand = 'EXECUTE
> master.dbo.xp_sqlmaint ''-PlanID ' + convert(varchar
> (256),@.MaintenancePlanID) + ' -WriteHistory -RebldIdx
> 100'''
> If only "-RebldIdx" could be replaced with something else.
> Could it'
> yours truly
> Jakob Persson
> ********************SCRIPT*************************''
> USE MSDB
> Go
>
> BEGIN TRANSACTION
> DECLARE @.JobID BINARY(16)
> DECLARE @.MaintenancePlanID UNIQUEIDENTIFIER
> DECLARE @.ReturnCode INT
> DECLARE @.SQL VARCHAR(2000)
> DECLARE @.strMaintenanceCommand VARCHAR(2000)
> SELECT @.ReturnCode = 0
> IF (SELECT COUNT(*) FROM [msdb].[dbo].[sysdbmaintplans]
> WHERE [msdb].[dbo].[sysdbmaintplans].[plan_name] => N'INDEXDEFRAG') >= 1
> BEGIN
> SET @.MaintenancePlanID = (
> SELECT [plan_id]
> FROM [msdb].[dbo].[sysdbmaintplans]
> WHERE [msdb].[dbo].[sysdbmaintplans].[plan_name]
> = 'INDEXDEFRAG'
> )
> SET @.SQL = 'EXEC sp_delete_maintenance_plan '
> + '''' + convert(varchar(256),@.MaintenancePlanID) + ''''
> EXECUTE(@.SQL)
> END
> SET @.MaintenancePlanID = NULL
> EXEC sp_add_maintenance_plan @.plan_name
> = 'INDEXDEFRAG', @.plan_id = @.MaintenancePlanID OUTPUT
> EXEC sp_add_maintenance_plan_db @.plan_id => @.MaintenancePlanID , @.db_name = 'DACCIS01'
> EXEC sp_add_maintenance_plan_db @.plan_id => @.MaintenancePlanID , @.db_name = 'MIRROR'
>
> IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name
> = N'Database Maintenance') < 1
> EXECUTE msdb.dbo.sp_add_category @.name = N'Database
> Maintenance'
> -- Delete the job with the same name (if it exists)
> SELECT @.JobID = job_id
> FROM msdb.dbo.sysjobs
> WHERE (name = N'DACCIS01 and
> MIRROR ''INDEXDEFRAG''')
> IF (@.JobID IS NOT NULL)
> BEGIN
> -- Check if the job is a multi-server job
> IF (EXISTS (SELECT *
> FROM msdb.dbo.sysjobservers
> WHERE (job_id = @.JobID) AND (server_id <>
> 0)))
> BEGIN
> -- There is, so abort the script
> RAISERROR (N'Unable to import job ''DACCIS01 and
> MIRROR ''INDEXDEFRAG'''' since there is already a multi-
> server job with this name.', 16, 1)
> -- GOTO QuitWithRollback
> END
> ELSE
> -- Delete the [local] job
> EXECUTE msdb.dbo.sp_delete_job @.job_name = N'DACCIS01
> and MIRROR ''INDEXDEFRAG'''
> SELECT @.JobID = NULL
> END
>
> BEGIN
> -- Add the job
> EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id => @.JobID OUTPUT , @.job_name = N'DACCIS01 and
> MIRROR ''INDEXDEFRAG''', @.owner_login_name = N'SA',
> @.description = N'No description available.',
> @.category_name = N'Database Maintenance', @.enabled = 1,
> @.notify_level_email = 0, @.notify_level_page = 0,
> @.notify_level_netsend = 0, @.notify_level_eventlog = 2,
> @.delete_level= 0
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO
> QuitWithRollback
>
> -- Add the job steps
> SET @.strMaintenanceCommand = 'EXECUTE
> master.dbo.xp_sqlmaint ''-PlanID ' + convert(varchar
> (256),@.MaintenancePlanID) + ' -WriteHistory -RebldIdx
> 100'''
> EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep
> @.job_id = @.JobID,
> @.step_id => 1,
> @.step_name
> = N'Step 1',
> @.command => @.strMaintenanceCommand,
>
> @.database_name = N'master',
> @.server => N'',
>
> @.database_user_name = N'',
> @.subsystem
> = N'TSQL',
>
> @.cmdexec_success_code = 0,
> @.flags => 4,
>
> @.retry_attempts = 0,
>
> @.retry_interval = 0,
>
> @.output_file_name = N'',
>
> @.on_success_step_id = 0,
>
> @.on_success_action = 1,
>
> @.on_fail_step_id = 0,
>
> @.on_fail_action = 2
>
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO
> QuitWithRollback
> EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id => @.JobID, @.start_step_id = 1
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO
> QuitWithRollback
> -- Add the job schedules
> EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule
> @.job_id = @.JobID, @.name = N'Schedule 1', @.enabled = 1,
> @.freq_type = 4, @.active_start_date = 20031212,
> @.active_start_time = 10000, @.freq_interval = 1,
> @.freq_subday_type = 8, @.freq_subday_interval = 2,
> @.freq_relative_interval = 0, @.freq_recurrence_factor = 0,
> @.active_end_date = 99991231, @.active_end_time = 235959
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO
> QuitWithRollback
> -- Add the Target Servers
> EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id
> = @.JobID, @.server_name = N'(local)'
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO
> QuitWithRollback
> END
> COMMIT TRANSACTION
> GOTO EndSave
> QuitWithRollback:
> IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
> EndSave:

How to Schedule and Run a SSIS package

Does any one know how to run

How to Schedule and Run a SSIS

Package for certain time intervals ?

I have created an application which will do some data transfering from one database

to another databse based on the start

time and end time values(Which is been already configured in some tables).

What I am exactly looking is that my application should do data transffering

Based on only the time intervals(Startime and End Time)

I wanted to execute my package only these time intervals. How to do this

Can any one help please.

Thanks & Regards

Deepu M.I

It sounds like you have a custom application that runs the packages at certain intervals. If that's the case, then just load the packages using the object model and then execute them at the given intervals. If you want to execute the packages using another tool, you can use Agent.

http://msdn2.microsoft.com/en-us/library/ms139805.aspx

Kirk Haselden
Author "SQL Server Integration Services"

|||

Hi Kirk,

I have a SQL Server 2005 Advance Express Edition installed and I could not find SQL Server Agent as mentioned.

|||

Yashman wrote:
Hi Kirk,
I have a SQL Server 2005 Advance Express Edition installed and I could not find SQL Server Agent as mentioned.

Yashman,
No need to post your question in multiple threads...|||

Hi Phil,

My question was related to the issue discussed above your message..

sql

How to Schedule and Run a SSIS package

Does any one know how to run

How to Schedule and Run a SSIS

Package for certain time intervals ?

I have created an application which will do some data transfering from one database

to another databse based on the start

time and end time values(Which is been already configured in some tables).

What I am exactly looking is that my application should do data transffering

Based on only the time intervals(Startime and End Time)

I wanted to execute my package only these time intervals. How to do this

Can any one help please.

Thanks & Regards

Deepu M.I

http://msdn2.microsoft.com/en-us/library/ms139805.aspx

Kirk Haselden
Author "SQL Server Integration Services"

|||

Hi Kirk,

I have a SQL Server 2005 Advance Express Edition installed and I could not find SQL Server Agent as mentioned.

|||

Yashman wrote:
Hi Kirk,
I have a SQL Server 2005 Advance Express Edition installed and I could not find SQL Server Agent as mentioned.

Yashman,
No need to post your question in multiple threads...|||

Hi Phil,

My question was related to the issue discussed above your message..

Wednesday, March 28, 2012

How to schedule a Stored Procedure to run nightly in SQL Server

Hello.
What is the easiest way to schedule a stored procedure to run nightly at a
specified time ?
Thanks in advance.Creating a job (sp_add_job) and scheduling it (sp_add_jobschedule). You can
use EM to do this. Open the server group, locate the server, open it, go to
management, right click on jobs and select "New job".
AMB
"Dave Pylatuk" wrote:
> Hello.
> What is the easiest way to schedule a stored procedure to run nightly at a
> specified time ?
> Thanks in advance.|||Thanks very much. It is so simple I feel silly.
"Alejandro Mesa" wrote:
> Creating a job (sp_add_job) and scheduling it (sp_add_jobschedule). You can
> use EM to do this. Open the server group, locate the server, open it, go to
> management, right click on jobs and select "New job".
>
> AMB
> "Dave Pylatuk" wrote:
> > Hello.
> >
> > What is the easiest way to schedule a stored procedure to run nightly at a
> > specified time ?
> >
> > Thanks in advance.

How to schedule a Stored Procedure to run nightly in SQL Server

How to schedule a stored procedure to run everyday

I have a stored procedure in Seever 2005 that i want to run everynight .

Whats the best way to do this. (? windows scheduler)

Can some one give me directions how to do it

Thanks

Generally you would use SQL Server Agent and schedule a job to run the stored procedure every night. If you are using the Express edition, you would need to use Windows Schedule as SQL Agent is not available with that edition. If you look up Jobs in Books Online, you will find information on doing this. Check the how to topics to walk you through the steps for setting up and executing jobs: http://msdn2.microsoft.com/en-us/library/ms189880.aspx

-Sue

|||

Ok i will give it a try and let you know

Thanks for your help

How to schedule a stored procedure

I'm new to sql server programing. Please help me to scedule a stored
procedure to run every hour. Thnak you all.No problem, just set up a new job with s step "TSQL" execution. You can
schedule that like any other jon in SQL Server.

EXEC SPSomeProcedure

HTH, jens Suessmeyer.|||Can u please illustrate it more ? How ro set up a new job ?|||MSDN is your friend:

http://msdn.microsoft.com/library/d...tomaem_19is.asp

HTH, jens Suessmeyer.|||"Ranjit" <mahalwarawa@.gmail.com> wrote in message
news:1135231814.534191.117420@.g44g2000cwa.googlegr oups.com...
> I'm new to sql server programing. Please help me to scedule a stored
> procedure to run every hour. Thnak you all.

Use the SQL Server Agent. Best bet in your case may be to look for it in
the Enterprise Manager GUI.

How to Save Report Parameters

I am trying to design a C# .NET web application that allows the user to run
a report, then save the report parameters so that when they open it again,
they get the same parameters with refreshed data.
I think my design should go something like this:
1) Render the report using the web service
2) make a button or something for the user to save the report
3) When the user clicks the save report button, call a web service method to
retrieve the report ParameterValue[] collection of parameter values that
were last used to run the report.
4) Save the ParameterValue[] collection into a DB table
5) when the user goes back to the report, load the ParameterValue[]
collection back from the DB table and render the report with the saved
parameters
How do I accomplish step (3)'
I looked at the ReportingService.GetReportParameters Method, but it does not
return the parameter values! It just returns a collection of ReportParameter
objects, which have no properties for the current values of a report. This
is just a listing of report parameters defined for a report, not what their
current values are in the current session.
The only methods that return a ParameterValue[] collection are the
GetDataDrivenSubscriptionProperties and GetSubscriptionProperties methods.
Does this mean I have to create a subscription before I can retrieve the
values?
I also see that the Render method does return a ParameterValue[] collection
as an output parameter, but only if the report is being rendered as a Report
History Snapshot! Do I have to create a snapshot every time just to store
the report parameters?
The only other thing I can think of is to write my own code to display the
parameters and code my own "view report" button, so that I can manually
store the parameter values... I don't want to resort to this!! :-P
Please point me in the right direction, and I will follow up.
MalikTry this (I'm using VB.Net syntax)
Dim ReportParameters(Number of Parameters defined in your report) as
ParameterValue
Dim iCount as Integer
'Retrieve your parameter values from the database.
'Assume two parameters from this example.
For iCount = 1 to ReportParameters.Length
ReportParameters(iCount - 1) = New ParameterValue
Select Case iCount
Case 1
ReportParameters(iCount - 1).Name = "The name of your report parameter
defined in your report"
ReportParameters(iCount - 1).value = "Value from your database"
Case 2
ReportParameters(iCount - 1).Name = "The name of your report parameter
defined in your report"
ReportParameters(iCount - 1).value = "Value from your database"
End Select
Next
Once you have defined all of your report parameters, you can use
ReportParameters in the render method, and your report will have the last
values that were used in the report you are viewing.
"Abdul Malik Said" wrote:
> I am trying to design a C# .NET web application that allows the user to run
> a report, then save the report parameters so that when they open it again,
> they get the same parameters with refreshed data.
> I think my design should go something like this:
> 1) Render the report using the web service
> 2) make a button or something for the user to save the report
> 3) When the user clicks the save report button, call a web service method to
> retrieve the report ParameterValue[] collection of parameter values that
> were last used to run the report.
> 4) Save the ParameterValue[] collection into a DB table
> 5) when the user goes back to the report, load the ParameterValue[]
> collection back from the DB table and render the report with the saved
> parameters
> How do I accomplish step (3)'
> I looked at the ReportingService.GetReportParameters Method, but it does not
> return the parameter values! It just returns a collection of ReportParameter
> objects, which have no properties for the current values of a report. This
> is just a listing of report parameters defined for a report, not what their
> current values are in the current session.
> The only methods that return a ParameterValue[] collection are the
> GetDataDrivenSubscriptionProperties and GetSubscriptionProperties methods.
> Does this mean I have to create a subscription before I can retrieve the
> values?
> I also see that the Render method does return a ParameterValue[] collection
> as an output parameter, but only if the report is being rendered as a Report
> History Snapshot! Do I have to create a snapshot every time just to store
> the report parameters?
> The only other thing I can think of is to write my own code to display the
> parameters and code my own "view report" button, so that I can manually
> store the parameter values... I don't want to resort to this!! :-P
> Please point me in the right direction, and I will follow up.
> Malik
>
>|||Mike,
Thanks a lot for your help.
I guess I was not clear in my stating of "Step 3", which was to determine
which parameters the user last used to run the report. By this, I meant to
say:
How can I programmatically get from the report object model which parameters
were last used to run the report? I have to get these first before I can
store them in the database. Retrieving them and reconstituting them is not
difficult after they are saved. The problem is, how do I find the values in
the first place?
Here is a better description:
The user clicks "View Report" from the parameters toolbar
The user clicks "Save Report" button which I have made elsewhere on the page
In the code for the "Save Report" button, how do I reference the parameter
values that were last chosen? I need to get these values from the report
first, before I can store them in the database. This is what I don't know
how to do.
Any more help would be greatly appreciated.
Malik
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:B8C4E6E5-5AA7-4D8F-8CB3-2B01387392B4@.microsoft.com...
> Try this (I'm using VB.Net syntax)
> Dim ReportParameters(Number of Parameters defined in your report) as
> ParameterValue
> Dim iCount as Integer
> 'Retrieve your parameter values from the database.
> 'Assume two parameters from this example.
> For iCount = 1 to ReportParameters.Length
> ReportParameters(iCount - 1) = New ParameterValue
> Select Case iCount
> Case 1
> ReportParameters(iCount - 1).Name = "The name of your report
parameter
> defined in your report"
> ReportParameters(iCount - 1).value = "Value from your database"
> Case 2
> ReportParameters(iCount - 1).Name = "The name of your report
parameter
> defined in your report"
> ReportParameters(iCount - 1).value = "Value from your database"
> End Select
> Next
> Once you have defined all of your report parameters, you can use
> ReportParameters in the render method, and your report will have the last
> values that were used in the report you are viewing.
> "Abdul Malik Said" wrote:
> > I am trying to design a C# .NET web application that allows the user to
run
> > a report, then save the report parameters so that when they open it
again,
> > they get the same parameters with refreshed data.
> >
> > I think my design should go something like this:
> >
> > 1) Render the report using the web service
> > 2) make a button or something for the user to save the report
> > 3) When the user clicks the save report button, call a web service
method to
> > retrieve the report ParameterValue[] collection of parameter values that
> > were last used to run the report.
> > 4) Save the ParameterValue[] collection into a DB table
> > 5) when the user goes back to the report, load the ParameterValue[]
> > collection back from the DB table and render the report with the saved
> > parameters
> >
> > How do I accomplish step (3)'
> >
> > I looked at the ReportingService.GetReportParameters Method, but it does
not
> > return the parameter values! It just returns a collection of
ReportParameter
> > objects, which have no properties for the current values of a report.
This
> > is just a listing of report parameters defined for a report, not what
their
> > current values are in the current session.
> >
> > The only methods that return a ParameterValue[] collection are the
> > GetDataDrivenSubscriptionProperties and GetSubscriptionProperties
methods.
> > Does this mean I have to create a subscription before I can retrieve the
> > values?
> >
> > I also see that the Render method does return a ParameterValue[]
collection
> > as an output parameter, but only if the report is being rendered as a
Report
> > History Snapshot! Do I have to create a snapshot every time just to
store
> > the report parameters?
> >
> > The only other thing I can think of is to write my own code to display
the
> > parameters and code my own "view report" button, so that I can manually
> > store the parameter values... I don't want to resort to this!! :-P
> >
> > Please point me in the right direction, and I will follow up.
> >
> > Malik
> >
> >
> >|||Hi Malik,
I've just completed an application which does exactly what you are
trying to do :) Mine does a little more like managing subscriptions,
schedules etc but the viewing and saving of the report/params is the
core of my app. I can't share the code as it's a commercial app but
will gladly help out with snippets etc :)
I did it the following way:
1/ Reports are already on the server so I loop through the selected
report and obtain all the params for that report. I then
enable/disable webusercontrols for each of the params on screen.
see code below to get the params from a report (formatting screwed by
newsreader :( )
private void GetSpecificReportParameters(string report)
{
if(rs == null) rs = new ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
ReportParameter[] parameters parameters =rs.GetReportParameters(report, null, false, null, null);
if (parameters != null)
{
string []nameArray = new string[50];
int i = 0;
foreach (ReportParameter rp in parameters)
{
// this is each of the names banged into an array
nameArray[i] = rp.Name;
i++;
}
// now loop through em and switch on/off the correct params
foreach (string str in nameArray)
{
if(str == null) // nothing so carry on
continue;
if(str.ToString() == "SurnameFrom")
{
SurnameWebUserControl.Visible = true;
}
// etc etc for all params found
}
}
}
2/ now i've got all the params for the report and the relevant
controls to fill in these params, my user can now set their params and
click on either View or Save report.
View builds up a string from the webusercontrols, appending the &
after each param & then passing this into the ReportViewer control on
the form.
Save does the same building of the string but instead of rendering to
the ReportViewer control, it simply saves to a database field along
with the path to the report i.e. heres one I've just created:
/Reports/MyReport&rs:Command=Render&rs:Format=HTML4.0&rc:Parameters=false&SurnameFrom=SMITH&SurnameTo=SMITH
When I recall the saved report, I simply pass that line in as the URL
prefixing the server address to it - then it's passed to the
ReportViewer control to render to screen.
Very simple and works very well - hope it makes sense to you. The
major advantage of this mechanism is that all we need to do is upload
a new report with params, the application will not need recompilation
in order to deal with the params (as long as no new params have been
added that the previous app knows nothing of!)
Si
On Fri, 20 Aug 2004 10:30:45 +0100, "Abdul Malik Said"
<diplacusis@.hotmNOSPAMail.com> wrote:
>Mike,
>Thanks a lot for your help.
>I guess I was not clear in my stating of "Step 3", which was to determine
>which parameters the user last used to run the report. By this, I meant to
>say:
>How can I programmatically get from the report object model which parameters
>were last used to run the report? I have to get these first before I can
>store them in the database. Retrieving them and reconstituting them is not
>difficult after they are saved. The problem is, how do I find the values in
>the first place?
>Here is a better description:
>The user clicks "View Report" from the parameters toolbar
>The user clicks "Save Report" button which I have made elsewhere on the page
>In the code for the "Save Report" button, how do I reference the parameter
>values that were last chosen? I need to get these values from the report
>first, before I can store them in the database. This is what I don't know
>how to do.
>Any more help would be greatly appreciated.
>Malik
>|||Hi Si,
That is a good way to solve this problem. If I understand what you are
doing, then it seems like you are basically writing your own code to replace
the parameter toolbar. This way, you know exactly what the values of the
controls are when the user clicks "save".
I could write a solution like this, but I was hoping there was a way of
doing it without developing code to replace the parameter selection toolbar.
Since there is already a parameter toolbar, I would like to use it. This
way, any report could be added to my application, since I wouldn't have to
assume anything about parameters.
I am currently trying to see if there is anything in the client-side
javascript generated for the report that will help me. I can see the current
report parameters there, but I am still trying to figure out how to retrieve
them properly...
Anyway, all of this has helped me think through my design, so many thanks
for your ideas.
Malik
"Si" <no@.spam.thanks> wrote in message
news:l9nbi052eoggh9ucpro71t1t72lbnou72i@.4ax.com...
> Hi Malik,
> I've just completed an application which does exactly what you are
> trying to do :) Mine does a little more like managing subscriptions,
> schedules etc but the viewing and saving of the report/params is the
> core of my app. I can't share the code as it's a commercial app but
> will gladly help out with snippets etc :)
> I did it the following way:
> 1/ Reports are already on the server so I loop through the selected
> report and obtain all the params for that report. I then
> enable/disable webusercontrols for each of the params on screen.
> see code below to get the params from a report (formatting screwed by
> newsreader :( )
> private void GetSpecificReportParameters(string report)
> {
> if(rs == null) rs = new ReportingService();
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
> ReportParameter[] parameters parameters => rs.GetReportParameters(report, null, false, null, null);
> if (parameters != null)
> {
> string []nameArray = new string[50];
> int i = 0;
> foreach (ReportParameter rp in parameters)
> {
> // this is each of the names banged into an array
> nameArray[i] = rp.Name;
> i++;
> }
> // now loop through em and switch on/off the correct params
> foreach (string str in nameArray)
> {
> if(str == null) // nothing so carry on
> continue;
> if(str.ToString() == "SurnameFrom")
> {
> SurnameWebUserControl.Visible = true;
> }
> // etc etc for all params found
> }
> }
> }
>
> 2/ now i've got all the params for the report and the relevant
> controls to fill in these params, my user can now set their params and
> click on either View or Save report.
> View builds up a string from the webusercontrols, appending the &
> after each param & then passing this into the ReportViewer control on
> the form.
> Save does the same building of the string but instead of rendering to
> the ReportViewer control, it simply saves to a database field along
> with the path to the report i.e. heres one I've just created:
>
/Reports/MyReport&rs:Command=Render&rs:Format=HTML4.0&rc:Parameters=false&Su
rnameFrom=SMITH&SurnameTo=SMITH
> When I recall the saved report, I simply pass that line in as the URL
> prefixing the server address to it - then it's passed to the
> ReportViewer control to render to screen.
> Very simple and works very well - hope it makes sense to you. The
> major advantage of this mechanism is that all we need to do is upload
> a new report with params, the application will not need recompilation
> in order to deal with the params (as long as no new params have been
> added that the previous app knows nothing of!)
> Si
>
>
>
> On Fri, 20 Aug 2004 10:30:45 +0100, "Abdul Malik Said"
> <diplacusis@.hotmNOSPAMail.com> wrote:
> >Mike,
> >
> >Thanks a lot for your help.
> >
> >I guess I was not clear in my stating of "Step 3", which was to determine
> >which parameters the user last used to run the report. By this, I meant
to
> >say:
> >
> >How can I programmatically get from the report object model which
parameters
> >were last used to run the report? I have to get these first before I can
> >store them in the database. Retrieving them and reconstituting them is
not
> >difficult after they are saved. The problem is, how do I find the values
in
> >the first place?
> >
> >Here is a better description:
> >
> >The user clicks "View Report" from the parameters toolbar
> >The user clicks "Save Report" button which I have made elsewhere on the
page
> >
> >In the code for the "Save Report" button, how do I reference the
parameter
> >values that were last chosen? I need to get these values from the report
> >first, before I can store them in the database. This is what I don't know
> >how to do.
> >
> >Any more help would be greatly appreciated.
> >
> >Malik
> >|||No probs,
I still have the param toolbar there, it's just hidden using the
rc:Parameters=false tag in the url.
I understand what you are trying to achive but from my limited
understanding, it's currently not possible as there is no statefull
save of the params once the report is generated, only beforehand.
Si
On Fri, 20 Aug 2004 12:52:22 +0100, "Abdul Malik Said"
<diplacusis@.hotmNOSPAMail.com> wrote:
>Hi Si,
>That is a good way to solve this problem. If I understand what you are
>doing, then it seems like you are basically writing your own code to replace
>the parameter toolbar. This way, you know exactly what the values of the
>controls are when the user clicks "save".
>I could write a solution like this, but I was hoping there was a way of
>doing it without developing code to replace the parameter selection toolbar.
>Since there is already a parameter toolbar, I would like to use it. This
>way, any report could be added to my application, since I wouldn't have to
>assume anything about parameters.
>I am currently trying to see if there is anything in the client-side
>javascript generated for the report that will help me. I can see the current
>report parameters there, but I am still trying to figure out how to retrieve
>them properly...
>Anyway, all of this has helped me think through my design, so many thanks
>for your ideas.
>Malik
>|||If you come up with an elegant solution to this problem, I'd love to
hear about it (i.e. please share it with the group). We would also
very much like to be able to save and restore parameters without
reinventing the wheel (a.k.a. the parameter selection toolbar :-)
Brad.
On Fri, 20 Aug 2004 12:52:22 +0100, "Abdul Malik Said"
<diplacusis@.hotmNOSPAMail.com> wrote:
>Hi Si,
>That is a good way to solve this problem. If I understand what you are
>doing, then it seems like you are basically writing your own code to replace
>the parameter toolbar. This way, you know exactly what the values of the
>controls are when the user clicks "save".
>I could write a solution like this, but I was hoping there was a way of
>doing it without developing code to replace the parameter selection toolbar.
>Since there is already a parameter toolbar, I would like to use it. This
>way, any report could be added to my application, since I wouldn't have to
>assume anything about parameters.
>I am currently trying to see if there is anything in the client-side
>javascript generated for the report that will help me. I can see the current
>report parameters there, but I am still trying to figure out how to retrieve
>them properly...
>Anyway, all of this has helped me think through my design, so many thanks
>for your ideas.
>Malik
>"Si" <no@.spam.thanks> wrote in message
>news:l9nbi052eoggh9ucpro71t1t72lbnou72i@.4ax.com...
>> Hi Malik,
>> I've just completed an application which does exactly what you are
>> trying to do :) Mine does a little more like managing subscriptions,
>> schedules etc but the viewing and saving of the report/params is the
>> core of my app. I can't share the code as it's a commercial app but
>> will gladly help out with snippets etc :)
>> I did it the following way:
>> 1/ Reports are already on the server so I loop through the selected
>> report and obtain all the params for that report. I then
>> enable/disable webusercontrols for each of the params on screen.
>> see code below to get the params from a report (formatting screwed by
>> newsreader :( )
>> private void GetSpecificReportParameters(string report)
>> {
>> if(rs == null) rs = new ReportingService();
>> rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
>> ReportParameter[] parameters parameters =>> rs.GetReportParameters(report, null, false, null, null);
>> if (parameters != null)
>> {
>> string []nameArray = new string[50];
>> int i = 0;
>> foreach (ReportParameter rp in parameters)
>> {
>> // this is each of the names banged into an array
>> nameArray[i] = rp.Name;
>> i++;
>> }
>> // now loop through em and switch on/off the correct params
>> foreach (string str in nameArray)
>> {
>> if(str == null) // nothing so carry on
>> continue;
>> if(str.ToString() == "SurnameFrom")
>> {
>> SurnameWebUserControl.Visible = true;
>> }
>> // etc etc for all params found
>> }
>> }
>> }
>>
>> 2/ now i've got all the params for the report and the relevant
>> controls to fill in these params, my user can now set their params and
>> click on either View or Save report.
>> View builds up a string from the webusercontrols, appending the &
>> after each param & then passing this into the ReportViewer control on
>> the form.
>> Save does the same building of the string but instead of rendering to
>> the ReportViewer control, it simply saves to a database field along
>> with the path to the report i.e. heres one I've just created:
>>
>/Reports/MyReport&rs:Command=Render&rs:Format=HTML4.0&rc:Parameters=false&Su
>rnameFrom=SMITH&SurnameTo=SMITH
>> When I recall the saved report, I simply pass that line in as the URL
>> prefixing the server address to it - then it's passed to the
>> ReportViewer control to render to screen.
>> Very simple and works very well - hope it makes sense to you. The
>> major advantage of this mechanism is that all we need to do is upload
>> a new report with params, the application will not need recompilation
>> in order to deal with the params (as long as no new params have been
>> added that the previous app knows nothing of!)
>> Si
>>
>>
>>
>> On Fri, 20 Aug 2004 10:30:45 +0100, "Abdul Malik Said"
>> <diplacusis@.hotmNOSPAMail.com> wrote:
>> >Mike,
>> >
>> >Thanks a lot for your help.
>> >
>> >I guess I was not clear in my stating of "Step 3", which was to determine
>> >which parameters the user last used to run the report. By this, I meant
>to
>> >say:
>> >
>> >How can I programmatically get from the report object model which
>parameters
>> >were last used to run the report? I have to get these first before I can
>> >store them in the database. Retrieving them and reconstituting them is
>not
>> >difficult after they are saved. The problem is, how do I find the values
>in
>> >the first place?
>> >
>> >Here is a better description:
>> >
>> >The user clicks "View Report" from the parameters toolbar
>> >The user clicks "Save Report" button which I have made elsewhere on the
>page
>> >
>> >In the code for the "Save Report" button, how do I reference the
>parameter
>> >values that were last chosen? I need to get these values from the report
>> >first, before I can store them in the database. This is what I don't know
>> >how to do.
>> >
>> >Any more help would be greatly appreciated.
>> >
>> >Malik
>> >
>|||Unfortunately, I have hit the wall with this one. The last hope was to use
the current URL that can be found in the client-side javascript generated
by the report server. However, I hear from microsoft that these variables
are for their internal use only, and could change in the future.
Since I don't want my reports to stop working when v2 is released, I can't
do that! :-@. I have given up hope for an "elegant solution".
I have looked at the SOAP method of integration as well as the URL method,
and it is just not possible to save report parameters as selected by the
user! The only way you can save them is if you re-invent your own parameter
inputs! And keep in mind that a complete integration solution should be able
to accept any possible combination of report parameters!
I think this is another oversight in the design of this product from
microsoft. They have specially designed ways to cache report data, saving
history, etc... But what about the more common case of wanting fresh data
for the same parameters? I am amazed that they did not think of this...
Malik
"Abdul Malik Said" <diplacusis@.hotmNOSPAMail.com> wrote in message
news:uMytSxqhEHA.3992@.TK2MSFTNGP11.phx.gbl...
> Hi Si,
> That is a good way to solve this problem. If I understand what you are
> doing, then it seems like you are basically writing your own code to
replace
> the parameter toolbar. This way, you know exactly what the values of the
> controls are when the user clicks "save".
> I could write a solution like this, but I was hoping there was a way of
> doing it without developing code to replace the parameter selection
toolbar.
> Since there is already a parameter toolbar, I would like to use it. This
> way, any report could be added to my application, since I wouldn't have to
> assume anything about parameters.
> I am currently trying to see if there is anything in the client-side
> javascript generated for the report that will help me. I can see the
current
> report parameters there, but I am still trying to figure out how to
retrieve
> them properly...
> Anyway, all of this has helped me think through my design, so many thanks
> for your ideas.
> Malik
> "Si" <no@.spam.thanks> wrote in message
> news:l9nbi052eoggh9ucpro71t1t72lbnou72i@.4ax.com...
> > Hi Malik,
> >
> > I've just completed an application which does exactly what you are
> > trying to do :) Mine does a little more like managing subscriptions,
> > schedules etc but the viewing and saving of the report/params is the
> > core of my app. I can't share the code as it's a commercial app but
> > will gladly help out with snippets etc :)
> >
> > I did it the following way:
> >
> > 1/ Reports are already on the server so I loop through the selected
> > report and obtain all the params for that report. I then
> > enable/disable webusercontrols for each of the params on screen.
> > see code below to get the params from a report (formatting screwed by
> > newsreader :( )
> >
> > private void GetSpecificReportParameters(string report)
> > {
> > if(rs == null) rs = new ReportingService();
> > rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
> >
> > ReportParameter[] parameters parameters => > rs.GetReportParameters(report, null, false, null, null);
> >
> > if (parameters != null)
> > {
> > string []nameArray = new string[50];
> > int i = 0;
> > foreach (ReportParameter rp in parameters)
> > {
> > // this is each of the names banged into an array
> > nameArray[i] = rp.Name;
> > i++;
> > }
> >
> > // now loop through em and switch on/off the correct params
> > foreach (string str in nameArray)
> > {
> > if(str == null) // nothing so carry on
> > continue;
> >
> > if(str.ToString() == "SurnameFrom")
> > {
> > SurnameWebUserControl.Visible = true;
> > }
> > // etc etc for all params found
> > }
> > }
> > }
> >
> >
> > 2/ now i've got all the params for the report and the relevant
> > controls to fill in these params, my user can now set their params and
> > click on either View or Save report.
> >
> > View builds up a string from the webusercontrols, appending the &
> > after each param & then passing this into the ReportViewer control on
> > the form.
> >
> > Save does the same building of the string but instead of rendering to
> > the ReportViewer control, it simply saves to a database field along
> > with the path to the report i.e. heres one I've just created:
> >
> >
>
/Reports/MyReport&rs:Command=Render&rs:Format=HTML4.0&rc:Parameters=false&Su
> rnameFrom=SMITH&SurnameTo=SMITH
> >
> > When I recall the saved report, I simply pass that line in as the URL
> > prefixing the server address to it - then it's passed to the
> > ReportViewer control to render to screen.
> >
> > Very simple and works very well - hope it makes sense to you. The
> > major advantage of this mechanism is that all we need to do is upload
> > a new report with params, the application will not need recompilation
> > in order to deal with the params (as long as no new params have been
> > added that the previous app knows nothing of!)
> >
> > Si
> >
> >
> >
> >
> >
> >
> >
> > On Fri, 20 Aug 2004 10:30:45 +0100, "Abdul Malik Said"
> > <diplacusis@.hotmNOSPAMail.com> wrote:
> >
> > >Mike,
> > >
> > >Thanks a lot for your help.
> > >
> > >I guess I was not clear in my stating of "Step 3", which was to
determine
> > >which parameters the user last used to run the report. By this, I meant
> to
> > >say:
> > >
> > >How can I programmatically get from the report object model which
> parameters
> > >were last used to run the report? I have to get these first before I
can
> > >store them in the database. Retrieving them and reconstituting them is
> not
> > >difficult after they are saved. The problem is, how do I find the
values
> in
> > >the first place?
> > >
> > >Here is a better description:
> > >
> > >The user clicks "View Report" from the parameters toolbar
> > >The user clicks "Save Report" button which I have made elsewhere on the
> page
> > >
> > >In the code for the "Save Report" button, how do I reference the
> parameter
> > >values that were last chosen? I need to get these values from the
report
> > >first, before I can store them in the database. This is what I don't
know
> > >how to do.
> > >
> > >Any more help would be greatly appreciated.
> > >
> > >Malik
> > >
>sql

Monday, March 26, 2012

How to save headings in query results?

In query analyzer when I run a query it displays
the query results in the reulsts pane with column
headings, but when I save the file, it doesn't save
column headings. I already checked in Tools -> Options ->
Results - Print Column Headers(*) is checked.
How can I save the column headers? Otherwise I have to
manually enter it in the spreasheet.
Thank you,
KK
Consider running the same query via a DTS data pump to a flat file. You can
check on the option to include headers.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"KK" <anonymous@.discussions.microsoft.com> wrote in message
news:191d01c49ccc$d8ffca00$a301280a@.phx.gbl...
In query analyzer when I run a query it displays
the query results in the reulsts pane with column
headings, but when I save the file, it doesn't save
column headings. I already checked in Tools -> Options ->
Results - Print Column Headers(*) is checked.
How can I save the column headers? Otherwise I have to
manually enter it in the spreasheet.
Thank you,
KK
sql

How to save headings in query results?

In query analyzer when I run a query it displays
the query results in the reulsts pane with column
headings, but when I save the file, it doesn't save
column headings. I already checked in Tools -> Options ->
Results - Print Column Headers(*) is checked.
How can I save the column headers? Otherwise I have to
manually enter it in the spreasheet.
Thank you,
KKConsider running the same query via a DTS data pump to a flat file. You can
check on the option to include headers.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"KK" <anonymous@.discussions.microsoft.com> wrote in message
news:191d01c49ccc$d8ffca00$a301280a@.phx.gbl...
In query analyzer when I run a query it displays
the query results in the reulsts pane with column
headings, but when I save the file, it doesn't save
column headings. I already checked in Tools -> Options ->
Results - Print Column Headers(*) is checked.
How can I save the column headers? Otherwise I have to
manually enter it in the spreasheet.
Thank you,
KK

how to run XMLA file in C Sharp?

how to run XMLA file in C Sharp

You need to execute an AdomdCommand. In your C# project, you need to add reference to Microsoft.AnalysisServices.AdomdCleint. If you have a non query DDL (such as creating a mining structure), the following example will do the trick:

try

{

using (AdomdCommand cmd = conn.CreateCommand())

{

cmd.CommandText = DDL;

cmd.ExecuteNoQuery();

}

catch(Exception ..)

{

…

}

In the example, DDL is a string contains your XMLA script. If you are running a query, you may want to call cmd.ExecuteReader to obtain a data reader.

Cheers,

|||sorry, but with a XMLA file, i want to build another database in analysis service, and i get XMLA file from exists database, i want to run it from C# project to create new database on another computer, because i can't create data source or data DSV from statement?
With DDL as you say, my XMLA file to long to put in to C# project, if i put it in C# project, DDL will be in many rows, it will return error.
please explain for me clearly
thanks|||

Your DDL should be a string in C# like this:

string DDL;

and you can read your XMLA script from a XML file into this string. Or you can append your xmla script line by line like this:

StringBuilder DDLBuilder = new StringBuilder();

DDLBuilder.Append(" <Envelope xmlns=\"http://schemas.xmlsoap.org/soap/envelope/\">");

DDLBuilder.Append("<Body>");

.....

DDLBuilder.Append("</Envelope>");

DDL = DDLBuilder.ToString();

Now you have you script in a string without put it in multiple lines. I hope this will help for your job.

Good luck,

How to run windows DOS command in SQL?

I want to check to see if any error files from SQL uploads are present in a
folder.
I want to run IF EXISTS *.err then run EXEC xp_sendmail stored procedure
How can I check a windows directory for *.err files in SQL syntax? osql?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
There is an 'undocumented' function that will do that for you
[xp_fileexist]. See this article:
Functions -UnDocumented Check to Verify File Existence
http://www.sqlservercentral.com/columnists/bknight/xpfileexist.asp
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
"jsheldon via droptable.com" <u2880@.uwe> wrote in message
news:6909a192e8dbf@.uwe...
>I want to check to see if any error files from SQL uploads are present in a
> folder.
> I want to run IF EXISTS *.err then run EXEC xp_sendmail stored procedure
>
> How can I check a windows directory for *.err files in SQL syntax? osql?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
>
sql

Friday, March 30, 2012

How to script adding a field to a table

How to scheule job to run last day of the month

How to schedule to run Integration Packages using SQL Agent/Job?

How to schedule report to run

how to schedule job to run vbscript file

how to schedule DTS package in sql 2005

how to schedule DTS package in sql 2005

How to schedule DBCC INDEXDEFRAG the best way

How to Schedule and Run a SSIS package

How to Schedule and Run a SSIS package

Wednesday, March 28, 2012

How to schedule a Stored Procedure to run nightly in SQL Server

How to schedule a Stored Procedure to run nightly in SQL Server

How to schedule a Stored Procedure to run nightly in SQL Server

How to schedule a stored procedure to run everyday

How to schedule a stored procedure

How to Save Report Parameters

Monday, March 26, 2012

How to save headings in query results?

How to save headings in query results?

how to run XMLA file in C Sharp?

How to run windows DOS command in SQL?

restrict access to database

Blog Archive

About Me