Archiving Problem

Hi!
The SQL Server Express run several versions of an Archive file, such as
Archive #1, etc. under 'SQL Server Logs'.
What is generating these archives? Also, How can I see what has been
archived? Can I stop the server from doing this or needed for to operate?
Thank you very much!
-BahmanHI
"Bahman" wrote:
> Hi!
> The SQL Server Express run several versions of an Archive file, such as
> Archive #1, etc. under 'SQL Server Logs'.
> What is generating these archives? Also, How can I see what has been
> archived? Can I stop the server from doing this or needed for to operate?
> Thank you very much!
> -Bahman
>
These are old versions of the ERRORLOG file. A new ERRORLOG is created when
you restart SQL Server or run sp_cycle_errorlog. The file will be ERRORLOG.1
ERRORLOG.2 etc.. in the LOGS directory. The Displayed name in SSMS and EM
will have the date and time that the log goes up to. More information is in
Books online.
John|||John,
Thanks a million. I guess the word archive was throwing me off. I was
looking in the wrong place. Somehow I thought it is archiving, so that I can
restore from it. But no info on that anywhere. Thank you very much for your
help.
-Bahman
"John Bell" wrote:
> HI
> "Bahman" wrote:
> > Hi!
> >
> > The SQL Server Express run several versions of an Archive file, such as
> > Archive #1, etc. under 'SQL Server Logs'.
> >
> > What is generating these archives? Also, How can I see what has been
> > archived? Can I stop the server from doing this or needed for to operate?
> >
> > Thank you very much!
> >
> > -Bahman
> >
> These are old versions of the ERRORLOG file. A new ERRORLOG is created when
> you restart SQL Server or run sp_cycle_errorlog. The file will be ERRORLOG.1
> ERRORLOG.2 etc.. in the LOGS directory. The Displayed name in SSMS and EM
> will have the date and time that the log goes up to. More information is in
> Books online.
> John
>sql

Archiving Problem

Hi!
The SQL Server Express run several versions of an Archive file, such as
Archive #1, etc. under 'SQL Server Logs'.
What is generating these archives? Also, How can I see what has been
archived? Can I stop the server from doing this or needed for to operate?
Thank you very much!
-Bahman
HI
"Bahman" wrote:

> Hi!
> The SQL Server Express run several versions of an Archive file, such as
> Archive #1, etc. under 'SQL Server Logs'.
> What is generating these archives? Also, How can I see what has been
> archived? Can I stop the server from doing this or needed for to operate?
> Thank you very much!
> -Bahman
>
These are old versions of the ERRORLOG file. A new ERRORLOG is created when
you restart SQL Server or run sp_cycle_errorlog. The file will be ERRORLOG.1
ERRORLOG.2 etc.. in the LOGS directory. The Displayed name in SSMS and EM
will have the date and time that the log goes up to. More information is in
Books online.
John
|||John,
Thanks a million. I guess the word archive was throwing me off. I was
looking in the wrong place. Somehow I thought it is archiving, so that I can
restore from it. But no info on that anywhere. Thank you very much for your
help.
-Bahman
"John Bell" wrote:

> HI
> "Bahman" wrote:
>
> These are old versions of the ERRORLOG file. A new ERRORLOG is created when
> you restart SQL Server or run sp_cycle_errorlog. The file will be ERRORLOG.1
> ERRORLOG.2 etc.. in the LOGS directory. The Displayed name in SSMS and EM
> will have the date and time that the log goes up to. More information is in
> Books online.
> John
>

Archiving Problem

Hi!
The SQL Server Express run several versions of an Archive file, such as
Archive #1, etc. under 'SQL Server Logs'.
What is generating these archives? Also, How can I see what has been
archived? Can I stop the server from doing this or needed for to operate?
Thank you very much!
-BahmanHI
"Bahman" wrote:

> Hi!
> The SQL Server Express run several versions of an Archive file, such as
> Archive #1, etc. under 'SQL Server Logs'.
> What is generating these archives? Also, How can I see what has been
> archived? Can I stop the server from doing this or needed for to operate?
> Thank you very much!
> -Bahman
>
These are old versions of the ERRORLOG file. A new ERRORLOG is created when
you restart SQL Server or run sp_cycle_errorlog. The file will be ERRORLOG.1
ERRORLOG.2 etc.. in the LOGS directory. The Displayed name in SSMS and EM
will have the date and time that the log goes up to. More information is in
Books online.
John|||John,
Thanks a million. I guess the word archive was throwing me off. I was
looking in the wrong place. Somehow I thought it is archiving, so that I can
restore from it. But no info on that anywhere. Thank you very much for your
help.
-Bahman
"John Bell" wrote:

> HI
> "Bahman" wrote:
>
> These are old versions of the ERRORLOG file. A new ERRORLOG is created whe
n
> you restart SQL Server or run sp_cycle_errorlog. The file will be ERRORLOG
.1
> ERRORLOG.2 etc.. in the LOGS directory. The Displayed name in SSMS and EM
> will have the date and time that the log goes up to. More information is i
n
> Books online.
> John
>

Archiving

After doing some archiving I have found that rather than freeing up the
space straight after the archive run, the database is freeing up space
slowly over a number of days. Can anybody explain this? I am presuming that
there is some background process cleaning up and freeing the space.
Gav> After doing some archiving I have found that rather than freeing up the
> space straight after the archive run, the database is freeing up space
> slowly over a number of days. Can anybody explain this? I am presuming
that
> there is some background process cleaning up and freeing the space.
Are you talking abou SQL Server and Backup command? Bakup is not freeing the
space. Take a look at the DBCC SHRINKDATABASE and DBCC SHRINKFILE commands
in Books OnLine if this is what you need.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.|||No we have a SQL SAP database. At the weekend we archived some SAP data (at
SAP level), the data has been deleted from the database but the free space
has not gone up in one go, it seems to free up a bit at a time each day. I
am not shrinking anything.
example. We delete 2Gb of data from the database. After deleteing the data
it appears as if only 100 MB has been deleted. But each day a further 300MB
free space appears until 2Gb worth of free space has appeared.
Note: Not actual figures just a explanation of what happens.
Gav
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OdLzKK4rDHA.1784@.TK2MSFTNGP09.phx.gbl...
> > After doing some archiving I have found that rather than freeing up the
> > space straight after the archive run, the database is freeing up space
> > slowly over a number of days. Can anybody explain this? I am presuming
> that
> > there is some background process cleaning up and freeing the space.
> Are you talking abou SQL Server and Backup command? Bakup is not freeing
the
> space. Take a look at the DBCC SHRINKDATABASE and DBCC SHRINKFILE commands
> in Books OnLine if this is what you need.
> --
> Dejan Sarka, SQL Server MVP
> Please reply only to the newsgroups.
>|||So you aren't talking about file sizes, but usage inside the files? If so, did you run DBCC
UPDATEUSAGE? Also, could possibly be a fragmentation issue.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Gav" <gavin.metcalfe@.portakabinnospam.com> wrote in message
news:bpirmq$a5q$1@.hercules.btinternet.com...
> No we have a SQL SAP database. At the weekend we archived some SAP data (at
> SAP level), the data has been deleted from the database but the free space
> has not gone up in one go, it seems to free up a bit at a time each day. I
> am not shrinking anything.
> example. We delete 2Gb of data from the database. After deleteing the data
> it appears as if only 100 MB has been deleted. But each day a further 300MB
> free space appears until 2Gb worth of free space has appeared.
> Note: Not actual figures just a explanation of what happens.
> Gav
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:OdLzKK4rDHA.1784@.TK2MSFTNGP09.phx.gbl...
> > > After doing some archiving I have found that rather than freeing up the
> > > space straight after the archive run, the database is freeing up space
> > > slowly over a number of days. Can anybody explain this? I am presuming
> > that
> > > there is some background process cleaning up and freeing the space.
> >
> > Are you talking abou SQL Server and Backup command? Bakup is not freeing
> the
> > space. Take a look at the DBCC SHRINKDATABASE and DBCC SHRINKFILE commands
> > in Books OnLine if this is what you need.
> >
> > --
> > Dejan Sarka, SQL Server MVP
> > Please reply only to the newsgroups.
> >
> >
>|||No I havn't. But since your post I have tested this on a non-production
server and it seems to do the trick. Thanks for your advice. :o)
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OiUJYq6rDHA.1196@.TK2MSFTNGP12.phx.gbl...
> So you aren't talking about file sizes, but usage inside the files? If so,
did you run DBCC
> UPDATEUSAGE? Also, could possibly be a fragmentation issue.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Gav" <gavin.metcalfe@.portakabinnospam.com> wrote in message
> news:bpirmq$a5q$1@.hercules.btinternet.com...
> > No we have a SQL SAP database. At the weekend we archived some SAP data
(at
> > SAP level), the data has been deleted from the database but the free
space
> > has not gone up in one go, it seems to free up a bit at a time each day.
I
> > am not shrinking anything.
> >
> > example. We delete 2Gb of data from the database. After deleteing the
data
> > it appears as if only 100 MB has been deleted. But each day a further
300MB
> > free space appears until 2Gb worth of free space has appeared.
> >
> > Note: Not actual figures just a explanation of what happens.
> >
> > Gav
> >
> > "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote
in
> > message news:OdLzKK4rDHA.1784@.TK2MSFTNGP09.phx.gbl...
> > > > After doing some archiving I have found that rather than freeing up
the
> > > > space straight after the archive run, the database is freeing up
space
> > > > slowly over a number of days. Can anybody explain this? I am
presuming
> > > that
> > > > there is some background process cleaning up and freeing the space.
> > >
> > > Are you talking abou SQL Server and Backup command? Bakup is not
freeing
> > the
> > > space. Take a look at the DBCC SHRINKDATABASE and DBCC SHRINKFILE
commands
> > > in Books OnLine if this is what you need.
> > >
> > > --
> > > Dejan Sarka, SQL Server MVP
> > > Please reply only to the newsgroups.
> > >
> > >
> >
> >
>

Applications without install SQL Server

Hello all
I write a program that use sqlclient to connect to SQL Server 2005 Database
so how can i run this program on a computer that SQL Server 2005 is not
installed on it
cause it's too much for the customers to install SQL Server 2005 express
edition it takes too much time and space
i think if i can take the library files and make a virtual registery on the
client PC it must works
but i don't know how to make it
please help me as soon as possible
and thanks
Use the native client redistributable and redistribute it with your
software. Download it here:
http://www.microsoft.com/downloads/d...displaylang=en
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
"Abood_aa" <u16990@.uwe> wrote in message news:5970150f4c114@.uwe...
> Hello all
> I write a program that use sqlclient to connect to SQL Server 2005
> Database
> so how can i run this program on a computer that SQL Server 2005 is not
> installed on it
> cause it's too much for the customers to install SQL Server 2005 express
> edition it takes too much time and space
> i think if i can take the library files and make a virtual registery on
> the
> client PC it must works
> but i don't know how to make it
> please help me as soon as possible
> and thanks

Application to SQL db

I'm not sure if this is the right section for the question, but here goes...

I have an application that needs to be run in network client mode. This app is supposed to connect to a SQL 2005 db hosted on a server. As I procede through the installation process I get a prompt when i populate the server\db information.

'The listener port was responsice, but did not return a primary port number for <servername>. Please verify that <servername> is a valid and running database before proceeding.

A little background is in order:

Host:

Server 2003 Standard

SQL Server 2005

Installed instance of said db

Client:

Windows XP Pro sp2

Network config:

We have a domain with about 125 workstations\laptops. Windows firewall is turned off using a group policy (makes it easier to push apps out to clients). For giggles I made exceptions to the 'turned off' firewall policies for the needed ports etc. This was done on both the host server and the client.

Thank you in advance.

Hi,

Is your SQL Server 2005 installed as a named instance? What needs to happen in order to connect is:

(a) The client needs to specify the server as servername\instancename.

(b) The client will try to "resolve" the instance name to an appropriate tcp port (or pipe name) via the SQLBrowser (SQL2005) / Listener thread (SQL 2000) functionality before opening an appropriate connection.

If SQL is installed as a "default" instance, the port is assumed to be 1433 and the resolution may be skipped.

The error message above seems to be application-specific. Is there a chance that the application setup is trying to do some "in-house" resolution by attempting to send/broadcast a packet to UDP port 1434 of the server? That is not supported and may cause problems. The message implies that it was able to talk to UDP port 1434 (Listener/SQLBrowser) and probably even got the list of instances, but did not find the desired instance in the list?

What you should try is a test connection within ODBC Administrator or a .UDL DataLink file to the server first. If this succeeds, contact the application support to find out what is missing in the connectivity on their end. If this fails, please post the exact error here.

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hello,

The database that we are working with is indeed a named instance. The application that we are using to connect to the DB doesn't want to connect. The prompts that we recieve seem to hint that it does indeed see the database, but it gets kicked out. We have done every trick in the book in hopes that we could get this resolved. It is my belief at this point that the application is the issue and not the SQL db or said connectors.

Thank you for the response and have a wonderful day.

Application to SQL db

I'm not sure if this is the right section for the question, but here goes...

I have an application that needs to be run in network client mode. This app is supposed to connect to a SQL 2005 db hosted on a server. As I procede through the installation process I get a prompt when i populate the server\db information.

'The listener port was responsice, but did not return a primary port number for <servername>. Please verify that <servername> is a valid and running database before proceeding.

A little background is in order:

Host:

Server 2003 Standard

SQL Server 2005

Installed instance of said db

Client:

Windows XP Pro sp2

Network config:

We have a domain with about 125 workstations\laptops. Windows firewall is turned off using a group policy (makes it easier to push apps out to clients). For giggles I made exceptions to the 'turned off' firewall policies for the needed ports etc. This was done on both the host server and the client.

Thank you in advance.

Hi,

Is your SQL Server 2005 installed as a named instance? What needs to happen in order to connect is:

(a) The client needs to specify the server as servername\instancename.

(b) The client will try to "resolve" the instance name to an appropriate tcp port (or pipe name) via the SQLBrowser (SQL2005) / Listener thread (SQL 2000) functionality before opening an appropriate connection.

If SQL is installed as a "default" instance, the port is assumed to be 1433 and the resolution may be skipped.

The error message above seems to be application-specific. Is there a chance that the application setup is trying to do some "in-house" resolution by attempting to send/broadcast a packet to UDP port 1434 of the server? That is not supported and may cause problems. The message implies that it was able to talk to UDP port 1434 (Listener/SQLBrowser) and probably even got the list of instances, but did not find the desired instance in the list?

What you should try is a test connection within ODBC Administrator or a .UDL DataLink file to the server first. If this succeeds, contact the application support to find out what is missing in the connectivity on their end. If this fails, please post the exact error here.

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hello,

The database that we are working with is indeed a named instance. The application that we are using to connect to the DB doesn't want to connect. The prompts that we recieve seem to hint that it does indeed see the database, but it gets kicked out. We have done every trick in the book in hopes that we could get this resolved. It is my belief at this point that the application is the issue and not the SQL db or said connectors.

Thank you for the response and have a wonderful day.

Application SQL Server installing problems

Hello Folks,
Managed to loose my SQL Server while uninstalling application and now the
server doesn't get installed while trying to run the application installer
again. Is there any way to locate traces of the old installation anyhow...
or what could be the case.
Is it possible to trace down for example C:\Program Files\Microsoft SQL
Server\80\Tools\Binn\sqlmangr.exe for example if the registry settings have
somehow been vanished?
After the uninstallation and regcleaning traces of SQL were hanging still
for example in the XP Home Services prompt.
Thanks
http://support.microsoft.com/default...b;en-us;290991
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kaide" <kai.porvali@.kotiportti.fi> wrote in message news:OVDn0mbqFHA.4072@.TK2MSFTNGP09.phx.gbl...
> Hello Folks,
> Managed to loose my SQL Server while uninstalling application and now the
> server doesn't get installed while trying to run the application installer
> again. Is there any way to locate traces of the old installation anyhow...
> or what could be the case.
> Is it possible to trace down for example C:\Program Files\Microsoft SQL
> Server\80\Tools\Binn\sqlmangr.exe for example if the registry settings have
> somehow been vanished?
> After the uninstallation and regcleaning traces of SQL were hanging still
> for example in the XP Home Services prompt.
> Thanks
>
>
>
|||Thanks for your reply!
While editing values used, by Murphy, Regedit in the first faze. Is there
anything that could be done anymore or do I have to reinstall XP again.
Forgot to tell you that the first problems started after running repair
installation of XP. SQL just quit responding.
Best Regards!
Kaide
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> kirjoitti
viestiss:O4ZiyrgqFHA.2968@.TK2MSFTNGP10.phx.gbl... [vbcol=seagreen]
> http://support.microsoft.com/default...b;en-us;290991
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Kaide" <kai.porvali@.kotiportti.fi> wrote in message
> news:OVDn0mbqFHA.4072@.TK2MSFTNGP09.phx.gbl...
|||Not sure what you are saying. Do you want to remove all traces of SQL Server so you can install SQL
Server again? If so, the link I posted should help you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kaide" <kai.porvali@.kotiportti.fi> wrote in message news:OBkdQ79qFHA.2244@.tk2msftngp13.phx.gbl...
> Thanks for your reply!
> While editing values used, by Murphy, Regedit in the first faze. Is there anything that could be
> done anymore or do I have to reinstall XP again. Forgot to tell you that the first problems
> started after running repair installation of XP. SQL just quit responding.
> Best Regards!
> Kaide
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> kirjoitti
> viestiss:O4ZiyrgqFHA.2968@.TK2MSFTNGP10.phx.gbl...
>
|||Yes Tibor, and Thank You!
"Haste takes pleasure out of everything"! like they say. Respectivly, I
posted my question without checking all details...
Got server running after removing all known instances of the SQL and
reinstalling the application. I'm happy with the result that has bothered me
a lot during the last weeks.
Thanks' for your help once more! Even the software supplier couldn't help
with this issue even they got all info needed. (And this "Pineapple" company
should have a good customer support)!
Best Regards
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> kirjoitti
viestiss:uWBRKM%23qFHA.4044@.TK2MSFTNGP09.phx.gbl. ..
> Not sure what you are saying. Do you want to remove all traces of SQL
> Server so you can install SQL Server again? If so, the link I posted
> should help you.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Kaide" <kai.porvali@.kotiportti.fi> wrote in message
> news:OBkdQ79qFHA.2244@.tk2msftngp13.phx.gbl...
>
|||Top paraphrase another saying: "KB is your friend". :-)
Glad you sorted it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kaide" <kai.porvali@.kotiportti.fi> wrote in message news:OEHCnT$qFHA.3096@.TK2MSFTNGP15.phx.gbl...
> Yes Tibor, and Thank You!
> "Haste takes pleasure out of everything"! like they say. Respectivly, I posted my question without
> checking all details...
> Got server running after removing all known instances of the SQL and reinstalling the application.
> I'm happy with the result that has bothered me a lot during the last weeks.
> Thanks' for your help once more! Even the software supplier couldn't help with this issue even
> they got all info needed. (And this "Pineapple" company should have a good customer support)!
> Best Regards
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> kirjoitti
> viestiss:uWBRKM%23qFHA.4044@.TK2MSFTNGP09.phx.gbl. ..
>
|||Hello once more,
Hmm... KB is short for what... lacking a one letter propably?
Kaide
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> kirjoitti
viestiss:%23%23ahJ%23$qFHA.240@.tk2msftngp13.phx.g bl...
> Top paraphrase another saying: "KB is your friend". :-)
> Glad you sorted it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Kaide" <kai.porvali@.kotiportti.fi> wrote in message
> news:OEHCnT$qFHA.3096@.TK2MSFTNGP15.phx.gbl...
>
|||KB = KnowledgeBase :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kaide" <kai.porvali@.kotiportti.fi> wrote in message news:%23$TUGRCrFHA.3736@.TK2MSFTNGP10.phx.gbl...
> Hello once more,
> Hmm... KB is short for what... lacking a one letter propably?
> Kaide
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> kirjoitti
> viestiss:%23%23ahJ%23$qFHA.240@.tk2msftngp13.phx.g bl...
>

Application Roles with IIS

I wish to use SQL Server 2000 Application Security with an ASP (Classic) app
lication.
When I run the ASP app and look at SQL Server Current Activity Process Info
, the column Application is showing "Internet Information Services". How do
I align SQL Server and IIS so that Application Security can be utilised?
regards
Greg
PS I have already set the Application Name for this site in IIS.If you are using ASP to connect to SQL Server then the application is IIS
so that is what is diplayed. What application would you prefer it to
display?
Rand
This posting is provided "as is" with no warranties and confers no rights.

application roles and strong passwords

Hi,
we are testing upgrading an application to run SQL 2005. One issue we
are having is that the one application role they have has a 'weak'
password. We are trying to change the password and it seem to be
inforcing the 'strong password policy from windows. I cannot see a way
around this. Do all application roles need strong passwords? Is there
any way around it?Yes...they should all be strong passwords. Using strong
passwords would be the first option.
If for some reason you can't and need time until you can use
strong passwords, One way around it is to use the old stored
procedures which are only there for backwards compatibility.
sp_addapprole, sp_approlepassword, etc.
-Sue
On 16 Apr 2007 18:15:02 -0700, rjvanzanten
<rvanzant@.premierbankcard.com> wrote:

>Hi,
>we are testing upgrading an application to run SQL 2005. One issue we
>are having is that the one application role they have has a 'weak'
>password. We are trying to change the password and it seem to be
>inforcing the 'strong password policy from windows. I cannot see a way
>around this. Do all application roles need strong passwords? Is there
>any way around it?|||Thanks Sue, I know that a better password would be preferred. But its
a difficult option for us.
Thanks for the tip on the sp_addapprole. It should get us through
this.
Ron
On Apr 16, 11:26 pm, Sue Hoegemeier <S...@.nomail.please> wrote:
> Yes...they should all be strong passwords. Using strong
> passwords would be the first option.
> If for some reason you can't and need time until you can use
> strong passwords, One way around it is to use the old stored
> procedures which are only there for backwards compatibility.
> sp_addapprole, sp_approlepassword, etc.
> -Sue
> On 16 Apr 2007 18:15:02 -0700, rjvanzanten
>
> <rvanz...@.premierbankcard.com> wrote:
> - Show quoted text -|||Sue Hoegemeier (Sue_H@.nomail.please) writes:
> Yes...they should all be strong passwords. Using strong
> passwords would be the first option.
> If for some reason you can't and need time until you can use
> strong passwords, One way around it is to use the old stored
> procedures which are only there for backwards compatibility.
> sp_addapprole, sp_approlepassword, etc.
sp_addrole uses CREATE APPLICATION ROLE, so that wouldn't be any different.
I am not able to run sp_helptext on sp_approlepassword, but I would not
expect it be possible to use password that does not pass the rules.
The only way out would be to modify the Windows policy as the password
for the application role is changed, and then change back.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Yeah...I see that in the stored proc but I'm pretty sure the
password check is bypassed - thought it was on one of the
blogs but only found this reference:
http://forums.microsoft.com/MSDN/Sh...576174&SiteID=1
I'm not on a box where I can test it, not until tomorrow.
-Sue
On Tue, 17 Apr 2007 22:10:41 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

>Sue Hoegemeier (Sue_H@.nomail.please) writes:
>sp_addrole uses CREATE APPLICATION ROLE, so that wouldn't be any different.
>I am not able to run sp_helptext on sp_approlepassword, but I would not
>expect it be possible to use password that does not pass the rules.
>The only way out would be to modify the Windows policy as the password
>for the application role is changed, and then change back.|||Sue Hoegemeier (Sue_H@.nomail.please) writes:
> Yeah...I see that in the stored proc but I'm pretty sure the
> password check is bypassed - thought it was on one of the
> blogs but only found this reference:
> http://forums.microsoft.com/MSDN/Sh...576174&SiteID=1
> I'm not on a box where I can test it, not until tomorrow.
Indeed. I tested it at a server at work, and the password check did
not catch my lousy password. Hm, I wonder how they do that...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Yeah...I thought I remember testing while back when I first
read it - I still can't find where I originally read it. I
had not looked at the stored proc though - which does make
it look odd that it works.
-Sue
On Thu, 19 Apr 2007 22:15:18 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

>Sue Hoegemeier (Sue_H@.nomail.please) writes:
>Indeed. I tested it at a server at work, and the password check did
>not catch my lousy password. Hm, I wonder how they do that...

Application Roles across databases in SQL Server 2000

Hello
I have 2 databases that run application role security
(different role names and passwords), users access these
databases only from within different Visual Basic
applications.
I require to be able to request data from both
databases. I have read in SQL Server help that if you
enable the guest user account and then give it the
relevant permissions the system will only allow the other
database to get to these objects.
I have created a stored procedure on one of the databases
that calls a table in the database with the guest account
enabled. I have not given the guest account access to
this table but I can still get to the data in the table.
Please can someone explain why this is and what I need to
do to prevent this.
Thank you
Caroline> I have created a stored procedure on one of the databases
> that calls a table in the database with the guest account
> enabled. I have not given the guest account access to
> this table but I can still get to the data in the table.
> Please can someone explain why this is and what I need to
> do to prevent this.
This is due to ownership chaining behavior. As long as all objects are
owned by the same login, permissions are not checked on indirectly
referenced objects. Additionally, you need to enable cross database
chaining for ownership chains to apply to cross-database access. This
appears to be the case in your environment.
As long as you access data only via views and procedures, you don't need to
grant any permissions to guest. This allows you to leverage ownership
chains as a security mechanism. See Ownership Chains in the Books Online
for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Caroline" <anonymous@.discussions.microsoft.com> wrote in message
news:2512601c46019$5e372e20$a501280a@.phx
.gbl...
> Hello
> I have 2 databases that run application role security
> (different role names and passwords), users access these
> databases only from within different Visual Basic
> applications.
> I require to be able to request data from both
> databases. I have read in SQL Server help that if you
> enable the guest user account and then give it the
> relevant permissions the system will only allow the other
> database to get to these objects.
> I have created a stored procedure on one of the databases
> that calls a table in the database with the guest account
> enabled. I have not given the guest account access to
> this table but I can still get to the data in the table.
> Please can someone explain why this is and what I need to
> do to prevent this.
> Thank you
> Caroline|||> and what I need to do to prevent this.
Only grant execute permissions on the procedure to those users/roles whom
you want to access the underlying data.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23fcOSPDYEHA.3596@.tk2msftngp13.phx.gbl...
> This is due to ownership chaining behavior. As long as all objects are
> owned by the same login, permissions are not checked on indirectly
> referenced objects. Additionally, you need to enable cross database
> chaining for ownership chains to apply to cross-database access. This
> appears to be the case in your environment.
> As long as you access data only via views and procedures, you don't need
to
> grant any permissions to guest. This allows you to leverage ownership
> chains as a security mechanism. See Ownership Chains in the Books Online
> for more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Caroline" <anonymous@.discussions.microsoft.com> wrote in message
> news:2512601c46019$5e372e20$a501280a@.phx
.gbl...
>

application process don't continue after run trigger

Hi,
There is an application about cashier application process. I'd
wrote a trigger for send information messages to users. Send an e-mail
message when TransactType equal to db. But there is a problem, e-mail
message is sending but other application process is not continue and
record is not write to MSSQL database when TransactType equal to db.
Have any suggestion? Why is not application process continue after run
the Trigger proccess.
MSSQL version 7.0
Thanks.
CREATE TRIGGER sendmail_trigger ON [Transact]
FOR INSERT
AS
DECLARE
@.ptype varchar (100),
@.psubject varchar (100),
@.pacctid varchar (100),
@.ptransactamount varchar (100),
@.premail varchar (100),
@.pmessage varchar (900),
@.pname varchar (100)
set @.ptype = (select TransactType from Inserted)
if (@.ptype = 'db')
begin
set @.pacctid = (select AcctID from Inserted)
set @.ptransactamount = (select TransactAmount/-50 from Inserted)
set @.premail = (SELECT EmailAddress FROM Users WHERE AcctID=@.pacctid)
set @.pname = (SELECT UserName FROM Users WHERE AcctID=@.pacctid)
set @.psubject = 'Information Message'
set @.pmessage =
'Dear '+ @.pname+',
Debit '+ @.ptransactamount + ' your account.
For your information.'
EXEC master..xp_sendmail @.recipients = @.premail,
@.blind_copy_recipients = 'xxx@.yyy.zzz',
@.subject = @.psubject, @.message = @.pmessage
end
It is possible that you are getting an error, which may cause a scope
abort... Scope aborts do NOT return control back to the calling Sp etc, they
merely exit...
Comment out lines of code one at a time and see if you can find the line
which is hurting you... I suspect the email send may be causing the
problem...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"U A" <umuta@.sabanciuniv.edu> wrote in message
news:u1YkANWSEHA.2408@.tk2msftngp13.phx.gbl...
> Hi,
> There is an application about cashier application process. I'd
> wrote a trigger for send information messages to users. Send an e-mail
> message when TransactType equal to db. But there is a problem, e-mail
> message is sending but other application process is not continue and
> record is not write to MSSQL database when TransactType equal to db.
> Have any suggestion? Why is not application process continue after run
> the Trigger proccess.
> MSSQL version 7.0
> Thanks.
> --
> CREATE TRIGGER sendmail_trigger ON [Transact]
> FOR INSERT
> AS
> DECLARE
> @.ptype varchar (100),
> @.psubject varchar (100),
> @.pacctid varchar (100),
> @.ptransactamount varchar (100),
> @.premail varchar (100),
> @.pmessage varchar (900),
> @.pname varchar (100)
> set @.ptype = (select TransactType from Inserted)
> if (@.ptype = 'db')
> begin
> set @.pacctid = (select AcctID from Inserted)
> set @.ptransactamount = (select TransactAmount/-50 from Inserted)
> set @.premail = (SELECT EmailAddress FROM Users WHERE AcctID=@.pacctid)
> set @.pname = (SELECT UserName FROM Users WHERE AcctID=@.pacctid)
> set @.psubject = 'Information Message'
> set @.pmessage =
> 'Dear '+ @.pname+',
> Debit '+ @.ptransactamount + ' your account.
> For your information.'
> EXEC master..xp_sendmail @.recipients = @.premail,
> @.blind_copy_recipients = 'xxx@.yyy.zzz',
> @.subject = @.psubject, @.message = @.pmessage
> end
> --
>
|||Many thanks. I had comment out line of EXEC master..xp_sendmail. Process
successfuly finished. But e-mail didn't send. What can I do for send
e-mail automatically this trigger method?
thanks.
On 03-06-2004 15:30, Wayne Snyder wrote:
> It is possible that you are getting an error, which may cause a scope
> abort... Scope aborts do NOT return control back to the calling Sp etc, they
> merely exit...
> Comment out lines of code one at a time and see if you can find the line
> which is hurting you... I suspect the email send may be causing the
> problem...
>
|||I suggest you don't send email from the trigger. Have the trigger to insert necessary information into a table
and create a job which runs every x minutes that reads off of this table and does the email sending.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"U A" <umuta@.sabanciuniv.edu> wrote in message news:e144dYXSEHA.3872@.TK2MSFTNGP10.phx.gbl...
> Many thanks. I had comment out line of EXEC master..xp_sendmail. Process
> successfuly finished. But e-mail didn't send. What can I do for send
> e-mail automatically this trigger method?
> thanks.
>
> On 03-06-2004 15:30, Wayne Snyder wrote:
>
|||Thanks your suggestion but it gave same error. I had insert the records
to a new table and send e-mail with a remote perl script. I think,
trigger occasion exits from application.
thanks.
On 03-06-2004 18:55, Tibor Karaszi wrote:
> I suggest you don't send email from the trigger. Have the trigger to insert necessary
information into a table and create a job which runs every x minutes
that reads off of
this table and does the email sending.

application process don't continue after run trigger

Hi,
There is an application about cashier application process. I'd
wrote a trigger for send information messages to users. Send an e-mail
message when TransactType equal to db. But there is a problem, e-mail
message is sending but other application process is not continue and
record is not write to MSSQL database when TransactType equal to db.
Have any suggestion? Why is not application process continue after run
the Trigger proccess.
MSSQL version 7.0
Thanks.
--
CREATE TRIGGER sendmail_trigger ON [Transact]
FOR INSERT
AS
DECLARE
@.ptype varchar (100),
@.psubject varchar (100),
@.pacctid varchar (100),
@.ptransactamount varchar (100),
@.premail varchar (100),
@.pmessage varchar (900),
@.pname varchar (100)
set @.ptype = (select TransactType from Inserted)
if (@.ptype = 'db')
begin
set @.pacctid = (select AcctID from Inserted)
set @.ptransactamount = (select TransactAmount/-50 from Inserted)
set @.premail = (SELECT EmailAddress FROM Users WHERE AcctID=@.pacctid)
set @.pname = (SELECT UserName FROM Users WHERE AcctID=@.pacctid)
set @.psubject = 'Information Message'
set @.pmessage =
'Dear '+ @.pname+',
Debit '+ @.ptransactamount + ' your account.
For your information.'
EXEC master..xp_sendmail @.recipients = @.premail,
@.blind_copy_recipients = 'xxx@.yyy.zzz',
@.subject = @.psubject, @.message = @.pmessage
end
--It is possible that you are getting an error, which may cause a scope
abort... Scope aborts do NOT return control back to the calling Sp etc, they
merely exit...
Comment out lines of code one at a time and see if you can find the line
which is hurting you... I suspect the email send may be causing the
problem...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"U A" <umuta@.sabanciuniv.edu> wrote in message
news:u1YkANWSEHA.2408@.tk2msftngp13.phx.gbl...
> Hi,
> There is an application about cashier application process. I'd
> wrote a trigger for send information messages to users. Send an e-mail
> message when TransactType equal to db. But there is a problem, e-mail
> message is sending but other application process is not continue and
> record is not write to MSSQL database when TransactType equal to db.
> Have any suggestion? Why is not application process continue after run
> the Trigger proccess.
> MSSQL version 7.0
> Thanks.
> --
> CREATE TRIGGER sendmail_trigger ON [Transact]
> FOR INSERT
> AS
> DECLARE
> @.ptype varchar (100),
> @.psubject varchar (100),
> @.pacctid varchar (100),
> @.ptransactamount varchar (100),
> @.premail varchar (100),
> @.pmessage varchar (900),
> @.pname varchar (100)
> set @.ptype = (select TransactType from Inserted)
> if (@.ptype = 'db')
> begin
> set @.pacctid = (select AcctID from Inserted)
> set @.ptransactamount = (select TransactAmount/-50 from Inserted)
> set @.premail = (SELECT EmailAddress FROM Users WHERE AcctID=@.pacctid)
> set @.pname = (SELECT UserName FROM Users WHERE AcctID=@.pacctid)
> set @.psubject = 'Information Message'
> set @.pmessage =
> 'Dear '+ @.pname+',
> Debit '+ @.ptransactamount + ' your account.
> For your information.'
> EXEC master..xp_sendmail @.recipients = @.premail,
> @.blind_copy_recipients = 'xxx@.yyy.zzz',
> @.subject = @.psubject, @.message = @.pmessage
> end
> --
>|||Many thanks. I had comment out line of EXEC master..xp_sendmail. Process
successfuly finished. But e-mail didn't send. What can I do for send
e-mail automatically this trigger method?
thanks.
On 03-06-2004 15:30, Wayne Snyder wrote:
> It is possible that you are getting an error, which may cause a scope
> abort... Scope aborts do NOT return control back to the calling Sp etc, th
ey
> merely exit...
> Comment out lines of code one at a time and see if you can find the line
> which is hurting you... I suspect the email send may be causing the
> problem...
>|||I suggest you don't send email from the trigger. Have the trigger to insert
necessary information into a table
and create a job which runs every x minutes that reads off of this table and
does the email sending.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"U A" <umuta@.sabanciuniv.edu> wrote in message news:e144dYXSEHA.3872@.TK2MSFTNGP10.phx.gbl...

> Many thanks. I had comment out line of EXEC master..xp_sendmail. Process
> successfuly finished. But e-mail didn't send. What can I do for send
> e-mail automatically this trigger method?
> thanks.
>
> On 03-06-2004 15:30, Wayne Snyder wrote:
>|||Thanks your suggestion but it gave same error. I had insert the records
to a new table and send e-mail with a remote PERL script. I think,
trigger occasion exits from application.
thanks.
On 03-06-2004 18:55, Tibor Karaszi wrote:
> I suggest you don't send email from the trigger. Have the trigger to insert necess
ary
information into a table and create a job which runs every x minutes
that reads off of
this table and does the email sending.

application process don't continue after run trigger

Hi,
There is an application about cashier application process. I'd
wrote a trigger for send information messages to users. Send an e-mail
message when TransactType equal to db. But there is a problem, e-mail
message is sending but other application process is not continue and
record is not write to MSSQL database when TransactType equal to db.
Have any suggestion? Why is not application process continue after run
the Trigger proccess.
MSSQL version 7.0
Thanks.
--
CREATE TRIGGER sendmail_trigger ON [Transact]
FOR INSERT
AS
DECLARE
@.ptype varchar (100),
@.psubject varchar (100),
@.pacctid varchar (100),
@.ptransactamount varchar (100),
@.premail varchar (100),
@.pmessage varchar (900),
@.pname varchar (100)
set @.ptype = (select TransactType from Inserted)
if (@.ptype = 'db')
begin
set @.pacctid = (select AcctID from Inserted)
set @.ptransactamount = (select TransactAmount/-50 from Inserted)
set @.premail = (SELECT EmailAddress FROM Users WHERE AcctID=@.pacctid)
set @.pname = (SELECT UserName FROM Users WHERE AcctID=@.pacctid)
set @.psubject = 'Information Message'
set @.pmessage = 'Dear '+ @.pname+',
Debit '+ @.ptransactamount + ' your account.
For your information.'
EXEC master..xp_sendmail @.recipients = @.premail,
@.blind_copy_recipients = 'xxx@.yyy.zzz',
@.subject = @.psubject, @.message = @.pmessage
end
--It is possible that you are getting an error, which may cause a scope
abort... Scope aborts do NOT return control back to the calling Sp etc, they
merely exit...
Comment out lines of code one at a time and see if you can find the line
which is hurting you... I suspect the email send may be causing the
problem...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"U A" <umuta@.sabanciuniv.edu> wrote in message
news:u1YkANWSEHA.2408@.tk2msftngp13.phx.gbl...
> Hi,
> There is an application about cashier application process. I'd
> wrote a trigger for send information messages to users. Send an e-mail
> message when TransactType equal to db. But there is a problem, e-mail
> message is sending but other application process is not continue and
> record is not write to MSSQL database when TransactType equal to db.
> Have any suggestion? Why is not application process continue after run
> the Trigger proccess.
> MSSQL version 7.0
> Thanks.
> --
> CREATE TRIGGER sendmail_trigger ON [Transact]
> FOR INSERT
> AS
> DECLARE
> @.ptype varchar (100),
> @.psubject varchar (100),
> @.pacctid varchar (100),
> @.ptransactamount varchar (100),
> @.premail varchar (100),
> @.pmessage varchar (900),
> @.pname varchar (100)
> set @.ptype = (select TransactType from Inserted)
> if (@.ptype = 'db')
> begin
> set @.pacctid = (select AcctID from Inserted)
> set @.ptransactamount = (select TransactAmount/-50 from Inserted)
> set @.premail = (SELECT EmailAddress FROM Users WHERE AcctID=@.pacctid)
> set @.pname = (SELECT UserName FROM Users WHERE AcctID=@.pacctid)
> set @.psubject = 'Information Message'
> set @.pmessage => 'Dear '+ @.pname+',
> Debit '+ @.ptransactamount + ' your account.
> For your information.'
> EXEC master..xp_sendmail @.recipients = @.premail,
> @.blind_copy_recipients = 'xxx@.yyy.zzz',
> @.subject = @.psubject, @.message = @.pmessage
> end
> --
>|||Many thanks. I had comment out line of EXEC master..xp_sendmail. Process
successfuly finished. But e-mail didn't send. What can I do for send
e-mail automatically this trigger method?
thanks.
On 03-06-2004 15:30, Wayne Snyder wrote:
> It is possible that you are getting an error, which may cause a scope
> abort... Scope aborts do NOT return control back to the calling Sp etc, they
> merely exit...
> Comment out lines of code one at a time and see if you can find the line
> which is hurting you... I suspect the email send may be causing the
> problem...
>|||I suggest you don't send email from the trigger. Have the trigger to insert necessary information into a table
and create a job which runs every x minutes that reads off of this table and does the email sending.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"U A" <umuta@.sabanciuniv.edu> wrote in message news:e144dYXSEHA.3872@.TK2MSFTNGP10.phx.gbl...
> Many thanks. I had comment out line of EXEC master..xp_sendmail. Process
> successfuly finished. But e-mail didn't send. What can I do for send
> e-mail automatically this trigger method?
> thanks.
>
> On 03-06-2004 15:30, Wayne Snyder wrote:
> > It is possible that you are getting an error, which may cause a scope
> > abort... Scope aborts do NOT return control back to the calling Sp etc, they
> > merely exit...
> >
> > Comment out lines of code one at a time and see if you can find the line
> > which is hurting you... I suspect the email send may be causing the
> > problem...
> >
>|||Thanks your suggestion but it gave same error. I had insert the records
to a new table and send e-mail with a remote perl script. I think,
trigger occasion exits from application.
thanks.
On 03-06-2004 18:55, Tibor Karaszi wrote:
> I suggest you don't send email from the trigger. Have the trigger to insert necessary
information into a table and create a job which runs every x minutes
that reads off of
this table and does the email sending.

Application on Windows CE 3.0 and 4.2

Hello
We have small application writtten for Palmtop/Pocket PCs in Embedded VB
for Windows CE3.0
When trying to run the same on Windows CE4.2, "MS embedded Visual Basic
Runtime" occured.
Can someone guide us how we can tailor the application suitable for Windows
CE 4.2 and in future versions too..?
Regards
SathianWell, eVB is no longer supported and the run-time is not available on
Windows CE .NET 4.2-based devices, except for Pocket PC devices. You might
be able to get the same run-time that Microsoft provides as an after-market
install for PPC devices, but you'll be cobbling together pieces that have
not been tested or designed to work. I think that you can find the PPC
pieces by searching on www.microsoft.com/downloads. You might search the
archives of microsoft.public.windowsce.embedded.vb for information on other
attempts to get that (4.2 and eVB), to work. GoogleGroups archives all of
the newsgroup traffic: www.googlegroups.com.
Paul T.
"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:dgme5n$21k$1@.ns2.fe.internet.bosch.com...
> Hello
> We have small application writtten for Palmtop/Pocket PCs in Embedded VB
> for Windows CE3.0
> When trying to run the same on Windows CE4.2, "MS embedded Visual Basic
> Runtime" occured.
> Can someone guide us how we can tailor the application suitable for
> Windows
> CE 4.2 and in future versions too..?
> Regards
> Sathian
>
>

application name in profiler, could it be modifed

Hi,
We have 500+ dts running 24 X 7 ,started from dts run
In profiler every enty in application name column show as Dts
designer.
1.
Is possible to modify dts property or anything and add customer
informatiom ?
example
application name = dtsrun - Insert_Into_table_X
2.
is possible to modify application name for any connection ?
Thanks
AlexSure. For a SQL connection just bring up the properties and click on the
Advanced button and you'll see an Application Name property. You could for
example add an ActiveX script task to the start of your package that loops
through the SQL connections and sets the Application Name property of the
connection to the package name.
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
<t2581@.hotmail.com> wrote in message
news:1137770497.717723.119880@.g14g2000cwa.googlegroups.com...
> Hi,
>
> We have 500+ dts running 24 X 7 ,started from dts run
> In profiler every enty in application name column show as Dts
> designer.
>
> 1.
> Is possible to modify dts property or anything and add customer
> informatiom ?
> example
> application name = dtsrun - Insert_Into_table_X
> 2.
> is possible to modify application name for any connection ?
>
> Thanks
>
> Alex
>

Application cannot connect to SQL Server 2005.

I am having a strange problem. I have an application that to connects to SQL Database in SQL Server 2005.

When I run the application with Visual Studio everything works fine but when I deploy the application on my web server and then to access the site .

I get an error :Login failed for user ''. The user is not associated with a trusted SQL Server connection.

For some reason the application is not passing the user name . I have checked the IIS and authentication is set to Integrated Windows Authentication .

My connection string is correct as well

<add name="xxxxx" connectionString="Data Source=xxxxxx;Initial Catalog=xxxx;Integrated Security=SSPI" providerName="System.Data.SqlClient"/>

I will appreciate any kind of assistance in this matter. Thanks

check under which login your asp web page works ASPNET or Network service and create login on your SQL server for this account.

You also have to give access to your databases for this login.

If you run web page on IIS server user under which IIS server is running (or your web page specific user) requests access to SQL server.

|||

I check my sql logs and when I tried to make a connection using : UID and PWD in my connection string. The log says that

Login failed for user "<username"> [CLIENT:xxx.xxx.xxx.xxx]

below that I saw the the same user successfully logged using Management Studio saying

Login succeeded for user "<username>" [CLIENT:local machine]

I don't understand the difference. It is the same user connecting from the same local machine where sql is loaded.

Thanks

Application as service inside SQL possible?

I would like to run an application inside SQL CLR as a service so that once started it continues to run until explicitly shut down. The application would sit idle and only wake up in response to data coming in from data acquisition devices over serial ports (COM ports). Is this possible? I read somewhere that this would be supported, but I have not been able to find any information on how to do it. Any help or pointers would be greatly appreciated!Well, by default when a CLR Proc/Function has been called, it will stay up until the server goes down (hopefully never) or the appdomain is torn down. However, I do not think this is what you had in mind.
Not knowing how the data ends up in the database, I would suggest you take a look at SQL Server Service Broker, which allows you to have a "service program" monitoring a queue and when a message appears on the queue, the service program is automatically started (on a separate thread) and handles the message. The message can be anything, and the service program can be written in either T-SQL or .NET.
If you post some more about how the data is shipped to the database, maybe we can help some more.
Niels|||Hi Niels,

Thank you for your reply.

Just think of the database being the first entry point into the system for the data. there are some data acquisition boards that expose themselves as serial ports on the system and I would like to have all the code runing inside SQL sto make it as efficient as possible and to avoid any type of marshalling or serialization coming into or out of SQL, so I desire to have the code that opens the COM ports and waits for activity to run in SQL as well.

I do understand that the CLR will stay up once code has run in it, so what I want to do is have a service (application) running continually inside the SQL CLR without it being in a request/response mode where it is only executing in response to an external event, or message in a queue, but sits idle until a delegate notifies that there is activity in the COM ports.

Somehow, from what I have read, I don't think service broker is what I want but I will look further into it.

Any ideas?

Gus|||

Something like this would require your SQL CLR assembly to be registered as unsafe which is not recommended. Why not have an intermediate service that would monitor the activity on the ports and calls SQL CLR routines to do data processing as required.

Thanks,
-Vineet.

|||

Why would it need to be unsafe if it can be entirely implemented with the use of managed classes. As far as I know the SerialPort class is not marked unsafe. Am I missing something here? In any event, the reason to do it whithin SQL would be for performance and to avoid the overhead marshalling in and out of the SQL CLR.

Still hoping for an answer

|||

I guess you can use a CLR object as a service for SAFE assemblies.
(it would be great to get a comment from Microsoft developers, especially concerning multithreading problems)

The easiest way is to create a class that will represent a service and create a static readonly field that will store the instance of a service (SQL Server assemblies have a limitation: they may contain only readonly static fields).

public class MyService
{
private static readonly MyService _instance = new MyService();

public static Instance
{
get { return _instance; }
}
}

Using this code you should be aware that the service would be started on the first invoke and will remain in memory until SQL Server shut down (restart).

Make things a bit more complicated, we can make the service to shut down after a specified amount of time of user inactivity.
First of all, we need a "static" field that we can assign to null. As we cannot use not-readonly static fields, the workaround is to create a helper class to store that field.

public class MyService
{
private class Storage
{
private MyService _instance;

public MyService Instance
{
get { return _instance; }
set { _instance = value; }
}
}

private static readonly Storage _storage = new Storage();

public static Instance
{
get
{
if (_storage.Instance == null)
_storage.Instance = new MyService();

return _storage.Instance;
}
}
}

Now we have editable field that we can assign to null when service is not needed. I'm not sure if this workaround is what Microsoft was intended to allow developers to do (because otherwise I cannot understand the reason of limiting static fields to readonly)... but it works.
Then we have to add DateTime timestamp to our class to trace user activity (don't forget to refresh it in your CLR code whenever the user's action should be treated as "activity").

public class MyService
{
private DateTime _timestamp;

public MyService()
{
RefreshTimestamp();
}

public void RefreshTimestamp()
{
_timestamp = DateTime.Now;
}
....
}

And the last thing we have to do is to unload service after specified amount of time of user inactivity. We cannot use another thread to do it, but we have Service Broker , another workaround that allows us to create a new thread. We have to create a chain of SQL objects (Message Type/Contract/Service/Queue) and a stored procedure that will handle that queue. All it does is waits for xx seconds for a message in a loop, on timeout checks the _timestamp field, and if it differs from DateTime.Now more than xx seconds - unloads the service and exits.
And the last thing is to activate this "thread". We can do it in a MyService's constructor by sending an empty message to queue.

P.S. I didn't touched the multithreading problem (as I foresee) that may occur in a service when several users access same resources. This problem actually even bigger if we take into account that SAFE assemblies cannot use standard locking mechanism. I haven't investigated this problem deeply, but for now the only way I see is to create semaphore class ourselves using atomic operations from System.Threading.Interlocked and control access to all resources with corresponding semaphores.

|||I'm not sure it's possible, so I just ask

Is it possible to "inform" a Windows application or a Windows service when a message on the SQL Service Broker is coming up?

Could U post any example?

Thanks

Application as service inside SQL possible?

I would like to run an application inside SQL CLR as a service so that once started it continues to run until explicitly shut down. The application would sit idle and only wake up in response to data coming in from data acquisition devices over serial ports (COM ports). Is this possible? I read somewhere that this would be supported, but I have not been able to find any information on how to do it. Any help or pointers would be greatly appreciated!Well, by default when a CLR Proc/Function has been called, it will stay up until the server goes down (hopefully never) or the appdomain is torn down. However, I do not think this is what you had in mind.
Not knowing how the data ends up in the database, I would suggest you take a look at SQL Server Service Broker, which allows you to have a "service program" monitoring a queue and when a message appears on the queue, the service program is automatically started (on a separate thread) and handles the message. The message can be anything, and the service program can be written in either T-SQL or .NET.
If you post some more about how the data is shipped to the database, maybe we can help some more.
Niels
|||Hi Niels,

Thank you for your reply.

Just think of the database being the first entry point into the system for the data. there are some data acquisition boards that expose themselves as serial ports on the system and I would like to have all the code runing inside SQL sto make it as efficient as possible and to avoid any type of marshalling or serialization coming into or out of SQL, so I desire to have the code that opens the COM ports and waits for activity to run in SQL as well.

I do understand that the CLR will stay up once code has run in it, so what I want to do is have a service (application) running continually inside the SQL CLR without it being in a request/response mode where it is only executing in response to an external event, or message in a queue, but sits idle until a delegate notifies that there is activity in the COM ports.

Somehow, from what I have read, I don't think service broker is what I want but I will look further into it.

Any ideas?

Gus|||

Something like this would require your SQL CLR assembly to be registered as unsafe which is not recommended. Why not have an intermediate service that would monitor the activity on the ports and calls SQL CLR routines to do data processing as required.

Thanks,
-Vineet.

|||

Why would it need to be unsafe if it can be entirely implemented with the use of managed classes. As far as I know the SerialPort class is not marked unsafe. Am I missing something here? In any event, the reason to do it whithin SQL would be for performance and to avoid the overhead marshalling in and out of the SQL CLR.

Still hoping for an answer

|||

I guess you can use a CLR object as a service for SAFE assemblies.
(it would be great to get a comment from Microsoft developers, especially concerning multithreading problems)

The easiest way is to create a class that will represent a service and create a static readonly field that will store the instance of a service (SQL Server assemblies have a limitation: they may contain only readonly static fields).

public class MyService
{
private static readonly MyService _instance = new MyService();

public static Instance
{
get { return _instance; }
}
}

Using this code you should be aware that the service would be started on the first invoke and will remain in memory until SQL Server shut down (restart).

Make things a bit more complicated, we can make the service to shut down after a specified amount of time of user inactivity.
First of all, we need a "static" field that we can assign to null. As we cannot use not-readonly static fields, the workaround is to create a helper class to store that field.

public class MyService
{
private class Storage
{
private MyService _instance;

public MyService Instance
{
get { return _instance; }
set { _instance = value; }
}
}

private static readonly Storage _storage = new Storage();

public static Instance
{
get
{
if (_storage.Instance == null)
_storage.Instance = new MyService();

return _storage.Instance;
}
}
}

Now we have editable field that we can assign to null when service is not needed. I'm not sure if this workaround is what Microsoft was intended to allow developers to do (because otherwise I cannot understand the reason of limiting static fields to readonly)... but it works.
Then we have to add DateTime timestamp to our class to trace user activity (don't forget to refresh it in your CLR code whenever the user's action should be treated as "activity").

public class MyService
{
private DateTime _timestamp;

public MyService()
{
RefreshTimestamp();
}

public void RefreshTimestamp()
{
_timestamp = DateTime.Now;
}
....
}

And the last thing we have to do is to unload service after specified amount of time of user inactivity. We cannot use another thread to do it, but we have Service Broker , another workaround that allows us to create a new thread. We have to create a chain of SQL objects (Message Type/Contract/Service/Queue) and a stored procedure that will handle that queue. All it does is waits for xx seconds for a message in a loop, on timeout checks the _timestamp field, and if it differs from DateTime.Now more than xx seconds - unloads the service and exits.
And the last thing is to activate this "thread". We can do it in a MyService's constructor by sending an empty message to queue.

P.S. I didn't touched the multithreading problem (as I foresee) that may occur in a service when several users access same resources. This problem actually even bigger if we take into account that SAFE assemblies cannot use standard locking mechanism. I haven't investigated this problem deeply, but for now the only way I see is to create semaphore class ourselves using atomic operations from System.Threading.Interlocked and control access to all resources with corresponding semaphores.

|||I'm not sure it's possible, so I just ask

Is it possible to "inform" a Windows application or a Windows service when a message on the SQL Service Broker is coming up?

Could U post any example?

Thanks

Appending to a text file via DTS

DTS wizard is not allowing me to append the data to a text file. Every time I run DTS and choose the destination to be this text file (say A.txt), it overwrites the data. I have a table whose data I am dumping to a text file. I truncate the table, then get the data again into it and want to append it to the same text file. But I end up overwriting the text file with the new data.

Kindly let me know where I am going wrong.I am having the same issue as well... I thought I remember seeing an option checkbox somewhere to do that... but I cant seem to find it.
I doubt we are the only ones having to deal with this, so can someone please chime in with an answer or maybe a possible direction for us to follow? Thanks in advance... :)