Cannot edit jobs

i am get this error message every time I try to remove some old jobs that
were created on a different server:
error 14274: cannot add, update,or delete a job (or its step or
schedules) that originated from an msx server
What do I need to do to remove these jobs?
Either remove them from the master server. Or hack msdb..sysjobs (originating_server column to the
name for your server). Some info found here (adapt to your needs):
http://www.karaszi.com/SQLServer/inf...erver_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Takia" <Takia@.discussions.microsoft.com> wrote in message
news:527F2E42-BDF8-4968-B717-4BDA27347FDB@.microsoft.com...
>i am get this error message every time I try to remove some old jobs that
> were created on a different server:
> error 14274: cannot add, update,or delete a job (or its step or
> schedules) that originated from an msx server
> What do I need to do to remove these jobs?
>

Cannot edit jobs

i am get this error message every time I try to remove some old jobs that
were created on a different server:
error 14274: cannot add, update,or delete a job (or its step or
schedules) that originated from an msx server
What do I need to do to remove these jobs?Either remove them from the master server. Or hack msdb..sysjobs (originatin
g_server column to the
name for your server). Some info found here (adapt to your needs):
http://www.karaszi.com/SQLServer/in...server_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Takia" <Takia@.discussions.microsoft.com> wrote in message
news:527F2E42-BDF8-4968-B717-4BDA27347FDB@.microsoft.com...
>i am get this error message every time I try to remove some old jobs that
> were created on a different server:
> error 14274: cannot add, update,or delete a job (or its step or
> schedules) that originated from an msx server
> What do I need to do to remove these jobs?
>

Cannot edit jobs

i am get this error message every time I try to remove some old jobs that
were created on a different server:
error 14274: cannot add, update,or delete a job (or its step or
schedules) that originated from an msx server
What do I need to do to remove these jobs?Either remove them from the master server. Or hack msdb..sysjobs (originating_server column to the
name for your server). Some info found here (adapt to your needs):
http://www.karaszi.com/SQLServer/info_change_server_name.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Takia" <Takia@.discussions.microsoft.com> wrote in message
news:527F2E42-BDF8-4968-B717-4BDA27347FDB@.microsoft.com...
>i am get this error message every time I try to remove some old jobs that
> were created on a different server:
> error 14274: cannot add, update,or delete a job (or its step or
> schedules) that originated from an msx server
> What do I need to do to remove these jobs?
>

Cannot divide

I'm having a real hard time with a division in my table. It is in a field in a group that I have no problem getting the correct result by these expressions:
=SUM(Fields!NrOfOptysTotal.Value + Fields!NrOfActivities.Value) or
=SUM(Fields!NrOfOptysTotal.Value - Fields!NrOfActivities.Value)
But if I do this:
=SUM(Fields!NrOfOptysTotal.Value / Fields!NrOfActivities.Value) or
=SUM(Fields!NrOfOptysTotal.Value * Fields!NrOfActivities.Value)
,I don't get the correct results, I only get Infinity or NaN, but I shouldn't. I have tried manipulating the expressions like
=SUM(Fields!NrOfOptysTotal.Value) / SUM(Fields!NrOfActivities.Value) and several other combinations, but it never works. I really don't understand the problem! Please help...Somehow I got it to work now with
=SUM(Fields!NrOfOptysTotal.Value) / SUM(Fields!NrOfActivities.Value)
I'm SURE I tried that before, I don't get it, but whatever...
"FredrikT" wrote:
> I'm having a real hard time with a division in my table. It is in a field in a group that I have no problem getting the correct result by these expressions:
> =SUM(Fields!NrOfOptysTotal.Value + Fields!NrOfActivities.Value) or
> =SUM(Fields!NrOfOptysTotal.Value - Fields!NrOfActivities.Value)
> But if I do this:
> =SUM(Fields!NrOfOptysTotal.Value / Fields!NrOfActivities.Value) or
> =SUM(Fields!NrOfOptysTotal.Value * Fields!NrOfActivities.Value)
> ,I don't get the correct results, I only get Infinity or NaN, but I shouldn't. I have tried manipulating the expressions like
> =SUM(Fields!NrOfOptysTotal.Value) / SUM(Fields!NrOfActivities.Value) and several other combinations, but it never works. I really don't understand the problem! Please help...
>

Cannot display/return SQL Query Output from a Variable in DTS

I have been having a very difficult time trying to get the output of a
sql query that is in a dts global variable to return either into a
msgbox or for populating a portion of the body of a mail task.
I created an Execute SQL task with the query I wish to use. The query
has been tested in Query Analyzer and works fine and returns the results
I am looking for. Four rows are returned. Basically, I wish to put these
results in the body of a mail task or right now I would settle for a
msgbox just to see it work.
I have created the Execute SQL task as described in
http://msdn.microsoft.com/library/e...s_task_4gkl.asp .
I have tried to retrieve the results using the example in
http://msdn.microsoft.com/library/d...y/en-us/howtosq
l/ht_dts_task_6llt.asp but I have been unable to do so.
I also tried the GetString method of the recordset without success.
I also tried the using the Storing the resultset in a flat file
example from dotnetbips.com/displayarticle.aspx?id=228 and I was unable
to write the recordset to a file.
I feel like I am going about this all wrong and the answer is just
staring me in the face and I dont get it.
I welcome suggestions and comments on how to achieve the goal of putting
the query results into the email body.
Thanks.
*** Sent via Developersdex http://www.examnotes.net ***Hi
You may want to use call xp_sendmail directly (see Books online) rather than
the DTS Send Mail task. If not this may help:
http://www.sqldts.com/default.aspx?235
John
"SJM" <nospam@.devdex.com> wrote in message
news:O8fvDgusFHA.596@.TK2MSFTNGP12.phx.gbl...
>I have been having a very difficult time trying to get the output of a
> sql query that is in a dts global variable to return either into a
> msgbox or for populating a portion of the body of a mail task.
> I created an Execute SQL task with the query I wish to use. The query
> has been tested in Query Analyzer and works fine and returns the results
> I am looking for. Four rows are returned. Basically, I wish to put these
> results in the body of a mail task or right now I would settle for a
> msgbox just to see it work.
> I have created the Execute SQL task as described in
> http://msdn.microsoft.com/library/e...s_task_4gkl.asp .
> I have tried to retrieve the results using the example in
> http://msdn.microsoft.com/library/d...y/en-us/howtosq
> l/ht_dts_task_6llt.asp but I have been unable to do so.
> I also tried the GetString method of the recordset without success.
> I also tried the using the "Storing the resultset in a flat file"
> example from dotnetbips.com/displayarticle.aspx?id=228 and I was unable
> to write the recordset to a file.
> I feel like I am going about this all wrong and the answer is just
> staring me in the face and I don't get it.
> I welcome suggestions and comments on how to achieve the goal of putting
> the query results into the email body.
> Thanks.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Indeed, I am considering sending the mail from within the script, but
right now I am concentrating on getting the data and other info into the
message body. Thanks for the suggestion.
*** Sent via Developersdex http://www.examnotes.net ***sql

Cannot display/return SQL Query Output from a Dispatch Variable

I have been having a very difficult time trying to get the output of a
sql query that is in a DTS global variable to return, presently to a
messagebox.
I created an Execute SQL task with the query I wish to use. The query
has been tested in Query Analyzer and works fine and returns the results
I am looking for. I set a global variable to the result set. Basically,
I wish to display the results as a string or similar in a messagebox.
I have created the Execute SQL task as described in
http://msdn.microsoft.com/library/e...s_task_4gkl.asp .
I have tried to retrieve the results using the example in
http://msdn.microsoft.com/library/d...y/en-us/howtosq
l/ht_dts_task_6llt.asp but I have been unable to do so.
I also tried the GetString method of the recordset without success.
I also tried the using the Storing the resultset in a flat file
example from http://dotnetbips.com/displayarticle.aspx?id=228 and I was
unable to write the recordset to a file.
What is the proper way to display a variable of type dispatch? I feel
like I am going about this the wrong way.
I welcome suggestions and comments on how to achieve the goal of
displaying the query results.
Thanks.
*** Sent via Developersdex http://www.examnotes.net ***Hi
You don't seem to have:
http://msdn.microsoft.com/library/d...>
ask_4gkl.asp
listed.
John
"SJM" <nospam@.devdex.com> wrote in message
news:u6hU08ItFHA.304@.TK2MSFTNGP11.phx.gbl...
>
> I have been having a very difficult time trying to get the output of a
> sql query that is in a DTS global variable to return, presently to a
> messagebox.
> I created an Execute SQL task with the query I wish to use. The query
> has been tested in Query Analyzer and works fine and returns the results
> I am looking for. I set a global variable to the result set. Basically,
> I wish to display the results as a string or similar in a messagebox.
> I have created the Execute SQL task as described in
> http://msdn.microsoft.com/library/e...s_task_4gkl.asp .
> I have tried to retrieve the results using the example in
> http://msdn.microsoft.com/library/d...y/en-us/howtosq
> l/ht_dts_task_6llt.asp but I have been unable to do so.
> I also tried the GetString method of the recordset without success.
> I also tried the using the "Storing the resultset in a flat file"
> example from http://dotnetbips.com/displayarticle.aspx?id=228 and I was
> unable to write the recordset to a file.
> What is the proper way to display a variable of type dispatch? I feel
> like I am going about this the wrong way.
> I welcome suggestions and comments on how to achieve the goal of
> displaying the query results.
> Thanks.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||
Indeed, that MSDN is what I followed to get the data into the variable,
getting it out is the problem.
The workaround I am using is to execute the SQL statements in the
VBscript and not use the execute SQL DTS task. For example:
Option Explicit
Function Main()
dim cnn
dim rs
dim intLoop
set cnn=createobject("ADODB.Connection")
cnn.Open "Provider=sqloledb;" & _
"Data Source=SERVER;" & _
"Initial Catalog=msdb;" & _
"Integrated Security=SSPI"
set rs=cnn.execute("SQL STATEMENT HERE")
if rs.eof and rs.bof then
msgbox "Error: No records"
else
do until rs.EOF
For intLoop = 0 To rs.Fields.Count - 1
msgbox " " & rs.Fields(intLoop).Name
Next
set rs = rs.Next
loop
end if
Main = DTSTaskExecResult_Success
End Function
*** Sent via Developersdex http://www.examnotes.net ***|||
Everything is fine with the code listed below. I am going to use this
instead of the DTS execute sql task.
Option Explicit
Function Main()
dim cnn
dim rs
dim intLoop
dim strText
set cnn=createobject("ADODB.Connection")
cnn.Open "Provider=sqloledb;" & _
"Data Source=SERVER;" & _
"Initial Catalog=msdb;" & _
"Integrated Security=SSPI"
set rs=cnn.execute("SQL QUERY")
If rs.BOF then
msgbox "No Records Found"
else
do until rs.EOF
For intLoop = 0 To rs.Fields.Count - 1
strText = strText & rs.fields(intLoop).value & " " & vbCrLf
Next
rs.MoveNext
loop
msgbox strText
end if
set rs = nothing
Main = DTSTaskExecResult_Success
End Function
*** Sent via Developersdex http://www.examnotes.net ***|||Hi
It was the wrong link, should have sync'd
http://msdn.microsoft.com/library/d...asp?frame=true
I have just followed
http://msdn.microsoft.com/library/d...>
ask_4gkl.asp
and the above and there have been no problems.
John
"SJM" <nospam@.devdex.com> wrote in message
news:euD9JTUtFHA.2624@.TK2MSFTNGP12.phx.gbl...
>
> Everything is fine with the code listed below. I am going to use this
> instead of the DTS execute sql task.
> Option Explicit
> Function Main()
> dim cnn
> dim rs
> dim intLoop
> dim strText
> set cnn=createobject("ADODB.Connection")
> cnn.Open "Provider=sqloledb;" & _
> "Data Source=SERVER;" & _
> "Initial Catalog=msdb;" & _
> "Integrated Security=SSPI"
> set rs=cnn.execute("SQL QUERY")
> If rs.BOF then
> msgbox "No Records Found"
> else
> do until rs.EOF
> For intLoop = 0 To rs.Fields.Count - 1
> strText = strText & rs.fields(intLoop).value & " " & vbCrLf
> Next
> rs.MoveNext
> loop
> msgbox strText
> end if
> set rs = nothing
> Main = DTSTaskExecResult_Success
> End Function
>
> *** Sent via Developersdex http://www.examnotes.net ***

Cannot detach suspect db

A db is flagged as suspect. I tried to detach it or take
offline but was unable to as the db is replicated (merge
replication). At the same time I was unable to cancel the
replication because the db wasn't found.
The suspect status was caused by the db failing to close
cleanly after a failed back-up. I also haven't been able
to succesfully run sp_resetstatus.
Any ideas onhow to break out of this catch 22 situation?
RegardsHere's an old post i found that explains how to fix a suspect db :-
http://groups.google.co.uk/groups?q=sql+2000+suspect+fix&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=dcde2a5a.0309151952.424b43cb%40posting.google.com&rnum=1
--
HTH
Ryan Waight, MCDBA, MCSE
<japan11@.hotmail.com> wrote in message
news:074901c39147$f819ece0$a301280a@.phx.gbl...
> A db is flagged as suspect. I tried to detach it or take
> offline but was unable to as the db is replicated (merge
> replication). At the same time I was unable to cancel the
> replication because the db wasn't found.
> The suspect status was caused by the db failing to close
> cleanly after a failed back-up. I also haven't been able
> to succesfully run sp_resetstatus.
> Any ideas onhow to break out of this catch 22 situation?
> Regards

Cannot create variable of type table

Hi all,
Just heard of table-type variables and wanted to try one out in a SP
(MS-SQL Server 7.0).
However, every time I try to declare such a variable as, (for instance)
:
declare @.mytbl table(pos_num smallint, seq_num smallint)
I get an error :
Error 156: Incorrect syntax near the word 'table' .
Any ideas as to what the problem here might be (I have full admin
rights on this particular database)?
TIA, RobertSQL Server 7.0 doesnt know about table variables:
http://developer.com/db/article.php/3414331
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"robertino" <rbanfield@.telair.com> schrieb im Newsbeitrag
news:1117443939.893947.77470@.g44g2000cwa.googlegroups.com...
> Hi all,
> Just heard of table-type variables and wanted to try one out in a SP
> (MS-SQL Server 7.0).
> However, every time I try to declare such a variable as, (for instance)
> :
> declare @.mytbl table(pos_num smallint, seq_num smallint)
> I get an error :
> Error 156: Incorrect syntax near the word 'table' .
> Any ideas as to what the problem here might be (I have full admin
> rights on this particular database)?
> TIA, Robert
>|||Table variables are not supported in7.0. They are available in SQL Server
2000.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"robertino" <rbanfield@.telair.com> wrote in message
news:1117443939.893947.77470@.g44g2000cwa.googlegroups.com...
Hi all,
Just heard of table-type variables and wanted to try one out in a SP
(MS-SQL Server 7.0).
However, every time I try to declare such a variable as, (for instance)
:
declare @.mytbl table(pos_num smallint, seq_num smallint)
I get an error :
Error 156: Incorrect syntax near the word 'table' .
Any ideas as to what the problem here might be (I have full admin
rights on this particular database)?
TIA, Robert|||Hmmm, just as I suspected - we have here SQL Server 2000 clients
running as frontends to a SQL Server 7 DB. Phooey! (Been on holiday, so
haven't been able to reply until now.)
Robert
Tom Moreau wrote:
> Table variables are not supported in7.0. They are available in SQL Server
> 2000.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "robertino" <rbanfield@.telair.com> wrote in message
> news:1117443939.893947.77470@.g44g2000cwa.googlegroups.com...
> Hi all,
> Just heard of table-type variables and wanted to try one out in a SP
> (MS-SQL Server 7.0).
> However, every time I try to declare such a variable as, (for instance)
> :
> declare @.mytbl table(pos_num smallint, seq_num smallint)
> I get an error :
> Error 156: Incorrect syntax near the word 'table' .
> Any ideas as to what the problem here might be (I have full admin
> rights on this particular database)?
> TIA, Robert

Cannot Create private Time Dimension

I have a fact table with 32.000.000 rows. In cube editor , I want to create
a
private time dimension based on a datetime type column of my fact table.
after I press next button on "select advanced options" window, several
minutes passes and then a "finish" button appears in the same window
instead of next button. when I pressed finish, I got pop up message "the nam
e
cannot be an empty string" but I have no place to write the name of the
dimension.I typically discourage creating a time dimension like this. I strongly
recommend that you create an independent dimension table which incorporates
"Time". Then use the timestamps from your fact table as FKs to this
dimension. This is normally the best approach because it:
1) allows you to model data which does not exist, e.g. holidays, weekends,
etc, which are not represented in your fact table.
2) dimension processing goes much faster (i.e. you don't have to scan
multiple rows); this is particularly true here because the system will issue
a SELECT DISTINCT to process the private time dimension.
3) private dimensions must always be reprocessed when you process the fact
table -- which can greatly increase your overhead (see #2 above)
4) you will always be limited to a single partition because you can't build
across multiple fact tables (which can limit your scalability, query
response time, rolling "n" months -- all of which use multiple partitions).
5) if you create an independent time dimension table, you can have
interesting extensions to just a timestamp, e.g. model holidays, seasons,
work-days compared to weekends, etc.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Banu_Tr" <Banu_Tr@.discussions.microsoft.com> wrote in message
news:ABBE65C4-C6C8-4DF5-AEB8-2DFBE6489608@.microsoft.com...
> I have a fact table with 32.000.000 rows. In cube editor , I want to
create a
> private time dimension based on a datetime type column of my fact table.
> after I press next button on "select advanced options" window, several
> minutes passes and then a "finish" button appears in the same window
> instead of next button. when I pressed finish, I got pop up message "the
name
> cannot be an empty string" but I have no place to write the name of the
> dimension.

Cannot Create private Time Dimension

I have a fact table with 32.000.000 rows. In cube editor , I want to create a
private time dimension based on a datetime type column of my fact table.
after I press next button on "select advanced options" window, several
minutes passes and then a "finish" button appears in the same window
instead of next button. when I pressed finish, I got pop up message "the name
cannot be an empty string" but I have no place to write the name of the
dimension.
I typically discourage creating a time dimension like this. I strongly
recommend that you create an independent dimension table which incorporates
"Time". Then use the timestamps from your fact table as FKs to this
dimension. This is normally the best approach because it:
1) allows you to model data which does not exist, e.g. holidays, weekends,
etc, which are not represented in your fact table.
2) dimension processing goes much faster (i.e. you don't have to scan
multiple rows); this is particularly true here because the system will issue
a SELECT DISTINCT to process the private time dimension.
3) private dimensions must always be reprocessed when you process the fact
table -- which can greatly increase your overhead (see #2 above)
4) you will always be limited to a single partition because you can't build
across multiple fact tables (which can limit your scalability, query
response time, rolling "n" months -- all of which use multiple partitions).
5) if you create an independent time dimension table, you can have
interesting extensions to just a timestamp, e.g. model holidays, seasons,
work-days compared to weekends, etc.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Banu_Tr" <Banu_Tr@.discussions.microsoft.com> wrote in message
news:ABBE65C4-C6C8-4DF5-AEB8-2DFBE6489608@.microsoft.com...
> I have a fact table with 32.000.000 rows. In cube editor , I want to
create a
> private time dimension based on a datetime type column of my fact table.
> after I press next button on "select advanced options" window, several
> minutes passes and then a "finish" button appears in the same window
> instead of next button. when I pressed finish, I got pop up message "the
name
> cannot be an empty string" but I have no place to write the name of the
> dimension.
sql

Cannot create diagrams in SQLSERVER 2005

Hi,
Every time I click on the Database Diagram node in object explorer in SQL
Server Management Studio, I get a message:
Database diagram support objects cannot be installed because this database
does not have a valid owner. ...
I have checked the owner and it coincides with my logon name in my domain
(i.e. DENTDEVELOPMENT\JuanDent). What could be going on? Could I have a
problem with Active Directory? Why am I not being recognized as a valid db
owner?
Thanks in advance,
Juan Dent, M.Sc.
Make sure the compatibility level of the databases is set to
90 - that's often what causes the problem.
You can set it to 90 using:
EXEC sp_dbcmptlevel 'database name', '90'
-Sue
On Mon, 23 Jan 2006 17:04:01 -0800, Juan Dent
<Juan_Dent@.nospam.nospam> wrote:

>Hi,
>Every time I click on the Database Diagram node in object explorer in SQL
>Server Management Studio, I get a message:
> Database diagram support objects cannot be installed because this database
>does not have a valid owner. ...
>I have checked the owner and it coincides with my logon name in my domain
>(i.e. DENTDEVELOPMENT\JuanDent). What could be going on? Could I have a
>problem with Active Directory? Why am I not being recognized as a valid db
>owner?
|||Juan Dent (Juan_Dent@.nospam.nospam) writes:
> Every time I click on the Database Diagram node in object explorer in SQL
> Server Management Studio, I get a message:
> Database diagram support objects cannot be installed because this
> database does not have a valid owner. ...
> I have checked the owner and it coincides with my logon name in my domain
> (i.e. DENTDEVELOPMENT\JuanDent). What could be going on? Could I have a
> problem with Active Directory? Why am I not being recognized as a valid db
> owner?
This typically happens when you migrate a database from another server.
The problem can be seen by SELECT * FROM sys.database_principals. Compare
the row for dbo with a database created on the server.
I think changing the ownership to some other login, and back to yourself
fixes the issues.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||This site will help. There are two quick steps.
http://mcfunley.com/cs/blogs/dan/arc...12/23/899.aspx

************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

Cannot connect to sql database using Config app tool.

hi AllI'm new comer , the first time to work with Ent lib , I try to use modify sql connection string using configuration application tool integrated in Visual Studio against sql server 200 , I got this error .

_http://imagesshared.com/images/jdv1192388741v.JPG

I don't know how to solve my problem , someone please help me to overcome this problem .Thanks in advance .sorry for my poor English , I'm a Vietnamese student .

The problem is that you're trying to connect to a Sql server express user instance, but you're in fact using Sql Server 2000. You can modify your connection string to work with sql server 2000, or you can download and install sql server express - this is a free download from here:http://msdn2.microsoft.com/en-us/sql/Aa336346.aspx .

|||

DO the following modification in the Connection String of the App.Config / Web.config ...

Remove the AttributeUser Instance =true from the connection string that should solve the issue

|||

Thanks Svante , skurocks for replying

Then I solved my problem , thanks skurocks|||

Hi All

I got this error when I try to display whole record of customers table into GridView .

_http://imagesshared.com/images/opt1192519086n.JPG

This is the first time I work with Ent Lib 3.1 .

Here is my source code , please help me and explain to me why this error occur .

_http://www.4shared.com/file/26591127/38feb983/WebSite1.html?dirPwdVerified=814e49b

Thanks in advance for your time .

.........

Regards

cannot connect to SQL database through IIS

Hello,

I'm new...

I've installed Web Matrix, and MDSE. I've been successful in installing the Time Tracker Starter kit, I've connected to multiple databases (SQL and Access) within Web Matrix and I'm now trying to connect to my own SQL database...with limited success.

Scenario:
I've created a SQL database using MS Access (*.adp) access project for SQL. I've created a new ASPX with Web Matrix and placed a database table into my new ASPX by draggin-n-droppin the table from the DATA connections in Web Matrix. Everything goes smoothly...

When I view the page using the Web Matrix 'built-in' web server (i.e. http://localhost:8080/default.aspx), everything displays just fine...it appears that the script is connecting properly to the database and executing the query without any problem -- I can view my data within the page. However, when I view this page using IIS (i.e. http://localhost/default.aspx), I get:
System.Data.SqlClient.SqlException: Cannot open database requested in login 'myDB'. Login fails. Login failed for user 'MYCOMPUTER\ASPNET' and then a stack dump.

Same code...different web servers...different results.

I've checked as many SQL permissions as I could think of, but my experience with SQL is limited. I'm using Microsoft's FREE SQL tools, but I'm pretty certain that there should be a simple way to ensure that my ASPNET user account has appropriate permissions to access my SQL database through IIS.

any suggestions?As you can see the sql error is occuring as the ASPNET account on your machine doesn't have sql priveleges - to solve this you could either add the apsnet account to sql logins and give it sufficient permissions or impersonate and identity which has sufficient sql priveleges.

Hope that helps
Kashif|||I have the same problem .
Would you please tell me how to add the apsnet account to sql logins .|||You can either run the following stored procedure to add the aspnet account

sp_grantlogin @.loginame

in your case @.login name would be "MachineName\ASPNET" or if you have SQL Enterprise Manager you can open the sql server you want to add the login to and then select security -> Logins -> NewLogin

Hope that helps
Kashif|||Thank you so much. However, I do have MSDE on my Windows XP machine.
and stil have the same problem.
your help will be appreciated.|||To installed MSDE on your system with SQL authentication support use this command:
Setup SAPWD=(Some password) SecurityMode=SQL

where setup is the executable of the setup of MSDE (download it over here with SP3 integrated:http://www.asp.net/msde/default.aspx?tabindex=0&tabid=1). Follow the instructions of the setup.

When you've done so, you can login to the MSDE database using this credentials:
uid=sa
pwd=(Some password)

Replace the (Some password) in the command line by the password you like.|||Thank you so much for your response however I already done that but it doesn't work:
This is my frist samle project for MSDE so would tell me the steps:
1. Create a project by MS Access and login='sa' password='passowrd'. that mean the SQL server is running and it's shown at my desck top.
2. write a small ample of aspx code by WebMatrix
3, it gives me the "Cannot open database requested in login 'adp1'. Login fails. Login failed for user 'sa'."

<%@. Page Language="C#" %>
<%@. import Namespace="System.Data" %>
<%@. import Namespace="System.Data.OleDb" %>
<%@. import Namespace="System.Data.SqlClient" %>
<script runat="server"
 void Page_Load(object sender, EventArgs e) {
 // TODO: Update the ConnectionString and CommandText values for your application
 string ConnectionString = "server=(local);database='adp1';uid='sa'; pwd='password'";
 string CommandText = "select * from news";
 SqlConnection myConnection = new SqlConnection(ConnectionString);
 SqlDataAdapter objCmd1 = new SqlDataAdapter( CommandText, myConnection);
 DataSet ds1 = new DataSet();
 objCmd1.Fill(ds1);
 //myDataList1.DataSource = ds1;
 //MyDataList1.DataBind();
 }
</script>
<html>
<head>
</head>
<body style="FONT-FAMILY: arial">
 <h2>Simple Data Report
 </h2>
 <hr size="1" />
 <ASP:DataList id="MyDataList1" RepeatColumns="1" RepeatDirection="Horizontal" runat="server">
 <ItemTemplate>
 <table border="0" cellpadding="2" width="100%" height="68">
 <tr valign="TOP">
 <td width="20%" valign="top" rowspan="2" height="62">
 Hello
 </td>
 <td width="80%" valign="top" height="7">
 <font face="Verdana" size="2"> <%# DataBinder.Eval(Container.DataItem, "news_title") %> <font face="Verdana" size="1.5"><%# DataBinder.Eval(Container.DataItem, "news_date") %> </font> </font></td>
 </tr>
 <tr>
 <td width="70%" valign="top" height="49">
 <font face="Verdana" size="1.5"> <%# DataBinder.Eval(Container.DataItem, "news_description") %> </font>
 </td>
 </tr>
 </table>
 </ItemTemplate>
 </ASP:DataList>
</body>
</html>

|||From the error it seems the password you are using for sa is incorrect, can you try running some sql scripts from command line using login sa and password password and see if you are able to run those.

-Kashif|||thank you so muh, I am sorry but I been away for a short vacation.
This is my frist samle project for MSDE so would tell me the steps.
I do not have SQL Server..
Would you tell me how to check these command. ...

sat|||Open command prompt, switch to binn directory msde and run something like the following

osql -U sa id -P myPwd -S (.) -d Northwind -q "Select * from customers"

if this fails to run then we know that you are either not setting the sa password right or the MSSQL service is not running on your machine. Also if you have a named instance of MSDE installed then you would use "-S ServerName\InstanceName"

Hope that helps
Kashif|||Thank you.
I have created MS Acces project called adp1. wehn I open the project it asks me for user "sa" and password. I have database tables "news". however when I write the following:
>osql -U sa -P password -S (.) -D adp1 -Q "Select * from news"

Error " Data Source name not found and no default driver"

>osql -U sa -P password -S localhost\adp1 -D adp1 -Q "Select * from news"

Error " Data Source name not found and no default driver"|||actullay, after I run the previous command I got this error on my
http://localhost/

403 Forbidden - The server denies the specified Uniform Resource Locator (URL). Contact the server administrator. (12202)
Internet Security and Acceleration Server.

any explations please..!!! thank you|||The error you have specified above just implies that there is no page defined. Can you look up the Default wesite's home directory and see the "Default document" tab.

-Kashif|||Hello all,

I am writing this message simply because I started this thread and I wanted to remark that with ALL your help, and with many hours of studying SQL server permissions, osql commands and syntax, SQL server port configuration and firewalls, I have resolved the problems I was having when I started this thread.

Thanks to all who provided excellent advice and tolerated my previous ignorances.

Regards.