Hi,
I am using Sql server 2005 and when I try to create a new login from
the sql server mgmnt studio, I get this error.
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'LOGIN'.
I have logged in as "sa" and the login statement that I am using is:
CREATE LOGIN test WITH PASSWORD = 'beta'
The product version is 8.00.2039.
Anybody knows whats wrong'
Thanks for helping out.
EshaWorks for me. The two most likely cause I can think of is there's a
non-printable character somewhere that didn't get copied to the post or you
have a Beta version of SQL Server 2005 (or SQL Server 2000) that had a
different CREATE LOGIN syntax.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Esha" <eshhyasi@.gmail.com> wrote in message
news:1151465435.377462.192620@.p79g2000cwp.googlegroups.com...
> Hi,
> I am using Sql server 2005 and when I try to create a new login from
> the sql server mgmnt studio, I get this error.
>
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'LOGIN'.
>
> I have logged in as "sa" and the login statement that I am using is:
> CREATE LOGIN test WITH PASSWORD = 'beta'
> The product version is 8.00.2039.
> Anybody knows whats wrong'
> Thanks for helping out.
> Esha
>|||Esha,
CREATE LOGIN is a new T-SQL statement in SQL Server 2005.
Since you are using SQL Server 2000, you need to use sp_addlogin
or sp_grantlogin. Management Studio should use the version-appropriate
T-SQL depending on whether you are adding a login to a 2000 instance
or a 2005 instance. If this is not working for you, can you tell us what
steps you are taking in Management Studio in your attempt to create
the new login?
Steve Kass
Drew University
Esha wrote:
>Hi,
>I am using Sql server 2005 and when I try to create a new login from
>the sql server mgmnt studio, I get this error.
>
>Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near 'LOGIN'.
>
>I have logged in as "sa" and the login statement that I am using is:
>CREATE LOGIN test WITH PASSWORD = 'beta'
>The product version is 8.00.2039.
>Anybody knows whats wrong'
>Thanks for helping out.
>Esha
>
>|||Hi Steve,
Thanks for the quick response.
I just verified and you are right. My database engine is SQL server
2000 but my management studio is 2005. I used sp_addlogin and it
worked.
I created a new user for the new login from the management studio and I
assigned the roles of db_datareader and db_datawriter. If I need to
allow the user, read and write access only, but not "execute" access
for scripts, do I need to assign any other roles apart from the above
two?
Thanks a lot
Esha
Steve Kass wrote:
> Esha,
> CREATE LOGIN is a new T-SQL statement in SQL Server 2005.
> Since you are using SQL Server 2000, you need to use sp_addlogin
> or sp_grantlogin. Management Studio should use the version-appropriate
> T-SQL depending on whether you are adding a login to a 2000 instance
> or a 2005 instance. If this is not working for you, can you tell us what
> steps you are taking in Management Studio in your attempt to create
> the new login?
> Steve Kass
> Drew University
> Esha wrote:
>|||Thanks Roger,
I was using management studio 2005 to connect to SQL server 2000 and
hence it was not working. I used sp_addlogin as suggested by Steve and
it worked.
Thanks a lot,
Esha
Roger Wolter[MSFT] wrote:
> Works for me. The two most likely cause I can think of is there's a
> non-printable character somewhere that didn't get copied to the post or yo
u
> have a Beta version of SQL Server 2005 (or SQL Server 2000) that had a
> different CREATE LOGIN syntax.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Esha" <eshhyasi@.gmail.com> wrote in message
> news:1151465435.377462.192620@.p79g2000cwp.googlegroups.com...|||Esha,
Whether the user can "execute" a script (a T-SQL statement?) depends on
what the script does. A data reader can execute a SELECT statement,
and a data writer can execute DELETE, UPDATE, or INSERT statements.
You can grant or deny permissions on individual stored procedures, tables,
and other objects more specifically with GRANT and DENY. The security
models for 2000 and 2005 are somewhat different, and I suggest you refer to
Books Online for the appropriate version of your database instance for more
details.
SK
Esha wrote:
>Hi Steve,
>Thanks for the quick response.
>I just verified and you are right. My database engine is SQL server
>2000 but my management studio is 2005. I used sp_addlogin and it
>worked.
>I created a new user for the new login from the management studio and I
>assigned the roles of db_datareader and db_datawriter. If I need to
>allow the user, read and write access only, but not "execute" access
>for scripts, do I need to assign any other roles apart from the above
>two?
>Thanks a lot
>Esha
>Steve Kass wrote:
>
>
>
Showing posts with label syntax. Show all posts
Showing posts with label syntax. Show all posts
Monday, March 19, 2012
Error creating login - Incorrect syntax near 'LOGIN'.
Hi,
I am using Sql server 2005 and when I try to create a new login from
the sql server mgmnt studio, I get this error.
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'LOGIN'.
I have logged in as "sa" and the login statement that I am using is:
CREATE LOGIN test WITH PASSWORD = 'beta'
The product version is 8.00.2039.
Anybody knows whats wrong'
Thanks for helping out.
EshaThis is a multi-part message in MIME format.
--060001040307010407040902
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
instance by the sounds of it. What's the version of the SQL instance
(run "SELECT SERVERPROPERTY('ProductVersion')")?
CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new logins
to a SQL 2000 instance, regardless of which SQL client tool you use, you
need to use sp_addlogin or sp_grantlogin (depending on whether it's a
standard SQL login or a trusted login we're talking about). I think the
GUI in Management Studio ought to take care of this automatically (ie.
use different commands depending on the server version) but if you want
to use T-SQL yourself then you need to write the correct statements for
the SQL version the server is running.
--
*mike hodgson*
http://sqlnerd.blogspot.com
eshhyasi@.gmail.com wrote:
>Hi,
>I am using Sql server 2005 and when I try to create a new login from
>the sql server mgmnt studio, I get this error.
>Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near 'LOGIN'.
>I have logged in as "sa" and the login statement that I am using is:
>CREATE LOGIN test WITH PASSWORD = 'beta'
>The product version is 8.00.2039.
>Anybody knows whats wrong'
>Thanks for helping out.
>Esha
>
>
--060001040307010407040902
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
instance by the sounds of it. What's the version of the SQL instance
(run "SELECT SERVERPROPERTY('ProductVersion')")?<br>
<br>
CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new
logins to a SQL 2000 instance, regardless of which SQL client tool you
use, you need to use sp_addlogin or sp_grantlogin (depending on whether
it's a standard SQL login or a trusted login we're talking about). I
think the GUI in Management Studio ought to take care of this
automatically (ie. use different commands depending on the server
version) but if you want to use T-SQL yourself then you need to write
the correct statements for the SQL version the server is running.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:eshhyasi@.gmail.com">eshhyasi@.gmail.com</a> wrote:
<blockquote
cite="mid1151465632.155321.236760@.m73g2000cwd.googlegroups.com"
type="cite">
<pre wrap="">Hi,
I am using Sql server 2005 and when I try to create a new login from
the sql server mgmnt studio, I get this error.
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'LOGIN'.
I have logged in as "sa" and the login statement that I am using is:
CREATE LOGIN test WITH PASSWORD = 'beta'
The product version is 8.00.2039.
Anybody knows whats wrong'
Thanks for helping out.
Esha
</pre>
</blockquote>
</body>
</html>
--060001040307010407040902--|||Thanks Mike,
I used sp_addlogin and it worked.
Thanks a lot,
Esha
Mike Hodgson wrote:
> Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL instance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new logins
> to a SQL 2000 instance, regardless of which SQL client tool you use, you
> need to use sp_addlogin or sp_grantlogin (depending on whether it's a
> standard SQL login or a trusted login we're talking about). I think the
> GUI in Management Studio ought to take care of this automatically (ie.
> use different commands depending on the server version) but if you want
> to use T-SQL yourself then you need to write the correct statements for
> the SQL version the server is running.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> eshhyasi@.gmail.com wrote:
> >Hi,
> >
> >I am using Sql server 2005 and when I try to create a new login from
> >the sql server mgmnt studio, I get this error.
> >
> >Msg 170, Level 15, State 1, Line 1
> >Line 1: Incorrect syntax near 'LOGIN'.
> >
> >I have logged in as "sa" and the login statement that I am using is:
> >
> >CREATE LOGIN test WITH PASSWORD = 'beta'
> >
> >The product version is 8.00.2039.
> >
> >Anybody knows whats wrong'
> >
> >Thanks for helping out.
> >
> >Esha
> >
> >
> >
> --060001040307010407040902
> Content-Type: text/html; charset=ISO-8859-1
> X-Google-AttachSize: 2029
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
> </head>
> <body bgcolor="#ffffff" text="#000000">
> <tt>Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL instance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?<br>
> <br>
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new
> logins to a SQL 2000 instance, regardless of which SQL client tool you
> use, you need to use sp_addlogin or sp_grantlogin (depending on whether
> it's a standard SQL login or a trusted login we're talking about). I
> think the GUI in Management Studio ought to take care of this
> automatically (ie. use different commands depending on the server
> version) but if you want to use T-SQL yourself then you need to write
> the correct statements for the SQL version the server is running.<br>
> </tt>
> <div class="moz-signature">
> <title></title>
> <meta http-equiv="Content-Type" content="text/html; ">
> <p><span lang="en-au"><font face="Tahoma" size="2">--<br>
> </font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
> hodgson</font></span></b><span lang="en-au"><br>
> <font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
> </p>
> </div>
> <br>
> <br>
> <a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:eshhyasi@.gmail.com">eshhyasi@.gmail.com</a> wrote:
> <blockquote
> cite="mid1151465632.155321.236760@.m73g2000cwd.googlegroups.com"
> type="cite">
> <pre wrap="">Hi,
> I am using Sql server 2005 and when I try to create a new login from
> the sql server mgmnt studio, I get this error.
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'LOGIN'.
> I have logged in as "sa" and the login statement that I am using is:
> CREATE LOGIN test WITH PASSWORD = 'beta'
> The product version is 8.00.2039.
> Anybody knows whats wrong'
> Thanks for helping out.
> Esha
> </pre>
> </blockquote>
> </body>
> </html>
> --060001040307010407040902--|||Thanks Mike,
I used sp_addlogin and it worked.
Thanks a lot,
Esha
Mike Hodgson wrote:
> Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL instance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new logins
> to a SQL 2000 instance, regardless of which SQL client tool you use, you
> need to use sp_addlogin or sp_grantlogin (depending on whether it's a
> standard SQL login or a trusted login we're talking about). I think the
> GUI in Management Studio ought to take care of this automatically (ie.
> use different commands depending on the server version) but if you want
> to use T-SQL yourself then you need to write the correct statements for
> the SQL version the server is running.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> eshhyasi@.gmail.com wrote:
> >Hi,
> >
> >I am using Sql server 2005 and when I try to create a new login from
> >the sql server mgmnt studio, I get this error.
> >
> >Msg 170, Level 15, State 1, Line 1
> >Line 1: Incorrect syntax near 'LOGIN'.
> >
> >I have logged in as "sa" and the login statement that I am using is:
> >
> >CREATE LOGIN test WITH PASSWORD = 'beta'
> >
> >The product version is 8.00.2039.
> >
> >Anybody knows whats wrong'
> >
> >Thanks for helping out.
> >
> >Esha
> >
> >
> >
> --060001040307010407040902
> Content-Type: text/html; charset=ISO-8859-1
> X-Google-AttachSize: 2029
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
> </head>
> <body bgcolor="#ffffff" text="#000000">
> <tt>Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL instance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?<br>
> <br>
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new
> logins to a SQL 2000 instance, regardless of which SQL client tool you
> use, you need to use sp_addlogin or sp_grantlogin (depending on whether
> it's a standard SQL login or a trusted login we're talking about). I
> think the GUI in Management Studio ought to take care of this
> automatically (ie. use different commands depending on the server
> version) but if you want to use T-SQL yourself then you need to write
> the correct statements for the SQL version the server is running.<br>
> </tt>
> <div class="moz-signature">
> <title></title>
> <meta http-equiv="Content-Type" content="text/html; ">
> <p><span lang="en-au"><font face="Tahoma" size="2">--<br>
> </font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
> hodgson</font></span></b><span lang="en-au"><br>
> <font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
> </p>
> </div>
> <br>
> <br>
> <a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:eshhyasi@.gmail.com">eshhyasi@.gmail.com</a> wrote:
> <blockquote
> cite="mid1151465632.155321.236760@.m73g2000cwd.googlegroups.com"
> type="cite">
> <pre wrap="">Hi,
> I am using Sql server 2005 and when I try to create a new login from
> the sql server mgmnt studio, I get this error.
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'LOGIN'.
> I have logged in as "sa" and the login statement that I am using is:
> CREATE LOGIN test WITH PASSWORD = 'beta'
> The product version is 8.00.2039.
> Anybody knows whats wrong'
> Thanks for helping out.
> Esha
> </pre>
> </blockquote>
> </body>
> </html>
> --060001040307010407040902--|||Even you are using Management Studio, you are connected to a SQL Server 2000
instance (according to your product version). You should connect to a SQL
Server 2005 instance to use create login.
Ben Nevarez, MCDBA, OCP
Database Administrator
"eshhyasi@.gmail.com" wrote:
> Hi,
> I am using Sql server 2005 and when I try to create a new login from
> the sql server mgmnt studio, I get this error.
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'LOGIN'.
> I have logged in as "sa" and the login statement that I am using is:
> CREATE LOGIN test WITH PASSWORD = 'beta'
> The product version is 8.00.2039.
> Anybody knows whats wrong'
> Thanks for helping out.
> Esha
>
I am using Sql server 2005 and when I try to create a new login from
the sql server mgmnt studio, I get this error.
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'LOGIN'.
I have logged in as "sa" and the login statement that I am using is:
CREATE LOGIN test WITH PASSWORD = 'beta'
The product version is 8.00.2039.
Anybody knows whats wrong'
Thanks for helping out.
EshaThis is a multi-part message in MIME format.
--060001040307010407040902
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
instance by the sounds of it. What's the version of the SQL instance
(run "SELECT SERVERPROPERTY('ProductVersion')")?
CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new logins
to a SQL 2000 instance, regardless of which SQL client tool you use, you
need to use sp_addlogin or sp_grantlogin (depending on whether it's a
standard SQL login or a trusted login we're talking about). I think the
GUI in Management Studio ought to take care of this automatically (ie.
use different commands depending on the server version) but if you want
to use T-SQL yourself then you need to write the correct statements for
the SQL version the server is running.
--
*mike hodgson*
http://sqlnerd.blogspot.com
eshhyasi@.gmail.com wrote:
>Hi,
>I am using Sql server 2005 and when I try to create a new login from
>the sql server mgmnt studio, I get this error.
>Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near 'LOGIN'.
>I have logged in as "sa" and the login statement that I am using is:
>CREATE LOGIN test WITH PASSWORD = 'beta'
>The product version is 8.00.2039.
>Anybody knows whats wrong'
>Thanks for helping out.
>Esha
>
>
--060001040307010407040902
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
instance by the sounds of it. What's the version of the SQL instance
(run "SELECT SERVERPROPERTY('ProductVersion')")?<br>
<br>
CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new
logins to a SQL 2000 instance, regardless of which SQL client tool you
use, you need to use sp_addlogin or sp_grantlogin (depending on whether
it's a standard SQL login or a trusted login we're talking about). I
think the GUI in Management Studio ought to take care of this
automatically (ie. use different commands depending on the server
version) but if you want to use T-SQL yourself then you need to write
the correct statements for the SQL version the server is running.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:eshhyasi@.gmail.com">eshhyasi@.gmail.com</a> wrote:
<blockquote
cite="mid1151465632.155321.236760@.m73g2000cwd.googlegroups.com"
type="cite">
<pre wrap="">Hi,
I am using Sql server 2005 and when I try to create a new login from
the sql server mgmnt studio, I get this error.
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'LOGIN'.
I have logged in as "sa" and the login statement that I am using is:
CREATE LOGIN test WITH PASSWORD = 'beta'
The product version is 8.00.2039.
Anybody knows whats wrong'
Thanks for helping out.
Esha
</pre>
</blockquote>
</body>
</html>
--060001040307010407040902--|||Thanks Mike,
I used sp_addlogin and it worked.
Thanks a lot,
Esha
Mike Hodgson wrote:
> Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL instance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new logins
> to a SQL 2000 instance, regardless of which SQL client tool you use, you
> need to use sp_addlogin or sp_grantlogin (depending on whether it's a
> standard SQL login or a trusted login we're talking about). I think the
> GUI in Management Studio ought to take care of this automatically (ie.
> use different commands depending on the server version) but if you want
> to use T-SQL yourself then you need to write the correct statements for
> the SQL version the server is running.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> eshhyasi@.gmail.com wrote:
> >Hi,
> >
> >I am using Sql server 2005 and when I try to create a new login from
> >the sql server mgmnt studio, I get this error.
> >
> >Msg 170, Level 15, State 1, Line 1
> >Line 1: Incorrect syntax near 'LOGIN'.
> >
> >I have logged in as "sa" and the login statement that I am using is:
> >
> >CREATE LOGIN test WITH PASSWORD = 'beta'
> >
> >The product version is 8.00.2039.
> >
> >Anybody knows whats wrong'
> >
> >Thanks for helping out.
> >
> >Esha
> >
> >
> >
> --060001040307010407040902
> Content-Type: text/html; charset=ISO-8859-1
> X-Google-AttachSize: 2029
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
> </head>
> <body bgcolor="#ffffff" text="#000000">
> <tt>Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL instance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?<br>
> <br>
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new
> logins to a SQL 2000 instance, regardless of which SQL client tool you
> use, you need to use sp_addlogin or sp_grantlogin (depending on whether
> it's a standard SQL login or a trusted login we're talking about). I
> think the GUI in Management Studio ought to take care of this
> automatically (ie. use different commands depending on the server
> version) but if you want to use T-SQL yourself then you need to write
> the correct statements for the SQL version the server is running.<br>
> </tt>
> <div class="moz-signature">
> <title></title>
> <meta http-equiv="Content-Type" content="text/html; ">
> <p><span lang="en-au"><font face="Tahoma" size="2">--<br>
> </font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
> hodgson</font></span></b><span lang="en-au"><br>
> <font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
> </p>
> </div>
> <br>
> <br>
> <a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:eshhyasi@.gmail.com">eshhyasi@.gmail.com</a> wrote:
> <blockquote
> cite="mid1151465632.155321.236760@.m73g2000cwd.googlegroups.com"
> type="cite">
> <pre wrap="">Hi,
> I am using Sql server 2005 and when I try to create a new login from
> the sql server mgmnt studio, I get this error.
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'LOGIN'.
> I have logged in as "sa" and the login statement that I am using is:
> CREATE LOGIN test WITH PASSWORD = 'beta'
> The product version is 8.00.2039.
> Anybody knows whats wrong'
> Thanks for helping out.
> Esha
> </pre>
> </blockquote>
> </body>
> </html>
> --060001040307010407040902--|||Thanks Mike,
I used sp_addlogin and it worked.
Thanks a lot,
Esha
Mike Hodgson wrote:
> Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL instance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new logins
> to a SQL 2000 instance, regardless of which SQL client tool you use, you
> need to use sp_addlogin or sp_grantlogin (depending on whether it's a
> standard SQL login or a trusted login we're talking about). I think the
> GUI in Management Studio ought to take care of this automatically (ie.
> use different commands depending on the server version) but if you want
> to use T-SQL yourself then you need to write the correct statements for
> the SQL version the server is running.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> eshhyasi@.gmail.com wrote:
> >Hi,
> >
> >I am using Sql server 2005 and when I try to create a new login from
> >the sql server mgmnt studio, I get this error.
> >
> >Msg 170, Level 15, State 1, Line 1
> >Line 1: Incorrect syntax near 'LOGIN'.
> >
> >I have logged in as "sa" and the login statement that I am using is:
> >
> >CREATE LOGIN test WITH PASSWORD = 'beta'
> >
> >The product version is 8.00.2039.
> >
> >Anybody knows whats wrong'
> >
> >Thanks for helping out.
> >
> >Esha
> >
> >
> >
> --060001040307010407040902
> Content-Type: text/html; charset=ISO-8859-1
> X-Google-AttachSize: 2029
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
> </head>
> <body bgcolor="#ffffff" text="#000000">
> <tt>Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL instance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?<br>
> <br>
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new
> logins to a SQL 2000 instance, regardless of which SQL client tool you
> use, you need to use sp_addlogin or sp_grantlogin (depending on whether
> it's a standard SQL login or a trusted login we're talking about). I
> think the GUI in Management Studio ought to take care of this
> automatically (ie. use different commands depending on the server
> version) but if you want to use T-SQL yourself then you need to write
> the correct statements for the SQL version the server is running.<br>
> </tt>
> <div class="moz-signature">
> <title></title>
> <meta http-equiv="Content-Type" content="text/html; ">
> <p><span lang="en-au"><font face="Tahoma" size="2">--<br>
> </font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
> hodgson</font></span></b><span lang="en-au"><br>
> <font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
> </p>
> </div>
> <br>
> <br>
> <a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:eshhyasi@.gmail.com">eshhyasi@.gmail.com</a> wrote:
> <blockquote
> cite="mid1151465632.155321.236760@.m73g2000cwd.googlegroups.com"
> type="cite">
> <pre wrap="">Hi,
> I am using Sql server 2005 and when I try to create a new login from
> the sql server mgmnt studio, I get this error.
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'LOGIN'.
> I have logged in as "sa" and the login statement that I am using is:
> CREATE LOGIN test WITH PASSWORD = 'beta'
> The product version is 8.00.2039.
> Anybody knows whats wrong'
> Thanks for helping out.
> Esha
> </pre>
> </blockquote>
> </body>
> </html>
> --060001040307010407040902--|||Even you are using Management Studio, you are connected to a SQL Server 2000
instance (according to your product version). You should connect to a SQL
Server 2005 instance to use create login.
Ben Nevarez, MCDBA, OCP
Database Administrator
"eshhyasi@.gmail.com" wrote:
> Hi,
> I am using Sql server 2005 and when I try to create a new login from
> the sql server mgmnt studio, I get this error.
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'LOGIN'.
> I have logged in as "sa" and the login statement that I am using is:
> CREATE LOGIN test WITH PASSWORD = 'beta'
> The product version is 8.00.2039.
> Anybody knows whats wrong'
> Thanks for helping out.
> Esha
>
Error creating login - Incorrect syntax near 'LOGIN'.
Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
instance by the sounds of it. What's the version of the SQL instance
(run "SELECT SERVERPROPERTY('ProductVersion')")?
CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new logins
to a SQL 2000 instance, regardless of which SQL client tool you use, you
need to use sp_addlogin or sp_grantlogin (depending on whether it's a
standard SQL login or a trusted login we're talking about). I think the
GUI in Management Studio ought to take care of this automatically (ie.
use different commands depending on the server version) but if you want
to use T-SQL yourself then you need to write the correct statements for
the SQL version the server is running.
*mike hodgson*
http://sqlnerd.blogspot.com
eshhyasi@.gmail.com wrote:
>Hi,
>I am using Sql server 2005 and when I try to create a new login from
>the sql server mgmnt studio, I get this error.
>Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near 'LOGIN'.
>I have logged in as "sa" and the login statement that I am using is:
>CREATE LOGIN test WITH PASSWORD = 'beta'
>The product version is 8.00.2039.
>Anybody knows whats wrong'
>Thanks for helping out.
>Esha
>
>Even you are using Management Studio, you are connected to a SQL Server 2000
instance (according to your product version). You should connect to a SQL
Server 2005 instance to use create login.
Ben Nevarez, MCDBA, OCP
Database Administrator
"eshhyasi@.gmail.com" wrote:
> Hi,
> I am using Sql server 2005 and when I try to create a new login from
> the sql server mgmnt studio, I get this error.
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'LOGIN'.
> I have logged in as "sa" and the login statement that I am using is:
> CREATE LOGIN test WITH PASSWORD = 'beta'
> The product version is 8.00.2039.
> Anybody knows whats wrong'
> Thanks for helping out.
> Esha
>|||Thanks Mike,
I used sp_addlogin and it worked.
Thanks a lot,
Esha
Mike Hodgson wrote:
> Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL instance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new logins
> to a SQL 2000 instance, regardless of which SQL client tool you use, you
> need to use sp_addlogin or sp_grantlogin (depending on whether it's a
> standard SQL login or a trusted login we're talking about). I think the
> GUI in Management Studio ought to take care of this automatically (ie.
> use different commands depending on the server version) but if you want
> to use T-SQL yourself then you need to write the correct statements for
> the SQL version the server is running.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> eshhyasi@.gmail.com wrote:
>
> --060001040307010407040902
> Content-Type: text/html; charset=ISO-8859-1
> X-Google-AttachSize: 2029
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
> </head>
> <body bgcolor="#ffffff" text="#000000">
> <tt>Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL inst
ance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?<br>
> <br>
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new
> logins to a SQL 2000 instance, regardless of which SQL client tool you
> use, you need to use sp_addlogin or sp_grantlogin (depending on whether
> it's a standard SQL login or a trusted login we're talking about).&nbs
p; I
> think the GUI in Management Studio ought to take care of this
> automatically (ie. use different commands depending on the server
> version) but if you want to use T-SQL yourself then you need to write
> the correct statements for the SQL version the server is running.<br>
> </tt>
> <div class="moz-signature">
> <title></title>
> <meta http-equiv="Content-Type" content="text/html; ">
> <p><span lang="en-au"><font face="Tahoma" size="2">--<br>
> </font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
> hodgson</font></span></b><span lang="en-au"><br>
> <font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com">http://sqlner
d.blogspot.com</a></font></span>
> </p>
> </div>
> <br>
> <br>
> <a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:eshhyasi@.gmail.com">eshhy
asi@.gmail.com</a> wrote:
> <blockquote
> cite="mid1151465632.155321.236760@.m73g2000cwd.googlegroups.com"
> type="cite">
> <pre wrap="">Hi,
> I am using Sql server 2005 and when I try to create a new login from
> the sql server mgmnt studio, I get this error.
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'LOGIN'.
> I have logged in as "sa" and the login statement that I am using is:
> CREATE LOGIN test WITH PASSWORD = 'beta'
> The product version is 8.00.2039.
> Anybody knows whats wrong'
> Thanks for helping out.
> Esha
> </pre>
> </blockquote>
> </body>
> </html>
> --060001040307010407040902--|||Thanks Mike,
I used sp_addlogin and it worked.
Thanks a lot,
Esha
Mike Hodgson wrote:
> Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL instance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new logins
> to a SQL 2000 instance, regardless of which SQL client tool you use, you
> need to use sp_addlogin or sp_grantlogin (depending on whether it's a
> standard SQL login or a trusted login we're talking about). I think the
> GUI in Management Studio ought to take care of this automatically (ie.
> use different commands depending on the server version) but if you want
> to use T-SQL yourself then you need to write the correct statements for
> the SQL version the server is running.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> eshhyasi@.gmail.com wrote:
>
> --060001040307010407040902
> Content-Type: text/html; charset=ISO-8859-1
> X-Google-AttachSize: 2029
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
> </head>
> <body bgcolor="#ffffff" text="#000000">
> <tt>Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL inst
ance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?<br>
> <br>
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new
> logins to a SQL 2000 instance, regardless of which SQL client tool you
> use, you need to use sp_addlogin or sp_grantlogin (depending on whether
> it's a standard SQL login or a trusted login we're talking about).&nbs
p; I
> think the GUI in Management Studio ought to take care of this
> automatically (ie. use different commands depending on the server
> version) but if you want to use T-SQL yourself then you need to write
> the correct statements for the SQL version the server is running.<br>
> </tt>
> <div class="moz-signature">
> <title></title>
> <meta http-equiv="Content-Type" content="text/html; ">
> <p><span lang="en-au"><font face="Tahoma" size="2">--<br>
> </font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
> hodgson</font></span></b><span lang="en-au"><br>
> <font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com">http://sqlner
d.blogspot.com</a></font></span>
> </p>
> </div>
> <br>
> <br>
> <a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:eshhyasi@.gmail.com">eshhy
asi@.gmail.com</a> wrote:
> <blockquote
> cite="mid1151465632.155321.236760@.m73g2000cwd.googlegroups.com"
> type="cite">
> <pre wrap="">Hi,
> I am using Sql server 2005 and when I try to create a new login from
> the sql server mgmnt studio, I get this error.
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'LOGIN'.
> I have logged in as "sa" and the login statement that I am using is:
> CREATE LOGIN test WITH PASSWORD = 'beta'
> The product version is 8.00.2039.
> Anybody knows whats wrong'
> Thanks for helping out.
> Esha
> </pre>
> </blockquote>
> </body>
> </html>
> --060001040307010407040902--|||Hi,
I am using Sql server 2005 and when I try to create a new login from
the sql server mgmnt studio, I get this error.
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'LOGIN'.
I have logged in as "sa" and the login statement that I am using is:
CREATE LOGIN test WITH PASSWORD = 'beta'
The product version is 8.00.2039.
Anybody knows whats wrong'
Thanks for helping out.
Esha|||Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
instance by the sounds of it. What's the version of the SQL instance
(run "SELECT SERVERPROPERTY('ProductVersion')")?
CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new logins
to a SQL 2000 instance, regardless of which SQL client tool you use, you
need to use sp_addlogin or sp_grantlogin (depending on whether it's a
standard SQL login or a trusted login we're talking about). I think the
GUI in Management Studio ought to take care of this automatically (ie.
use different commands depending on the server version) but if you want
to use T-SQL yourself then you need to write the correct statements for
the SQL version the server is running.
*mike hodgson*
http://sqlnerd.blogspot.com
eshhyasi@.gmail.com wrote:
>Hi,
>I am using Sql server 2005 and when I try to create a new login from
>the sql server mgmnt studio, I get this error.
>Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near 'LOGIN'.
>I have logged in as "sa" and the login statement that I am using is:
>CREATE LOGIN test WITH PASSWORD = 'beta'
>The product version is 8.00.2039.
>Anybody knows whats wrong'
>Thanks for helping out.
>Esha
>
>|||Even you are using Management Studio, you are connected to a SQL Server 2000
instance (according to your product version). You should connect to a SQL
Server 2005 instance to use create login.
Ben Nevarez, MCDBA, OCP
Database Administrator
"eshhyasi@.gmail.com" wrote:
> Hi,
> I am using Sql server 2005 and when I try to create a new login from
> the sql server mgmnt studio, I get this error.
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'LOGIN'.
> I have logged in as "sa" and the login statement that I am using is:
> CREATE LOGIN test WITH PASSWORD = 'beta'
> The product version is 8.00.2039.
> Anybody knows whats wrong'
> Thanks for helping out.
> Esha
>|||Thanks Mike,
I used sp_addlogin and it worked.
Thanks a lot,
Esha
Mike Hodgson wrote:
> Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL instance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new logins
> to a SQL 2000 instance, regardless of which SQL client tool you use, you
> need to use sp_addlogin or sp_grantlogin (depending on whether it's a
> standard SQL login or a trusted login we're talking about). I think the
> GUI in Management Studio ought to take care of this automatically (ie.
> use different commands depending on the server version) but if you want
> to use T-SQL yourself then you need to write the correct statements for
> the SQL version the server is running.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> eshhyasi@.gmail.com wrote:
>
> --060001040307010407040902
> Content-Type: text/html; charset=ISO-8859-1
> X-Google-AttachSize: 2029
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
> </head>
> <body bgcolor="#ffffff" text="#000000">
> <tt>Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL inst
ance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?<br>
> <br>
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new
> logins to a SQL 2000 instance, regardless of which SQL client tool you
> use, you need to use sp_addlogin or sp_grantlogin (depending on whether
> it's a standard SQL login or a trusted login we're talking about).&nbs
p; I
> think the GUI in Management Studio ought to take care of this
> automatically (ie. use different commands depending on the server
> version) but if you want to use T-SQL yourself then you need to write
> the correct statements for the SQL version the server is running.<br>
> </tt>
> <div class="moz-signature">
> <title></title>
> <meta http-equiv="Content-Type" content="text/html; ">
> <p><span lang="en-au"><font face="Tahoma" size="2">--<br>
> </font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
> hodgson</font></span></b><span lang="en-au"><br>
> <font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com">http://sqlner
d.blogspot.com</a></font></span>
> </p>
> </div>
> <br>
> <br>
> <a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:eshhyasi@.gmail.com">eshhy
asi@.gmail.com</a> wrote:
> <blockquote
> cite="mid1151465632.155321.236760@.m73g2000cwd.googlegroups.com"
> type="cite">
> <pre wrap="">Hi,
> I am using Sql server 2005 and when I try to create a new login from
> the sql server mgmnt studio, I get this error.
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'LOGIN'.
> I have logged in as "sa" and the login statement that I am using is:
> CREATE LOGIN test WITH PASSWORD = 'beta'
> The product version is 8.00.2039.
> Anybody knows whats wrong'
> Thanks for helping out.
> Esha
> </pre>
> </blockquote>
> </body>
> </html>
> --060001040307010407040902--|||Thanks Mike,
I used sp_addlogin and it worked.
Thanks a lot,
Esha
Mike Hodgson wrote:
> Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL instance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new logins
> to a SQL 2000 instance, regardless of which SQL client tool you use, you
> need to use sp_addlogin or sp_grantlogin (depending on whether it's a
> standard SQL login or a trusted login we're talking about). I think the
> GUI in Management Studio ought to take care of this automatically (ie.
> use different commands depending on the server version) but if you want
> to use T-SQL yourself then you need to write the correct statements for
> the SQL version the server is running.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> eshhyasi@.gmail.com wrote:
>
> --060001040307010407040902
> Content-Type: text/html; charset=ISO-8859-1
> X-Google-AttachSize: 2029
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
> </head>
> <body bgcolor="#ffffff" text="#000000">
> <tt>Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL inst
ance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?<br>
> <br>
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new
> logins to a SQL 2000 instance, regardless of which SQL client tool you
> use, you need to use sp_addlogin or sp_grantlogin (depending on whether
> it's a standard SQL login or a trusted login we're talking about).&nbs
p; I
> think the GUI in Management Studio ought to take care of this
> automatically (ie. use different commands depending on the server
> version) but if you want to use T-SQL yourself then you need to write
> the correct statements for the SQL version the server is running.<br>
> </tt>
> <div class="moz-signature">
> <title></title>
> <meta http-equiv="Content-Type" content="text/html; ">
> <p><span lang="en-au"><font face="Tahoma" size="2">--<br>
> </font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
> hodgson</font></span></b><span lang="en-au"><br>
> <font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com">http://sqlner
d.blogspot.com</a></font></span>
> </p>
> </div>
> <br>
> <br>
> <a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:eshhyasi@.gmail.com">eshhy
asi@.gmail.com</a> wrote:
> <blockquote
> cite="mid1151465632.155321.236760@.m73g2000cwd.googlegroups.com"
> type="cite">
> <pre wrap="">Hi,
> I am using Sql server 2005 and when I try to create a new login from
> the sql server mgmnt studio, I get this error.
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'LOGIN'.
> I have logged in as "sa" and the login statement that I am using is:
> CREATE LOGIN test WITH PASSWORD = 'beta'
> The product version is 8.00.2039.
> Anybody knows whats wrong'
> Thanks for helping out.
> Esha
> </pre>
> </blockquote>
> </body>
> </html>
> --060001040307010407040902--
instance by the sounds of it. What's the version of the SQL instance
(run "SELECT SERVERPROPERTY('ProductVersion')")?
CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new logins
to a SQL 2000 instance, regardless of which SQL client tool you use, you
need to use sp_addlogin or sp_grantlogin (depending on whether it's a
standard SQL login or a trusted login we're talking about). I think the
GUI in Management Studio ought to take care of this automatically (ie.
use different commands depending on the server version) but if you want
to use T-SQL yourself then you need to write the correct statements for
the SQL version the server is running.
*mike hodgson*
http://sqlnerd.blogspot.com
eshhyasi@.gmail.com wrote:
>Hi,
>I am using Sql server 2005 and when I try to create a new login from
>the sql server mgmnt studio, I get this error.
>Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near 'LOGIN'.
>I have logged in as "sa" and the login statement that I am using is:
>CREATE LOGIN test WITH PASSWORD = 'beta'
>The product version is 8.00.2039.
>Anybody knows whats wrong'
>Thanks for helping out.
>Esha
>
>Even you are using Management Studio, you are connected to a SQL Server 2000
instance (according to your product version). You should connect to a SQL
Server 2005 instance to use create login.
Ben Nevarez, MCDBA, OCP
Database Administrator
"eshhyasi@.gmail.com" wrote:
> Hi,
> I am using Sql server 2005 and when I try to create a new login from
> the sql server mgmnt studio, I get this error.
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'LOGIN'.
> I have logged in as "sa" and the login statement that I am using is:
> CREATE LOGIN test WITH PASSWORD = 'beta'
> The product version is 8.00.2039.
> Anybody knows whats wrong'
> Thanks for helping out.
> Esha
>|||Thanks Mike,
I used sp_addlogin and it worked.
Thanks a lot,
Esha
Mike Hodgson wrote:
> Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL instance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new logins
> to a SQL 2000 instance, regardless of which SQL client tool you use, you
> need to use sp_addlogin or sp_grantlogin (depending on whether it's a
> standard SQL login or a trusted login we're talking about). I think the
> GUI in Management Studio ought to take care of this automatically (ie.
> use different commands depending on the server version) but if you want
> to use T-SQL yourself then you need to write the correct statements for
> the SQL version the server is running.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> eshhyasi@.gmail.com wrote:
>
> --060001040307010407040902
> Content-Type: text/html; charset=ISO-8859-1
> X-Google-AttachSize: 2029
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
> </head>
> <body bgcolor="#ffffff" text="#000000">
> <tt>Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL inst
ance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?<br>
> <br>
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new
> logins to a SQL 2000 instance, regardless of which SQL client tool you
> use, you need to use sp_addlogin or sp_grantlogin (depending on whether
> it's a standard SQL login or a trusted login we're talking about).&nbs
p; I
> think the GUI in Management Studio ought to take care of this
> automatically (ie. use different commands depending on the server
> version) but if you want to use T-SQL yourself then you need to write
> the correct statements for the SQL version the server is running.<br>
> </tt>
> <div class="moz-signature">
> <title></title>
> <meta http-equiv="Content-Type" content="text/html; ">
> <p><span lang="en-au"><font face="Tahoma" size="2">--<br>
> </font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
> hodgson</font></span></b><span lang="en-au"><br>
> <font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com">http://sqlner
d.blogspot.com</a></font></span>
> </p>
> </div>
> <br>
> <br>
> <a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:eshhyasi@.gmail.com">eshhy
asi@.gmail.com</a> wrote:
> <blockquote
> cite="mid1151465632.155321.236760@.m73g2000cwd.googlegroups.com"
> type="cite">
> <pre wrap="">Hi,
> I am using Sql server 2005 and when I try to create a new login from
> the sql server mgmnt studio, I get this error.
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'LOGIN'.
> I have logged in as "sa" and the login statement that I am using is:
> CREATE LOGIN test WITH PASSWORD = 'beta'
> The product version is 8.00.2039.
> Anybody knows whats wrong'
> Thanks for helping out.
> Esha
> </pre>
> </blockquote>
> </body>
> </html>
> --060001040307010407040902--|||Thanks Mike,
I used sp_addlogin and it worked.
Thanks a lot,
Esha
Mike Hodgson wrote:
> Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL instance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new logins
> to a SQL 2000 instance, regardless of which SQL client tool you use, you
> need to use sp_addlogin or sp_grantlogin (depending on whether it's a
> standard SQL login or a trusted login we're talking about). I think the
> GUI in Management Studio ought to take care of this automatically (ie.
> use different commands depending on the server version) but if you want
> to use T-SQL yourself then you need to write the correct statements for
> the SQL version the server is running.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> eshhyasi@.gmail.com wrote:
>
> --060001040307010407040902
> Content-Type: text/html; charset=ISO-8859-1
> X-Google-AttachSize: 2029
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
> </head>
> <body bgcolor="#ffffff" text="#000000">
> <tt>Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL inst
ance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?<br>
> <br>
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new
> logins to a SQL 2000 instance, regardless of which SQL client tool you
> use, you need to use sp_addlogin or sp_grantlogin (depending on whether
> it's a standard SQL login or a trusted login we're talking about).&nbs
p; I
> think the GUI in Management Studio ought to take care of this
> automatically (ie. use different commands depending on the server
> version) but if you want to use T-SQL yourself then you need to write
> the correct statements for the SQL version the server is running.<br>
> </tt>
> <div class="moz-signature">
> <title></title>
> <meta http-equiv="Content-Type" content="text/html; ">
> <p><span lang="en-au"><font face="Tahoma" size="2">--<br>
> </font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
> hodgson</font></span></b><span lang="en-au"><br>
> <font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com">http://sqlner
d.blogspot.com</a></font></span>
> </p>
> </div>
> <br>
> <br>
> <a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:eshhyasi@.gmail.com">eshhy
asi@.gmail.com</a> wrote:
> <blockquote
> cite="mid1151465632.155321.236760@.m73g2000cwd.googlegroups.com"
> type="cite">
> <pre wrap="">Hi,
> I am using Sql server 2005 and when I try to create a new login from
> the sql server mgmnt studio, I get this error.
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'LOGIN'.
> I have logged in as "sa" and the login statement that I am using is:
> CREATE LOGIN test WITH PASSWORD = 'beta'
> The product version is 8.00.2039.
> Anybody knows whats wrong'
> Thanks for helping out.
> Esha
> </pre>
> </blockquote>
> </body>
> </html>
> --060001040307010407040902--|||Hi,
I am using Sql server 2005 and when I try to create a new login from
the sql server mgmnt studio, I get this error.
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'LOGIN'.
I have logged in as "sa" and the login statement that I am using is:
CREATE LOGIN test WITH PASSWORD = 'beta'
The product version is 8.00.2039.
Anybody knows whats wrong'
Thanks for helping out.
Esha|||Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
instance by the sounds of it. What's the version of the SQL instance
(run "SELECT SERVERPROPERTY('ProductVersion')")?
CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new logins
to a SQL 2000 instance, regardless of which SQL client tool you use, you
need to use sp_addlogin or sp_grantlogin (depending on whether it's a
standard SQL login or a trusted login we're talking about). I think the
GUI in Management Studio ought to take care of this automatically (ie.
use different commands depending on the server version) but if you want
to use T-SQL yourself then you need to write the correct statements for
the SQL version the server is running.
*mike hodgson*
http://sqlnerd.blogspot.com
eshhyasi@.gmail.com wrote:
>Hi,
>I am using Sql server 2005 and when I try to create a new login from
>the sql server mgmnt studio, I get this error.
>Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near 'LOGIN'.
>I have logged in as "sa" and the login statement that I am using is:
>CREATE LOGIN test WITH PASSWORD = 'beta'
>The product version is 8.00.2039.
>Anybody knows whats wrong'
>Thanks for helping out.
>Esha
>
>|||Even you are using Management Studio, you are connected to a SQL Server 2000
instance (according to your product version). You should connect to a SQL
Server 2005 instance to use create login.
Ben Nevarez, MCDBA, OCP
Database Administrator
"eshhyasi@.gmail.com" wrote:
> Hi,
> I am using Sql server 2005 and when I try to create a new login from
> the sql server mgmnt studio, I get this error.
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'LOGIN'.
> I have logged in as "sa" and the login statement that I am using is:
> CREATE LOGIN test WITH PASSWORD = 'beta'
> The product version is 8.00.2039.
> Anybody knows whats wrong'
> Thanks for helping out.
> Esha
>|||Thanks Mike,
I used sp_addlogin and it worked.
Thanks a lot,
Esha
Mike Hodgson wrote:
> Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL instance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new logins
> to a SQL 2000 instance, regardless of which SQL client tool you use, you
> need to use sp_addlogin or sp_grantlogin (depending on whether it's a
> standard SQL login or a trusted login we're talking about). I think the
> GUI in Management Studio ought to take care of this automatically (ie.
> use different commands depending on the server version) but if you want
> to use T-SQL yourself then you need to write the correct statements for
> the SQL version the server is running.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> eshhyasi@.gmail.com wrote:
>
> --060001040307010407040902
> Content-Type: text/html; charset=ISO-8859-1
> X-Google-AttachSize: 2029
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
> </head>
> <body bgcolor="#ffffff" text="#000000">
> <tt>Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL inst
ance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?<br>
> <br>
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new
> logins to a SQL 2000 instance, regardless of which SQL client tool you
> use, you need to use sp_addlogin or sp_grantlogin (depending on whether
> it's a standard SQL login or a trusted login we're talking about).&nbs
p; I
> think the GUI in Management Studio ought to take care of this
> automatically (ie. use different commands depending on the server
> version) but if you want to use T-SQL yourself then you need to write
> the correct statements for the SQL version the server is running.<br>
> </tt>
> <div class="moz-signature">
> <title></title>
> <meta http-equiv="Content-Type" content="text/html; ">
> <p><span lang="en-au"><font face="Tahoma" size="2">--<br>
> </font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
> hodgson</font></span></b><span lang="en-au"><br>
> <font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com">http://sqlner
d.blogspot.com</a></font></span>
> </p>
> </div>
> <br>
> <br>
> <a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:eshhyasi@.gmail.com">eshhy
asi@.gmail.com</a> wrote:
> <blockquote
> cite="mid1151465632.155321.236760@.m73g2000cwd.googlegroups.com"
> type="cite">
> <pre wrap="">Hi,
> I am using Sql server 2005 and when I try to create a new login from
> the sql server mgmnt studio, I get this error.
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'LOGIN'.
> I have logged in as "sa" and the login statement that I am using is:
> CREATE LOGIN test WITH PASSWORD = 'beta'
> The product version is 8.00.2039.
> Anybody knows whats wrong'
> Thanks for helping out.
> Esha
> </pre>
> </blockquote>
> </body>
> </html>
> --060001040307010407040902--|||Thanks Mike,
I used sp_addlogin and it worked.
Thanks a lot,
Esha
Mike Hodgson wrote:
> Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL instance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new logins
> to a SQL 2000 instance, regardless of which SQL client tool you use, you
> need to use sp_addlogin or sp_grantlogin (depending on whether it's a
> standard SQL login or a trusted login we're talking about). I think the
> GUI in Management Studio ought to take care of this automatically (ie.
> use different commands depending on the server version) but if you want
> to use T-SQL yourself then you need to write the correct statements for
> the SQL version the server is running.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> eshhyasi@.gmail.com wrote:
>
> --060001040307010407040902
> Content-Type: text/html; charset=ISO-8859-1
> X-Google-AttachSize: 2029
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
> </head>
> <body bgcolor="#ffffff" text="#000000">
> <tt>Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL inst
ance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?<br>
> <br>
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new
> logins to a SQL 2000 instance, regardless of which SQL client tool you
> use, you need to use sp_addlogin or sp_grantlogin (depending on whether
> it's a standard SQL login or a trusted login we're talking about).&nbs
p; I
> think the GUI in Management Studio ought to take care of this
> automatically (ie. use different commands depending on the server
> version) but if you want to use T-SQL yourself then you need to write
> the correct statements for the SQL version the server is running.<br>
> </tt>
> <div class="moz-signature">
> <title></title>
> <meta http-equiv="Content-Type" content="text/html; ">
> <p><span lang="en-au"><font face="Tahoma" size="2">--<br>
> </font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
> hodgson</font></span></b><span lang="en-au"><br>
> <font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com">http://sqlner
d.blogspot.com</a></font></span>
> </p>
> </div>
> <br>
> <br>
> <a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:eshhyasi@.gmail.com">eshhy
asi@.gmail.com</a> wrote:
> <blockquote
> cite="mid1151465632.155321.236760@.m73g2000cwd.googlegroups.com"
> type="cite">
> <pre wrap="">Hi,
> I am using Sql server 2005 and when I try to create a new login from
> the sql server mgmnt studio, I get this error.
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'LOGIN'.
> I have logged in as "sa" and the login statement that I am using is:
> CREATE LOGIN test WITH PASSWORD = 'beta'
> The product version is 8.00.2039.
> Anybody knows whats wrong'
> Thanks for helping out.
> Esha
> </pre>
> </blockquote>
> </body>
> </html>
> --060001040307010407040902--
Friday, February 17, 2012
Error after migrating to SQL Server 2005
I'm trying to migrate MS Access 2003 query into SQL Server 2005 view.
I'm getting error for the following query.
CREATE VIEW dbo.[qryReport]
AS
/*
* Generated by SQL Server Migration Assistant for Access.
* Contact accssma@.microsoft.com or visit http://www.microsoft.com/sql/migration for more information.
*/
I'm getting error for the following query.
ERROR: Incorrect syntax near the keyword 'LEFT'
MS-Access query:
SELECT tbl.*, tblP.ParticipantLastName, tblP.ParticipantFirstName, tblP.ParticipantAmount, tblP.SocialSecurityNumber, tblP.Comment
FROM tbl LEFT JOIN tblP ON tbl.AutoNumber = tblP.RecordNumber
WHERE ToBeProcessed='YES' and [Cancel]=0;
SQL Server View:
CREATE VIEW dbo.[qryReport]
AS
/*
* Generated by SQL Server Migration Assistant for Access.
* Contact accssma@.microsoft.com or visit http://www.microsoft.com/sql/migration for more information.
*/
SELECT
tbl.*,
tblP.ParticipantLastName,
tblP.ParticipantFirstName,
tblP.ParticipantAmount,
tblP.SocialSecurityNumber,
tblP.Comment
FROM tbl AS LEFT
JOIN tblP ON tbl.AutoNumber = tblP.RecordNumber
WHERE (ToBeProcessed = 'YES' AND [Cancel] = 0)
Drop the AS before the LEFT. your migration has assumed that LEFT was an alias for tbl rather than part of the JOIN clause for tblp
I assume this is still supported and so I would send a comment to the email address as this is clearly a bug.
|||SQL Server View:CREATE VIEW dbo.[qryReport]
AS
/*
* Generated by SQL Server Migration Assistant for Access.
* Contact accssma@.microsoft.com or visit http://www.microsoft.com/sql/migration for more information.
*/
SELECT
tbl.*,
tblP.ParticipantLastName,
tblP.ParticipantFirstName,
tblP.ParticipantAmount,
tblP.SocialSecurityNumber,
tblP.Comment
FROM tbl LEFT
JOIN tblP ON tbl.AutoNumber = tblP.RecordNumber
WHERE (ToBeProcessed = 'YES' AND [Cancel] = 0)|||I have one more query that is not getting migrated/ converted to SQL
MS Access query:
SELECT qryByDept.AutoNumber, qryByDept.InputDate, qryByDept.FN, qryByDept.CompanyName, qryByDept.TotalAmount, qryByDept.TaxYear, qryByDept.Reason, qryByDept.Department, qryByDept.TaxType, qryByDept.AccountID, qryByDept.Payee, qryByDept.Address1, qryByDept.Address2, qryByDept.Address3, qryByDept.Participant, qryByDept.Username, qryByDept.Extension, qryByDept.ToSSC, qryByDept.Authorization, qryByDept.ManagersAuthorization, qryByDept.CheckNumber, qryByDept.CheckDate, qryByDept.Cancel, qryByDept.ParticipantInfo, qryByDept.ToBeProcessed, qryByDept.CheckAmount, qryByDept.Archived
FROM qryByDept
GROUP BY qryByDept.AutoNumber, qryByDept.InputDate, qryByDept.FN, qryByDept.CompanyName, qryByDept.TotalAmount, qryByDept.TaxYear, qryByDept.Reason, qryByDept.Department, qryByDept.TaxType, qryByDept.AccountID, qryByDept.Payee, qryByDept.Address1, qryByDept.Address2, qryByDept.Address3, qryByDept.Participant, qryByDept.Username, qryByDept.Extension, qryByDept.ToSSC, qryByDept.Authorization, qryByDept.ManagersAuthorization, qryByDept.CheckNumber, qryByDept.CheckDate, qryByDept.Cancel, qryByDept.ParticipantInfo, qryByDept.ToBeProcessed, qryByDept.CheckAmount, qryByDept.Archived;
Error Message:
* SSMA error messages:
* A2SS0058: Following SQL statement is not supported and cannot be converted:
*
|||
Could you please send email to accssma@.microsoft.com or post these problems in the microsoft.public.access.sqlupsizing newsgroup? See link below for more details:
http://www.microsoft.com/sql/solutions/migration/access/ssma_access_faq.mspx
You will get more help on the SSMA related issues there.
|||Your column qryByDept.Authorization is the problem. Authorization is a reserved word in t-sql. You can either alias the column with qryByDept.Authorization AS 'Authorization' or use qryByDept.Authorization [Authorization]. It also looks like you could get rid of the group by statement since you have no aggregation happening.|||I'm trying to modify the keyword and make this query work.I need help with another query:
SELECT
tblCurrent.AutoNumber,
tblCurrent.TaxYear,
tblCurrent.TaxType,
tblCurrent.FEIN,
tblCurrent.CompanyName,
-(TotalAmount) AS Amount,
tblCurrent.AccountID,
tblCurrent.ParticipantInfo,
'Refund' AS FileName,
Date() & ' Recd ' & [ParticipantInfo] AS Comment,
'No' AS RefundToBeProcessed,
tblCurrent.Payee,
tblCurrent.Address1,
tblCurrent.Address2,
tblCurrent.Address3,
tblCurrent.Department,
tblCurrent.ToSSCRTT
FROM tblCurrent
WHERE (((tblCurrent.AutoNumber) Between [forms]![frmTaxUnitMain]![txtAutonumberFrom] And [forms]![frmTaxUnitMain]![txtAutonumberTo]) AND ((tblCurrent.Archived)=-1));
Error on lines : Line 11 ans 20
|||I tried aliasingqryByDept.Authorization to
qryByDept.Authorization AS 'Authorization'
qryByDept.Authorization [Authorization]
But both didnt work. The error remains.
|||You can't do what you are trying. You are refering to fields on your forms which SQL doesn't know anything about. I think the solution is to create stored procedure that accepts the values you are passing and uses them as variables in the SP.|||ok.Is there any other alternative for getting the same results?
I'm new to SP and I'm trying to write a stored procedure to one the Access Queries.
But I'm not sure how to check if there are more records to check or not in the while loop.
ACCESS QUERY:
UPDATE
tbChckNum
INNER JOIN tblHtry ON tbChckNum.Auto = tblHtry.Auto
SET tblHtry.CheckAmt = ([tbChckNum].[amount1]),
tblHtry.ChckNum = [tbChckNum].[chcknum],
tblHtry.ChckDt = [tbChckNum].[chckdt],
tblHtry.AccntNum = [tbChckNum].[AcctNum],
tbChckNum.Updated = "YES"
WHERE (((tblHtry.ChckAmt) Is Null Or (tblHtry.CheckAmt)=0) AND ((tblHtry.ChckNum) Is Null) AND ((tblHtry.ChckDt) Is Null) AND ((tblHtry.AcctNum) Is Null));
STORED PROCEDURE:
CREATE PROCEDURE UpdChkNum
AS
BEGIN
SET NOCOUNT ON;
/*declare variable*/
DECLARE @.AcctNum1 varchar
DECLARE @.amt1 money
DECLARE @.ChkDt1 datetime
DECLARE @.Auto1 int
DECLARE @.ChkNum1 float
/*SELECT stmts for procedure*/
WHILE
BEGIN
SELECT
amount1,
chknum,
checkdate,
AcctNum,
Auto
Into
@.amt1,
@.chknum1,
@.chkdt1,
@.AcctNum1,
@.Auto1
from tblChkNum
UPDATE tblHtry
SET tblHtry.ChkAmt = @.amt1, @.chknum, @.chkdt, @.AcctNum where tblHtry.Auto = @.Auto
and (((tblHtry.ChkAmt) Is Null Or (tblHtry.ChkAmt)=0) AND
((tblHtry.ChkNum) Is Null) AND ((tblHtry.ChkDt) Is Null) AND
((tblHtry.AcctNum) Is Null))
BEGIN
IF (Auto = @.Auto)
UPDATE tblChkNum
SET tblChkNum.Updated = "YES"
--WHERE Auto = @.Auto
END
fetch next
END
END
GO
Error after migrating to SQL Server 2005
I'm trying to migrate MS Access 2003 query into SQL Server 2005 view.
I'm getting error for the following query.
CREATE VIEW dbo.[qryReport]
AS
/*
* Generated by SQL Server Migration Assistant for Access.
* Contact accssma@.microsoft.com or visit http://www.microsoft.com/sql/migration for more information.
*/
I'm getting error for the following query.
ERROR: Incorrect syntax near the keyword 'LEFT'
MS-Access query:
SELECT tbl.*, tblP.ParticipantLastName, tblP.ParticipantFirstName, tblP.ParticipantAmount, tblP.SocialSecurityNumber, tblP.Comment
FROM tbl LEFT JOIN tblP ON tbl.AutoNumber = tblP.RecordNumber
WHERE ToBeProcessed='YES' and [Cancel]=0;
SQL Server View:
CREATE VIEW dbo.[qryReport]
AS
/*
* Generated by SQL Server Migration Assistant for Access.
* Contact accssma@.microsoft.com or visit http://www.microsoft.com/sql/migration for more information.
*/
SELECT
tbl.*,
tblP.ParticipantLastName,
tblP.ParticipantFirstName,
tblP.ParticipantAmount,
tblP.SocialSecurityNumber,
tblP.Comment
FROM tbl AS LEFT
JOIN tblP ON tbl.AutoNumber = tblP.RecordNumber
WHERE (ToBeProcessed = 'YES' AND [Cancel] = 0)
Drop the AS before the LEFT. your migration has assumed that LEFT was an alias for tbl rather than part of the JOIN clause for tblp
I assume this is still supported and so I would send a comment to the email address as this is clearly a bug.
|||SQL Server View:CREATE VIEW dbo.[qryReport]
AS
/*
* Generated by SQL Server Migration Assistant for Access.
* Contact accssma@.microsoft.com or visit http://www.microsoft.com/sql/migration for more information.
*/
SELECT
tbl.*,
tblP.ParticipantLastName,
tblP.ParticipantFirstName,
tblP.ParticipantAmount,
tblP.SocialSecurityNumber,
tblP.Comment
FROM tbl LEFT
JOIN tblP ON tbl.AutoNumber = tblP.RecordNumber
WHERE (ToBeProcessed = 'YES' AND [Cancel] = 0)|||I have one more query that is not getting migrated/ converted to SQL
MS Access query:
SELECT qryByDept.AutoNumber, qryByDept.InputDate, qryByDept.FN, qryByDept.CompanyName, qryByDept.TotalAmount, qryByDept.TaxYear, qryByDept.Reason, qryByDept.Department, qryByDept.TaxType, qryByDept.AccountID, qryByDept.Payee, qryByDept.Address1, qryByDept.Address2, qryByDept.Address3, qryByDept.Participant, qryByDept.Username, qryByDept.Extension, qryByDept.ToSSC, qryByDept.Authorization, qryByDept.ManagersAuthorization, qryByDept.CheckNumber, qryByDept.CheckDate, qryByDept.Cancel, qryByDept.ParticipantInfo, qryByDept.ToBeProcessed, qryByDept.CheckAmount, qryByDept.Archived
FROM qryByDept
GROUP BY qryByDept.AutoNumber, qryByDept.InputDate, qryByDept.FN, qryByDept.CompanyName, qryByDept.TotalAmount, qryByDept.TaxYear, qryByDept.Reason, qryByDept.Department, qryByDept.TaxType, qryByDept.AccountID, qryByDept.Payee, qryByDept.Address1, qryByDept.Address2, qryByDept.Address3, qryByDept.Participant, qryByDept.Username, qryByDept.Extension, qryByDept.ToSSC, qryByDept.Authorization, qryByDept.ManagersAuthorization, qryByDept.CheckNumber, qryByDept.CheckDate, qryByDept.Cancel, qryByDept.ParticipantInfo, qryByDept.ToBeProcessed, qryByDept.CheckAmount, qryByDept.Archived;
Error Message:
* SSMA error messages:
* A2SS0058: Following SQL statement is not supported and cannot be converted:
*|||
Could you please send email to accssma@.microsoft.com or post these problems in the microsoft.public.access.sqlupsizing newsgroup? See link below for more details:
http://www.microsoft.com/sql/solutions/migration/access/ssma_access_faq.mspx
You will get more help on the SSMA related issues there.
|||Your column qryByDept.Authorization is the problem. Authorization is a reserved word in t-sql. You can either alias the column with qryByDept.Authorization AS 'Authorization' or use qryByDept.Authorization [Authorization]. It also looks like you could get rid of the group by statement since you have no aggregation happening.|||I'm trying to modify the keyword and make this query work.I need help with another query:
SELECT
tblCurrent.AutoNumber,
tblCurrent.TaxYear,
tblCurrent.TaxType,
tblCurrent.FEIN,
tblCurrent.CompanyName,
-(TotalAmount) AS Amount,
tblCurrent.AccountID,
tblCurrent.ParticipantInfo,
'Refund' AS FileName,
Date() & ' Recd ' & [ParticipantInfo] AS Comment,
'No' AS RefundToBeProcessed,
tblCurrent.Payee,
tblCurrent.Address1,
tblCurrent.Address2,
tblCurrent.Address3,
tblCurrent.Department,
tblCurrent.ToSSCRTT
FROM tblCurrent
WHERE (((tblCurrent.AutoNumber) Between [forms]![frmTaxUnitMain]![txtAutonumberFrom] And [forms]![frmTaxUnitMain]![txtAutonumberTo]) AND ((tblCurrent.Archived)=-1));
Error on lines : Line 11 ans 20|||I tried aliasing qryByDept.Authorization to
qryByDept.Authorization AS 'Authorization'
qryByDept.Authorization [Authorization]
But both didnt work. The error remains.
|||You can't do what you are trying. You are refering to fields on your forms which SQL doesn't know anything about. I think the solution is to create stored procedure that accepts the values you are passing and uses them as variables in the SP.|||ok.Is there any other alternative for getting the same results?
I'm new to SP and I'm trying to write a stored procedure to one the Access Queries.
But I'm not sure how to check if there are more records to check or not in the while loop.
ACCESS QUERY:
UPDATE
tbChckNum
INNER JOIN tblHtry ON tbChckNum.Auto = tblHtry.Auto
SET tblHtry.CheckAmt = ([tbChckNum].[amount1]),
tblHtry.ChckNum = [tbChckNum].[chcknum],
tblHtry.ChckDt = [tbChckNum].[chckdt],
tblHtry.AccntNum = [tbChckNum].[AcctNum],
tbChckNum.Updated = "YES"
WHERE (((tblHtry.ChckAmt) Is Null Or (tblHtry.CheckAmt)=0) AND ((tblHtry.ChckNum) Is Null) AND ((tblHtry.ChckDt) Is Null) AND ((tblHtry.AcctNum) Is Null));
STORED PROCEDURE:
CREATE PROCEDURE UpdChkNum
AS
BEGIN
SET NOCOUNT ON;
/*declare variable*/
DECLARE @.AcctNum1 varchar
DECLARE @.amt1 money
DECLARE @.ChkDt1 datetime
DECLARE @.Auto1 int
DECLARE @.ChkNum1 float
/*SELECT stmts for procedure*/
WHILE
BEGIN
SELECT
amount1,
chknum,
checkdate,
AcctNum,
Auto
Into
@.amt1,
@.chknum1,
@.chkdt1,
@.AcctNum1,
@.Auto1
from tblChkNum
UPDATE tblHtry
SET tblHtry.ChkAmt = @.amt1, @.chknum, @.chkdt, @.AcctNum where tblHtry.Auto = @.Auto
and (((tblHtry.ChkAmt) Is Null Or (tblHtry.ChkAmt)=0) AND
((tblHtry.ChkNum) Is Null) AND ((tblHtry.ChkDt) Is Null) AND
((tblHtry.AcctNum) Is Null))
BEGIN
IF (Auto = @.Auto)
UPDATE tblChkNum
SET tblChkNum.Updated = "YES"
--WHERE Auto = @.Auto
END
fetch next
END
END
GO
Subscribe to:
Posts (Atom)