Professionally Handling ADO Logins

Hello -- I'm curious to know how most folks write VB code to connect to
their database using ADO without hardcoding the values in the application.
I frequently see books that allow the user to enter a username/password
within a login form and pass this information to the Conneciton object
Open() method. However, they then turn around and hardcode the server
name!!!
It would appear even attempting to use ADO Bound Controls in a professinoal
application is totally out of the question; since you must set the
connection info at design-time within its properties.
I was wondering if it made sense to display a logon form requesting the
login name/password, and then having a combo box that enumerated all the SQL
Servers on the network using SQLDMO. The users selection can be saved to
the registry or to an INI file and pre-filled in the box the next time the
application is executed...
Any help, ideas, or feedback would be appreciated.
--
...david
I you wish to reply to me personnally, please remove
the "underline" from scandal_123@.cox.net. The is done to avoid SPAM!I think there are multiple options you have for making the connection to the
database transparent to your application.
(1) You could use a DSN name in your code and possibly show the user the
list of configured DSN's in the system and then connect based on the user
selection.
(2) You could use a UDL file that contains information regarding the login
information.
(3) You could collect user-name, password and also show the list of servers
in the network for the the user to select and then form the connection
string yourself based on the input parameters and then connect.
In my experience, I've found (1) and (3) to be most popular.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"DavidM" <scandal_123@.cox.net> wrote in message
news:%234KT5atoDHA.2216@.TK2MSFTNGP12.phx.gbl...
> Hello -- I'm curious to know how most folks write VB code to connect to
> their database using ADO without hardcoding the values in the application.
> I frequently see books that allow the user to enter a username/password
> within a login form and pass this information to the Conneciton object
> Open() method. However, they then turn around and hardcode the server
> name!!!
> It would appear even attempting to use ADO Bound Controls in a
professinoal
> application is totally out of the question; since you must set the
> connection info at design-time within its properties.
> I was wondering if it made sense to display a logon form requesting the
> login name/password, and then having a combo box that enumerated all the
SQL
> Servers on the network using SQLDMO. The users selection can be saved to
> the registry or to an INI file and pre-filled in the box the next time the
> application is executed...
> Any help, ideas, or feedback would be appreciated.
>
> --
> ...david
> I you wish to reply to me personnally, please remove
> the "underline" from scandal_123@.cox.net. The is done to avoid SPAM!
>

Prod database counters

SQL Server 2000 SP4
How can I get "Transactions per second" and "write transactions percent"
values for a production database ?. I tried to find in the profiler but did
not see an event that would give me this info.
Thanks for any help.Those come from Perfmon not profiler.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:1456F10A-B293-48C1-9AFF-BB3FB5906F76@.microsoft.com...
> SQL Server 2000 SP4
> How can I get "Transactions per second" and "write transactions percent"
> values for a production database ?. I tried to find in the profiler but
> did
> not see an event that would give me this info.
> Thanks for any help.
>|||Thanks Andrew............ What do I need to choose in Performance
Monitoring ?
"Andrew J. Kelly" wrote:
> Those come from Perfmon not profiler.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:1456F10A-B293-48C1-9AFF-BB3FB5906F76@.microsoft.com...
> > SQL Server 2000 SP4
> >
> > How can I get "Transactions per second" and "write transactions percent"
> > values for a production database ?. I tried to find in the profiler but
> > did
> > not see an event that would give me this info.
> >
> > Thanks for any help.
> >
>|||Well the databases counters will have Transactions Per Second for each
database but I don't know what you mean by the other. If you want to see the
difference between requests and things that actually begin a transaction you
can monitor Batch Requests Per Second and Trans Per Second.
Some of these may be of interest:
http://www.sql-server-performance.com/sql_server_performance_audit10.asp
Performance Audit
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:8515ED62-75BF-4B44-AF03-6112174664A7@.microsoft.com...
> Thanks Andrew............ What do I need to choose in Performance
> Monitoring ?
>
> "Andrew J. Kelly" wrote:
>> Those come from Perfmon not profiler.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> news:1456F10A-B293-48C1-9AFF-BB3FB5906F76@.microsoft.com...
>> > SQL Server 2000 SP4
>> >
>> > How can I get "Transactions per second" and "write transactions
>> > percent"
>> > values for a production database ?. I tried to find in the profiler but
>> > did
>> > not see an event that would give me this info.
>> >
>> > Thanks for any help.
>> >
>>|||Thanks.................
"Andrew J. Kelly" wrote:
> Well the databases counters will have Transactions Per Second for each
> database but I don't know what you mean by the other. If you want to see the
> difference between requests and things that actually begin a transaction you
> can monitor Batch Requests Per Second and Trans Per Second.
> Some of these may be of interest:
> http://www.sql-server-performance.com/sql_server_performance_audit10.asp
> Performance Audit
> http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx
> Performance WP's
> http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
> http://www.sql-server-performance.com/sql_server_performance_audit.asp
> Hardware Performance CheckList
> http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
> SQL 2000 Performance tuning tips
> http://www.support.microsoft.com/?id=224587 Troubleshooting App
> Performance
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
> Disk Monitoring
> http://sqldev.net/misc/WaitTypes.htm
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:8515ED62-75BF-4B44-AF03-6112174664A7@.microsoft.com...
> > Thanks Andrew............ What do I need to choose in Performance
> > Monitoring ?
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Those come from Perfmon not profiler.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
> >>
> >>
> >> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> >> news:1456F10A-B293-48C1-9AFF-BB3FB5906F76@.microsoft.com...
> >> > SQL Server 2000 SP4
> >> >
> >> > How can I get "Transactions per second" and "write transactions
> >> > percent"
> >> > values for a production database ?. I tried to find in the profiler but
> >> > did
> >> > not see an event that would give me this info.
> >> >
> >> > Thanks for any help.
> >> >
> >>
> >>
>

Processing cubes in Analysis Services 2000

I'm working with Analysis Services 2000 (SP4) on a 64 bit Windows Server 2003 where I have installed the framework 2.0.
I need to write an application to process Analysis Services 2000 cubes but I can't use DSO because there isn't a 64 bit version available of this library.
Can I use AMO (Analysis Management Objects) for administering Analysis Services 2000? Is there another solution?
Thanks in advance
Daniele

Unfortunatlely you cannot.
Your only option is to find a 32-bit workstation and run your DSO application from there. This is a restriction of AS2K. Sorry.

_-_-_ Dave

Processing cubes in Analysis Services 2000

I'm working with Analysis Services 2000 (SP4) on a 64 bit Windows Server 2003 where I have installed the framework 2.0.
I need to write an application to process Analysis Services 2000 cubes but I can't use DSO because there isn't a 64 bit version available of this library.
Can I use AMO (Analysis Management Objects) for administering Analysis Services 2000? Is there another solution?
Thanks in advance
DanieleUnfortunately you cannot use AMO against Analysis Services 2000.
You can use DSO to remotely process Analysis Services object from another 32bit machine.
By the way you can use separate Redistributable component to install DSO
http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en
Look for
"Microsoft SQL Server 2005 Backward Compatibility Components "

Edward Melomed (MSFT)

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Process Add by AMO

Hi,

I want to write a programm which adds entries to an sql table and then process the dimension with processadd to only add this single row.

I have checked the samples and internet forums - but could not find out howto do..

anyhow I miss the link between a querybinding and the process command of the dimension...

I thought it would be something like...

Code Snippet

/* connect to lap server */

olapClient.Connect("DataSource=" + OLAPServerName);

/* get the Database */

Database olapDB = olapClient.Databases.GetByName(DatabaseName);

/* get the Dimension */

Dimension olapDim = olapDB.Dimensions.GetByName(DimensionName);

/* process the dimension and add the items i have created before */

olapDim.Process(ProcessType.ProcessAdd, new QueryBinding(DataSourceName, "select * from dbo.Slot where Slot='" + ResultID + "'"));

but this only raises an error ("

Errors in the metadata manager. The object reference is not valid. It does not match the structure of the metadata class hierarchy.")

How is this done correctly?

I want to use AMO to not write the xmla command statically - if there are further changes - but the dimension is build up to 100% of the single table dbo.Slot.

An example or a link would be helpfull.

HANNES

Nobody an idea?|||

Check this out. I think this guy has worked out the issues:

http://www.artisconsulting.com/Blogs/tabid/94/EntryID/2/Default.aspx

|||

I have seen this. My Question is - how do I programmatically create the xmla command. It seems AMO always creates wrong commands - the command always looks like a Process ADD for a partition - even if we do it on the dimension object.

It you confirm that there is no other way I will go by manually write xmla. But the drawback is - if we change the dimension I have to manually change the XMLA - and parts of the code - and otherwise the would be much less maintenance.

Hannes

|||

When I review the blog, it appears he works out both techniques. In his first example, he shows how to use query bindings which works for the Partition version. Towards the bottom, he shows you that you must create a DSV in AMO to provide the data source for the Dimension verions. (The DSV is used instead of the query bindings.)

I've looked through the Melomed et al. (SAMS) and Lachev (PROLOGIKA) books and do not find any additional info that would be of help to you. In the Harinath and Quinn book (WROX), an XMLA example of the process add against a dimension is shown on page 443. It too shows a DSV being generated to hold the query for the process add on the dimension.

Good luck,
Bryan

Process Add by AMO

Hi,

I want to write a programm which adds entries to an sql table and then process the dimension with processadd to only add this single row.

I have checked the samples and internet forums - but could not find out howto do..

anyhow I miss the link between a querybinding and the process command of the dimension...

I thought it would be something like...

Code Snippet

/* connect to lap server */

olapClient.Connect("DataSource=" + OLAPServerName);

/* get the Database */

Database olapDB = olapClient.Databases.GetByName(DatabaseName);

/* get the Dimension */

Dimension olapDim = olapDB.Dimensions.GetByName(DimensionName);

/* process the dimension and add the items i have created before */

olapDim.Process(ProcessType.ProcessAdd, new QueryBinding(DataSourceName, "select * from dbo.Slot where Slot='" + ResultID + "'"));

but this only raises an error ("

Errors in the metadata manager. The object reference is not valid. It does not match the structure of the metadata class hierarchy.")

How is this done correctly?

I want to use AMO to not write the xmla command statically - if there are further changes - but the dimension is build up to 100% of the single table dbo.Slot.

An example or a link would be helpfull.

HANNES

Nobody an idea?|||

Check this out. I think this guy has worked out the issues:

http://www.artisconsulting.com/Blogs/tabid/94/EntryID/2/Default.aspx

|||

I have seen this. My Question is - how do I programmatically create the xmla command. It seems AMO always creates wrong commands - the command always looks like a Process ADD for a partition - even if we do it on the dimension object.

It you confirm that there is no other way I will go by manually write xmla. But the drawback is - if we change the dimension I have to manually change the XMLA - and parts of the code - and otherwise the would be much less maintenance.

Hannes

|||

When I review the blog, it appears he works out both techniques. In his first example, he shows how to use query bindings which works for the Partition version. Towards the bottom, he shows you that you must create a DSV in AMO to provide the data source for the Dimension verions. (The DSV is used instead of the query bindings.)

I've looked through the Melomed et al. (SAMS) and Lachev (PROLOGIKA) books and do not find any additional info that would be of help to you. In the Harinath and Quinn book (WROX), an XMLA example of the process add against a dimension is shown on page 443. It too shows a DSV being generated to hold the query for the process add on the dimension.

Good luck,
Bryan

Process Add by AMO

Hi,

I want to write a programm which adds entries to an sql table and then process the dimension with processadd to only add this single row.

I have checked the samples and internet forums - but could not find out howto do..

anyhow I miss the link between a querybinding and the process command of the dimension...

I thought it would be something like...

Code Snippet

/* connect to lap server */

olapClient.Connect("DataSource=" + OLAPServerName);

/* get the Database */

Database olapDB = olapClient.Databases.GetByName(DatabaseName);

/* get the Dimension */

Dimension olapDim = olapDB.Dimensions.GetByName(DimensionName);

/* process the dimension and add the items i have created before */

olapDim.Process(ProcessType.ProcessAdd, new QueryBinding(DataSourceName, "select * from dbo.Slot where Slot='" + ResultID + "'"));

but this only raises an error ("

Errors in the metadata manager. The object reference is not valid. It does not match the structure of the metadata class hierarchy.")

How is this done correctly?

I want to use AMO to not write the xmla command statically - if there are further changes - but the dimension is build up to 100% of the single table dbo.Slot.

An example or a link would be helpfull.

HANNES

Nobody an idea?|||

Check this out. I think this guy has worked out the issues:

http://www.artisconsulting.com/Blogs/tabid/94/EntryID/2/Default.aspx

|||

I have seen this. My Question is - how do I programmatically create the xmla command. It seems AMO always creates wrong commands - the command always looks like a Process ADD for a partition - even if we do it on the dimension object.

It you confirm that there is no other way I will go by manually write xmla. But the drawback is - if we change the dimension I have to manually change the XMLA - and parts of the code - and otherwise the would be much less maintenance.

Hannes

|||

When I review the blog, it appears he works out both techniques. In his first example, he shows how to use query bindings which works for the Partition version. Towards the bottom, he shows you that you must create a DSV in AMO to provide the data source for the Dimension verions. (The DSV is used instead of the query bindings.)

I've looked through the Melomed et al. (SAMS) and Lachev (PROLOGIKA) books and do not find any additional info that would be of help to you. In the Harinath and Quinn book (WROX), an XMLA example of the process add against a dimension is shown on page 443. It too shows a DSV being generated to hold the query for the process add on the dimension.

Good luck,
Bryan

Procedures Information

Hi all
In Oracle if we want to get the procedures,functions or packages information we can write the stmt like this

select text from user_source where name='' and type=''

In sqlserver is it possible to get the information. I know that we are having a stored procedure sp_procedures but i don't know how to get the information from that because of its structure

Baba

Use the following query..

Code Snippet

Exec sp_helptext 'Your Sp Name'

|||If you're using SQL2005 you can use

SELECT OBJECT_DEFINITION(OBJECT_ID('sp_yourproc'))

|||

Note:

OBJECT_DEFINITION wont return the full soruce of you sp if you suffix the numbers (grouped stored procedures).

Code Snippet

Create Proc #Test

as

Begin

Select 1

End

Go

Create Proc #Test;2

as

Begin

Select 2

End

Go

Create Proc #Test;3

as

Begin

Select 3

End

Exec tempdb..sp_helptext '#Test' --Correct; Better to use on any version

SELECT OBJECT_DEFINITION(OBJECT_ID('tempdb..#Test')) --Only Fetch the first sp group 2 & 3 missing

|||

Thanks for pointing that out- have never used grouped stored procedures before. Is there a common scenario when you may wish to go this route?

Cheers

Rich

Procedure to Stress Test

Could someone write for exact SQL statment to stress test my tempDB, or
production.db?
(assume physical location is E:\Data\tempdb.MDF)
In order to test exact size of productionDB, should I copy the mdf file,
rename, and attached as different database to do it?
ThanksThere is no single exact SQL statement to stress your database, tempdb or
otherwise. One of the easiest ways is stress a database is to run a variety
of SQL statements using multiple clients concurrently. The more clients you
can run, the more stress you can put on the database, within reason. But of
course your SQL statments could be poorly written so that they end up not
stressing the database but generating just overhead.
Linchi
"MC" wrote:
> Could someone write for exact SQL statment to stress test my tempDB, or
> production.db?
> (assume physical location is E:\Data\tempdb.MDF)
> In order to test exact size of productionDB, should I copy the mdf file,
> rename, and attached as different database to do it?
> Thanks
>
>|||Check out LoadRunner from Mercury.

Procedure to Stress Test

Could someone write for exact SQL statment to stress test my tempDB, or
production.db?
(assume physical location is E:\Data\tempdb.MDF)
In order to test exact size of productionDB, should I copy the mdf file,
rename, and attached as different database to do it?
ThanksThere is no single exact SQL statement to stress your database, tempdb or
otherwise. One of the easiest ways is stress a database is to run a variety
of SQL statements using multiple clients concurrently. The more clients you
can run, the more stress you can put on the database, within reason. But of
course your SQL statments could be poorly written so that they end up not
stressing the database but generating just overhead.
Linchi
"MC" wrote:

> Could someone write for exact SQL statment to stress test my tempDB, or
> production.db?
> (assume physical location is E:\Data\tempdb.MDF)
> In order to test exact size of productionDB, should I copy the mdf file,
> rename, and attached as different database to do it?
> Thanks
>
>|||Check out LoadRunner from Mercury.

Procedure to Stress Test

Could someone write for exact SQL statment to stress test my tempDB, or
production.db?
(assume physical location is E:\Data\tempdb.MDF)
In order to test exact size of productionDB, should I copy the mdf file,
rename, and attached as different database to do it?
Thanks
There is no single exact SQL statement to stress your database, tempdb or
otherwise. One of the easiest ways is stress a database is to run a variety
of SQL statements using multiple clients concurrently. The more clients you
can run, the more stress you can put on the database, within reason. But of
course your SQL statments could be poorly written so that they end up not
stressing the database but generating just overhead.
Linchi
"MC" wrote:

> Could someone write for exact SQL statment to stress test my tempDB, or
> production.db?
> (assume physical location is E:\Data\tempdb.MDF)
> In order to test exact size of productionDB, should I copy the mdf file,
> rename, and attached as different database to do it?
> Thanks
>
>
|||Check out LoadRunner from Mercury.

procedure to send an email reminder help?

Hi guys,I hope somebody can help as I am trying to write a procedure in SQL that will be an email reminder sent to users on every 5 th or the month if they don't submit data before.

I am quite new to sql and procedures in particular so I was hoping somebody can help.

I need to:
1.Check the day of the month, if not 5th do nothing

2.Get the date of the prevoious month (get current date - 1 month,set day to 1)this is the funny bit.
I have a field DateEntered,but users only select the month and the year on the acctual page,but when submitted it gets written as a full date and defaults to the 1st of the month.

3.Get the list of hospitals that haven't got data for the previous month

4.Email the hospitals.

DECLARE @.returnDay int;
DECLARE @.DateEntered datetime;

SELECT @.returnDay = DatePart(day,GetDate())
If @.returnDay = 5 (syntax error near 5)
BEGIN
SELECT @.DateEntered = GetDate() - 30
Print DatePart(month, @.DateEntered)

I was hoping somebody could look at this and help me.

thanksYou don't have to get the fifth day of the month at all. When you set up your job to run your proc, just set it Monthly, every 5th day of the month.

You can the get the other date by:

SELECT @.datetime = DATEPART(MM,DATEADD(MM,-1,GETDATE()) + '/01/' + DATEPART(YY,GETDATE()

Then do what you need to do.|||skunked again, (job part)|||Yes, but I am getting this error "Syntax error converting datetime from character string"

I know there is a CONVERT function to do this but suprise,suprise I can't put it properly inside the formula

procedure to retrieve primary key

Hello!
I want to write a stored procedure, that will insert new row into a table and return the key of newly inserted row.check out SCOPE_IDENTITY() in bol

EDIT: i am assuming you are using an identity column for your pk. if you aren't, then you must already know the value you are inserting, right?|||That's exactly what I was looking for. Thanks.