Converting Dates as paramters

Hey guys,

Hoping i can get some help with this one..

Problem:

Data source has a datetime format as YYYYMMDD

I would like to have my users enter a date in the format of DD/MM/YYYY and in the background have it convert to the YYYYMMDD so i can filter my data according to the data

ANy ideas on how i will do this?

thanks

scotty

Use Format function and convert the date parameter to required format.

Format(Parameters!prmStartDate.Value, "yyyy-MM-dd")

Convert VB.NET to TSQL PROC & Reference a Proc from another Proc

Howdy,
ISSUE 1: See issue 2 below. I have a distance calculator on my site which wo
rks great. However, the users need to sort by distance, which make sense. I'
m not sure how to do it other than like this. With the returning query inclu
de the distance from origin. Here's my dilemma, I have the script working gr
eat in VB which provides the distance, but that is not sortable, but when I
port it over to TSQL I get differing results. Here is the code in VB:
x = (Math.Sin(DegToRads(_Lat1)) * Math.Sin(DegToRads(_Lat2)) + Math.Cos(DegT
oRads(_Lat1)) * Math.Cos(DegToRads(_Lat2)) * Math.Cos(Math.Abs((DegToRads(_L
ong2)) - (DegToRads(_Long1)))))
x = Math.Atan((Math.Sqrt(1 - x ^ 2)) / x)
x = 60.0 * ((x / Math.PI) * 180) * 1.1507794480235425
return x
Function DegToRads(ByVal Deg)
DegToRads = CDbl(Deg * Math.PI / 180)
End Function
As you can see, nice and simple. Here is how I ported it over to TSQL
CREATE PROCEDURE [dbo].[cp_FindDistance]
@.FromLat as decimal(38,18),
@.FromLong as decimal(38,18),
@.ToLat as decimal(38,18),
@.ToLong as decimal(38,18)
AS
DECLARE @.X as decimal(38,20)
DECLARE @.PI as decimal(38,20)
SET @.PI = 3.14159265358979323846
SET @.X = (Sin(CAST((@.FromLat * @.PI / 180) as int)) * Sin(CAST((@.ToLat * @.PI
/ 180) as int)) + Cos(CAST((@.FromLat * @.PI / 180) as int)) * Cos(CAST((@.ToLa
t * @.PI / 180) as int)) * Cos(Abs(CAST((@.ToLong * @.PI / 180) as int)) - (CAS
T((@.FromLong * @.PI / 180) as int))))
SET @.X = Atan((Sqrt(1 - SQUARE(@.X))) / @.X)
SET @.X = (1.852 * 60.0 * ((@.X / @.PI) * 180))
SET @.X = @.X / 1.609344
SELECT @.X as Miles
The VB is returning accurate miles while the TSQL is returning some number w
ay out of reach, for example when entering cp_FindDistance 41.63,-87.73,41.7
,-88.07, the PROC returns -4516.23854688618468000000 while the VB script ret
urns 15.81.
ISSUE 2: Once I get this proc working, how do I get it into the proc that is
returning the recordset of locations? i.e. select *, cp_GetDistance(fromlat
,fromlong,places.lat,places.long) as distance from places.
Thanks a ton!!!
David LozziDavid,
My math skills are not as good as yours, however, my SQL skills are strong.
I played with your logic some, to attempt to help you out, but, when you lo
ok at it, you'll probably find my math error right away. You got 15 miles,
and I'm getting 18 ...
this is probably a rounding error somewhere that you'll be able to find.
I started from your VB code, instead of trying to use the SQL code. I looke
d at the SQL code and recognized issues, so I started over from the VB Code.
When you find my rounding error, I'd appreciate a response
to ctruett3 at gmail.
hope this was helpful. As to the second portion of the post, try creating a
function (Like below) instead of a stored procedure, this will allow you to
use it in-line like:
/*
Select Top 1
dbo.fnuFindDistance(41.63, -87.73, 41.7, -88.07) Distance
, name
From
master.dbo.sysobjects
*/
--spuFindDistance 41.63,-87.73,41.7,-88.07
--Your answer = 15.81
Create Procedure
dbo.spuFindDistance
(
@.FromLat float
, @.FromLong float
, @.ToLat float
, @.ToLong float
)
As
Declare @.Miles float
Select @.Miles = Sin(dbo.fnuDegToRads(@.FromLat))
* Sin(dbo.fnuDegToRads(@.ToLat))
+ Cos(dbo.fnuDegToRads(@.FromLat))
* Cos(dbo.fnuDegToRads(@.ToLat))
* Cos(Abs(dbo.fnuDegToRads(@.ToLong) - dbo.fnuDegToRads(@.FromLong)))
Select @.Miles = Atan(Sqrt(1 - Power(@.Miles, 2)) / @.Miles)
Select @.Miles = (60.0 * ((@.Miles / PI()) * 180) * 1.1507794480235425)
Select @.Miles [Distance]
Go
Create Function
dbo.fnuDegToRads
(
@.Deg float
)
Returns float
As
Begin
Declare @.RetVal float
Select @.RetVal = Cast(@.Deg * Pi() / 180 as float)
Return @.RetVal
End
Go
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...|||
Tim Heap
Software & Database Manager
POSTAR Ltd
www.postar.co.uk
tim@.postar.co.uk
*** Sent via Developersdex http://www.examnotes.net ***

Convert MS Excel to XML and then insert all data records into MS SQL

Hi,

I am developing a web page for users to input data records. I am seeking the fastest way that users can upload their MS Excel files and the system can help data insertion into one data table. I know XML can insert data records into SQL database easily. Could any one give me some ideas how to perform this issue? Thanks a lot.

If you are using excel, you don't need to transform to xml. You can load data directly from excel file.

INSERT YourTable(...)

SELECT ...
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\YourExcelFile.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[YourSheetName]

|||

1) Save your excel file in XML format

2) create YourTable(xml_excel xml)

3) Run

INSERT YourTable(xml_excel)

select cast(x as xml) from openrowset(bulk 'dir_path_to_excel_xml_file', single_blob) as t(x)

|||

Thanks a lot! Could there be any sample code somewhere? I hardly find one suit into my case. The process should be:

1) Upload Excel file (Will be grateful if users can directly import.)

2) System Import data from specific data column in Excel to MS SQL table

A pre-define Excel workbook is provided for users.|||

I would like to convert the MS Excel file to XML on the server, rather than training users to export in XML format. Also, users may not all have Excel 2003.

Is there a way to convert on a server?

Thanks

|||

Kenneth,

Another method is to use a third party tool, like the FarPoint Spread control (www.FarPointSpread.com) to load the Excel file on your web server, without needing access to Excel. Then, you have the data and formatting inspreadsheet format that you can do what you need to with. From here, I would suggest creating a DataSet object with the data and then opening a connection to the Sql database to write your DataRows from the DataSet you created.

Scott Shorter
FarPoint Technologies

|||

Hi can u please tell me where this code should be written in an excel sheet.I am using macros to select data from sql and want to export the updated data from excel to sql database.

Regards,

Shiva

|||See http://www.360data.nl/EN/Docs/080123_XML.aspx for an example parsing Excel-generated XML into a SQL db.

Convert MS Excel to XML and then insert all data records into MS SQL

Hi,

I am developing a web page for users to input data records. I am seeking the fastest way that users can upload their MS Excel files and the system can help data insertion into one data table. I know XML can insert data records into SQL database easily. Could any one give me some ideas how to perform this issue? Thanks a lot.

If you are using excel, you don't need to transform to xml. You can load data directly from excel file.

INSERT YourTable(...)

SELECT ...
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\YourExcelFile.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[YourSheetName]

|||

1) Save your excel file in XML format

2) create YourTable(xml_excel xml)

3) Run

INSERT YourTable(xml_excel)

select cast(x as xml) from openrowset(bulk 'dir_path_to_excel_xml_file', single_blob) as t(x)

|||

Thanks a lot! Could there be any sample code somewhere? I hardly find one suit into my case. The process should be:

1) Upload Excel file (Will be grateful if users can directly import.)

2) System Import data from specific data column in Excel to MS SQL table

A pre-define Excel workbook is provided for users.|||

I would like to convert the MS Excel file to XML on the server, rather than training users to export in XML format. Also, users may not all have Excel 2003.

Is there a way to convert on a server?

Thanks

|||

Kenneth,

Another method is to use a third party tool, like the FarPoint Spread control (www.FarPointSpread.com) to load the Excel file on your web server, without needing access to Excel. Then, you have the data and formatting inspreadsheet format that you can do what you need to with. From here, I would suggest creating a DataSet object with the data and then opening a connection to the Sql database to write your DataRows from the DataSet you created.

Scott Shorter
FarPoint Technologies

|||

Hi can u please tell me where this code should be written in an excel sheet.I am using macros to select data from sql and want to export the updated data from excel to sql database.

Regards,

Shiva

Convert MS Excel to XML and then insert all data records into MS SQL

Hi,

I am developing a web page for users to input data records. I am seeking the fastest way that users can upload their MS Excel files and the system can help data insertion into one data table. I know XML can insert data records into SQL database easily. Could any one give me some ideas how to perform this issue? Thanks a lot.

If you are using excel, you don't need to transform to xml. You can load data directly from excel file.

INSERT YourTable(...)

SELECT ...
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\YourExcelFile.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[YourSheetName]

|||

1) Save your excel file in XML format

2) create YourTable(xml_excel xml)

3) Run

INSERT YourTable(xml_excel)

select cast(x as xml) from openrowset(bulk 'dir_path_to_excel_xml_file', single_blob) as t(x)

|||

Thanks a lot! Could there be any sample code somewhere? I hardly find one suit into my case. The process should be:

1) Upload Excel file (Will be grateful if users can directly import.)

2) System Import data from specific data column in Excel to MS SQL table

A pre-define Excel workbook is provided for users.|||

I would like to convert the MS Excel file to XML on the server, rather than training users to export in XML format. Also, users may not all have Excel 2003.

Is there a way to convert on a server?

Thanks

|||

Kenneth,

Another method is to use a third party tool, like the FarPoint Spread control (www.FarPointSpread.com) to load the Excel file on your web server, without needing access to Excel. Then, you have the data and formatting inspreadsheet format that you can do what you need to with. From here, I would suggest creating a DataSet object with the data and then opening a connection to the Sql database to write your DataRows from the DataSet you created.

Scott Shorter
FarPoint Technologies

|||

Hi can u please tell me where this code should be written in an excel sheet.I am using macros to select data from sql and want to export the updated data from excel to sql database.

Regards,

Shiva

Convert MS acess to SQL Server

if (!Page.IsPostBack)

{ if (Session["users"] != null && (Session[flag"] == "true"))

{

String IP = Request.ServerVariables["remote_host"].ToString();

String Datee = DateTime.Now.Date.ToString();

OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source="

+ HttpContext.Current.Server.MapPath("~/App_Data/result.mdb"));

OleDbCommand cmd = new OleDbCommand("Insert Into KullaniciSayisi (IP,Datee) Values (IPDatee)", con);

cmd.Parameters.AddWithValue("IP", IP);

cmd.Parameters.AddWithValue("Datee", Datee);

con.Open();

intresultt = cmd.ExecuteNonQuery();

con.Close();

Session["flag"] = "false";

}

}

I want to convert this code to SQL sever . However I can not do it? Can you help me?

Hi,

1) Change OleDbConnection to SqlClient.SqlConnection

2) Change OleDbCommand to SqlClient.SqlCommand

3) Change the connection string to connect into your local SQL server, you can get more info from www.connectionstrings.com

That is all what you need.

|||The name "IP" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.|||

I convert however I have a problem. my code do not work :(

String IP=...;

String Datee=DateTime.Now.ToShortDateString();

............

SqlParameter paramIP=new SqlParameter("@.IP",SqlDbType.varchar,50);

SqlParameter paramdate=new SqlParameter("@.IP",SqlDbType.datetime);

paramIP.value=IP;

paramdate.value=Convert.ToDateTime(Datee).ToShortDateString();

cmd.Parameters.AddWithValue("@.IP",paramIP);

cmd.Parameters.AddWithValue("@.date",paramdate);

con.Open();

cmd.ExecuteNonQuery();

con.Close();

When I debug the code cmd.ExecuteNon.Query() give problem. In my db date type is Datetime, IP is String. Can you help me?

|||Post the whole code and the exception as well.|||

if (!Page.IsPostBack)

{

if (Session["ziyaretci"] != null && (Session["kontrol"] == "true"))

{

try{

String IP = Request.ServerVariables["remote_host"].ToString();

String Tarih = DateTime.Now.Date.ToString();

SqlConnection= my connection string

SqlCommand cmd = new OleDbCommand("Insert Into userss (IP,Datee) Values (IP,Datee)", con);

SqlParameter paramIP=new SqlParameter("@.IP",SqlDbType.varchar,50);

SqlParameter paramdate=new SqlParameter("@.IP",SqlDbType.datetime);

paramIP.value=IP;

paramdate.value=Convert.ToDateTime(Datee);

cmd.Open();

int result = cmd.ExecuteNonQuery();

cmd.Close();

Session["kontrol"] = "false";

}

catch(Exception ex){

Response.Write("There is a problem" + ex)

}

}

}

This is my all code.

|||

Hi,

Change the followings

SqlCommand cmd = new OleDbCommand("Insert Into userss (IP,Datee) Values (@.IP,@.Date)", con);

SqlParameter paramIP=new SqlParameter("@.IP",SqlDbType.varchar,50);

SqlParameter paramdate=new SqlParameter("@.Date",SqlDbType.datetime);

|||

Thanks for your helping.

Convert HEX to Text

I have an image data type in a table (it is a digital signature) and one of my users wants me to search the image data and see how many people have a certain attribute in their digital signature. I know the image data is HEX, but how, in query analyzer, can I view it as the XML digital signature data that it really is? So, how can I convert hex to text in sql server?

hi
you can use this function:

CREATE function HEXTODEC(@.s VARCHAR(255) )
--Converts an hexadecimal number to decimal.
returns int
as
BEGIN
DECLARE @.i int, @.temp char(1), @.result int
SELECT @.i=1
SELECT @.result=0
WHILE (@.i<=LEN(@.s))
BEGIN
SELECT @.temp=UPPER(SUBSTRING(@.s,@.i,1))
IF (@.temp>='0') AND (@.temp<='9')
SELECT @.result=@.result+ (ASCII(@.temp)-48)*POWER(16,LEN(@.s)-@.i)
ELSE
IF (@.temp>='A') AND (@.temp<='F')
SELECT @.result=@.result+ (ASCII(@.temp)-55)*POWER(16,LEN(@.s)-@.i)
SELECT @.i=@.i+1
END
return @.result
END

good luck

|||

Thanks, but that doesn't help. I had actually found that on a Google search, but it only works for numbers, when the data is really XML.

I did, however, find that SQL Manager 2005 for SQL Server can show the image field as the XML that it really is, so I was able to kind of get the data out that I needed. A bit of a convoluted way, and I only have a trial version of SQL Manager 2005 for SQL Server, but it got me the results I needed.

Convert from logins from SQL to Windows authentication

My company is converting from Novell to Windows (Active Directory)
environment. One of the SQL 200 server has more than 550 logins/users and
they are using SQL authentication.
What is the quickest way to convert all of them to Windows authentication
and keeping them to have same permission to databases & database roles (e.g
script etc). Manually recreate them is a big task.
Regards,
Johnny
Anyone can give me some advices please ?
"Johnny" wrote:

> My company is converting from Novell to Windows (Active Directory)
> environment. One of the SQL 200 server has more than 550 logins/users and
> they are using SQL authentication.
> What is the quickest way to convert all of them to Windows authentication
> and keeping them to have same permission to databases & database roles (e.g
> script etc). Manually recreate them is a big task.
> Regards,
> Johnny
|||Since you cannot convert a login, you have to add a new, re-map the users to the new login, then
drop the old login. Check out sp_change_users_logins for re-mapping users.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:DBBAA319-8FCA-484B-8706-4B857BD6FE8C@.microsoft.com...[vbcol=seagreen]
> Anyone can give me some advices please ?
> "Johnny" wrote:
|||sp_change_users_login cannot be used with Microsoft Windows NT? users. I
cannot remap the database users to the new windows logins.
e.g sp_change_users_login update_one,'user1','domain\users1'
But if I drop the database user before adding the user to the windows login,
the user will lose all the roles and permissions. Even the scripting is not
very useful because it isn't grouped by users.
Help please.
Johnny
"Tibor Karaszi" wrote:

> Since you cannot convert a login, you have to add a new, re-map the users to the new login, then
> drop the old login. Check out sp_change_users_logins for re-mapping users.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> news:DBBAA319-8FCA-484B-8706-4B857BD6FE8C@.microsoft.com...
>
>
|||Can anyone out there give me more suggestions ?
Regards,
Johnny
"Johnny" wrote:
[vbcol=seagreen]
> sp_change_users_login cannot be used with Microsoft Windows NT? users. I
> cannot remap the database users to the new windows logins.
> e.g sp_change_users_login update_one,'user1','domain\users1'
> But if I drop the database user before adding the user to the windows login,
> the user will lose all the roles and permissions. Even the scripting is not
> very useful because it isn't grouped by users.
> Help please.
> Johnny
>
> "Tibor Karaszi" wrote:
|||Take a look at sp_help_revlogin.
http://support.microsoft.com/kb/246133
-oj
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:85513978-7C52-4AC0-8A12-CB9A9636E903@.microsoft.com...[vbcol=seagreen]
> Can anyone out there give me more suggestions ?
> Regards,
> Johnny
> "Johnny" wrote:

Convert from logins from SQL to Windows authentication

My company is converting from Novell to Windows (Active Directory)
environment. One of the SQL 200 server has more than 550 logins/users and
they are using SQL authentication.
What is the quickest way to convert all of them to Windows authentication
and keeping them to have same permission to databases & database roles (e.g
script etc). Manually recreate them is a big task.
Regards,
JohnnyAnyone can give me some advices please ?
"Johnny" wrote:

> My company is converting from Novell to Windows (Active Directory)
> environment. One of the SQL 200 server has more than 550 logins/users an
d
> they are using SQL authentication.
> What is the quickest way to convert all of them to Windows authentication
> and keeping them to have same permission to databases & database roles (e.
g
> script etc). Manually recreate them is a big task.
> Regards,
> Johnny|||Since you cannot convert a login, you have to add a new, re-map the users to
the new login, then
drop the old login. Check out sp_change_users_logins for re-mapping users.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:DBBAA319-8FCA-484B-8706-4B857BD6FE8C@.microsoft.com...[vbcol=seagreen]
> Anyone can give me some advices please ?
> "Johnny" wrote:
>|||sp_change_users_login cannot be used with Microsoft Windows NT? users. I
cannot remap the database users to the new windows logins.
e.g sp_change_users_login update_one,'user1','domain\users1'
But if I drop the database user before adding the user to the windows login,
the user will lose all the roles and permissions. Even the scripting is not
very useful because it isn't grouped by users.
Help please.
Johnny
"Tibor Karaszi" wrote:

> Since you cannot convert a login, you have to add a new, re-map the users
to the new login, then
> drop the old login. Check out sp_change_users_logins for re-mapping users.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> news:DBBAA319-8FCA-484B-8706-4B857BD6FE8C@.microsoft.com...
>
>|||Can anyone out there give me more suggestions ?
Regards,
Johnny
"Johnny" wrote:
[vbcol=seagreen]
> sp_change_users_login cannot be used with Microsoft Windows NT? users. I
> cannot remap the database users to the new windows logins.
> e.g sp_change_users_login update_one,'user1','domain\users1'
> But if I drop the database user before adding the user to the windows logi
n,
> the user will lose all the roles and permissions. Even the scripting is n
ot
> very useful because it isn't grouped by users.
> Help please.
> Johnny
>
> "Tibor Karaszi" wrote:
>|||Take a look at sp_help_revlogin.
http://support.microsoft.com/kb/246133
-oj
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:85513978-7C52-4AC0-8A12-CB9A9636E903@.microsoft.com...[vbcol=seagreen]
> Can anyone out there give me more suggestions ?
> Regards,
> Johnny
> "Johnny" wrote:
>

Convert from logins from SQL to Windows authentication

My company is converting from Novell to Windows (Active Directory)
environment. One of the SQL 200 server has more than 550 logins/users and
they are using SQL authentication.
What is the quickest way to convert all of them to Windows authentication
and keeping them to have same permission to databases & database roles (e.g
script etc). Manually recreate them is a big task.
Regards,
JohnnyAnyone can give me some advices please ?
"Johnny" wrote:
> My company is converting from Novell to Windows (Active Directory)
> environment. One of the SQL 200 server has more than 550 logins/users and
> they are using SQL authentication.
> What is the quickest way to convert all of them to Windows authentication
> and keeping them to have same permission to databases & database roles (e.g
> script etc). Manually recreate them is a big task.
> Regards,
> Johnny|||Since you cannot convert a login, you have to add a new, re-map the users to the new login, then
drop the old login. Check out sp_change_users_logins for re-mapping users.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:DBBAA319-8FCA-484B-8706-4B857BD6FE8C@.microsoft.com...
> Anyone can give me some advices please ?
> "Johnny" wrote:
>> My company is converting from Novell to Windows (Active Directory)
>> environment. One of the SQL 200 server has more than 550 logins/users and
>> they are using SQL authentication.
>> What is the quickest way to convert all of them to Windows authentication
>> and keeping them to have same permission to databases & database roles (e.g
>> script etc). Manually recreate them is a big task.
>> Regards,
>> Johnny|||sp_change_users_login cannot be used with Microsoft Windows NT® users. I
cannot remap the database users to the new windows logins.
e.g sp_change_users_login update_one,'user1','domain\users1'
But if I drop the database user before adding the user to the windows login,
the user will lose all the roles and permissions. Even the scripting is not
very useful because it isn't grouped by users.
Help please.
Johnny
"Tibor Karaszi" wrote:
> Since you cannot convert a login, you have to add a new, re-map the users to the new login, then
> drop the old login. Check out sp_change_users_logins for re-mapping users.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> news:DBBAA319-8FCA-484B-8706-4B857BD6FE8C@.microsoft.com...
> > Anyone can give me some advices please ?
> >
> > "Johnny" wrote:
> >
> >> My company is converting from Novell to Windows (Active Directory)
> >> environment. One of the SQL 200 server has more than 550 logins/users and
> >> they are using SQL authentication.
> >>
> >> What is the quickest way to convert all of them to Windows authentication
> >> and keeping them to have same permission to databases & database roles (e.g
> >> script etc). Manually recreate them is a big task.
> >>
> >> Regards,
> >> Johnny
>
>|||Can anyone out there give me more suggestions ?
Regards,
Johnny
"Johnny" wrote:
> sp_change_users_login cannot be used with Microsoft Windows NT® users. I
> cannot remap the database users to the new windows logins.
> e.g sp_change_users_login update_one,'user1','domain\users1'
> But if I drop the database user before adding the user to the windows login,
> the user will lose all the roles and permissions. Even the scripting is not
> very useful because it isn't grouped by users.
> Help please.
> Johnny
>
> "Tibor Karaszi" wrote:
> > Since you cannot convert a login, you have to add a new, re-map the users to the new login, then
> > drop the old login. Check out sp_change_users_logins for re-mapping users.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> > news:DBBAA319-8FCA-484B-8706-4B857BD6FE8C@.microsoft.com...
> > > Anyone can give me some advices please ?
> > >
> > > "Johnny" wrote:
> > >
> > >> My company is converting from Novell to Windows (Active Directory)
> > >> environment. One of the SQL 200 server has more than 550 logins/users and
> > >> they are using SQL authentication.
> > >>
> > >> What is the quickest way to convert all of them to Windows authentication
> > >> and keeping them to have same permission to databases & database roles (e.g
> > >> script etc). Manually recreate them is a big task.
> > >>
> > >> Regards,
> > >> Johnny
> >
> >
> >|||Take a look at sp_help_revlogin.
http://support.microsoft.com/kb/246133
-oj
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:85513978-7C52-4AC0-8A12-CB9A9636E903@.microsoft.com...
> Can anyone out there give me more suggestions ?
> Regards,
> Johnny
> "Johnny" wrote:
>> sp_change_users_login cannot be used with Microsoft Windows NT® users. I
>> cannot remap the database users to the new windows logins.
>> e.g sp_change_users_login update_one,'user1','domain\users1'
>> But if I drop the database user before adding the user to the windows
>> login,
>> the user will lose all the roles and permissions. Even the scripting is
>> not
>> very useful because it isn't grouped by users.
>> Help please.
>> Johnny
>>
>> "Tibor Karaszi" wrote:
>> > Since you cannot convert a login, you have to add a new, re-map the
>> > users to the new login, then
>> > drop the old login. Check out sp_change_users_logins for re-mapping
>> > users.
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://www.solidqualitylearning.com/
>> >
>> >
>> > "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
>> > news:DBBAA319-8FCA-484B-8706-4B857BD6FE8C@.microsoft.com...
>> > > Anyone can give me some advices please ?
>> > >
>> > > "Johnny" wrote:
>> > >
>> > >> My company is converting from Novell to Windows (Active Directory)
>> > >> environment. One of the SQL 200 server has more than 550
>> > >> logins/users and
>> > >> they are using SQL authentication.
>> > >>
>> > >> What is the quickest way to convert all of them to Windows
>> > >> authentication
>> > >> and keeping them to have same permission to databases & database
>> > >> roles (e.g
>> > >> script etc). Manually recreate them is a big task.
>> > >>
>> > >> Regards,
>> > >> Johnny
>> >
>> >
>> >