Can not Create a DSN

I am trying to create a new System DSN but when I get to the part where it
reads" How should SQL Server verify the authenticity of the login ID?" I
select the second option: "With SQL Server authentication...." Then on the
bottom part I enter a username and password. When I click on NEXT, it
fails telling me it's not associated with a trusted SQL Server connection.
I don't use SQL that much, so I am a bit lost.
Can you give me some ideas? Or at least a link to somewhere where I can
find out on my own?
Thanks
Hi,
Check if you SQL Server is supporting authentication of SQL users.
It should be set to use "SQL Server and Windows authentication mode". You
can set that in server properties.
Danijel Novak
"Johnfli" <john@.ivhs.us> wrote in message
news:uUbzupFHGHA.1628@.TK2MSFTNGP12.phx.gbl...
>I am trying to create a new System DSN but when I get to the part where it
>reads" How should SQL Server verify the authenticity of the login ID?" I
>select the second option: "With SQL Server authentication...." Then on
>the bottom part I enter a username and password. When I click on NEXT, it
>fails telling me it's not associated with a trusted SQL Server connection.
> I don't use SQL that much, so I am a bit lost.
> Can you give me some ideas? Or at least a link to somewhere where I can
> find out on my own?
> Thanks
>

Can not Create a DSN

I am trying to create a new System DSN but when I get to the part where it
reads" How should SQL Server verify the authenticity of the login ID?" I
select the second option: "With SQL Server authentication...." Then on the
bottom part I enter a username and password. When I click on NEXT, it
fails telling me it's not associated with a trusted SQL Server connection.
I don't use SQL that much, so I am a bit lost.
Can you give me some ideas? Or at least a link to somewhere where I can
find out on my own'
ThanksHi,
Check if you SQL Server is supporting authentication of SQL users.
It should be set to use "SQL Server and Windows authentication mode". You
can set that in server properties.
Danijel Novak
"Johnfli" <john@.ivhs.us> wrote in message
news:uUbzupFHGHA.1628@.TK2MSFTNGP12.phx.gbl...
>I am trying to create a new System DSN but when I get to the part where it
>reads" How should SQL Server verify the authenticity of the login ID?" I
>select the second option: "With SQL Server authentication...." Then on
>the bottom part I enter a username and password. When I click on NEXT, it
>fails telling me it's not associated with a trusted SQL Server connection.
> I don't use SQL that much, so I am a bit lost.
> Can you give me some ideas? Or at least a link to somewhere where I can
> find out on my own'
> Thanks
>

Can not Create a DSN

I am trying to create a new System DSN but when I get to the part where it
reads" How should SQL Server verify the authenticity of the login ID?" I
select the second option: "With SQL Server authentication...." Then on the
bottom part I enter a username and password. When I click on NEXT, it
fails telling me it's not associated with a trusted SQL Server connection.
I don't use SQL that much, so I am a bit lost.
Can you give me some ideas? Or at least a link to somewhere where I can
find out on my own'
ThanksHi,
Check if you SQL Server is supporting authentication of SQL users.
It should be set to use "SQL Server and Windows authentication mode". You
can set that in server properties.
--
Danijel Novak
"Johnfli" <john@.ivhs.us> wrote in message
news:uUbzupFHGHA.1628@.TK2MSFTNGP12.phx.gbl...
>I am trying to create a new System DSN but when I get to the part where it
>reads" How should SQL Server verify the authenticity of the login ID?" I
>select the second option: "With SQL Server authentication...." Then on
>the bottom part I enter a username and password. When I click on NEXT, it
>fails telling me it's not associated with a trusted SQL Server connection.
> I don't use SQL that much, so I am a bit lost.
> Can you give me some ideas? Or at least a link to somewhere where I can
> find out on my own'
> Thanks
>

can not connect with system account

we have two non-sql servers (a & b) that connect to a third sql server with
their system account (servername$). We have permissioned the system accounts
in SQL. However, server a is able to connect with it's system account and b
is not.
When I schedule a batch file to connect to the sql server using osql, server
b 's connection errors out with the error: Login failed for user 'NT
AUTHORITY\ANONYMOUS LOGON'.
DBA72 wrote:
> we have two non-sql servers (a & b) that connect to a third sql
> server with their system account (servername$). We have permissioned
> the system accounts in SQL. However, server a is able to connect with
> it's system account and b is not.
> When I schedule a batch file to connect to the sql server using osql,
> server b 's connection errors out with the error: Login failed for
> user 'NT AUTHORITY\ANONYMOUS LOGON'.
Why not create an account for SQL Server and use it for the service.
David Gugick
Imceda Software
www.imceda.com
|||This is for an SMS setup and apparently, under advanced security settings,
the SMS service on other servers is supposed to run under the system account.
"David Gugick" wrote:

> DBA72 wrote:
> Why not create an account for SQL Server and use it for the service.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Are these two (three) servers in different domains? Are any of them running
on NT 4 servers? The fact that you received anonymous logon errors says the
SQL Server domain controller doesn't know anything about the system account
that this server is passing to the SQL Server.
Another possibility is that server b's system account password has not been
able to successfully synchronize with its domain controller.
Lastly, the NT AUTHORITY accounts are not; they are Windows AD Groups. So,
either NT AUTHORITY\SYSTEM or NT AUTHORITY\ANONYMOUS LOGON can be granted
access to your SQL Server. However, they include several systems and/or
unkown users. Give whatever access very limited rights with your SQL Server
and user databases. Luckily, these accounts are restricted to the domain
that hosts them; so, if you are in a secured WAN\LAN, it will be less of a
threat.
Sincerely,
Anthony Thomas

"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:BF1203C3-3219-484A-99BC-661B4B8C6A72@.microsoft.com...
This is for an SMS setup and apparently, under advanced security settings,
the SMS service on other servers is supposed to run under the system
account.
"David Gugick" wrote:

> DBA72 wrote:
> Why not create an account for SQL Server and use it for the service.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

can not connect with system account

we have two non-sql servers (a & b) that connect to a third sql server with
their system account (servername$). We have permissioned the system accounts
in SQL. However, server a is able to connect with it's system account and b
is not.
When I schedule a batch file to connect to the sql server using osql, server
b 's connection errors out with the error: Login failed for user 'NT
AUTHORITY\ANONYMOUS LOGON'.DBA72 wrote:
> we have two non-sql servers (a & b) that connect to a third sql
> server with their system account (servername$). We have permissioned
> the system accounts in SQL. However, server a is able to connect with
> it's system account and b is not.
> When I schedule a batch file to connect to the sql server using osql,
> server b 's connection errors out with the error: Login failed for
> user 'NT AUTHORITY\ANONYMOUS LOGON'.
Why not create an account for SQL Server and use it for the service.
David Gugick
Imceda Software
www.imceda.com|||This is for an SMS setup and apparently, under advanced security settings,
the SMS service on other servers is supposed to run under the system account
.
"David Gugick" wrote:

> DBA72 wrote:
> Why not create an account for SQL Server and use it for the service.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Are these two (three) servers in different domains? Are any of them running
on NT 4 servers? The fact that you received anonymous logon errors says the
SQL Server domain controller doesn't know anything about the system account
that this server is passing to the SQL Server.
Another possibility is that server b's system account password has not been
able to successfully synchronize with its domain controller.
Lastly, the NT AUTHORITY accounts are not; they are Windows AD Groups. So,
either NT AUTHORITY\SYSTEM or NT AUTHORITY\ANONYMOUS LOGON can be granted
access to your SQL Server. However, they include several systems and/or
unkown users. Give whatever access very limited rights with your SQL Server
and user databases. Luckily, these accounts are restricted to the domain
that hosts them; so, if you are in a secured WAN\LAN, it will be less of a
threat.
Sincerely,
Anthony Thomas
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:BF1203C3-3219-484A-99BC-661B4B8C6A72@.microsoft.com...
This is for an SMS setup and apparently, under advanced security settings,
the SMS service on other servers is supposed to run under the system
account.
"David Gugick" wrote:

> DBA72 wrote:
> Why not create an account for SQL Server and use it for the service.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

can not connect with system account

we have two non-sql servers (a & b) that connect to a third sql server with
their system account (servername$). We have permissioned the system accounts
in SQL. However, server a is able to connect with it's system account and b
is not.
When I schedule a batch file to connect to the sql server using osql, server
b 's connection errors out with the error: Login failed for user 'NT
AUTHORITY\ANONYMOUS LOGON'.DBA72 wrote:
> we have two non-sql servers (a & b) that connect to a third sql
> server with their system account (servername$). We have permissioned
> the system accounts in SQL. However, server a is able to connect with
> it's system account and b is not.
> When I schedule a batch file to connect to the sql server using osql,
> server b 's connection errors out with the error: Login failed for
> user 'NT AUTHORITY\ANONYMOUS LOGON'.
Why not create an account for SQL Server and use it for the service.
--
David Gugick
Imceda Software
www.imceda.com|||This is for an SMS setup and apparently, under advanced security settings,
the SMS service on other servers is supposed to run under the system account.
"David Gugick" wrote:
> DBA72 wrote:
> > we have two non-sql servers (a & b) that connect to a third sql
> > server with their system account (servername$). We have permissioned
> > the system accounts in SQL. However, server a is able to connect with
> > it's system account and b is not.
> > When I schedule a batch file to connect to the sql server using osql,
> > server b 's connection errors out with the error: Login failed for
> > user 'NT AUTHORITY\ANONYMOUS LOGON'.
> Why not create an account for SQL Server and use it for the service.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Are these two (three) servers in different domains? Are any of them running
on NT 4 servers? The fact that you received anonymous logon errors says the
SQL Server domain controller doesn't know anything about the system account
that this server is passing to the SQL Server.
Another possibility is that server b's system account password has not been
able to successfully synchronize with its domain controller.
Lastly, the NT AUTHORITY accounts are not; they are Windows AD Groups. So,
either NT AUTHORITY\SYSTEM or NT AUTHORITY\ANONYMOUS LOGON can be granted
access to your SQL Server. However, they include several systems and/or
unkown users. Give whatever access very limited rights with your SQL Server
and user databases. Luckily, these accounts are restricted to the domain
that hosts them; so, if you are in a secured WAN\LAN, it will be less of a
threat.
Sincerely,
Anthony Thomas
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:BF1203C3-3219-484A-99BC-661B4B8C6A72@.microsoft.com...
This is for an SMS setup and apparently, under advanced security settings,
the SMS service on other servers is supposed to run under the system
account.
"David Gugick" wrote:
> DBA72 wrote:
> > we have two non-sql servers (a & b) that connect to a third sql
> > server with their system account (servername$). We have permissioned
> > the system accounts in SQL. However, server a is able to connect with
> > it's system account and b is not.
> > When I schedule a batch file to connect to the sql server using osql,
> > server b 's connection errors out with the error: Login failed for
> > user 'NT AUTHORITY\ANONYMOUS LOGON'.
> Why not create an account for SQL Server and use it for the service.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Can not connect to desktop sql database from mobile device (Pocket PC)

Hi,

I am developing an application for a Pocket PC (PPC). I am using Visual studio 2005, and the Operating system on this PPC is Window Mobile 2003 SE. The followings are my codes to connect PPC with the database on my PC with sql server 2005. But when I cick the button1, the error with "SQL Server does not exist or access denied." Any ideas? Thanks a lot.

Imports System.IO

Imports System.Data

Imports System.Data.SqlClient

Imports System.Math

Imports System.Drawing

Imports System

Public Class Form1

Dim strConnection As String = "Server=localhost;Integrated Security=SSPI;database=AdventureWorks"

Dim strQuery As String

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Label1.Text = "Hello, Clicking after."

ReadOrderData()

End Sub

Private Sub ReadOrderData()

Dim queryString As String = "select * from Person.Address where AddressID < '10'"

Using connection As New SqlConnection(strConnection)

Dim command As New SqlCommand(queryString, connection)

connection.Open()

Dim reader As SqlDataReader = command.ExecuteReader()

While reader.Read()

MsgBox(String.Format("{0}, {1}", _

reader(0), reader(1)))

End While

reader.Close()

End Using

End Sub

End Class

Please see this:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=184007&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=235638&SiteID=1

If it still does not work after you fixed your 'localhost' issue:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=270532&SiteID=1

Also, we have forum dedicated to SQL Mobile. Please post to it should you have any SQL or SQL Mobile related questions:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=152&SiteID=1

|||

Thanks for your reply.

I would like to install vxUtil, and I have already read the readme.txt file. But I still have some questions about that. Should vxUtil be installed on my personal PC or the Pocket PC(PPC)? If it should be installed in the PPC, how can I install? In the readme.txt, I get the following information:

Open the appropriate folder on the hard drive then double-click
on the InstallCE icon to start the install process for the correct device.

From this information, I feel that vxUtil should be installed in my personal PC. Is it correct?

Could you please answer me about that?

Thanks a lot.

|||

vxUtil should be ultimately installed on device. However, like pretty much all Pocket PC products, it goes through desktop and Active Sync. You install/unzip application on to desktop, then you (or desktop installer) execute something which would push it to device via AS. As a developer you would have to do the same for your application eventually. In some cases there's no desktop installer, just a device CAB file. In that case simply copy CAB to device and click on it from device's file explorer.

|||

Dear Ilya Tumanov,

Your answer is so great. I have already installed it into the PPC, and it scanned the ports in my desktop PC. From the result, the ports 25, 80, 135, 139, 443, 445, 990 and1025 are all open. But the program that I posted yesterday and listed above can not still work. What do I need in the next step?

Moreover, I have changed the connection string to

Dim strConnection As String = "Server=Server1;Integrated Security=SSPI;database=AdventureWorks"

Thanks a lot.

|||

Hi,

I am developing an application for a Pocket PC (PPC). I am using Visual studio 2005, and the Operating system on this PPC is Window Mobile 2003 SE. The followings are my codes to connect PPC with the database on my PC with sql server 2005. But when I cick the button1, the error with "SQL Server does not exist or access denied."

I have already installed vxUtil into the PPC, and Ping is OK. I use it to scan the ports in my desktop PC. From the result, the ports 25, 80, 135, 139, 443, 445, 990 and1025 are all open. But the program can not still work.

Any ideas? Thanks a lot.

Imports System.IO

Imports System.Data

Imports System.Data.SqlClient

Imports System.Math

Imports System.Drawing

Imports System

Public Class Form1

Dim strConnection As String = "Server=server1;Integrated Security=SSPI;database=AdventureWorks"

Dim strQuery As String

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Label1.Text = "Hello, Clicking after."

ReadOrderData()

End Sub

Private Sub ReadOrderData()

Dim queryString As String = "select * from Person.Address where AddressID < '10'"

Using connection As New SqlConnection(strConnection)

Dim command As New SqlCommand(queryString, connection)

connection.Open()

Dim reader As SqlDataReader = command.ExecuteReader()

While reader.Read()

MsgBox(String.Format("{0}, {1}", _

reader(0), reader(1)))

End While

reader.Close()

End Using

End Sub

End Class

|||

Default SQL Server port is 1433 which is not opened so it's not going to work until your SQL Server is properly configured. Also you have to provide credential in connection string, this string is not going to work.

I'm moving this thread to appropriate forum.

|||

Hi,

Thanks for your answer, and right now 1433 port is open. I would like to use windows authentication protocol. Could you please provide a sample for the connection string? Thanks a lot.

|||

Hi,

Thanks for your answer, and right now 1433 port is open. I would like to use windows authentication protocol. Could you please provide a sample for the connection string? Thanks a lot.

|||

This should work:

"Server=YourServerHere,1433;Initial Catalog=YourDataBaseHere;User ID=YourLoginHere;Password=YourPasswordHere;Integrated Security=SSPI;"

Note you have to provide login and password even though windows authentication will be used. That's because there's no login procedure on devices.

|||

Dear Ilya Tumanov,

Thanks a lot for your so many helps, and it works.

|||

so..It must have a password?is it same with windows user's password?what if blank?

if I'm not wrong, if we set the windows password, we should change the SQL password too right?

|||

you should check this link http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=709129&SiteID=1&mode=1

It works for me after a loooooonggg time testing

Can not access the Deleted System Table

I have a problem where I can not perform "Select * from Deleted" within
a trigger, but the problem occurs when I insert/update data from
Enterprise Manager ONLY. The trigger operates fine if I modify data in
Query Analyzer or through VBCode.
I finally found a workaround, modifying the select statement to "Select
* Into #deleted from Deleted", but am worried that will impact
performance on larger databases. I'm 99% certain it is just an access
issue somewhere, I just can't figure out where.
Any help y'all can suggest is appreciated.
Here is a snippet of the trigger:
CREATE trigger InsertUpdate_Table1
On Table1
For Update, Insert As
Declare @.Temp Int
Select * from Deleted
Select @.Temp = @.@.Rowcount
If @.Temp > 0
{rest of the code has been confirmed to work just fine}> Select * from Deleted
> Select @.Temp = @.@.Rowcount
So your trigger returns 2 resultsets? I can see how this would confuse EM
because special application code is needed to handle this situation.
This technique to get the number of updated rows is a bad practice for a
number of reasons. The proper way to get the number of updated rows is with
@.@.ROWCOUNT after the UPDATE statement rather in the trigger itself:
UPDATE dbo.Table1
SET MyColumn = 1
SELECT @.@.ROWCOUNT
Hope this helps.
Dan Guzman
SQL Server MVP
"Fayven" <FayvenWren@.gmail.com> wrote in message
news:1154833842.671883.224220@.b28g2000cwb.googlegroups.com...
>I have a problem where I can not perform "Select * from Deleted" within
> a trigger, but the problem occurs when I insert/update data from
> Enterprise Manager ONLY. The trigger operates fine if I modify data in
> Query Analyzer or through VBCode.
> I finally found a workaround, modifying the select statement to "Select
> * Into #deleted from Deleted", but am worried that will impact
> performance on larger databases. I'm 99% certain it is just an access
> issue somewhere, I just can't figure out where.
> Any help y'all can suggest is appreciated.
> Here is a snippet of the trigger:
> CREATE trigger InsertUpdate_Table1
> On Table1
> For Update, Insert As
> Declare @.Temp Int
> Select * from Deleted
> Select @.Temp = @.@.Rowcount
> If @.Temp > 0
> {rest of the code has been confirmed to work just fine}
>|||FYI - SQL EM is using RPC to run the SQL Statements
--
THANKS & PLEASE RATE THE POSTING.
--RAVI--
"Fayven" wrote:

> I have a problem where I can not perform "Select * from Deleted" within
> a trigger, but the problem occurs when I insert/update data from
> Enterprise Manager ONLY. The trigger operates fine if I modify data in
> Query Analyzer or through VBCode.
> I finally found a workaround, modifying the select statement to "Select
> * Into #deleted from Deleted", but am worried that will impact
> performance on larger databases. I'm 99% certain it is just an access
> issue somewhere, I just can't figure out where.
> Any help y'all can suggest is appreciated.
> Here is a snippet of the trigger:
> CREATE trigger InsertUpdate_Table1
> On Table1
> For Update, Insert As
> Declare @.Temp Int
> Select * from Deleted
> Select @.Temp = @.@.Rowcount
> If @.Temp > 0
> {rest of the code has been confirmed to work just fine}
>sql

Can MSDE and SQL live on the same system?

Hey everyone,
We are running MSDE on a Windows 200 server and it has data in it. What I
would like to know is can we also install MSSQL 2005 on that server and have
assess to both databases. Is there anything we have to do to install it and
make sure it doesn't overwrite or delete the MSDE database?
TIA,
Claytonhi Clayton,
Clayton Sutton wrote:
> Hey everyone,
> We are running MSDE on a Windows 200 server and it has data in it. What I
> would like to know is can we also install MSSQL 2005 on that
> server and have assess to both databases. Is there anything we have
> to do to install it and make sure it doesn't overwrite or delete the
> MSDE database?
MSDE and SQL Server 2005 can coexist on the same computer... just pay
attention, at installation time, not to "upgrade" the MSDE instance to a SQL
Server 2005 one..
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply|||Yes this is possible, you just have to install it as a default or named
instance, which doesn=B4t equal the instance name of the MSDE, although
I doubt that this is possible through the installer to crash the system
that way. They are using different ports, so co-existence on the system
is possible.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Just interesting, why you do not want a run both databases on sql 2005?
Andris
Clayton Sutton wrote:
> Hey everyone,
> We are running MSDE on a Windows 200 server and it has data in it. What I
> would like to know is can we also install MSSQL 2005 on that server and ha
ve
> assess to both databases. Is there anything we have to do to install it a
nd
> make sure it doesn't overwrite or delete the MSDE database?
>
> TIA,
> Clayton
>|||An old app. that one dept. has been using for years was running on MSDE.
Now they are moving to a new program that uses MSSQL 2000/2005. However,
the old data is going to stay in the MSDE database (unless that dept. wants
to pay someone to do a data export/import into MSSQL). I just wanted to
make sure installing MSSQL on the same system as MSDE didn't wipe out MSDE
and all of the old data therein.
Clayton
"Andris" <spameris@.gmail.com> wrote in message
news:Ocnzq1BdGHA.1272@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> Just interesting, why you do not want a run both databases on sql 2005?
> Andris
> Clayton Sutton wrote:|||I just migrated from MSSQL 7 to MSSQL 2005, whithout big headaches.
Simple backup/restore. With MSDE i think u can too, backup that db and
restore to MSSQL 2005. How i understand MSDE is some as MSSQL with some
restrictions on db size,replications etc. All who run un MSDE must run
un MSSQL. I have few msde servers who replicating with MSSQL, sometimes
i need open archive of these msde databases, i do this on MSSQL without
problems.
Sry my poor english
Andris
Clayton Sutton wrote:
> An old app. that one dept. has been using for years was running on MSDE.
> Now they are moving to a new program that uses MSSQL 2000/2005. However,
> the old data is going to stay in the MSDE database (unless that dept. want
s
> to pay someone to do a data export/import into MSSQL). I just wanted to
> make sure installing MSSQL on the same system as MSDE didn't wipe out MSDE
> and all of the old data therein.
>
> Clayton
>
> "Andris" <spameris@.gmail.com> wrote in message
> news:Ocnzq1BdGHA.1272@.TK2MSFTNGP03.phx.gbl...
>
>
>|||Hey Andris,
First thing, your english is fine. Don't ever apologize for that. Truth be
told, I can't spell .
Backing up MSDE is a little tricky. Most backup software doesn't have a
connector for MSDE so backing up and restore becomes a little harder.
The command line command to back one up is:
C:\MSSQL7\Binn>osql -Usa -P -n -Q "Backup Database dbname To Disk =
'c:\mssql7\backup\dbname.bak'"
It took me forever to find that command, and I don't know what the command
is to restore it.
Clayton
"Andris" <spameris@.gmail.com> wrote in message
news:%23sZUlTNdGHA.4108@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
>I just migrated from MSSQL 7 to MSSQL 2005, whithout big headaches. Simple
>backup/restore. With MSDE i think u can too, backup that db and restore to
>MSSQL 2005. How i understand MSDE is some as MSSQL with some restrictions
>on db size,replications etc. All who run un MSDE must run un MSSQL. I have
>few msde servers who replicating with MSSQL, sometimes i need open archive
>of these msde databases, i do this on MSSQL without problems.
> Sry my poor english
> Andris
> Clayton Sutton wrote:|||For the RESTORE syntax, you can see the SQL Server 2005 RESTORE syntax at
http://msdn2.microsoft.com/en-us/library/ms186858.aspx. For SQL Server 2000
(the MSDE info), you can download a copy of Books Online (BOL) at
http://www.microsoft.com/downloads/...DisplayLang=en.
Using the example syntax you provided, it would probably be something like:
osql -Usa -P -n -Q "RESTORE DATABASE dbname FROM
DISK='c:\mssql7\backup\dbname.bak'"
Note, however, that I haven't tested that. :-)
Sincerely,
Steve Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Clayton Sutton" <none@.none.com> wrote in message
news:2mH8g.471159$7i1.118830@.fe06.news.easynews.com...
> Hey Andris,
> First thing, your english is fine. Don't ever apologize for that. Truth
> be told, I can't spell .
> Backing up MSDE is a little tricky. Most backup software doesn't have a
> connector for MSDE so backing up and restore becomes a little harder.
> The command line command to back one up is:
> C:\MSSQL7\Binn>osql -Usa -P -n -Q "Backup Database dbname To Disk =
> 'c:\mssql7\backup\dbname.bak'"
> It took me forever to find that command, and I don't know what the command
> is to restore it.
>
>
> Clayton
>
> "Andris" <spameris@.gmail.com> wrote in message
> news:%23sZUlTNdGHA.4108@.TK2MSFTNGP03.phx.gbl...
>|||Hey Steve,
Would that restore the DB to MSDE only. What if you wanted to restore it
into MSSQL 2000 or 2005? That's really what we are talking about, how to
move from MSDE to MSSQL 2000/2005. Get the data out of MSDE and into MSSQL.
Clayton
"Steve Dybing [MSFT]" <steve.dybing@.online.microsoft.com> wrote in messa
ge
news:uIP9O6QdGHA.1436@.TK2MSFTNGP05.phx.gbl...
> For the RESTORE syntax, you can see the SQL Server 2005 RESTORE syntax at
> http://msdn2.microsoft.com/en-us/library/ms186858.aspx. For SQL Server
> 2000 (the MSDE info), you can download a copy of Books Online (BOL) at
> http://www.microsoft.com/downloads/...DisplayLang=en.
> Using the example syntax you provided, it would probably be something
> like:
> osql -Usa -P -n -Q "RESTORE DATABASE dbname FROM
> DISK='c:\mssql7\backup\dbname.bak'"
> Note, however, that I haven't tested that. :-)
> --
> Sincerely,
> Steve Dybing
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Clayton Sutton" <none@.none.com> wrote in message
> news:2mH8g.471159$7i1.118830@.fe06.news.easynews.com...
>|||MSDE is SQL Server. So you can both attach and restore an MSDE 2000 database
into SQL Server 2000 or
2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Clayton" <none@.none.com> wrote in message news:8GV8g.100380$JV1.10779@.fe05.news.easynews.co
m...
> Hey Steve,
> Would that restore the DB to MSDE only. What if you wanted to restore it
into MSSQL 2000 or 2005?
> That's really what we are talking about, how to move from MSDE to MSSQL 20
00/2005. Get the data
> out of MSDE and into MSSQL.
>
> Clayton
>
> "Steve Dybing [MSFT]" <steve.dybing@.online.microsoft.com> wrote in mes
sage
> news:uIP9O6QdGHA.1436@.TK2MSFTNGP05.phx.gbl...
>

Can MSDE and SQL live on the same system?

Hey everyone,
We are running MSDE on a Windows 200 server and it has data in it. What I
would like to know is can we also install MSSQL 2005 on that server and have
assess to both databases. Is there anything we have to do to install it and
make sure it doesn't overwrite or delete the MSDE database?
TIA,
Claytonhi Clayton,
Clayton Sutton wrote:
> Hey everyone,
> We are running MSDE on a Windows 200 server and it has data in it. What I
> would like to know is can we also install MSSQL 2005 on that
> server and have assess to both databases. Is there anything we have
> to do to install it and make sure it doesn't overwrite or delete the
> MSDE database?
MSDE and SQL Server 2005 can coexist on the same computer... just pay
attention, at installation time, not to "upgrade" the MSDE instance to a SQL
Server 2005 one..
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply|||Yes this is possible, you just have to install it as a default or named
instance, which doesn=B4t equal the instance name of the MSDE, although
I doubt that this is possible through the installer to crash the system
that way. They are using different ports, so co-existence on the system
is possible.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Just interesting, why you do not want a run both databases on sql 2005?
Andris
Clayton Sutton wrote:
> Hey everyone,
> We are running MSDE on a Windows 200 server and it has data in it. What I
> would like to know is can we also install MSSQL 2005 on that server and have
> assess to both databases. Is there anything we have to do to install it and
> make sure it doesn't overwrite or delete the MSDE database?
>
> TIA,
> Clayton
>|||An old app. that one dept. has been using for years was running on MSDE.
Now they are moving to a new program that uses MSSQL 2000/2005. However,
the old data is going to stay in the MSDE database (unless that dept. wants
to pay someone to do a data export/import into MSSQL). I just wanted to
make sure installing MSSQL on the same system as MSDE didn't wipe out MSDE
and all of the old data therein.
Clayton
"Andris" <spameris@.gmail.com> wrote in message
news:Ocnzq1BdGHA.1272@.TK2MSFTNGP03.phx.gbl...
> Just interesting, why you do not want a run both databases on sql 2005?
> Andris
> Clayton Sutton wrote:
>> Hey everyone,
>> We are running MSDE on a Windows 200 server and it has data in it. What
>> I would like to know is can we also install MSSQL 2005 on that server and
>> have assess to both databases. Is there anything we have to do to
>> install it and make sure it doesn't overwrite or delete the MSDE
>> database?
>>
>> TIA,
>> Clayton|||I just migrated from MSSQL 7 to MSSQL 2005, whithout big headaches.
Simple backup/restore. With MSDE i think u can too, backup that db and
restore to MSSQL 2005. How i understand MSDE is some as MSSQL with some
restrictions on db size,replications etc. All who run un MSDE must run
un MSSQL. I have few msde servers who replicating with MSSQL, sometimes
i need open archive of these msde databases, i do this on MSSQL without
problems.
Sry my poor english
Andris
Clayton Sutton wrote:
> An old app. that one dept. has been using for years was running on MSDE.
> Now they are moving to a new program that uses MSSQL 2000/2005. However,
> the old data is going to stay in the MSDE database (unless that dept. wants
> to pay someone to do a data export/import into MSSQL). I just wanted to
> make sure installing MSSQL on the same system as MSDE didn't wipe out MSDE
> and all of the old data therein.
>
> Clayton
>
> "Andris" <spameris@.gmail.com> wrote in message
> news:Ocnzq1BdGHA.1272@.TK2MSFTNGP03.phx.gbl...
>>Just interesting, why you do not want a run both databases on sql 2005?
>>Andris
>>Clayton Sutton wrote:
>>Hey everyone,
>>We are running MSDE on a Windows 200 server and it has data in it. What
>>I would like to know is can we also install MSSQL 2005 on that server and
>>have assess to both databases. Is there anything we have to do to
>>install it and make sure it doesn't overwrite or delete the MSDE
>>database?
>>
>>TIA,
>>Clayton
>
>|||Hey Andris,
First thing, your english is fine. Don't ever apologize for that. Truth be
told, I can't spell :).
Backing up MSDE is a little tricky. Most backup software doesn't have a
connector for MSDE so backing up and restore becomes a little harder.
The command line command to back one up is:
C:\MSSQL7\Binn>osql -Usa -P -n -Q "Backup Database dbname To Disk ='c:\mssql7\backup\dbname.bak'"
It took me forever to find that command, and I don't know what the command
is to restore it.
Clayton
"Andris" <spameris@.gmail.com> wrote in message
news:%23sZUlTNdGHA.4108@.TK2MSFTNGP03.phx.gbl...
>I just migrated from MSSQL 7 to MSSQL 2005, whithout big headaches. Simple
>backup/restore. With MSDE i think u can too, backup that db and restore to
>MSSQL 2005. How i understand MSDE is some as MSSQL with some restrictions
>on db size,replications etc. All who run un MSDE must run un MSSQL. I have
>few msde servers who replicating with MSSQL, sometimes i need open archive
>of these msde databases, i do this on MSSQL without problems.
> Sry my poor english
> Andris
> Clayton Sutton wrote:
>> An old app. that one dept. has been using for years was running on MSDE.
>> Now they are moving to a new program that uses MSSQL 2000/2005. However,
>> the old data is going to stay in the MSDE database (unless that dept.
>> wants to pay someone to do a data export/import into MSSQL). I just
>> wanted to make sure installing MSSQL on the same system as MSDE didn't
>> wipe out MSDE and all of the old data therein.
>>
>> Clayton
>>
>> "Andris" <spameris@.gmail.com> wrote in message
>> news:Ocnzq1BdGHA.1272@.TK2MSFTNGP03.phx.gbl...
>>Just interesting, why you do not want a run both databases on sql 2005?
>>Andris
>>Clayton Sutton wrote:
>>Hey everyone,
>>We are running MSDE on a Windows 200 server and it has data in it. What
>>I would like to know is can we also install MSSQL 2005 on that server
>>and have assess to both databases. Is there anything we have to do to
>>install it and make sure it doesn't overwrite or delete the MSDE
>>database?
>>
>>TIA,
>>Clayton
>>|||For the RESTORE syntax, you can see the SQL Server 2005 RESTORE syntax at
http://msdn2.microsoft.com/en-us/library/ms186858.aspx. For SQL Server 2000
(the MSDE info), you can download a copy of Books Online (BOL) at
http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en.
Using the example syntax you provided, it would probably be something like:
osql -Usa -P -n -Q "RESTORE DATABASE dbname FROM
DISK='c:\mssql7\backup\dbname.bak'"
Note, however, that I haven't tested that. :-)
--
Sincerely,
Steve Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Clayton Sutton" <none@.none.com> wrote in message
news:2mH8g.471159$7i1.118830@.fe06.news.easynews.com...
> Hey Andris,
> First thing, your english is fine. Don't ever apologize for that. Truth
> be told, I can't spell :).
> Backing up MSDE is a little tricky. Most backup software doesn't have a
> connector for MSDE so backing up and restore becomes a little harder.
> The command line command to back one up is:
> C:\MSSQL7\Binn>osql -Usa -P -n -Q "Backup Database dbname To Disk => 'c:\mssql7\backup\dbname.bak'"
> It took me forever to find that command, and I don't know what the command
> is to restore it.
>
>
> Clayton
>
> "Andris" <spameris@.gmail.com> wrote in message
> news:%23sZUlTNdGHA.4108@.TK2MSFTNGP03.phx.gbl...
>>I just migrated from MSSQL 7 to MSSQL 2005, whithout big headaches. Simple
>>backup/restore. With MSDE i think u can too, backup that db and restore to
>>MSSQL 2005. How i understand MSDE is some as MSSQL with some restrictions
>>on db size,replications etc. All who run un MSDE must run un MSSQL. I have
>>few msde servers who replicating with MSSQL, sometimes i need open archive
>>of these msde databases, i do this on MSSQL without problems.
>> Sry my poor english
>> Andris
>> Clayton Sutton wrote:
>> An old app. that one dept. has been using for years was running on MSDE.
>> Now they are moving to a new program that uses MSSQL 2000/2005.
>> However, the old data is going to stay in the MSDE database (unless that
>> dept. wants to pay someone to do a data export/import into MSSQL). I
>> just wanted to make sure installing MSSQL on the same system as MSDE
>> didn't wipe out MSDE and all of the old data therein.
>>
>> Clayton
>>
>> "Andris" <spameris@.gmail.com> wrote in message
>> news:Ocnzq1BdGHA.1272@.TK2MSFTNGP03.phx.gbl...
>>Just interesting, why you do not want a run both databases on sql 2005?
>>Andris
>>Clayton Sutton wrote:
>>Hey everyone,
>>We are running MSDE on a Windows 200 server and it has data in it.
>>What I would like to know is can we also install MSSQL 2005 on that
>>server and have assess to both databases. Is there anything we have to
>>do to install it and make sure it doesn't overwrite or delete the MSDE
>>database?
>>
>>TIA,
>>Clayton
>>
>|||Hey Steve,
Would that restore the DB to MSDE only. What if you wanted to restore it
into MSSQL 2000 or 2005? That's really what we are talking about, how to
move from MSDE to MSSQL 2000/2005. Get the data out of MSDE and into MSSQL.
Clayton
"Steve Dybing [MSFT]" <steve.dybing@.online.microsoft.com> wrote in message
news:uIP9O6QdGHA.1436@.TK2MSFTNGP05.phx.gbl...
> For the RESTORE syntax, you can see the SQL Server 2005 RESTORE syntax at
> http://msdn2.microsoft.com/en-us/library/ms186858.aspx. For SQL Server
> 2000 (the MSDE info), you can download a copy of Books Online (BOL) at
> http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en.
> Using the example syntax you provided, it would probably be something
> like:
> osql -Usa -P -n -Q "RESTORE DATABASE dbname FROM
> DISK='c:\mssql7\backup\dbname.bak'"
> Note, however, that I haven't tested that. :-)
> --
> Sincerely,
> Steve Dybing
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Clayton Sutton" <none@.none.com> wrote in message
> news:2mH8g.471159$7i1.118830@.fe06.news.easynews.com...
>> Hey Andris,
>> First thing, your english is fine. Don't ever apologize for that. Truth
>> be told, I can't spell :).
>> Backing up MSDE is a little tricky. Most backup software doesn't have a
>> connector for MSDE so backing up and restore becomes a little harder.
>> The command line command to back one up is:
>> C:\MSSQL7\Binn>osql -Usa -P -n -Q "Backup Database dbname To Disk =>> 'c:\mssql7\backup\dbname.bak'"
>> It took me forever to find that command, and I don't know what the
>> command is to restore it.
>>
>>
>> Clayton
>>
>> "Andris" <spameris@.gmail.com> wrote in message
>> news:%23sZUlTNdGHA.4108@.TK2MSFTNGP03.phx.gbl...
>>I just migrated from MSSQL 7 to MSSQL 2005, whithout big headaches.
>>Simple backup/restore. With MSDE i think u can too, backup that db and
>>restore to MSSQL 2005. How i understand MSDE is some as MSSQL with some
>>restrictions on db size,replications etc. All who run un MSDE must run un
>>MSSQL. I have few msde servers who replicating with MSSQL, sometimes i
>>need open archive of these msde databases, i do this on MSSQL without
>>problems.
>> Sry my poor english
>> Andris
>> Clayton Sutton wrote:
>> An old app. that one dept. has been using for years was running on
>> MSDE. Now they are moving to a new program that uses MSSQL 2000/2005.
>> However, the old data is going to stay in the MSDE database (unless
>> that dept. wants to pay someone to do a data export/import into MSSQL).
>> I just wanted to make sure installing MSSQL on the same system as MSDE
>> didn't wipe out MSDE and all of the old data therein.
>>
>> Clayton
>>
>> "Andris" <spameris@.gmail.com> wrote in message
>> news:Ocnzq1BdGHA.1272@.TK2MSFTNGP03.phx.gbl...
>>Just interesting, why you do not want a run both databases on sql 2005?
>>Andris
>>Clayton Sutton wrote:
>>Hey everyone,
>>We are running MSDE on a Windows 200 server and it has data in it.
>>What I would like to know is can we also install MSSQL 2005 on that
>>server and have assess to both databases. Is there anything we have
>>to do to install it and make sure it doesn't overwrite or delete the
>>MSDE database?
>>
>>TIA,
>>Clayton
>>
>|||MSDE is SQL Server. So you can both attach and restore an MSDE 2000 database into SQL Server 2000 or
2005.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Clayton" <none@.none.com> wrote in message news:8GV8g.100380$JV1.10779@.fe05.news.easynews.com...
> Hey Steve,
> Would that restore the DB to MSDE only. What if you wanted to restore it into MSSQL 2000 or 2005?
> That's really what we are talking about, how to move from MSDE to MSSQL 2000/2005. Get the data
> out of MSDE and into MSSQL.
>
> Clayton
>
> "Steve Dybing [MSFT]" <steve.dybing@.online.microsoft.com> wrote in message
> news:uIP9O6QdGHA.1436@.TK2MSFTNGP05.phx.gbl...
>> For the RESTORE syntax, you can see the SQL Server 2005 RESTORE syntax at
>> http://msdn2.microsoft.com/en-us/library/ms186858.aspx. For SQL Server 2000 (the MSDE info), you
>> can download a copy of Books Online (BOL) at
>> http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en.
>> Using the example syntax you provided, it would probably be something like:
>> osql -Usa -P -n -Q "RESTORE DATABASE dbname FROM DISK='c:\mssql7\backup\dbname.bak'"
>> Note, however, that I haven't tested that. :-)
>> --
>> Sincerely,
>> Steve Dybing
>> This posting is provided "AS IS" with no warranties, and confers no rights.
>> "Clayton Sutton" <none@.none.com> wrote in message
>> news:2mH8g.471159$7i1.118830@.fe06.news.easynews.com...
>> Hey Andris,
>> First thing, your english is fine. Don't ever apologize for that. Truth be told, I can't spell
>> :).
>> Backing up MSDE is a little tricky. Most backup software doesn't have a connector for MSDE so
>> backing up and restore becomes a little harder.
>> The command line command to back one up is:
>> C:\MSSQL7\Binn>osql -Usa -P -n -Q "Backup Database dbname To Disk =>> 'c:\mssql7\backup\dbname.bak'"
>> It took me forever to find that command, and I don't know what the command is to restore it.
>>
>>
>> Clayton
>>
>> "Andris" <spameris@.gmail.com> wrote in message news:%23sZUlTNdGHA.4108@.TK2MSFTNGP03.phx.gbl...
>>I just migrated from MSSQL 7 to MSSQL 2005, whithout big headaches. Simple backup/restore. With
>>MSDE i think u can too, backup that db and restore to MSSQL 2005. How i understand MSDE is some
>>as MSSQL with some restrictions on db size,replications etc. All who run un MSDE must run un
>>MSSQL. I have few msde servers who replicating with MSSQL, sometimes i need open archive of
>>these msde databases, i do this on MSSQL without problems.
>> Sry my poor english
>> Andris
>> Clayton Sutton wrote:
>> An old app. that one dept. has been using for years was running on MSDE. Now they are moving
>> to a new program that uses MSSQL 2000/2005. However, the old data is going to stay in the MSDE
>> database (unless that dept. wants to pay someone to do a data export/import into MSSQL). I
>> just wanted to make sure installing MSSQL on the same system as MSDE didn't wipe out MSDE and
>> all of the old data therein.
>>
>> Clayton
>>
>> "Andris" <spameris@.gmail.com> wrote in message news:Ocnzq1BdGHA.1272@.TK2MSFTNGP03.phx.gbl...
>>Just interesting, why you do not want a run both databases on sql 2005?
>>Andris
>>Clayton Sutton wrote:
>>>Hey everyone,
>>>
>>>We are running MSDE on a Windows 200 server and it has data in it. What I would like to know
>>>is can we also install MSSQL 2005 on that server and have assess to both databases. Is there
>>>anything we have to do to install it and make sure it doesn't overwrite or delete the MSDE
>>>database?
>>>
>>>
>>>TIA,
>>>
>>>Clayton
>>
>>
>|||From your post it sounds like you believe that MSSQL and MSDE are two
different database formats. They are not. MSDE is merely a scaled-down
version of SQL Server, and the file formats are identical.
I, personally, develop in MSDE on my development machine, and then I make
changes to the live database which is running on SQL Server. When I need a
new copy of the data, I download a backup file from the SQL Server database
and run it in my MSDE. The two formats are identical.
Thus, while installing MSSQL might create conflicts with running MSDE, the
*data itself* will remain untouched. Since your MSDE data is in SQL 7
format, there may be issues with running your data on a later version of
MSSQL, but those should be able to be resolved.
"Clayton Sutton" <none@.none.com> wrote in message
news:xIt8g.391355$4P2.48017@.fe03.news.easynews.com...
> An old app. that one dept. has been using for years was running on MSDE.
> Now they are moving to a new program that uses MSSQL 2000/2005. However,
> the old data is going to stay in the MSDE database (unless that dept.
> wants to pay someone to do a data export/import into MSSQL). I just
> wanted to make sure installing MSSQL on the same system as MSDE didn't
> wipe out MSDE and all of the old data therein.
>
> Clayton
>
> "Andris" <spameris@.gmail.com> wrote in message
> news:Ocnzq1BdGHA.1272@.TK2MSFTNGP03.phx.gbl...
>> Just interesting, why you do not want a run both databases on sql 2005?
>> Andris
>> Clayton Sutton wrote:
>> Hey everyone,
>> We are running MSDE on a Windows 200 server and it has data in it. What
>> I would like to know is can we also install MSSQL 2005 on that server
>> and have assess to both databases. Is there anything we have to do to
>> install it and make sure it doesn't overwrite or delete the MSDE
>> database?
>>
>> TIA,
>> Clayton
>

Can IIF() function be used in SELECT statment?

I try to use IIF() function in SELECT statmenmt in a store procedure
difination.
SELECT iif( month(ref_date)=1 , 1 , 0 )
FROM MyTable
But the system always show me
Incorrect syntax near '='
What's the corect syntax of iif function?
Thank you.Hi!
There is no IIF function in T-SQL, but you can use CASE expression instead.
Do please look for the syntax in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Xiaotao, Lu" <luxiaotao@.hotmail.com> wrote in message
news:eLm9ieCAEHA.1608@.TK2MSFTNGP11.phx.gbl...
> I try to use IIF() function in SELECT statmenmt in a store procedure
> difination.
> SELECT iif( month(ref_date)=1 , 1 , 0 )
> FROM MyTable
> But the system always show me
> Incorrect syntax near '='
> What's the corect syntax of iif function?
> Thank you.
>|||Use CASE:
SELECT CASE WHEN MONTH(ref_date)=1 THEN 1 ELSE 0 END
FROM MyTable
David Portas
SQL Server MVP
--

Can IIF() function be used in SELECT statment?

I try to use IIF() function in SELECT statmenmt in a store procedure
difination.
SELECT iif( month(ref_date)=1 , 1 , 0 )
FROM MyTable
But the system always show me
Incorrect syntax near '='
What's the corect syntax of iif function?
Thank you.Hi!
There is no IIF function in T-SQL, but you can use CASE expression instead.
Do please look for the syntax in Books OnLine.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Xiaotao, Lu" <luxiaotao@.hotmail.com> wrote in message
news:eLm9ieCAEHA.1608@.TK2MSFTNGP11.phx.gbl...
> I try to use IIF() function in SELECT statmenmt in a store procedure
> difination.
> SELECT iif( month(ref_date)=1 , 1 , 0 )
> FROM MyTable
> But the system always show me
> Incorrect syntax near '='
> What's the corect syntax of iif function?
> Thank you.
>|||Use CASE:
SELECT CASE WHEN MONTH(ref_date)=1 THEN 1 ELSE 0 END
FROM MyTable
--
David Portas
SQL Server MVP
--

Can I use ALTER LOGIN in a stored procedure?

Can the ALTER USER statement be used (without a hack like using EXEC) in a stored procedure? I know that the sp_password system stored procedure can not be. Additionally, it is being deprecated anyway. I guess what is boggling me about my attempts so far relate to the errors I am getting due to the user being specified not being in quotes in the syntax. All of the searching I have done so far have come up lame so far; the only examples I have found about it were in scripts that create other scripts for transferring users and other administrative tasks that would be run from the query window, but not from an application. To be complete as possible, here is an example of a script the returns errors:

ALTER PROC [dbo].[lbxChangePassword]
(
@.loginid nvarchar(180),
@.oldpassword nvarchar(40),
@.newpassword nvarchar(40)
) AS BEGIN

IF @.oldpassword = (SELECT password FROM contacts WHERE loginid = @.loginid)
BEGIN
BEGIN TRANSACTION
UPDATE contacts
SET password = @.newpassword
WHERE loginid = @.loginid

ALTER LOGIN @.loginid WITH PASSWORD=@.newpassword OLD_PASSWORD=@.oldpassword
END
ELSE
BEGIN
RAISERROR(N'The password you entered does not match your current password.', 16, 1)
RETURN
END

IF @.@.ERROR <> 0
BEGIN
RAISERROR(N'There was an error creating your new password.', 16, 1)
RETURN
END

COMMIT TRANSACTION

END

************
This returns:

Msg 102, Level 15, State 1, Procedure lbxChangePassword, Line 15
Incorrect syntax near '@.loginid'.
Msg 319, Level 15, State 1, Procedure lbxChangePassword, Line 15
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
************

If ALTER LOGIN isn't how to change the password, then please tell me what the correct practice of changing a password is. I want to use the CURRENT_USER keyword in my queries and want I can't finish setting that up until I have this resolved because users will need to change their own passwords through the application I am developing.

I guess you have to put that in dynamic sql to execute:

DECLARE @.SQLStrng NVARCHAR(500)
SET @.SQLString = 'ALTER LOGIN ' + @.loginid + ' WITH PASSWORD= '' + @.newpassword + '' OLD_PASSWORD= '' + @.oldpassword + '''
EXEC(SQLString )

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Yes, the DDL does not accept variables. If you build the string dynamically, use quotename() around the variable names to get them quoted properly:

DECLARE @.SQLStrng NVARCHAR(500)
SET @.SQLString = 'ALTER LOGIN ' + quotename(@.loginid) + ' WITH PASSWORD= ' + quotename(@.newpassword, '''') + ' OLD_PASSWORD= ' + quotename(@.oldpassword, '''')
EXEC(SQLString )

Thanks
Laurentiu

|||

I'll try this, but I was hoping that there would be a way to do this without using a "hack".

Am I not approaching this the right way? The user will be logging into a PHP application and I need for them to be able to change their password. Would you recommend a stored procedure to accomplish this like the ones we've been writing about, or is there a best practice for this. I mean, otherwise how can one administer database users from a web app and still use SQL Server's security model? I don't understand Microsoft's reasoning in this architecture.

|||

Writing a stored procedure to accomplish this is OK. If you don't like to build the dynamic SQL and execute it with EXEC, you might prefer using sp_executesql instead: http://msdn2.microsoft.com/en-US/library/ms175170(SQL.90).aspx.

Thanks
Laurentiu

|||

Well, I read a post by a SQL Server MVP that said that another way to do this is to specify the old/new password in a connection string to the SQL Server Native Client. e.g.:

"Provider=SQLNCLI;Data Source=MyServer;User ID=MyLogin;Old Password=oldpassword;Password=password"

But I don't think PHP is friendly to connection strings though; I tried fiddling with ODBC connections in PHP with no luck. Has anyone heard anything about this?

|||

Try posting on SQL Server Data Access for more details on the connection string idea. I think this connection string is probably meant to be used when a login password is expired; it is not the regular way of changing a login password. If you're looking at all possible ways of changing a login password, you should also check the SQL Server SMO/DMO forum - you could write a CLR procedure to perform this action (see .NET Framework inside SQL Server for questions on using CLR inside SQL Server).

Thanks
Laurentiu

|||

I'm just a curious person :-) Thanks. Good Ideas.

-Ryan

Can I use "Local System" account ?

We are running Novell as NOS and we start deploying SQL Server 2005.
During the installation, it asks for the account to be used for "SQL Server
Service" / "SQL Server Agent" service. Could it be possible for me to use
"Local System" account ? I have tried to use other account but I have
difficulty when backing up / restoring database.
Your advice is sought.
PeterIf your SQL Server 2005 in a domain environment, it's recommended using a
domain account as SQL Server service. And for security purposes, it's
advised using different service accounts for different services.
If you use Local System Account (that has all administrative rights on your
local machine) in a domain environment, then you will face some
difficulties.
Please read the following article which is related to this topic and I
believe it will be very helpful for you:
http://technet.microsoft.com/en-us/library/ms143691.aspx
Ekrem Önsoy
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:uuwmmA48HHA.4612@.TK2MSFTNGP03.phx.gbl...
> We are running Novell as NOS and we start deploying SQL Server 2005.
> During the installation, it asks for the account to be used for "SQL
> Server Service" / "SQL Server Agent" service. Could it be possible for me
> to use "Local System" account ? I have tried to use other account but I
> have difficulty when backing up / restoring database.
> Your advice is sought.
> Peter
>|||Dear Ekrem,
We are using Novell as NOS and thus there is no domain at our site. It is
the reason why I wonder whether I can use "Local System" account OR I have
to create a local account and grant permission ?
Thanks again
Peter
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:0FA02F27-280C-402A-9260-32B0AB637FC8@.microsoft.com...
> If your SQL Server 2005 in a domain environment, it's recommended using a
> domain account as SQL Server service. And for security purposes, it's
> advised using different service accounts for different services.
> If you use Local System Account (that has all administrative rights on
> your local machine) in a domain environment, then you will face some
> difficulties.
> Please read the following article which is related to this topic and I
> believe it will be very helpful for you:
> http://technet.microsoft.com/en-us/library/ms143691.aspx
>
> --
> Ekrem Önsoy
>
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:uuwmmA48HHA.4612@.TK2MSFTNGP03.phx.gbl...
>> We are running Novell as NOS and we start deploying SQL Server 2005.
>> During the installation, it asks for the account to be used for "SQL
>> Server Service" / "SQL Server Agent" service. Could it be possible for
>> me to use "Local System" account ? I have tried to use other account but
>> I have difficulty when backing up / restoring database.
>> Your advice is sought.
>> Peter
>>
>

Can I shift the stress of SQL processing using a linked server?

I often need to run intensive queries against our live database but
this affects the performance of the system while the queries are
executing so I'm looking for a way to shift the processing to another
server. I have tried creating a linked server on my local PC pointing
at the live DB but it seems to stress both servers. Does anybody have
any suggestions how I can achieve this?
Thanks,
LiamCan you create a refreshable replica of your Live DB. Quite often reporting
requirements cause this kind of impact. We replicate our live databases to
reporting servers for just this purpose.
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Liam Weston" <liam_weston@.hotmail.com> wrote in message
news:5f9a8c3b.0308200334.7a43ef56@.posting.google.com...
> I often need to run intensive queries against our live database but
> this affects the performance of the system while the queries are
> executing so I'm looking for a way to shift the processing to another
> server. I have tried creating a linked server on my local PC pointing
> at the live DB but it seems to stress both servers. Does anybody have
> any suggestions how I can achieve this?
> Thanks,
> Liam|||Linked servers are not of use in your scenario since the server where the
data lives still has to do a substantial, if not majority, of the work. The
solution you want is to take a copy of the data to another machine to do
your reporting. There are numerous strategies for doing this depending on
your exact scenario. Replication, Log Shipping, or simply backing up and
restoring the database on another server are all potential solutions.
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"Liam Weston" <liam_weston@.hotmail.com> wrote in message
news:5f9a8c3b.0308200334.7a43ef56@.posting.google.com...
> I often need to run intensive queries against our live database but
> this affects the performance of the system while the queries are
> executing so I'm looking for a way to shift the processing to another
> server. I have tried creating a linked server on my local PC pointing
> at the live DB but it seems to stress both servers. Does anybody have
> any suggestions how I can achieve this?
> Thanks,
> Liam|||Allan,
I plan to implement replication in the coming months and this would
certainly solve the problem. In the short term I can extract data to a
different server 'out of hours' but I was looking for a way of running
queries during peak times at short notice.
Thanks,
Liam

can I restore sql server 6.5 without backup

My operation system is destroyed and I have a Sql server 6.5 on it .
I had never done the backup process , Can I using the SQL server 6.5's File
to restore it ?
thanks anyway.
Hi,
If you have all the Physical DAT files , including Master database . Then
all you have to do is..
1. Take a copy of all DAT files to a safe location
2. Install SQL 6.5 in the same folder as old installation
3. apply the same service pack as old
4. stop sql server and sql executive
5. Copy all the DAT files to the same folder
6. Start SQl server and SQL executive service.
Now your SQl server will be back as old.
Note:
Now onwards please prepare and backup strategy to backup your databases.
Thanks
Hari
MCDBA
"william.huang" <william.huang@.saturn.yzu.edu.tw> wrote in message
news:#QQ43HSeEHA.3348@.TK2MSFTNGP09.phx.gbl...
> My operation system is destroyed and I have a Sql server 6.5 on it .
> I had never done the backup process , Can I using the SQL server 6.5's
File
> to restore it ?
> thanks anyway.
>

can I restore sql server 6.5 without backup

My operation system is destroyed and I have a Sql server 6.5 on it .
I had never done the backup process , Can I using the SQL server 6.5's File
to restore it ?
thanks anyway.Hi,
If you have all the Physical DAT files , including Master database . Then
all you have to do is..
1. Take a copy of all DAT files to a safe location
2. Install SQL 6.5 in the same folder as old installation
3. apply the same service pack as old
4. stop sql server and sql executive
5. Copy all the DAT files to the same folder
6. Start SQl server and SQL executive service.
Now your SQl server will be back as old.
Note:
Now onwards please prepare and backup strategy to backup your databases.
Thanks
Hari
MCDBA
"william.huang" <william.huang@.saturn.yzu.edu.tw> wrote in message
news:#QQ43HSeEHA.3348@.TK2MSFTNGP09.phx.gbl...
> My operation system is destroyed and I have a Sql server 6.5 on it .
> I had never done the backup process , Can I using the SQL server 6.5's
File
> to restore it ?
> thanks anyway.
>

can I restore sql server 6.5 without backup

My operation system is destroyed and I have a Sql server 6.5 on it .
I had never done the backup process , Can I using the SQL server 6.5's File
to restore it ?
thanks anyway.Hi,
If you have all the Physical DAT files , including Master database . Then
all you have to do is..
1. Take a copy of all DAT files to a safe location
2. Install SQL 6.5 in the same folder as old installation
3. apply the same service pack as old
4. stop sql server and sql executive
5. Copy all the DAT files to the same folder
6. Start SQl server and SQL executive service.
Now your SQl server will be back as old.
Note:
Now onwards please prepare and backup strategy to backup your databases.
Thanks
Hari
MCDBA
"william.huang" <william.huang@.saturn.yzu.edu.tw> wrote in message
news:#QQ43HSeEHA.3348@.TK2MSFTNGP09.phx.gbl...
> My operation system is destroyed and I have a Sql server 6.5 on it .
> I had never done the backup process , Can I using the SQL server 6.5's
File
> to restore it ?
> thanks anyway.
>

Can I restore msdb from another sqlserver 's backup

My msdb,system database, of sqlserver is marked suspected,
I cant recovery it for any method.
Therefore, Can I restore it from another sqlserver's
msdb backup?
Hi,
It is not required to restore the MSDB from another server. Best option
will be drop the MSDB database and run the INSTMSDB.SQL script
from sql server installation directory\install folder.
Follow the below steps:-
1. Login to sql server using query analyzer
2. Drop the MSD database
DROP database MSDB
3. Open the INSTMSDB.SQL file in query analyzer
4. Just execute the script. This create the MSDB database, ALL objects ,
......
5. After the succesful execution you will get a fresh MSDB.
Thanks
Hari
MCDBA
"StarNet" <anonymous@.discussions.microsoft.com> wrote in message
news:9e5b01c4792d$3cf98050$a601280a@.phx.gbl...
> My msdb,system database, of sqlserver is marked suspected,
> I cant recovery it for any method.
> Therefore, Can I restore it from another sqlserver's
> msdb backup?
|||Hi,
We have the same problem with a suspected MSDB.
We tried to drop the msdb but got the message that the database cannot be
droped because it'a a system database.
How can we drop the database anyway and solve our problem?
Best Regards,
Karin W
"Hari Prasad" wrote:

> Hi,
> It is not required to restore the MSDB from another server. Best option
> will be drop the MSDB database and run the INSTMSDB.SQL script
> from sql server installation directory\install folder.
> Follow the below steps:-
> 1. Login to sql server using query analyzer
> 2. Drop the MSD database
> DROP database MSDB
> 3. Open the INSTMSDB.SQL file in query analyzer
> 4. Just execute the script. This create the MSDB database, ALL objects ,
> ......
> 5. After the succesful execution you will get a fresh MSDB.
> Thanks
> Hari
> MCDBA
>
> "StarNet" <anonymous@.discussions.microsoft.com> wrote in message
> news:9e5b01c4792d$3cf98050$a601280a@.phx.gbl...
>
>
|||You should find info on this in KB 224071.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Karin W" <Karin W@.discussions.microsoft.com> wrote in message
news:417700A9-7115-481C-8884-BA26136A8E93@.microsoft.com...[vbcol=seagreen]
> Hi,
> We have the same problem with a suspected MSDB.
> We tried to drop the msdb but got the message that the database cannot be
> droped because it'a a system database.
> How can we drop the database anyway and solve our problem?
> Best Regards,
> Karin W
> "Hari Prasad" wrote:

Can I restore msdb from another sqlserver 's backup

My msdb,system database, of sqlserver is marked suspected,
I cant recovery it for any method.
Therefore, Can I restore it from another sqlserver's
msdb backup?Hi,
It is not required to restore the MSDB from another server. Best option
will be drop the MSDB database and run the INSTMSDB.SQL script
from sql server installation directory\install folder.
Follow the below steps:-
1. Login to sql server using query analyzer
2. Drop the MSD database
DROP database MSDB
3. Open the INSTMSDB.SQL file in query analyzer
4. Just execute the script. This create the MSDB database, ALL objects ,
.....
5. After the succesful execution you will get a fresh MSDB.
Thanks
Hari
MCDBA
"StarNet" <anonymous@.discussions.microsoft.com> wrote in message
news:9e5b01c4792d$3cf98050$a601280a@.phx.gbl...
> My msdb,system database, of sqlserver is marked suspected,
> I cant recovery it for any method.
> Therefore, Can I restore it from another sqlserver's
> msdb backup?|||Hi,
We have the same problem with a suspected MSDB.
We tried to drop the msdb but got the message that the database cannot be
droped because it'a a system database.
How can we drop the database anyway and solve our problem?
Best Regards,
Karin W
"Hari Prasad" wrote:
> Hi,
> It is not required to restore the MSDB from another server. Best option
> will be drop the MSDB database and run the INSTMSDB.SQL script
> from sql server installation directory\install folder.
> Follow the below steps:-
> 1. Login to sql server using query analyzer
> 2. Drop the MSD database
> DROP database MSDB
> 3. Open the INSTMSDB.SQL file in query analyzer
> 4. Just execute the script. This create the MSDB database, ALL objects ,
> ......
> 5. After the succesful execution you will get a fresh MSDB.
> Thanks
> Hari
> MCDBA
>
> "StarNet" <anonymous@.discussions.microsoft.com> wrote in message
> news:9e5b01c4792d$3cf98050$a601280a@.phx.gbl...
> > My msdb,system database, of sqlserver is marked suspected,
> > I cant recovery it for any method.
> > Therefore, Can I restore it from another sqlserver's
> > msdb backup?
>
>|||You should find info on this in KB 224071.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Karin W" <Karin W@.discussions.microsoft.com> wrote in message
news:417700A9-7115-481C-8884-BA26136A8E93@.microsoft.com...
> Hi,
> We have the same problem with a suspected MSDB.
> We tried to drop the msdb but got the message that the database cannot be
> droped because it'a a system database.
> How can we drop the database anyway and solve our problem?
> Best Regards,
> Karin W
> "Hari Prasad" wrote:
> > Hi,
> >
> > It is not required to restore the MSDB from another server. Best option
> > will be drop the MSDB database and run the INSTMSDB.SQL script
> > from sql server installation directory\install folder.
> >
> > Follow the below steps:-
> >
> > 1. Login to sql server using query analyzer
> > 2. Drop the MSD database
> >
> > DROP database MSDB
> >
> > 3. Open the INSTMSDB.SQL file in query analyzer
> >
> > 4. Just execute the script. This create the MSDB database, ALL objects ,
> > ......
> >
> > 5. After the succesful execution you will get a fresh MSDB.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> > "StarNet" <anonymous@.discussions.microsoft.com> wrote in message
> > news:9e5b01c4792d$3cf98050$a601280a@.phx.gbl...
> > > My msdb,system database, of sqlserver is marked suspected,
> > > I cant recovery it for any method.
> > > Therefore, Can I restore it from another sqlserver's
> > > msdb backup?
> >
> >
> >