BEFORE Triggers: March 2012

Thursday, March 29, 2012

best tutorial for sql reporting service 2005

i am asked to develop a web application where i have to display reports in a seperate window from a hyperlink .i have to display the report in grid format and show a pie chart within the report layout .

i need to give user credential like print,save the report in excel or doc format and email report.

can anybody give the links for the best tutorial to achieve my purpose.

thanks

sally

Hi there,

There are many resources available. Try books online.

Here is a great guide to building a report.

http://www.eggheadcafe.com/articles/20040823.asp

Here is a guide to building a report + a web interface.

http://www.15seconds.com/Issue/041013.htm

Try MS downloads for further samples.

cheers,

Andrew

|||

Reporting Services Tutorials >>> http://msdn2.microsoft.com/en-us/library/ms170246.aspx

Best Tool to Create Web Page?

I am looking to extend some customer data from my SQL Server out to the Web.
I am looking for the best utility to help create these pages\sites quickly.
We are running SQL Server 2000 and IIS 5.0
Thanks..Ted,
Refer sp_makewebtask in BooksOnLine.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Ted Appleberry" <ted@.appleberry.com> wrote in message
news:efO3YkNQDHA.2832@.TK2MSFTNGP10.phx.gbl...
> I am looking to extend some customer data from my SQL Server out to the
Web.
> I am looking for the best utility to help create these pages\sites
quickly.
> We are running SQL Server 2000 and IIS 5.0
> Thanks..
>|||You could use free community supported tool Web Matrix.
http://www.asp.net/webmatrix/default.aspx?tabindex=4&tabid=46
Thanks
sethu
"Ted Appleberry" <ted@.appleberry.com> wrote in message
news:OgY8aHOQDHA.1752@.TK2MSFTNGP12.phx.gbl...
> I am looking for a nice drag and drop interface or something. Third
Party..
> Don't mind spending $$$. Don't want to write a bunch of code if I can
avoid
> it.. Yes, I know.. I am a wimp..
> "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> news:eN3$erNQDHA.1988@.TK2MSFTNGP12.phx.gbl...
> > Ted,
> >
> > Refer sp_makewebtask in BooksOnLine.
> >
> > --
> > Dinesh.
> > SQL Server FAQ at
> > http://www.tkdinesh.com
> >
> > "Ted Appleberry" <ted@.appleberry.com> wrote in message
> > news:efO3YkNQDHA.2832@.TK2MSFTNGP10.phx.gbl...
> > > I am looking to extend some customer data from my SQL Server out to
the
> > Web.
> > > I am looking for the best utility to help create these pages\sites
> > quickly.
> > > We are running SQL Server 2000 and IIS 5.0
> > > Thanks..
> > >
> > >
> >
> >
>

Best tool for Performance Tuning

Which is the best third party tool for SQL Server 2005 Performance Tuning/Optimization.

We purchased the Toad for SQL Server from Quest sofwater but it works only with dbo schemas....So if ur users were in schema xyz then it would not recognize it because they develop the tool for 2005 based on SQL Server 2000 where the schema ownesrhip is tied to the users.

Have you tried the Database Engine Tuning Advisor in Management Studio? I've not used it a great deal but what I have done seemed quite good. It also doesn't require an extra purchase.

sql

Tuesday, March 27, 2012

Best tool for OLAP browsing in SQL 2005 ?

Hi all,

Although SQL 2005 is well designed database and SQL 2005 AS is a good BI Platform. I have some bad feelings about the client tools taht supoorts these systems.

We use Proclarity 5.2 and 5.3 versions for browsing 2000 AS cubes.

But if i am not wrong, There Proclairty versions do not connect to SQL AS 2005 services. So I should use Proclartiy Analytics Server or 6.3 versions of Proclarity Desktop Professionals.

Additionaly, although we are fully licensed these Procalirty Desktop Professionals. I could not get any support from our country Partner...in TURKEY..

So I want to learn which desktop clients other AS 2005 user have for browsing 2005 cubes.

Microsoft developed a good platform for BI business as SQL 2005 and also had Proclarity for browsing cubes. But previous versions can not use SQL 2005 AS features.

So what is your opinion our solutions for browsing SQL 2005 cubes by front-office desktop users.

I need feasible any suggestions or solutions for my client users.

Thank you for all, kind responses.

ayhan akgun

MCSD .Net

Try installing AS OLEDB 9 to get your existing ProClarity to connect to Anlaysis Services 2005. You can get it from http://www.microsoft.com/downloads/details.aspx?familyid=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en

Of cource new version of ProClarity is going to have great features to work with new version of Analysis Services but you should definitely try and see if there is lots of things missing with existing ProClarity connecting to AS2005.

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

|||

Hi Edward,

Thank you for your quick response. I have downloaded and installed AS OLEDB to my client pc. Unfortunately, i still can not access 2005 analysis services. But if i use version 6.3 Proclarity i can access and browse 2005 cubes.

I get Login failed error while trying to access 2005 cubes by using 5.2.111 version,?

Any suddesions.?

Ayhan

|||

Ayhan,

I would suggest you to look also at Excel 2007. From what I heared Microsoft plans even Proclarity to work on top of Excel in the not so distance future. You will see that Excel 2007 has a lot of great features, and you can also use it with Sharepoint and Excel services.

I also compiled a list of known to me Analysis Services clients here: http://www.ssas-info.com/ssas_products.html

Vidas Matelis

|||

Hi,

It is exactly ok now. Although before installing that package, Proclarity says taht it uses OLEDB 9.0 version. It is exactly wrong.

To be able to connect SQL 2005 AS every Proclarity users must again install new that verison of driver.

Thank you, it is very important point.

Ayhan Akgün|||

Ayhan

ProClarity is now a Microsoft product and so you can get licenses and help direct from them.

Excel 2007 is also really good against SSAS, but is obviously a desktop install.

Finally you can get Reporting services to build reports against the cube but either direclty or by genretaing a report model from the cube (create a connection to the cube in report manager and then from the properties of the connection you can create a model) and then you can use report builder to build ad hoc rpeorts against the cube. This isn't olap of course.

Good Luck

Best to clear users before detaching a database.

What is the best way to clear users before using the stored procedure to
detach a database? I'm thinking about just writting a script to kill their
process before doing the detach command.
Thanks
DonAlter Database FooDB set offline with rollback immediate
go
Alter Database FooDB set online
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Don" <Don@.discussions.microsoft.com> wrote in message
news:70D2D8A6-80A4-4A61-81A7-169AFED7AECB@.microsoft.com...
> What is the best way to clear users before using the stored procedure to
> detach a database? I'm thinking about just writting a script to kill
> their
> process before doing the detach command.
> Thanks
> Don|||Don,
EM provides this option (CLEAR) when you detach a database graphically -
also asks if notificition should passed on to disconnected users.
HTH
Jerry
"Don" <Don@.discussions.microsoft.com> wrote in message
news:70D2D8A6-80A4-4A61-81A7-169AFED7AECB@.microsoft.com...
> What is the best way to clear users before using the stored procedure to
> detach a database? I'm thinking about just writting a script to kill
> their
> process before doing the detach command.
> Thanks
> Don

Best to clear users before detaching a database.

What is the best way to clear users before using the stored procedure to
detach a database? I'm thinking about just writting a script to kill their
process before doing the detach command.
Thanks
DonAlter Database FooDB set offline with rollback immediate
go
Alter Database FooDB set online
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Don" <Don@.discussions.microsoft.com> wrote in message
news:70D2D8A6-80A4-4A61-81A7-169AFED7AECB@.microsoft.com...
> What is the best way to clear users before using the stored procedure to
> detach a database? I'm thinking about just writting a script to kill
> their
> process before doing the detach command.
> Thanks
> Don|||Don,
EM provides this option (CLEAR) when you detach a database graphically -
also asks if notificition should passed on to disconnected users.
HTH
Jerry
"Don" <Don@.discussions.microsoft.com> wrote in message
news:70D2D8A6-80A4-4A61-81A7-169AFED7AECB@.microsoft.com...
> What is the best way to clear users before using the stored procedure to
> detach a database? I'm thinking about just writting a script to kill
> their
> process before doing the detach command.
> Thanks
> Don

Best to clear users before detaching a database.

What is the best way to clear users before using the stored procedure to
detach a database? I'm thinking about just writting a script to kill their
process before doing the detach command.
Thanks
Don
Alter Database FooDB set offline with rollback immediate
go
Alter Database FooDB set online
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Don" <Don@.discussions.microsoft.com> wrote in message
news:70D2D8A6-80A4-4A61-81A7-169AFED7AECB@.microsoft.com...
> What is the best way to clear users before using the stored procedure to
> detach a database? I'm thinking about just writting a script to kill
> their
> process before doing the detach command.
> Thanks
> Don
|||Don,
EM provides this option (CLEAR) when you detach a database graphically -
also asks if notificition should passed on to disconnected users.
HTH
Jerry
"Don" <Don@.discussions.microsoft.com> wrote in message
news:70D2D8A6-80A4-4A61-81A7-169AFED7AECB@.microsoft.com...
> What is the best way to clear users before using the stored procedure to
> detach a database? I'm thinking about just writting a script to kill
> their
> process before doing the detach command.
> Thanks
> Don

Best Technology for Reporting.

Current Environment:
SQL 2000 SP4 EE on Windows 2003 SP1
I need some suggestion on some of the currently available options for
reporting.
We have a transactional database where lots of transactions come throughout
the day. Some of the tables can have over 200 k records added. We need to
aggregate data on another server for reporting purposes and currently all
this is done via DTS jobs which do lots of locking and time consuming.
Since we are looking into re-architecting this solution and go away from
DTS, I am looking for various options that we can set up in test environment
and see which one supercedes over another one. Also, should I look into SQL
2005 or stay with SQL 2000 only.
Please advice.
Thanks in Advance."Mark" <Mark@.discussions.microsoft.com> wrote in message
news:8F1E6BD8-A36D-4D3E-8799-C67DF4F19D9D@.microsoft.com...
> Current Environment:
> SQL 2000 SP4 EE on Windows 2003 SP1
> I need some suggestion on some of the currently available options for
> reporting.
> We have a transactional database where lots of transactions come
> throughout
> the day. Some of the tables can have over 200 k records added. We need to
> aggregate data on another server for reporting purposes and currently all
> this is done via DTS jobs which do lots of locking and time consuming.
> Since we are looking into re-architecting this solution and go away from
> DTS, I am looking for various options that we can set up in test
> environment
> and see which one supercedes over another one. Also, should I look into
> SQL
> 2005 or stay with SQL 2000 only.
> Please advice.
> Thanks in Advance.
If the jobs are doing that much locking, then I would probably look at
rearchitecting the jobs themselves. Take advantage of the WITH (NOLOCK)
hints where appropriate etc.
Having your reporting done on the live data with the same aggregations that
your DTS jobs are doing doesn't seem like a particularly good idea to me.
Your performance will be better on the aggregated data already stored in the
reporting database server.
Just my .02
Rick Sawtell
MCT, MCSD, MCDBAsql

Best techniques for databse creation ?

Dear Experts,
Any one knows any link regarding best practices for schema and database crea
tion in SQL server 2000 in detail .
Faheem Latif
NETWORK SOLUTIONFaheem,
What specifically are you looking for? There are many things on the web if
you search google. However I recommend these links as a start:
SQL Server 2000 Operations Guide
http://www.microsoft.com/technet/pr...in/sqlops0.mspx
Vyas's article on Best Practices
http://www.sql-server-performance.c...t_practices.asp
Microsoft SQL Server 2000 Best Practices Analyzer 1.0 Beta
E07339C1F22&displaylang=en" target="_blank">http://www.microsoft.com/downloads/...&displaylang=en
Tinyurl: http://tinyurl.com/upzi
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Faheem" <anonymous@.discussions.microsoft.com> wrote in message
news:189C9EC5-9492-4B34-A0D7-C9908D15BA0E@.microsoft.com...
> Dear Experts,
> Any one knows any link regarding best practices for schema and database
creation in SQL server 2000 in detail .
> Faheem Latif
> NETWORK SOLUTION
>

Best techniques for databse creation ?

Dear Experts
Any one knows any link regarding best practices for schema and database creation in SQL server 2000 in detail
Faheem Latif
NETWORK SOLUTIONFaheem,
What specifically are you looking for? There are many things on the web if
you search google. However I recommend these links as a start:
SQL Server 2000 Operations Guide
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops0.mspx
Vyas's article on Best Practices
http://www.sql-server-performance.com/vk_sql_best_practices.asp
Microsoft SQL Server 2000 Best Practices Analyzer 1.0 Beta
http://www.microsoft.com/downloads/details.aspx?FamilyId=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
Tinyurl: http://tinyurl.com/upzi
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Faheem" <anonymous@.discussions.microsoft.com> wrote in message
news:189C9EC5-9492-4B34-A0D7-C9908D15BA0E@.microsoft.com...
> Dear Experts,
> Any one knows any link regarding best practices for schema and database
creation in SQL server 2000 in detail .
> Faheem Latif
> NETWORK SOLUTION
>

Best techniques for databse creation ?

Dear Experts,
Any one knows any link regarding best practices for schema and database creation in SQL server 2000 in detail .
Faheem Latif
NETWORK SOLUTION
Faheem,
What specifically are you looking for? There are many things on the web if
you search google. However I recommend these links as a start:
SQL Server 2000 Operations Guide
http://www.microsoft.com/technet/pro...n/sqlops0.mspx
Vyas's article on Best Practices
http://www.sql-server-performance.co..._practices.asp
Microsoft SQL Server 2000 Best Practices Analyzer 1.0 Beta
http://www.microsoft.com/downloads/d...displaylang=en
Tinyurl: http://tinyurl.com/upzi
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Faheem" <anonymous@.discussions.microsoft.com> wrote in message
news:189C9EC5-9492-4B34-A0D7-C9908D15BA0E@.microsoft.com...
> Dear Experts,
> Any one knows any link regarding best practices for schema and database
creation in SQL server 2000 in detail .
> Faheem Latif
> NETWORK SOLUTION
>

Best technique for Replication

thx Christian and Paul fro previous answers!
I have a SQL2000 server that hosts our accounting database and I want to
replicate it to another SQL2000 server so I can write reports etc, and do not
want to use the production server for these tasks. I tried to setup a
"snapshot" and was somewhat successful. My question is would it be better to
do a snapshot or transactional replication? The snapshot seemed to take along
time to run. I also do log backups every 15 minutes.
ps Paul I ordered your book...hope it will help also.
Rick Rushing
System Administrator
SQL Newbie
D & J Construction
Rick,
snapshot is sometimes OK but it'll lock the tables and you can't really do
this regularly, and unless all your data is changing it is overkill.
Transactional would be more appropriate. Log shipping is sometimes used, but
you have to be out of the standby server when the log is restored, which in
most cases rules it out.
Rgds,
Paul Ibison, SQL Server MVP
BTW the only book I've written is my address book

- Hilary's the man for
replication books and it's a very useful resource.
|||Paul,
There will be about 20 users on the SQL production server and only myself
using the other SQL server. My goal is to find a way to have a copy of the
database on the production server to the other server for use in writing
reports etc. So you think transaction would be a better approach?
ps you are right..it was Hilary's bok on replication.
Rick Rushing
System Administrator
D & J Construction
"Paul Ibison" wrote:

> Rick,
> snapshot is sometimes OK but it'll lock the tables and you can't really do
> this regularly, and unless all your data is changing it is overkill.
> Transactional would be more appropriate. Log shipping is sometimes used, but
> you have to be out of the standby server when the log is restored, which in
> most cases rules it out.
> Rgds,
> Paul Ibison, SQL Server MVP
> BTW the only book I've written is my address book

- Hilary's the man for
> replication books and it's a very useful resource.
>
>
|||Rick - yes this is quite a common approach, and gives you the control to
decide exactly which tables are selected. BTW the Database snapshots in SQL
Server 2005 will offer an interesting alternative.
Rgds,
Paul Ibison
|||Thanks Paul
I will pursue using transactional when my book arrives. We just installed
these SQL servers back in October so it will be a while before we upgrade.
thanks again for the input.
Rick Rushing
System Administrator
D & J Construction
"Paul Ibison" wrote:

> Rick - yes this is quite a common approach, and gives you the control to
> decide exactly which tables are selected. BTW the Database snapshots in SQL
> Server 2005 will offer an interesting alternative.
> Rgds,
> Paul Ibison
>
>

Best strategy? How to filter Report results

Hi,

I’m using SQL Server 2000.

I need some help on what strategy to adopt in order to filter some existing reports that I have written. I’m basically a web developer (ASP.NET) with some ? T-SQL skills (therefore I need advice on which is the best strategy please).

The business system I’m working on has 2 levels of access: Administrators (there can be several) and Users (20,000+).

Users can login and do stuff (do a task).

Administrators can login and give tasks to the Users to do. Administrators can also run reports to see the status of the Users relating to a particular task. A report on task X will show a list of all Users who have been given that same task (example of a task: “attend to a conference”) and next to each User will be the status (e.g. “Completed” or “Not yet completed”).

That is the existing system (above). Now I need to add a new type of person who can login and view the same reports as the administrator. This type of person will be called a “Manager”. The difference is that the Manager will only be able to view information on a defined set of Users. There might be a few hundred “Managers”.

So my “Wizard web page” code that allows an Administrator to create a Manager account will allow the Administrator to select (from a list of Users) which Users this Manager is allowed to see.

This means I’m going to have a new TABLE to relate the Manager to the User. I’ll call this the tblReporterUser table.

This table will have a ReporterID and UserID (read strategy #1 for reason this is not called “ManagerID”).

Here are the strategies I can think of. Which is best or is there one even better?

Strategy #1

As well as filling the tblReporterUser with Manager to User relationships, also fill it will Administrator to User relationships.

This means that when I call the Stored Procedure to select the rows of my report I need to pass in the ReporterID (which will actually be a ManagerID if it’s a Manager running the report, or an AdministratorID if it is an Administrator).

In order to filter Users out of the report I would then use this ReporterID to JOIN against the tblReporterUser TABLE.

Disadvantages: Each time Users are added to the system I have to make sure all administrators get permission to view the new User (add a row to tblReporterUser TABLE for each administrator with the new UserID).

Also it might make reporting for administrators slower?

Strategy #2

Explained with some pseudo-code (I don’t like this because of the “repeated code” as its easy to get into maintenance problems):

If (RoleIsAdministrator) THEN

-- role is Administrator

Existing report code

ELSE

-- role is Manager

Existing report code

JOIN on the tblReporterUser using the ManagerID as the ReporterID

ENDIF

Strategy #3

Is there a way to do a “conditional” JOIN ON the tblReporterUser if the “role” is Manager (but don’t do the JOIN if the role is Administrator).

If there is a way then this sounds like the best solution.

I have no idea how to do a conditional join – could someone give me an example please?

Massive thanks in advance to anyone replying with any amount of help.

Cheers,

Just back from 5 days camping. It rained! :D But in Australia that is a good thing!!!!

I guess this is a *BUMP* for this thread. If anyone has any ideas about "conditional JOINs" please help.

Many thanks,

Chiz

sql

Best Strategy to backup the system db

Hi,
I am trying to approach method to copy my system database using enterprise
tool. I have researched on Database Mainteance tool and Backup database. I
would like to use one of the method to copy my database without any
transcational log since we don't have any transcational going on and not
growing my database too much.
how would i accomplish this?
any comments would be appreciate
Pooja,
You might want to clarify whether or not this is a system database i.e.,
Master, MSDB, etc... or a user-defined database. When you say "my" database
I'm going to assume it is a user-defined database. If you do not require
the transaction logs to be backed up as part of your database recovery
strategy, you can set the recovery mode for the database to SIMPLE (the
t-log will be automatically truncated). Then schedule your full database
backup as a job or use a Maintenance Plan.
HTH
Jerry
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:8F05EF72-186D-4B93-BA20-E81364824F29@.microsoft.com...
> Hi,
> I am trying to approach method to copy my system database using enterprise
> tool. I have researched on Database Mainteance tool and Backup database. I
> would like to use one of the method to copy my database without any
> transcational log since we don't have any transcational going on and not
> growing my database too much.
> how would i accomplish this?
> any comments would be appreciate

Best Strategy to backup the system db

Hi,
I am trying to approach method to copy my system database using enterprise
tool. I have researched on Database Mainteance tool and Backup database. I
would like to use one of the method to copy my database without any
transcational log since we don't have any transcational going on and not
growing my database too much.
how would i accomplish this?
any comments would be appreciatePooja,
You might want to clarify whether or not this is a system database i.e.,
Master, MSDB, etc... or a user-defined database. When you say "my" database
I'm going to assume it is a user-defined database. If you do not require
the transaction logs to be backed up as part of your database recovery
strategy, you can set the recovery mode for the database to SIMPLE (the
t-log will be automatically truncated). Then schedule your full database
backup as a job or use a Maintenance Plan.
HTH
Jerry
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:8F05EF72-186D-4B93-BA20-E81364824F29@.microsoft.com...
> Hi,
> I am trying to approach method to copy my system database using enterprise
> tool. I have researched on Database Mainteance tool and Backup database. I
> would like to use one of the method to copy my database without any
> transcational log since we don't have any transcational going on and not
> growing my database too much.
> how would i accomplish this?
> any comments would be appreciate

Best Strategy to backup the system db

Hi,
I am trying to approach method to copy my system database using enterprise
tool. I have researched on Database Mainteance tool and Backup database. I
would like to use one of the method to copy my database without any
transcational log since we don't have any transcational going on and not
growing my database too much.
how would i accomplish this?
any comments would be appreciatePooja,
You might want to clarify whether or not this is a system database i.e.,
Master, MSDB, etc... or a user-defined database. When you say "my" database
I'm going to assume it is a user-defined database. If you do not require
the transaction logs to be backed up as part of your database recovery
strategy, you can set the recovery mode for the database to SIMPLE (the
t-log will be automatically truncated). Then schedule your full database
backup as a job or use a Maintenance Plan.
HTH
Jerry
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:8F05EF72-186D-4B93-BA20-E81364824F29@.microsoft.com...
> Hi,
> I am trying to approach method to copy my system database using enterprise
> tool. I have researched on Database Mainteance tool and Backup database. I
> would like to use one of the method to copy my database without any
> transcational log since we don't have any transcational going on and not
> growing my database too much.
> how would i accomplish this?
> any comments would be appreciate

Best strategy for multiple sites

I have a client that has a central database. Users are dispersed at regional
offices, and should only have access to data pertaining to their own office.
I'm trying to figure out the best way to secure data access for each group
of users. I thought of maintaining a table of users and the regional office
they belong to. Then a UDF would only return the appropriate data. This
means doing everything at the database level, regardless of the front end
(in this case, SSRS).
Another strategy would be to do all the filtering within SSRS, using the
user global variable, and filter the data dynamically based on the user
value (with a query expression). One thing I don't like about this approach
is that any other front end (web or Windows based) could access data without
filtering on a regional site basis.
Any thoughts?
--
Thank you,
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.comIt looks to me like you are currently planning to have a one to one match
between SQL Server logins and users. This can be a real pain. Also, you lose
the chance for connection pooling. For connection pooling to work the
connection must be exactly the same. If SQL Server can be run in mixed mode
I suggest creating a read only user that is used just for reporting. Then
when you have stored procedure you just have to give this execution rights.
Then combine that with using the user global variable. Don't filter it
locally, that will require a lot more data to be processed and you are much
better off to limit the amount of data coming to RS. Instead you can have a
hidden parameter that takes the user variable does a query and uses the
first aggregate to return the office. Then for all queries that need the
office you then pass that to the query.
Another possibility is you have a database per office. Then create views to
the database that has the data. Some views are one to one, others use the
appropriate office. Then use the user in an expression for the datasource.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Alain Quesnel" <alainsansspam@.logiquel.com> wrote in message
news:%23vUJ5XpNIHA.5720@.TK2MSFTNGP04.phx.gbl...
>I have a client that has a central database. Users are dispersed at
>regional offices, and should only have access to data pertaining to their
>own office. I'm trying to figure out the best way to secure data access for
>each group of users. I thought of maintaining a table of users and the
>regional office they belong to. Then a UDF would only return the
>appropriate data. This means doing everything at the database level,
>regardless of the front end (in this case, SSRS).
> Another strategy would be to do all the filtering within SSRS, using the
> user global variable, and filter the data dynamically based on the user
> value (with a query expression). One thing I don't like about this
> approach is that any other front end (web or Windows based) could access
> data without filtering on a regional site basis.
> Any thoughts?
> --
> Thank you,
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>

Best SSIS scripting tome?

Is The Rational Guide to Scripting SQL Server 2005 Integration Services Beta Preview by Donald Farmer the best way to learn how to use scripting in SSIS as of late 2006? I'm not a .NET developer, although I come from a Java and C++ background.

I already own Professional SQL Server 2005 Integration Server, but that one doesn't cover scripting so much.

Thanks in advance.

Donald is a frequent poster on this site. However, this question might be hard for him (and me) to answer because of conflict of interest issues.

I work with Donald and I can assure you that he is quite knowledgeable in the uses, misuses and intricacies of SSIS.

As one of our primary points of contact with customers he also owns a wealth of experience in typical and interesting uses for scripting.

I hope this helps inform your decision.

|||

I've read that book.. It gives you a nice understanding of the ScriptTask and ScriptComponent ( http://msdn2.microsoft.com/fr-fr/library/ms345171.aspx ) and gives you some tips and advices throughout the book. It covers input/ output variables and how to access them in the ScriptTask etc... The book has some practical realworld scenarios at the end.

However the real power of SSIS is hidden in the CustomComponent, but that's not even mentioned - well atleast imho (it's a lot easier to debug CustomComponents contra ScriptComponent which are nearly impossible)

Overall I was very happy with the chapters, the writing is clear and easy to understand. And if you understand Java it should be easy to get started with.

BEST SSIS equivalent for DTS2000 CURSORS

Hi everyone,

Right now we are trying to replace all stored procedures by SSIS logic.

One of the stored procedures does the following,

1. For Every record in PSTREELEAF find the Hierarchy FROM
PSTREENODE and store them in local valriables.
First the stored procedure pulls all the data from the PSTREELEAF table based on join condition into local variables using cursor. Then for every record in the cursor it finds the corresponding records from PSTREENODE table. This is just a part of the stored procedure.
can anyone tell me how can i do this in SSIS? and also tell me which task would be a perfect replacement for CURSORS in SSIS?

Thanks,
Praveen

Hi,
Do not use cursors if you can avoid it and do not attempt to replicate cursors.
Use Set based logic or derived tables or subqueries or Update statements or affect values to columns using subqueries as columns (top 1 or distinct).

Please post some more explicit examples and we may suggest another approach.

you can create loops in SSSI but the point is to make sure you really need this in the first place.

Philippe|||

Hi ,

thanks Phillippe.... here is the code which i need to change it to SSIS logic.

DECLARE NodeCursor CURSOR LOCAL FOR SELECT DISTINCT Tree_node_num,Range_from,Range_to,EFFDT
FROM FINANCEODS.DBO.PSTREELEAF
WHERE TREE_NAME='ACCTROLLUP' AND EffDt>=@.effDt AND INACTIVE_DATE IS NULL
OPEN NodeCursor
/*********************************************************************************
fetch nodes one by one and find out the hierarchy of the nodes
*********************************************************************************/
FETCH NEXT FROM NodeCursor
INTO @.Tree_node_num,@.LeafFrom,@.LeafTo,@.effDt
SET @.Parent_node_num=@.Tree_node_num
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.p_errNum = 50505
SET @.p_errMsg = 'SPUpdateAccounts: Error Fetching Node Cursor.'
RETURN Exit Stored Procedure
END
WHILE @.@.FETCH_STATUS = 0
BEGIN
/*****************************************************
While Loop to find out all the Hierarchy information
******************************************************/
WHILE (@.Parent_node_num)<>0
BEGIN
/************************************************************************************
Find the Parent node and its level(hierarchy)
*************************************************************************************/
SELECT @.Parent_node_num=Parent_node_num, @.Tree_node_num=Tree_node_num,@.TreeLevel=TREE_LEVEL_NUM ,@.TreeNode=ISNULL(B.DESCR,A.TREE_NODE)
FROM FINANCEODS.DBO.PSTREENODE A
LEFT JOIN ( SELECT MAX(EFFDT) AS EFFDT,DESCR,TREE_NODE,INACTIVE_DATE FROM FINANCEODS.DBO.PS_TREE_NODE_TBL WHERE EFFDT<=@.EFFDT GROUP BY DESCR,TREE_NODE,INACTIVE_DATE ) B ON A.TREE_NODE=B.TREE_NODE
WHERE A.Tree_node_num=@.Tree_node_num
AND A.TREE_NAME='ACCTROLLUP' AND A.EFFDT=@.EFFDT AND A.INACTIVE_DATE IS NULL AND B.INACTIVE_DATE IS NULL
/************************************************************************************
Store the Hierarchy level information in local variables.
*************************************************************************************/
SELECT @.Source= (CASE @.TreeLevel WHEN 6 THEN @.TreeNode ELSE @.Source END),
@.Type= (CASE @.TreeLevel WHEN 5 THEN @.TreeNode ELSE @.Type END),
@.Element= (CASE @.TreeLevel WHEN 4 THEN @.TreeNode ELSE @.Element END),
@.Component= (CASE @.TreeLevel WHEN 3 THEN @.TreeNode ELSE @.Component END),
@.FinStatement= (CASE @.TreeLevel WHEN 2 THEN @.TreeNode ELSE @.FinStatement END)
SET @.Tree_node_num=@.Parent_node_num
END

Best SQL Magazine

Folks
I wanted to subscribe best online SQL magazine or printed material also
should be fine. There are so many magazines out there. I just wanted to
subscribe one and that got to be the best one. Can anyone suggest one that
you find it very useful, has real life tips, best practices etc?
Thank a lot!
I would suggest the SQL Server Magazine.
HTH, Jens Suessmeyer.
"rupart" <rupart@.discussions.microsoft.com> schrieb im Newsbeitrag
news:8A37A59F-CC48-4862-A7B1-9171AA44DAF1@.microsoft.com...
> Folks
> I wanted to subscribe best online SQL magazine or printed material also
> should be fine. There are so many magazines out there. I just wanted to
> subscribe one and that got to be the best one. Can anyone suggest one that
> you find it very useful, has real life tips, best practices etc?
> Thank a lot!
|||Best...well that's a hard question to answer because what's best for
me would be one that meets my needs. Since my needs would differ from
yours, my best would differ from yours.
Having said that, I find SQL Server Standard and SQL Server Magazine
to be useful. Both are relatively inexpensive and tend to offer good
quality.
SQL Server Magazine provides articles written by a handful of experts
with LOTS of technical expertise and knowledge.
SQL Server Central tends to offer a much more diverse set of authors
with varying levels of expertise and experiences. It's more of a users
helping users kind of approach.
I don't think you'll go wrong with either. Probably even good idea to
get both.
HTH...
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg...l/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
On Fri, 24 Jun 2005 01:17:02 -0700, rupart
<rupart@.discussions.microsoft.com> wrote:

>Folks
>I wanted to subscribe best online SQL magazine or printed material also
>should be fine. There are so many magazines out there. I just wanted to
>subscribe one and that got to be the best one. Can anyone suggest one that
>you find it very useful, has real life tips, best practices etc?
>Thank a lot!

Best SQL Magazine

Folks
I wanted to subscribe best online SQL magazine or printed material also
should be fine. There are so many magazines out there. I just wanted to
subscribe one and that got to be the best one. Can anyone suggest one that
you find it very useful, has real life tips, best practices etc?
Thank a lot!I would suggest the SQL Server Magazine.
HTH, Jens Suessmeyer.
"rupart" <rupart@.discussions.microsoft.com> schrieb im Newsbeitrag
news:8A37A59F-CC48-4862-A7B1-9171AA44DAF1@.microsoft.com...
> Folks
> I wanted to subscribe best online SQL magazine or printed material also
> should be fine. There are so many magazines out there. I just wanted to
> subscribe one and that got to be the best one. Can anyone suggest one that
> you find it very useful, has real life tips, best practices etc?
> Thank a lot!|||Best...well that's a hard question to answer because what's best for
me would be one that meets my needs. Since my needs would differ from
yours, my best would differ from yours.
Having said that, I find SQL Server Standard and SQL Server Magazine
to be useful. Both are relatively inexpensive and tend to offer good
quality.
SQL Server Magazine provides articles written by a handful of experts
with LOTS of technical expertise and knowledge.
SQL Server Central tends to offer a much more diverse set of authors
with varying levels of expertise and experiences. It's more of a users
helping users kind of approach.
I don't think you'll go wrong with either. Probably even good idea to
get both.
HTH...
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/t...il/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
On Fri, 24 Jun 2005 01:17:02 -0700, rupart
<rupart@.discussions.microsoft.com> wrote:

>Folks
>I wanted to subscribe best online SQL magazine or printed material also
>should be fine. There are so many magazines out there. I just wanted to
>subscribe one and that got to be the best one. Can anyone suggest one that
>you find it very useful, has real life tips, best practices etc?
>Thank a lot!

Best SQL Magazine

Folks
I wanted to subscribe best online SQL magazine or printed material also
should be fine. There are so many magazines out there. I just wanted to
subscribe one and that got to be the best one. Can anyone suggest one that
you find it very useful, has real life tips, best practices etc?
Thank a lot!I would suggest the SQL Server Magazine.
HTH, Jens Suessmeyer.
"rupart" <rupart@.discussions.microsoft.com> schrieb im Newsbeitrag
news:8A37A59F-CC48-4862-A7B1-9171AA44DAF1@.microsoft.com...
> Folks
> I wanted to subscribe best online SQL magazine or printed material also
> should be fine. There are so many magazines out there. I just wanted to
> subscribe one and that got to be the best one. Can anyone suggest one that
> you find it very useful, has real life tips, best practices etc?
> Thank a lot!|||Best...well that's a hard question to answer because what's best for
me would be one that meets my needs. Since my needs would differ from
yours, my best would differ from yours.
Having said that, I find SQL Server Standard and SQL Server Magazine
to be useful. Both are relatively inexpensive and tend to offer good
quality.
SQL Server Magazine provides articles written by a handful of experts
with LOTS of technical expertise and knowledge.
SQL Server Central tends to offer a much more diverse set of authors
with varying levels of expertise and experiences. It's more of a users
helping users kind of approach.
I don't think you'll go wrong with either. Probably even good idea to
get both.
HTH...
--
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
On Fri, 24 Jun 2005 01:17:02 -0700, rupart
<rupart@.discussions.microsoft.com> wrote:
>Folks
>I wanted to subscribe best online SQL magazine or printed material also
>should be fine. There are so many magazines out there. I just wanted to
>subscribe one and that got to be the best one. Can anyone suggest one that
>you find it very useful, has real life tips, best practices etc?
>Thank a lot!sql

BEST SQL Interview Questions Site

Hi,
Can anyone tell me the BEST interview Questions site for SQL 2000 and for SQL 2005, BOTH versions.

The following URL is very nice one, you can download as PDF.

http://blog.sqlauthority.com/2007/04/21/sql-server-interview-questions-and-answers-complete-list-download/

|||I need a site which is having practical examples with solution. and questioons.
I already having this site SQLAuthority ..|||

To be honest, i'm always a little reluctant to use websites for this sort of thing. If the candidate is smart, they will have checked up on the web and may already have seen them.

Personally, i like to ask questions from real life situations i've been involved with, so maybe give some thought to that. And lets not forget, these forums are a great resource for ideas for questions as (hopefully) they'll have solutions attached and they are nicely categorised.

Of course, I don't know your circumstances and you may be short of time etc, but just thought i'd throw that in to the mix.

Good luck!

Best SQL IDE

Anyone have any thoughts on the best IDE for SQL Server development? I'd
like object explorer, drag-and-drop, code formatting, code completion, code
analysis, execution, query builder, the WORKS! I've tried RapidSQL, having
gotten excited by their online demo, but I have not found it well-behaved.
It completely crashed no less than 5 times in as many minutes. D'oh!
TIA!
RobertPersonally, I like Management Studio (SQL Server 2005) with PromptSQL (which
gives you IntelliSense in SSMS, Query Analyzer, etc). I'm pretty religious
about code formatting, and I have no need for a "query builder" (especially
those with bugs or really nasty side effects).
"Best" is going to be pretty subjective, since everyone has their own
opinions, criteria, etc. Like what is the best car, I can't even count how
many variables go into that, and why you get as many answers as there are
people.
A
"Robert Davis" <radbase@.yahoo.com> wrote in message
news:eP5T2VIwFHA.724@.TK2MSFTNGP14.phx.gbl...
> Anyone have any thoughts on the best IDE for SQL Server development? I'd
> like object explorer, drag-and-drop, code formatting, code completion,
> code analysis, execution, query builder, the WORKS! I've tried RapidSQL,
> having gotten excited by their online demo, but I have not found it
> well-behaved. It completely crashed no less than 5 times in as many
> minutes. D'oh!
> TIA!
> Robert
>|||I havn't had time to try this out myself, but there is a 3rd party tool for
enabled T-SQL intellisense style code completion. It claims to work with
Query Analyzer, VS 2003 and 2005 Enterprise Manager.
http://www.promptsql.com/
I don't know about formatting. I have my own style of structured indenting
and comes so natural I don't even think about it when typing.
"Robert Davis" <radbase@.yahoo.com> wrote in message
news:eP5T2VIwFHA.724@.TK2MSFTNGP14.phx.gbl...
> Anyone have any thoughts on the best IDE for SQL Server development? I'd
> like object explorer, drag-and-drop, code formatting, code completion,
> code analysis, execution, query builder, the WORKS! I've tried RapidSQL,
> having gotten excited by their online demo, but I have not found it
> well-behaved. It completely crashed no less than 5 times in as many
> minutes. D'oh!
> TIA!
> Robert
>|||Hi
You may want to look through the Readers choices in the September SQL Server
Magazine
http://www.windowsitpro.com/SQLServ.../786/Index.html
John
"Robert Davis" <radbase@.yahoo.com> wrote in message
news:eP5T2VIwFHA.724@.TK2MSFTNGP14.phx.gbl...
> Anyone have any thoughts on the best IDE for SQL Server development? I'd
> like object explorer, drag-and-drop, code formatting, code completion,
> code analysis, execution, query builder, the WORKS! I've tried RapidSQL,
> having gotten excited by their online demo, but I have not found it
> well-behaved. It completely crashed no less than 5 times in as many
> minutes. D'oh!
> TIA!
> Robert
>|||I've had good success with ApexSQLEdit - has intellsense, execution
plans [not as graphic as SQL Q/A - i.e., in a flowchart view, but not
with icons for s

s, scans, etc., but has the text to explain it], and
integration with source control.
www.apexsql.com
They have other good tools as well.
Robert Davis wrote:

>Anyone have any thoughts on the best IDE for SQL Server development? I'd
>like object explorer, drag-and-drop, code formatting, code completion, code
>analysis, execution, query builder, the WORKS! I've tried RapidSQL, having
>gotten excited by their online demo, but I have not found it well-behaved.
>It completely crashed no less than 5 times in as many minutes. D'oh!
>TIA!
>Robert
>
>|||Here is another query builder to keep an eye on:
http://151.100.3.84/technicalpreview/
Pamela

Best SQL DBA and BI magazines to order

Hi...I would like to order some magazines about SQL DBA and BI. would
anybody suggest some good options?
Thanks.SQL Magazine would be my first choice. http://www.sqlmag.com
Andrew J. Kelly SQL MVP
Solid Quality Mentors
<yvette.ye@.gmail.com> wrote in message
news:48ba86d8-e399-40d0-bfa6-f1fa41e404a1@.y21g2000hsf.googlegroups.com...
> Hi...I would like to order some magazines about SQL DBA and BI. would
> anybody suggest some good options?
> Thanks.|||On Apr 22, 6:27=A0pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> SQLMagazinewould be my first choice. =A0http://www.sqlmag.com
> --
> Andrew J. Kelly =A0 =A0SQL MVP
> Solid Quality Mentors
> <yvette...@.gmail.com> wrote in message
> news:48ba86d8-e399-40d0-bfa6-f1fa41e404a1@.y21g2000hsf.googlegroups.com...
>
> > Hi...I would like to order some magazines about SQL DBA and BI. would
> > anybody suggest some good options?
> > Thanks.- Hide quoted text -
> - Show quoted text -
Any other options, other than SQLMag? Is there any magazine for BI
(Business Intelligence)?|||SQL Mag does have BI content as well but I don't know of any BI specific
magazines related to SQL Server off hand.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
<yvette.ye@.gmail.com> wrote in message
news:dfa0ad5f-a16f-4eae-88d6-3197c8933efa@.x35g2000hsb.googlegroups.com...
On Apr 22, 6:27 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> SQLMagazinewould be my first choice. http://www.sqlmag.com
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
> <yvette...@.gmail.com> wrote in message
> news:48ba86d8-e399-40d0-bfa6-f1fa41e404a1@.y21g2000hsf.googlegroups.com...
>
> > Hi...I would like to order some magazines about SQL DBA and BI. would
> > anybody suggest some good options?
> > Thanks.- Hide quoted text -
> - Show quoted text -
Any other options, other than SQLMag? Is there any magazine for BI
(Business Intelligence)?

best solutions : sql server replication (Maintenance)

we are frequently running the maintenance tasks on production during that
databases are not accessible.
to avoid this we want to switch over to the standby server during maintenace
and after maintenance has been completed
switch back to primary server.During maintenance all data changes need to be
replicated back to production server.
To accomplish this I am planning to setup database replication from
producation database server to Standby server.
Please advise best solutions for this like microsoft trnsaction
replication,Goldengate replication, wansync replication.
I think microsoft trnsaction replication,Goldengate replication doesn't
support complex shema changes to get replicated.
Requirement :
1. 10 databases need to get replicated (with 30GB - 70GB each)
2. these 10 databases will have schema changes monthly.
3. data changes need to moved from standby to production server after
maintenance has been completed.
Please advise
ThanksI don't think that replication in the answer to your challenge.
What you probably want is a hot standby. Replication is not recommended
since you will have frequently schema changes.
I think you should investigate these solutions:
- (Transaction) Log shipping
- Cross linked San solution
- Clustering
Michel Gosen (MCDBA)
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||Use bi-directional transactional replication for this. You will need to stop
the distribution agents while running your optimizations.
--
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
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||DKRReddy wrote:
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during maintenace
> and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to be
> replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
What sort of maintenance tasks are you performing?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
First I would want to know why your databases aren't available during the
maintenance periods.
Almost all maintenance steps can be done with the databases on-line.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||During reindex, tables will be locked exclusively.This is main concern.
We have very huge tables , reindex will take hours.
SQL Server version is SQL Server 2000 SP4 on Windows 2003.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uF8o$RTMHHA.536@.TK2MSFTNGP02.phx.gbl...
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
>> we are frequently running the maintenance tasks on production during that
>> databases are not accessible.
>> to avoid this we want to switch over to the standby server during
>> maintenace and after maintenance has been completed
>> switch back to primary server.During maintenance all data changes need to
>> be replicated back to production server.
> First I would want to know why your databases aren't available during the
> maintenance periods.
> Almost all maintenance steps can be done with the databases on-line.
>
>> To accomplish this I am planning to setup database replication from
>> producation database server to Standby server.
>> Please advise best solutions for this like microsoft trnsaction
>> replication,Goldengate replication, wansync replication.
>> I think microsoft trnsaction replication,Goldengate replication doesn't
>> support complex shema changes to get replicated.
>> Requirement :
>> 1. 10 databases need to get replicated (with 30GB - 70GB each)
>> 2. these 10 databases will have schema changes monthly.
>> 3. data changes need to moved from standby to production server after
>> maintenance has been completed.
>> Please advise
>> Thanks
>|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:edrmFpaMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Hmm, have you tried using DBCC INDEXDEFRAG. I find for most cases this is
more than suffecient and it's an online operation which means you can avoid
the whole switchover, etc.|||DKRReddy wrote:
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Are you just blindly rebuilding all indexes, or are you analyzing them
first to determine which ones actually "need" to be rebuilt? Rebuilding
an index that is minimally fragmented gains you nothing. You should be
checking the degree of fragmentation of each index, and rebuilding only
those that need it. Here's a script to get you started:
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
Tracy McKibben
MCDBA
http://www.realsqlguy.com

best solutions : sql server replication (Maintenance)

we are frequently running the maintenance tasks on production during that
databases are not accessible.
to avoid this we want to switch over to the standby server during maintenace
and after maintenance has been completed
switch back to primary server.During maintenance all data changes need to be
replicated back to production server.
To accomplish this I am planning to setup database replication from
producation database server to Standby server.
Please advise best solutions for this like microsoft trnsaction
replication,Goldengate replication, wansync replication.
I think microsoft trnsaction replication,Goldengate replication doesn't
support complex shema changes to get replicated.
Requirement :
1. 10 databases need to get replicated (with 30GB - 70GB each)
2. these 10 databases will have schema changes monthly.
3. data changes need to moved from standby to production server after
maintenance has been completed.
Please advise
Thanks
Use bi-directional transactional replication for this. You will need to stop
the distribution agents while running your optimizations.
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
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
|||DKRReddy wrote:
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during maintenace
> and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to be
> replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
What sort of maintenance tasks are you performing?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
First I would want to know why your databases aren't available during the
maintenance periods.
Almost all maintenance steps can be done with the databases on-line.

> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
|||During reindex, tables will be locked exclusively.This is main concern.
We have very huge tables , reindex will take hours.
SQL Server version is SQL Server 2000 SP4 on Windows 2003.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uF8o$RTMHHA.536@.TK2MSFTNGP02.phx.gbl...
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> First I would want to know why your databases aren't available during the
> maintenance periods.
> Almost all maintenance steps can be done with the databases on-line.
>
>
|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:edrmFpaMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Hmm, have you tried using DBCC INDEXDEFRAG. I find for most cases this is
more than suffecient and it's an online operation which means you can avoid
the whole switchover, etc.
|||DKRReddy wrote:
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Are you just blindly rebuilding all indexes, or are you analyzing them
first to determine which ones actually "need" to be rebuilt? Rebuilding
an index that is minimally fragmented gains you nothing. You should be
checking the degree of fragmentation of each index, and rebuilding only
those that need it. Here's a script to get you started:
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
Tracy McKibben
MCDBA
http://www.realsqlguy.com
sql

best solutions : sql server replication (Maintenance)

we are frequently running the maintenance tasks on production during that
databases are not accessible.
to avoid this we want to switch over to the standby server during maintenace
and after maintenance has been completed
switch back to primary server.During maintenance all data changes need to be
replicated back to production server.
To accomplish this I am planning to setup database replication from
producation database server to Standby server.
Please advise best solutions for this like microsoft trnsaction
replication,Goldengate replication, wansync replication.
I think microsoft trnsaction replication,Goldengate replication doesn't
support complex shema changes to get replicated.
Requirement :
1. 10 databases need to get replicated (with 30GB - 70GB each)
2. these 10 databases will have schema changes monthly.
3. data changes need to moved from standby to production server after
maintenance has been completed.
Please advise
ThanksI don't think that replication in the answer to your challenge.
What you probably want is a hot standby. Replication is not recommended
since you will have frequently schema changes.
I think you should investigate these solutions:
- (Transaction) Log shipping
- Cross linked San solution
- Clustering
Michel Gosen (MCDBA)
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||Use bi-directional transactional replication for this. You will need to stop
the distribution agents while running your optimizations.
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
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||DKRReddy wrote:
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during maintena
ce
> and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
be
> replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
What sort of maintenance tasks are you performing?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
First I would want to know why your databases aren't available during the
maintenance periods.
Almost all maintenance steps can be done with the databases on-line.

> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||During reindex, tables will be locked exclusively.This is main concern.
We have very huge tables , reindex will take hours.
SQL Server version is SQL Server 2000 SP4 on Windows 2003.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uF8o$RTMHHA.536@.TK2MSFTNGP02.phx.gbl...
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> First I would want to know why your databases aren't available during the
> maintenance periods.
> Almost all maintenance steps can be done with the databases on-line.
>
>|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:edrmFpaMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Hmm, have you tried using DBCC INDEXDEFRAG. I find for most cases this is
more than suffecient and it's an online operation which means you can avoid
the whole switchover, etc.|||DKRReddy wrote:
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Are you just blindly rebuilding all indexes, or are you analyzing them
first to determine which ones actually "need" to be rebuilt? Rebuilding
an index that is minimally fragmented gains you nothing. You should be
checking the degree of fragmentation of each index, and rebuilding only
those that need it. Here's a script to get you started:
http://realsqlguy.com/serendipity/a...realsqlguy.com

Best solution: SQLXML, XML Data Binding, or MSXML?

I need some help to determine what the best solution would be for the
following scenarios where the dataset is large and performance is key:
Scenario #1
I am trying to retrieve relational data in XML from a SQL Server 2000 stored
proc. My client is a VBA Access application. I want to be able to map the xml
elements to db tables/columns and use a stored proc to collect the data and
build the xml. Validate it against the xsd. I am assuming that with large
datasets it would be faster to create the xml on the server than on the
client - desktop pcs - since I would have to loop through data rows to
create the xml using MSXML on the client, thoughts. What about annotated
schemas. Do you have to use along with XPath queries even if your result is
already filtered in an SQL sproc.
Scenario #2
I have a .Net webservice that will be the recepient of this xml and validate
it and persisit it to SQL 2K. After looking at all available options it seems
to me that using XML Data Binding & creating .Net classes based on the xsd &
loading this .Net object from xml is the cleanest solution. Reading into
SQLXML, it seems as though every method to persist data to SQL Server uses
the COM-based SQLXMLOLEDB provider and my code will have to jump from CLR to
COM. Cant see how that is good. And MSXML is also COM based parser.
Thoughts?
Scenario #1: I would look at the FOR XML functionality of SQL Server 2000 if
you want to do it in the database. Annotated schemas do not work with stored
procs. You can look into the client-side FOR XML of SQLXML 3.0 if you want
to run FOR XML over the result of a stored proc.
Re Scenario #2: The SQLXML Bulkload object can be called through the managed
providers (in the latest SP of SQLXML 3.0). You will go through COM interop,
which you don't have to for the data binding. But I think the loading may
still be faster due to the bulkload mechanism. I would suggest doing some
perf tests for your specific data...
Best regards
Michael
"Aazihh" <nowayjose@.newsgroupms.com> wrote in message
news:D56903DB-3984-4EE4-9CE1-5E1CCC9FBEA6@.microsoft.com...
>I need some help to determine what the best solution would be for the
> following scenarios where the dataset is large and performance is key:
> Scenario #1
> I am trying to retrieve relational data in XML from a SQL Server 2000
> stored
> proc. My client is a VBA Access application. I want to be able to map the
> xml
> elements to db tables/columns and use a stored proc to collect the data
> and
> build the xml. Validate it against the xsd. I am assuming that with large
> datasets it would be faster to create the xml on the server than on the
> client - desktop pcs - since I would have to loop through data rows to
> create the xml using MSXML on the client, thoughts. What about annotated
> schemas. Do you have to use along with XPath queries even if your result
> is
> already filtered in an SQL sproc.
> Scenario #2
> I have a .Net webservice that will be the recepient of this xml and
> validate
> it and persisit it to SQL 2K. After looking at all available options it
> seems
> to me that using XML Data Binding & creating .Net classes based on the xsd
> &
> loading this .Net object from xml is the cleanest solution. Reading into
> SQLXML, it seems as though every method to persist data to SQL Server uses
> the COM-based SQLXMLOLEDB provider and my code will have to jump from CLR
> to
> COM. Cant see how that is good. And MSXML is also COM based parser.
> Thoughts?

Best solution: SQLXML, XML Data Binding, or MSXML?

I need some help to determine what the best solution would be for the
following scenarios where the dataset is large and performance is key:
Scenario #1
I am trying to retrieve relational data in XML from a SQL Server 2000 stored
proc. My client is a VBA Access application. I want to be able to map the xm
l
elements to db tables/columns and use a stored proc to collect the data and
build the xml. Validate it against the xsd. I am assuming that with large
datasets it would be faster to create the xml on the server than on the
client - desktop pcs - since I would have to loop through data rows to
create the xml using MSXML on the client, thoughts. What about annotated
schemas. Do you have to use along with XPath queries even if your result is
already filtered in an SQL sproc.
Scenario #2
I have a .Net webservice that will be the recepient of this xml and validate
it and persisit it to SQL 2K. After looking at all available options it seem
s
to me that using XML Data Binding & creating .Net classes based on the xsd &
loading this .Net object from xml is the cleanest solution. Reading into
SQLXML, it seems as though every method to persist data to SQL Server uses
the COM-based SQLXMLOLEDB provider and my code will have to jump from CLR to
COM. Cant see how that is good. And MSXML is also COM based parser.
Thoughts?Scenario #1: I would look at the FOR XML functionality of SQL Server 2000 if
you want to do it in the database. Annotated schemas do not work with stored
procs. You can look into the client-side FOR XML of SQLXML 3.0 if you want
to run FOR XML over the result of a stored proc.
Re Scenario #2: The SQLXML Bulkload object can be called through the managed
providers (in the latest SP of SQLXML 3.0). You will go through COM interop,
which you don't have to for the data binding. But I think the loading may
still be faster due to the bulkload mechanism. I would suggest doing some
perf tests for your specific data...
Best regards
Michael
"Aazihh" <nowayjose@.newsgroupms.com> wrote in message
news:D56903DB-3984-4EE4-9CE1-5E1CCC9FBEA6@.microsoft.com...
>I need some help to determine what the best solution would be for the
> following scenarios where the dataset is large and performance is key:
> Scenario #1
> I am trying to retrieve relational data in XML from a SQL Server 2000
> stored
> proc. My client is a VBA Access application. I want to be able to map the
> xml
> elements to db tables/columns and use a stored proc to collect the data
> and
> build the xml. Validate it against the xsd. I am assuming that with large
> datasets it would be faster to create the xml on the server than on the
> client - desktop pcs - since I would have to loop through data rows to
> create the xml using MSXML on the client, thoughts. What about annotated
> schemas. Do you have to use along with XPath queries even if your result
> is
> already filtered in an SQL sproc.
> Scenario #2
> I have a .Net webservice that will be the recepient of this xml and
> validate
> it and persisit it to SQL 2K. After looking at all available options it
> seems
> to me that using XML Data Binding & creating .Net classes based on the xsd
> &
> loading this .Net object from xml is the cleanest solution. Reading into
> SQLXML, it seems as though every method to persist data to SQL Server uses
> the COM-based SQLXMLOLEDB provider and my code will have to jump from CLR
> to
> COM. Cant see how that is good. And MSXML is also COM based parser.
> Thoughts?

Best solution, iterate over millions records and call extended sp

Hi,
I need to iterate over millions rows in a table and I need call an extended
stored procedure (written in C++ and not possible be written in TSQL) using
the columns of each row as parameters and write the return values to an new
table. The current script open a cursor.
What's the best way to implement it? BCP to a text file and external program
parse and write the text file and BCP back? sp_cmdshell an executible for
each row? (so needn't worry about C++ memory leak issu). XML?....
Thanks,"nick" <nick@.discussions.microsoft.com> wrote in message
news:75FAE6C1-EEC1-4D8B-A3A2-073F10CD859E@.microsoft.com...
> Hi,
> I need to iterate over millions rows in a table and I need call an
> extended
> stored procedure (written in C++ and not possible be written in TSQL)
> using
> the columns of each row as parameters and write the return values to an
> new
> table. The current script open a cursor.
> What's the best way to implement it? BCP to a text file and external
> program
> parse and write the text file and BCP back? sp_cmdshell an executible for
> each row? (so needn't worry about C++ memory leak issu). XML?....
> Thanks,
I'd say the "best" solution would be to rethink your architecture and
implement it differently if you plan to do this on a regular basis. This
doesn't sound like a very scalable or desirable way to use a client-server
database. Have you considered using SQL Server 2005, where you can implement
.NET code in the database? Or implementing your code in ADO rather than wit
h
an XP?
If it's just a one-off requirement then you just need to test which approach
works best for you. It isn't really a SQL question since, for the purposes
of this exercise, you are just using SQL Server as a file dump rather than
what it was designed for. Why not just loop in TSQL and call the proc for
each row?
David Portas
SQL Server MVP
--|||If you have a situation that calls for looping through a cursor, then it's
better to implement the cursor on the client side than on the server. Open a
read-only, forward only ADO recordset and Command.Execute the stored
procedure for each row.
"nick" <nick@.discussions.microsoft.com> wrote in message
news:75FAE6C1-EEC1-4D8B-A3A2-073F10CD859E@.microsoft.com...
> Hi,
> I need to iterate over millions rows in a table and I need call an
> extended
> stored procedure (written in C++ and not possible be written in TSQL)
> using
> the columns of each row as parameters and write the return values to an
> new
> table. The current script open a cursor.
> What's the best way to implement it? BCP to a text file and external
> program
> parse and write the text file and BCP back? sp_cmdshell an executible for
> each row? (so needn't worry about C++ memory leak issu). XML?....
> Thanks,sql

Best solution, iterate over millions records and call extended

In fact, the functionarity needs to be available on the server. So I will
created a program, maybe C# or C++ program to do the looping and calculate
and put the executible on the server so it can be launched via xp_cmdshell..
.
It should be better than big TSQL cursor?
"JT" wrote:

> If you have a situation that calls for looping through a cursor, then it's
> better to implement the cursor on the client side than on the server. Open
a
> read-only, forward only ADO recordset and Command.Execute the stored
> procedure for each row.
> "nick" <nick@.discussions.microsoft.com> wrote in message
> news:75FAE6C1-EEC1-4D8B-A3A2-073F10CD859E@.microsoft.com...
>
>In fact, my question is
Transact-SQL script "declare cursor" (fast forward) vs. Client side ADO code
with fast forward server cursor
Which one is better for very large rows?
"nick" wrote:
> In fact, the functionarity needs to be available on the server. So I will
> created a program, maybe C# or C++ program to do the looping and calculate
> and put the executible on the server so it can be launched via xp_cmdshell
..
> It should be better than big TSQL cursor?
> "JT" wrote:
>

Best solution for this "problem"

Alright, me and mine are planning of creating a big community, ( no futher details possible )
The point is that, we got 1 webserver with php who's reading out a huge database.
That database is beeing updated by some of a 100 users, all these users are updating the db at the same time. so as you can see, what we need is a good and reliable database witch can easily hold up this lot of data.

Wich database type do you guys recommend?

Need to know more before recommending anything, but if you are thinking about SQL Server check this link

http://www.microsoft.com/sql/editions/default.mspx

All the info about various editions of SQL Server and what they can and can not support.

Best solution

Let say I have a table that is composed of 11 columns - one the Primary Key and the other are keys to rows in another table. Of these 10 column 2-10 are nullable. Can I get all the info in one SELECT? I can't use JOINS because columns 1-10 are keys to the same table. I am not very good at explaining these things but hopefully it makes sense.

ThanksNope, it's not making sense :-) Can you provide a small example with data to illustrate what you are trying to do?

Terri|||If I understand correctly, can you do several UNIONs and get them in turn?

Table1 join table 2 on col2 UNION
Table1 join table 2 on col3 UNION
Table1 join table 2 on col4 UNION
etc.

What it sounds like is that you should have a third table that contains a record for each possible combination of keys between table1 and table2. It sounds correcting the database structure is the best bet if you are able to do that.|||okay let me try :)

Let say I have a row that consist of the following:

TABLE 1:
key|ele1|ele2|ele3|ele4|ele5
1 6 2 5 null null

key column contains the rowID

ele1 - ele5 columns contain row IDs from the same table. ele1 is not nullable but the rest is nullable. I think if I use JOINS I will get an "ambigious error."

Table 2 ( ele ):
key|name |value
1 | "first" | 1
2 | "second" | 2
3 | "third" | 3 and so on.|||You should be able to accomplish what you need using JOINs with aliases.


SELECT
 table1.key,
 table2key.name,
 table2key.value,
 table1.ele1,
 table2ele1.name,
 table2ele1.value,
 table1.ele2,
 table2ele2.name,
 table2ele2.value,
 table1.ele3,
 table2ele3.name,
 table2ele3.value,
 table1.ele4,
 table2ele4.name,
 table2ele4.value,
 table1.ele5,
 table2ele5.name,
 table2ele5.value
FROM
 table1
LEFT OUTER JOIN
 table2 AS table2key ON table1.key = table2key.key
LEFT OUTER JOIN
 table2 AS table2ele1 ON table1.ele1 = table2ele1.key
LEFT OUTER JOIN
 table2 AS table2ele2 ON table1.ele2 = table2ele2.key
LEFT OUTER JOIN
 table2 AS table2ele3 ON table1.ele3 = table2ele3.key
LEFT OUTER JOIN
 table2 AS table2ele4 ON table1.ele4 = table2ele4.key
LEFT OUTER JOIN
 table2 AS table2ele5 ON table1.ele5 = table2ele5.key

Terri|||Thanks so much for all your help Terri!

Best Security Template for a Member Server level SQL server install

This does not seem like rocket science but I can't find a recommendation
from MS on which uplevel (from defaults) Security Template (file server,
infrastructure server, etc.) is the best baseline starting point for a SQL
install on a W2K3 AD. I realize that mods will be necessary no matter but
since there are 700+ settings having a head start seems like a good thing. I
have created the OU specifically for the SQL server and just need to
configure it's GP including IPSec. I have implemented most of the suggested
Security settings to the install of SQL itself (see prior post).
Any suggestions?
ThanksThe Enterprise - Member Server Template is the template that should be
used. Also, generate a rollback template to help you with your testing.
The High Security Template should not be used.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Thursday, March 29, 2012

Tuesday, March 27, 2012

Blog Archive

About Me