sql nulls slow: creating

Showing posts with label creating. Show all posts

Thursday, March 22, 2012

ASP COM and database access

Hello All,

I am currently creating a browser interface for our thick client which
was built on Visual Basic using COM ( a three tier environment). We
support environments where the SQL database server is separate from the
application server.

The problem I am facing is this.

When I try to access the database server from the ASP page through the
COM object, I get access denied. This is because the COM is running
under the IWAM account and it does not have access to the database
tables. Since this is a system account I cannot add it to the database
server which is on a different machine possible a different domain too.
When I gave anonymous access to the database server, it worked fine but
this is obvously not a solution I want. The other option is to create a
COM+ wrapper for every objects (possibly 100)which can be run under a
specific account. But this will be a huge task.
Are there any other options? Any ideas are appreciated.
Thanks
SatishHi.I think this id COM fault not Database server.

Try to change access permissions using "dcomcnfg". On the Server where
IIS running.
Add IWAM account to allow access and to launch com objects.
It woks fine in my case. And i had the same situation.

sss wrote:
> Hello All,
>
> I am currently creating a browser interface for our thick client
which
> was built on Visual Basic using COM ( a three tier environment). We
> support environments where the SQL database server is separate from
the
> application server.
>
> The problem I am facing is this.
>
> When I try to access the database server from the ASP page through
the
> COM object, I get access denied. This is because the COM is running
> under the IWAM account and it does not have access to the database
> tables. Since this is a system account I cannot add it to the
database
> server which is on a different machine possible a different domain
too.
> When I gave anonymous access to the database server, it worked fine
but
> this is obvously not a solution I want. The other option is to create
a
> COM+ wrapper for every objects (possibly 100)which can be run under a
> specific account. But this will be a huge task.
> Are there any other options? Any ideas are appreciated.
> Thanks
> Satishsql

ASP Code to Monitor Scheduled SQL Jobs via the web

Hi Guys,

I'm Hoping some one could help me out, I'm in need of creating a web interface to monitor scheduled SQL Jobs, both on sql 2000 & 2005.

I'm new to asp, Can anyone point me in the right direction.Really appreciate it.

Regards

This should get you started for SQL2005.

<%@. Page Language="VB" EnableTheming="False" %><%@. ImportNamespace="System.Data" %><%@. ImportNamespace="System.Data.SqlClient" %><!DOCTYPE html PUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server">Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)If Not Page.IsPostBackThen JobHistory_GridView.DataSource = FJobHistory_GridView() JobHistory_GridView.DataBind()End If End Sub Function FJobHistory_GridView()As System.Data.IDataReaderDim SelectConnAs New SqlConnection("SQL_Server_ConnectionString_With_User_That_Can_Access_MSDB_Database")Dim SelectCmdAs New SqlCommand() SelectCmd.CommandText ="msdb.dbo.sp_help_jobactivity" SelectCmd.CommandType = CommandType.StoredProcedure SelectCmd.Connection = SelectConn SelectConn.Open()Dim dataReaderAs IDataReader = SelectCmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)Return dataReaderEnd Function </script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>SQL Job History</title></head><body> <form id="form1" runat="server"> <div> <asp:GridView ID="JobHistory_GridView" runat="server" AutoGenerateColumns="True"> </asp:GridView> </div> </form></body></html>

|||

Thanks a mil Brad,

This will def come in handy, I'm new at this company, i've noticed that they already have a web interface set up to monitor sql jobs, at present it is setup to monitor jobs on sql 2000, I need to now get this working with sql2005, I've configured everything neccessary on the config & aspx pages. I get a connection to sql 2005, I know this because as part of the monitoring tool you are able to view proccesser info as well, I can view this information of the the sql 2005 server however it does not display the jobs. (Just Blank)

Any idea why this might be? I could post the code if that would help.

Thanks again for your assistance.

|||

Try this for the gridview instead

 <asp:GridView ID="JobHistory_GridView" runat="server" AutoGenerateColumns="False" DataKeyNames="session_id,job_id"> <Columns> <asp:BoundField DataField="session_id" HeaderText="session_id"ReadOnly="True" /> <asp:BoundField DataField="job_id" HeaderText="job_id"ReadOnly="True" /> <asp:BoundField DataField="job_name" HeaderText="job_name" /> <asp:BoundField DataField="run_requested_date" HeaderText="run_requested_date" /> <asp:BoundField DataField="run_requested_source" HeaderText="run_requested_source" /> <asp:BoundField DataField="queued_date" HeaderText="queued_date" /> <asp:BoundField DataField="start_execution_date" HeaderText="start_execution_date" /> <asp:BoundField DataField="last_executed_step_id" HeaderText="last_executed_step_id" /> <asp:BoundField DataField="last_executed_step_date" HeaderText="last_executed_step_date" /> <asp:BoundField DataField="stop_execution_date" HeaderText="stop_execution_date" /> <asp:BoundField DataField="next_scheduled_run_date" HeaderText="next_scheduled_run_date" /> <asp:BoundField DataField="job_history_id" HeaderText="job_history_id" /> <asp:BoundField DataField="message" HeaderText="message" /> <asp:BoundField DataField="run_status" HeaderText="run_status" /> <asp:BoundField DataField="operator_id_emailed" HeaderText="operator_id_emailed" /> <asp:BoundField DataField="operator_id_netsent" HeaderText="operator_id_netsent" /> <asp:BoundField DataField="operator_id_paged" HeaderText="operator_id_paged" /> </Columns> </asp:GridView>

If that doesnt work, post your code.

Sunday, March 11, 2012

AS400/DB2 different column names in different configurations

Hello!

I am creating a package that will be executed against different AS400/DB2 databases. (I use a Foreach Loop). The AS400/DB2 is configured a little bit differently. This results in that the same column can have different name in different AS400/DB2. For example the column nameA4￡ADG, A4$ADG and A4?ADG is the same column.

In the Data Flow I use DataReader. Does anyone know how to get past this issue without creating one package for each column name?

Best regard

Tina

I'm not an AS400 person (or DB2), so I have no idea if this will work. But, it seems like you could use an expression to set the SQL Statement on the DataReader source, and alias the differently named columns to the same name. To set an expression on the data reader, you have to select the data flow task in the control flow, go to properties, and go to expressions.|||

Hello!

Thank you for your help!

But know I have solved it. I use different Data Flow for each set of column names. Depending on the company that is executed I defined the Precedence Constraint to "Expression and Constraint" and use the Expression:

@.[User::varCompanyName]=="CDHU"

In the Data Reader I have to set ValidateExternalMetadata to False

Best regards,

Tina

Saturday, February 25, 2012

AS / Creating cubes

Hi,
I have started to learn Analysis Services. I did the tutorial step by step,
but the last step "create the 'Sales' cube" doesn't work. I get the error
message "Unable to create cube 'sales'" - Unspecified error. It doesn't even
work with the manuelly in footmart creatd cube. What is wrong here? I have
also installed sp3a.
My other question is that is it possible to create diagramms in AS? If no,
how do you create you database diagramms?
Many thanks in advance
Do you belong to OLAP Admin group? Do you have write permission on OLAP
cube data directory?
Eric Li
SQL DBA
MCDBA
Stefan wrote:

> Hi,
> I have started to learn Analysis Services. I did the tutorial step by step,
> but the last step "create the 'Sales' cube" doesn't work. I get the error
> message "Unable to create cube 'sales'" - Unspecified error. It doesn't even
> work with the manuelly in footmart creatd cube. What is wrong here? I have
> also installed sp3a.
> My other question is that is it possible to create diagramms in AS? If no,
> how do you create you database diagramms?
> Many thanks in advance
>

AS / Creating cubes

Hi,
I have started to learn Analysis Services. I did the tutorial step by step,
but the last step "create the 'Sales' cube" doesn't work. I get the error
message "Unable to create cube 'sales'" - Unspecified error. It doesn't even
work with the manuelly in footmart creatd cube. What is wrong here? I have
also installed sp3a.
My other question is that is it possible to create diagramms in AS? If no,
how do you create you database diagramms?
Many thanks in advanceDo you belong to OLAP Admin group? Do you have write permission on OLAP
cube data directory?
Eric Li
SQL DBA
MCDBA
Stefan wrote:

> Hi,
> I have started to learn Analysis Services. I did the tutorial step by step
,
> but the last step "create the 'Sales' cube" doesn't work. I get the error
> message "Unable to create cube 'sales'" - Unspecified error. It doesn't ev
en
> work with the manuelly in footmart creatd cube. What is wrong here? I have
> also installed sp3a.
> My other question is that is it possible to create diagramms in AS? If no,
> how do you create you database diagramms?
> Many thanks in advance
>

Friday, February 24, 2012

Arrays in SQL server

I am creating an application for booking events. Each event has several dates and each date has a fixed amount of available seats.

Currently in the events table I have a field for the number of available seats and a field for the string of dates that are later parsed into a listbox control.

The problem with my current setup is each date is sharing the same number of available seats so if date Event A is decemented 10 so will Event B.

I need some way to associate each date with it's own number of available seats. What is the best way to do this? An array? If so how do I store an array in the DB?

Thanks, Justin.::I need some way to associate each date with it's own number of available seats. What is
::the best way to do this? An array?

By NO means.

The best way is to apply proper standard procedures for REALATIONAL databases. There is something called "normalization", which, btw., is explained in EVERY beginner book about databases.

Som ,y advice is: your best way is to shell out some money and read a ookabout how a database works. Something simple, like "SQL for Dummies".

You will end up with multiple tables, and an event being on multiple occations will have multiple entries, one for every date.

This:

::and a field for the string of dates that are later parsed into a listbox control.

is something that you better make sure your boss does not see. I would terminate everyone doing something like this immediately for gross incompetence in 99.99% of the cases. The relational model is VERY clear with how things like this should be modelled, and has been so for the last 30-sth years. Your model is denormalized and it is sure you run into trouble.

Really, get a beginner book about databases.|||As Thona states - Normalization is the key.
when you build a denormalized database, your asking for trouble.

With limited knowledge of what your trying to accomplish, I would envision a structure more like this...

tblEvent
--EventId (autonumber)
--EventDescription

tblEventInstance
--EventInstanceId (autonumber)
--EventId (integer)
--EventDate (date/time)
--EventSeats (integer)

tblEventSignUp
--EventInstanceId (integer)
--Person

etc...

anytime you're packing multiple pieces of data into a single column you gotta ask yourself "why?"

Sunday, February 19, 2012

Array

We are creating a report that we view detail records and find a row in the
list of records and want to grab one of the fields associated with that
record.
Example:
I have a loan number and principle balance.
I count the number of loans for a borrower.
Here is the data:
LN NUM Prin
1 5.00
2 10.00
3 15.00
The middle record is the one I want to use. But what I want as well is the
prin amount. How do I tell a function to look at the prin amount instead of
the ln num field?Hi Susan,
I've run into a similar issue. Were you able to resolve your issue? If so
please tell.
THanks,
"Susan" wrote:
> We are creating a report that we view detail records and find a row in the
> list of records and want to grab one of the fields associated with that
> record.
> Example:
> I have a loan number and principle balance.
> I count the number of loans for a borrower.
> Here is the data:
> LN NUM Prin
> 1 5.00
> 2 10.00
> 3 15.00
> The middle record is the one I want to use. But what I want as well is the
> prin amount. How do I tell a function to look at the prin amount instead of
> the ln num field?
>|||No I actually was looking for a way to calculate the median. I had to write
the report in Crystal.
"DigitalVixen" wrote:
> Hi Susan,
> I've run into a similar issue. Were you able to resolve your issue? If so
> please tell.
> THanks,
>
> "Susan" wrote:
> > We are creating a report that we view detail records and find a row in the
> > list of records and want to grab one of the fields associated with that
> > record.
> > Example:
> >
> > I have a loan number and principle balance.
> > I count the number of loans for a borrower.
> > Here is the data:
> >
> > LN NUM Prin
> > 1 5.00
> > 2 10.00
> > 3 15.00
> >
> > The middle record is the one I want to use. But what I want as well is the
> > prin amount. How do I tell a function to look at the prin amount instead of
> > the ln num field?
> >

Monday, February 13, 2012

Arithmetic operator

I'm using Sql Server reporting tools and creating a report.
How can I display the difference of two fields on a report?

you have 3 text boxes on a form..

say you have two fields...

textbox1.value = fields!myfield1.value

textbox2.value = fields!myfield2.value

then in the value for textbox3 put

= fields!myfield1.value - fields!myfield2.value

QED

Daryl

Sunday, February 12, 2012

Are there limitations to OLAP in Excel?

Hi,

Now, I use Analysis Services 2005 and Excel 2003 SP2 to deploy my OLAP model.

In Excel sheet, when creating a pivot table based on a Cube in Analysis Services, everything appears to work until I add a fourth dimension to the pivot table. Once I do that, I get a message that says "Running OLAP Query...(Press Esc to cancel)" so I must wait for a long time (about 5 minutes) to get the result. But while browsing the same data dimensions in Analysis Services Window, everything appears immediately.

Any ideas?

Best Regards!

You might qualify the question to read: "Are there limitations to using AS 2005 cubes in Excel 2003?"

Choosing client for SSAS 2005: Excel 2003 or Excel 2007

By Ramunas Balukonis

After we migrated Analysis Services from 2000 to 2005 we discovered that the same (or analogous) queries against SSAS 2005 runs even slower than AS 2000. Our users used MS Excel 2003 as front end on AS 2005. After some time I installed MS Excel 2007. Then I tried to do the same Pivot Table actions on both Excel versions (2003 and 2007): make Pivot Table from Advenure Works database placing days of [Date].[Calendar] [July 2003] and [August 2003] on rows, [Mountain-200 Silver, 38] and [Mountain-200 Silver, 42] from product [Product].[categories] on columns and [Sales Amount] as facts

Excel 2003 generates cumbersome MDX wits lots of except:

...

Excel 2007 generates elegant MDX:

...

The 1’st query from Excel 2003 runs 5 times longer!

After some time I gathered statistics from profiler and querylog. The results are as follows:

Avg MDX text length (Excel 2003): 2403
Avg MDX text length (Excel 2007): 1567
Avg query duration (Excel 2003): 507 ms
Avg query duration (Excel 2007): 96 ms

|||Thanks Ramunas!
Did you try to use the browser in Analysis Services or the Reporting Services to query your OLAP data . In my opinion, the queried time of using two ways above is better than the time of using Excel pivot table.
Could you explain me the reason why?

|||

Yeah, Excel 2003 is horrible with OLAP. It's the MDX issue, it does tons of EXCEPTS(), etc. Instead of scripting what it wants in the pivottable, it scripts what it doesn't want. Not sure how this ever got passed.

For your last reply, you will be hitting cache if you run from Excel, then run the same query somewhere else. You would have to execute the ClearCache command after every MDX statement for a true test. Or could be a network slowup if your using Excel externally (off the server) and running Management Studio on the server directly, for example.

Are there limitations to OLAP in Excel?

Hi,

Now, I use Analysis Services 2005 and Excel 2003 SP2 to deploy my OLAP model.

Any ideas?

Best Regards!

You might qualify the question to read: "Are there limitations to using AS 2005 cubes in Excel 2003?"

Choosing client for SSAS 2005: Excel 2003 or Excel 2007

By Ramunas Balukonis

Excel 2003 generates cumbersome MDX wits lots of except:

...

Excel 2007 generates elegant MDX:

...

The 1’st query from Excel 2003 runs 5 times longer!

After some time I gathered statistics from profiler and querylog. The results are as follows:

Avg MDX text length (Excel 2003): 2403
Avg MDX text length (Excel 2007): 1567
Avg query duration (Excel 2003): 507 ms
Avg query duration (Excel 2007): 96 ms

Thursday, February 9, 2012

Are Steps 1 and 3 of a replication agent job important?

Hello,
I am creating a custom job that will publish several publications to
subscribers. I will NOT be using the auto-generated jobs created by SQL
Server to perform these tasks, and instead I am performing this activity in
my custom job. I copied the job steps from the SQL generated jobs over to
mine.
However, I noticed that for every replication task, each job generated has 3
steps. Usually, they include something like the following steps:
1. Snapshot Agent startup message.
2. Run agent.
3. Detect nonlogged agent shutdown.
Because my custom job is very complex, I would prefer not to include steps 1
and 3 of every task as they dont seem to do anything important besides
logging. Before I remove them, I would like to ask for your feedback and/or
find out if I should keep these tasks there for any reason.
What will I miss out on if I remove them?
Johnny
I'd have to imagine you could get by without them... test it out and see
what happens.
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:E6274B37-FFBE-4A5A-9453-A2DDD4FB15A9@.microsoft.com...
> Hello,
> I am creating a custom job that will publish several publications to
> subscribers. I will NOT be using the auto-generated jobs created by SQL
> Server to perform these tasks, and instead I am performing this activity
> in
> my custom job. I copied the job steps from the SQL generated jobs over to
> mine.
> However, I noticed that for every replication task, each job generated has
> 3
> steps. Usually, they include something like the following steps:
> 1. Snapshot Agent startup message.
> 2. Run agent.
> 3. Detect nonlogged agent shutdown.
> Because my custom job is very complex, I would prefer not to include steps
> 1
> and 3 of every task as they dont seem to do anything important besides
> logging. Before I remove them, I would like to ask for your feedback
> and/or
> find out if I should keep these tasks there for any reason.
> What will I miss out on if I remove them?
> Johnny
>
|||I did test it out before and after and everything seems to work perfectly as
step 1 & 3 are simply logging startup and error messages. This works for both
the snapshot & distribution agents.
Johnny
"ChrisR" wrote:

> I'd have to imagine you could get by without them... test it out and see
> what happens.
>
> "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> news:E6274B37-FFBE-4A5A-9453-A2DDD4FB15A9@.microsoft.com...
>
>
|||The first job step adds a message to the history table. The last step is
used to detect if the agent shut down (gpf'd) without an error message.
It is advisable to include these job steps.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:3567799D-ACF3-4CF9-9329-6EC74CCFF89E@.microsoft.com...
> I did test it out before and after and everything seems to work perfectly
as
> step 1 & 3 are simply logging startup and error messages. This works for
both[vbcol=seagreen]
> the snapshot & distribution agents.
> Johnny
>
> "ChrisR" wrote:
SQL[vbcol=seagreen]
activity[vbcol=seagreen]
to[vbcol=seagreen]
has[vbcol=seagreen]
steps[vbcol=seagreen]

Thursday, March 22, 2012

ASP COM and database access

ASP Code to Monitor Scheduled SQL Jobs via the web

Sunday, March 11, 2012

AS400/DB2 different column names in different configurations

Saturday, February 25, 2012

AS / Creating cubes

AS / Creating cubes

Friday, February 24, 2012

Arrays in SQL server

Sunday, February 19, 2012

Array

Monday, February 13, 2012

Arithmetic operator

Sunday, February 12, 2012

Are there limitations to OLAP in Excel?

Are there limitations to OLAP in Excel?

Thursday, February 9, 2012

Are Steps 1 and 3 of a replication agent job important?

sql nulls slow

Blog Archive

About Me

About Me

Followers

Blog Archive