Mirroring and logins

Hi,
I am maintaining a mirroring set up that I did not create. I have two
boxes with the same logins and 15-20 mirrored databases. My issue is
that I know some of the sids for SQL logins are different between the
boxes so that when we fail over in a crisis, some of the apps are not
going to be able to connect because of the ophaned login process. Is
there any way that I can run an automated process/script to find out
in advance what sids don't match. We have over 100 SQL logins that
exist on each server but may be mis-matched. I don't want to mirror
over and run the EXEC sp_change_users_login for each user on each
database.
Thanks in advance...
Kristina
> over and run the EXEC sp_change_users_login for each user on each
> database.
If you have SP2 you ca use ALTER USER username WITH LOGIN ='login'
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:d00cdbcb-7985-4c4b-b3fe-b8ef64d8eeb4@.m3g2000hsc.googlegroups.com...
> Hi,
> I am maintaining a mirroring set up that I did not create. I have two
> boxes with the same logins and 15-20 mirrored databases. My issue is
> that I know some of the sids for SQL logins are different between the
> boxes so that when we fail over in a crisis, some of the apps are not
> going to be able to connect because of the ophaned login process. Is
> there any way that I can run an automated process/script to find out
> in advance what sids don't match. We have over 100 SQL logins that
> exist on each server but may be mis-matched. I don't want to mirror
> over and run the EXEC sp_change_users_login for each user on each
> database.
> Thanks in advance...
|||On Apr 16, 10:30Xam, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Kristina> over and run the EXEC sp_change_users_login for each user Xon each
> If you have SP2 you ca use ALTER USER username WITH LOGIN ='login'
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:d00cdbcb-7985-4c4b-b3fe-b8ef64d8eeb4@.m3g2000hsc.googlegroups.com...
>
>
>
> - Show quoted text -
I don't think that will do the trick. On a mirrored instance the
databases are in a restoring mode so we can't do that. The databases
are not accessible.
|||Perhaps you can create a snapshot of your mirrored database? That should get you to
sys.database_principals (in the snapshot database) which you can check against
sys.server_principals.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:d00cdbcb-7985-4c4b-b3fe-b8ef64d8eeb4@.m3g2000hsc.googlegroups.com...
> Hi,
> I am maintaining a mirroring set up that I did not create. I have two
> boxes with the same logins and 15-20 mirrored databases. My issue is
> that I know some of the sids for SQL logins are different between the
> boxes so that when we fail over in a crisis, some of the apps are not
> going to be able to connect because of the ophaned login process. Is
> there any way that I can run an automated process/script to find out
> in advance what sids don't match. We have over 100 SQL logins that
> exist on each server but may be mis-matched. I don't want to mirror
> over and run the EXEC sp_change_users_login for each user on each
> database.
> Thanks in advance...
|||On Apr 16, 10:26Xpm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Perhaps you can create a snapshot of your mirrored database? That should get you to
> sys.database_principals (in the snapshot database) which you can check against
> sys.server_principals.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:d00cdbcb-7985-4c4b-b3fe-b8ef64d8eeb4@.m3g2000hsc.googlegroups.com...
>
>
>
> - Show quoted text -
GREAT IDEA!!! Thanks! Now I know how to solve my problelm. I don't
know why I didn't think of this myself.

Mirroring and logins

Hi,
I am maintaining a mirroring set up that I did not create. I have two
boxes with the same logins and 15-20 mirrored databases. My issue is
that I know some of the sids for SQL logins are different between the
boxes so that when we fail over in a crisis, some of the apps are not
going to be able to connect because of the ophaned login process. Is
there any way that I can run an automated process/script to find out
in advance what sids don't match. We have over 100 SQL logins that
exist on each server but may be mis-matched. I don't want to mirror
over and run the EXEC sp_change_users_login for each user on each
database.
Thanks in advance...Kristina
> over and run the EXEC sp_change_users_login for each user on each
> database.
If you have SP2 you ca use ALTER USER username WITH LOGIN ='login'
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:d00cdbcb-7985-4c4b-b3fe-b8ef64d8eeb4@.m3g2000hsc.googlegroups.com...
> Hi,
> I am maintaining a mirroring set up that I did not create. I have two
> boxes with the same logins and 15-20 mirrored databases. My issue is
> that I know some of the sids for SQL logins are different between the
> boxes so that when we fail over in a crisis, some of the apps are not
> going to be able to connect because of the ophaned login process. Is
> there any way that I can run an automated process/script to find out
> in advance what sids don't match. We have over 100 SQL logins that
> exist on each server but may be mis-matched. I don't want to mirror
> over and run the EXEC sp_change_users_login for each user on each
> database.
> Thanks in advance...|||On Apr 16, 10:30=A0am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Kristina> over and run the EXEC sp_change_users_login for each user =A0on =each
> > database.
> If you have SP2 you ca use ALTER USER username WITH LOGIN =3D'login'
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:d00cdbcb-7985-4c4b-b3fe-b8ef64d8eeb4@.m3g2000hsc.googlegroups.com...
>
> > Hi,
> > I am maintaining a mirroring set up that I did not create. I have two
> > boxes with the same logins and 15-20 mirrored databases. My issue is
> > that I know some of the sids for SQL logins are different between the
> > boxes so that when we fail over in a crisis, some of the apps are not
> > going to be able to connect because of the ophaned login process. Is
> > there any way that I can run an automated process/script to find out
> > in advance what sids don't match. We have over 100 SQL logins that
> > exist on each server but may be mis-matched. I don't want to mirror
> > over and run the EXEC sp_change_users_login for each user =A0on each
> > database.
> > Thanks in advance...- Hide quoted text -
> - Show quoted text -
I don't think that will do the trick. On a mirrored instance the
databases are in a restoring mode so we can't do that. The databases
are not accessible.|||Perhaps you can create a snapshot of your mirrored database? That should get you to
sys.database_principals (in the snapshot database) which you can check against
sys.server_principals.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:d00cdbcb-7985-4c4b-b3fe-b8ef64d8eeb4@.m3g2000hsc.googlegroups.com...
> Hi,
> I am maintaining a mirroring set up that I did not create. I have two
> boxes with the same logins and 15-20 mirrored databases. My issue is
> that I know some of the sids for SQL logins are different between the
> boxes so that when we fail over in a crisis, some of the apps are not
> going to be able to connect because of the ophaned login process. Is
> there any way that I can run an automated process/script to find out
> in advance what sids don't match. We have over 100 SQL logins that
> exist on each server but may be mis-matched. I don't want to mirror
> over and run the EXEC sp_change_users_login for each user on each
> database.
> Thanks in advance...|||On Apr 16, 10:26=A0pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Perhaps you can create a snapshot of your mirrored database? That should g=et you to
> sys.database_principals (in the snapshot database) which you can check aga=inst
> sys.server_principals.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph=
ttp://sqlblog.com/blogs/tibor_karaszi
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:d00cdbcb-7985-4c4b-b3fe-b8ef64d8eeb4@.m3g2000hsc.googlegroups.com...
>
> > Hi,
> > I am maintaining a mirroring set up that I did not create. I have two
> > boxes with the same logins and 15-20 mirrored databases. My issue is
> > that I know some of the sids for SQL logins are different between the
> > boxes so that when we fail over in a crisis, some of the apps are not
> > going to be able to connect because of the ophaned login process. Is
> > there any way that I can run an automated process/script to find out
> > in advance what sids don't match. We have over 100 SQL logins that
> > exist on each server but may be mis-matched. I don't want to mirror
> > over and run the EXEC sp_change_users_login for each user =A0on each
> > database.
> > Thanks in advance...- Hide quoted text -
> - Show quoted text -
GREAT IDEA!!! Thanks! Now I know how to solve my problelm. I don't
know why I didn't think of this myself.

mirroring

hello

i used following query to create endpoint

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

AS TCP (

LISTENER_PORT=5024

, LISTENER_IP = ALL

)

FOR DATABASE_MIRRORING (

AUTHENTICATION = CERTIFICATE HOST_A_cert

, ENCRYPTION = REQUIRED ALGORITHM RC4

, ROLE = ALL

);

GO

i got the following error msg:

Msg 1498, Level 16, State 2, Line 11

Database mirroring is disabled by default. Database mirroring is currently provided for evaluation purposes only and is not to be used in production environments. To enable database mirroring for evaluation purposes, use trace flag 1400 during startup. For more information about trace flags and startup options, see SQL Server Books Online.

any help plzz

thanks in advance

Hello

Support for Database mirroring in SQL Server 2005 as a feature was officially availble in SQL Server 2005 Service Pack 1. Before Service Pack 1 in RTM build you could still use this feature but after Enabling Trace Flag 1400 DBCC TRACEON(1400,-1). I would suggest upgrading to SQL Server 2005 SP1 or SP2.

Hope this helps

Vishal Gandhi

|||

thanks vishal

i downloaded sp2 i check its created the endpoints properly, let me check whether mirroring will work proper or not.

sql

Mirror of master, msdb, tempdb, or model databases.

Hi Guys,

Since,You cannot mirror the master, msdb, tempdb, or model databases.What will happen if i create new login,change existing security profile and new jobs,change of existing job on princicpal db. how these will be mirrored to other server and in case failover, how it will treat.

Thaks

Mirroring works at the database level only. dts/ssis packages, jobs etc are not mirrored. Database users are mirrored but not their corresponding SQL Server login.

You need to keep a copy of dts/ssis packages, jobs etc on the mirror server and whatever changes you make to them on the principal server, you will also have to make on the mirror server if you want to keep the servers exactly in sync.

hope this helps

Mirror Database in SQL 2000

I am using MS SQL 2000 Standard, is it possible to create a mirror database in SQL 2000? If so, would you suggest how to do this. Thanks! I am a newbie about this.

Unfortunately mirroring is a SQL Server 2005 feature.

You could do log shipping in SQL Server 2000 but that is not exactly the same as mirroring.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

However, I am using SQL Server 2000 Std. which doesn't includes log shipping feature! Besides replication, any other features could do the function of mirroring?

|||

I'm afraid only an upgrade to SQL Server 2005 will give you the full power.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

minvalue and maxvalue in sequence

Hello everyone:

I try to create a sequence with following requirements:

create a sequence that will generate integers starting with the value 9. Each value should be three less than the previous value generated. The lowest possible balue the sequence should be allowed to generate is -1, and it should not be allowed to cycle.

I think values generated by this sequence are 9, 6, 3, 0

my sql statement goes like this:

SQL> create sequence MY_FIRST_SEQUENCE
2 increment by -3
3 start with 9
4 minvalue -1
5 nocycle;
create sequence MY_FIRST_SEQUENCE
*
ERROR at line 1:
ORA-04004: MINVALUE must be less than MAXVALUE

I don't know what's the maxvalue in this squence. Should it be 9 if that's the case. why do I get a error message?

Plase give me some suggestio on how can I make this sequence work. Thanks!

sjgrad03
12-03Even without knowing anything about sequences, if this is a DESCENDING sequence and it STARTS WITH 9, then it is logical that its MAXIMUM VALUE is 9.

Your statement will be executed properly if you add MAXVALUE n, where n >= START_WITH_value. If it is not clear enough, here's an example:

CREATE SEQUENCE my_seq
START WITH 9
MAXVALUE 9
MINVALUE -1
INCREMENT BY -3;

or

CREATE SEQUENCE my_seq
START WITH 9
MAXVALUE 1000
MINVALUE -1
INCREMENT BY -3;

NOCYCLE is the default and it is not necessary to specify it.

Read more about creating Oracle sequences here (http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6015.htm).

Minium Permissions necessary to create a login?

What are the minimum permissions necessary to use CREATE LOGIN?

I'm using db_accessAdmin and db_securityAdmin and db_owner. The user as access to the database.

It still says I can't create the login. What am I missing?

The answer is in Books Online:

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

"Requires ALTER ANY LOGIN permission on the server. If the CREDENTIAL option is used, also requires ALTER ANY CREDENTIAL permission on the server."

Creating a login requires permissions at the server level, it cannot be accomplished with database level permissions.

Thanks
Laurentiu

sql

Minium Permissions necessary to create a login?

What are the minimum permissions necessary to use CREATE LOGIN?

I'm using db_accessAdmin and db_securityAdmin and db_owner. The user as access to the database.

It still says I can't create the login. What am I missing?

The answer is in Books Online:

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

"Requires ALTER ANY LOGIN permission on the server. If the CREDENTIAL option is used, also requires ALTER ANY CREDENTIAL permission on the server."

Creating a login requires permissions at the server level, it cannot be accomplished with database level permissions.

Thanks
Laurentiu

Mining Structures

I am using the wizard to create a new mining structure and getting the error at the time of selecting the datamining technique like either microsoft decision tree or microsoft timeseries etc.,

"Unable to retrieve a list of supported data mining algorithms. Make sure you are connected to the correct Analysis Services server instance and the Analysis Services server named localhost is running and configured properly. You can continue with a default list of data mining algorithms."

What is that means?

Thanks in advance,

Got it. I need to start the AS from Control Panel/Administrative tools etc etc., before doning this.

I think the default is Manual to start the service.

|||Right-click on the Analysis Service project and select Properties - under Configuration Properties->Deployment, check what you have under Target->Server. The value of the Server property should be the name of the Analysis Services instance that you want the project to connect/deploy to. When you try to select a data mining technique, we try to connect to the Target Server to fetch the list of supported techniques. If you have "localhost" in this property, check Control Panel->Administrative Services->Services to confirm that the "SQL Server Analysis Services" service is running. If you specified a named instance (servername\instancename), check that the corresponding instance (SQL Server Analysis Services (instancename)" as well as the "SQL Server Browser" service is running.sql

Mining Model?

Hi,

I'm working on a project to create a mining model.I have one "flattened" table with a productID (key) and related attributes. Example columns are weight, color, price, units sold last year, product category, product rating (top seller, etc) and similar columns, about 40 in total. There are about 15,000 products and same number of rows in the table.

The objective is to pass a ProductID (that also exists in the table) and get back the top N products that most resemble the source product based on all the attributes.

Any advice on which models I should test and how to set up the models would be much appreciated. Also if there is a similar example/sample out there, please let me know. I downloaded the MovieClick sample, but that doesn't work in my case as I only have the equivalent of the Customers table.

Thanks in Advance.

This is actually a K-Nearest Neighbor problem, for which we don't ship an implementation for SQL Server Analysis Services (although someone may have written a plug in). I could imagine that you could use the fuzzy matching transform in Integration Services to do this, however. To do so you would just use your product table as the reference table, send in the fields of the product of interest, then sort the results by the match confidence.

HTH

-Jamie

|||

Thanks Jamie, I'll try fuzzy matching in SSIS. I'm also thinking of writing my own code to calculate based on KNN in TSQL. I'm not sure if the performance will be acceptable though especially as I need to pass in up to 5 products, evaluate based on their combined attributes and get a resultset back with top N matches.

Is there a third party software out there that can do this?

|||When using the fuzzy components in SSIS, do be aware that they are string matching components, evaluating the edit distance between two string. So, for example, "911" will be pretty much as close a match to "999" as "199" - so for any columns where you need to evaluate similarity of values you may need to use other components such as the Derived Column to perform the calculation.|||

Hi Donald,

Can you explain what you mean by Derived column. How would I make it work to calculate distances for numeric columns/attributes.

Thanks.

|||I don't know of a 3rd party KNN, but you would likely get better performance implementing your own algorithm using our plug-in interfaces in C#.|||

Jamie/Donald

Thanks for your responses. My C# is not that strong. For now I'll write something in SQL and if I can create a model that works, I'll convert that into a plug-in.

This is the plan. Normalize all attributes using mean and std dev. Apply the nearest neighbour algorithm using Euclidean distance. Also experiment with arbitrary weights for attributes or maybe use correlation to filter out some of the weaker attributes, not sure how that will work though at this point.

Any suggestions are welcome. Also, Is there a sample plug-in in C# out there I can use as template. Is there any way I can mold this table to use Association Rules or any other built-in algorithm.

Regards,

Asim.

|||

There are tutorials here for writing managed plug-in algorithms: http://www.sqlserverdatamining.com/DMCommunity/Tutorials/default.aspx

You may need to register (free!) to get access to the links.

mining model with time

i want create a model to predict what product i should Ads for customer at time.

example: in summer, i should show the , drinks (coca, pepsi) , ice food, bikini, sandals, glasses. in winter, i should show shoes, coats, hot food....

i have some table:

order(orderid, time,cusid...)

product(productid,..)

orderdetails(orderid, productid..)

customers(cuisid,....)

You can add the season as an input for the model, along with whatever other inputs you are interested in.

minimum price on earliest date

Ok my first posted question :
(This is related to a travel website)

I have the following table layout :

CREATE TABLE "public"."package" (
"id" BIGINT NOT NULL,
"accom_code" VARCHAR(4) NOT NULL,
"start_date" DATE NOT NULL,
"end_date" DATE NOT NULL,
"pricing_type" VARCHAR(2),
"indic_price" NUMERIC(7,2),
"unit_price" NUMERIC(7,2),
"adult_age_max_cnt" INTEGER,
CONSTRAINT "package_pkey" PRIMARY KEY("id")
) WITH OIDS;

The package table contains a list (a very large one) for holiday accomodation packages.

What i'm trying to get is the following :

The MINIMUM price using the following for "price" :

CAST (CASE p.pricing_type
WHEN 'UN' THEN p.unit_price
WHEN 'PA' THEN p.indic_price*p.adult_age_max_cnt
ELSE p.unit_price
END AS NUMERIC(7,2))

AND

The minimum date (i.e. nearest start date) greater than today
WHERE the start_date equals the minimum start_date and the price equals the minimum price.

Any thoughts?

With any luck I will be able to give you an test data insert for this.

This should probably be posted in the POSTGRESQL section however I feel that it is a more general SQL question than anything else.What I have thus far :

SELECT p.accom_code
,MIN(CAST (CASE p.pricing_type
WHEN 'UN' THEN p.unit_price
WHEN 'PA' THEN p.indic_price*p.adult_age_max_cnt
ELSE p.indic_price
END AS NUMERIC(7,2))) as min_price
,MIN(p.start_date) as min_start
FROM package p
WHERE accom_code IN ('DDNA','ADE9','CGHH','ASEC','BDB9','HGMD','CMEF', 'BGDE','YRB5','BJAM')
AND p.duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
GROUP BY p.accom_code

At the present time i'm getting 10 records (as expected). However I believe these records are WRONG as the minimum price doesn't necessarily match up with the minimum date for a particular (correct me i'm wrong here). How do I go about correcting this?|||Ooh think I nearly got it, can someone verify this :

SELECT p.accom_code
,x.min_start
,MIN(CAST (CASE p.pricing_type
WHEN 'UN' THEN p.unit_price
WHEN 'PA' THEN p.indic_price*p.adult_age_max_cnt
ELSE p.indic_price
END AS NUMERIC(7,2))) as min_price
FROM package p
JOIN (
SELECT p.accom_code,MIN(p.start_date) as min_start
FROM package p
WHERE accom_code IN ('DDNA','ADE9','CGHH','ASEC','BDB9','HGMD','CMEF', 'BGDE','YRB5','BJAM')
AND p.duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
GROUP BY p.accom_code
) x
ON p.accom_code = x.accom_code AND x.min_start = p.start_date
WHERE p.accom_code IN ('DDNA','ADE9','CGHH','ASEC','BDB9','HGMD','CMEF', 'BGDE','YRB5','BJAM')
AND p.duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
GROUP BY p.accom_code,x.min_start

In theory it should give the cheapest price on the earliest start date for each of the 10 accomodation types.

Edit : Just to finish it off I needed the start_date out of it as well ;) (added x.min_start to select and group clause)|||looks okay to me

i would not use the same alias "p" in more than one place in the query

and i would probably remove one of the following:AND p.duration = 7
AND p.duration = 7;)|||LOL oh yeah. I've cleaned it up in the above post now.
However I do have one question to finish it off (and this annoys me about of lot of SQL i've done in the past) :
Is there any way to remove the duplicate WHERE clauses in a query like this, and somehow apply it only once but for both sections? I hope that question makes sense.|||Is there any way to remove the duplicate WHERE clauses in a query like thisdefine a view|||Btw, your queries won't work at all in SQL because you used delimited schema/table/column names in the CREATE TABLE statement (i.e. double-quotes) but not in your query.|||That DDL was written in PMS for Postgres so it's a pure copy. I did mention that I was using Postgresql in my first post.

What I DID want to be standard was the SQL query I was doing. :D|||Well, then you have to use delimited schema/table/column names in your query - as I said.|||I have come across a problem with the query above. If two records calculate the same minimum price for one accom_code I can get two results when joined to itself.
i.e. min price and min date is £189 and 25/05/07
when self-joined total price = £567 / 3 adults = £189
and total price = £378 / 2 adults = £189

They both match the minimum price and date and thus both records are output. DOH!

As a quick hack (and I don't like this method) I did a distinct and ordered by total price minimum first.|||Is there any way to remove the duplicate WHERE clauses in a query like this, and somehow apply it only once but for both sections?
For this purpose, SQL99 defines "common table expressions", i.e., a "WITH" subclause of a select statement. Not yet available in the current version of PostgreSQL but coming soon (8.4 probably) ;)
(DB2, Oracle and SQLServer already have them in place.)
Your query, with CTEs, would become:WITH p AS
( SELECT accom_code, start_date,
MIN(CAST (CASE pricing_type
WHEN 'UN' THEN unit_price
WHEN 'PA' THEN indic_price*adult_age_max_cnt
ELSE indic_price
END AS NUMERIC(7,2))) as min_price
FROM package
WHERE accom_code IN ('DDNA','ADE9','CGHH','ASEC',
'BDB9','HGMD','CMEF','BGDE','YRB5','BJAM')
AND duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
),
x AS
( SELECT p.accom_code,MIN(p.start_date) as min_start
FROM p
GROUP BY p.accom_code
)
SELECT p.accom_code,x.min_start,p.min_price
FROM p INNER JOIN x
ON p.accom_code = x.accom_code AND x.min_start = p.start_date
GROUP BY p.accom_code,x.min_start
These are effectively "local view definitions", cf suggestion by r937.
(Note that in the above query, the SELECT .. p.min_price is not compatible with the GROUP BY -- maybe add p.min_price to the GROUP BY? Or replace it by "MIN(p.min_price)"?)sql

Minimum components required to create 2005 database/tables programmatically

I want to know what are the minimum SQL Server 2005 tools required to perform the following task, and how do I install them:

1. I have a server with SQLS 2005 (and all of its management components) installed. Let's call this Server A. I want to create a database and tables in an instance on this machine.

2. I have another machine (Workstation B) on the same domain from which I want to programmatically create the database on Server A with legacy code that uses SQL-DMO.

What are the minimum components (i.e. SQLDMO for 2005?) I need to install on Workstation B and how/where are they available/packaged? I know that SMO is the replacement for SQLDMO, but we're not ready to upgrade to it yet and we still want to create databases on the 2005 instance.

Thanks in advance,

jd

You just have to install SQLDMO for SQL 2005. It is available as a separate download as part of SQL Server 2005 Feature Pack.

You can get it from here:

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

Regards,

Minimum components required to create 2005 database/tables programmatically

I want to know what are the minimum SQL Server 2005 tools required to perform the following task, and how do I install them:

1. I have a server with SQLS 2005 (and all of its management components) installed. Let's call this Server A. I want to create a database and tables in an instance on this machine.

2. I have another machine (Workstation B) on the same domain from which I want to programmatically create the database on Server A with legacy code that uses SQL-DMO.

What are the minimum components (i.e. SQLDMO for 2005?) I need to install on Workstation B and how/where are they available/packaged? I know that SMO is the replacement for SQLDMO, but we're not ready to upgrade to it yet and we still want to create databases on the 2005 instance.

Thanks in advance,

jd

You just have to install SQLDMO for SQL 2005. It is available as a separate download as part of SQL Server 2005 Feature Pack.

You can get it from here:

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

Regards,

Minimal install possible?

The smallest downloadable version seems to be 53MB. Is there any way to create a smaller version to use as an embedded DB for an application? I only need it to be single-user, for what that's worth. I'm thinking more in the 10MB to 20MB range.to my knowledge NO and the SQLExpress team made every effort to ensure the smallest D/Ls possible.|||

You have an option to use SQL Server 2005 Everywhere edition. It has to be run in-process and won't run as a service (however, I think it might be possible to create your own windows service where you embed SQL Server Everywhere). It only uses a few MB HD space, and uses about 5 MB RAM. Available at http://www.microsoft.com/sql/ctp_sqlserver2005everywhereedition.mspx, though only in CTP. Scheduled for release at the end of 2006.

Best regards

Bj?rnar Sundsb?

Minimal install possible?

The smallest downloadable version seems to be 53MB. Is there any way to create a smaller version to use as an embedded DB for an application? I only need it to be single-user, for what that's worth. I'm thinking more in the 10MB to 20MB range.to my knowledge NO and the SQLExpress team made every effort to ensure the smallest D/Ls possible.|||

You have an option to use SQL Server 2005 Everywhere edition. It has to be run in-process and won't run as a service (however, I think it might be possible to create your own windows service where you embed SQL Server Everywhere). It only uses a few MB HD space, and uses about 5 MB RAM. Available at http://www.microsoft.com/sql/ctp_sqlserver2005everywhereedition.mspx, though only in CTP. Scheduled for release at the end of 2006.

Best regards

Bj?rnar Sundsb?

min/max of x minutes

I am trying to develop a sql statement that will create a recordset of the min (or max) values in x minute increments over a period of time.

e.g. over a period of 7 days, I have data that was collected in 1 minute intervals. I need to know the min (or max) value in each 10 minute interval over that same period of time.

Is there an efficient way of doing this?

Try something like this:

DECLARE @.dt_from DATETIME, @.dt_to DATETIME;
SET @.dt_from = '2006-03-21T00:00:00.000';
SET @.dt_to = '2006-03-28T00:00:00.000';

SELECT MIN(dt) AS dt,
MIN(val) AS min_val, MAX(val) AS max_val
FROM tbl
WHERE dt >= @.dt_from
AND dt < @.dt_to
GROUP BY FLOOR(DATEDIFF(MINUTE,@.dt_from,dt)/10) ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

|||Just recently had a chance to try this out. It worked perfectly. Thanks!

min rights required to schedule a sql server job

Hello,

I need to create a user that can schedule jobs. I cannot use the 'sa' account.

What rights should I assign this user?

Michael

Michael,

You need a user that is a memeber of SQLAgentUserRole database role in msdb database.

You can read more about that in BOL topics:

"How to: Configure a User to Create and Manage SQL Server Agent Jobs"

"Security for SQL Server Agent Administration"

Regards,

|||

Here is the link to the Books Online topic for SQL Server Agent security: http://msdn2.microsoft.com/en-us/library/ms190978.aspx

Cheers,
Dan

min rights required to schedule a sql server job

Hello,

I need to create a user that can schedule jobs. I cannot use the 'sa' account.

What rights should I assign this user?

Michael

Michael,

You need a user that is a memeber of SQLAgentUserRole database role in msdb database.

You can read more about that in BOL topics:

"How to: Configure a User to Create and Manage SQL Server Agent Jobs"

"Security for SQL Server Agent Administration"

Regards,

|||

Here is the link to the Books Online topic for SQL Server Agent security: http://msdn2.microsoft.com/en-us/library/ms190978.aspx

Cheers,
Dan