ID Creation/ASP/SQL Server

Using SQL Server with ASP for an intranet website.
I'm generating a ID using a user defined function. (getting the Maximum and
adding 1 to get a new ID )
During testing i have got 2 similar ids (example: 31000,31000) for 2 records
when there is concurrent access to a website
here i need to track the ID generated,manipulate and convert into string
format (example: U31000_CTF_FILE)
so im generating it thru function.
is there any other best way to generate & track IDs.
Note: im not using any stored procedures moreover i want to use normal ASP
code only
please help
-dnkHi
You can try somethimg like that
CREATE PROC spNewIds
AS
DECLARE @.new_id INT
BEGIN TRANSACTION
SELECT @.new_id =COALESCE(MAX(Id)+1,0) FROM MyTable WITH (UPDLOCK,HOLDLOCK)
INSERT INTO AnotherTable (colname) VALUES (@.new_id )
COMMIT TRANSACTION
"DNKMCA" <dnk@.msn.com> wrote in message
news:uPeCLiOFGHA.916@.TK2MSFTNGP10.phx.gbl...
> Using SQL Server with ASP for an intranet website.
> I'm generating a ID using a user defined function. (getting the Maximum
> and
> adding 1 to get a new ID )
> During testing i have got 2 similar ids (example: 31000,31000) for 2
> records
> when there is concurrent access to a website
> here i need to track the ID generated,manipulate and convert into string
> format (example: U31000_CTF_FILE)
> so im generating it thru function.
> is there any other best way to generate & track IDs.
> Note: im not using any stored procedures moreover i want to use normal
> ASP
> code only
> please help
> -dnk
>|||Why don't you just have a dummy table with an IDENTITY column?
CREATE TABLE dbo.MyTable(ID INT IDENTITY(1,1))
Now In the ASP code,
set rs = conn.execute("SET NOCOUNT ON; INSERT dbo.MyTable DEFAULT VALUES;
SELECT SCOPE_IDENTITY()")
Response.Write rs(0)
Though I'm not sure I understand the objection to using stored procedures,
or what you mean by "track IDs"...
"DNKMCA" <dnk@.msn.com> wrote in message
news:uPeCLiOFGHA.916@.TK2MSFTNGP10.phx.gbl...
> Using SQL Server with ASP for an intranet website.
> I'm generating a ID using a user defined function. (getting the Maximum
> and
> adding 1 to get a new ID )
> During testing i have got 2 similar ids (example: 31000,31000) for 2
> records
> when there is concurrent access to a website
> here i need to track the ID generated,manipulate and convert into string
> format (example: U31000_CTF_FILE)
> so im generating it thru function.
> is there any other best way to generate & track IDs.
> Note: im not using any stored procedures moreover i want to use normal
> ASP
> code only
> please help
> -dnk
>sql

IBMDASQL OLEDB linked server "access denied" using Windows Authentication

I have a linked server defined on a SQL Server 2005 SP2 standard edition server using the IBMDASQL OLEDB driver. The linked server has been defined and working for months when used from a SQL Server authenticated session. I started converting our developers to Windows Authentication and access to the linked server is denied when used from a Windows Authenticated session. Here are the error messages:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "IBMDASQL" for linked server "DB2ARUBA" reported an error. Access denied.

Msg 7350, Level 16, State 2, Line 1

Cannot get the column information from OLE DB provider "IBMDASQL" for linked server "DB2ARUBA".

The linked server security is defined to login to the IBM iSeries using a fixed user and password regardless of the login using the linked server.

In order to analyze the problem, I created one sysadmin SQL Server authenticated login and one sysadmin Windows Authenticated login. The Windows Authenticated login refers to a domain login having administrator priviledges on the local SQL Server box.

So, when I connect to SQL Server using the SQL Authenticated login, a query against the linked server works. When I connect to SQL Server using the Windows Authenticated login, the exact same query against the linked server fails with the messages above.

Does anyone know why?

I had the same problem and here is how I fixed it:

Following the advice from another post, ensure "Allow inprocess" is selected for the linked server provider:

Can you make sure that you have allowed inproc access in SQL Server for the
MSOLAP provider?:
1.. In SQL Mgmt Studio, connect to the SQL Server Database Engine and go
to Server Objects->Linked Server->Providers in the Object Explorer.
2.. Right-click on MSOLAP and select Properties. Check the "Allow
inprocess" box in the Provider Options dialog that comes up
--

Ensure the ID has permission to use the linked server (try with sysadmin for starters)

Ensure the security tab of the linked server is using an AS400 ID and password

I also noticed that my default database as my login ID was tempdb. If I changed my context to master, it ran without giving me the permission error.

Hope this helps!

|||

Lexner55 -

That fixed it for me. Thanks very much. FYI, I am using the IBMDASQL provider.

|||

I am actually not using the IBMDASQL driver anymore for my servers as I noticed there was quite a difference in response time versus using ODBC (which as a side note is not available in 64 bit servers).

I tested the difference between the two drivers by doing a select * on a large table and compared the time it took to complete using the IBMDASQL driver versus the ODBC driver. It took the ODBC driver a little over 3 minutes to call the 91000 rows whereas it took the IBMDASQL driver over 7 minutes running the same query. With a 64 bit server, I am using the OLE for DB2 driver provided by Microsoft and it's comparable to the ODBC driver.

For the record, I am not saying IBMDASQL should not be used. If you do not need to call large data sets from the AS400, this driver will be fine (in production scenarios as we all know, it's not best practice to select * anyways). It could also be an issue with how our AS400 server is configured or some other setting I may be missing. Regardless, you might want to run some tests and compare the different drivers.

I'm glad to hear you got it working though. Thanks for letting me know.

|||

Lexner55 -

We had similar performance issues when we converted from ODBC to OLE using the IBMDASQL driver. We got around the major performance issues by using OPENQUERY instead of direct SQL, although we are still not very happy with the performance. I believe the reason for the improvement using OPENQUERY is that OPENQUERY ships the query to DB2 and lets DB2 optimize and execute it whereas a direct query ( using 4 part names ) pulls all tables involved over to SQL Server and then optimizes and exeutes the query on SQL Server. I could be wrong about that but that's the way it looks to me.

In our 64 bit environment, since the ODBC driver is no longer available, we are using the IBMDASQL OLE driver. Where did you get the OLE for DB2 driver that you referred to? I don't see it in my list of providers when I look in Management Studio.

|||

Lexner55 -

I found the DB2OLEDB driver in the feature pack. So, thank again.

IBMDASQL OLEDB linked server "access denied" using Windows Authentication

I have a linked server defined on a SQL Server 2005 SP2 standard edition server using the IBMDASQL OLEDB driver. The linked server has been defined and working for months when used from a SQL Server authenticated session. I started converting our developers to Windows Authentication and access to the linked server is denied when used from a Windows Authenticated session. Here are the error messages:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "IBMDASQL" for linked server "DB2ARUBA" reported an error. Access denied.

Msg 7350, Level 16, State 2, Line 1

Cannot get the column information from OLE DB provider "IBMDASQL" for linked server "DB2ARUBA".

The linked server security is defined to login to the IBM iSeries using a fixed user and password regardless of the login using the linked server.

In order to analyze the problem, I created one sysadmin SQL Server authenticated login and one sysadmin Windows Authenticated login. The Windows Authenticated login refers to a domain login having administrator priviledges on the local SQL Server box.

So, when I connect to SQL Server using the SQL Authenticated login, a query against the linked server works. When I connect to SQL Server using the Windows Authenticated login, the exact same query against the linked server fails with the messages above.

Does anyone know why?

I had the same problem and here is how I fixed it:

Following the advice from another post, ensure "Allow inprocess" is selected for the linked server provider:

Can you make sure that you have allowed inproc access in SQL Server for the
MSOLAP provider?:
1.. In SQL Mgmt Studio, connect to the SQL Server Database Engine and go
to Server Objects->Linked Server->Providers in the Object Explorer.
2.. Right-click on MSOLAP and select Properties. Check the "Allow
inprocess" box in the Provider Options dialog that comes up
--

Ensure the ID has permission to use the linked server (try with sysadmin for starters)

Ensure the security tab of the linked server is using an AS400 ID and password

I also noticed that my default database as my login ID was tempdb. If I changed my context to master, it ran without giving me the permission error.

Hope this helps!

|||

Lexner55 -

That fixed it for me. Thanks very much. FYI, I am using the IBMDASQL provider.

|||

I am actually not using the IBMDASQL driver anymore for my servers as I noticed there was quite a difference in response time versus using ODBC (which as a side note is not available in 64 bit servers).

I tested the difference between the two drivers by doing a select * on a large table and compared the time it took to complete using the IBMDASQL driver versus the ODBC driver. It took the ODBC driver a little over 3 minutes to call the 91000 rows whereas it took the IBMDASQL driver over 7 minutes running the same query. With a 64 bit server, I am using the OLE for DB2 driver provided by Microsoft and it's comparable to the ODBC driver.

For the record, I am not saying IBMDASQL should not be used. If you do not need to call large data sets from the AS400, this driver will be fine (in production scenarios as we all know, it's not best practice to select * anyways). It could also be an issue with how our AS400 server is configured or some other setting I may be missing. Regardless, you might want to run some tests and compare the different drivers.

I'm glad to hear you got it working though. Thanks for letting me know.

|||

Lexner55 -

We had similar performance issues when we converted from ODBC to OLE using the IBMDASQL driver. We got around the major performance issues by using OPENQUERY instead of direct SQL, although we are still not very happy with the performance. I believe the reason for the improvement using OPENQUERY is that OPENQUERY ships the query to DB2 and lets DB2 optimize and execute it whereas a direct query ( using 4 part names ) pulls all tables involved over to SQL Server and then optimizes and exeutes the query on SQL Server. I could be wrong about that but that's the way it looks to me.

In our 64 bit environment, since the ODBC driver is no longer available, we are using the IBMDASQL OLE driver. Where did you get the OLE for DB2 driver that you referred to? I don't see it in my list of providers when I look in Management Studio.

|||

Lexner55 -

I found the DB2OLEDB driver in the feature pack. So, thank again.

I need to set up a ADDITIONAL server with unique userid/password. How?

I already have a SQL Server defined. I want to do some testing using a
different userid/password for the server. Reason: I am emulating another
site for testing purposes. Since the userid/password seems to be at the
Server level, and not a database level, it looks like I need to define
another server with a different userid/password combo. Is this right or am
I missing the boat?
(Yes, I need SQL Server security. I can't get by with Windows-only)
TIA,
Larry WoodsLogins are at the server level.
Users are at the database level.
"Larry Woods" <larry@.NOSPAMlwoods.com> wrote in message
news:epwQ74V%23DHA.2808@.TK2MSFTNGP10.phx.gbl...
> I already have a SQL Server defined. I want to do some testing using a
> different userid/password for the server. Reason: I am emulating another
> site for testing purposes. Since the userid/password seems to be at the
> Server level, and not a database level, it looks like I need to define
> another server with a different userid/password combo. Is this right or
am
> I missing the boat?
> (Yes, I need SQL Server security. I can't get by with Windows-only)
> TIA,
> Larry Woods
>|||> Since the userid/password seems to be at the
> Server level, and not a database level, it looks like I need to define
> another server with a different userid/password combo.
You can create the desired login using a script like the one below.
EXEC sp_addlogin 'MyLogin', 'MyPassword'
USE MyDatabase
EXEC sp_adduser 'MyLogin'
--if role-based security
EXEC sp_addrolemember 'MyRole', 'MyLogin'
Hope this helps.
Dan Guzman
SQL Server MVP
"Larry Woods" <larry@.NOSPAMlwoods.com> wrote in message
news:epwQ74V%23DHA.2808@.TK2MSFTNGP10.phx.gbl...
> I already have a SQL Server defined. I want to do some testing using a
> different userid/password for the server. Reason: I am emulating another
> site for testing purposes. Since the userid/password seems to be at the
> Server level, and not a database level, it looks like I need to define
> another server with a different userid/password combo. Is this right or
am
> I missing the boat?
> (Yes, I need SQL Server security. I can't get by with Windows-only)
> TIA,
> Larry Woods
>|||O.K. Here is my situation. The ONLY SQL Server that I have defined was
defined with Windows-only security; therefore no SQL Server security. Now,
I need a SQL Server defined that has both Windows and SQL Server security.
That's why I "think" that I need to define a second server.
Make sense?
TIA,
Larry Woods
"Paul Wehland" <pwehland@.eMeSeN.COM> wrote in message
news:u4uwo5d%23DHA.4084@.tk2msftngp13.phx.gbl...
> Logins are at the server level.
> Users are at the database level.
>
> "Larry Woods" <larry@.NOSPAMlwoods.com> wrote in message
> news:epwQ74V%23DHA.2808@.TK2MSFTNGP10.phx.gbl...
another
> am
>|||You can switch down from Windows only to Mixed Mode for your current server,
create an SQL Server login and do testings for that login, as you wish. Just
make sure you consider security in doing that, and make sure that you set a
password for sa before switching down. :-)
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Larry Woods" <larry@.NOSPAMlwoods.com> wrote in message
news:e14MnO7%23DHA.2860@.tk2msftngp13.phx.gbl...
> O.K. Here is my situation. The ONLY SQL Server that I have defined was
> defined with Windows-only security; therefore no SQL Server security.
Now,
> I need a SQL Server defined that has both Windows and SQL Server security.
> That's why I "think" that I need to define a second server.
> Make sense?
> TIA,
> Larry Woods
> "Paul Wehland" <pwehland@.eMeSeN.COM> wrote in message
> news:u4uwo5d%23DHA.4084@.tk2msftngp13.phx.gbl...
a
> another
the
define
or
>

I need to set up a ADDITIONAL server with unique userid/password. How?

I already have a SQL Server defined. I want to do some testing using a
different userid/password for the server. Reason: I am emulating another
site for testing purposes. Since the userid/password seems to be at the
Server level, and not a database level, it looks like I need to define
another server with a different userid/password combo. Is this right or am
I missing the boat?
(Yes, I need SQL Server security. I can't get by with Windows-only)
TIA,
Larry WoodsLogins are at the server level.
Users are at the database level.
"Larry Woods" <larry@.NOSPAMlwoods.com> wrote in message
news:epwQ74V%23DHA.2808@.TK2MSFTNGP10.phx.gbl...
> I already have a SQL Server defined. I want to do some testing using a
> different userid/password for the server. Reason: I am emulating another
> site for testing purposes. Since the userid/password seems to be at the
> Server level, and not a database level, it looks like I need to define
> another server with a different userid/password combo. Is this right or
am
> I missing the boat?
> (Yes, I need SQL Server security. I can't get by with Windows-only)
> TIA,
> Larry Woods
>|||> Since the userid/password seems to be at the
> Server level, and not a database level, it looks like I need to define
> another server with a different userid/password combo.
You can create the desired login using a script like the one below.
EXEC sp_addlogin 'MyLogin', 'MyPassword'
USE MyDatabase
EXEC sp_adduser 'MyLogin'
--if role-based security
EXEC sp_addrolemember 'MyRole', 'MyLogin'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Larry Woods" <larry@.NOSPAMlwoods.com> wrote in message
news:epwQ74V%23DHA.2808@.TK2MSFTNGP10.phx.gbl...
> I already have a SQL Server defined. I want to do some testing using a
> different userid/password for the server. Reason: I am emulating another
> site for testing purposes. Since the userid/password seems to be at the
> Server level, and not a database level, it looks like I need to define
> another server with a different userid/password combo. Is this right or
am
> I missing the boat?
> (Yes, I need SQL Server security. I can't get by with Windows-only)
> TIA,
> Larry Woods
>|||O.K. Here is my situation. The ONLY SQL Server that I have defined was
defined with Windows-only security; therefore no SQL Server security. Now,
I need a SQL Server defined that has both Windows and SQL Server security.
That's why I "think" that I need to define a second server.
Make sense?
TIA,
Larry Woods
"Paul Wehland" <pwehland@.eMeSeN.COM> wrote in message
news:u4uwo5d%23DHA.4084@.tk2msftngp13.phx.gbl...
> Logins are at the server level.
> Users are at the database level.
>
> "Larry Woods" <larry@.NOSPAMlwoods.com> wrote in message
> news:epwQ74V%23DHA.2808@.TK2MSFTNGP10.phx.gbl...
> > I already have a SQL Server defined. I want to do some testing using a
> > different userid/password for the server. Reason: I am emulating
another
> > site for testing purposes. Since the userid/password seems to be at the
> > Server level, and not a database level, it looks like I need to define
> > another server with a different userid/password combo. Is this right or
> am
> > I missing the boat?
> >
> > (Yes, I need SQL Server security. I can't get by with Windows-only)
> >
> > TIA,
> >
> > Larry Woods
> >
> >
>|||You can switch down from Windows only to Mixed Mode for your current server,
create an SQL Server login and do testings for that login, as you wish. Just
make sure you consider security in doing that, and make sure that you set a
password for sa before switching down. :-)
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Larry Woods" <larry@.NOSPAMlwoods.com> wrote in message
news:e14MnO7%23DHA.2860@.tk2msftngp13.phx.gbl...
> O.K. Here is my situation. The ONLY SQL Server that I have defined was
> defined with Windows-only security; therefore no SQL Server security.
Now,
> I need a SQL Server defined that has both Windows and SQL Server security.
> That's why I "think" that I need to define a second server.
> Make sense?
> TIA,
> Larry Woods
> "Paul Wehland" <pwehland@.eMeSeN.COM> wrote in message
> news:u4uwo5d%23DHA.4084@.tk2msftngp13.phx.gbl...
> > Logins are at the server level.
> > Users are at the database level.
> >
> >
> > "Larry Woods" <larry@.NOSPAMlwoods.com> wrote in message
> > news:epwQ74V%23DHA.2808@.TK2MSFTNGP10.phx.gbl...
> > > I already have a SQL Server defined. I want to do some testing using
a
> > > different userid/password for the server. Reason: I am emulating
> another
> > > site for testing purposes. Since the userid/password seems to be at
the
> > > Server level, and not a database level, it looks like I need to
define
> > > another server with a different userid/password combo. Is this right
or
> > am
> > > I missing the boat?
> > >
> > > (Yes, I need SQL Server security. I can't get by with Windows-only)
> > >
> > > TIA,
> > >
> > > Larry Woods
> > >
> > >
> >
> >
>