I have a project that uses SQL 2000 for the database. It is finished and I am looking for the best way to deploy it via CD.
I am mainly worried about getting the SQL database to install correctly automatically and have it work correctly with the web application.
Any suggestions?
Thanks,
Mikeare you shipping sql server with your application on a cd ?
I'm looking at creating a sample utility script that will invoking
scripts to deploy some SQL code. For example, a utlity script that will
run a SQL script, and on successful completion, execute the next script.
Having not used SQLCMD at all before, and being very new to SQL2005
(< 1 month) please guide me if there is a better way of invoking
this... For example, a way of avoiding the xp_cmdshell invocation!
The following code invokes a script, but I'm trying to find a way of
getting a return code back from sqlcmd, so I can progress and do the
next, or fail if the return code <> 0 (success).
[code]
--Process to create DB, Tables, and Stored Procedures
set nocount on
DECLARE
@.Error int,
@.ExecCommand varchar(512),
@.FullFilePath varchar(255)
--create the database
BEGIN TRY
SET @.FullFilePath =
'D:\Documentation\Projects\Integration Services\BIDS Projects\Tesco DNF
Integration Services\TescoDNF ProductPromo\SQL Code\OBJECTS\Create DB
TescoDNF_SSISPackageManager.sql'
SET @.ExecCommand = 'xp_cmdshell ''sqlcmd -S Rgalbraith\SQL2005_1 -i "'+@.FullFilePath+'"'' '
SELECT @.FullFilePath
UNION
SELECT @.ExecCommand
EXEC (@.ExecCommand)
SELECT @.@.ERROR
SELECT @.Error
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
GOTO ErrorAbort
END CATCH
ErrorAbort:
[/code]
Hello
I didn't quite understand what are you trying to achieve. You have some sequence of sql scripts that need to be executed against a server one after another, if no error occurs?
Then, what if an error occurs? Maybe there is some branching in the scripts? I.e. if script1.sql succeedes, then execute script2.sql, else execute script3.sql. If script3.sql fails, restore a database backup...
And why are you doing this from SQL? Isn't using a programming language more effective?
of SQL scripts to, for example, a server. For example, some pseudo-code
Create Database
If Error abort
Create Table1
If Error abort
Create Table2
If Error Abort
Create Stored Procedure1
If Error Abort
ELSE Complete and report success
I do agree that this is something that could be (better) done in a
"proper" coding language like .Net, c# etc. but (a) it's just a simple
utility script (b) it teaches me moore about usage of SQLCMD and (c) I
do not have any skill in any normal programming language, hence I was
planning to write a quick deployment utility with a script.
The idea might be something as ugly as a table structure that has a
list of scripts registered in it, with some sequence logic - like
creating parent tables before children tables - and then a cursor (or a
better method if I can find it) that fetches a sqlcmd filename
execution command, executes it, and on success fetches the next one
based on the sequence logic.
I can probably do all of that in about 4 hours in T-SQL, if I can find
a way to confirm the successful execution of the previous command....|||
Ok I got it.
You can have a "Version" table, then number your scripts so that each of them updates the version. Before executing each portion of code, you can check the current version to be exactly the number you need.
e.g.
Create Database
Create table Version(VersionNum varchar(255), ChangeID int)
insert into Version(VersionNum, ChanegeID) values("1", @.ID)
GO
If (select VersionNum where ChangeID=@.ID)="1"
BEGIN
Create Table1
Update Version set VersionNum = "2" where ChangeID=@.ID
END
GO
If (select VersionNum where ChangeID=@.ID)="2"
...
So, basicly you just update the version number as the last command of each batch. Then, you check for the appropriate version number at the beginning of the next block.
This way you can even do some "branching". Even more, if your scripts fails, you can check what scripts have succeeded and what scripts have not, simply by looking at the VersionNo field.
Anyhow, I'd strongly reccomend using ordinal programming language if you are going to use that utility more than once and it MIGHT become somehow complicated.
|||Well, in a sense. The point is though that I want to fetch sql files
and execute them, and not merge them all into a single large script.
So, I want utility script to do this:
Run external sql script
On failure abort, on Success
Run external sql script
On Failure abort, On Success
...
You being to see why I referred to a cursor?
The point is that the utility script wouldn't contain any of the client
SQL commands - it would fetch them by referring to the table, and
fetching the path to the SQL file, and building a SQLCMD to execute
that script
I guess, as you say, I could add a generic update ##SQLScriptTracker
table, then check it on the new execution, or abort. I had hoped for a
neater solution - i.e. SQLCMD being able to return a returncode that it
gets from a SQL file it ran....|||
Why dont go for Batch files (.bat). There you can execute the individual script files one by one using the SQLCMD. And for aborting when error occurs, check the ERRORLEVEL, if its not 0 then quit execution or skip to other location using GOTO.
echo Backup database
sqlcmd -S(local) -U<uid> -P<pwd> -i"backup_db.sql"
IF ERRORLEVEL 1 GOTO abort_bkp
echo Update database
sqlcmd -S(local) -U<uid> -P<pwd> -i"create_proc.sql"
IF ERRORLEVEL 1 GOTO abort
echo Update customer data
sqlcmd -S(local) -U<uid> -P<pwd> -i"update_customer_data.sql"
IF ERRORLEVEL 1 GOTO abort_with_restore
:abort_bkp
echo Error backup database. Setup aborted
:abort_with_restore
echo Error updating data. Restoring database...
sqlcmd -S(local) -U<uid> -P<pwd> -i"restore_db.sql"
IF ERRORLEVEL 1 GOTO res_falied
...
...
|||
hmm ... it seems as thought ERRORLEVEL is only set on the SUCCESS/FAILURE of the SQLCMD invocation, and not based on the SUCCESS/FAILURE of the invoked sql commands?
for example:
batch CALLBACKUP.BAT file contents:
echo Backup database
sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
IF ERRORLEVEL 1 GOTO abort_bkp
IF ERRORLEVEL 0 GOTO done
:abort_bkp
echo Error backup database. Setup aborted
:done
echo all done now
backup_db.sql contents
backup database DataStore2 to disk = 'D:\BackupDatabase.bak'
execution results:
D:\>sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
Msg 911, Level 16, State 11, Server RGALBRAITH\SQL2005_1, Line 1
Could not locate entry in sysdatabases for database 'DataStore2'. No entry found with that name. Make sure that the name
is entered correctly.
Msg 3013, Level 16, State 1, Server RGALBRAITH\SQL2005_1, Line 1
BACKUP DATABASE is terminating abnormally.
D:\>IF ERRORLEVEL 1 GOTO abort_bkp
D:\>IF ERRORLEVEL 0 GOTO done
D:\>echo all done now
all done now
A sample of sqlcmd failing was:
D:\>callbackup
D:\>echo Backup database
Backup database
D:\>sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
Sqlcmd: 'd:\backup_db.sql': Invalid filename.
D:\>IF ERRORLEVEL 1 GOTO abort_bkp
D:\>echo Error backup database. Setup aborted
Error backup database. Setup aborted
D:\>echo all done now
all done now
...
As is probably obvious, I'm not much of a batch file coder :-), but the jmist of it is there - when the SQLCMD failed (file not found) then it reported error, but when the SQL script failed (database not found) no error was reported. Is there a way around that?
|||You have to set the -b option for the SQLCMD. -b makes the batch abort with an error if the script fails. So you would write this...
@.ECHO OFF
@.echo.
@.echo Backup database
sqlcmd -S.\sqlexpress -E -i"backup_db.sql" -b
IF %ERRORLEVEL% NEQ 0 GOTO err_bkp_failed
:success
echo Database update successful
goto end
:err_bkp_failed
echo Backup failed. Aborting...
goto end
:end
HTH
|||hmm - good to know! still going to investiage the other options aswell, since with the batch file I have to add a file each time.
Thanks|||
Visual Studio .Net 2003 had a "create batch file" command which was beautiful for creating this batch file to process the sequence of sql scripts that you create.
I still use it today. But it seems we are in need to migrate to Visual Studio 2005, and this feature has been disabled now.
Do you have a more elegant solution now?
|||Actually you don't have to modifiy the bat script each time. I have been using bat scripts to do exactly this for years.
The shell support the For Each looping structure which will set a shell variable to each file name that meet's a spec.
For Each %%1 in *.sql <execute a dos command>
I have been using the OSQL command line utility for years like this. I guess I will have to update to SQLCMD now.
You can find out the details of shell commands by going to "My Computer" <Help> and searching for "For Each"
You can find out about OSQL in BOL
|||I've been searching solution on catching MS SQL abortion errors in a launching batch file. With option '-b', at least the batch file could return error code 1 instead of 0. Thanks for the hint!
Still, I'd appreciate if anyone could offer answer on capturing the stdout error in the batch file. My problem is that once the sql statement is aborted, it immediately exits from the erroneous line, ignores the rest code in the same script. Therefore, no error could be saved.
Also, I found that in some env. the 'sqlcmd' is not recognized (SQL Server 2000?) but 'osql' or 'isql'. Are there any differences among them (must be, but I don't know).
ErrorAbort:
[/code]
Hello
I didn't quite understand what are you trying to achieve. You have some sequence of sql scripts that need to be executed against a server one after another, if no error occurs?
Then, what if an error occurs? Maybe there is some branching in the scripts? I.e. if script1.sql succeedes, then execute script2.sql, else execute script3.sql. If script3.sql fails, restore a database backup...
And why are you doing this from SQL? Isn't using a programming language more effective?
Ok I got it.
You can have a "Version" table, then number your scripts so that each of them updates the version. Before executing each portion of code, you can check the current version to be exactly the number you need.
e.g.
Create Database
Create table Version(VersionNum varchar(255), ChangeID int)
insert into Version(VersionNum, ChanegeID) values("1", @.ID)
GO
If (select VersionNum where ChangeID=@.ID)="1"
BEGIN
Create Table1
Update Version set VersionNum = "2" where ChangeID=@.ID
END
GO
If (select VersionNum where ChangeID=@.ID)="2"
...
So, basicly you just update the version number as the last command of each batch. Then, you check for the appropriate version number at the beginning of the next block.
This way you can even do some "branching". Even more, if your scripts fails, you can check what scripts have succeeded and what scripts have not, simply by looking at the VersionNo field.
Anyhow, I'd strongly reccomend using ordinal programming language if you are going to use that utility more than once and it MIGHT become somehow complicated.
|||Well, in a sense. The point is though that I want to fetch sql files and execute them, and not merge them all into a single large script.
Why dont go for Batch files (.bat). There you can execute the individual script files one by one using the SQLCMD. And for aborting when error occurs, check the ERRORLEVEL, if its not 0 then quit execution or skip to other location using GOTO.
echo Backup database
sqlcmd -S(local) -U<uid> -P<pwd> -i"backup_db.sql"
IF ERRORLEVEL 1 GOTO abort_bkp
echo Update database
sqlcmd -S(local) -U<uid> -P<pwd> -i"create_proc.sql"
IF ERRORLEVEL 1 GOTO abort
echo Update customer data
sqlcmd -S(local) -U<uid> -P<pwd> -i"update_customer_data.sql"
IF ERRORLEVEL 1 GOTO abort_with_restore
:abort_bkp
echo Error backup database. Setup aborted
:abort_with_restore
echo Error updating data. Restoring database...
sqlcmd -S(local) -U<uid> -P<pwd> -i"restore_db.sql"
IF ERRORLEVEL 1 GOTO res_falied
...
...
|||
hmm ... it seems as thought ERRORLEVEL is only set on the SUCCESS/FAILURE of the SQLCMD invocation, and not based on the SUCCESS/FAILURE of the invoked sql commands?
for example:
batch CALLBACKUP.BAT file contents:
echo Backup database
sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
IF ERRORLEVEL 1 GOTO abort_bkp
IF ERRORLEVEL 0 GOTO done
:abort_bkp
echo Error backup database. Setup aborted
:done
echo all done now
backup_db.sql contents
backup database DataStore2 to disk = 'D:\BackupDatabase.bak'
execution results:
D:\>sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
Msg 911, Level 16, State 11, Server RGALBRAITH\SQL2005_1, Line 1
Could not locate entry in sysdatabases for database 'DataStore2'. No entry found with that name. Make sure that the name
is entered correctly.
Msg 3013, Level 16, State 1, Server RGALBRAITH\SQL2005_1, Line 1
BACKUP DATABASE is terminating abnormally.
D:\>IF ERRORLEVEL 1 GOTO abort_bkp
D:\>IF ERRORLEVEL 0 GOTO done
D:\>echo all done now
all done now
A sample of sqlcmd failing was:
D:\>callbackup
D:\>echo Backup database
Backup database
D:\>sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
Sqlcmd: 'd:\backup_db.sql': Invalid filename.
D:\>IF ERRORLEVEL 1 GOTO abort_bkp
D:\>echo Error backup database. Setup aborted
Error backup database. Setup aborted
D:\>echo all done now
all done now
...
As is probably obvious, I'm not much of a batch file coder :-), but the jmist of it is there - when the SQLCMD failed (file not found) then it reported error, but when the SQL script failed (database not found) no error was reported. Is there a way around that?
|||You have to set the -b option for the SQLCMD. -b makes the batch abort with an error if the script fails. So you would write this...
@.ECHO OFF
@.echo.
@.echo Backup database
sqlcmd -S.\sqlexpress -E -i"backup_db.sql" -b
IF %ERRORLEVEL% NEQ 0 GOTO err_bkp_failed
:success
echo Database update successful
goto end
:err_bkp_failed
echo Backup failed. Aborting...
goto end
:end
HTH
|||hmm - good to know! still going to investiage the other options as well, since with the batch file I have to add a file each time.Visual Studio .Net 2003 had a "create batch file" command which was beautiful for creating this batch file to process the sequence of sql scripts that you create.
I still use it today. But it seems we are in need to migrate to Visual Studio 2005, and this feature has been disabled now.
Do you have a more elegant solution now?
|||Actually you don't have to modifiy the bat script each time. I have been using bat scripts to do exactly this for years.
The shell support the For Each looping structure which will set a shell variable to each file name that meet's a spec.
For Each %%1 in *.sql <execute a dos command>
I have been using the OSQL command line utility for years like this. I guess I will have to update to SQLCMD now.
You can find out the details of shell commands by going to "My Computer" <Help> and searching for "For Each"
You can find out about OSQL in BOL
|||I've been searching solution on catching MS SQL abortion errors in a launching batch file. With option '-b', at least the batch file could return error code 1 instead of 0. Thanks for the hint!
Still, I'd appreciate if anyone could offer answer on capturing the stdout error in the batch file. My problem is that once the sql statement is aborted, it immediately exits from the erroneous line, ignores the rest code in the same script. Therefore, no error could be saved.
Also, I found that in some env. the 'sqlcmd' is not recognized (SQL Server 2000?) but 'osql' or 'isql'. Are there any differences among them (must be, but I don't know).
sqlHi,
I would like to be able to deploy a report to a particular folder in the report server. It seems when I deploy a report it always shows up in the root even after I have moved it into another folder where I wish it to reside.
Thanks,
DD
You set the deployment path of reports (and datasources in 2005) using the project property pages.
Right click on the project (in solution explorer) - select "properties", then if you expand "configuration properties" in the left hand pane of the dialog and select "general"
You can now see the "target report folder" (and if using 2005 "target data source folder")
you can amend this as follows
if you want the following folder
root/adminReports/server
just put
"adminReports/server" in the "target report folder" textbox
|||Right, but that changes the deployment folder for the whole project. I don't seem to be able to change the reports individually. What would be sweet would be if you add folders to your solution and the deployment process puts the reports in the corresponding folders.|||You have these options:
1) Create 1 solution per folder group that you require, set them to point at the folders you requires
2) Use configuration manager to create different deployment folders, deploy the reports individually once you've picked your target folder using config mgr
3) Use a tool like Reporting Services scripter to generate a deployment script, then edit this (and the winnt folder structure) accordingly - "find in folders" using vs.net is easy enough [http://www.sqldbatips.com/]
|||Thanks for the valuable tips. I will check out that Linked Report tool.
I ended up creating a project for every folder.
Thanks,
DD
"An exception 'System.ArgumentException' has occured in <Myapp>".
I do the run time debugging and it tells me: "Keyword not supported:
'dsn'.
For those who have migrated to .Net, how do you handle this issue? I
mean, we are going to distribute this application, and our users will
have different names for their servers, so how do we specify a data
source dynamically.
The way we have it for the moment is that we read the information from
a config file. But it seems like there must be a better way. What
happened to dsn?
Any help on this would greatly appreciated.
Thanks,
Ed Hawkes
olaamigoquepasa@.nospamplease-hotmail.comEd Hawkes (olaamigoquepasa@.hotmail.com) writes:
> When we deploy applications, we have been using DSN's set up on the
> users systems. Then in the sqlconnection string, we go "dsn = xyz". I
> tried this with our new application, which is the first to be done
> with ADO.net and I get the following run time problem:
> "An exception 'System.ArgumentException' has occured in <Myapp>".
> I do the run time debugging and it tells me: "Keyword not supported:
> 'dsn'.
> For those who have migrated to .Net, how do you handle this issue? I
> mean, we are going to distribute this application, and our users will
> have different names for their servers, so how do we specify a data
> source dynamically.
> The way we have it for the moment is that we read the information from
> a config file. But it seems like there must be a better way. What
> happened to dsn?
If you despearately need DSN, I would suppose you could use OleDb
client rather than SqlClient.
As for what happened, I guess DSN got of fashion, and I can't say that
I miss it. Our application - which is VB6 - once used DSN, but now you
can specify server and database on the login form (and the values are
saved in registry between invocations), which I very much like. Of course,
end users normally use the same server and database, and it's only
developers like me, who change all the time. But nevertheless, DSNs
is just one more thing that has to be configured, and which could buy
settings you did not expect.
IF you don't want to prompt your users, a config file seems like a
good way to go.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns952AF310B27FBYazorman@.127.0.0.1>...
> Ed Hawkes (olaamigoquepasa@.hotmail.com) writes:
> > When we deploy applications, we have been using DSN's set up on the
> > users systems. Then in the sqlconnection string, we go "dsn = xyz". I
> > tried this with our new application, which is the first to be done
> > with ADO.net and I get the following run time problem:
> > "An exception 'System.ArgumentException' has occured in <Myapp>".
> > I do the run time debugging and it tells me: "Keyword not supported:
> > 'dsn'.
> > For those who have migrated to .Net, how do you handle this issue? I
> > mean, we are going to distribute this application, and our users will
> > have different names for their servers, so how do we specify a data
> > source dynamically.
> > The way we have it for the moment is that we read the information from
> > a config file. But it seems like there must be a better way. What
> > happened to dsn?
> If you despearately need DSN, I would suppose you could use OleDb
> client rather than SqlClient.
> As for what happened, I guess DSN got of fashion, and I can't say that
> I miss it. Our application - which is VB6 - once used DSN, but now you
> can specify server and database on the login form (and the values are
> saved in registry between invocations), which I very much like. Of course,
> end users normally use the same server and database, and it's only
> developers like me, who change all the time. But nevertheless, DSNs
> is just one more thing that has to be configured, and which could buy
> settings you did not expect.
> IF you don't want to prompt your users, a config file seems like a
> good way to go.
Erland,
much obliged! I really appreciate the response, and this will help
move our project along. Thanks again.
Cheers,
Ed Hawkessql
Hi all,
I have a problem while trying to deploy my packages that are configured in ProtectionLevel=EncryptSensitiveWithUserPassword
I use the Deployment Utility to deploy my packages. I set the password when deploying and the deplyment works fine. But I run the packages I have the following error : " Failed to decrypt an encrypted XML node because the password was not specified or not correct."
Did I miss something ?
Why not use protectionlevel = 'DontSaveSensitive"?|||Because I'm deploying to a Test Environment and the Data on the Test Env are different so sometimes I would like to debug on the Test Env which means I load the package in BIDS.
What's wrong about using EncryptSensitiveWithUserPassword ?
|||Sbastien Nunes wrote:
Because I'm deploying to a Test Environment and the Data on the Test Env are different so sometimes I would like to debug on the Test Env which means I load the package in BIDS.
What's wrong about using EncryptSensitiveWithUserPassword ?
Are you providing the password when executing the package? you have to provide password each time you execute the package. See Setting the Protection Level of Packages
I don't think there is nothing wrong with using EncryptSensitiveWithUserPassword; it's just that it adds and extra step to the deployment process making it more prone to error. If you use a combination of DonSaveSensitive as protection level with package configurations things get simpler; and you still will be able to debug o any environment.
I am suggesting the DonSaveSensite option; because that is what I use and I know it works I have never used EncryptSensitiveWithUserPassword.
|||
Well yeah you can still debug but you have to open each package to set the sensitive data back as they were removed when saved with ProtectionLevel=DontSaveSensitive
Indeed I'm not providing the password when the package is called for execution. I thought I had to give the password only when I open the package not when I run it. I'll fix that. Thanks for your help.
It's not very clear in BOL! They are talking about providing a password each time the package is loaded in BIDS but when talking about running the package it's not clear if they are still talking about executing the package within BIDS or outside.
sqlI just started using reporting services and have a set of reports done that works on VS2005, however I am struggling with how to deploy this to a production server. The production server does not have a public presnet on the web. If I copy the directory to the production machine it does not work. I realzie that somehow I need to update the report database on the production machine but am lost about how to do that if I cannot connect to the machine from VS2005. I also tried the upload function in report manager.
How do I get my reports onto this box? Surely there is a way to do this without having to have a connection from VS2005. If you do applications that you want to ship to remote users there must be a way to do this.
Thanks
Chuck
In an effort to see how this works I took the reports database from my devleopment machine and installed it onto the server using SQL backup / restore. Does this break the reports database? Do I have to uninstall the reporting services using the SQL install tool or is there a way to recreate the database without doing the SQL uninstall reinstall?
Thanks
Chuck
I have included the eight dlls required by the Sql Server 2005 CE engine (per the documentation) in the deployment project.
Any help would be greatly appreciated.
(I presume that you are deploying on a desktop.)
The recommended method is to install the runtime package (through click once or otherwise). No manual intervention is needed. The data provider (i.e. System.Data.SqlServerCe.dll) is put in GAC. It is easy to update the package too.
If you do not want to use the installer for some reason, then ensure that all SSC binaries are in the executable folder.
Here is the error message:
Another question, would local host work for server id?
Server Error in '/' Application.
------------------------SQL Server does not exist or access denied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.Exception Details: System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.
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: SQL Server does not exist or access denied.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +474
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) +44
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +36
ICCSalesTracker.WebForm1.GetRGU() +433
ICCSalesTracker.WebForm1.Page_Load(Object sender, EventArgs e) +7
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +750
Also, I took a look at PDB file and it still has my other file paths. Does that matter? Should they be changed?
And lastly, The web server admin does not want to use the inetpub folder so he created a different folder that houses all of the files. What will that effect? What do I have to change?
Please let me know. Thanks!!!!|||Hi,
It sounds like the app worked well on your laptop, but wont work when moved to a server. You need to understand the authentication and authorization scheme the app will use to access the DB. That situation may be different from the one you had on your laptop. You can use Windows Integrated security, if IIS and the SQL Server are on the same computer and the user is in the same domain. For an internet app, the client may not have credentials to pass, or passing them posses a risk, so ASP.NET uses a default user for it's worker process called ASPNET (it's called NETWORK SERVICE by Windows 2003 Server). SQL Server sees this default user and checks to see if it has permission to use that DB. In order to gain access to the DB, the ASPNET (or NETWORK SERVICE), user needs to be registered on the SQL Server and given the appropriate (just enough), permissions to use that DB. There are other factors and schemes, but you might just look to see that the SQL Server DB has the right user set up correctly.
Good luck.|||I added ASPNET & NT AUTHORITY and even my domain user is set to owner. It still will not pull it up.|||Hi,
Was the user in the form "NT_AUTHORITY\NETWORK SERVICE"? I know that worked for me one time on a local setup that didn't accept "thecomputername\NETWORK SERVICE". Also, are you sure that these default users have the correct read/write permissions set?
Some more that know more than I do will probably get you the right answer. If I had to figure it out myself, I might try using a different type of Authentication (Forms), just to see if I could get it working - then figure out how to get the authentication scheme I want working too. You might try writting a very simple data access app (using the Data Web Form wizard), just to test the connection. That way you don't have to messup your project.
Good luck.|||Thank you for your help Brian!
Well I figured out the server name. Apparently, if the server say local when you look at it in Enterprise Manager, you leave the sql servername blank in the connection string. "SERVER/"
Now I"m getting a login failed for user 'NT AUTHORITY\NETWORK SERVICE error even though IIS is set to windows integrated authentication. Is there another place I have to change a setting to ensure it only uses windows authentication?|||Hi,
Glad to hear that you are making some progress - even if my info wasn't much help.
Is the login error message from SQL Server?
In your message it says ...for user 'NT AUTHORITY\NETW...', but I think it should have an underscore between NT and AUTHORITY -- 'NT_AUTHORITY\NETWORK SERVICE'
It seemed odd to me that they connect the first two part name, and leave a blank in the second, but that's the way I saw it on my system. Double check.
Good luck.|||You are correct. I typed it in wrong. I had to add that user to the SQL users directory and now it loads. But now I have yet another problem. The initial page loads. It has an onload function that calls a stored procedure to return the number of units sold for the day. That works just fine. The second part of the page is the order entry function that passes parameters to an insert procedure. When I click on submit, I get an access denied or server not found error. It's wierd because it's using the same connection string as the on load procedure. Do you have any idea what that might be? Thanks for the help!|||Hi,
Looks like you are making progress.
I'm not sure about why the second part of the page wont function. My first guess is that the first part is only reading data, and it sounds like the second part is reading and writing. Are you sure that the user permissions include insert and update allowed?
You need to be very careful with any database access granted to internet users. That's even more the case when you grant write or execute permissions. Keep in mind that it's one thing to get it working, but you'll want to make sure you have the security issues understood and accounted for before you deploy the application. I'm still working my way through this myself. Using stored procedures for the functions that your users will perform is generally a good idea, but they're not a security panacea. You'll have to do some research to find the best safegaurds. I know that MSDN has some series on security best practices, webcasts, etc...
One other thing occurs to me, if you don't have a compelling reason to have both the functions you mentioned on the same aspx page, consider moving the second to another page, with a link from the first. My thought is that it compartmentalizes the functions, and it could make it easier to track down problems, and keep the first function available, even if the other is broken. You'll know if it makes sense to do this in your project.
Let me know how it goes for you.|||All of the required users have permissions to all of the objects in the database. This is an intranet and the security is set to windows integrated. The reason the page has both the on load and on submit function is to track entered units in real time. As they enter in sales, the datagrid tied to the getunit stored procedure runs every time the page loads. So when the sales agent enters in 2 revenue units, datagrid reflects those units when the page reloads. That portion works just fine. However when I click submit, I get the following error
Server Error in '/' Application.
------------------------SQL Server does not exist or access denied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.Exception Details: System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.
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: SQL Server does not exist or access denied.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +474
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
ICCSalesTracker.WebForm1.btnSubmit_Click(Object sender, EventArgs e) +7239
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1292
I'm pretty sure it's not the connection string as that is specified in the web.config file. I'm thinking it has something to do with IIS or something. Thanks for the help! I'll let you know if I figure it out...
Hello all,
I am considering the different options for package deployment on the server.
Until now, I have found several different ways to deploy packages to the server (File System):
Does anyone have any other suggestions for deployment?
The 4th seems to be the easiest one, but I seen anybody suggesting such an action. What's the downside of such an action?
Thanks,
Liran
The 4th option IS easiest but you do have the potential for human error. You could easily write a batch file to do the same thing however and just amend that as necassary.
-Jamie
Hi,
How do we deploy SSRS Reports to various server. One option which we
are using currently is deploying from the report project. We want to
automate the Reports Deployment. Can any of you who have done this in
the past share the experience?
Thanks,
S Suresh
YOu can use the APIS of the reporting Services. Deploying is quite straight forward with them, see the link here for an example http://groups.google.de/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/d265ba0a4f82369e
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
