sql permissions: installed

Showing posts with label installed. Show all posts

Thursday, March 29, 2012

Default language and dates

I've just moved servers - WK3 to WK3 - installed SQLserver 2005 and uploaded the database. Code base has not changed but now I'm getting

"String was not recognized as a valid DateTime.Couldn't store <21/07/2006>" . I suspect its to do with the default language , but although the default is English (United States) as in my last server, I have set the language in Advanced settings to be British English. I can't see any difference between the settings and my last server. I'm British BTW .

I also ran

EXEC @.ret=sp_defaultlanguage'sa','British English'

as the only login is sa . WK3 is itself set to English (United Kingdom) - I thought SQL 2005 would inherit this setting? Any help would be much appreciated.

ashaig:
"String was not recognized as a valid DateTime.Couldn't store <21/07/2006>" . I suspect its to do with the default language , but although the default is English (United States) as in my last server, I have set the language in Advanced settings to be British English. I can't see any difference between the settings and my last server. I'm British BTW .

Yes it may be related to the language setting. Actually there are some differences between English (United States) and British English, including date format. You can use the following command to check details of all language settings:

EXEC sp_helplanguage

From the result we can see the dateformat of us_en is mdy, while the dateformate of British is dmy.

ashaig:
as the only login is sa . WK3 is itself set to English (United Kingdom) - I thought SQL 2005 would inherit this setting? Any help would be much appreciated.

You can change the default language setting of a SQL instance by using such command:

EXEC sp_configure 'default language',0
reconfigure with override

Where 0 is the id of the language. Here are some useful links about the language setting in SQL Server:

Default Language option:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_3xny.asp

sp_configure:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ca-cz_0put.asp

sql

DEFAULT INSTANCE INSTALLATION PROBLEM ON SQL2005

I have win2003 machine which is named fenermarket.
I have an installed instance which is FENERMARKET\ALISVERISSAATI.
I installed sp1 and sp2 too.
When i try to install default instance as another instance the setup process
give the error.Im sure that my Sa password is true. I choose windows
authentication as aut type.
SQL service MSSQLSERVER started successfully waiting for SQL service to
accept client connections
Service MSSQLSERVER started at Mon Feb 04 19:18:21 2008
Loaded DLL:
C:\WINDOWS\system32\Odbc32.dll
Version:
3.526.3959.0
SQL_ERROR (-1) in OdbcConnection::connect
sqlstate=28000, level=-1, state=-1, native_error=18456, msg=[Microsoft][SQL
Native Client][SQL Server]Login failed for user 'sa'.
Error Code: 0x80074818 (18456)
Windows Error Text: Source File Name: lib\odbc_connection.cpp
Compiler Timestamp: Fri Jul 29 01:13:53 2005
Function Name: OdbcConnection::connect@.connect
Source Line Number: 148
-- Context ---
Connecting to SQL Server
ExecuteSqlCommands
Originial error was 80074818 (18456)
ipt
SqlScriptHlpr
Error Code: 18456
Where do you need the "sa" Login and it's password if you use Windows
Authentication in SQL Server Setup?
In this error, the key thing is to see the "State number" in Login Failed
error message. Here is a nice blog about this error:
http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx
Ekrem nsoy
"in da club" <s> wrote in message
news:umNJdQ1ZIHA.1164@.TK2MSFTNGP02.phx.gbl...
>I have win2003 machine which is named fenermarket.
> I have an installed instance which is FENERMARKET\ALISVERISSAATI.
> I installed sp1 and sp2 too.
> When i try to install default instance as another instance the setup
> process give the error.Im sure that my Sa password is true. I choose
> windows authentication as aut type.
>
> SQL service MSSQLSERVER started successfully waiting for SQL service to
> accept client connections
> Service MSSQLSERVER started at Mon Feb 04 19:18:21 2008
> Loaded DLL:
> C:\WINDOWS\system32\Odbc32.dll
> Version:
> 3.526.3959.0
>
> SQL_ERROR (-1) in OdbcConnection::connect
> sqlstate=28000, level=-1, state=-1, native_error=18456,
> msg=[Microsoft][SQL Native Client][SQL Server]Login failed for user 'sa'.
> Error Code: 0x80074818 (18456)
> Windows Error Text: Source File Name: lib\odbc_connection.cpp
> Compiler Timestamp: Fri Jul 29 01:13:53 2005
> Function Name: OdbcConnection::connect@.connect
> Source Line Number: 148
>
> -- Context ---
>
> Connecting to SQL Server
> ExecuteSqlCommands
> Originial error was 80074818 (18456)
> ipt
> SqlScriptHlpr
>
> Error Code: 18456
>
|||i have choosen mixed mode. I know the url but i couldnt find any info about
my stuation . my error state id is not listed here.
sql

DEFAULT INSTANCE INSTALLATION PROBLEM ON SQL2005

I have win2003 machine which is named fenermarket.
I have an installed instance which is FENERMARKET\ALISVERISSAATI.
I installed sp1 and sp2 too.
When i try to install default instance as another instance the setup process
give the error.Im sure that my Sa password is true. I choose windows
authentication as aut type.
SQL service MSSQLSERVER started successfully waiting for SQL service to
accept client connections
Service MSSQLSERVER started at Mon Feb 04 19:18:21 2008
Loaded DLL:
C:\WINDOWS\system32\Odbc32.dll
Version:
3.526.3959.0
SQL_ERROR (-1) in OdbcConnection::connect
sqlstate=28000, level=-1, state=-1, native_error=18456, msg=[Microsoft][SQL
Native Client][SQL Server]Login failed for user 'sa'.
Error Code: 0x80074818 (18456)
Windows Error Text: Source File Name: lib\odbc_connection.cpp
Compiler Timestamp: Fri Jul 29 01:13:53 2005
Function Name: OdbcConnection::connect@.connect
Source Line Number: 148
-- Context ---
Connecting to SQL Server
ExecuteSqlCommands
Originial error was 80074818 (18456)
ipt
SqlScriptHlpr
Error Code: 18456Where do you need the "sa" Login and it's password if you use Windows
Authentication in SQL Server Setup?
In this error, the key thing is to see the "State number" in Login Failed
error message. Here is a nice blog about this error:
http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx
--
Ekrem Önsoy
"in da club" <s> wrote in message
news:umNJdQ1ZIHA.1164@.TK2MSFTNGP02.phx.gbl...
>I have win2003 machine which is named fenermarket.
> I have an installed instance which is FENERMARKET\ALISVERISSAATI.
> I installed sp1 and sp2 too.
> When i try to install default instance as another instance the setup
> process give the error.Im sure that my Sa password is true. I choose
> windows authentication as aut type.
>
> SQL service MSSQLSERVER started successfully waiting for SQL service to
> accept client connections
> Service MSSQLSERVER started at Mon Feb 04 19:18:21 2008
> Loaded DLL:
> C:\WINDOWS\system32\Odbc32.dll
> Version:
> 3.526.3959.0
>
> SQL_ERROR (-1) in OdbcConnection::connect
> sqlstate=28000, level=-1, state=-1, native_error=18456,
> msg=[Microsoft][SQL Native Client][SQL Server]Login failed for user 'sa'.
> Error Code: 0x80074818 (18456)
> Windows Error Text: Source File Name: lib\odbc_connection.cpp
> Compiler Timestamp: Fri Jul 29 01:13:53 2005
> Function Name: OdbcConnection::connect@.connect
> Source Line Number: 148
>
> -- Context ---
>
> Connecting to SQL Server
> ExecuteSqlCommands
> Originial error was 80074818 (18456)
> ipt
> SqlScriptHlpr
>
> Error Code: 18456
>|||i have choosen mixed mode. I know the url but i couldnt find any info about
my stuation . my error state id is not listed here.

Tuesday, March 27, 2012

Default Instance

Our installation of SQL server 2000 has been installed as
a named instance, and I am trying to install SMS2003. I
have read in the documentation that SMS2003 cannot be run
on a named instance, and must be run on the default
instance.
Is there any way of switching the named instance to the
default instance, or does anyone have any ideas on how I
can overcome this problem without reinstalling SQL server?
Hi,
As far as I know, You need to reinstall SQL server to move from named to
default instance.
Thanks
Hari
MCDBA
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:19fd601c44e18$8ac5ffb0$a301280a@.phx.gbl...
> Our installation of SQL server 2000 has been installed as
> a named instance, and I am trying to install SMS2003. I
> have read in the documentation that SMS2003 cannot be run
> on a named instance, and must be run on the default
> instance.
> Is there any way of switching the named instance to the
> default instance, or does anyone have any ideas on how I
> can overcome this problem without reinstalling SQL server?
|||You will have to re-install SQL... But you can get your databases back by
backup/restore or sp_detach_db sp_attach_db
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
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:19fd601c44e18$8ac5ffb0$a301280a@.phx.gbl...
> Our installation of SQL server 2000 has been installed as
> a named instance, and I am trying to install SMS2003. I
> have read in the documentation that SMS2003 cannot be run
> on a named instance, and must be run on the default
> instance.
> Is there any way of switching the named instance to the
> default instance, or does anyone have any ideas on how I
> can overcome this problem without reinstalling SQL server?
|||Will this restore the security, or will I have to restore
the Master DB as well?

>--Original Message--
>You will have to re-install SQL... But you can get your
databases back by
>backup/restore or sp_detach_db sp_attach_db
>--
>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
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:19fd601c44e18$8ac5ffb0$a301280a@.phx.gbl...
as[vbcol=seagreen]
I[vbcol=seagreen]
run[vbcol=seagreen]
I[vbcol=seagreen]
server?
>
>.
>
|||Hi,
To restore the SQl Server Logins you have to restore the Master database as
well. This will ensure that your security chain is brought back as same as
old.
Master database can be restored only if you start sql server in single user
mode.
How to start SQl server in single user mode:-
Execute the below command from command prompt
sqlservr.exe -c -m
Thanks
Hari
MCDBA
"Chris" <anonymous@.discussions.microsoft.com> wrote in message
news:19e7e01c44e25$6a834200$a601280a@.phx.gbl...[vbcol=seagreen]
> Will this restore the security, or will I have to restore
> the Master DB as well?
> databases back by
> (PASS) and it's
> message
> as
> I
> run
> I
> server?

Default Instance

Our installation of SQL server 2000 has been installed as
a named instance, and I am trying to install SMS2003. I
have read in the documentation that SMS2003 cannot be run
on a named instance, and must be run on the default
instance.
Is there any way of switching the named instance to the
default instance, or does anyone have any ideas on how I
can overcome this problem without reinstalling SQL server?Hi,
As far as I know, You need to reinstall SQL server to move from named to
default instance.
--
Thanks
Hari
MCDBA
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:19fd601c44e18$8ac5ffb0$a301280a@.phx.gbl...
> Our installation of SQL server 2000 has been installed as
> a named instance, and I am trying to install SMS2003. I
> have read in the documentation that SMS2003 cannot be run
> on a named instance, and must be run on the default
> instance.
> Is there any way of switching the named instance to the
> default instance, or does anyone have any ideas on how I
> can overcome this problem without reinstalling SQL server?|||You will have to re-install SQL... But you can get your databases back by
backup/restore or sp_detach_db sp_attach_db
--
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
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:19fd601c44e18$8ac5ffb0$a301280a@.phx.gbl...
> Our installation of SQL server 2000 has been installed as
> a named instance, and I am trying to install SMS2003. I
> have read in the documentation that SMS2003 cannot be run
> on a named instance, and must be run on the default
> instance.
> Is there any way of switching the named instance to the
> default instance, or does anyone have any ideas on how I
> can overcome this problem without reinstalling SQL server?|||Will this restore the security, or will I have to restore
the Master DB as well?
>--Original Message--
>You will have to re-install SQL... But you can get your
databases back by
>backup/restore or sp_detach_db sp_attach_db
>--
>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
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:19fd601c44e18$8ac5ffb0$a301280a@.phx.gbl...
>> Our installation of SQL server 2000 has been installed
as
>> a named instance, and I am trying to install SMS2003.
I
>> have read in the documentation that SMS2003 cannot be
run
>> on a named instance, and must be run on the default
>> instance.
>> Is there any way of switching the named instance to the
>> default instance, or does anyone have any ideas on how
I
>> can overcome this problem without reinstalling SQL
server?
>
>.
>|||Hi,
To restore the SQl Server Logins you have to restore the Master database as
well. This will ensure that your security chain is brought back as same as
old.
Master database can be restored only if you start sql server in single user
mode.
How to start SQl server in single user mode:-
Execute the below command from command prompt
sqlservr.exe -c -m
Thanks
Hari
MCDBA
"Chris" <anonymous@.discussions.microsoft.com> wrote in message
news:19e7e01c44e25$6a834200$a601280a@.phx.gbl...
> Will this restore the security, or will I have to restore
> the Master DB as well?
> >--Original Message--
> >You will have to re-install SQL... But you can get your
> databases back by
> >backup/restore or sp_detach_db sp_attach_db
> >
> >--
> >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
> >
> >"chris" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:19fd601c44e18$8ac5ffb0$a301280a@.phx.gbl...
> >> Our installation of SQL server 2000 has been installed
> as
> >> a named instance, and I am trying to install SMS2003.
> I
> >> have read in the documentation that SMS2003 cannot be
> run
> >> on a named instance, and must be run on the default
> >> instance.
> >>
> >> Is there any way of switching the named instance to the
> >> default instance, or does anyone have any ideas on how
> I
> >> can overcome this problem without reinstalling SQL
> server?
> >
> >
> >.
> >

Default Instance

Our installation of SQL server 2000 has been installed as
a named instance, and I am trying to install SMS2003. I
have read in the documentation that SMS2003 cannot be run
on a named instance, and must be run on the default
instance.
Is there any way of switching the named instance to the
default instance, or does anyone have any ideas on how I
can overcome this problem without reinstalling SQL server?Hi,
As far as I know, You need to reinstall SQL server to move from named to
default instance.
Thanks
Hari
MCDBA
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:19fd601c44e18$8ac5ffb0$a301280a@.phx
.gbl...
> Our installation of SQL server 2000 has been installed as
> a named instance, and I am trying to install SMS2003. I
> have read in the documentation that SMS2003 cannot be run
> on a named instance, and must be run on the default
> instance.
> Is there any way of switching the named instance to the
> default instance, or does anyone have any ideas on how I
> can overcome this problem without reinstalling SQL server?|||You will have to re-install SQL... But you can get your databases back by
backup/restore or sp_detach_db sp_attach_db
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
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:19fd601c44e18$8ac5ffb0$a301280a@.phx
.gbl...
> Our installation of SQL server 2000 has been installed as
> a named instance, and I am trying to install SMS2003. I
> have read in the documentation that SMS2003 cannot be run
> on a named instance, and must be run on the default
> instance.
> Is there any way of switching the named instance to the
> default instance, or does anyone have any ideas on how I
> can overcome this problem without reinstalling SQL server?|||Will this restore the security, or will I have to restore
the Master DB as well?

>--Original Message--
>You will have to re-install SQL... But you can get your
databases back by
>backup/restore or sp_detach_db sp_attach_db
>--
>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
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message
> news:19fd601c44e18$8ac5ffb0$a301280a@.phx
.gbl...
as[vbcol=seagreen]
I[vbcol=seagreen]
run[vbcol=seagreen]
I[vbcol=seagreen]
server?[vbcol=seagreen]
>
>.
>|||Hi,
To restore the SQl Server Logins you have to restore the Master database as
well. This will ensure that your security chain is brought back as same as
old.
Master database can be restored only if you start sql server in single user
mode.
How to start SQl server in single user mode:-
Execute the below command from command prompt
sqlservr.exe -c -m
Thanks
Hari
MCDBA
"Chris" <anonymous@.discussions.microsoft.com> wrote in message
news:19e7e01c44e25$6a834200$a601280a@.phx
.gbl...[vbcol=seagreen]
> Will this restore the security, or will I have to restore
> the Master DB as well?
>
> databases back by
> (PASS) and it's
> message
> as
> I
> run
> I
> server?sql

Default installation to named one

Hi,
I have installed sql server on one of my box as a named installation and now
I want to change it to default one without re-installing sql server.
Please suggest how should I go for it. Also, whether it is possible or not.
Thanks in advance
Manu JaidkaHello,
You will have to install a default instance and copy the databases including
system databases over to default instance
using Backup and restore or detach and Attach.
Thanks
Hari
"manu" <manu@.discussions.microsoft.com> wrote in message
news:7A299083-671F-4457-B9CE-263AE17838C0@.microsoft.com...
> Hi,
> I have installed sql server on one of my box as a named installation and
> now
> I want to change it to default one without re-installing sql server.
> Please suggest how should I go for it. Also, whether it is possible or
> not.
> Thanks in advance
> Manu Jaidka
>

Default installation to named one

Hi,
I have installed sql server on one of my box as a named installation and now
I want to change it to default one without re-installing sql server.
Please suggest how should I go for it. Also, whether it is possible or not.
Thanks in advance
Manu Jaidka
Hello,
You will have to install a default instance and copy the databases including
system databases over to default instance
using Backup and restore or detach and Attach.
Thanks
Hari
"manu" <manu@.discussions.microsoft.com> wrote in message
news:7A299083-671F-4457-B9CE-263AE17838C0@.microsoft.com...
> Hi,
> I have installed sql server on one of my box as a named installation and
> now
> I want to change it to default one without re-installing sql server.
> Please suggest how should I go for it. Also, whether it is possible or
> not.
> Thanks in advance
> Manu Jaidka
>

Default installation to named one

Default Install of SQLExpress Does NOT Work Well

I have modified one of the ASP.NET 2.0 Starter Kits using and when deploying it to a new server it works great when installed to a server that I have manually installed SQLExpress 2005 first, but if I deploy to a clean server and configure my standard Setup and Deployment project to require .NET 2.0 Runtime and SQLExpress 2005 problems start. The setup program will detect no .NET Framework or SQLExpress 2005 installed and will download and install them from Microsoft as it should. The ASP.NET web app installs correctly. I then grant the ASPNET machine account WRITE access permissions to the web app's App_Data directory where the database files are located for the application. For Windows 2003 Server this account has been renamed to: IIS_WPG.
To allow remote connection to this SQLExpress server, I then run the Surface Area Configuration Tool to allow remote connections via TCP/IP and Named Pipes and I set the SQLBrowser service to a running state with auto-start. The server is then re-started. The now infamous error occurs:

Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed.

The very same app can be XCopyed to another server where SQLExpress has been manually installed and where other ASP.NET web apps using attached DBF files are running merrily along, and it will work perfectly after granting ASPNET or IIS_WPG users write access to the app's data directory. Using local or remote access.

QUESTION? What is it with the default installation of SQLExpress 2005 when triggered via a Setup and Deployment project installer (MyApp.msi) and auto-downloaded from a Microsoft web site during installation of MyApp (ASP.NET 2.0 web application) that causes this.

This is a very bad thing if it is so hard for a web developer to distribute SQLEXpress with their applications and to get it running easily. The fact that the software is free pales against the backdrop of a support nightmare when a client just tries to get a simple web app up and running on a Microsoft IIS server. As a developer you have the intergrated SQL Server Developer Edition that just works, but when the rubber hits the road during deployment and the end user trys to install your "simple" web app and hits these roadblocks he/she justs gives up and moves onto another vendor's product.

Note to MS SQL Team: Distribute a product such as SQLExpress with the default settings set to allow the most reasonable security footprint for an intended audience. Default it to allow TCP/IP access for remote connections, and enable the SQLBrowser by default. Microsoft's focus on "security" goes way too far when the default installation of your products won't work out of the box without 3-4 days of searching the web for some arcane command settings. Do not ship a product such as this locked down for the FBI/CIA/Interpol/The Royal Canadian Mounted Police etc. They won't be shipping simple web applications that use it to small and medium sized businesses. They will however, use SQL Server 2005 Enterprise Edition and you can reasonably expect that they would have on their IT staff someone who knows how to "open up" the database from a security viewpoint. Your "free" product must work in a reliable, no-touch mode right from the box for the small developer to interested in shipping it with their software products. SQLExpress 2005 is an excellent product and a great step up from MSDE 2000, but when it doesn't work, it can bring a small development shop to its knees. I had a well-tested and running ASP.NET web application that used SQLExpress rejected by upload.com because when they went to verify that the application ran it was installed onto a clean machine and the .MSI installer triggered a download of .NET 2.0 Framework and SQLExpress. Developers such as myself look stupid trying to explain to an upload.com QA team that "the program really runs; honestly...", you just have to stand on one foot, touch your nose with your left hand, while learning to use the included Surface Area Configuration tool.
Assume little to no knowledge on the part of the end user installing an application, and configure your default settings of a low-end product from that perspective. The end user in a small business tring a demo web application should not have to worry about port numbers, surface area configuration tools, or any of that, they just want a product to work well.

By the way, don't get the impression that I am mad or otherwise frustrated from using this product...

That pretty much says it all... seems issues have not been addressed since Nov 2005... Do you have any tips regarding remote connections since you have dealth with many of the issues earlier...

Default Install of SQLExpress Does NOT Work Well

By the way, don't get the impression that I am mad or otherwise frustrated from using this product...

That pretty much says it all... seems issues have not been addressed since Nov 2005... Do you have any tips regarding remote connections since you have dealth with many of the issues earlier...

Default IIS Security Settings for RS?

What are the default directory security settings for each of the virtual
directories installed with RS?
/Reports
/ReportServer
In my quest to get authentication working properly I have changed 'em and
now they are totally broke.
If someone can help, it would be most appreciated.Integrated Windows Authentication.
--
Adrian M.
MCP
"Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
message news:35464884-1C32-42B0-BC77-392E02A31B29@.microsoft.com...
> What are the default directory security settings for each of the virtual
> directories installed with RS?
> /Reports
> /ReportServer
> In my quest to get authentication working properly I have changed 'em and
> now they are totally broke.
> If someone can help, it would be most appreciated.sql

Default Drive for new DBs

Is there a way to change the default location, including drive, of a SQL
isntall that was initially installed to the C drive?Space Junk wrote:
> Is there a way to change the default location, including drive, of a
> SQL isntall that was initially installed to the C drive?
By "default location", do you mean where the executables are installed?
Or do you mean where the data and log files are installed?
--
David Gugick
Imceda Software
www.imceda.com|||I mean where data and logs are installed by default. Sorry I thought I said
that.
Example, adding a bigger HDD to a system and needing new data bases to
default to the new drive.
"David Gugick" wrote:
> Space Junk wrote:
> > Is there a way to change the default location, including drive, of a
> > SQL isntall that was initially installed to the C drive?
> By "default location", do you mean where the executables are installed?
> Or do you mean where the data and log files are installed?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Space Junk wrote:
> I mean where data and logs are installed by default. Sorry I thought
> I said that.
> Example, adding a bigger HDD to a system and needing new data bases to
> default to the new drive.
Just add a new data file on the database to the new drive. SQL Server
will fill the data files (or log files) proportionally. If you want to
actually move the data and log files, you can use sp_detachdb and
sp_attach_db / sp_attach_single_file_db to disconnect them and reconnect
once they are on the new drive.
--
David Gugick
Imceda Software
www.imceda.com|||You can specify the database default folders for data and log files from
Enterprise Manager under server properties --> database settings.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Space Junk" <SpaceJunk@.discussions.microsoft.com> wrote in message
news:A66E48D4-8B2B-4948-B349-F2BDA2EFA09A@.microsoft.com...
> Is there a way to change the default location, including drive, of a SQL
> isntall that was initially installed to the C drive?|||I know how to move a data base, and data base settings only shows the C
drive. I do not need to change the folder data bases are being stored in. I
need to change the DEFAULT drive.
It is looking like a re-installation.
"Dan Guzman" wrote:
> You can specify the database default folders for data and log files from
> Enterprise Manager under server properties --> database settings.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Space Junk" <SpaceJunk@.discussions.microsoft.com> wrote in message
> news:A66E48D4-8B2B-4948-B349-F2BDA2EFA09A@.microsoft.com...
> > Is there a way to change the default location, including drive, of a SQL
> > isntall that was initially installed to the C drive?
>
>|||I tried a different physical HDD and then that new drive showed up under DB
Settings under DB properties. I am not sure why the other functioning drive
did not show up.
"Space Junk" wrote:
> I know how to move a data base, and data base settings only shows the C
> drive. I do not need to change the folder data bases are being stored in. I
> need to change the DEFAULT drive.
> It is looking like a re-installation.
> "Dan Guzman" wrote:
> > You can specify the database default folders for data and log files from
> > Enterprise Manager under server properties --> database settings.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Space Junk" <SpaceJunk@.discussions.microsoft.com> wrote in message
> > news:A66E48D4-8B2B-4948-B349-F2BDA2EFA09A@.microsoft.com...
> > > Is there a way to change the default location, including drive, of a SQL
> > > isntall that was initially installed to the C drive?
> >
> >
> >|||Space Junk wrote:
You can easily default new database data and log files under the
database settings tab in SQL EM. Just change the Default Data and
Default Log locations.
David Gugick
Imceda Software
www.imceda.com|||I was trying that, however me previous second HDD was not showing up in
there, only drive C. Not sure why, the drive was functioning normally. I
put a new drive in, and check again, then I had the option to choose other
than the C drive.
"David Gugick" wrote:
> Space Junk wrote:
> You can easily default new database data and log files under the
> database settings tab in SQL EM. Just change the Default Data and
> Default Log locations.
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>sql

Default Drive for new DBs

Is there a way to change the default location, including drive, of a SQL
isntall that was initially installed to the C drive?
Space Junk wrote:
> Is there a way to change the default location, including drive, of a
> SQL isntall that was initially installed to the C drive?
By "default location", do you mean where the executables are installed?
Or do you mean where the data and log files are installed?
David Gugick
Imceda Software
www.imceda.com
|||I mean where data and logs are installed by default. Sorry I thought I said
that.
Example, adding a bigger HDD to a system and needing new data bases to
default to the new drive.
"David Gugick" wrote:

> Space Junk wrote:
> By "default location", do you mean where the executables are installed?
> Or do you mean where the data and log files are installed?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Space Junk wrote:
> I mean where data and logs are installed by default. Sorry I thought
> I said that.
> Example, adding a bigger HDD to a system and needing new data bases to
> default to the new drive.
Just add a new data file on the database to the new drive. SQL Server
will fill the data files (or log files) proportionally. If you want to
actually move the data and log files, you can use sp_detachdb and
sp_attach_db / sp_attach_single_file_db to disconnect them and reconnect
once they are on the new drive.
David Gugick
Imceda Software
www.imceda.com
|||You can specify the database default folders for data and log files from
Enterprise Manager under server properties --> database settings.
Hope this helps.
Dan Guzman
SQL Server MVP
"Space Junk" <SpaceJunk@.discussions.microsoft.com> wrote in message
news:A66E48D4-8B2B-4948-B349-F2BDA2EFA09A@.microsoft.com...
> Is there a way to change the default location, including drive, of a SQL
> isntall that was initially installed to the C drive?
|||I know how to move a data base, and data base settings only shows the C
drive. I do not need to change the folder data bases are being stored in. I
need to change the DEFAULT drive.
It is looking like a re-installation.
"Dan Guzman" wrote:

> You can specify the database default folders for data and log files from
> Enterprise Manager under server properties --> database settings.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Space Junk" <SpaceJunk@.discussions.microsoft.com> wrote in message
> news:A66E48D4-8B2B-4948-B349-F2BDA2EFA09A@.microsoft.com...
>
>
|||I tried a different physical HDD and then that new drive showed up under DB
Settings under DB properties. I am not sure why the other functioning drive
did not show up.
"Space Junk" wrote:
[vbcol=seagreen]
> I know how to move a data base, and data base settings only shows the C
> drive. I do not need to change the folder data bases are being stored in. I
> need to change the DEFAULT drive.
> It is looking like a re-installation.
> "Dan Guzman" wrote:
|||Space Junk wrote:
You can easily default new database data and log files under the
database settings tab in SQL EM. Just change the Default Data and
Default Log locations.
David Gugick
Imceda Software
www.imceda.com
|||I was trying that, however me previous second HDD was not showing up in
there, only drive C. Not sure why, the drive was functioning normally. I
put a new drive in, and check again, then I had the option to choose other
than the C drive.
"David Gugick" wrote:

> Space Junk wrote:
> You can easily default new database data and log files under the
> database settings tab in SQL EM. Just change the Default Data and
> Default Log locations.
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Default Drive for new DBs

Is there a way to change the default location, including drive, of a SQL
isntall that was initially installed to the C drive?Space Junk wrote:
> Is there a way to change the default location, including drive, of a
> SQL isntall that was initially installed to the C drive?
By "default location", do you mean where the executables are installed?
Or do you mean where the data and log files are installed?
David Gugick
Imceda Software
www.imceda.com|||I mean where data and logs are installed by default. Sorry I thought I said
that.
Example, adding a bigger HDD to a system and needing new data bases to
default to the new drive.
"David Gugick" wrote:

> Space Junk wrote:
> By "default location", do you mean where the executables are installed?
> Or do you mean where the data and log files are installed?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Space Junk wrote:
> I mean where data and logs are installed by default. Sorry I thought
> I said that.
> Example, adding a bigger HDD to a system and needing new data bases to
> default to the new drive.
Just add a new data file on the database to the new drive. SQL Server
will fill the data files (or log files) proportionally. If you want to
actually move the data and log files, you can use sp_detachdb and
sp_attach_db / sp_attach_single_file_db to disconnect them and reconnect
once they are on the new drive.
David Gugick
Imceda Software
www.imceda.com|||You can specify the database default folders for data and log files from
Enterprise Manager under server properties --> database settings.
Hope this helps.
Dan Guzman
SQL Server MVP
"Space Junk" <SpaceJunk@.discussions.microsoft.com> wrote in message
news:A66E48D4-8B2B-4948-B349-F2BDA2EFA09A@.microsoft.com...
> Is there a way to change the default location, including drive, of a SQL
> isntall that was initially installed to the C drive?|||I know how to move a data base, and data base settings only shows the C
drive. I do not need to change the folder data bases are being stored in.
I
need to change the DEFAULT drive.
It is looking like a re-installation.
"Dan Guzman" wrote:

> You can specify the database default folders for data and log files from
> Enterprise Manager under server properties --> database settings.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Space Junk" <SpaceJunk@.discussions.microsoft.com> wrote in message
> news:A66E48D4-8B2B-4948-B349-F2BDA2EFA09A@.microsoft.com...
>
>|||I tried a different physical HDD and then that new drive showed up under DB
Settings under DB properties. I am not sure why the other functioning drive
did not show up.
"Space Junk" wrote:
[vbcol=seagreen]
> I know how to move a data base, and data base settings only shows the C
> drive. I do not need to change the folder data bases are being stored in.
I
> need to change the DEFAULT drive.
> It is looking like a re-installation.
> "Dan Guzman" wrote:
>|||Space Junk wrote:
You can easily default new database data and log files under the
database settings tab in SQL EM. Just change the Default Data and
Default Log locations.
David Gugick
Imceda Software
www.imceda.com|||I was trying that, however me previous second HDD was not showing up in
there, only drive C. Not sure why, the drive was functioning normally. I
put a new drive in, and check again, then I had the option to choose other
than the C drive.
"David Gugick" wrote:

> Space Junk wrote:
> You can easily default new database data and log files under the
> database settings tab in SQL EM. Just change the Default Data and
> Default Log locations.
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Sunday, March 25, 2012

Default dates to UTC in SQL Server 2000

Hi all,

I hope this is the correct forum. I have SQL Server 2000 installed on a server located in the USA. The local time zone on the server is EST. Therefore getdate() returns EST time. I now realise that I'd like to be saving all my timestamps in UTC. While I could modify all my stored procedures to use getutcdate() rather than getdate() I wonder if there is a database level setting for time zone that will cause getdate() to return UTC date?

Any ideas?

Des

Unfortunately, there is not a database or server lever 'switch' that would allow us to shift time zones.

However, it would be fairly quick to search syscomments for getdate() to determine a list of procedures needing alteration.

You would also want to change any table definitions where column level defaults are set to getdate().

Or, you could set the OS Server time for GMT timezone and turn automatic DST off -but that would effect all other non database logging on the server. Perhaps not a good idea...

|||

Thanks Arnie. I thought as much but it's always good to double check.

Regards,

Des

Friday, February 24, 2012

Debugging problem with break points not being picked up

I have Team suite VS2005 sp1 installed and I can't debug my SSIS packages. If I put any break points in the Script code when I try to debug I get the message.

"SQL Server Integration Services Script Task has encountered a problem and needs to close. We are sorry for the inconvenience."

I click on close and it runs the package but doesn't stop at any break point. If I take out all break points it will run without this error. A couple of times I have managed to put in a single break point early in the package within a different script component which causes it to break... but at a completely different point within a completely different script component. It's so weird. Any help would be appreciated.

Hoots.

I've moved this thread to the SSIS forum. The core VS Debugger team does not cover this area,

John

|||

An excert from Books Online -

Debugging the Script Task

To debug the code in your Script task, set at least one breakpoint in the code, and then close the VSA IDE to run the package in Business Intelligence Development Studio. When package execution enters the Script task, the VSA IDE reopens and displays your code in read-only mode. After execution reaches your breakpoint, you can examine variable values and step through the remaining code.

Note: You must execute the package to debug into your Script task. If you execute only the individual task, breakpoints in the Script task code are ignored.

Note:

You cannot debug a Script task when you run the Script task as part of a child package that is run from an Execute Package task. Breakpoints that you set within the Script task in the child package are disregarded in these circumstances. You can debug the child package normally by running it separately.

In addition -

You cannot debug Script tasks in event handlers.

When you debug a package that contains multiple Script tasks, the debugger hits breakpoints in only one Script task and will ignore breakpoints in the other Script tasks. If a Script task is part of a Foreach Loop or For Loop container, the debugger ignores breakpoints in the Script task after the first iteration of the loop.

|||I know how to debug a script task, it just doesn't. Clicking on Debug instead of close at the error message that's received brings up the Visual Studio Just in time debugger dialog which says:

An unhandled exception ('System.Runtime.InteropServices.COMException') occured in DTAttach.exe [3148]

It then gives me options to continue debugging in a new instance of Microsoft CLR debugger 2005 or a new instance of Visual Studio 2005. Choosing the CLT debugger brings up the dialog box saying:

"An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in Microsoft.SqlServer.ScriptTask.dll

Additional information: File position not valid for new breakpoint."

But the CLR debugger for DTAttach just reports that "(Disassembly cannot be displayed in run mode.)"

This seems to be a problem that has been around for a while, see this other post:

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

I too get the "Microsoft Visual Studio has lost its link to..." message. I have searched and searched, it seems quite a few other people are having the same problem but so far I haven't found a fix yet which is why I posted here.

Please help, is there a fix, can anyone tell me which file break points are stored in so that I can delete it manually as it could be corrupt?

thanks

Hoots.

|||I got a similar error without reference to the 'DTAttach'. Clicked the Debug button (Debug, Send Error Report, Don't Send) and continued from the break point that caused the error. Hope it works because that's all I got.|||Thanks for the response, unfortunately I think it just depends on what it feels like!! I can't get round it cos the whole debug thing just seems to be really flakey and am having to do it the old fashioned way with lots of msgbox commands. Would really appreciate a comment from a MVP or Microsoft itself on this one.

|||

Was having this problem just now. realized I had a breakpoint in a disabled script task. Once I took it out, not only did it stop throwing an error, it stopped on my other breakpoints.

Hope this helps.

|||Non of my script tasks are disabled. I think it's just a problem with the BIDS debugger. I'd love to have that confirmed and to find out when a fix is going to be available though.

|||

Hi there,

The problem you're seeing can be related to the fact that the precompiled script is out of sync with the code. One way to fix it is to remove the breakpoints on that script, regenerate the script binary ( either by changing the script by adding a comment or by flipping the precompiled property on the task off and back on), add the breakpoints back and save the script task and see if it works.

This assumes that you are not running on Windows Vista for which the debugging is currently broken due to an incompatibility between VSA and the CLR installed with the OS. On Vista the problem should be fixed at a later time but only for SQL Server SP2.

Let me know if refreshing the script binary caused the breakpoints to be valid again.

Thanks,

Silviu

|||Thanks for the info, unfortunately it didn't work. My package is made up predominantly of script components and contains a Foreach Loop and an additional Foreach loop within the other Loop, both are variable enumerators.

I went through each script component, took out any break points and put in additional comments. I then put a single break point in one of the scripts and ran it again but it still comes up with the same problem.

thanks

Hoots.
|||But break points are not supported in SSIS 2005... You have to use message boxes instead. This is a well documented "feature."|||Well documented... Really? Could you point me to the document that describes the features that causes BIDS to come up with the exception:

"An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in Microsoft.SqlServer.ScriptTask.dll

Seriously, I really would like to be able to read something definitive on break points in any script tasks not being supported for SSIS. After a large amount of searching and finding other people with the same problem, this is the first I've heard of them not being supported.

thanks

Hoots.

|||Have you read through this document? I see it claims support for script tasks, but not script components. I was under the assumption that breakpoints inside either script element were not supported.

http://msdn2.microsoft.com/en-us/library/ms140033.aspx|||Sorry, I meant script task rather than script component although I can't even see anything called a script component, what is it? Just as a note, all my script tasks are all set with precompiled as false.

Hoots.

|||Yep, read it now, I seem to be doing everything right, it just doesn't work.