Hello,
When a failover occurs can clients be automatically redirected to the active
instance of a db or does this have to be done with code?
Thanks in advance!Mark,
When the database failover occurs your sessions will be disconnected. You
need to update your application to automatically fail over when this database
failover occurs.
Here is how you can do it.
Implementing Application Failover with Database Mirroring
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/implappfailover.mspx
Hope this helps,
Ben Nevarez
"Mark" wrote:
> Hello,
> When a failover occurs can clients be automatically redirected to the active
> instance of a db or does this have to be done with code?
> Thanks in advance!
>|||When you have a session connected to a database (principal) and the database
fails over to the mirror, your session will be disconnected. You can open a
new session and it will now be connected to the new principal (that was the
mirror before). This requires no application changes and perhaps only will
need the failover partner clause on the connection string.
But if you need application automatic failover you can implement the retry
logic described in the best practices article I mentioned here.
Hope this helps,
Ben Nevarez
"Ben Nevarez" wrote:
> Mark,
> When the database failover occurs your sessions will be disconnected. You
> need to update your application to automatically fail over when this database
> failover occurs.
> Here is how you can do it.
> Implementing Application Failover with Database Mirroring
> http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/implappfailover.mspx
> Hope this helps,
> Ben Nevarez
>
>
> "Mark" wrote:
> > Hello,
> >
> > When a failover occurs can clients be automatically redirected to the active
> > instance of a db or does this have to be done with code?
> >
> > Thanks in advance!
> >
Showing posts with label code. Show all posts
Showing posts with label code. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
Miracle, Bug or My Ignorance ??
select
'[Cust Code]' ColName
,RowId
from
Temp_Upload_Table
where
[cust code]
not in ( select Convert(Float,KUNNR) from KNVV_View
where isnumeric(kunnr) = 1)
The code above is giving an error
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
The [cust code] column is float .
The subquery select Convert(Float,KUNNR) from KNVV_View where isnumeric(kunnr) = 1 when executed alone gives correct result. When i put is as subquery it bombs ...
Is this a miracle, a bug or is there something I need to know ?I can't seem to replicate the problem over here. Here is what I am using:
create table temp_upload_table
([cust code] float,
rowid int)
create table KNVV_View
(KUNNR varchar(10))
insert into KNVV_View values ('o')
insert into KNVV_View values ('hello')
insert into KNVV_View values ('3.2')
insert into KNVV_View values ('1')
insert into temp_upload_table values (3.2, 1)
insert into temp_upload_table values (2.3, 2)
Am I missing something?|||dont know what is happening .. the code runs good on your tables ... gives an error on mine|||There was a thread a little while ago, maybe February that I remember had a similar problem. Danged if I can find it, now, but it may not apply. In the thread, it was found that some of the older notations for numbers (like 2.0e05 for 200,000) were making life miserable for the guy with the question. I think Pat Phelan may have been in on that thread, but I can't be too sure. That was a few too many beers ago.|||Cut and paste this, and tell me it doesn't work...
USE Northwind
GO
SET NOCOUNT ON
create table temp_upload_table
([cust code] float,
rowid int)
create table KNVV_View
(KUNNR varchar(10))
insert into KNVV_View values ('o')
insert into KNVV_View values ('hello')
insert into KNVV_View values ('3.2')
insert into KNVV_View values ('1')
insert into temp_upload_table values (3.2, 1)
insert into temp_upload_table values (2.3, 2)
SELECT '[Cust Code]' ColName
, RowId
FROM Temp_Upload_Table
WHERE [cust code]
not in ( select Convert(Float,KUNNR) from KNVV_View
where isnumeric(kunnr) = 1)
GO|||Found the thread I was after. Here it is:
http://www.dbforums.com/t993650.html
I think Pat was up a bit late last night (or early this morning), so he may not be able to reply for a while.|||Added that link to my SQL Server favorites folder...|||Huh?!?! What, who me?
Did anyone check to see if 1 = IsNumeric([cust code])?
-PatP|||He mentioned that [cust code] is defined as float (not the he post the DDL or sample data...tsk...tsk...)|||Yes I did pat ...
CREATE TABLE [KNVV] (
[VTWEG] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VKORG] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VKGRP] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VKBUR] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SPART] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MANDT] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KVGR5] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KVGR4] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KVGR2] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KVGR1] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KUNNR] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KDGRP] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BZIRK] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create view KNVV_View as
select distinct VKGRP
,VKBUR
,SPART
,MANDT
,KVGR5
,KVGR4
,KVGR2
,KVGR1
,KUNNR
,KDGRP
,BZIRK
from KNVV
where BZIRK <> '999999'
and kvgr1 <> '999'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TABLE [Temp_Upload_Table] (
[RowId] [int] IDENTITY (1, 1) NOT NULL ,
[AOP-Value] [float] NULL ,
[AOP-Volume] [float] NULL ,
[Cust Code] [float] NULL
) ON [PRIMARY]
GO
Brett .. bcp in the data and tell me this works ;)
SELECT '[Cust Code]' ColName
, RowId
FROM Temp_Upload_Table
WHERE [cust code]
not in ( select Convert(Float,KUNNR) from KNVV_View
where isnumeric(kunnr) = 1)|||And i did not post the code and data earlier coz i thought there might be some reasonable explanation for this ... and hey this works on the same data ...
select * from Temp_Upload_Table where convert(varchar(20),Cust_Code) not in ( select convert(float,(select KUNNR where isnumeric(kunnr) = 1)) from KNVV_View )
Any explanations ?|||Hmm. Having a bit of trouble BCP-ing the temp_upload_table file in. I must be missing something obvious.
bcp pubs..Temp_Upload_Table in temp_upload_table.dat -T -E -c -t\t -Smyserver
is giving me:
SQLState = 37000, NativeError = 170
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '-'.
anyone else getting this, or is this because of the big bowl of stupid flakes I had this morning?|||Checked that out ... try using EM ... import/export wizard ;D|||bcp pubs..Temp_Upload_Table in temp_upload_table.dat -T -E -c -t"\t" -Smyserver
?|||Enterprise Manager? Pfft! I can't stand Enterprise Manager. It worked, though. ;-) And I am now getting the error. See if I get any results, though....|||OK, I have a piece of this figured out. It looks from the query plan that when you use "not in", the optimizer is checking for nulls. This check is not necessary when you are only interested in "in". I think that the implicit comparison for null is what is getting you. This apparently works:
SELECT '[Cust Code]' ColName
, RowId
FROM Temp_Upload_Table
WHERE [cust code]
not in (
select isnull (Convert(Float, KUNNR), 0) from KNVV_View
where isnumeric(kunnr) = 1
)|||Are you talking about the nulls in [cust code] column|||I think the optimizer is more worried about nulls returned by the subquery. I think if there is a null in the not in list, SQL Server may get confused. Something about that three way logic. This may be why the optimizer if pulling the possibility aside in the query plan.
What confuses me, though, is why it is not doing that extra "side comparison" for queries that simply use "IN (select...)". It may be that in a query with "in (...)", the nulls in the list are simply tossed, since null compared with anything returns nothing. You, however have a "not in (...)" in your query, so the ghost of DeMorgan may be haunting you.
Make any better sense?|||The funny thing here is that KUNNR in KNVV_View table does not have nulls ... so why is that happening ...|||Probably the optimizer does not want to risk it, so it has to check.
'[Cust Code]' ColName
,RowId
from
Temp_Upload_Table
where
[cust code]
not in ( select Convert(Float,KUNNR) from KNVV_View
where isnumeric(kunnr) = 1)
The code above is giving an error
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
The [cust code] column is float .
The subquery select Convert(Float,KUNNR) from KNVV_View where isnumeric(kunnr) = 1 when executed alone gives correct result. When i put is as subquery it bombs ...
Is this a miracle, a bug or is there something I need to know ?I can't seem to replicate the problem over here. Here is what I am using:
create table temp_upload_table
([cust code] float,
rowid int)
create table KNVV_View
(KUNNR varchar(10))
insert into KNVV_View values ('o')
insert into KNVV_View values ('hello')
insert into KNVV_View values ('3.2')
insert into KNVV_View values ('1')
insert into temp_upload_table values (3.2, 1)
insert into temp_upload_table values (2.3, 2)
Am I missing something?|||dont know what is happening .. the code runs good on your tables ... gives an error on mine|||There was a thread a little while ago, maybe February that I remember had a similar problem. Danged if I can find it, now, but it may not apply. In the thread, it was found that some of the older notations for numbers (like 2.0e05 for 200,000) were making life miserable for the guy with the question. I think Pat Phelan may have been in on that thread, but I can't be too sure. That was a few too many beers ago.|||Cut and paste this, and tell me it doesn't work...
USE Northwind
GO
SET NOCOUNT ON
create table temp_upload_table
([cust code] float,
rowid int)
create table KNVV_View
(KUNNR varchar(10))
insert into KNVV_View values ('o')
insert into KNVV_View values ('hello')
insert into KNVV_View values ('3.2')
insert into KNVV_View values ('1')
insert into temp_upload_table values (3.2, 1)
insert into temp_upload_table values (2.3, 2)
SELECT '[Cust Code]' ColName
, RowId
FROM Temp_Upload_Table
WHERE [cust code]
not in ( select Convert(Float,KUNNR) from KNVV_View
where isnumeric(kunnr) = 1)
GO|||Found the thread I was after. Here it is:
http://www.dbforums.com/t993650.html
I think Pat was up a bit late last night (or early this morning), so he may not be able to reply for a while.|||Added that link to my SQL Server favorites folder...|||Huh?!?! What, who me?
Did anyone check to see if 1 = IsNumeric([cust code])?
-PatP|||He mentioned that [cust code] is defined as float (not the he post the DDL or sample data...tsk...tsk...)|||Yes I did pat ...
CREATE TABLE [KNVV] (
[VTWEG] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VKORG] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VKGRP] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VKBUR] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SPART] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MANDT] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KVGR5] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KVGR4] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KVGR2] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KVGR1] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KUNNR] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KDGRP] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BZIRK] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create view KNVV_View as
select distinct VKGRP
,VKBUR
,SPART
,MANDT
,KVGR5
,KVGR4
,KVGR2
,KVGR1
,KUNNR
,KDGRP
,BZIRK
from KNVV
where BZIRK <> '999999'
and kvgr1 <> '999'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TABLE [Temp_Upload_Table] (
[RowId] [int] IDENTITY (1, 1) NOT NULL ,
[AOP-Value] [float] NULL ,
[AOP-Volume] [float] NULL ,
[Cust Code] [float] NULL
) ON [PRIMARY]
GO
Brett .. bcp in the data and tell me this works ;)
SELECT '[Cust Code]' ColName
, RowId
FROM Temp_Upload_Table
WHERE [cust code]
not in ( select Convert(Float,KUNNR) from KNVV_View
where isnumeric(kunnr) = 1)|||And i did not post the code and data earlier coz i thought there might be some reasonable explanation for this ... and hey this works on the same data ...
select * from Temp_Upload_Table where convert(varchar(20),Cust_Code) not in ( select convert(float,(select KUNNR where isnumeric(kunnr) = 1)) from KNVV_View )
Any explanations ?|||Hmm. Having a bit of trouble BCP-ing the temp_upload_table file in. I must be missing something obvious.
bcp pubs..Temp_Upload_Table in temp_upload_table.dat -T -E -c -t\t -Smyserver
is giving me:
SQLState = 37000, NativeError = 170
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '-'.
anyone else getting this, or is this because of the big bowl of stupid flakes I had this morning?|||Checked that out ... try using EM ... import/export wizard ;D|||bcp pubs..Temp_Upload_Table in temp_upload_table.dat -T -E -c -t"\t" -Smyserver
?|||Enterprise Manager? Pfft! I can't stand Enterprise Manager. It worked, though. ;-) And I am now getting the error. See if I get any results, though....|||OK, I have a piece of this figured out. It looks from the query plan that when you use "not in", the optimizer is checking for nulls. This check is not necessary when you are only interested in "in". I think that the implicit comparison for null is what is getting you. This apparently works:
SELECT '[Cust Code]' ColName
, RowId
FROM Temp_Upload_Table
WHERE [cust code]
not in (
select isnull (Convert(Float, KUNNR), 0) from KNVV_View
where isnumeric(kunnr) = 1
)|||Are you talking about the nulls in [cust code] column|||I think the optimizer is more worried about nulls returned by the subquery. I think if there is a null in the not in list, SQL Server may get confused. Something about that three way logic. This may be why the optimizer if pulling the possibility aside in the query plan.
What confuses me, though, is why it is not doing that extra "side comparison" for queries that simply use "IN (select...)". It may be that in a query with "in (...)", the nulls in the list are simply tossed, since null compared with anything returns nothing. You, however have a "not in (...)" in your query, so the ghost of DeMorgan may be haunting you.
Make any better sense?|||The funny thing here is that KUNNR in KNVV_View table does not have nulls ... so why is that happening ...|||Probably the optimizer does not want to risk it, so it has to check.
Minor Table Insert help
i have the following code, it all works how i want it to bar the first time it runs, when i run the program and insert the data the first time, it inserts the data twice, all other times only once or the update.
$dbh=mysql_connect ("localhost", "twqwwsoy_user", "iiyama") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("twqwwsoy_resources");
$count="SELECT COUNT(message) FROM Diary";
$result = mysql_query($count);
$co = mysql_result($result,$x);
if ($co == 0) {
$SQL= "INSERT INTO Diary (username, day_id, message) VALUES('$username', '$day', '$message')";
$result = @.mysql_query ($SQL) or die('query error ' . mysql_error());
}
else {
$count="SELECT COUNT(username) FROM Diary WHERE username = '$username' AND day_id = '$day'";
$result = mysql_query($count);
$co1 = mysql_result($result,$x);
echo "$co1";
}
if ($co1 == 1) {
$SQL= "UPDATE Diary SET message = '$message' WHERE username = '$username' AND day_id= '$day'";
$result = @.mysql_query ($SQL) or die('query error ' . mysql_error());
}
else {
$SQL= "INSERT INTO Diary (username, day_id, message) VALUES('$username', '$day', '$message')";
$result = @.mysql_query ($SQL) or die('query error ' . mysql_error());
}I don't speak MySQL, but it seems like this: when the table 'diary' is empty, you are running the script for the first time:co = 0 and co1 = 0
IF co = 0 (yes, it is) THEN
INSERT INTO diary ... -> this is executed
ELSE
SELECT COUNT ... -> this is not executed
END IF
IF co1 = 1 (no, it isn't) THEN
UPDATE diary ... -> this is not executed
ELSE
INSERT INTO diary ... -> this statement performs second insertRunning the script second (and every other) time:IF co = 0 (no, it isn't) THEN
INSERT INTO diary ...
ELSE
SELECT COUNT ... -> it is executed and co1 = 2
END IF
IF co1 = 1 (no, it isn't) THEN
UPDATE diary ... -> this is not executed
ELSE
INSERT INTO diary ... -> this is executed
END IFIf I'm not wrong, this is what happens. But, you didn't say what you wanted to happen ... Anyway, I guess you'll have to adjust logic a little bit.
$dbh=mysql_connect ("localhost", "twqwwsoy_user", "iiyama") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("twqwwsoy_resources");
$count="SELECT COUNT(message) FROM Diary";
$result = mysql_query($count);
$co = mysql_result($result,$x);
if ($co == 0) {
$SQL= "INSERT INTO Diary (username, day_id, message) VALUES('$username', '$day', '$message')";
$result = @.mysql_query ($SQL) or die('query error ' . mysql_error());
}
else {
$count="SELECT COUNT(username) FROM Diary WHERE username = '$username' AND day_id = '$day'";
$result = mysql_query($count);
$co1 = mysql_result($result,$x);
echo "$co1";
}
if ($co1 == 1) {
$SQL= "UPDATE Diary SET message = '$message' WHERE username = '$username' AND day_id= '$day'";
$result = @.mysql_query ($SQL) or die('query error ' . mysql_error());
}
else {
$SQL= "INSERT INTO Diary (username, day_id, message) VALUES('$username', '$day', '$message')";
$result = @.mysql_query ($SQL) or die('query error ' . mysql_error());
}I don't speak MySQL, but it seems like this: when the table 'diary' is empty, you are running the script for the first time:co = 0 and co1 = 0
IF co = 0 (yes, it is) THEN
INSERT INTO diary ... -> this is executed
ELSE
SELECT COUNT ... -> this is not executed
END IF
IF co1 = 1 (no, it isn't) THEN
UPDATE diary ... -> this is not executed
ELSE
INSERT INTO diary ... -> this statement performs second insertRunning the script second (and every other) time:IF co = 0 (no, it isn't) THEN
INSERT INTO diary ...
ELSE
SELECT COUNT ... -> it is executed and co1 = 2
END IF
IF co1 = 1 (no, it isn't) THEN
UPDATE diary ... -> this is not executed
ELSE
INSERT INTO diary ... -> this is executed
END IFIf I'm not wrong, this is what happens. But, you didn't say what you wanted to happen ... Anyway, I guess you'll have to adjust logic a little bit.
Friday, March 23, 2012
Minimum SQL Server permissions needed
I have some Access .adp VBA code (below) that deletes a SQL Server 2005 tabl
e
and recreates it.
DoCmd.DeleteObject acTable, "dbo.tblTemp"
DoCmd.CopyObject , "tblTemp", acTable, "dbo.tblTempStructure"
I would like to create a role that has the minimum permissions necessary to
do this. Any advice?
If I don't SELECT control to the two tables, Access doesn't seem to see
them. If I don't grant CONTROL, Access doesn't seem to be able to drop the
tables. Yet, after the tblTemp is recreated in the CopyObject line of code,
Access doesn't see it again as the role doesn't have that SELECT or CONTROL
permissions granted to it anymore.
Another problem which seems like overkill was that I had to grant CREATE
TABLE to the role.
Also, If I don't grant control to the role dbo, Access squawks about not
having permissions to dbo.
Your help is appreciated.Not sure about your case - as you don't mention how you are trying to access
these tables after their creations but did you look into the possibility of
creating your tables in the tempdb database?
(I don't even know if this will work from DoCmd.*; however, using a SP or
the ADO connection or the command objects would probably be a much better
idea than using DoCmd.* even if the DoCmd.* are working).
For example:
Set NoCount ON
create table Tempdb..Members (IdMember int Identity (1,1) primary key,
firstname
varchar(50) collate database_default)
insert into Tempdb..Members (firstname) values ('deny')
insert into Tempdb..Members (firstname) values ('ben')
select M.* from Tempdb..Members as M
drop table Tempdb..Members
(the collate database_default statement is there in case the default
collation for the tempdb database would be different from the default
collation of the current database. If this your case, don't forget the
collate database_default statement and don't use a use Tempdb
statement; otherwise the collation used will be the one defined for the
tempdb database. Of course, if both default collations are the same then
you don't have to fiddle with this.)
In my opinion, granting Control and Create table permission on an account is
pretty much giving away all security; so if possible, it would be a much
better idea to use the tempdb database; as this database has been created
exactly for that purpose.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:56B5B829-9DE2-4805-9F9C-076E49F6ABEF@.microsoft.com...
>I have some Access .adp VBA code (below) that deletes a SQL Server 2005
>table
> and recreates it.
> DoCmd.DeleteObject acTable, "dbo.tblTemp"
> DoCmd.CopyObject , "tblTemp", acTable, "dbo.tblTempStructure"
> I would like to create a role that has the minimum permissions necessary
> to
> do this. Any advice?
> If I don't SELECT control to the two tables, Access doesn't seem to see
> them. If I don't grant CONTROL, Access doesn't seem to be able to drop
> the
> tables. Yet, after the tblTemp is recreated in the CopyObject line of
> code,
> Access doesn't see it again as the role doesn't have that SELECT or
> CONTROL
> permissions granted to it anymore.
> Another problem which seems like overkill was that I had to grant CREATE
> TABLE to the role.
> Also, If I don't grant control to the role dbo, Access squawks about not
> having permissions to dbo.
> Your help is appreciated.|||For the connection object, the most simple is to use the one who is already
available:
CurrentProject.Connection.Execute ("Your sql statement here")
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:%23aSMFFaPIHA.5184@.TK2MSFTNGP05.phx.gbl...
> Not sure about your case - as you don't mention how you are trying to
> access these tables after their creations but did you look into the
> possibility of creating your tables in the tempdb database?
> (I don't even know if this will work from DoCmd.*; however, using a SP or
> the ADO connection or the command objects would probably be a much better
> idea than using DoCmd.* even if the DoCmd.* are working).
> For example:
> Set NoCount ON
> create table Tempdb..Members (IdMember int Identity (1,1) primary key,
> firstname
> varchar(50) collate database_default)
> insert into Tempdb..Members (firstname) values ('deny')
> insert into Tempdb..Members (firstname) values ('ben')
> select M.* from Tempdb..Members as M
> drop table Tempdb..Members
>
> (the collate database_default statement is there in case the default
> collation for the tempdb database would be different from the default
> collation of the current database. If this your case, don't forget the
> collate database_default statement and don't use a use Tempdb
> statement; otherwise the collation used will be the one defined for the
> tempdb database. Of course, if both default collations are the same then
> you don't have to fiddle with this.)
> In my opinion, granting Control and Create table permission on an account
> is pretty much giving away all security; so if possible, it would be a
> much better idea to use the tempdb database; as this database has been
> created exactly for that purpose.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:56B5B829-9DE2-4805-9F9C-076E49F6ABEF@.microsoft.com...
>|||Sylvain,
This is an older app that I've inherited. It currently runs with sa rights
(not good), so I was trying to create a new user & role with a limited set o
f
rights that could still allow the Access app to do what it needs. I was
hoping to avoid overly changing the MS Access code, but that may not be
possible. Your suggestion of ditching the DoCmd for executing a stored proc
though is a good one. I'll pursue this.
Thank you
"Sylvain Lafontaine" wrote:
[vbcol=seagreen]
> Not sure about your case - as you don't mention how you are trying to acce
ss
> these tables after their creations but did you look into the possibility o
f
> creating your tables in the tempdb database?
> (I don't even know if this will work from DoCmd.*; however, using a SP or
> the ADO connection or the command objects would probably be a much better
> idea than using DoCmd.* even if the DoCmd.* are working).
> For example:
> Set NoCount ON
> create table Tempdb..Members (IdMember int Identity (1,1) primary key,
> firstname
> varchar(50) collate database_default)
> insert into Tempdb..Members (firstname) values ('deny')
> insert into Tempdb..Members (firstname) values ('ben')
> select M.* from Tempdb..Members as M
> drop table Tempdb..Members
>
> (the ? collate database_default ? statement is there in case the default
> collation for the tempdb database would be different from the default
> collation of the current database. If this your case, don't forget the ?
> collate database_default ? statement and don't use a ? use Tempdb ?
> statement; otherwise the collation used will be the one defined for the
> tempdb database. Of course, if both default collations are the same then
> you don't have to fiddle with this.)
> In my opinion, granting Control and Create table permission on an account
is
> pretty much giving away all security; so if possible, it would be a much
> better idea to use the tempdb database; as this database has been created
> exactly for that purpose.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:56B5B829-9DE2-4805-9F9C-076E49F6ABEF@.microsoft.com...|||Rob (Rob@.discussions.microsoft.com) writes:
> This is an older app that I've inherited. It currently runs with sa
> rights (not good), so I was trying to create a new user & role with a
> limited set of rights that could still allow the Access app to do what
> it needs. I was hoping to avoid overly changing the MS Access code, but
> that may not be possible. Your suggestion of ditching the DoCmd for
> executing a stored proc though is a good one. I'll pursue this.
Yes, putting this in a stored proceedure is the only way out. But Sylvain
did not tell the full story. For this to work you need to sign the procedure
with a certificate, and create a user fot the certificate and grant that
user the required rights.
I describe this in detail in this article on my web site:
http://www.sommarskog.se/grantperm.html
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> ha
scritto nel messaggio news:#aSMFFaPIHA.5184@.TK2MSFTNGP05.phx.gbl...
> Not sure about your case - as you don't mention how you are trying to
> access these tables after their creations but did you look into the
> possibility of creating your tables in the tempdb database?
> (I don't even know if this will work from DoCmd.*; however, using a SP or
> the ADO connection or the command objects would probably be a much better
> idea than using DoCmd.* even if the DoCmd.* are working).
> For example:
> Set NoCount ON
> create table Tempdb..Members (IdMember int Identity (1,1) primary key,
> firstname
> varchar(50) collate database_default)
> insert into Tempdb..Members (firstname) values ('deny')
> insert into Tempdb..Members (firstname) values ('ben')
> select M.* from Tempdb..Members as M
> drop table Tempdb..Members
>
> (the collate database_default statement is there in case the default
> collation for the tempdb database would be different from the default
> collation of the current database. If this your case, don't forget the
> collate database_default statement and don't use a use Tempdb
> statement; otherwise the collation used will be the one defined for the
> tempdb database. Of course, if both default collations are the same then
> you don't have to fiddle with this.)
> In my opinion, granting Control and Create table permission on an account
> is pretty much giving away all security; so if possible, it would be a
> much better idea to use the tempdb database; as this database has been
> created exactly for that purpose.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:56B5B829-9DE2-4805-9F9C-076E49F6ABEF@.microsoft.com...
>
e
and recreates it.
DoCmd.DeleteObject acTable, "dbo.tblTemp"
DoCmd.CopyObject , "tblTemp", acTable, "dbo.tblTempStructure"
I would like to create a role that has the minimum permissions necessary to
do this. Any advice?
If I don't SELECT control to the two tables, Access doesn't seem to see
them. If I don't grant CONTROL, Access doesn't seem to be able to drop the
tables. Yet, after the tblTemp is recreated in the CopyObject line of code,
Access doesn't see it again as the role doesn't have that SELECT or CONTROL
permissions granted to it anymore.
Another problem which seems like overkill was that I had to grant CREATE
TABLE to the role.
Also, If I don't grant control to the role dbo, Access squawks about not
having permissions to dbo.
Your help is appreciated.Not sure about your case - as you don't mention how you are trying to access
these tables after their creations but did you look into the possibility of
creating your tables in the tempdb database?
(I don't even know if this will work from DoCmd.*; however, using a SP or
the ADO connection or the command objects would probably be a much better
idea than using DoCmd.* even if the DoCmd.* are working).
For example:
Set NoCount ON
create table Tempdb..Members (IdMember int Identity (1,1) primary key,
firstname
varchar(50) collate database_default)
insert into Tempdb..Members (firstname) values ('deny')
insert into Tempdb..Members (firstname) values ('ben')
select M.* from Tempdb..Members as M
drop table Tempdb..Members
(the collate database_default statement is there in case the default
collation for the tempdb database would be different from the default
collation of the current database. If this your case, don't forget the
collate database_default statement and don't use a use Tempdb
statement; otherwise the collation used will be the one defined for the
tempdb database. Of course, if both default collations are the same then
you don't have to fiddle with this.)
In my opinion, granting Control and Create table permission on an account is
pretty much giving away all security; so if possible, it would be a much
better idea to use the tempdb database; as this database has been created
exactly for that purpose.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:56B5B829-9DE2-4805-9F9C-076E49F6ABEF@.microsoft.com...
>I have some Access .adp VBA code (below) that deletes a SQL Server 2005
>table
> and recreates it.
> DoCmd.DeleteObject acTable, "dbo.tblTemp"
> DoCmd.CopyObject , "tblTemp", acTable, "dbo.tblTempStructure"
> I would like to create a role that has the minimum permissions necessary
> to
> do this. Any advice?
> If I don't SELECT control to the two tables, Access doesn't seem to see
> them. If I don't grant CONTROL, Access doesn't seem to be able to drop
> the
> tables. Yet, after the tblTemp is recreated in the CopyObject line of
> code,
> Access doesn't see it again as the role doesn't have that SELECT or
> CONTROL
> permissions granted to it anymore.
> Another problem which seems like overkill was that I had to grant CREATE
> TABLE to the role.
> Also, If I don't grant control to the role dbo, Access squawks about not
> having permissions to dbo.
> Your help is appreciated.|||For the connection object, the most simple is to use the one who is already
available:
CurrentProject.Connection.Execute ("Your sql statement here")
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:%23aSMFFaPIHA.5184@.TK2MSFTNGP05.phx.gbl...
> Not sure about your case - as you don't mention how you are trying to
> access these tables after their creations but did you look into the
> possibility of creating your tables in the tempdb database?
> (I don't even know if this will work from DoCmd.*; however, using a SP or
> the ADO connection or the command objects would probably be a much better
> idea than using DoCmd.* even if the DoCmd.* are working).
> For example:
> Set NoCount ON
> create table Tempdb..Members (IdMember int Identity (1,1) primary key,
> firstname
> varchar(50) collate database_default)
> insert into Tempdb..Members (firstname) values ('deny')
> insert into Tempdb..Members (firstname) values ('ben')
> select M.* from Tempdb..Members as M
> drop table Tempdb..Members
>
> (the collate database_default statement is there in case the default
> collation for the tempdb database would be different from the default
> collation of the current database. If this your case, don't forget the
> collate database_default statement and don't use a use Tempdb
> statement; otherwise the collation used will be the one defined for the
> tempdb database. Of course, if both default collations are the same then
> you don't have to fiddle with this.)
> In my opinion, granting Control and Create table permission on an account
> is pretty much giving away all security; so if possible, it would be a
> much better idea to use the tempdb database; as this database has been
> created exactly for that purpose.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:56B5B829-9DE2-4805-9F9C-076E49F6ABEF@.microsoft.com...
>|||Sylvain,
This is an older app that I've inherited. It currently runs with sa rights
(not good), so I was trying to create a new user & role with a limited set o
f
rights that could still allow the Access app to do what it needs. I was
hoping to avoid overly changing the MS Access code, but that may not be
possible. Your suggestion of ditching the DoCmd for executing a stored proc
though is a good one. I'll pursue this.
Thank you
"Sylvain Lafontaine" wrote:
[vbcol=seagreen]
> Not sure about your case - as you don't mention how you are trying to acce
ss
> these tables after their creations but did you look into the possibility o
f
> creating your tables in the tempdb database?
> (I don't even know if this will work from DoCmd.*; however, using a SP or
> the ADO connection or the command objects would probably be a much better
> idea than using DoCmd.* even if the DoCmd.* are working).
> For example:
> Set NoCount ON
> create table Tempdb..Members (IdMember int Identity (1,1) primary key,
> firstname
> varchar(50) collate database_default)
> insert into Tempdb..Members (firstname) values ('deny')
> insert into Tempdb..Members (firstname) values ('ben')
> select M.* from Tempdb..Members as M
> drop table Tempdb..Members
>
> (the ? collate database_default ? statement is there in case the default
> collation for the tempdb database would be different from the default
> collation of the current database. If this your case, don't forget the ?
> collate database_default ? statement and don't use a ? use Tempdb ?
> statement; otherwise the collation used will be the one defined for the
> tempdb database. Of course, if both default collations are the same then
> you don't have to fiddle with this.)
> In my opinion, granting Control and Create table permission on an account
is
> pretty much giving away all security; so if possible, it would be a much
> better idea to use the tempdb database; as this database has been created
> exactly for that purpose.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:56B5B829-9DE2-4805-9F9C-076E49F6ABEF@.microsoft.com...|||Rob (Rob@.discussions.microsoft.com) writes:
> This is an older app that I've inherited. It currently runs with sa
> rights (not good), so I was trying to create a new user & role with a
> limited set of rights that could still allow the Access app to do what
> it needs. I was hoping to avoid overly changing the MS Access code, but
> that may not be possible. Your suggestion of ditching the DoCmd for
> executing a stored proc though is a good one. I'll pursue this.
Yes, putting this in a stored proceedure is the only way out. But Sylvain
did not tell the full story. For this to work you need to sign the procedure
with a certificate, and create a user fot the certificate and grant that
user the required rights.
I describe this in detail in this article on my web site:
http://www.sommarskog.se/grantperm.html
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> ha
scritto nel messaggio news:#aSMFFaPIHA.5184@.TK2MSFTNGP05.phx.gbl...
> Not sure about your case - as you don't mention how you are trying to
> access these tables after their creations but did you look into the
> possibility of creating your tables in the tempdb database?
> (I don't even know if this will work from DoCmd.*; however, using a SP or
> the ADO connection or the command objects would probably be a much better
> idea than using DoCmd.* even if the DoCmd.* are working).
> For example:
> Set NoCount ON
> create table Tempdb..Members (IdMember int Identity (1,1) primary key,
> firstname
> varchar(50) collate database_default)
> insert into Tempdb..Members (firstname) values ('deny')
> insert into Tempdb..Members (firstname) values ('ben')
> select M.* from Tempdb..Members as M
> drop table Tempdb..Members
>
> (the collate database_default statement is there in case the default
> collation for the tempdb database would be different from the default
> collation of the current database. If this your case, don't forget the
> collate database_default statement and don't use a use Tempdb
> statement; otherwise the collation used will be the one defined for the
> tempdb database. Of course, if both default collations are the same then
> you don't have to fiddle with this.)
> In my opinion, granting Control and Create table permission on an account
> is pretty much giving away all security; so if possible, it would be a
> much better idea to use the tempdb database; as this database has been
> created exactly for that purpose.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:56B5B829-9DE2-4805-9F9C-076E49F6ABEF@.microsoft.com...
>
Saturday, February 25, 2012
Migration from MySQL to SQL Server
How can I change the MySQL code to SQL Server's like below?
I could not find out to change appropriate UNSIGNED keyword and ENUM
keyword in SQL Server.
CREATE TABLE myTable (
id INT UNSIGNED NOT NULL,
name CHAR(20),
role ENUM('ADMIN','USER','GUEST','UNKNOWN') DEFAULT 'ADMIN'
PRIMARY KEY(id)
);
hi,
you can use "standard" signed integers in SQL Server syntax to host integer values in the range -2,147,483,648 to 2,147,483,647 as SQL Server does not provide unsigned integers... you can "scale up" to bigint if you required larger range in the integer domain..
http://msdn2.microsoft.com/en-us/library/ms187745.aspx
SQL Server does not provide an "Enum" feature, but you can define a CHECK CONSTRAINT for that...
usually you should consider a referential integrity based referenced table in the design if the "range" can grow as a direct CHECK requires modelling refinement and rewriting if you, say, want to add sometime later another "role" valid entry...
so... you can "translate" as
SET NOCOUNT ON; USE tempdb; GO CREATE TABLE myTable ( Id int NOT NULL PRIMARY KEY, Name char(20) NULL, -- but I bet varchar(20) should be better -- as not all names will be 20 chars long Role varchar(7) NOT NULL DEFAULT 'ADMIN' CONSTRAINT chk_Valid$Entries$for$Roles -- use this check constraint if you require CASE SENSITIVE validation of the role -- CHECK ( Role IN ('ADMIN' COLLATE Latin1_General_CS_AS , 'USER' COLLATE Latin1_General_CS_AS , 'GUEST' COLLATE Latin1_General_CS_AS , 'UNKNOWN' COLLATE Latin1_General_CS_AS ) ) -- use this check constraint if you do not require CASE SENSITIVE validation of the role CHECK ( Role IN ('ADMIN', 'USER', 'GUEST', 'UNKNOWN') ) ); GO INSERT INTO myTable VALUES ( 1 , 'Andrea', DEFAULT ); -- default role GO INSERT INTO myTable VALUES ( 2 , 'Andrea2', 'USER' ); -- valid role GO INSERT INTO myTable VALUES ( 3 , 'Andrea3', 'other' ); -- NOT valid role GO INSERT INTO myTable VALUES ( 4 , 'Andrea3', 'User' ); -- NOT valid role IF case sensite -- collation check has been set GO SELECT * FROM myTable GO DROP TABLE myTable; --<-- Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the CHECK constraint "chk_Valid$Entries$for$Roles". The conflict occurred in database "tempdb", table "dbo.myTable", column 'Role'. The statement has been terminated. Id Name Role -- -- - 1 Andrea ADMIN 2 Andrea2 USER 4 Andrea3 User I provided 2 different check constraints... the active one is NOT case sensitive, that's to say users can enter both 'ADMIN' and 'admin' (or whatever conbination of cases), where the commented one performs a case sensitive check and the 4th insert will fail accordingly...
thanks to Lorenzo Benaglia, friend and SQL Server MVP fellow for helping in the "migration"...
regards
Subscribe to:
Posts (Atom)