auto format of stored procedures?

hi,
i have many stored procedures, and i'm currently working on one with many
levels of nested IF's. it came from someone else, and i'm modifying it.
So...i'd love a utility, that i've seen in most other dev environments, that
will 'pretty print', or automatically format all the levels of a stored
procedure.
any one know of such a thing? i don't find much when googling!
thanks,
fred
You could look at SQLEdit from www.ApexSQL.com, and SSMS for SQL 2005 has
what is called 'Smart formatting' -though many may quibble about how 'smart'
it may be...
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Freddy" <frosenbergNOSpam@.globaltouchtelecom.com> wrote in message
news:%23eICHNO4GHA.1848@.TK2MSFTNGP06.phx.gbl...
> hi,
> i have many stored procedures, and i'm currently working on one with many
> levels of nested IF's. it came from someone else, and i'm modifying it.
> So...i'd love a utility, that i've seen in most other dev environments,
> that will 'pretty print', or automatically format all the levels of a
> stored procedure.
> any one know of such a thing? i don't find much when googling!
> thanks,
> fred
>
|||thanks arnie, i'll take a look!
fred
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eQDB%23CP4GHA.2596@.TK2MSFTNGP06.phx.gbl...
> You could look at SQLEdit from www.ApexSQL.com, and SSMS for SQL 2005 has
> what is called 'Smart formatting' -though many may quibble about how
> 'smart' it may be...
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Freddy" <frosenbergNOSpam@.globaltouchtelecom.com> wrote in message
> news:%23eICHNO4GHA.1848@.TK2MSFTNGP06.phx.gbl...
>
sql

AUTO DROP OF SUBSCRIPTION

Hi,
I have merge replicated, remote server & local server.
The database has only one table. It was working fine.
More than 15 days, it was isolated without any transaction.
After some days (around 15 days) I found that the subscription is dropped.
What is the reason for this?
Is there any way to avoid this?
Thanks,
Soura.
Soura,
the Expired Subscription Cleanup agent has deleted the subscription.
Disabling this agent won't help, because you need to forcably prevent the
subscription from expiring. If it is transactional, make sure that the
subscription expiration period is sufficiently long eg 3 weeks, and history
retention period also the same length of time. If it was transactional, the
same would apply to the transaction retention period.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I was under the impression that transactional replication needs to be
re-established after 72 hours and that this is the limit as it can not be
changed past this value. Are you primarily discussing the removal of the
subscription or the expiration of the subscription ?
It has been a long held understanding with the DBA's at my organisation that
72 Hours was the maximum time before expiration of a subscription to
transactional replication required a rebuild of the subscritpion .
"Paul Ibison" wrote:

> Soura,
> the Expired Subscription Cleanup agent has deleted the subscription.
> Disabling this agent won't help, because you need to forcably prevent the
> subscription from expiring. If it is transactional, make sure that the
> subscription expiration period is sufficiently long eg 3 weeks, and history
> retention period also the same length of time. If it was transactional, the
> same would apply to the transaction retention period.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Perhaps the quantity of information in your company makes this a practivcal
limit for the size of the distribution database, but there is no such
hardcoded limit in SQL Server - it is entirely configurable.
Rgds,
Paul Ibison

Authentication problem - Urgent

During the development of the project I used windows authentication on IIS
and everything was working perfectly. But the user must access the reports
through a web application, so I changed the security to anonymous and set the
rights on RS for the anonymous user and since then, the server has crashed in
a random way (rsProcessingError). If I reapply the security the same thing
happens. Does anyone have any ideas?Possibly - we are using a custom built proxy, and in order to use
System.Net.CredentialCache.DefaultCredentials I had to allow anon access.
When deploying it broke [b/c IUSR_machinename didn't have the correct
priv's]. There is a post on how to allow anon access & be able to deploy
reports [titled "does anonymous access have to be disabled in iis?",
8/10/2004] on how to set it up. Note you do it in reporting services, not
iis. Maybe this will get you on the right path.
Good luck,
John Hennesey
"raph" wrote:
> During the development of the project I used windows authentication on IIS
> and everything was working perfectly. But the user must access the reports
> through a web application, so I changed the security to anonymous and set the
> rights on RS for the anonymous user and since then, the server has crashed in
> a random way (rsProcessingError). If I reapply the security the same thing
> happens. Does anyone have any ideas?|||Thanks for your help.
I already tried to put anonymous user as content manager on RS and PowerUser
on the server. But the problem is the same.The report crashes once
every one hundred times with the same parameters by URL and the same error
message.
The processing of group expression for the table 'table1' cannot be
performed. The comparison failed. Please check the data type returned by
group expression.
Raph
"JohnHennesey" wrote:
> Possibly - we are using a custom built proxy, and in order to use
> System.Net.CredentialCache.DefaultCredentials I had to allow anon access.
> When deploying it broke [b/c IUSR_machinename didn't have the correct
> priv's]. There is a post on how to allow anon access & be able to deploy
> reports [titled "does anonymous access have to be disabled in iis?",
> 8/10/2004] on how to set it up. Note you do it in reporting services, not
> iis. Maybe this will get you on the right path.
> Good luck,
> John Hennesey
> "raph" wrote:
> > During the development of the project I used windows authentication on IIS
> > and everything was working perfectly. But the user must access the reports
> > through a web application, so I changed the security to anonymous and set the
> > rights on RS for the anonymous user and since then, the server has crashed in
> > a random way (rsProcessingError). If I reapply the security the same thing
> > happens. Does anyone have any ideas?

Authentication not working!

Hey there,

I have a table [user] in a SQLExpress database [Database.mdf] in the App_Data folder. There are two columns. [userId] and [password]. I want to authenticate the user who wants to log in through a Login control. the user should have his userId and password in the [user] table. I digged and made my code. but it doesn't work it keeps giving me that the user is not authenticated. i guess it has no errors. please tell me if you find the error. here is the code:

1protected void Login1_Authenticate(object sender, AuthenticateEventArgs e)2 {3 SqlConnection conExpress =new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=Database;Integrated Security=True;User Instance=False");//Create the server connection45try6 {7string id_user = Login1.UserName.Trim();//Get the username from the control8string pass_user = Login1.Password;//get the Password from the control910 //These are a sql and connection Examples11string sql ="SELECT userId, password FROM user WHERE userId = @.param_Id AND password = @.param_Password";12 SqlCommand comSQL =new SqlCommand(sql, conExpress);//Create the sql command using sql string and sql connection1314 //Add the sql parameters15 comSQL.Parameters.AddWithValue("@.param_Id", id_user);//New on VS 2.016 comSQL.Parameters.AddWithValue("@.param_Password", pass_user);1718string cod_user ="";19string name_user ="";2021//Open database connection22 conExpress.Open();23 SqlDataReader dr = comSQL.ExecuteReader();24while (dr.Read())25 {26 cod_user = dr.GetValue(0).ToString();//The coduser is unique onto database table27 name_user = dr.GetValue(1).ToString();28 }29 conExpress.Close();//Close Database Connection30if (cod_user !="")//The user exist onto database31 {32//Create the session vars33 Session["coduser"] = cod_user;34 Session["nameuser"] = name_user;35 e.Authenticated =true;//Grant the access, Goes to DestinationPageUrl36 }37 }38catch(Exception)//On Login Error39 {40 e.Authenticated =false;//Confirm that you are out41 conExpress.Close();//On any error case, close the database connection42 }43 }

The way I've used the Login control with custom authentication (without MembershipProvider) is

processing the event of the Login Control:

protected

void Login1_LoggingIn(object sender,LoginCancelEventArgs e)

and if the credentials is valid i create the autheentication cookie:

FormsAuthentication.SetAuthCookie(userName,false);

and Redirect to the page user has requested.

If the credentials are wrong I set :

e.Cancel =

true;

and

Login1.FailureText =

"Unable to validate credentials";

I'm not sure this is the right way of doing that, but it works for me.

Hope this will help you.

Cheers,

Yani

sql

Authentication methods for connections to SQL Server in ASP Pages

I am trying to access SQL Server via an ASP page using a Trusted Connection,
but it is not working. When I run the page I receive the following error
message:
Microsoft OLE DB Service Components error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status
value, if available. No work was done.
line 35
My connection string is in a separate file: cst = "data
source=X099789\Widgets;Initial Catalog=Automotive; Integrated Security=SSPI;
"
My code snipet looks like the following:
set OBJRST = Server.CreateObject("ADODB.Recordset")
Set objComm = Server.CreateObject("ADODB.Command")
objComm.ActiveConnection = cst '****LIne 35
MotorsSQL = "usp_MotorAll"
UIPWSQL = "usp_UIPW '" & struserid &"', '"& strpassword &"';"
objConnAll.open cst
What is the correct coding to connect to the SQL Server using Windows
Authentication in an ASP page?
I have read the instructions on:
http://support.microsoft.com/default.aspx/kb/247931, made the changes,
however the page still will not work.
Kindly assist. I will be thankful.
AuntieAuntieAuntie> Microsoft OLE DB Service Components error '80040e21'
> Multiple-step OLE DB operation generated errors. Check each OLE DB status
> value, if available. No work was done.
These errors are probably because there is no 'Provider' keyword in your
OLEDB connection string. Try adding 'Provider=SQLOLEDB'.
Hope this helps.
Dan Guzman
SQL Server MVP
"AuntieAuntieAuntie" <AuntieAuntieAuntie@.discussions.microsoft.com> wrote in
message news:EE4C5259-1F64-4893-9AF1-657A1C03917B@.microsoft.com...
>I am trying to access SQL Server via an ASP page using a Trusted
>Connection,
> but it is not working. When I run the page I receive the following error
> message:
> Microsoft OLE DB Service Components error '80040e21'
> Multiple-step OLE DB operation generated errors. Check each OLE DB status
> value, if available. No work was done.
> line 35
> My connection string is in a separate file: cst = "data
> source=X099789\Widgets;Initial Catalog=Automotive; Integrated
> Security=SSPI;"
> My code snipet looks like the following:
> set OBJRST = Server.CreateObject("ADODB.Recordset")
> Set objComm = Server.CreateObject("ADODB.Command")
> objComm.ActiveConnection = cst '****LIne 35
> MotorsSQL = "usp_MotorAll"
> UIPWSQL = "usp_UIPW '" & struserid &"', '"& strpassword &"';"
> objConnAll.open cst
> What is the correct coding to connect to the SQL Server using Windows
> Authentication in an ASP page?
> I have read the instructions on:
> http://support.microsoft.com/default.aspx/kb/247931, made the changes,
> however the page still will not work.
> Kindly assist. I will be thankful.
> AuntieAuntieAuntie
>

Authenticate via Active Directory from PC that's not joined the do

I have several people on my team that are working remotely from PCs that are
stand-alone, ie. they have not been joined to the domain. They do have an
account on the domain, though, which they use to map drives and such. How
can I set it up so that they can use Enterprise Manager or Query Analyzer by
authenticating with their domain account?Hello,
I suggest that you create a SQL login for each user. You can refer to the
following article:
Adding a SQL Server Login
http://msdn.microsoft.com/library/d...-us/adminsql/ad
_security_9m0e.asp
How to set up Mixed Mode security (Enterprise Manager)
http://msdn.microsoft.com/library/d...-us/howtosql/ht
_6_secrty_68c9.asp
Make sure SQL server use mixed mode Authentication.
If the client can join the domain with a domain user account, you can add
the domain user account as a Windows login. You can refer to the following
web site:
How to grant a Windows user or group login access to SQL Server (Enterprise
Manager)
http://msdn.microsoft.com/library/d...-us/howtosql/ht
_6_secrty_68c9.asp
Granting a Windows User or Group Access to a Database
http://msdn.microsoft.com/library/d...-us/adminsql/ad
_security_2wit.asp
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

auditing sql server

I need to enable c2 auditing on our SQL SERVER 2000 database, but it's not
working.
I set the parameters:show advanced options and 'c2 audit mode' to 1. I
restarted SQL server. I am a DBA,so my privileges should be appropriate. I
went into SQL analyzer and ran a few DML statements. I am not seeing an audit
file under the mssql serverdata directory. Somethings missing and I do not
know what it is.
I would appreciate any suggestions you may have.Did you run RECONFIGURE?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:3B04E334-80AA-475E-B677-728B1932A173@.microsoft.com...
>I need to enable c2 auditing on our SQL SERVER 2000 database, but it's not
> working.
> I set the parameters:show advanced options and 'c2 audit mode' to 1. I
> restarted SQL server. I am a DBA,so my privileges should be appropriate. I
> went into SQL analyzer and ran a few DML statements. I am not seeing an audit
> file under the mssql serverdata directory. Somethings missing and I do not
> know what it is.
> I would appreciate any suggestions you may have.

Auditing Data Changes

I am using the SCD Wizard and it is working nicely. Can someone point be to an article/tuorial that would explain how you could create an "audit trail" on the items that may have been changed (type I and II)?

Basically, what I want to be able to do is run a query that tells me what data may have changed. I figured I would have to create an auditkey field in my table which would then link the key to the change detail?

The idea behind type 1 changes is that the changes AREN'T tracked. That's the point of type-1 SCDs. Type 2s ARE tracked by their very nature, so reporting on them should be easy. Type 2 records that have changed have "expired" records either indicated by some current flag or by some end date field.|||

Hi Jrp210,

I know of no articles or white papers on this, although some probably exist.

In my mind, this is ETL metadata. In larger ETL, I currently build a database that tracks row counts for the source data and then for different types of changes (including no change). It provides a the means for a quick "sanity-check" at the end of each load: I add up the rows coming out of the SCD Wizard outputs and see if this total matches the rows going in.

If I'm understanding you, you would like to take this to another level and store enough information to identify the rows that have changed, along with which type of change was made.

To tackle this scenario, I would probably use a small field appended to each destination dimension and fact table - tinyint would probably do the trick. If you make this field Null-able, I would start by updating this field - setting it to Null - before each load. For instance, let's say you have a table called dbo.DimStuff that looks like this:

StuffSK int identity(1,1),

StuffBK varchar(50),

StuffName varchar(50)

You could add another field:

StuffChangeType tinyint NULL

When your SSIS package starts, you could include an ExecuteSQL Task with the following statement:

UPDATE dbo.DimStuff

SET StuffChangeType = NULL

This would set all the StffChangeType values to Null prior to executing the load. Then you could add a Derived Column to each output of the Slowly Changing Dimension Wizard that adds a column named StuffChangeType and assigns an appropriate value (maybe 1 for updates, 2 for historical, 3 for new, etc.)

This would add change type data for each row changed - and let you know which rows were not changed.

You could then execute a query like:

SELECT StuffBK, StuffChangeType

FROM dbo.DimStuff

This would give you a picture of the business key and change types. You could qualify with a where clause:

WHERE StuffChangeType IsNotNULL

to only get changes (and change types) since the last load.

You could also do a nice summary with a query like:

SELECT StuffChangeType,Count(*)AS'ChangeTypeCount'

FROM dbo.DimStuff

WHERE StuffChangeType IsNotNULL

GROUPBY StuffChangeType

Hope This Helps,

Andy

|||But Andy, if you want to track Type 1 changes, make them Type 2. When using Type 2 SCDs, use begin and end dates to identify current and historical rows. The auditing is done for you by the nature of SCDs. That's the point of them in the first place. If you want to keep track of which LOAD processed the change, add another column, SessionKey, or something like that.|||

Hi Phil,

I think he's asking for something different from SessionID, and for something more than just Type 2 tracking. To me, it sounds as if he has a business requirement to update certain data - or perhaps lacks a business requirement to track changes on that data - and still wants to track the fact that it changed. I don't think SCDs cover that, and I think the solution I offered does. It's not the only way, but I think it will accomplish what he's after.

If not, perhaps it will help someone else in the future!

Thanks,

Andy

|||Along the path you're thinking Andy, the Kimball Webcast on the first page of this forum (towards the top) talks about auditing. In there, an audit entry is recorded and an identity value is returned that is used within the applicable pieces within SSIS. This value would correspond to the "session" of this load, for this package, etc... Talks about row counts, and other things. Very valuable.|||

Hi Phil,

Yep - that's good stuff.

I don't think that's what the original question was about, but it's definitely good stuff.

Thanks!

Andy

|||

Thanks for the responses. The SCD does do what it is intended to do but I probably should clarify a bit.

Perhaps an example will be the best way to explain.

For the sake of the example I have the following fields in my table:

CompanyKey

CompanyName

CompanyTicker

StartDate

EndDate

The SCD is setup to create historical changes for the name and ticker. This is done with StartDate and EndDate fields. Phil, you are right that I will have a date in the EndDate field if a change comes through for a CompanyName or CompanyTicker. But what I don't know unless I compare the two rows is what exactly changed. Now this is fairly easy to eyeball given this simple scenario. But in my real life scenario I have many fields that could have changed and its not easy to tell which one(s) did. I would like to create a way to determine which of the fields did change.

Hope this makes sense.

Attribute- vs. Element-Centric XML

Hi all,
I am working on a project that involves extracting the data from XML files i
nto SQL Server 2000. My dilemma is attribute- vs. element-centric XML files.
When to use one over the other, and why?
References to good websites would be helpfull, too.
Thanks
Goran DjuranovicGoran Djuranovic wrote:
> Hi all,
> I am working on a project that involves extracting the data from XML
> files into SQL Server 2000. My dilemma is attribute- vs. element-centric
> XML files.
> When to use one over the other, and why?
> References to good websites would be helpfull, too.
This is a FAQ: see http://xml.silmaril.ie/developers/attributes/
///Peter|||Hi Goran,
In addition to the faq article Peter has provided, I think whether to
choose Attribute or Element centric XML format also depend on your
application layer's design or requirement. And such XML format is usually
important when we do data exporting from SQL Server, because when exporting
data, the exported XML document will be further used by other application
or data processing component which may have particular requirement on the
XML document. As for importing ,this won't matter much as long as the
format is recognizable by the SQL Server engine.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Thanks, both.
Goran
"Goran Djuranovic" <goran.djuranovic@.newsgroups.nospam> wrote in message new
s:u4l7QdafGHA.356@.TK2MSFTNGP02.phx.gbl...
Hi all,
I am working on a project that involves extracting the data from XML files i
nto SQL Server 2000. My dilemma is attribute- vs. element-centric XML files.
When to use one over the other, and why?
References to good websites would be helpfull, too.
Thanks
Goran Djuranovic

attribute search in a database

Hi guys,

I'm trying to find some attributes within a database I'm working with. I'm working with SQL Server 2000, and I'm trying to find table which have a certain attribute in them. for example, If I want to see tables from my database which have an attribute called 'home_address', what would the query be? Or is there an inbuilt function in SQL Server 2000 which allows me to search this?

Thank you!

You can query the syscolumns table directly.

SELECT object_name(id) AS objectname
FROM syscolumns
WHERE name = 'Home_Address'

HTH!

|||

Or have a look at INFORMATION_SCHEMA.columns view.

Code Snippet

SELECT * FROM INFORMATION_SCHEMA.columns WHERE COLUMN_NAME = 'Home_Address'

|||

Of course. A much better suggstion that directly accessing the system tables.

Nice one!

Attribute Drill Through not working on certain servers

When I create a report on my local server using Report Builder, and place an attribute on the report that I have set EnableDrillThrough = true, the report behaves as expected when run. That attribute, when hovered over, displays a hand cursor and navigates to the raw data display (not another report) of the related row(role).

This is not working on our network server. When I run the Report Builder from that server, using the exact same model, there is no drill through behavior at all. I don't see the hand cursor, and nothing happens when the column cell is clicked on.

Could this be a security issue? Any help would be appreciated.

I figured it out. Our other server has the Standard edition, not Enterprise or Developer.

Attn: MS Engineers - 3rd Posting - KeepTogether not working

I originally posted this on 10/18 and again on 10/19 but received no response
so I am trying again.
I have a main report that contains a table. One detail row of that table
calls to a subreport (which also contains a table). When the entire report
displays, if the main report and subreport data will all fit on one page it
will do so, but if not, the main report goes part of the way down a page
(leaving up to 3/4 of the page blank), then breaks to a new page to display
the subreport data. It seems to try and keep the subreport data table
together more than the main report table. I have KeepTogether set to False on
both tables in the main report and the subreport. The way it is now, the
display format is unacceptable. Please offer any ideas.I donâ't know if this helps, but I ran into a similar problem.
Problem â' Detail is too large for a single page
Table herders get blown off the page and do not show on subsequent pages
Resolution:
Copy your table header row
Insert a new detail row below the header and after your first detail row
Paste the dummy table header row into the new detail row
Change the visible property on the dummy table header row to something like
the following
o =iif(ReportItems!TableHeaderField.Value=nothing, false,true)
This should fix your issue if I understand it correctly..
Good luck, AHH

Attempt to fetch logical page error

Hello,
I have recently moved some packages I had been working on from my development to production environment. Having come across many configuration related errors, I believed my latest one to be of this type. However I'm recieving one I've never seen before:

"Attempt to fetch logical page (1:95152) in database 8 failed. It belongs to allocation unit 72057594043301888 not to 72057594045661184.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly"

This package works fine on the development db, however I don't even know where to start tracking this issue down. Any thoughts?

Thanks,
Adrian CrawfordI just googled that message and got a link to an article by Bob Dorr who works for PSS supporting SQL Server.
In other words - I suspect this is a SQL Server engine problem, not a SSIS one.

-Jamie|||This sounds like you have a database problem. If it is SQL Server then you should probably post this to the relational engine forum. Again if this is SQL Server, you might try running DBCC on the database to see if that shows any problems.

Matt|||Thanks for the responses, I'll see if anyone knows about this in the database engine forum.
Adrian

Attempt to fetch logical page error

Hello,
I have recently moved some dts packages I had been working on from my development(sql2k5) to production environment(sql2k5). Having come across many configuration related errors, I believed my latest one to be of this type at first. However I'm recieving one I've never seen before:

"Attempt to fetch logical page (1:95152) in database 8 failed. It belongs to allocation unit 72057594043301888 not to 72057594045661184.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly"

This package works fine on the development db, however I don't even know where to start tracking this issue down. Any thoughts? I have tried the SSIS forum and they believe it to be a db engine issue.

Thanks,
Adrian CrawfordThere was a low-level problem with one of my tables involved in the dts on production. I found the specific table by running DBCC checkdb. At that point I dropped and recreated the table which has fixed the problem.
Adrian

attempt to execute SLQ not working

I am hosting my website with a web hosting company on the web. My web application reads
data from a SQL server 2005 database. So far I have been able to establish a connection
to the database, but when I attempt to query data from a table, I a get an error message.
I can't figure out what I am doing wrong here.basically I just want to read data from a
column in the database and to store it into a multi-line textbox control. I am using some
inline sql for the call. Can someone help me out here?

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e) { }
protected void Button1_Click(object sender, EventArgs e)
{
mySqlConnection = new SqlConnection(ConnectionStringConst);
mySqlConnection.Open();
try
{
// Database is named "Torsion", Table I am querying is named "Content"
SqlCommand cmd = new SqlCommand("SELECT * FROM [Torsion].[dbo].[Content] ORDER BY [Section] DESC", mySqlConnection);
rdr = cmd.ExecuteReader();
ListBox1.DataSource = rdr[0];
ListBox1.DataTextField = "HLContent"; // HLContent is the first column in my database
ListBox1.DataBind(); // I want to store HLContent into ListBox1 control
}
catch { throw;}
finally
{ mySqlConnection.Close();
mySqlConnection.Dispose();
}
}
}
-------------ERROR_MESSAGE_BELOW---------

Server Error in '/' Application.

---

Invalid attempt to read when no data is present.

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.InvalidOperationException: Invalid attempt to read when no data is present.

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:

[InvalidOperationException: Invalid attempt to read when no data is present.] System.Data.SqlClient.SqlDataReader.GetValue(Int32 i) +137 System.Data.SqlClient.SqlDataReader.get_Item(Int32 i) +7 _Default.Button1_Click(Object sender, EventArgs e) +167 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

---

Version Information: Microsoft .NET Framework Version:2.0.50727.832; ASP.NET Version:2.0.50727.832

Can you please check whether data is available in the table.

In the code can please check for the null before assingning to the list box. It is as follows:

rdr = cmd.ExecuteReader();

if(rdr != null)
{

ListBox1.DataSource = rdr[0];
ListBox1.DataTextField = "HLContent"; // HLContent is the first column in my database
ListBox1.DataBind(); // I want to store HLContent into ListBox1 control

}

And also please make sure that the DataReader object is also closed, which is nothing to do with the error but it is recommended for the DataReader.

|||The table does have one record in it. The exception is thrown when

cmd.ExecuteReader();

is executed.

|||

u need to specify whether the commandtype is text or stored procedure

Attching A Databse To the SQL Server 2000

I am using " Microsoft.SqlServer.Management.Smo". I am trying to attach a database using the AttachDatabse method in it. but it is not working for me.

I am pasting my code as follows...tell me the problem or the right way to do it...

SqlConnection Connection =newSqlConnection("Data Source=SHEBZ; User ID=sa; Password=sa");

Microsoft.SqlServer.Management.Common.ServerConnection con =new Microsoft.SqlServer.Management.Common.ServerConnection(Connection);Server ser =newServer("SHEBZ");

ser.ConnectionContext.Connect();

System.Collections.Specialized.StringCollection File =new System.Collections.Specialized.StringCollection();

File.Add(@."D:\Project-Parts\MasterPageOffice\Uploads\Db3");

try

{

ser.AttachDatabase("Db3", File);

}

catch (Exception ex)

{

Label1.Text ="ERROR: " + ex.Message.ToString();

}

Hi,

What you are using is Server.AttachDatabase method, try to follow the sample below.

Datastr = "C:\Program Files\Microsoft SQL Server"Datastr = datastr + "\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf"Logstr = "C:\Program Files\Microsoft SQL Server"Logstr = datastr + "\MSSQL.1\MSSQL\Data\AdventureWorks_Log.ldf"

StringCollection sc;sc = new StringCollection();sc.Add(datastr);sc.Add(logstr);srv.AttachDatabase("AdventureWorks", sc, owner, AttachOptions.None);

Thanks.

|||

I know this code...it is for detaching and attaching the databse to a SQL Server 2005 or high..... if you try to use this code for SQL Server 2000 you will get the same exception that this property or method can be used with Sql Server 2005 or high... i want a solution for 2000...

attaching to msdb db

Hello there
On the business i;m working on, I've installed MSDE on my client computer.
So far i've worked with SQL sever 2000
now i transfered to sql server 2005.
and i tried to attach one of my files. It gave me Error because my DB i;m
trying to attach is bigger then 2GB (which is not possible according to
microsoft Licence)
now what can i do,
if i instead of detach and attach i do backup and restore it allow me to do
that?Roy
SQL Server 2005 Express Edition has 4GB limited .Or do you try to attach
SQL Server 2005 to MSDE database?
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:umkvw3UGHHA.4116@.TK2MSFTNGP05.phx.gbl...
> Hello there
> On the business i;m working on, I've installed MSDE on my client computer.
> So far i've worked with SQL sever 2000
> now i transfered to sql server 2005.
> and i tried to attach one of my files. It gave me Error because my DB i;m
> trying to attach is bigger then 2GB (which is not possible according to
> microsoft Licence)
> now what can i do,
> if i instead of detach and attach i do backup and restore it allow me to
> do that?
>

attaching to msdb db

Hello there
On the business i;m working on, I've installed MSDE on my client computer.
So far i've worked with SQL sever 2000
now i transfered to sql server 2005.
and i tried to attach one of my files. It gave me Error because my DB i;m
trying to attach is bigger then 2GB (which is not possible according to
microsoft Licence)
now what can i do,
if i instead of detach and attach i do backup and restore it allow me to do
that?
Roy
SQL Server 2005 Express Edition has 4GB limited .Or do you try to attach
SQL Server 2005 to MSDE database?
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:umkvw3UGHHA.4116@.TK2MSFTNGP05.phx.gbl...
> Hello there
> On the business i;m working on, I've installed MSDE on my client computer.
> So far i've worked with SQL sever 2000
> now i transfered to sql server 2005.
> and i tried to attach one of my files. It gave me Error because my DB i;m
> trying to attach is bigger then 2GB (which is not possible according to
> microsoft Licence)
> now what can i do,
> if i instead of detach and attach i do backup and restore it allow me to
> do that?
>
|||Maybe one of the following will help you:
1. If the db has a lot of free space, see if shrinking will reduce the size.
2. Move some tables into a new database, then shrink and attach both.
3. Delete and/or move some of your older data into a new database, then
shrink and attach both.

attaching to msdb db

Hello there
On the business i;m working on, I've installed MSDE on my client computer.
So far i've worked with SQL sever 2000
now i transfered to sql server 2005.
and i tried to attach one of my files. It gave me Error because my DB i;m
trying to attach is bigger then 2GB (which is not possible according to
microsoft Licence)
now what can i do,
if i instead of detach and attach i do backup and restore it allow me to do
that?Roy
SQL Server 2005 Express Edition has 4GB limited .Or do you try to attach
SQL Server 2005 to MSDE database?
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:umkvw3UGHHA.4116@.TK2MSFTNGP05.phx.gbl...
> Hello there
> On the business i;m working on, I've installed MSDE on my client computer.
> So far i've worked with SQL sever 2000
> now i transfered to sql server 2005.
> and i tried to attach one of my files. It gave me Error because my DB i;m
> trying to attach is bigger then 2GB (which is not possible according to
> microsoft Licence)
> now what can i do,
> if i instead of detach and attach i do backup and restore it allow me to
> do that?
>|||Maybe one of the following will help you:
1. If the db has a lot of free space, see if shrinking will reduce the size.
2. Move some tables into a new database, then shrink and attach both.
3. Delete and/or move some of your older data into a new database, then
shrink and attach both.

Attaching DB's to Sql Server Management (User Instantances) Error

I have been working on a website in Visual Studio 2005 Pro using the MS SQL Server that comes with VS. I have tried Attaching those MDF files in Visual Studio to the SQL Server Express Management but it wont let me, get an error::

Error Number: 5133
Severity: 16
State: 1
Line Number: 1

TITLE: Microsoft SQL Server Management Studio Express

Attach database failed for Server 'WYATT-PC\SQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

Directory lookup for the file "C:\Users\Wyatt\Documents\Visual Studio 2005\WebSites\AdultUnderWorld\App_Data\aspnetdb.mdf" failed with the operating system error 5(Access is denied.). (Microsoft SQL Server, Error: 5133)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=5133&LinkId=20476

BUTTONS:

OK

Here is the info for the Web.config Connection Strings::

<connectionStrings>

<add name="Personal" connectionString="Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|Personal.mdf" providerName="System.Data.SqlClient" />

<remove name="LocalSqlServer"/>

<add name="LocalSqlServer" connectionString="Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|aspnetdb.mdf" />

</connectionStrings>

I want to attach the above databases into the SQL Server Management Studio,so how can i go about doing this?

You don't attach this database using Management Studio, it is being used by a User Instance and automatically attaches at runtime of the application. By default, Management Studio works against the parent instance of SQL Express, not your User Instance, check out the blog post that documents how to connect to your User Instance with Management Studio. (FYI - This is listed in the FAQ at the top of this forum, always a good place to check every once in a while to see if your question is answered there.)

Mike