Showing posts with label run. Show all posts
Showing posts with label run. Show all posts

Friday, March 30, 2012

Problems installing SP4 on MSDE 2000 (SP3)

Hi there,

I have some trouble installing SP4 onto an existing MSDE 2000 (with SP3).
I run setup like described in the readme: "setup /upgradesp sqlrun" => Setup starts and I get: "Product already installed ... "
Im using the MSDE version of the SP4 setup, the MSSQL version Im running is 8.00.760
Ive also tried to use the UPGRADEUSER and UPGRADEPWD arguments, but with the same results.

Any ideas?

Thank you

I guess you attempted couple of times SP4 install.

You need to modify following registry key and rerun setup.

after running successfull you need to change it back to original values.

Registry key is:

Local Machine\software\Microsoft\MSSQLServer\MSSqlServer\CurrentVersion

change 8.00.194 to something else [8.00.888]

change 8.00.761 to something else [8.00.999]

Thanks!

Problems installing Reporting Services

Hi Folks,

I want to download Reporting Services for SQL Server Express Edition but I can't find it.

It seems to be in Analysis Services but when I run SQLEXPR_ADV.EXE I don't find any mention of Reporting Services.

I get to the final options choice which offers me 'Database Services' and 'Client Components' but neither of these mentions Reporting Services.

I have installed BI Studio successfully.

I'd be grateful for any and all suggestions.

Thanks a lot.

Barry

You obtain "Reporting Services", you have to download and install SQL Server Express Advanced Services.

See:

SQL Server 2005 Express Edition (Advanced/SSMS)
http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx
http://msdn2.microsoft.com/en-us/library/ms365247.aspx
http://tinyurl.com/yelwr9

|||

It doesn't seem you read my question very carefully.

|||

hi Barry,

first verify IIS has been properly installed on the hosting computer ()...

uncheck "Hide advanced configuration options" setting..

in the "Feature selection" dialog task wizard, include "Reporting services"

after the "installation whistles" I got
"Reporting Services
The Reporting Services installation options you specified in Setup determine whether further configuration is required before you can access the report server. If you installed the default configuration, the report server can be used immediately. If you installed just the program files, you must run the Reporting Services Configuration tool to deploy the report server."

and it has been properly installed...

regards

|||

Hi Andrea,

Thanks for your reply.

Unfortunately, you told me what to do but not how to do it so I couldn't follow your advice.

Barry

|||

hi Barry,

Barry333 wrote:

Hi Andrea,

Thanks for your reply.

Unfortunately, you told me what to do but not how to do it so I couldn't follow your advice.

Barry

where did you find problems?

regarding IIS installation, goto control panel->Add or remove programs->Add or remove Windows components... check IIS..

the rest of the "message" indicates that, after running the SQLEXPR_ADV.EXE, you have to uncheck the "hid advanced configuration options" check box and proceed with the installation wizard... the very next wizard step is "Feature selection", where you have to specify to install the Report Server as well...

regards

|||

Hi Andrea,

I appreciate your concern.

Thanks

Barry

|||

hi Barry,

did you solve your isue?

regards

|||

Yes, by referring to the three books I recently bought on SQL Server 2005 Express.

One of the obscure problems was that SSE defaults to preventing remote access, which I needed.

Barry

|||

Hi Andrea,

I have put together a short step-by-step Tutorial for installing Reporting Services to help other people.

Would you like to contribute ?

If so, you can look at my draft :-

http://www.databaseanswers.org/pi_best_practice_display/manual.asp?manual_id=MS-RPTSVCS

You might like to consider how to add this part of your email to me :-

"the rest of the "message" indicates that, after running the SQLEXPR_ADV.EXE, you have to uncheck the "hid advanced configuration options" check box and proceed with the installation wizard... the very next wizard step is "Feature selection", where you have to specify to install the Report Server as well.

Regards

Barry

|||

hi Barry,

Barry333 wrote:

You might like to consider how to add this part of your email to me :-

"the rest of the "message" indicates that, after running the SQLEXPR_ADV.EXE, you have to uncheck the "hid advanced configuration options" check box and proceed with the installation wizard... the very next wizard step is "Feature selection", where you have to specify to install the Report Server as well.

* After running the SQLEXPR_ADV.EXE installer just double-clicking on it and after the pre-requisites installation (when required), please uncheck the "Hide advanced configuration options" check box to enable the full wizard and wizard steps experience.. The very next wizard step is "Feature selection", where you have to specify to install the Report Server as well.

sounds good? but I'm not native english speaker, so you should perhaps reconsider your invitation

regards

Wednesday, March 28, 2012

Problems Inserting DateTimeStamp into database

Here's the error I get trying to run the code posted below..."Prepared statement '(@.datestamp datetime,@.PrevValue real,@.NewValue real,@.IPaddress r' expects parameter @.datestamp, which was not supplied."

I've tried it without the quotes around the DateTime.Now and also adding the # around them. Without the quotes, I do get a different error because it doesn't like the spaces in the DateTime text. Also, the datestamp data in the database is formatted as a datetime (MS SQL Server 2005).

Here's my code:

PrivateSub UpdateRefresh()

UpdateConnection.Open()

UpdateDataAdapter.InsertCommand.CommandText = "INSERT INTO dbo.tbDeploy(datestamp," & _

"PrevValue, NewValue, IPaddress, HighCapability, LowCapability, Share) VALUES" & _

"('" & DateTime.Now & "', '" & lblCurrent.Text & "', '" & lblCurrent.Text & _

"', '" & lblIP.Text & "', '" & lblHigh.Text & "', '" & lblLow.Text & "', '" & lblShare.Text & "')"

UpdateDataAdapter.InsertCommand.ExecuteNonQuery()

UpdateConnection.Close()

EndSub

Use parameterized queries. Your problem will be solved. You can also prevent SQL Injection attacks.

Problems inserting datetime object into sql server 2005

hey everyone,

Im trying run a stored procedure, but I am getting the following message when trying to execute it.

Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

which seems strange, as there is no integer going to a datetime field. Also, it does not return the value that is incorrect, nor the parameter that is failing. If there is anyway to view this in VS 2005 Team suite, please let me know.

It should also be noted that when I execute this sproc from within sql management studio, it executes fine.

This is the stored procedure im trying to execute...

CREATE PROCEDURE [dbo].[sp_UpdateRequest]

@.request_id INT,
@.request_client_id INT,
@.request_description VARCHAR(150), @.request_date DATETIME,
@.request_edit_date DATETIME, @.request_status VARCHAR(25), @.request_approve_date DATETIME,
@.request_activity_code VARCHAR(7), @.request_department_code VARCHAR(3), @.request_participants INT,
@.request_activity_date DATETIME, @.request_activity_due_date DATETIME,
@.request_company_id INT, @.request_company_code INT,
@.request_notes VARCHAR(2000)

AS
BEGIN

UPDATE invoice_requests

SET request_client_id = @.request_client_id, request_description = @.request_description,
request_date = @.request_date, request_edit_date = @.request_edit_date,
request_status = @.request_status, request_approve_date = @.request_approve_date,
request_activity_code = @.request_activity_code,
request_department_code = @.request_department_code,
request_participants = @.request_participants,
request_activity_date = @.request_activity_date,
request_activity_due_date = @.request_activity_due_date,
request_company_code = @.request_company_code, request_company_id = @.request_company_id,
request_notes = @.request_notes

WHERE request_id = @.request_id

END
GO

in my .net app, all the datetime objects are valid dates, so I could not see why this would be generating the error that it is, so I ran the SQL Profiler, and this was the output. Again, i cannot see where im going wrong...

please note, I have seperated the output from the single slab of text, making sure I didnt remove anything...

exec sp_executesql
N'EXECUTE sp_UpdateRequest
@.request_id,
@.request_client_id,
@.request_description,
@.request_date,
@.request_edit_date,
@.request_status,
@.request_approve_date,
@.request_activity_code,
@.request_participants,
@.request_activity_date,
@.request_activity_due_date,
@.request_company_id,
@.request_company_code,
@.request_notes',

N'@.request_id int,
@.request_client_id int,
@.request_description varchar(8000),
@.request_date datetime,
@.request_edit_date datetime,
@.request_status varchar(5),
@.request_approve_date datetime,
@.request_activity_code varchar(8000),
@.request_department_code varchar(8000),
@.request_participants int,
@.request_activity_date datetime,
@.request_activity_due_date datetime,
@.request_company_id int,
@.request_company_code int,
@.request_notes varchar(8000)',

@.request_id=5,
@.request_client_id=1,
@.request_description='',
@.request_date=''2007-11-22 16:34:32:997'',
@.request_edit_date=''2007-11-22 16:34:32:997'',
@.request_status='Draft',
@.request_approve_date=''1970-01-01 00:00:00:000'',
@.request_activity_code='',
@.request_department_code='',
@.request_participants=0,
@.request_activity_date=''2007-11-29 00:00:00:000'',
@.request_activity_due_date=''2007-11-23 00:00:00:000'',
@.request_company_id=0,
@.request_company_code=1,
@.request_notes=''

any help on this would be greatly appreciated.
Cheershey everyone, when i try to run the the following trace result as a query, i get an error near 2007...

exec sp_executesql N'EXECUTE sp_UpdateRequest @.request_id, @.request_client_id, @.request_description, @.request_date, @.request_edit_date, @.request_status, @.request_approve_date, @.request_activity_code, @.request_participants,
@.request_activity_date, @.request_activity_due_date, @.request_company_id, @.request_company_code, @.request_notes',N'@.request_id int,@.request_client_id int,@.request_description varchar(6),@.request_date datetime,@.request_edit_date
datetime,@.request_status varchar(5),@.request_approve_date datetime,@.request_activity_code varchar(4),@.request_department_code varchar(3),@.request_participants int,@.request_activity_date datetime,@.request_activity_due_date
datetime,@.request_company_id int,@.request_company_code int,@.request_notes varchar(11)',@.request_id=1,@.request_client_id=1,@.r equest_description='Justin',@.request_date=''2007-11-23 08:59:04:250'',@.request_edit_date=''2007-11-23
08:59:04:250'',@.request_status='Draft',@.request_ap prove_date=''1970-01-01 00:00:00:000'',@.request_activity_code='0000',@.requ est_department_code='022',@.request_participants=50 ,@.request_activity_date=''2007-11-23
00:00:00:000'',@.request_activity_due_date=''2007-11-30 00:00:00:000'',@.request_company_id=5292,@.request_c ompany_code=1,@.request_notes='Other Notes'

are there meant to be the double single quotes('') before and after the date?

Cheers,|||are there meant to be the double single quotes('') before and after the date?No, there should be a single apostrophe surrounding (before and after) date constants.

Your constant ''2007-11-23 08:59:04:250'' syntactically ought to be '2007-11-23 08:59:04:250' for use in Microsoft SQL Server.

-PatP|||No, there should be a single apostrophe surrounding (before and after) date constants.

Your constant ''2007-11-23 08:59:04:250'' syntactically ought to be '2007-11-23 08:59:04:250' for use in Microsoft SQL Server.

-PatP

I'm not sure because it's already surrounded by single quotes.

maybe you can try '''2007-11-23 08:59:04:250'''|||Sorry, I should have looked at your whole example.

There are multiple ways that I could parse what you've posted. The one that makes the most sense to me at the moment is:exec sp_executesql N'EXECUTE sp_UpdateRequest
@.request_id, @.request_client_id, @.request_description
, @.request_date, @.request_edit_date, @.request_status
, @.request_approve_date, @.request_activity_code, @.request_participants
, @.request_activity_date, @.request_activity_due_date, @.request_company_id
, @.request_company_code, @.request_notes'', N''@.request_id int
, @.request_client_id int, @.request_description varchar(6), @.request_date datetime
, @.request_edit_date datetime, @.request_status varchar(5), @.request_approve_date datetime
, @.request_activity_code varchar(4), @.request_department_code varchar(3), @.request_participants int
, @.request_activity_date datetime, @.request_activity_due_date datetime, @.request_company_id int
, @.request_company_code int, @.request_notes varchar(11)''
, @.request_id = 1, @.request_client_id = 1, @.request_description = ''Justin''
, @.request_date = ''2007-11-23 08:59:04:250'', @.request_edit_date = ''2007-11-23 08:59:04:250''
, @.request_status = ''Draft'', @.request_approve_date = ''1970-01-01 00:00:00:000''
, @.request_activity_code = ''0000'', @.request_department_code = ''022'', @.request_participants = 50
, @.request_activity_date = ''2007-11-23 00:00:00:000'', @.request_activity_due_date = ''2007-11-30 00:00:00:000''
, @.request_company_id = 5292, @.request_company_code = 1, @.request_notes = ''Other Notes''
'See if that represents what you think you meant, and let me know how it works for you.

-PatPsql

Problems in Loading configuration file

Hi,

We have configured package to load configurations from XML file.

But whenever we try to run the package, it throws below warning message

and continues to download the configuration file and finally ending up in low memory exception

The package is attempting to configure from the XML file "E:\Cybage\Deployment\MSCERT\MSCERT_MainPackgeConfig.dtsConfig".

I have made sure the file exists at the above location.

Please let me know if anyone have any solution on this.


Regards,

sachin

What exactly is the file name you are trying to supply? What are the machine specs where the package is running? What exactly is configured in the xml file?
|||

SachinS wrote:

The package is attempting to configure from the XML file "E:\Cybage\Deployment\MSCERT\MSCERT_MainPackgeConfig.dtsConfig".

That's an informational message. Where's the error/warning message? That message will occur every single time (and it is valid) that the package runs.|||

We are storing connection strings in this config file.

'Loading configuration settings from file' message gets repeated n number of times and it does not stop until it gets some memory exception

|||Do you have the same configuration file listed multiple times in the package configuration window?sql

Monday, March 26, 2012

Problems getting SQL Server 2005 Express to run on my XP Pro PC

Hello
I have installed SQL Server 2005 on my XP Pro PC but I can't get it to run.
If I go into the SQL Server Management Studio Express program in the Server
name dropdown there is nothing displayed - even if I browse. But database
should be local.
If I look in Services I don't see any SQL Server entry? The only thing
vaguely related I see in Services is SQL Server VSS Writer. And this has
started OK.
How can I troubleshoot this?
AngusHi Angus
"Angus" wrote:
> Hello
> I have installed SQL Server 2005 on my XP Pro PC but I can't get it to run.
> If I go into the SQL Server Management Studio Express program in the Server
> name dropdown there is nothing displayed - even if I browse. But database
> should be local.
> If I look in Services I don't see any SQL Server entry? The only thing
> vaguely related I see in Services is SQL Server VSS Writer. And this has
> started OK.
> How can I troubleshoot this?
> Angus
>
What does it say as installed programs?
Do you have the installation log files see
http://msdn2.microsoft.com/en-us/library/ms143702.aspx ?
John

Problems getting SQL Server 2005 Express to run on my XP Pro PC

Hello
I have installed SQL Server 2005 on my XP Pro PC but I can't get it to run.
If I go into the SQL Server Management Studio Express program in the Server
name dropdown there is nothing displayed - even if I browse. But database
should be local.
If I look in Services I don't see any SQL Server entry? The only thing
vaguely related I see in Services is SQL Server VSS Writer. And this has
started OK.
How can I troubleshoot this?
AngusHi Angus
"Angus" wrote:

> Hello
> I have installed SQL Server 2005 on my XP Pro PC but I can't get it to run
.
> If I go into the SQL Server Management Studio Express program in the Serve
r
> name dropdown there is nothing displayed - even if I browse. But database
> should be local.
> If I look in Services I don't see any SQL Server entry? The only thing
> vaguely related I see in Services is SQL Server VSS Writer. And this has
> started OK.
> How can I troubleshoot this?
> Angus
>
What does it say as installed programs?
Do you have the installation log files see
http://msdn2.microsoft.com/en-us/library/ms143702.aspx ?
John

Problems deploying website

My ASP.NET application runs fine within the VS2005 IDE; but, when I deploy to my local machine to try to run it under IIS and calling it up in a browser, I'm getting the following exception:

Exception Details: System.Data.SqlClient.SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


[SqlException (0x80131904): Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734979
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e) +92
System.Web.UI.WebControls.ListControl.PerformSelect() +31
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.ListControl.OnPreRender(EventArgs e) +26
System.Web.UI.Control.PreRenderRecursiveInternal() +77
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360

I'm a novice at this and really need some help. What stupid newbie mistake am I making here? Thanks in advance for any guidance/assistance rendered.

When you deplyed the app you left the same SQL trusted connection, and most probably there is no local SQL server running in the same machine. Make sure to change the SQL string to the IP, username and password of the database and will work really well

Al

|||

I remember I got this problem too when I did my first try. It seems that your are using trusted connection, you need assign the domain user in your database with appropriate access right. Or you can create a database user in the database to use standard User/password connection method in your database is in mixed mode..

You can easily find the syntax for the connection string. As to create user in database, you need to create a login account under Security on the Server, in the database, you assign this user for access under database security tab.

Limno

Friday, March 23, 2012

Problems connecting to SQL-Server

Hi,
we have an customer trying to run our app. after installation. They get the
error messeage: Login failed for user skdomain/skuser.
Our app uses ADO.NET and the database is MS SQL Server. The program and sql
server is installed on a separate server. To run the app. they have 8
application servers with metaframe. Our app is set up to connect to the
database using WIN NT autentication.
Our app. runs fine if the user log in as domain administrator.
Is skdomain\skuser been granted access with a login in SQL Server? Do
they have the required users added to the server as well as setup as
users in the database?
-Sue
On Mon, 25 Oct 2004 08:23:03 -0700, "Olav"
<Olav@.discussions.microsoft.com> wrote:

>Hi,
>we have an customer trying to run our app. after installation. They get the
>error messeage: Login failed for user skdomain/skuser.
>Our app uses ADO.NET and the database is MS SQL Server. The program and sql
>server is installed on a separate server. To run the app. they have 8
>application servers with metaframe. Our app is set up to connect to the
>database using WIN NT autentication.
>Our app. runs fine if the user log in as domain administrator.
|||Hi Sue,
i dont follow you here. My be because i use a standalone XP Pro PC?
Where do i "click" to grant access with a login in SQL Servers? Can i see it
on my PC or must i use my customers?
Olav
"Sue Hoegemeier" wrote:

> Is skdomain\skuser been granted access with a login in SQL Server? Do
> they have the required users added to the server as well as setup as
> users in the database?
> -Sue
> On Mon, 25 Oct 2004 08:23:03 -0700, "Olav"
> <Olav@.discussions.microsoft.com> wrote:
>
>
|||You won't see skdomain\skuser on your PC. A windows login is
specific to the domain or workgroup. When using windows
authentication, any login you want to have access to SQL
Server needs to be added as a login for SQL Server. In
Enterprise Manager go to Security and then to Logins. Right
click on select New Login. Click the button to the right of
the Name text box and you will pull up the domain or
workgroup logins that you can add as logins for SQL Server.
-Sue
On Wed, 27 Oct 2004 03:37:04 -0700, "Olav"
<Olav@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Hi Sue,
>i dont follow you here. My be because i use a standalone XP Pro PC?
>Where do i "click" to grant access with a login in SQL Servers? Can i see it
>on my PC or must i use my customers?
>Olav
>"Sue Hoegemeier" wrote:
|||Thanks Sue,
this can take som time when there is 200 users to add?
May be a faster approach is to use SQL authentication.
Then it would work with only one user registered the same place?
Is there a difference in connection speed with NT and SQL authentication?
Olav
"Sue Hoegemeier" wrote:

> You won't see skdomain\skuser on your PC. A windows login is
> specific to the domain or workgroup. When using windows
> authentication, any login you want to have access to SQL
> Server needs to be added as a login for SQL Server. In
> Enterprise Manager go to Security and then to Logins. Right
> click on select New Login. Click the button to the right of
> the Name text box and you will pull up the domain or
> workgroup logins that you can add as logins for SQL Server.
> -Sue
> On Wed, 27 Oct 2004 03:37:04 -0700, "Olav"
> <Olav@.discussions.microsoft.com> wrote:
>
>
|||When you have a lot of users, it's generally more efficient to use
Windows groups rather than individual Windows logins. Another
consideration is that you don't necessarily have to use just
Enterprise Manager to add the logins - you can execute sp_grantlogin
to grant a windows account login access to SQL Server. You can use
sp_grantdbaccess to allow the login access to a database. This can
give you more flexibility in terms of building a script the users
access.
There is no real performance difference between the two authentication
modes. Windows Authentication is more secure than using SQL logins.
You may also want to check SQL Server books online and read up on
application roles as that may be something you would want to look
into.
-Sue
On Wed, 27 Oct 2004 06:47:06 -0700, "Olav"
<Olav@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks Sue,
>this can take som time when there is 200 users to add?
>May be a faster approach is to use SQL authentication.
>Then it would work with only one user registered the same place?
>Is there a difference in connection speed with NT and SQL authentication?
>Olav
>
>"Sue Hoegemeier" wrote:
|||Using a single SQL Server auth login for 200 users is not very secure.
Although I'm sure the DBA will probably appreciate getting out and talking
to all 200 employees every time he decides to change that password.
Tracking down any security issues should be easier, since you don't actually
have to track 200 users; just track the one login and blame all 200 users!
"Olav" <Olav@.discussions.microsoft.com> wrote in message
news:A35FA747-EAF6-44CE-AAAB-659A6C80AB14@.microsoft.com...[vbcol=seagreen]
> Thanks Sue,
> this can take som time when there is 200 users to add?
> May be a faster approach is to use SQL authentication.
> Then it would work with only one user registered the same place?
> Is there a difference in connection speed with NT and SQL authentication?
> Olav
>
> "Sue Hoegemeier" wrote:
see it[vbcol=seagreen]
get the[vbcol=seagreen]
and sql[vbcol=seagreen]
8[vbcol=seagreen]
the[vbcol=seagreen]

Problems connecting to SQL-Server

Hi,
we have an customer trying to run our app. after installation. They get the
error messeage: Login failed for user skdomain/skuser.
Our app uses ADO.NET and the database is MS SQL Server. The program and sql
server is installed on a separate server. To run the app. they have 8
application servers with metaframe. Our app is set up to connect to the
database using WIN NT autentication.
Our app. runs fine if the user log in as domain administrator.Is skdomain\skuser been granted access with a login in SQL Server? Do
they have the required users added to the server as well as setup as
users in the database?
-Sue
On Mon, 25 Oct 2004 08:23:03 -0700, "Olav"
<Olav@.discussions.microsoft.com> wrote:

>Hi,
>we have an customer trying to run our app. after installation. They get the
>error messeage: Login failed for user skdomain/skuser.
>Our app uses ADO.NET and the database is MS SQL Server. The program and sql
>server is installed on a separate server. To run the app. they have 8
>application servers with metaframe. Our app is set up to connect to the
>database using WIN NT autentication.
>Our app. runs fine if the user log in as domain administrator.|||Hi Sue,
i dont follow you here. My be because i use a standalone XP Pro PC?
Where do i "click" to grant access with a login in SQL Servers? Can i see it
on my PC or must i use my customers?
Olav
"Sue Hoegemeier" wrote:

> Is skdomain\skuser been granted access with a login in SQL Server? Do
> they have the required users added to the server as well as setup as
> users in the database?
> -Sue
> On Mon, 25 Oct 2004 08:23:03 -0700, "Olav"
> <Olav@.discussions.microsoft.com> wrote:
>
>|||You won't see skdomain\skuser on your PC. A windows login is
specific to the domain or workgroup. When using windows
authentication, any login you want to have access to SQL
Server needs to be added as a login for SQL Server. In
Enterprise Manager go to Security and then to Logins. Right
click on select New Login. Click the button to the right of
the Name text box and you will pull up the domain or
workgroup logins that you can add as logins for SQL Server.
-Sue
On Wed, 27 Oct 2004 03:37:04 -0700, "Olav"
<Olav@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Hi Sue,
>i dont follow you here. My be because i use a standalone XP Pro PC?
>Where do i "click" to grant access with a login in SQL Servers? Can i see i
t
>on my PC or must i use my customers?
>Olav
>"Sue Hoegemeier" wrote:
>|||Thanks Sue,
this can take som time when there is 200 users to add?
May be a faster approach is to use SQL authentication.
Then it would work with only one user registered the same place?
Is there a difference in connection speed with NT and SQL authentication?
Olav
"Sue Hoegemeier" wrote:

> You won't see skdomain\skuser on your PC. A windows login is
> specific to the domain or workgroup. When using windows
> authentication, any login you want to have access to SQL
> Server needs to be added as a login for SQL Server. In
> Enterprise Manager go to Security and then to Logins. Right
> click on select New Login. Click the button to the right of
> the Name text box and you will pull up the domain or
> workgroup logins that you can add as logins for SQL Server.
> -Sue
> On Wed, 27 Oct 2004 03:37:04 -0700, "Olav"
> <Olav@.discussions.microsoft.com> wrote:
>
>|||When you have a lot of users, it's generally more efficient to use
Windows groups rather than individual Windows logins. Another
consideration is that you don't necessarily have to use just
Enterprise Manager to add the logins - you can execute sp_grantlogin
to grant a windows account login access to SQL Server. You can use
sp_grantdbaccess to allow the login access to a database. This can
give you more flexibility in terms of building a script the users
access.
There is no real performance difference between the two authentication
modes. Windows Authentication is more secure than using SQL logins.
You may also want to check SQL Server books online and read up on
application roles as that may be something you would want to look
into.
-Sue
On Wed, 27 Oct 2004 06:47:06 -0700, "Olav"
<Olav@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks Sue,
>this can take som time when there is 200 users to add?
>May be a faster approach is to use SQL authentication.
>Then it would work with only one user registered the same place?
>Is there a difference in connection speed with NT and SQL authentication?
>Olav
>
>"Sue Hoegemeier" wrote:
>|||Using a single SQL Server auth login for 200 users is not very secure.
Although I'm sure the DBA will probably appreciate getting out and talking
to all 200 employees every time he decides to change that password.
Tracking down any security issues should be easier, since you don't actually
have to track 200 users; just track the one login and blame all 200 users!
"Olav" <Olav@.discussions.microsoft.com> wrote in message
news:A35FA747-EAF6-44CE-AAAB-659A6C80AB14@.microsoft.com...[vbcol=seagreen]
> Thanks Sue,
> this can take som time when there is 200 users to add?
> May be a faster approach is to use SQL authentication.
> Then it would work with only one user registered the same place?
> Is there a difference in connection speed with NT and SQL authentication?
> Olav
>
> "Sue Hoegemeier" wrote:
>
see it[vbcol=seagreen]
get the[vbcol=seagreen]
and sql[vbcol=seagreen]
8[vbcol=seagreen]
the[vbcol=seagreen]

Tuesday, March 20, 2012

Problems backing up SQL Express Database...

Hello,

I found some code below that works fine when I run it in my vb.net code in Design mode. When I publish the application, run the app and then execute the sub MyDbBackup() I get the following error: Cannot open backup device 'C:\dbBackup'. Operating system error 5(Access is denied). BACKUP DATABASE is terminating abnormally.

Here is the code that runs:

Public Sub MyDbBackup()
Dim sqlconn As New SqlClient.SqlConnection(MyConnectionStringdb)
sqlconn.Open()
Dim Backupcommand As SqlClient.SqlCommand = New SqlClient.SqlCommand("BACKUP DATABASE [" & sqlconn.Database.ToString & "] TO DISK = 'c:\myDataBaseBackup.bak'")
Backupcommand.CommandType = CommandType.Text
Backupcommand.Connection = sqlconn
Backupcommand.ExecuteNonQuery()
sqlconn.Close()
End Sub

2 Questions: Why does it backup fine in design mode, but not after it is published? Also, in design mode, my database is called TAP_Master.mdf but when I look under C:\Program Files\Microsoft\ SQL Server\MSSQL.1\MSSQL\Data, I do not see that database name... all I see is master.mdf, model, msdbdata etc... Is that database stored anywhere else?

I have set the MyConnectionStringdb variable to = "Data Source =.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\TAP_Master.mdf;Integrated Security=True;User Instance=True"

Thanks in advance.

You don't see your database in the Data directory because you embedded it in your application. The database is installed to the same location where you application is installed, which is an obfuscated path that is generated during ClickOnce installation. ClickOnce applications use a special type of instances of SQL Server called a User Instance to allow the database to be attached at runtime.

I'm not sure about the backup failure, I'll have to do a little more research. Is your published application on a different computer than the Dev box? I'm thinking it has something to do with a backup device that was created on the Dev box, but does not exist on the test box.

Mike

|||

Mike Wachal - MSFT wrote:

You don't see your database in the Data directory because you embedded it in your application. The database is installed to the same location where you application is installed, which is an obfuscated path that is generated during ClickOnce installation. ClickOnce applications use a special type of instances of SQL Server called a User Instance to allow the database to be attached at runtime.

I'm not sure about the backup failure, I'll have to do a little more research. Is your published application on a different computer than the Dev box? I'm thinking it has something to do with a backup device that was created on the Dev box, but does not exist on the test box.

Mike

Thanks for your reply. You are correct... My published application is on a different computer than my Dev box. If you can find out any additional info as to why I'm getting that error that would be great.

Also, I'll ask this question as well, Once the bak up file is created, what would be the best method to restore the .bak file to the machine that runs the published application.? I thought I could use the following code, but I receive the message, "Restore cannot process the database C"\Visual Studio\Projects\Prog\BIN\DEBUG\TAP_Master.MDF beause it is in use by this session. It is recommended that the master database be used when performing this operation":

Public Sub MyDbBackup()
Dim sqlconn As New SqlClient.SqlConnection(MyConnectionStringdb)
sqlconn.Open()
Dim Backupcommand As SqlClient.SqlCommand = New SqlClient.SqlCommand("RESTORE DATABASE [" & sqlconn.Database.ToString & "] FROM DISK = 'c:\myDataBaseBackup.bak'")
Backupcommand.CommandType = CommandType.Text
Backupcommand.Connection = sqlconn
Backupcommand.ExecuteNonQuery()
sqlconn.Close()
End Sub

Thanks in advance.

|||


Hi,

I guess that the user instance feature is not made for Backup and restore (with the emphasize on restore) because a restore will do a server attaching after the restoring which is not the sense of a user attached instance. Why don′t you take the MDF as a backup only ?

HTH, Jens K. Suessmeyer.


http://www.sqlserver2005.de

|||

Jens K. Suessmeyer wrote:


Hi,

... Why don′t you take the MDF as a backup only ?

HTH, Jens K. Suessmeyer.


http://www.sqlserver2005.de

Hello, can you elaborate on that quote? How can I take only the MDF as a backup? Basically I want to be able to back up any saved data, and then be able to restore it in case of a system failure or a corrupted operating system.

Thanks for your replies all.

|||Just do a normal file copy when the database connection is closed, then you can easily attach it to a user instance again when the data is corrupt.

HTH, Jens K. Suessmeyer.

htttp://www.sqlserver2005.de

|||

Ok. This is what I have done. I opened my published app on my laptop, and then entered in some data. I closed my app, and then attempted to copy the master.mdf file from the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data directory. At first I couldn't copy because SQL server express was still running. So I killed the services and copied the data.

I reinstalled my app, copied back the master.mdf into the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data directory, and then launched the app. Now I do not see the data that I previously entered.

Am I understanding the File Copy / Attach to user instance concept correctly?

Thanks again for all your help.

|||No actually not, the database which has to be backup is your own database not the SQL Server internal ones. YOu will have to backup the MDF file of the database with the reference TAP_master.mdf you mentioned above.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

The reason you're seeing the backup error is that the user context running your app on the deployed system doesn't have Windows permissions to create a file in the root of the local C: drive. That's why it's different from your dev environment: Different machine, different permissions.

So, either point the backup to a known directory that you're guaranteed to have permission to write to on machines where you're deployed, or find a way to make sure that you have appropriate permissions to the root of C:.

On the restore, it's simply a matter that you can't overwrite a database while you have an active session attached to it. Your connection is that session. By setting focus to Master (use master) you're taking focus away from the database you're trying to restore, and it should work fine.

|||

Kevin Farlee wrote:

On the restore, it's simply a matter that you can't overwrite a database while you have an active session attached to it. Your connection is that session. By setting focus to Master (use master) you're taking focus away from the database you're trying to restore, and it should work fine.

Ok thanks...First issue resolved... Now with regards to the second issue, Where am I setting focus to the master from? Is it within Visual Studio design mode, or under SQL Server 2005 Surface Area Configuration?

Thanks.

|||

Rashar wrote:

Kevin Farlee wrote:

On the restore, it's simply a matter that you can't overwrite a database while you have an active session attached to it. Your connection is that session. By setting focus to Master (use master) you're taking focus away from the database you're trying to restore, and it should work fine.

Ok thanks...First issue resolved... Now with regards to the second issue, Where am I setting focus to the master from? Is it within Visual Studio design mode, or under SQL Server 2005 Surface Area Configuration?

Thanks.

The context information is session based, you can change the context from within your script (if you are using one, I am not sure from your explnation using the USE <DatabaseName> command. If you are in any GUI of SQL Management Studio, make sure that you are not residing on a database node in the treeview that you want to restore.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Monday, March 12, 2012

Problem: Stored Procedures not completing from Web Application

I have several stored procedures that run fine from my SQL Server
database (via the exec command.), though when I call these procedures
from my web application, they do not complete. I have other
procedures that in fact do run fine through my web application though,
so I do not believe its a front-end problem. The procedures only take
about 30 seconds to run from the back-end, so I know its not a time
out issue as well. Does anyone have any ideas?

Thanks in advance."Mike J" <mjewett_2000@.yahoo.com> wrote in message
news:1a9d60fa.0404261152.322ed838@.posting.google.c om...
> I have several stored procedures that run fine from my SQL Server
> database (via the exec command.), though when I call these procedures
> from my web application, they do not complete. I have other
> procedures that in fact do run fine through my web application though,
> so I do not believe its a front-end problem. The procedures only take
> about 30 seconds to run from the back-end, so I know its not a time
> out issue as well. Does anyone have any ideas?
> Thanks in advance.

Can you clarify what you mean by "does not complete"? Do you get an error
message? What version of MSSQL? What's the web platform, eg. ASP, PHP etc.?
If it works fine from Query Analyzer then that suggests an issue on the web
side, but without more information it's hard to say.

Simon|||Mike J (mjewett_2000@.yahoo.com) writes:
> I have several stored procedures that run fine from my SQL Server
> database (via the exec command.), though when I call these procedures
> from my web application, they do not complete. I have other
> procedures that in fact do run fine through my web application though,
> so I do not believe its a front-end problem. The procedures only take
> about 30 seconds to run from the back-end, so I know its not a time
> out issue as well. Does anyone have any ideas?

30 seconds is the default time-out, so timeout problems cannot be ruled
out completely. Do you have proper error handling in your web app?

There are a couple of possible reasons for this, but since you provided
very little information about your code, I can only give some general
comments.

Do you use indexed views or indexes on computed columns? In such case,
you need to issue SET ARITHABORT ON when you connect from your web app
(or make this default for the database with ALTER DATABASE). This setting
is on by default when you run from Query Analyzer.

Even if you don't use indexed views or indexed computed columns, SET
ARITHABORT ON, can still be useful, as you now will get the same plan
as Query Analyzer does.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<408d7223$1_3@.news.bluewin.ch>...
> "Mike J" <mjewett_2000@.yahoo.com> wrote in message
> news:1a9d60fa.0404261152.322ed838@.posting.google.c om...
> > I have several stored procedures that run fine from my SQL Server
> > database (via the exec command.), though when I call these procedures
> > from my web application, they do not complete. I have other
> > procedures that in fact do run fine through my web application though,
> > so I do not believe its a front-end problem. The procedures only take
> > about 30 seconds to run from the back-end, so I know its not a time
> > out issue as well. Does anyone have any ideas?
> > Thanks in advance.
> Can you clarify what you mean by "does not complete"? Do you get an error
> message? What version of MSSQL? What's the web platform, eg. ASP, PHP etc.?
> If it works fine from Query Analyzer then that suggests an issue on the web
> side, but without more information it's hard to say.
> Simon

Simon,

Here's some more elaboration. By "does not complete" I mean that the
procedures simply never finish running. They generate no error
messages, and do not lock any objects -- they just dont finish. I
verify this by manually running the procedures in query analyzer
(where they finish quickly) and viewing the results. The strange
thing is that we use over 50 stored procedures, and almost all of them
work -- from both the web application and query analyzer. The couple
procedures that are "not finishing" are not any more complex than the
others.

The web platform we are using is Microsoft asp .NET. We are using SQL
Server 2000 as well.
Any ideas? Thanks.|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94D7EF10D40A3Yazorman@.127.0.0.1>...
> Mike J (mjewett_2000@.yahoo.com) writes:
> > I have several stored procedures that run fine from my SQL Server
> > database (via the exec command.), though when I call these procedures
> > from my web application, they do not complete. I have other
> > procedures that in fact do run fine through my web application though,
> > so I do not believe its a front-end problem. The procedures only take
> > about 30 seconds to run from the back-end, so I know its not a time
> > out issue as well. Does anyone have any ideas?
> 30 seconds is the default time-out, so timeout problems cannot be ruled
> out completely. Do you have proper error handling in your web app?
> There are a couple of possible reasons for this, but since you provided
> very little information about your code, I can only give some general
> comments.
> Do you use indexed views or indexes on computed columns? In such case,
> you need to issue SET ARITHABORT ON when you connect from your web app
> (or make this default for the database with ALTER DATABASE). This setting
> is on by default when you run from Query Analyzer.
> Even if you don't use indexed views or indexed computed columns, SET
> ARITHABORT ON, can still be useful, as you now will get the same plan
> as Query Analyzer does.

Erland, yes, we have proper error handling in our web application. We
in fact run many (around 50) stored procedures from our web
application and they complete just fine. The couple procedures that
do not run from the application are not any more complex than the
others.

To elaborate some more on our system, we are using Microsoft .NET, SQL
Server 2000. I have run many tests in query analyzer on our database
server with the procedures in question. They complete accurately and
quickly every time, with no errors. Its only when I call them from
the web app where do not finish. We do not use any indexed views or
computed columns, though I will try to ARITHABOR ON property anyhow.
Any other ideas?
Thanks.|||Mike J (mjewett_2000@.yahoo.com) writes:
> To elaborate some more on our system, we are using Microsoft .NET, SQL
> Server 2000. I have run many tests in query analyzer on our database
> server with the procedures in question. They complete accurately and
> quickly every time, with no errors. Its only when I call them from
> the web app where do not finish. We do not use any indexed views or
> computed columns, though I will try to ARITHABOR ON property anyhow.
> Any other ideas?

With that miniscule of information, no. Well, while you ruled out blocking
in another posting, one possibility is that you manage to block yourself
in the app. When you have a procedure which does not complete, execute
sp_who, and see if any process has a non-zero value in the Blk column.

If there is no blocking, use the Profiler to see where the process gets
stuck.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I had experienced issues with a store proc executing very quickly in Query Analyzer, but not from my ASP app against a development database. I noticed recently the same issue with the procedure not executing quickly in either QA or the web app.

After checking my table indexes on the dev database I found one joined table that had no index for the fields I was joining on. The index existed in the production database. Once adding that index. the procedure executed in < 1 second in QA and only a couple seconds from the web app.

Hope this helps anyone who's frustrated with similar issues.

Dave

Problem: Stored Procedures not completing from Web Application

I have several stored procedures that run fine from my SQL Server
database (via the exec command.), though when I call these procedures
from my web application, they do not complete. I have other
procedures that in fact do run fine through my web application though,
so I do not believe its a front-end problem. The procedures only take
about 30 seconds to run from the back-end, so I know its not a time
out issue as well. Does anyone have any ideas?

Thanks in advance."Mike J" <mjewett_2000@.yahoo.com> wrote in message
news:1a9d60fa.0404261152.322ed838@.posting.google.c om...
> I have several stored procedures that run fine from my SQL Server
> database (via the exec command.), though when I call these procedures
> from my web application, they do not complete. I have other
> procedures that in fact do run fine through my web application though,
> so I do not believe its a front-end problem. The procedures only take
> about 30 seconds to run from the back-end, so I know its not a time
> out issue as well. Does anyone have any ideas?
> Thanks in advance.

Can you clarify what you mean by "does not complete"? Do you get an error
message? What version of MSSQL? What's the web platform, eg. ASP, PHP etc.?
If it works fine from Query Analyzer then that suggests an issue on the web
side, but without more information it's hard to say.

Simon|||Mike J (mjewett_2000@.yahoo.com) writes:
> I have several stored procedures that run fine from my SQL Server
> database (via the exec command.), though when I call these procedures
> from my web application, they do not complete. I have other
> procedures that in fact do run fine through my web application though,
> so I do not believe its a front-end problem. The procedures only take
> about 30 seconds to run from the back-end, so I know its not a time
> out issue as well. Does anyone have any ideas?

30 seconds is the default time-out, so timeout problems cannot be ruled
out completely. Do you have proper error handling in your web app?

There are a couple of possible reasons for this, but since you provided
very little information about your code, I can only give some general
comments.

Do you use indexed views or indexes on computed columns? In such case,
you need to issue SET ARITHABORT ON when you connect from your web app
(or make this default for the database with ALTER DATABASE). This setting
is on by default when you run from Query Analyzer.

Even if you don't use indexed views or indexed computed columns, SET
ARITHABORT ON, can still be useful, as you now will get the same plan
as Query Analyzer does.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<408d7223$1_3@.news.bluewin.ch>...
> "Mike J" <mjewett_2000@.yahoo.com> wrote in message
> news:1a9d60fa.0404261152.322ed838@.posting.google.c om...
> > I have several stored procedures that run fine from my SQL Server
> > database (via the exec command.), though when I call these procedures
> > from my web application, they do not complete. I have other
> > procedures that in fact do run fine through my web application though,
> > so I do not believe its a front-end problem. The procedures only take
> > about 30 seconds to run from the back-end, so I know its not a time
> > out issue as well. Does anyone have any ideas?
> > Thanks in advance.
> Can you clarify what you mean by "does not complete"? Do you get an error
> message? What version of MSSQL? What's the web platform, eg. ASP, PHP etc.?
> If it works fine from Query Analyzer then that suggests an issue on the web
> side, but without more information it's hard to say.
> Simon

Simon,

Here's some more elaboration. By "does not complete" I mean that the
procedures simply never finish running. They generate no error
messages, and do not lock any objects -- they just dont finish. I
verify this by manually running the procedures in query analyzer
(where they finish quickly) and viewing the results. The strange
thing is that we use over 50 stored procedures, and almost all of them
work -- from both the web application and query analyzer. The couple
procedures that are "not finishing" are not any more complex than the
others.

The web platform we are using is Microsoft asp .NET. We are using SQL
Server 2000 as well.
Any ideas? Thanks.|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94D7EF10D40A3Yazorman@.127.0.0.1>...
> Mike J (mjewett_2000@.yahoo.com) writes:
> > I have several stored procedures that run fine from my SQL Server
> > database (via the exec command.), though when I call these procedures
> > from my web application, they do not complete. I have other
> > procedures that in fact do run fine through my web application though,
> > so I do not believe its a front-end problem. The procedures only take
> > about 30 seconds to run from the back-end, so I know its not a time
> > out issue as well. Does anyone have any ideas?
> 30 seconds is the default time-out, so timeout problems cannot be ruled
> out completely. Do you have proper error handling in your web app?
> There are a couple of possible reasons for this, but since you provided
> very little information about your code, I can only give some general
> comments.
> Do you use indexed views or indexes on computed columns? In such case,
> you need to issue SET ARITHABORT ON when you connect from your web app
> (or make this default for the database with ALTER DATABASE). This setting
> is on by default when you run from Query Analyzer.
> Even if you don't use indexed views or indexed computed columns, SET
> ARITHABORT ON, can still be useful, as you now will get the same plan
> as Query Analyzer does.

Erland, yes, we have proper error handling in our web application. We
in fact run many (around 50) stored procedures from our web
application and they complete just fine. The couple procedures that
do not run from the application are not any more complex than the
others.

To elaborate some more on our system, we are using Microsoft .NET, SQL
Server 2000. I have run many tests in query analyzer on our database
server with the procedures in question. They complete accurately and
quickly every time, with no errors. Its only when I call them from
the web app where do not finish. We do not use any indexed views or
computed columns, though I will try to ARITHABOR ON property anyhow.
Any other ideas?
Thanks.|||Mike J (mjewett_2000@.yahoo.com) writes:
> To elaborate some more on our system, we are using Microsoft .NET, SQL
> Server 2000. I have run many tests in query analyzer on our database
> server with the procedures in question. They complete accurately and
> quickly every time, with no errors. Its only when I call them from
> the web app where do not finish. We do not use any indexed views or
> computed columns, though I will try to ARITHABOR ON property anyhow.
> Any other ideas?

With that miniscule of information, no. Well, while you ruled out blocking
in another posting, one possibility is that you manage to block yourself
in the app. When you have a procedure which does not complete, execute
sp_who, and see if any process has a non-zero value in the Blk column.

If there is no blocking, use the Profiler to see where the process gets
stuck.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Saturday, February 25, 2012

Problem With Variables in Execute SQL Task

I am trying to run a simple update (just to establish it works) passing in a variable in the execute SQL task. It is within a Foreach loop and the incoming data is from a RecordSet destination. One of the fields in the recordset is UniqueID. I have a variable called User::UniqueID which holds the uniqueID value. I then have a second variable which is the insert statement of

"INSERT rptlifespan_transactionimagecopy SELECT " + (DT_WSTR, 20)@.[User::UniqueID] + ", null, null,null,null,null,null,null"

with delayvalidation set to True.

when I run the package i get the following error:

Error: 0xC0014054 at Execute SQL Task: Failed to lock variable "INSERT rptlifespan_transactionimagecopy SELECT 543, null, null,null,null,null,null,null" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

543 is the value of my first UniqueID so that bit is working ok. What I do not know is why the insert statement is failing.

Please help i have very little hair left!!

Is the SQLSourceType=Variable and the SourceVariable set to the name of your variable in the task editor? It looks as if maybe SQLSourceType=Variable and then you're setting the SqlStatementSource property through an expression to the value of your variable.
|||

Hi Jay

i have checked and the SqlSourceType is Variable and the SourceVariable is set to User:: SQLTest2 which is the name of the variable that contains the Value INSERT blah blah blah....

When I run it the package fails with the same error and when I go back into the Execute Sql Task editor the SourceVariable has changed to the actual INSERT statement.

Any help appreciated

|||Delete the Execute SQL Task and try recreating it. I think, too, there must be an expression set somewhere incorrectly.|||

Racsco wrote:

When I run it the package fails with the same error and when I go back into the Execute Sql Task editor the SourceVariable has changed to the actual INSERT statement.

That shouldn't be happening. Maybe you should delete the Execute SQL task and recreate it.
|||

Hi Guys

thanks for your continuing help.

I have deleted and recreated it and now i get the following error

Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query "INSERT rptlifespan_transactionimagecopy SELECT 543, null, null,null,null,null,null,null" failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_I4)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

any ideas?

|||Right. You're selecting more than one column and perhaps row, so you need to set the Execute SQL task to return a Full Result Set, not Single Row, and assign it to an OBJECT variable, not an integer variable.

Any reason you're selecting a bunch of NULL literals? Drop them and add them later using a derived column if you desire. It will make working with your result set easier.|||

Phil Brammer wrote:

Right. You're selecting more than one column and perhaps row, so you need to set the Execute SQL task to return a Full Result Set, not Single Row, and assign it to an OBJECT variable, not an integer variable.

Any reason you're selecting a bunch of NULL literals? Drop them and add them later using a derived column if you desire. It will make working with your result set easier.

It's an INSERT statement. There will be no resultset.
|||

JayH wrote:

Phil Brammer wrote:

Right. You're selecting more than one column and perhaps row, so you need to set the Execute SQL task to return a Full Result Set, not Single Row, and assign it to an OBJECT variable, not an integer variable.

Any reason you're selecting a bunch of NULL literals? Drop them and add them later using a derived column if you desire. It will make working with your result set easier.

It's an INSERT statement. There will be no resultset.

Oh, right. I saw the INSERT and the SELECT together and misread it as two distinct statements.

So unless you have another SQL statement after the result set, set the result set to "None."|||

OK I am nearing frustration point!!

I have ripped it back to the bare minimum just so I can get it to work. I have a data flow that does a select on 1 column - UniqueID from a table into a recordset destination as an object variable called LoadList. This then flows to a Foreach Container set as Foreach ADO Enumerator with 1 Variable mapped as User:: UniqueID and index 0. Within the Foreach Loop is the Execute SQL task with SQLSourceType of Variable and SourceVariable of User:: SQLTest3 and result set set to none. I have 2 package level variables which are User:: UniqueID which is INT32 and value is initially 0 and User:: SQLTest3 which is "INSERT rptlifespan_transactionimagecopy SELECT " + (DT_WSTR, 20)@.[User::UniqueID] . So all i am trying to do is place the unique ID from the initial select into a different table.

Thanks once again for any help

|||

Racsco wrote:

OK I am nearing frustration point!!

I have ripped it back to the bare minimum just so I can get it to work. I have a data flow that does a select on 1 column - UniqueID from a table into a recordset destination as an object variable called LoadList. This then flows to a Foreach Container set as Foreach ADO Enumerator with 1 Variable mapped as User:: UniqueID and index 0. Within the Foreach Loop is the Execute SQL task with SQLSourceType of Variable and SourceVariable of User:: SQLTest3 and result set set to none. I have 2 package level variables which are User:: UniqueID which is INT32 and value is initially 0 and User:: SQLTest3 which is "INSERT rptlifespan_transactionimagecopy SELECT " + (DT_WSTR, 20)@.[User::UniqueID] . So all i am trying to do is place the unique ID from the initial select into a different table.

Thanks once again for any help

Your description sounds good. I assume you have a reason for doing it this way instead of just bulk loading into rptlifespan_transactionimagecopy from the data flow. So from your "frustration" comment I assume you're still having a problem. Is it the "An error occurred while extracting the result into a variable..."? I don't see how you could be getting that if your ResultSet is None and there are no mappings on the Resultset page.
|||Forget the data flow.

Code block because of the $@.!* emoticon conversions...

1 - Execute SQL Task: select uniqueID from table. Put that into an object variable (User::ObjectVar, perhaps) and set the result set type to "Full Result Set"
2 - Foreach Loop - Set it to loop on the object variable created from step 1 (User::ObjectVar). Map the output accordingly to your "holding" variable (User::UniqueID)
3 - Execute SQL Task within the foreach loop: SQLSourceType: Variable SourceVariable: User::SQLTest3

That's it. One thing to note: On the variable, User::UniqueID, make sure you have EvaluateAsExpression set to TRUE and that you are using the expression property to build the insert string.|||

Hi Phil

I have followed the above the only bit I am not sure on is where you say "you are using the expression property to build the insert string" as my variable User:: SqlTest3 contains the insert statement?

Cheers again

scott

|||

Racsco wrote:

Hi Phil

I have followed the above the only bit I am not sure on is where you say "you are using the expression property to build the insert string" as my variable User:: SqlTest3 contains the insert statement?

Cheers again

scott

On the properties for SQLTest3, since you are concatenating another user variable in your SQL statement, you need to do that in the expression. Set the property, EvaluateAsExpression = TRUE for the variable, SQLTest3. Then use the expression:

"INSERT INTO ... SELECT " + [User::UniqueID]|||

Hi Phil

Went home and slept on it, came in deleted the lot and started fresh. What I did differently was to cast the UniqueID as a string from the initial select rather than casting it in User:: SQLtest3 variable and it works fine now.

Thanks for all your help and patience and also thanks to JayH

scott

Problem with using UNION in the where clause.

Hi, I'm using the JDBC:ODBC bridge but am having a problem using it with MS
Access.
I want to run the following thru the bridge;
SELECT * FROM Horse WHERE horseName Like 'MINORS UNION'
I can run this query in Access with no probs. I get an SQL.Exception when
running thru the bridge. I am running thru a try catch block.
I am assuming the problem lies with the keyword UNION being used in the
value.
any help appreciated,
Michael.
I haven't done very much with the JDBC/ODBC bridge. I would think that as
long as the expression 'MINOR UNION' is a valid string delimited by quotes,
the reserved word theory should not be a factor. Can you post the exact
SQLException that you receive?
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
| From: "Finbar Saunders" <finbar@.netspace.net.au>
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| Subject: Problem with using UNION in the where clause.
| Date: Fri, 16 Apr 2004 17:46:42 +0930
| Organization: Netspace Internet
| Lines: 16
| Message-ID: <c5o4p6$2i17$1@.otis.netspace.net.au>
| NNTP-Posting-Host: dsl-203-113-238-176.sa.netspace.net.au
| X-Trace: otis.netspace.net.au 1082103398 84007 203.113.238.176 (16 Apr
2004 08:16:38 GMT)
| X-Complaints-To: usenet@.otis.netspace.net.au
| NNTP-Posting-Date: Fri, 16 Apr 2004 08:16:38 +0000 (UTC)
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed 00.sul.t-online.de!t-onlin
e.de!newsfeed.wirehub.nl!surfnet.nl!newsfeed.kabel foon.nl!195.129.110.21.MIS
MATCH!bnewsfeed00.bru.ops.eu.uu.net!bnewsinpeer01. bru.ops.eu.uu.net!emea.uu.
net!ash.uu.net!duster.adelaide.on.net!news.melbour ne.pipenetworks.com!news.l
abyrinth.net.au!news.netspace.net.au!not-for-mail
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.jdbcdriver:5892
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hi, I'm using the JDBC:ODBC bridge but am having a problem using it with
MS
| Access.
|
| I want to run the following thru the bridge;
| SELECT * FROM Horse WHERE horseName Like 'MINORS UNION'
|
| I can run this query in Access with no probs. I get an SQL.Exception when
| running thru the bridge. I am running thru a try catch block.
|
| I am assuming the problem lies with the keyword UNION being used in the
| value.
|
| any help appreciated,
| Michael.
|
|
|

Monday, February 20, 2012

problem with url in microsoft server 2005

Hi,
I have a problem:

When i enter in the iis i have two virtual directory:
Reportsr$sqlexpress
ReportServer$sqlexpress

I try to run the report by browsing home.aspx and going to my
report.The url that i got is:

http://localhost/Reports$SQLExpress/Pages/Report.aspx?ItemPath=%2fReportInventory%2fReportTotalCounting

Now, i enter the parameter wanted and click on export to pdf file
it works fine :it opens the report in a pdf file ,i got a url like
this:

http://localhost/Reports$SQLExpress/Reserved.ReportViewerWebControl.axd?ReportSession=a52f1o55wy00wq454pq0jp45&ControlID=f8672382-3d09-430f-a308-9bf42b850184&Culture=1037&UICulture=9&ReportStack=1&OpType=Export&FileName=ReportTotalCounting&ContentDisposition=OnlyHtmlInline&Format=PDF

but the probem is that i think the url is encrript because i don't see the parameters i entered in that url!

what i would like to have is an url like this:

http://localhost/Reports$SQLExpress?%2fReportInventory%2fReportTotalCounting&Parameter_WarehouseID=12&rs%3aCommand=Render&rs%3AFormat=PDF

but this url doesn't work it return me to the url:
http://localhost/Reports$SQLExpress/Pages/Folder.aspx

how can i have a url that work fine so i can pass the paramerter(a url not encript)?

is there somewere where i have to set if the url is encript or not?

help me please why the url does'nt work?
thanks in advance

The Report Manager virtual directory does not have a URL access interface. If you wish to pass parameters and other directives via URL, you must use the Report Server virtual directory:

http://localhost/ReportServer$SQLExpress/Pages/ReportViewer.aspx?%2fReport1&rs%3aCommand=Render

|||

You can pass the parameter in url by accessing the report server. Try http://localhost/ReportServer$sqlexpress/... instead.

http://msdn2.microsoft.com/en-us/library/aa256630(SQL.80).aspx

Problem with updating column in table - transaction log full

Hello,
I had trouble performing an update of one column of a table.
The column is of Bit data type and the table has 60350 rows.
When I run the following query in the Query Analyzer:
update [Specimen CV] set HPV = 0
I get a message "The log file for database DBNAME is full. Back up the
transaction log for the database to free up space"
I backed up both the DB and the log, truncated as well as increased the size
of the log file, first to 100MB.
I even set the DB to simple recovery mode. Still, I got the same message.
Finally, when I set the size to 1 GB did the update run.
After the update ran I checked the Log Space used and it was 12.6%
Does really a simple query like that on a table with only 60K records
require 126 MB of log space?
Any comment would be appreciated.
Ragnar
On Mar 7, 7:54 pm, "Ragnar Midtskogen" <ragnar...@.newsgroups.com>
wrote:
> Hello,
> I had trouble performing an update of one column of a table.
> The column is of Bit data type and the table has 60350 rows.
> When I run the following query in the Query Analyzer:
> update [Specimen CV] set HPV = 0
> I get a message "The log file for database DBNAME is full. Back up the
> transaction log for the database to free up space"
> I backed up both the DB and the log, truncated as well as increased the size
> of the log file, first to 100MB.
> I even set the DB to simple recovery mode. Still, I got the same message.
> Finally, when I set the size to 1 GB did the update run.
> After the update ran I checked the Log Space used and it was 12.6%
> Does really a simple query like that on a table with only 60K records
> require 126 MB of log space?
> Any comment would be appreciated.
> Ragnar
Are there triggers on this table? Your single update statement, while
seemingly simple, is creating an implicit transaction, and any change
that the update produces, directly or indirectly, is captured within
that transaction. The log file, which stores the "undo" information
for all of these changes, must be large enough to hold everything that
the update statement is changing, within the table being updated, AND
any other tables that are being affected by triggers on that table.
|||Thank you Tracy,
There are no triggers on any of the tables.
I guess this was not an indication of anything wrong.
By the way, I seem to remember that, depending on some settings, the log
will grow if needed, but I have been digging through the SQL Server Books
Online without finding anything about this.
I have seen a few cases in our company where someone has set up an SQL
Server DB without setting up regular backups, where the log just kept
growing. In one case the log used up all the available space on the disk and
effectively shut down the computer, not enough room for the swap file.
I think there is a way to control the growth of the log file, but I have not
found out how to do this.
Any pointers would be appreciated.
Ragnar

Problem with updating column in table - transaction log full

Hello,
I had trouble performing an update of one column of a table.
The column is of Bit data type and the table has 60350 rows.
When I run the following query in the Query Analyzer:
update [Specimen CV] set HPV = 0
I get a message "The log file for database DBNAME is full. Back up the
transaction log for the database to free up space"
I backed up both the DB and the log, truncated as well as increased the size
of the log file, first to 100MB.
I even set the DB to simple recovery mode. Still, I got the same message.
Finally, when I set the size to 1 GB did the update run.
After the update ran I checked the Log Space used and it was 12.6%
Does really a simple query like that on a table with only 60K records
require 126 MB of log space?
Any comment would be appreciated.
RagnarOn Mar 7, 7:54 pm, "Ragnar Midtskogen" <ragnar...@.newsgroups.com>
wrote:
> Hello,
> I had trouble performing an update of one column of a table.
> The column is of Bit data type and the table has 60350 rows.
> When I run the following query in the Query Analyzer:
> update [Specimen CV] set HPV = 0
> I get a message "The log file for database DBNAME is full. Back up the
> transaction log for the database to free up space"
> I backed up both the DB and the log, truncated as well as increased the si
ze
> of the log file, first to 100MB.
> I even set the DB to simple recovery mode. Still, I got the same message.
> Finally, when I set the size to 1 GB did the update run.
> After the update ran I checked the Log Space used and it was 12.6%
> Does really a simple query like that on a table with only 60K records
> require 126 MB of log space?
> Any comment would be appreciated.
> Ragnar
Are there triggers on this table? Your single update statement, while
seemingly simple, is creating an implicit transaction, and any change
that the update produces, directly or indirectly, is captured within
that transaction. The log file, which stores the "undo" information
for all of these changes, must be large enough to hold everything that
the update statement is changing, within the table being updated, AND
any other tables that are being affected by triggers on that table.|||Thank you Tracy,
There are no triggers on any of the tables.
I guess this was not an indication of anything wrong.
By the way, I seem to remember that, depending on some settings, the log
will grow if needed, but I have been digging through the SQL Server Books
Online without finding anything about this.
I have seen a few cases in our company where someone has set up an SQL
Server DB without setting up regular backups, where the log just kept
growing. In one case the log used up all the available space on the disk and
effectively shut down the computer, not enough room for the swap file.
I think there is a way to control the growth of the log file, but I have not
found out how to do this.
Any pointers would be appreciated.
Ragnar|||How much space is used for the log is dictated by how much log records you g
enerate and how often
you empty the log. To empty the log, you have basically two options:
Have the database in simple recovery mode. Now SQL Server will empty the log
automatically (every
time a checkpoint occurs).
Have it in full recovery mode and do regular transaction log backups.
I suggest you read a bit about recovery models and backup to get more insigh
t into this. Also, you
might want to check out (related reading):
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://sqlblog.com/blogs/tibor_kara...>
rinking.aspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:e8F6%23RaYHHA.3824@.TK2MSFTNGP02.phx.gbl...
> Thank you Tracy,
> There are no triggers on any of the tables.
> I guess this was not an indication of anything wrong.
> By the way, I seem to remember that, depending on some settings, the log
will grow if needed, but
> I have been digging through the SQL Server Books Online without finding an
ything about this.
> I have seen a few cases in our company where someone has set up an SQL Ser
ver DB without setting
> up regular backups, where the log just kept growing. In one case the log u
sed up all the available
> space on the disk and effectively shut down the computer, not enough room
for the swap file.
> I think there is a way to control the growth of the log file, but I have n
ot found out how to do
> this.
> Any pointers would be appreciated.
> Ragnar
>

Problem with updating column in table - transaction log full

Hello,
I had trouble performing an update of one column of a table.
The column is of Bit data type and the table has 60350 rows.
When I run the following query in the Query Analyzer:
update [Specimen CV] set HPV = 0
I get a message "The log file for database DBNAME is full. Back up the
transaction log for the database to free up space"
I backed up both the DB and the log, truncated as well as increased the size
of the log file, first to 100MB.
I even set the DB to simple recovery mode. Still, I got the same message.
Finally, when I set the size to 1 GB did the update run.
After the update ran I checked the Log Space used and it was 12.6%
Does really a simple query like that on a table with only 60K records
require 126 MB of log space?
Any comment would be appreciated.
RagnarOn Mar 7, 7:54 pm, "Ragnar Midtskogen" <ragnar...@.newsgroups.com>
wrote:
> Hello,
> I had trouble performing an update of one column of a table.
> The column is of Bit data type and the table has 60350 rows.
> When I run the following query in the Query Analyzer:
> update [Specimen CV] set HPV = 0
> I get a message "The log file for database DBNAME is full. Back up the
> transaction log for the database to free up space"
> I backed up both the DB and the log, truncated as well as increased the size
> of the log file, first to 100MB.
> I even set the DB to simple recovery mode. Still, I got the same message.
> Finally, when I set the size to 1 GB did the update run.
> After the update ran I checked the Log Space used and it was 12.6%
> Does really a simple query like that on a table with only 60K records
> require 126 MB of log space?
> Any comment would be appreciated.
> Ragnar
Are there triggers on this table? Your single update statement, while
seemingly simple, is creating an implicit transaction, and any change
that the update produces, directly or indirectly, is captured within
that transaction. The log file, which stores the "undo" information
for all of these changes, must be large enough to hold everything that
the update statement is changing, within the table being updated, AND
any other tables that are being affected by triggers on that table.|||Thank you Tracy,
There are no triggers on any of the tables.
I guess this was not an indication of anything wrong.
By the way, I seem to remember that, depending on some settings, the log
will grow if needed, but I have been digging through the SQL Server Books
Online without finding anything about this.
I have seen a few cases in our company where someone has set up an SQL
Server DB without setting up regular backups, where the log just kept
growing. In one case the log used up all the available space on the disk and
effectively shut down the computer, not enough room for the swap file.
I think there is a way to control the growth of the log file, but I have not
found out how to do this.
Any pointers would be appreciated.
Ragnar|||How much space is used for the log is dictated by how much log records you generate and how often
you empty the log. To empty the log, you have basically two options:
Have the database in simple recovery mode. Now SQL Server will empty the log automatically (every
time a checkpoint occurs).
Have it in full recovery mode and do regular transaction log backups.
I suggest you read a bit about recovery models and backup to get more insight into this. Also, you
might want to check out (related reading):
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:e8F6%23RaYHHA.3824@.TK2MSFTNGP02.phx.gbl...
> Thank you Tracy,
> There are no triggers on any of the tables.
> I guess this was not an indication of anything wrong.
> By the way, I seem to remember that, depending on some settings, the log will grow if needed, but
> I have been digging through the SQL Server Books Online without finding anything about this.
> I have seen a few cases in our company where someone has set up an SQL Server DB without setting
> up regular backups, where the log just kept growing. In one case the log used up all the available
> space on the disk and effectively shut down the computer, not enough room for the swap file.
> I think there is a way to control the growth of the log file, but I have not found out how to do
> this.
> Any pointers would be appreciated.
> Ragnar
>