Database Unit Testing: select

Showing posts with label select. Show all posts

Sunday, March 25, 2012

Database size on IIS 5

Good Morning,
I have a web service developed using vb.net. The web
service returns xml from selected data (SQL SELECT
statement using XML AUTO) from SQL Server. The web service
runs properly on smaller databases on both IIS 5 (Windows
2000 Server) and IIS 6 (Windows 2003 Server), but returns
an error when run against larger databases on IIS 5. The
error has been 'out System.OutOfMemoryException'.
I am trying to figure out the difference between IIS 5 and
IIS 6 which is causing this, and a solution.I guess the database is too big for IIS5, so it can not run.
After all, when IIS5 design, no one can image a big database like we use
today.
I think If I am you, I will catch this exception and give the customer some
information to update the IIS.
Last: This is a problem of IIS, you may ask there:)

Monday, March 19, 2012

Database Select to Label

Hello!

I am tryingto select info from a database (MS-SQL) and show that whit a label. And don’t reallyget every thing to work. So I am glad for that help I can get.

SqlConnection myConnection =newSqlConnection();

myConnection.ConnectionString ="datasource='XXX';User ID='XXX';Password=XXX;database='XXX'";

myConnection.Open();

SqlCommand dataCommand =newSqlCommand();

dataCommand.Connection= myConnection;

dataCommand.CommandText ="SELECT XXX,XXX FROM XXX";

SqlDataReader dataReader =dataCommand.ExecuteReader();

string notis1 = dataReader;

Label1.Text = notis1;

myConnection.Close();

Any help whould be helpfoul.

You need to get the value you want from the DataReader.

string notis1;

while (dataReader.Read())
{
notis1 = dataReader.GetString("Some index");
}

...

dataReader.Close();

|||

Thanks for the help but still not get it to work perfectly. My code now is:

protected void Button1_Click(object sender, EventArgs e)
{
int notis_nr;
string notis_text, notis_rubrik;

SqlConnection myConnection = new SqlConnection();
myConnection.ConnectionString = "data source='xxx';User ID='xxx';Password=xxx;database='xxx'";
myConnection.Open();
SqlCommand dataCommand = new SqlCommand();
dataCommand.Connection = myConnection;
dataCommand.CommandText = "SELECT NotisNr, NotisRubrik, NotisText FROM Notis";
SqlDataReader dataReader = dataCommand.ExecuteReader();

while (dataReader.Read())
{
notis_nr = dataReader.GetInt32(0);
notis_rubrik = dataReader.GetString(1);
notis_text = dataReader.GetString(2);
Label1.Text = notis_rubrik;
}

dataReader.Close();
myConnection.Close();

|||Have you tried running the query to see if it returns any results?|||

Brogren:
Thanks for the help but still not get it to work perfectly. My code now is:

protected void Button1_Click(object sender, EventArgs e)
{
...
while (dataReader.Read())
{
notis_nr = dataReader.GetInt32(0);
notis_rubrik = dataReader.GetString(1);
notis_text = dataReader.GetString(2);
Label1.Text = notis_rubrik;
}
...

What's the result you want? Your code will set Label1.Text to 2nd field value of the last row, as in the while loop the Label1.Text is being changed at each read of the SqlDataReader. If you want to concatenate strings from 2nd field of each row, you may need something like:

while (dataReader.Read())
{
notis_nr = dataReader.GetInt32(0);
notis_rubrik = dataReader.GetString(1);
notis_text = dataReader.GetString(2);
Label1.Text += notis_rubrik+"#";
}

|||

ThanksStrongTypes every thingworksperfectly!

But now Ihave decided to us a multiline textbox and I need that the text is exactly likeI want. The problem now is when I us a string a can’t use enter. So a text thatshoul look like this:

----

Hello

ASP.Net friends!

----

Looks likethis

----

Hello ASP.Netfriends!

----

I guess Iwill need to store my textbox like a html page or xml file But I really not getit right.

So I am helpfulfor any ides.

Big Smile

Friday, February 24, 2012

Database recovery option

On right click of database in ER, goto properties and Options tab, there is
Recovery model selection, what would happen if I select one of them ?Alan,
From Books Online:
You can select one of three recovery models for each database in
Microsoft® SQL Server? 2000 to determine how your data is backed up and
what your exposure to data loss is. The following recovery models are
available:
Simple Recovery
Simple Recovery allows the database to be recovered to the most recent
backup.
Full Recovery
Full Recovery allows the database to be recovered to the point of failure.
Bulk-Logged Recovery
Bulk-Logged Recovery allows bulk-logged operations.
The recovery model of a new database is inherited from the model
database when the new database is created.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Alan wrote:
> On right click of database in ER, goto properties and Options tab, there is
> Recovery model selection, what would happen if I select one of them ?
>|||The more recommeded model is Full Recovery. And switching between the
model could impact/break the continuity of your log and your overall
backup and recovery strategy. You might want to fully understand it
before you start thinking what you want to do with it.
Mark Allison wrote:
> Alan,
> From Books Online:
> You can select one of three recovery models for each database in
> Microsoft® SQL Server? 2000 to determine how your data is backed up and
> what your exposure to data loss is. The following recovery models are
> available:
> Simple Recovery
> Simple Recovery allows the database to be recovered to the most recent
> backup.
> Full Recovery
> Full Recovery allows the database to be recovered to the point of failure.
> Bulk-Logged Recovery
> Bulk-Logged Recovery allows bulk-logged operations.
> The recovery model of a new database is inherited from the model
> database when the new database is created.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Alan wrote:
>> On right click of database in ER, goto properties and Options tab,
>> there is
>> Recovery model selection, what would happen if I select one of them ?
>>|||I just wonder what would happen if:
1) choose full recovery mode, then create a maintenance plan, just backup
the database without transaction log backup ?
2)choose simple recovery mode, then create a maintenance plan, backup both
database and transaction log ?
Do I still get the data back from the point of failure ?
"Jonathan Yong" <dataerror@.someplace.com> wrote in message
news:OtyB47IpEHA.1992@.TK2MSFTNGP09.phx.gbl...
> The more recommeded model is Full Recovery. And switching between the
> model could impact/break the continuity of your log and your overall
> backup and recovery strategy. You might want to fully understand it
> before you start thinking what you want to do with it.
>
> Mark Allison wrote:
> > Alan,
> >
> > From Books Online:
> >
> > You can select one of three recovery models for each database in
> > Microsoft?SQL Server?2000 to determine how your data is backed up and
> > what your exposure to data loss is. The following recovery models are
> > available:
> >
> > Simple Recovery
> > Simple Recovery allows the database to be recovered to the most recent
> > backup.
> >
> > Full Recovery
> > Full Recovery allows the database to be recovered to the point of
failure.
> >
> > Bulk-Logged Recovery
> > Bulk-Logged Recovery allows bulk-logged operations.
> >
> > The recovery model of a new database is inherited from the model
> > database when the new database is created.
> >
> > --
> > Mark Allison, SQL Server MVP
> > http://www.markallison.co.uk
> >
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602m.html
> >
> >
> > Alan wrote:
> >
> >> On right click of database in ER, goto properties and Options tab,
> >> there is
> >> Recovery model selection, what would happen if I select one of them ?
> >>
> >>|||In simple recovery mode, you do not get data back to the point of
failure because it does not support log backup.
If that is your requirement, choose full recovery model instead. You can
combine Full, Differential and Log backup in this model.
However, to really recover up to the point of failure, it is not as
straightforward as just restoring from the log. You need to be able to
get hold of the tail of the log of the database that fail.
Alan wrote:
> I just wonder what would happen if:
> 1) choose full recovery mode, then create a maintenance plan, just backup
> the database without transaction log backup ?
> 2)choose simple recovery mode, then create a maintenance plan, backup both
> database and transaction log ?
> Do I still get the data back from the point of failure ?
>
> "Jonathan Yong" <dataerror@.someplace.com> wrote in message
> news:OtyB47IpEHA.1992@.TK2MSFTNGP09.phx.gbl...
>>The more recommeded model is Full Recovery. And switching between the
>>model could impact/break the continuity of your log and your overall
>>backup and recovery strategy. You might want to fully understand it
>>before you start thinking what you want to do with it.
>>
>>Mark Allison wrote:
>>Alan,
>> From Books Online:
>>You can select one of three recovery models for each database in
>>Microsoft?SQL Server?2000 to determine how your data is backed up and
>>what your exposure to data loss is. The following recovery models are
>>available:
>>Simple Recovery
>>Simple Recovery allows the database to be recovered to the most recent
>>backup.
>>Full Recovery
>>Full Recovery allows the database to be recovered to the point of
> failure.
>>Bulk-Logged Recovery
>>Bulk-Logged Recovery allows bulk-logged operations.
>>The recovery model of a new database is inherited from the model
>>database when the new database is created.
>>--
>>Mark Allison, SQL Server MVP
>>http://www.markallison.co.uk
>>Looking for a SQL Server replication book?
>>http://www.nwsu.com/0974973602m.html
>>
>>Alan wrote:
>>
>>On right click of database in ER, goto properties and Options tab,
>>there is
>>Recovery model selection, what would happen if I select one of them ?
>>
>
>|||Is that mean if I choose simple recovery mode from the property page of a
database, eg. Northwind, I cannot create a maintenance plan that consists of
transaction log backup ?
"Jonathan Yong" <jyong@.someplace.net> wrote in message
news:%23DdP1iNrEHA.452@.TK2MSFTNGP09.phx.gbl...
> In simple recovery mode, you do not get data back to the point of
> failure because it does not support log backup.
> If that is your requirement, choose full recovery model instead. You can
> combine Full, Differential and Log backup in this model.
> However, to really recover up to the point of failure, it is not as
> straightforward as just restoring from the log. You need to be able to
> get hold of the tail of the log of the database that fail.
>
> Alan wrote:
> > I just wonder what would happen if:
> > 1) choose full recovery mode, then create a maintenance plan, just
backup
> > the database without transaction log backup ?
> >
> > 2)choose simple recovery mode, then create a maintenance plan, backup
both
> > database and transaction log ?
> >
> > Do I still get the data back from the point of failure ?
> >
> >
> >
> > "Jonathan Yong" <dataerror@.someplace.com> wrote in message
> > news:OtyB47IpEHA.1992@.TK2MSFTNGP09.phx.gbl...
> >
> >>The more recommeded model is Full Recovery. And switching between the
> >>model could impact/break the continuity of your log and your overall
> >>backup and recovery strategy. You might want to fully understand it
> >>before you start thinking what you want to do with it.
> >>
> >>
> >>Mark Allison wrote:
> >>
> >>Alan,
> >>
> >> From Books Online:
> >>
> >>You can select one of three recovery models for each database in
> >>Microsoft?SQL Server?2000 to determine how your data is backed up and
> >>what your exposure to data loss is. The following recovery models are
> >>available:
> >>
> >>Simple Recovery
> >>Simple Recovery allows the database to be recovered to the most recent
> >>backup.
> >>
> >>Full Recovery
> >>Full Recovery allows the database to be recovered to the point of
> >
> > failure.
> >
> >>Bulk-Logged Recovery
> >>Bulk-Logged Recovery allows bulk-logged operations.
> >>
> >>The recovery model of a new database is inherited from the model
> >>database when the new database is created.
> >>
> >>--
> >>Mark Allison, SQL Server MVP
> >>http://www.markallison.co.uk
> >>
> >>Looking for a SQL Server replication book?
> >>http://www.nwsu.com/0974973602m.html
> >>
> >>
> >>Alan wrote:
> >>
> >>
> >>On right click of database in ER, goto properties and Options tab,
> >>there is
> >>Recovery model selection, what would happen if I select one of them ?
> >>
> >>
> >
> >
> >

Database recovery option

Sunday, February 19, 2012

database question

I have an Access application that links to SQL Server tables via an ODBC link.

I execute the following query in the Access application,

SELECT customerName From customers Where stateCode = 'CA'

My question:

Who (Access, SQL Server or the ODBC provider) selects based on the state code?

Does SQL Server only return customerNames where stateCode = 'CA'
OR
Does SQL Server return ALL rows and let Access select the customerNames where stateCode = 'CA'
OR
Does SQL Server return ALL rows and let the ODBC provider select and return only the appropriate rows to Access?

Thanks for your helpThe cleanest way to determine this is to run the query while SQL Profiler is running, and see what gets passed to SQL Server. My guess is that Access only asks for the rows where stateCode is CA.|||Agree with D.R. However, from what I remember it depends on the ODBC driver. If the remote server (in this case SQL) implements a specific part of the ODBC capabiliies then it will do the query on the server, otherwise the client has to do it. I'm pretty sure SQL will implement that capability.

Tuesday, February 14, 2012

database owner is needed

hi,

on 1 of my servers (actually, the dev. server I have setup up here at home), I must included the database owner everytime I select something or make a db call.

for example

select T.foo from K inner join T on T.id = K.id

must actualy be written like:

select dbo.T.foo from dbo.K inner join dbo.T on dbo.T.id = dbo.K.id

or else it wont work

what did I do wrong for this database owner thing to be a "must" when writing queries on this server.

spec:
sql 2000 sp4You are not connecting as a dbo user.|||

That's wierd. What error are you getting? It should check the dbo owned objects after checking for an object owned by you.

|||

Amir, please update thread.

Thanks,

Derek

|||sorry folks,

I was tied up on another project for the week. I came back and tried to

trouble shoot instead of wasting you guy's time but at the end, i

failed.
basically, here is the situation:

the actual server that will end up hosting the project is setup fine so if i leave out the dbo.* or user.* and just type select * from table it works.

right now, i'm resorting to including the dbo in my queries... and evertime i upload to the server, i do a search in that folder and replace " dbo." with "" in all files.

let me explain how i made this database:

originally, it was on a dev server somewhere in US...
i made the db on my home server. Then created a user and gave it admin privilages over that db.
Then i did "All Tasks > Import" and imported the tables from that server, to this new home db. I had to go back and manually select the primary key for each table, as they got lost during the transfer.

now, when i look at the "Server Explorer" cluster of tables in this new database, i see that they all have (dbo) beside their names, meaning the owner of each table by default is dbo.

i think that should pretty much cover everything....
any clue as to why user "must" be specified?|||

What do you get on the server that requires dbo. when you execute:

select suser_sname(), user_name()

Also, for some object where you have to enter dbo. for the object, execute:

select *
from sysobjects
where name = '<name>'

select *
from information_schema.tables
where table_name = '<name>'

Perhaps this will shed some light? Also what is in @.@.version?

This might just be a stumper that requires a higher power :)

|||

> What do you get on the server that requires dbo. when you execute:

>select suser_sname(), user_name()

| __|_
| foouser | foouser

>select *
>from sysobjects
>where name = '<name>'

irrelevant

> select *
> from information_schema.tables
> where table_name = '<name>'

TABLE_SCHEMA is 'dbo' for all objects

conclusion:
objects where created as dbo when they were imported from the server on the hosting company to the local staging environment;

how can i prevent this when using SQL 2000 enterprise manager for import

(note: from my exp. with sql 2005, this problem wasn't encountered when going through the same steps -- or rather similar steps -- using native sql 2005 import/export functionality within the SQL 2005 studio)

side note:
i rather find a script based answer rather learning to use the GUI, because of my DB2, mysql background -- obviously i'm not gifted when using GUI tools

Sunday, March 25, 2012

Database size on IIS 5

Monday, March 19, 2012

Database Select to Label

Friday, February 24, 2012

Database recovery option

Database recovery option

Sunday, February 19, 2012

database question

Tuesday, February 14, 2012

database owner is needed

Database Unit Testing

Blog Archive

About Me