Exclusive Lock Table like Oracle?

In Oracle, I can do "LOCK TABLE table IN EXCLUSIVE MODE" if I want to
hold a table, is there an equivalent in MSSQL?
I have a table of "next available id numbers", and when one is
requested, I want to lock the table, get the next one, delete it, and
then unlock the table.
Thanks.What version are you using?
Lookup LOCK hints in the BOL
<bradwiseathome@.hotmail.com> wrote in message
news:1145280771.375136.20130@.j33g2000cwa.googlegroups.com...
> In Oracle, I can do "LOCK TABLE table IN EXCLUSIVE MODE" if I want to
> hold a table, is there an equivalent in MSSQL?
> I have a table of "next available id numbers", and when one is
> requested, I want to lock the table, get the next one, delete it, and
> then unlock the table.
> Thanks.
>|||I believe you can do this with table hints.
BEGIN TRANSACTION
SELECT ... WITH TABLOCK
DELETE ...
END TRANSACTION
You can find samples from the BOL.
Mel

Exception comes from where?

Being new to SQLServer, I may be doing something very basic wrong, but here's
my problem:
Java servlet code is calling a MSSQL stored procedure, returning this error
message:
Main catch exception java.sql.SQLException: [Microsoft][SQLServer 2000
Driver for JDBC][SQLServer]Line 1: Incorrect syntax near 'insert_person'
Java code as follows:
public String dbInsertPerson(Connection conn) throws Exception {
String curErrorId = "";
CallableStatement procCall = null;
String procString = "";
// Make sure no errors have occurred.
if (curErrorId.equals("")) {
try {
procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
procCall = conn.prepareCall(procString);
procCall.setString(1, this.lastName);
procCall.setString(2, this.firstName);
procCall.setString(3, this.middleName);
procCall.setString(4, this.preferredName);
procCall.setString(5, this.dateOfBirth);
procCall.setString(6, this.gender);
procCall.setString(7, this.emailAddress);
procCall.setString(8, this.highSchoolName);
procCall.setString(9, this.highSchoolGradYear);
procCall.executeUpdate();
}
catch (SQLException e) {
curErrorId = "100";
throw e;
}
catch (Exception e) {
curErrorId = "101";
throw e;
}
finally {
if (procCall != null) procCall.close();
}
} // end if
return curErrorId;
}
Stored procedure code as follows:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure insert_person
@.lastName varchar(16),
@.firstName varchar(16),
@.middleName varchar(16),
@.preferredName varchar(16),
@.dateOfBirth varchar(30),
@.gender varchar(1),
@.emailAddress varchar(25),
@.highSchoolName varchar(20),
@.highSchoolGradYear varchar(4)
AS
insert into people (LastName, FirstName, CreationDate, LastUpdateDate,
MiddleName, PreferredName, DateOfBirth, Gender,
EmailAddress, HighSchoolName, HighSchoolGradYear)
values (@.lastName, @.firstName, getdate(), getDate(), @.middleName,
@.preferredName, convert(datetime, @.dateOfBirth), @.gender,
@.emailAddress, @.highSchoolName, @.highSchoolGradYear)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Would one of you non-newbies be so kind as to straighten me out?
I figure it's my ignorance/syntax issue causing some problem, whether in the
java
callable statement syntax or the stored procedure itself.
Also, a pointer to any documentation that might help me resolve future issues
on my own would be much appreciated.
I think you need to do "insert person" instead of "insert_person" -
the _ makes it a single unrecognized word.
- dave
On Thu, 8 Sep 2005 09:10:05 -0700, "PJ Pugh"
<msee92_spamfree@.hotmail.com> wrote:

>Being new to SQLServer, I may be doing something very basic wrong, but here's
>my problem:
>Java servlet code is calling a MSSQL stored procedure, returning this error
>message:
>Main catch exception java.sql.SQLException: [Microsoft][SQLServer 2000
>Driver for JDBC][SQLServer]Line 1: Incorrect syntax near 'insert_person'
>Java code as follows:
>public String dbInsertPerson(Connection conn) throws Exception {
> String curErrorId = "";
> CallableStatement procCall = null;
> String procString = "";
> // Make sure no errors have occurred.
> if (curErrorId.equals("")) {
>try {
> procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
> procCall = conn.prepareCall(procString);
> procCall.setString(1, this.lastName);
> procCall.setString(2, this.firstName);
> procCall.setString(3, this.middleName);
> procCall.setString(4, this.preferredName);
> procCall.setString(5, this.dateOfBirth);
> procCall.setString(6, this.gender);
> procCall.setString(7, this.emailAddress);
> procCall.setString(8, this.highSchoolName);
> procCall.setString(9, this.highSchoolGradYear);
> procCall.executeUpdate();
>}
>catch (SQLException e) {
>curErrorId = "100";
>throw e;
>}
>catch (Exception e) {
>curErrorId = "101";
>throw e;
>}
>finally {
>if (procCall != null) procCall.close();
>}
> } // end if
> return curErrorId;
>}
>Stored procedure code as follows:
>SET QUOTED_IDENTIFIER ON
>GO
>SET ANSI_NULLS ON
>GO
>CREATE procedure insert_person
> @.lastName varchar(16),
> @.firstName varchar(16),
> @.middleName varchar(16),
> @.preferredName varchar(16),
> @.dateOfBirth varchar(30),
> @.gender varchar(1),
> @.emailAddress varchar(25),
> @.highSchoolName varchar(20),
> @.highSchoolGradYear varchar(4)
>AS
> insert into people (LastName, FirstName, CreationDate, LastUpdateDate,
> MiddleName, PreferredName, DateOfBirth, Gender,
> EmailAddress, HighSchoolName, HighSchoolGradYear)
> values (@.lastName, @.firstName, getdate(), getDate(), @.middleName,
> @.preferredName, convert(datetime, @.dateOfBirth), @.gender,
> @.emailAddress, @.highSchoolName, @.highSchoolGradYear)
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS ON
>GO
>
>Would one of you non-newbies be so kind as to straighten me out?
>I figure it's my ignorance/syntax issue causing some problem, whether in the
>java
>callable statement syntax or the stored procedure itself.
>Also, a pointer to any documentation that might help me resolve future issues
>on my own would be much appreciated.
>
david@.at-at-at@.windward.dot.dot.net
Windward Reports -- http://www.WindwardReports.com
Page 2 Stage -- http://www.Page2Stage.com
Enemy Nations -- http://www.EnemyNations.com
me -- http://dave.thielen.com
Barbie Science Fair -- http://www.BarbieScienceFair.info
(yes I have lots of links)
|||PJ Pugh wrote:

> Being new to SQLServer, I may be doing something very basic wrong, but here's
> my problem:
> Java servlet code is calling a MSSQL stored procedure, returning this error
> message:
> Main catch exception java.sql.SQLException: [Microsoft][SQLServer 2000
> Driver for JDBC][SQLServer]Line 1: Incorrect syntax near 'insert_person'
I was unable to duplicate the problem. Here's my code. I pasted yours in
and just changed the parameters to strings:
Properties props = new Properties();
Driver d = new com.microsoft.jdbc.sqlserver.SQLServerDriver();
props.put("user", "joe");
props.put("password", "joe");
c = d.connect("jdbc:microsoft:sqlserver://joe:1433", props );
DatabaseMetaData dd = c.getMetaData();
System.out.println("Driver version is " + dd.getDriverVersion() );
String procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
CallableStatement procCall = c.prepareCall(procString);
procCall.setString(1, "this.lastName");
procCall.setString(2, "this.firstName");
procCall.setString(3, "this.middleName");
procCall.setString(4, "this.preferredName");
procCall.setString(5, "this.dateOfBirth");
procCall.setString(6, "this.gender");
procCall.setString(7, "this.emailAddress");
procCall.setString(8, "this.highSchoolName");
procCall.setString(9, "this.highSchoolGradYear");
procCall.executeUpdate();
I get what I'd expect (because I have no procedure named insert_person),
but in order to get your problem, it would have been the SQL parser
that threw an exception, which would be before the query plan was
being created:
C:\ms_driver\examples>java foo
Driver version is 2.2.0037
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Could not find stored procedure 'insert_person'.
at
com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Ljava.lang.String;Ljava.lang.String;I)Ljava.s ql.SQLException;(Unknown Source)
at
com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Ljava.sql.SQLException;II[Ljava.lang.String;Ljav a.lang.String;I)Ljava.sql.SQLException;(Unknown
Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sErrorToken()V(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReplyToken(BLcom.microsoft.jdbc.base.BaseWarnings ;)Z(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.pro cessReplyToken(BLcom.microsoft.jdbc.base.BaseWarni ngs;)Z(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Lcom.microsoft.jdbc.base.BaseWarnings;)V(Un known Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType()I(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(I)V(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Z)V(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.post ImplExecute(Z)V(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecut e()V(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeUpdat eInternal()I(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.exec uteUpdate()I(Unknown Source)
at foo.main(foo.java:40)

> Java code as follows:
> public String dbInsertPerson(Connection conn) throws Exception {
> String curErrorId = "";
> CallableStatement procCall = null;
> String procString = "";
> // Make sure no errors have occurred.
> if (curErrorId.equals("")) {
> try {
> procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
> procCall = conn.prepareCall(procString);
> procCall.setString(1, this.lastName);
> procCall.setString(2, this.firstName);
> procCall.setString(3, this.middleName);
> procCall.setString(4, this.preferredName);
> procCall.setString(5, this.dateOfBirth);
> procCall.setString(6, this.gender);
> procCall.setString(7, this.emailAddress);
> procCall.setString(8, this.highSchoolName);
> procCall.setString(9, this.highSchoolGradYear);
> procCall.executeUpdate();
> }
> catch (SQLException e) {
> curErrorId = "100";
> throw e;
> }
> catch (Exception e) {
> curErrorId = "101";
> throw e;
> }
> finally {
> if (procCall != null) procCall.close();
> }
> } // end if
> return curErrorId;
> }
> Stored procedure code as follows:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE procedure insert_person
> @.lastName varchar(16),
> @.firstName varchar(16),
> @.middleName varchar(16),
> @.preferredName varchar(16),
> @.dateOfBirth varchar(30),
> @.gender varchar(1),
> @.emailAddress varchar(25),
> @.highSchoolName varchar(20),
> @.highSchoolGradYear varchar(4)
> AS
> insert into people (LastName, FirstName, CreationDate, LastUpdateDate,
> MiddleName, PreferredName, DateOfBirth, Gender,
> EmailAddress, HighSchoolName, HighSchoolGradYear)
> values (@.lastName, @.firstName, getdate(), getDate(), @.middleName,
> @.preferredName, convert(datetime, @.dateOfBirth), @.gender,
> @.emailAddress, @.highSchoolName, @.highSchoolGradYear)
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> Would one of you non-newbies be so kind as to straighten me out?
> I figure it's my ignorance/syntax issue causing some problem, whether in the
> java
> callable statement syntax or the stored procedure itself.
> Also, a pointer to any documentation that might help me resolve future issues
> on my own would be much appreciated.
>
|||PJ Pugh wrote:

> Being new to SQLServer, I may be doing something very basic wrong, but here's
> my problem:
> Java servlet code is calling a MSSQL stored procedure, returning this error
> message:
> Main catch exception java.sql.SQLException: [Microsoft][SQLServer 2000
> Driver for JDBC][SQLServer]Line 1: Incorrect syntax near 'insert_person'
In fact, not only was I unable to duplicate the problem,
here;s a program with your execute() code pasted in, that
creates the table and procedure and runs without complaint.
Joe Weinstein at BEA Systems

> Java code as follows:
> public String dbInsertPerson(Connection conn) throws Exception {
> String curErrorId = "";
> CallableStatement procCall = null;
> String procString = "";
> // Make sure no errors have occurred.
> if (curErrorId.equals("")) {
> try {
> procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
> procCall = conn.prepareCall(procString);
> procCall.setString(1, this.lastName);
> procCall.setString(2, this.firstName);
> procCall.setString(3, this.middleName);
> procCall.setString(4, this.preferredName);
> procCall.setString(5, this.dateOfBirth);
> procCall.setString(6, this.gender);
> procCall.setString(7, this.emailAddress);
> procCall.setString(8, this.highSchoolName);
> procCall.setString(9, this.highSchoolGradYear);
> procCall.executeUpdate();
> }
> catch (SQLException e) {
> curErrorId = "100";
> throw e;
> }
> catch (Exception e) {
> curErrorId = "101";
> throw e;
> }
> finally {
> if (procCall != null) procCall.close();
> }
> } // end if
> return curErrorId;
> }
> Stored procedure code as follows:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE procedure insert_person
> @.lastName varchar(16),
> @.firstName varchar(16),
> @.middleName varchar(16),
> @.preferredName varchar(16),
> @.dateOfBirth varchar(30),
> @.gender varchar(1),
> @.emailAddress varchar(25),
> @.highSchoolName varchar(20),
> @.highSchoolGradYear varchar(4)
> AS
> insert into people (LastName, FirstName, CreationDate, LastUpdateDate,
> MiddleName, PreferredName, DateOfBirth, Gender,
> EmailAddress, HighSchoolName, HighSchoolGradYear)
> values (@.lastName, @.firstName, getdate(), getDate(), @.middleName,
> @.preferredName, convert(datetime, @.dateOfBirth), @.gender,
> @.emailAddress, @.highSchoolName, @.highSchoolGradYear)
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> Would one of you non-newbies be so kind as to straighten me out?
> I figure it's my ignorance/syntax issue causing some problem, whether in the
> java
> callable statement syntax or the stored procedure itself.
> Also, a pointer to any documentation that might help me resolve future issues
> on my own would be much appreciated.
>
|||Joe -
Thanks for taking the time to look at this for me.
After granting execute permission on the sp to my user (duh), I am still
getting
the error message "(same...) Incorrect syntax near 'Call' "
As long as you believe the java looks correct, I guess I'll focus on some
other area.
Never having called an sp before from java, I wanted to validate that my
syntax and usage in that regard wasn't the issue.

> In fact, not only was I unable to duplicate the problem,
> here;s a program with your execute() code pasted in, that
> creates the table and procedure and runs without complaint.
> Joe Weinstein at BEA Systems
Did you include other code somewhere that I missed? If not, throw it out
here if you get a chance. Every little bit helps! ;-)
Thanks for your feedback - it is appreciated.
"Joe Weinstein" wrote:
[vbcol=seagreen]
>
> PJ Pugh wrote:
>
> In fact, not only was I unable to duplicate the problem,
> here;s a program with your execute() code pasted in, that
> creates the table and procedure and runs without complaint.
> Joe Weinstein at BEA Systems
>
|||David -
insert_person is the name of the stored procedure. I don't think there is any
issue with having the name of an sp contain an underscore.
Thanks for looking.
"David Thielen" wrote:

> I think you need to do "insert person" instead of "insert_person" -
> the _ makes it a single unrecognized word.
> - dave
>
> On Thu, 8 Sep 2005 09:10:05 -0700, "PJ Pugh"
> <msee92_spamfree@.hotmail.com> wrote:
>
> david@.at-at-at@.windward.dot.dot.net
> Windward Reports -- http://www.WindwardReports.com
> Page 2 Stage -- http://www.Page2Stage.com
> Enemy Nations -- http://www.EnemyNations.com
> me -- http://dave.thielen.com
> Barbie Science Fair -- http://www.BarbieScienceFair.info
> (yes I have lots of links)
>
|||PJ Pugh wrote:

> Joe -
> Thanks for taking the time to look at this for me.
> After granting execute permission on the sp to my user (duh), I am still
> getting
> the error message "(same...) Incorrect syntax near 'Call' "
Why is it 'Call' instead of 'call'?

> As long as you believe the java looks correct, I guess I'll focus on some
> other area.
Well, try running the little program I attached, or comparing my code in it,
line-by-line to yours.

> Never having called an sp before from java, I wanted to validate that my
> syntax and usage in that regard wasn't the issue.
>
>
> Did you include other code somewhere that I missed? If not, throw it out
> here if you get a chance. Every little bit helps! ;-)
I *did* attach it to that last post, but I'll put it inline here:
import java.io.PrintStream;
import java.sql.*;
import java.util.Hashtable;
import java.util.Properties;
import java.util.*;
import java.math.*;
public class foo
{
public static void main(String args[])
throws Exception
{
Connection c = null;
try
{
Properties props = new Properties();
Driver d = new com.microsoft.jdbc.sqlserver.SQLServerDriver();
props.put("user", "joe");
props.put("password", "joe");
c = d.connect("jdbc:microsoft:sqlserver://joe:1433", props );
DatabaseMetaData dd = c.getMetaData();
System.out.println("Driver version is " + dd.getDriverVersion() );
Statement s = c.createStatement();
try{s.executeUpdate("drop proc insert_person");} catch (Exception ignore){}
try{s.executeUpdate("drop table people");} catch (Exception ignore){}
s.executeUpdate("create table people "
+ "(LastName varchar(30), FirstName varchar(30), CreationDate datetime, LastUpdateDate datetime, "
+ "MiddleName varchar(30), PreferredName varchar(30), DateOfBirth varchar(30), Gender varchar(30), "
+ "EmailAddress varchar(30), HighSchoolName varchar(30), HighSchoolGradYear varchar(30)) ");
s.executeUpdate("create proc insert_person "
+ " @.lastName varchar(30), "
+ " @.firstName varchar(30), "
+ " @.middleName varchar(30), "
+ " @.preferredName varchar(30), "
+ " @.dateOfBirth varchar(30), "
+ " @.gender varchar(30), "
+ " @.emailAddress varchar(30), "
+ " @.highSchoolName varchar(30), "
+ " @.highSchoolGradYear varchar(30) "
+ "AS "
+ " insert into people (LastName, FirstName, CreationDate, LastUpdateDate, "
+ " MiddleName, PreferredName, DateOfBirth, Gender, "
+ " EmailAddress, HighSchoolName, HighSchoolGradYear) "
+ " values (@.lastName, @.firstName, getdate(), getdate(), @.middleName, "
+ " @.preferredName, convert(datetime, @.dateOfBirth), @.gender, "
+ " @.emailAddress, @.highSchoolName, @.highSchoolGradYear) " );
String procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
CallableStatement procCall = c.prepareCall(procString);
procCall.setString(1, "this.lastName");
procCall.setString(2, "this.firstName");
procCall.setString(3, "this.middleName");
procCall.setString(4, "this.preferredName");
procCall.setString(5, "11/11/1992 20:20:20");
procCall.setString(6, "this.gender");
procCall.setString(7, "this.emailAddress");
procCall.setString(8, "this.highSchoolName");
procCall.setString(9, "this.highSchoolGradYear");
procCall.executeUpdate();
}
catch(Exception exception1)
{
exception1.printStackTrace();
}
finally
{
if (c != null) try {c.close();} catch (Exception ignore){}
}
}
}
[vbcol=seagreen]
> Thanks for your feedback - it is appreciated.
> "Joe Weinstein" wrote:
>

Excel to MSSQL

hi

i have an excel sheet with 2 columns
regno name
-- --
and a table regdetails of the form

regdetails
(
regno bigint
name varchar(30)
)

my requirement is to export the data from excel sheet to the table.
the excel sheet may have duplicate entries but i want only distinct
records to be exported to the table.

can i create a DTS package to perform this ?

please post ur commentsYes you can accomplish this with DTS, import to a temp table and delete duplicate rows then copy to original table. May check http://www.sqldts.com website for more information.

excel to mssql

Hi,
is there any way to port excel data into mssql through mssql management studio express edition or by using excel itself.
thanksUm... T-SQL do ya?

Insert into MyTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\MyExcelWorkBook.xls;HDR=YES',
'SELECT * FROM [MyWorkSheet$]')|||Why don't you use DTS to import data from Excel to MSSQL?