Showing posts with label load. Show all posts
Showing posts with label load. Show all posts

Friday, March 30, 2012

Pros and cons with table lock

I use an ole db destination which I simply use to insert data into database.
If it's configured to use Fast Load as access mode, then it is possible to switch on/off the Lock table property, which is set to true as default.

Are there any drawbacks with switching off the Lock table property? What do I gain when using it?

Thanks for your help!

Its a performance issue. Having a table lock means that the load process doesn't have to worry about lower level locks, and so the load should be quicker.
But it does mean no one else can use that table

Friday, March 23, 2012

Prompting for userid and password

Hi,
I have a web application which is calling a reporting services URL to load
couple of reports.
The web server is on Machine A and the reporting services and the SQL Server
are on Machine B.
Both the Machines A and B are not on any domain...
But when I log into my website and then try to generate a report, the web
application is popping up an prompt to enter the userid and password.
When U enter the userdid and password of the machine B, I am to view my
reports.
Can anyone please help me in resolving the above issue, where the user would
not want to enter the userid and password.Check the Reporting Services virtual directories in IIS. If they are not
setup with "Enable anonymous access", you need to enable this.
Kaisa M. Lindahl Lervik
"Ashok Segu" <Ashok Segu@.discussions.microsoft.com> wrote in message
news:99B66025-A1E6-49A1-8BFB-0E7B78A92189@.microsoft.com...
> Hi,
> I have a web application which is calling a reporting services URL to load
> couple of reports.
> The web server is on Machine A and the reporting services and the SQL
> Server
> are on Machine B.
> Both the Machines A and B are not on any domain...
> But when I log into my website and then try to generate a report, the web
> application is popping up an prompt to enter the userid and password.
> When U enter the userdid and password of the machine B, I am to view my
> reports.
> Can anyone please help me in resolving the above issue, where the user
> would
> not want to enter the userid and password.|||Add the url of the SSRS services to your "Trusted Sites" in Internet
Options. Surprisingly this works.
Ashok Segu wrote:
> Hi,
> I have a web application which is calling a reporting services URL to load
> couple of reports.
> The web server is on Machine A and the reporting services and the SQL Server
> are on Machine B.
> Both the Machines A and B are not on any domain...
> But when I log into my website and then try to generate a report, the web
> application is popping up an prompt to enter the userid and password.
> When U enter the userdid and password of the machine B, I am to view my
> reports.
> Can anyone please help me in resolving the above issue, where the user would
> not want to enter the userid and password.

Monday, March 12, 2012

Programmertically create and execute stored procedure in SMO

Hi all,

I need to programmertically create and execute stored procedure in SMO, without registering it on the database. I also need to be able to load a file containing a stored procedure and execute it, using SMO.

Can someone show me how? A C# sample would be greatly appreciated.

Thanks in advance.

Hi,

a simple sample would be:

StoredProcedure sp = new StoredProcedure("SomeDatabase","usp_Somesp","SomeSchema");

sp.TextBody = "SELECT 'SomeData'";

Server s = new Server("SomeServer");

s.Databases["SomeDatabase"].StoredProcedures.Add(sp);

s.ConnectionContext.ExecuteNonQuery("usp_Somesp");

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens,

I appreciate you help, but this is the error I get:

Error 1 'Microsoft.SqlServer.Management.Smo.StoredProcedureCollection' does not contain a definition for 'Add'

|||

Good morning ( for me 9:34 )

Look at this link http://msdn2.microsoft.com/en-us/library/ms162553.aspx

The Add is automatic when you use the Create method if you use the constructor

sp = new StoredProcedure(DataBaseName,StoredProcedureName)

Excuse me for my english

Have a nice day

|||Thank you very much.

Programmatically Specify SqlDataSource Parameters

I have a GridView bound to a SqlDataSource. On page load I would like to programmatically specify a SelectParameter value based on the role of the user. This SelectParameter will be used in my WHERE clause. The closest post I can find ishttp://forums.asp.net/thread/1233258.aspx but no answer was posted.

What code would I use to modify a SelectParameters value? Is it possible to reference a parameter by name (SqlDataSource1.SelectParameters["usertype"]) or does it have to be by index? (SqlDataSource1.SelectParameters[0])

Alternatively, perhaps I'm going about this in the wrong way, is there a better way to have dynamic GridView content based on the role of the user?

Thank you very much for your help.

Yes, you can set the value of a select parameters by name, for example
SqlDataSource1.SelectParameters["usertype"].DefaultValue ="role"
|||

Both parameter name and index are working here.

protected

void Page_Load(object sender,EventArgs e)

{

SqlDataSource1.SelectParameters[0].DefaultValue=

"yourvalue";

or

SqlDataSource1.SelectParameters["usertype"].DefaultValue="yourvalue";

}

|||

You can also set the value in the SqlDataSource_Selecting event via the e parameter:

Private Sub SqlDataSource1_Selecting(stuff)

e.command.paramter("param1").value=something

end sub

Friday, March 9, 2012

Programmatically get ScriptTask source code

Hi am trying without luck to load a package which contains a ScriptTask and read the source code of that task.

I can load the package and get the ScriptTask no problem.
However i am not sure how to get the source code.
I know i have to use the ScriptTaskCodeProvider and i assume the GetSourceCode() method.

This is what i have so far

ScriptTask scriptTask = taskHost.InnerObject as ScriptTask;
ScriptTaskCodeProvider codeProvider = new ScriptTaskCodeProvider();
codeProvider.LoadFromTask(scriptTask);
string sourceCode = codeProvider.GetSourceCode(scriptTask.VsaProjectName);

Any assistance greatly appreciated.

Cheers
Richard.

Application application = new Application();

Package package = application.LoadPackage(@."C:\Yukon\ISPlaying\ISPlaying\Package14.dtsx", null);

TaskHost taskHost = package.Executables[0] as TaskHost;

ScriptTask scriptTask = taskHost.InnerObject as ScriptTask;

ScriptTaskCodeProvider codeProvider = new ScriptTaskCodeProvider();

codeProvider.LoadFromTask(scriptTask);

string script = codeProvider.GetSourceCode(String.Format("dts://Scripts/{0}/ScriptMain.vsaitem", scriptTask.VsaProjectName));

Saturday, February 25, 2012

Programatically setting the sub-report to load

My system uses a variety of invoice templates for each client, so 'Bank A' would have one invoice type sent to it, and 'Bank B' would have another etc. Each invoice template is a report, and they can vary quite substantially.

Each month the accounts team need to print out all the invoices for a month. I would like to create a report which has the invoices as a sub-report, so they can all be exported together and printed together.

However each sub-report could obviously be a different invoice-template. I notice that the sub-report selector field is one of the only fields that doesn't take an expression. Is there a way to programatically set which sub-report is used? The parameter sent to each report type is always the same (the primary key of the invoice to report on).

Any ideas? Or any other methods which would achieve the same goals?

Thanks,

Paul

One way is to add all subreport-types you could ever need to your report and hide all but the one you need dynamically via the hidden-expression..|||

Yes I could see that working, but it is pretty messy. As I would like the facility to just add extra invoice-template reports at any time. But with your method I'd have to also modify the main list report.

Thanks for your help anyway :)

Monday, February 20, 2012

Program Speed

I was rushed into putting live a new program for our datawarehouse load last
night without adequate testing.
The program took over 3 hours to run in approx 44000 records last night as
opposed to about 1 hour normally. The program uses a loop.
This is as a direct result of a change we put in for Vehicle Make Model code
s.
The program previously only ever ran into an Add vehicle Id program for non
existing vehicle_id's but due to the restructuring of the make model setup i
t
needed to run into this program for each record in the loop.
Due to no testing on our parrallel box, I could not evaluate the time impact
on the program.
I wonder could we talk about any methods I could use to speed up the process
.
It goes a little something like this...
-- IF @.vehicle_id IS NULL or @.vehicle_id = 0
EXECUTE usp_dim_vh_add_id
@.parms in @.vh_make_model, @.vh_group etc...
@.vehicle_id out
-- usp_dim_vh_add_id
SELECT @.make_id = a.Vh_Make_id, @.model_id = Vh_Model_id
FROM DB1..vht_lu_Make a, DB1..vht_lu_Model b
WHERE a.Vh_Make_Id = b.Vh_Make_Id
AND b.Vh_Make_Model_id = @.vh_make_model
UPDATE VHt_lu_Vehicle
SET Vh_make_model_code = @.vh_make_model,
Vh_make_id = @.make_id, Vh_model_id = @.model_id, WHERE vehicle_id =
@.vehicle_id
....
Any input here greatly appreciated.If any of the _id or _code are varchar, and the original value was NULL,
then updating the value to something not NULL may increase the length of the
row in such a way that it results in chaning the rows position in the page
or perhaps even page splitting. This results in more database I/O and
transaction logging.
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:0DF8E237-32BD-4D2C-9D49-74092F1613CD@.microsoft.com...
>I was rushed into putting live a new program for our datawarehouse load
>last
> night without adequate testing.
> The program took over 3 hours to run in approx 44000 records last night as
> opposed to about 1 hour normally. The program uses a loop.
> This is as a direct result of a change we put in for Vehicle Make Model
> codes.
> The program previously only ever ran into an Add vehicle Id program for
> non
> existing vehicle_id's but due to the restructuring of the make model setup
> it
> needed to run into this program for each record in the loop.
> Due to no testing on our parrallel box, I could not evaluate the time
> impact
> on the program.
> I wonder could we talk about any methods I could use to speed up the
> process.
> It goes a little something like this...
> -- IF @.vehicle_id IS NULL or @.vehicle_id = 0
> EXECUTE usp_dim_vh_add_id
> @.parms in @.vh_make_model, @.vh_group etc...
> @.vehicle_id out
> -- usp_dim_vh_add_id
> SELECT @.make_id = a.Vh_Make_id, @.model_id = Vh_Model_id
> FROM DB1..vht_lu_Make a, DB1..vht_lu_Model b
> WHERE a.Vh_Make_Id = b.Vh_Make_Id
> AND b.Vh_Make_Model_id = @.vh_make_model
> UPDATE VHt_lu_Vehicle
> SET Vh_make_model_code = @.vh_make_model,
> Vh_make_id = @.make_id, Vh_model_id = @.model_id, WHERE vehicle_id =
> @.vehicle_id
> ....
> Any input here greatly appreciated.
>
>|||examnotes <marcmc@.discussions.microsoft.com> wrote in
news:0DF8E237-32BD-4D2C-9D49-74092F1613CD@.microsoft.com:

> I was rushed into putting live a new program for our datawarehouse
> load last night without adequate testing.
> The program took over 3 hours to run in approx 44000 records last
> night as opposed to about 1 hour normally. The program uses a loop.
> This is as a direct result of a change we put in for Vehicle Make
> Model codes.
> The program previously only ever ran into an Add vehicle Id program
> for non existing vehicle_id's but due to the restructuring of the make
> model setup it needed to run into this program for each record in the
> loop.
> Due to no testing on our parrallel box, I could not evaluate the time
> impact on the program.
You could try tuning the sql statements. As datasets get better the
performance of a SQL and even the execution plan can change. So you'll need
to modify the SQL to increase the speed. If you want a tool that can do
this for you automatically, look at SQL Optimizer for Visual Studio. You
can download it from
http://www.extensibles.com/modules...=Products&op=NS

> I wonder could we talk about any methods I could use to speed up the
> process.
> It goes a little something like this...
> Any input here greatly appreciated.
>
>
The Relentless One
Debugging is a state of mind
http://www.extensibles.com/|||Not trying to pick this apart, but 44,000 records in an hour sounds
like an incredibly long time; 3 hours sounds like an eternity. Our
data warehouse processes 40,000 rows in about 2 minutes, and it's not a
massive server; I'm curious as to why it takes so long for you as well.
This is the first data warehouse I've designed; maybe I'm missing
something <G>
Just based off the sample code you sent, I'm curious as to why you are
doing row-at-a-time updates; can you not batch this?
DDL would help us give better hints.
Stu