copy database: February 2012

Friday, February 24, 2012

How to count distinct members and keep good performance

Hi all,

I have a simple cube with only one measure : "Amount", I have three dimensions Supplier, Category and Company.

I would like to display a matrix with Categories as rows, company as columns, the amount and the number of distinct suppliers who appaers for each category/company.

I tried to create a calculated measure like this:

Count(Crossjoin({[D Supplier].[Supplier Code].members}, {[Measures].[Amount]}), EXCLUDEEMPTY)

The result is OK, but when I add this calculated Measure to my query the execution time jumps from a few seconds (<10) to a few minutes. Am I missing something ?

Anyone have an idea to do this in a better way ?

Try

Count(NonEmptyCrossjoin({[D Supplier].[Supplier Code].members}, {[Measures].[Amount]})

|||Thanks for the answer, but I still have very long execution time when using this expression.|||How many cells are there in the query? If there are many cells, then the poor performance is likely due to the AS server using a slow cell-by-cell query execution plan. If you see a single CPU pegged during query execution on the AS server then it is an indication of running such a slow query plan. Improving performance for this kind of queries is one of the proposed work items for the next major release of the product.

How to count across multiple tables in a DB?

Thank you in advance for your assitance. I am trying to write a query that will query multiple tables for the same column. All the tables have thsi column "szF11". I am wanting something similar to this:

Code Snippet

SELECT count(ulID)

FROM (dbo.F_ACCOU_Data UNION dbo.F_AGNCY_Data UNION dbo.F_APPEA_Data UNION etc.....)

WHERE szF11 = ' '

Note: ulID is the name of a column that every table has and szF11 is also in every table.

Pseudo Code: I want to count how many ulID's (if there is a row then something is in the ulID column it is never blank) in all the tables that are listed that have a blank in the szF11 column.

I am getting a very cryptic error message and of course I can't find anything in the documentation to help me understand the error.

Thanks,

Erik

You have a few options.

You could do use a derived table:

Code Snippet

SELECT COUNT(uID)
FROM
(SELECT uID, szF11
FROM F_ACCOU_Data
UNION ALL
SELECT uID, szF11
FROM F_AGNCY_Data
UNON ALL
SELECT uID, szF11
FROM F_APPEA_Data
UNION
etc....) AS Tbl
WHERE sF11 = ''

A variation on this could be that you include the WHERE clause for each table within Tbl

Code Snippet

SELECT COUNT(uID)
FROM
(SELECT uID, szF11
FROM F_ACCOU_Data
WHERE sF11 = ''
UNION ALL
SELECT uID, szF11
FROM F_AGNCY_Data
WHERE sF11 = ''
etc...) AS Tbl

Another option would be to include the tablename in the derived table so you can find out how many rows per table

Code Snippet

SELECT TableName, COUNT(uID)
FROM
(SELECT uID, 'F_ACCOU_Data' AS TableName, sF11
FROM F_ACCOU_Data
UNION ALL
SELECT uID, 'F_AGNCY_Data', sF11
FROM F_AGNCY_Data
etc...
) AS tbl
WHERE sF11 = ''
GROUP BY TableName

HTH!

How to count a very large volume of request

quote:

> Hello,
> What is the best way to accomplish the following.
> 1) An ASP.NET program (consiting of one file somepage.aspx) receives about
> 25,000,000 requests pay day.
> 2) The requests come from a limited set of IP addresses.
> 3) How to count the total number of request from each IP address per day.
> SQL Server 2000 is used on the backend.
> Currently we used the following architecture:
> * Each request to somepage.aspx generates an INSERT into a LogTable1
> * There is a clustered index on the LogTable1 on the dateStamp field which
> is of the type smalldatetime
> * The dateStamp field has a default that sets it to the getdate()
> * At the end of each day at 12:01AM there is a simple query that runs and
> does a group by to count the number of requests from each IP in the given
> date range (past 24 hours). This query works great and takes only 2

minutes

quote:

> to run.
> Is there a better way to accomplish this without having to do INSERTS into
> the LogTable1 for each request?
> It would not work to have the ASP.NET program execute an UPDATE each time

quote:

> increment the total number of request, since this would cause LOTSSS of
> locking in the database layer.
> Thanks in advance.
> Arsen
>

|||Hi,
What about using the IIS log file?
you could set it in the way you need it, basically the client IP and maybe
the time, if you configure it to update daily all you have to do at 12:01
is run a process that read the file generated and do what you need.
Cheers,
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"Arsen V." <arsen.NoSpamPlease@.emergency24.com> wrote in message
news:emdbHKo6DHA.2412@.TK2MSFTNGP09.phx.gbl...

quote:

> Hello,
> What is the best way to accomplish the following.
> 1) An ASP.NET program (consiting of one file somepage.aspx) receives about
> 25,000,000 requests pay day.
> 2) The requests come from a limited set of IP addresses.
> 3) How to count the total number of request from each IP address per day.
> SQL Server 2000 is used on the backend.
> Currently we used the following architecture:
> * Each request to somepage.aspx generates an INSERT into a LogTable1
> * There is a clustered index on the LogTable1 on the dateStamp field which
> is of the type smalldatetime
> * The dateStamp field has a default that sets it to the getdate()
> * At the end of each day at 12:01AM there is a simple query that runs and
> does a group by to count the number of requests from each IP in the given
> date range (past 24 hours). This query works great and takes only 2

minutes

quote:

> to run.
> Is there a better way to accomplish this without having to do INSERTS into
> the LogTable1 for each request?
> It would not work to have the ASP.NET program execute an UPDATE each time

quote:

> increment the total number of request, since this would cause LOTSSS of
> locking in the database layer.
> Thanks in advance.
> Arsen
>

|||Hi Kevin,
Do you suggest storing the TOTALs in the Cache?
Or storing the actual requests: date and ip
Would there be a locking problem?
How to do the "periodic updates" to the database from the Cache?
Thanks,
Arsen
"Kevin Spencer" <kevin@.takempis.com> wrote in message
news:ezSYvso6DHA.1936@.TK2MSFTNGP12.phx.gbl...

quote:

> You could store the data in your Application Cache, and update it
> periodically to the database.
> --
> HTH,
> Kevin Spencer
> .Net Developer
> Microsoft MVP
> Big things are made up
> of lots of little things.
> "Arsen V." <arsen.NoSpamPlease@.emergency24.com> wrote in message
> news:emdbHKo6DHA.2412@.TK2MSFTNGP09.phx.gbl...
about[QUOTE]
day.[QUOTE]
which[QUOTE]
and[QUOTE]
given[QUOTE]
> minutes
into[QUOTE]
time[QUOTE]
> to
>

|||Hi Arsen,
You could put a DataTable in the Application Cache, and add records to it
with each Request. As for periodic updating, you could put a routine in the
Session_OnStart Sub that checks an Application DateTime variable, and at
certain intervals, inserts all the records from the DataTable into the
database and clears out the DataTable.
To be safe, you would want to add code to your Application_OnEnd sub to
update the database if the Application stops or times out; however, with 25M
requests per day, that might not be necessary.
HTH,
Kevin Spencer
.Net Developer
Microsoft MVP
Big things are made up
of lots of little things.
"Arsen V." <arsen.NoSpamPlease@.emergency24.com> wrote in message
news:#daQL6o6DHA.2908@.tk2msftngp13.phx.gbl...

quote:

> Hi Kevin,
> Do you suggest storing the TOTALs in the Cache?
> Or storing the actual requests: date and ip
> Would there be a locking problem?
> How to do the "periodic updates" to the database from the Cache?
> Thanks,
> Arsen
> "Kevin Spencer" <kevin@.takempis.com> wrote in message
> news:ezSYvso6DHA.1936@.TK2MSFTNGP12.phx.gbl...
> about
> day.
> which
> and
> given
> into
> time
of[QUOTE]
>

|||what i have done is cache the last 15 minutes. the cache has the ipaddress,
the start of the 15 min interval, pagename, and number of hits during the
interval. as most users cluster their hits, this cuts down the number of
inserts.
i flush the cache every 15 minutes of when too large.
you can then get daily or hourly stats from the db with simple queries.
-- bruce (sqlwork.com)
"Arsen V." <arsen.NoSpamPlease@.emergency24.com> wrote in message
news:emdbHKo6DHA.2412@.TK2MSFTNGP09.phx.gbl...

quote:

> Hello,
> What is the best way to accomplish the following.
> 1) An ASP.NET program (consiting of one file somepage.aspx) receives about
> 25,000,000 requests pay day.
> 2) The requests come from a limited set of IP addresses.
> 3) How to count the total number of request from each IP address per day.
> SQL Server 2000 is used on the backend.
> Currently we used the following architecture:
> * Each request to somepage.aspx generates an INSERT into a LogTable1
> * There is a clustered index on the LogTable1 on the dateStamp field which
> is of the type smalldatetime
> * The dateStamp field has a default that sets it to the getdate()
> * At the end of each day at 12:01AM there is a simple query that runs and
> does a group by to count the number of requests from each IP in the given
> date range (past 24 hours). This query works great and takes only 2

minutes

quote:

> to run.
> Is there a better way to accomplish this without having to do INSERTS into
> the LogTable1 for each request?
> It would not work to have the ASP.NET program execute an UPDATE each time

quote:

> increment the total number of request, since this would cause LOTSSS of
> locking in the database layer.
> Thanks in advance.
> Arsen
>

How to count a very large volume of request

Hello,
What is the best way to accomplish the following.
1) An ASP.NET program (consiting of one file somepage.aspx) receives about
25,000,000 requests pay day.
2) The requests come from a limited set of IP addresses.
3) How to count the total number of request from each IP address per day.
SQL Server 2000 is used on the backend.
Currently we used the following architecture:
* Each request to somepage.aspx generates an INSERT into a LogTable1
* There is a clustered index on the LogTable1 on the dateStamp field which
is of the type smalldatetime
* The dateStamp field has a default that sets it to the getdate()
* At the end of each day at 12:01AM there is a simple query that runs and
does a group by to count the number of requests from each IP in the given
date range (past 24 hours). This query works great and takes only 2 minutes
to run.
Is there a better way to accomplish this without having to do INSERTS into
the LogTable1 for each request?
It would not work to have the ASP.NET program execute an UPDATE each time to
increment the total number of request, since this would cause LOTSSS of
locking in the database layer.
Thanks in advance.
ArsenYou could store the data in your Application Cache, and update it
periodically to the database.
--
HTH,
Kevin Spencer
.Net Developer
Microsoft MVP
Big things are made up
of lots of little things.
"Arsen V." <arsen.NoSpamPlease@.emergency24.com> wrote in message
news:emdbHKo6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> Hello,
> What is the best way to accomplish the following.
> 1) An ASP.NET program (consiting of one file somepage.aspx) receives about
> 25,000,000 requests pay day.
> 2) The requests come from a limited set of IP addresses.
> 3) How to count the total number of request from each IP address per day.
> SQL Server 2000 is used on the backend.
> Currently we used the following architecture:
> * Each request to somepage.aspx generates an INSERT into a LogTable1
> * There is a clustered index on the LogTable1 on the dateStamp field which
> is of the type smalldatetime
> * The dateStamp field has a default that sets it to the getdate()
> * At the end of each day at 12:01AM there is a simple query that runs and
> does a group by to count the number of requests from each IP in the given
> date range (past 24 hours). This query works great and takes only 2
minutes
> to run.
> Is there a better way to accomplish this without having to do INSERTS into
> the LogTable1 for each request?
> It would not work to have the ASP.NET program execute an UPDATE each time
to
> increment the total number of request, since this would cause LOTSSS of
> locking in the database layer.
> Thanks in advance.
> Arsen
>|||Hi,
What about using the IIS log file?
you could set it in the way you need it, basically the client IP and maybe
the time, if you configure it to update daily all you have to do at 12:01
is run a process that read the file generated and do what you need.
Cheers,
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"Arsen V." <arsen.NoSpamPlease@.emergency24.com> wrote in message
news:emdbHKo6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> Hello,
> What is the best way to accomplish the following.
> 1) An ASP.NET program (consiting of one file somepage.aspx) receives about
> 25,000,000 requests pay day.
> 2) The requests come from a limited set of IP addresses.
> 3) How to count the total number of request from each IP address per day.
> SQL Server 2000 is used on the backend.
> Currently we used the following architecture:
> * Each request to somepage.aspx generates an INSERT into a LogTable1
> * There is a clustered index on the LogTable1 on the dateStamp field which
> is of the type smalldatetime
> * The dateStamp field has a default that sets it to the getdate()
> * At the end of each day at 12:01AM there is a simple query that runs and
> does a group by to count the number of requests from each IP in the given
> date range (past 24 hours). This query works great and takes only 2
minutes
> to run.
> Is there a better way to accomplish this without having to do INSERTS into
> the LogTable1 for each request?
> It would not work to have the ASP.NET program execute an UPDATE each time
to
> increment the total number of request, since this would cause LOTSSS of
> locking in the database layer.
> Thanks in advance.
> Arsen
>|||Hi Kevin,
Do you suggest storing the TOTALs in the Cache?
Or storing the actual requests: date and ip
Would there be a locking problem?
How to do the "periodic updates" to the database from the Cache?
Thanks,
Arsen
"Kevin Spencer" <kevin@.takempis.com> wrote in message
news:ezSYvso6DHA.1936@.TK2MSFTNGP12.phx.gbl...
> You could store the data in your Application Cache, and update it
> periodically to the database.
> --
> HTH,
> Kevin Spencer
> .Net Developer
> Microsoft MVP
> Big things are made up
> of lots of little things.
> "Arsen V." <arsen.NoSpamPlease@.emergency24.com> wrote in message
> news:emdbHKo6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> > Hello,
> >
> > What is the best way to accomplish the following.
> >
> > 1) An ASP.NET program (consiting of one file somepage.aspx) receives
about
> > 25,000,000 requests pay day.
> >
> > 2) The requests come from a limited set of IP addresses.
> >
> > 3) How to count the total number of request from each IP address per
day.
> >
> > SQL Server 2000 is used on the backend.
> >
> > Currently we used the following architecture:
> >
> > * Each request to somepage.aspx generates an INSERT into a LogTable1
> > * There is a clustered index on the LogTable1 on the dateStamp field
which
> > is of the type smalldatetime
> > * The dateStamp field has a default that sets it to the getdate()
> > * At the end of each day at 12:01AM there is a simple query that runs
and
> > does a group by to count the number of requests from each IP in the
given
> > date range (past 24 hours). This query works great and takes only 2
> minutes
> > to run.
> >
> > Is there a better way to accomplish this without having to do INSERTS
into
> > the LogTable1 for each request?
> >
> > It would not work to have the ASP.NET program execute an UPDATE each
time
> to
> > increment the total number of request, since this would cause LOTSSS of
> > locking in the database layer.
> >
> > Thanks in advance.
> >
> > Arsen
> >
> >
>|||Hi Arsen,
You could put a DataTable in the Application Cache, and add records to it
with each Request. As for periodic updating, you could put a routine in the
Session_OnStart Sub that checks an Application DateTime variable, and at
certain intervals, inserts all the records from the DataTable into the
database and clears out the DataTable.
To be safe, you would want to add code to your Application_OnEnd sub to
update the database if the Application stops or times out; however, with 25M
requests per day, that might not be necessary.
--
HTH,
Kevin Spencer
.Net Developer
Microsoft MVP
Big things are made up
of lots of little things.
"Arsen V." <arsen.NoSpamPlease@.emergency24.com> wrote in message
news:#daQL6o6DHA.2908@.tk2msftngp13.phx.gbl...
> Hi Kevin,
> Do you suggest storing the TOTALs in the Cache?
> Or storing the actual requests: date and ip
> Would there be a locking problem?
> How to do the "periodic updates" to the database from the Cache?
> Thanks,
> Arsen
> "Kevin Spencer" <kevin@.takempis.com> wrote in message
> news:ezSYvso6DHA.1936@.TK2MSFTNGP12.phx.gbl...
> > You could store the data in your Application Cache, and update it
> > periodically to the database.
> >
> > --
> > HTH,
> > Kevin Spencer
> > .Net Developer
> > Microsoft MVP
> > Big things are made up
> > of lots of little things.
> >
> > "Arsen V." <arsen.NoSpamPlease@.emergency24.com> wrote in message
> > news:emdbHKo6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> > > Hello,
> > >
> > > What is the best way to accomplish the following.
> > >
> > > 1) An ASP.NET program (consiting of one file somepage.aspx) receives
> about
> > > 25,000,000 requests pay day.
> > >
> > > 2) The requests come from a limited set of IP addresses.
> > >
> > > 3) How to count the total number of request from each IP address per
> day.
> > >
> > > SQL Server 2000 is used on the backend.
> > >
> > > Currently we used the following architecture:
> > >
> > > * Each request to somepage.aspx generates an INSERT into a LogTable1
> > > * There is a clustered index on the LogTable1 on the dateStamp field
> which
> > > is of the type smalldatetime
> > > * The dateStamp field has a default that sets it to the getdate()
> > > * At the end of each day at 12:01AM there is a simple query that runs
> and
> > > does a group by to count the number of requests from each IP in the
> given
> > > date range (past 24 hours). This query works great and takes only 2
> > minutes
> > > to run.
> > >
> > > Is there a better way to accomplish this without having to do INSERTS
> into
> > > the LogTable1 for each request?
> > >
> > > It would not work to have the ASP.NET program execute an UPDATE each
> time
> > to
> > > increment the total number of request, since this would cause LOTSSS
of
> > > locking in the database layer.
> > >
> > > Thanks in advance.
> > >
> > > Arsen
> > >
> > >
> >
> >
>|||what i have done is cache the last 15 minutes. the cache has the ipaddress,
the start of the 15 min interval, pagename, and number of hits during the
interval. as most users cluster their hits, this cuts down the number of
inserts.
i flush the cache every 15 minutes of when too large.
you can then get daily or hourly stats from the db with simple queries.
-- bruce (sqlwork.com)
"Arsen V." <arsen.NoSpamPlease@.emergency24.com> wrote in message
news:emdbHKo6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> Hello,
> What is the best way to accomplish the following.
> 1) An ASP.NET program (consiting of one file somepage.aspx) receives about
> 25,000,000 requests pay day.
> 2) The requests come from a limited set of IP addresses.
> 3) How to count the total number of request from each IP address per day.
> SQL Server 2000 is used on the backend.
> Currently we used the following architecture:
> * Each request to somepage.aspx generates an INSERT into a LogTable1
> * There is a clustered index on the LogTable1 on the dateStamp field which
> is of the type smalldatetime
> * The dateStamp field has a default that sets it to the getdate()
> * At the end of each day at 12:01AM there is a simple query that runs and
> does a group by to count the number of requests from each IP in the given
> date range (past 24 hours). This query works great and takes only 2
minutes
> to run.
> Is there a better way to accomplish this without having to do INSERTS into
> the LogTable1 for each request?
> It would not work to have the ASP.NET program execute an UPDATE each time
to
> increment the total number of request, since this would cause LOTSSS of
> locking in the database layer.
> Thanks in advance.
> Arsen
>

How to count

Hello all!
What is a neat way to count the number of children for every parent in
code
below?

CREATE TABLE parent
(
row_id INTEGER
NOT NULL
IDENTITY(1,1)
PRIMARY KEY,

parent_name VARCHAR(100)
)
GO

CREATE TABLE children
(
parent_row_id INTEGER,
child_name VARCHAR(100)
)

INSERT INTO parent (parent_name) VALUES ('King')
INSERT INTO parent (parent_name) VALUES ('Farmer')
INSERT INTO children (child_name, parent_row_id) VALUES ('Prince', 1)Select parent_name, SubQuery.Sum_of_children from parent
INNER JOIN
(
Select parent_row_id,COUNT(*) from children group by
parent_row_id
) SubQuery
ON SubQuery.parent_row_id = parent.row_id

HTH, Jens Suessmeyer.|||SELECT parent_name,
(SELECT COUNT(parent_row_id)
FROM children
WHERE parent_row_id = row_id) AS number_of_children
FROM parent

Good luck,
Tony Sebion

"jonsjostedt@.hotmail.com" <jonsjostedt@.hotmail.com> wrote in message
news:1126624285.607326.195420@.g44g2000cwa.googlegr oups.com:

> Hello all!
> What is a neat way to count the number of children for every parent in
> code
> below?
> CREATE TABLE parent
> (
> row_id INTEGER
> NOT NULL
> IDENTITY(1,1)
> PRIMARY KEY,
> parent_name VARCHAR(100)
> )
> GO
> CREATE TABLE children
> (
> parent_row_id INTEGER,
> child_name VARCHAR(100)
> )
> INSERT INTO parent (parent_name) VALUES ('King')
> INSERT INTO parent (parent_name) VALUES ('Farmer')
> INSERT INTO children (child_name, parent_row_id) VALUES ('Prince', 1)

How to cotrol format of datetime attributes?

There is an attribute that have type DateTime, based on the field type in data source. It hasn't separate name column. The member caption is formatted as yyyy-MM-dd hh:mm:ss. Can I control the format of Member name without giving separate name column.

I assigned different format in the format field of key property window, but it had no effect. What I did wrong?

Hello. I do not think that you will have to pay any penalty from adding a new column in the data source view with a new format of your date column. I recommend to use the TSQL function CONVERT that have arguments for different date formats. Have a look at CONVERT in Books On Line, and you will see the complete list of different codes/arguments for different date formats.

HTH

Thomas Ivarsson

|||

Thank you,

I thought about more sofisticated solution, that can be used in multi culture environment without adding x additional fields with "formatting" of a datetime attribute.

I hoped, that the AS2005 is smarter as AS2005 and offers more possibilties.

Do you know what is the format field in key properties for?

How to cosolidate databases of SQL Express from multiple installations?

1. install a SQL Express instance such as myexpress;

2. create some databases with tables such as db1 and db2;

3. Reinstall SQL Express with the same instance name "myexpress". I assume we need reinstall SQL Express myexpress again becuase of bad operations.

4. Open the myexpress by management tool but I can not see db1 and db2 databases but the datafiles and logfiles are in the data directory.

The question is what I need to do so I can consolidate exited databases so they can be seen in "myexpress" instance by management tool?

hi,

WhyHere wrote:

1. install a SQL Express instance such as myexpress;
2. create some databases with tables such as db1 and db2;
3. Reinstall SQL Express with the same instance name "myexpress". I assume we need reinstall SQL Express myexpress again becuase of bad operations.
4. Open the myexpress by management tool but I can not see db1 and db2 databases but the datafiles and logfiles are in the data directory.
The question is what I need to do so I can consolidate exited databases so they can be seen in "myexpress" instance by management tool?

reinstalling the database engine will actually overwrite your used master database... the master database contains the "registration" of each user database so that the "clean" master does not include those registrations... you can re-attach your existing database files via the

CREATE DATABASE xxx

ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\xxx.mdf'

)

FOR ATTACH ;

statement... please have a look at http://msdn2.microsoft.com/en-us/library/ms176061.aspx for further info about it's synopsis... this will result re-registering a database from it's physical files...

BTW, overwriting the master database will also clean-up all logins information about all SQL Server and Windows NT principals.. associated (for SQL Server standard logins only) database users must also be cleaned/syncronized, for each user, via the sp_change_users_login system stored procedure, as reported in http://msdn2.microsoft.com/en-us/library/ms174378.aspx..

regards

How to corrupt data

Hello!
I'm intresed in all possible ways to corrupt data and log files in SQL Server 2000 except manually editing them.
Thank you in advance.you can hurt peaple more directly if that what you want|||Damn!

I'm not intrested in hurting anyone!
On the opposite - I work in QA team for the company that makes disaster recovery software for various databases. I need this information in order to test our product.|||So what you are really saying is that you need help doing your job.|||Correct! And I'm not ashamed of it. This is not a kind of information you can find in books or manuals.

how to corrupt a db

I want to purposely corrupt a db for some testing. Any ideas on how this can
be accomplished?
--
SQL2K SP3
TIA, ChrisRhow about mashing up the mdf using Notepad as your editor?
"ChrisR" wrote:
> I want to purposely corrupt a db for some testing. Any ideas on how this can
> be accomplished?
> --
> SQL2K SP3
> TIA, ChrisR
>
>|||> I want to purposely corrupt a db for some testing. Any ideas on how this
> can
> be accomplished?
sp_configure allow, 1
go
reconfigure with override
go
update dbo.sysindexes set FirstIAM = 1234
where id = OBJECT_ID('YourTableName')
go
sp_configure allow, 0
go
reconfigure with override|||Perfect. Thanks.
"RADl0PASlV" <RADl0PASlV@.seznam.cz> wrote in message
news:en9sdjz9EHA.3592@.TK2MSFTNGP09.phx.gbl...
> > I want to purposely corrupt a db for some testing. Any ideas on how this
> > can
> > be accomplished?
> sp_configure allow, 1
> go
> reconfigure with override
> go
> update dbo.sysindexes set FirstIAM = 1234
> where id = OBJECT_ID('YourTableName')
> go
> sp_configure allow, 0
> go
> reconfigure with override
>

how to corrupt a db

I want to purposely corrupt a db for some testing. Any ideas on how this can
be accomplished?
SQL2K SP3
TIA, ChrisR
how about mashing up the mdf using Notepad as your editor?
"ChrisR" wrote:

> I want to purposely corrupt a db for some testing. Any ideas on how this can
> be accomplished?
> --
> SQL2K SP3
> TIA, ChrisR
>
>
|||> I want to purposely corrupt a db for some testing. Any ideas on how this
> can
> be accomplished?
sp_configure allow, 1
go
reconfigure with override
go
update dbo.sysindexes set FirstIAM = 1234
where id = OBJECT_ID('YourTableName')
go
sp_configure allow, 0
go
reconfigure with override
|||Perfect. Thanks.
"RADl0PASlV" <RADl0PASlV@.seznam.cz> wrote in message
news:en9sdjz9EHA.3592@.TK2MSFTNGP09.phx.gbl...
> sp_configure allow, 1
> go
> reconfigure with override
> go
> update dbo.sysindexes set FirstIAM = 1234
> where id = OBJECT_ID('YourTableName')
> go
> sp_configure allow, 0
> go
> reconfigure with override
>

how to corrupt a db

I want to purposely corrupt a db for some testing. Any ideas on how this can
be accomplished?
SQL2K SP3
TIA, ChrisRhow about mashing up the mdf using Notepad as your editor?
"ChrisR" wrote:

> I want to purposely corrupt a db for some testing. Any ideas on how this c
an
> be accomplished?
> --
> SQL2K SP3
> TIA, ChrisR
>
>|||> I want to purposely corrupt a db for some testing. Any ideas on how this
> can
> be accomplished?
sp_configure allow, 1
go
reconfigure with override
go
update dbo.sysindexes set FirstIAM = 1234
where id = OBJECT_ID('YourTableName')
go
sp_configure allow, 0
go
reconfigure with override|||Perfect. Thanks.
"RADl0PASlV" <RADl0PASlV@.seznam.cz> wrote in message
news:en9sdjz9EHA.3592@.TK2MSFTNGP09.phx.gbl...
> sp_configure allow, 1
> go
> reconfigure with override
> go
> update dbo.sysindexes set FirstIAM = 1234
> where id = OBJECT_ID('YourTableName')
> go
> sp_configure allow, 0
> go
> reconfigure with override
>

how to correctly work with two (or more) databases?

Does anybody know how to work with two (or more) databases in SQL Server 2005; or where that information can be obtained? I searched online, in BOL and asked in this forum but with no success.

information in this posting does not work; results in invalid object name (source database) and/or database does not exist (destination database) errors:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=292125&SiteID=1

this post about the database does not exist has received no replies:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=295742&SiteID=1

Of course, both databases exist and their names are valid. Both can be accessed individually thru SSMS and a VB app I am coding. The problem is when trying to work with both of them.

Any information on the subject of working with multiple datatabases and/or links to said subject would be appreciated.

Hi,

ok noone answered because this *has* to be a typo or a logical error. Try to make sure that you specified the *right* schema and that you specified the right database and make sure that you are connecting to the right server and the right instance.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

I don't understand. How I can successfully run queries on each database individually; yet, fail when trying to run a query that refers to both of them?

Here are two version of a query I tried. The first reports error: database 'tf_1' does not exist. The second reports invalid object name 'rawtf_1.dbo.TstTable_1'.

These queries are being run in SSMS. BTW, the [brackets] are used because the fieldnames are keywords in other programs that I am using.

SELECT
[DateTime],
[Open],
[High],
[Low],
[Close],
[Volume]
INTO tf_1.dbo.TstTable_1
FROM TstTable_1

SELECT
[DateTime],
[Open],
[High],
[Low],
[Close],
[Volume]
INTO tf_1.dbo.TstTable_1
FROM rawtf_1.dbo.TstTable_1

When I run sp_databases, both databases are listed; a query such as:

SELECT * FROM TstTable_1

runs successfully.

How can I discover what I am doing wrong?

|||

Are both the objects in the dbo schema, or are they in a different schema? Do you have permission to access each of the databases in question?

When you run the query that is successful, which database are you running the query in? Also, what is your default schema? To get some of this information, simply run the following queries and post the output:

select db_name()

select schema_name()

select schema_name(schema_id), name from tf_1.sys.tables where name = 'TstTable_1'

select schema_name(schema_id), name from rawtf_1.sys.tables where name = 'TstTable_1'

select name from master.sys.databases where database_id > 4

|||

First, thanks for taking the time to help me with this.

Both are in the dbo schema. I have successfully run queries on both databases individually so I assume that means I have the necessary permissions. I'm the admin on the computer and I created both databases.

In SSMS, running your queries:

select db_name() // correct dbname when run on each database

select schema_name() // dbo when run on each database

select schema_name(schema_id), name from tf_1.sys.tables where name = 'TstTable_1' // could not run this because have yet to successfully copy the table from rawtf_1.mdf

select schema_name(schema_id), name from rawtf_1.sys.tables where name = 'TstTable_1' // ran this and got the dreaded Invalid object name 'rawtf_1.sys.tables'. error message.

select name from master.sys.databases where database_id > 4 // ran this on each database and both times successfully listed both databases

|||

hmmm...well, are you by chance on a case-sensitive server? Is the database name all lower-case, or mixed-case?

Also, what compatibility level is each of these databases running under?

Something must be going on somewhere, this is definately something you should be able to (and can normally) do...hopefully we'll get it figured out...

|||

How can I tell if I'm on a case-sensitive server? I tried running the SELECT INTO query using upper case; same result database 'TF_1' does not exist. Both databases were created with all lowercase names.

Both databases have compatibility set to SQL Server 2005 (90).

I have discovered something interesting tho. In SSMS, I opened each databases properties dialog box and lined them up side by side. On the Options tab, in State options: for rawtf_1, Database State is Normal; for tf_1, there is no value. In the General tab, rawtf_1 Status is Normal; for tf_1, it is Shutdown, Normal.

|||

For the heck of it, I again tried deleting and recreating the tf_1 database. This time, fortunately, the SELECT INTO query worked. Unfortunately, I am now getting the following error message when I tried open TstTable_1 in either database in SSMS:

Class does not support aggregation (or class object is remote) (Exception from HRESULT: 0x80040110 (CLASS_E_NOGGREGATION)) (Microsoft.SqlServer.SqlTools.VSIntegration)

Other posts on the subject suggested uninstalling and reinstalling. Not a happy prospect but perhaps that is my only option at this point.

|||

Trying to uninstall and reinstall sql server only made things worse. I've moved this discussion to Setup & Upgrade; tho I'll probably be offline a day or two while I do a complete reinstallation of windows xp, programs and data. what a bummer.

http://forums.microsoft.com/MSDN/AddPost.aspx?ForumID=95&SiteID=1

|||BTW, did a complete reinstall of winxp and sql server 2005 dev edition. That just generated new problems.|||

After more than a week of aggravation and complete reinstalls of winxp and sql server 2005 dev edition, I'm back to the same situation: I cannot run a query involving two databases. Does anybody know what the problem could be? Or does anyone know of any other dbms that can easily work with two databases? Is the sql server 2000 any less aggravating than 2005?

how to correctly work with two (or more) databases?

Does anybody know how to work with two (or more) databases in SQL Server 2005; or where that information can be obtained? I searched online, in BOL and asked in this forum but with no success.

information in this posting does not work; results in invalid object name (source database) and/or database does not exist (destination database) errors:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=292125&SiteID=1

this post about the database does not exist has received no replies:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=295742&SiteID=1

Of course, both databases exist and their names are valid. Both can be accessed individually thru SSMS and a VB app I am coding. The problem is when trying to work with both of them.

Any information on the subject of working with multiple datatabases and/or links to said subject would be appreciated.

Hi,

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

I don't understand. How I can successfully run queries on each database individually; yet, fail when trying to run a query that refers to both of them?

Here are two version of a query I tried. The first reports error: database 'tf_1' does not exist. The second reports invalid object name 'rawtf_1.dbo.TstTable_1'.

These queries are being run in SSMS. BTW, the [brackets] are used because the fieldnames are keywords in other programs that I am using.

SELECT
[DateTime],
[Open],
[High],
[Low],
[Close],
[Volume]
INTO tf_1.dbo.TstTable_1
FROM TstTable_1

SELECT
[DateTime],
[Open],
[High],
[Low],
[Close],
[Volume]
INTO tf_1.dbo.TstTable_1
FROM rawtf_1.dbo.TstTable_1

When I run sp_databases, both databases are listed; a query such as:

SELECT * FROM TstTable_1

runs successfully.

How can I discover what I am doing wrong?

|||

Are both the objects in the dbo schema, or are they in a different schema? Do you have permission to access each of the databases in question?

select db_name()

select schema_name()

select schema_name(schema_id), name from tf_1.sys.tables where name = 'TstTable_1'

select schema_name(schema_id), name from rawtf_1.sys.tables where name = 'TstTable_1'

select name from master.sys.databases where database_id > 4

|||

First, thanks for taking the time to help me with this.

In SSMS, running your queries:

select db_name() // correct dbname when run on each database

select schema_name() // dbo when run on each database

select schema_name(schema_id), name from tf_1.sys.tables where name = 'TstTable_1' // could not run this because have yet to successfully copy the table from rawtf_1.mdf

select schema_name(schema_id), name from rawtf_1.sys.tables where name = 'TstTable_1' // ran this and got the dreaded Invalid object name 'rawtf_1.sys.tables'. error message.

select name from master.sys.databases where database_id > 4 // ran this on each database and both times successfully listed both databases

|||

hmmm...well, are you by chance on a case-sensitive server? Is the database name all lower-case, or mixed-case?

Also, what compatibility level is each of these databases running under?

Something must be going on somewhere, this is definately something you should be able to (and can normally) do...hopefully we'll get it figured out...

|||

Both databases have compatibility set to SQL Server 2005 (90).

|||

Class does not support aggregation (or class object is remote) (Exception from HRESULT: 0x80040110 (CLASS_E_NOGGREGATION)) (Microsoft.SqlServer.SqlTools.VSIntegration)

Other posts on the subject suggested uninstalling and reinstalling. Not a happy prospect but perhaps that is my only option at this point.

|||

http://forums.microsoft.com/MSDN/AddPost.aspx?ForumID=95&SiteID=1

|||BTW, did a complete reinstall of winxp and sql server 2005 dev edition. That just generated new problems.|||

How to correctly update a table which values can be either inserted/updated/deleted on upd

Hi SQL fans,
I realized that I often encounter the same situation in a relation
database context, where I really don't know what to do. Here is an
example, where I have 2 tables as follow:
________________________
__________________ | PortfolioTitle
|
| Portfolio |
+---+
+--+ | tfolio_id (int)
|
| folio_id (int) |<<-PK--FK--| tfolio_idfolio (int)
|
| folio_name (varchar) | | tfolio_idtitle (int)
|--FK--PK->>[ Titles]
+--+ | tfolio_weight
(decimal(6,5)) |
+---+
Note that I also have a "Titles" tables (hence the tfolio_idtitle
link).
My problem is : When I update a portfolio, I must update all the
associated titles in it. That means that titles can be either removed
from the portfolio (a folio does not support the title anymore), added
to it (a new title is supported by the folio) or simply updated (a
title stays in the portfolio, but has its weight changed)
For example, if the portfolio #2 would contain :
[ PortfolioTitle ]
id | idFolio | idTitre | poids
1 2 1 10
2 2 2 20
3 2 3 30
and I must update the PortfolioTitle based on these values :
idFolio | idTitre | poids
2 2 20
2 3 35
2 4 40
then I should
1 ) remove the title #1 from the folio by deleting its entry in the
PortfolioTitle table
2 ) update the title #2 (weight from 30 to 35)
3 ) add the title #4 to the folio
For now, the only way I've found to do this is delete all the entries
of the related folio (e.g.: DELETE TitrePortefeuille WHERE idFolio = 2), and then insert new values for each entry based on the new given
values.
Is there a way to better manage this by detecting which value has to be
inserted/updated/deleted?
And this applies to many situation :(
If you need other examples, I can give you.
thanks a lot!
ibizaerrr...I realized the diagram is all messed up :\
here it is : http://img156.imageshack.us/img156/5096/model8al.gif|||errr...I realized the diagram is all messed up :\
here it is : http://img156.imageshack.us/img156/5096/model8al.gif|||On 16 Feb 2006 10:39:45 -0800, ibiza wrote:
>Hi SQL fans,
(snip)
>My problem is : When I update a portfolio, I must update all the
>associated titles in it. That means that titles can be either removed
>from the portfolio (a folio does not support the title anymore), added
>to it (a new title is supported by the folio) or simply updated (a
>title stays in the portfolio, but has its weight changed)
>For example, if the portfolio #2 would contain :
>[ PortfolioTitle ]
>id | idFolio | idTitre | poids
>1 2 1 10
>2 2 2 20
>3 2 3 30
>and I must update the PortfolioTitle based on these values :
>idFolio | idTitre | poids
>2 2 20
>2 3 35
>2 4 40
>then I should
>1 ) remove the title #1 from the folio by deleting its entry in the
>PortfolioTitle table
>2 ) update the title #2 (weight from 30 to 35)
>3 ) add the title #4 to the folio
Hi ibiza,
Deleting all rows, then re-inserting (as you do now) is one of the two
popular ways to acheive this. The second is (assuming that the new
values are stored in the table NewFolioTitles):
-- Step 1: DELETE rows that are no longer needed
DELETE FROM FolioTitles
WHERE NOT EXISTS
(SELECT *
FROM NewFolioTitles AS n
WHERE n.idFolio = FolioTitles.idFolio
AND n.idTitre = FolioTitles.idTitre)
-- Step 2: UPDATE rows that have been changed
UPDATE FolioTitles
SET poids = (SELECT poids
FROM NewFolioTitles AS n
WHERE n.idFolio = FolioTitles.idFolio
AND n.idTitre = FolioTitles.idTitre)
WHERE EXISTS
(SELECT *
FROM NewFolioTitles AS n
WHERE n.idFolio = FolioTitles.idFolio
AND n.idTitre = FolioTitles.idTitre
AND n.poids <> FolioTitles.poids)
-- Or, as an alternative, use the version below;
-- this is shorter and often faster, but uses
-- proprietary code and is therefor less portable
-- UPDATE f
-- SET poids = n.poids
-- FROM FolioTitles AS f
-- INNER JOIN NewFolioTitles AS n
-- ON n.idFolio = f.idFolio
-- AND n.idTitre = f.idTitre
-- WHERE n.poids <> f.poids
-- Step 3: INSERT new rows
INSERT INTO FolioTitles (idFolio, idTitre, poids)
SELECT n.idFolio, n.idTitre, i.poids
FROM NewFolioTitles AS n
LEFT JOIN FolioTitles AS f
ON f.idFolio = n.idFolio
AND f.idTitre = n.idTitre
WHERE f.idFolio IS NULL
This is just the basic outline - you should enclose it in a transaction
and add proper error handling.
--
Hugo Kornelis, SQL Server MVP|||Hi Hugo,
thank you very much for your excellent reply.
Is there a big performance difference between the two methods? I guess
the only difference is that the identity numbers increment a lot
quicker with the first method of deleting a batch then reinserting a
batch, even if only a few records differ.
If I have to run this kind of update quite frequently (once/twice a
day, with thousands of records), what method would you suggest me?
Thanks again! :)
ibiza|||On 16 Feb 2006 12:54:19 -0800, ibiza wrote:
>Hi Hugo,
>thank you very much for your excellent reply.
>Is there a big performance difference between the two methods? I guess
>the only difference is that the identity numbers increment a lot
>quicker with the first method of deleting a batch then reinserting a
>batch, even if only a few records differ.
Hi ibiza,
Why would you even want to have an identity on the linking table
FolioTitles? Are there many other tables that refer to rows in this
table? I don't think so, since that would make deleting and re-inserting
rows a very bad idea!
If not, then stick to using only the natural key (FolioID + TitreID) and
leave the identity column out.
>If I have to run this kind of update quite frequently (once/twice a
>day, with thousands of records), what method would you suggest me?
If performance matters, than you should test both versions on your
hardware, with your data. That's the only way to get relevant data.
(My recommended test procedure is: clear cache; run one method a few
times, then clear cache again and run second method the same number of
times. Do thin on a test system that is as much as possible like your
production system. Compare average execution times).
--
Hugo Kornelis, SQL Server MVP|||ibiza (lambertb@.gmail.com) writes:
> thank you very much for your excellent reply.
> Is there a big performance difference between the two methods? I guess
> the only difference is that the identity numbers increment a lot
> quicker with the first method of deleting a batch then reinserting a
> batch, even if only a few records differ.
Why is there an IDENTITY columns at all? Surely a pair of
(foliotitle_idfolio, foliotitle_idtable) can only appear once in table?
Then this should be the primary key, and not any identity column.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Yes, you are both right, I will remove it then.
I thought it was always a good idea to have an identity primary key on
any table.
Thanks for the tip!|||ibiza (lambertb@.gmail.com) writes:
> Yes, you are both right, I will remove it then.
> I thought it was always a good idea to have an identity primary key on
> any table.
If you ask some people, they will tell you that it is never a good idea!
Personally, I say it is a good idea when there is no useful natural key,
which often is the case for base entities: customers, financial
instruments, addresses etc. However for connection table, or tables
describing composed entities, articifical keys are usually not needed.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

How to correctly update a table which values can be either inserted/updated/deleted on upd

Hi SQL fans,

I realized that I often encounter the same situation in a relation
database context, where I really don't know what to do. Here is an
example, where I have 2 tables as follow:

+-------------+

Note that I also have a "Titles" tables (hence the tfolio_idtitle
link).

My problem is : When I update a portfolio, I must update all the
associated titles in it. That means that titles can be either removed
from the portfolio (a folio does not support the title anymore), added
to it (a new title is supported by the folio) or simply updated (a
title stays in the portfolio, but has its weight changed)

For example, if the portfolio #2 would contain :

[ PortfolioTitle ]
id | idFolio | idTitre | poids
1 2 1 10
2 2 2 20
3 2 3 30

and I must update the PortfolioTitle based on these values :

idFolio | idTitre | poids
2 2 20
2 3 35
2 4 40

then I should
1 ) remove the title #1 from the folio by deleting its entry in the
PortfolioTitle table
2 ) update the title #2 (weight from 30 to 35)
3 ) add the title #4 to the folio

For now, the only way I've found to do this is delete all the entries
of the related folio (e.g.: DELETE TitrePortefeuille WHERE idFolio =
2), and then insert new values for each entry based on the new given
values.

Is there a way to better manage this by detecting which value has to be
inserted/updated/deleted?

And this applies to many situation :(

If you need other examples, I can give you.

thanks a lot!

ibizaerrr...I realized the diagram is all messed up :\

here it is : http://img156.imageshack.us/img156/5096/model8al.gif|||errr...I realized the diagram is all messed up :\

here it is : http://img156.imageshack.us/img156/5096/model8al.gif|||On 16 Feb 2006 10:39:45 -0800, ibiza wrote:

>Hi SQL fans,
(snip)
>My problem is : When I update a portfolio, I must update all the
>associated titles in it. That means that titles can be either removed
>from the portfolio (a folio does not support the title anymore), added
>to it (a new title is supported by the folio) or simply updated (a
>title stays in the portfolio, but has its weight changed)
>For example, if the portfolio #2 would contain :
>[ PortfolioTitle ]
>id | idFolio | idTitre | poids
>1 2 1 10
>2 2 2 20
>3 2 3 30
>and I must update the PortfolioTitle based on these values :
>idFolio | idTitre | poids
>2 2 20
>2 3 35
>2 4 40
>then I should
>1 ) remove the title #1 from the folio by deleting its entry in the
>PortfolioTitle table
>2 ) update the title #2 (weight from 30 to 35)
>3 ) add the title #4 to the folio

Hi ibiza,

Deleting all rows, then re-inserting (as you do now) is one of the two
popular ways to acheive this. The second is (assuming that the new
values are stored in the table NewFolioTitles):

-- Step 1: DELETE rows that are no longer needed
DELETE FROM FolioTitles
WHERE NOT EXISTS
(SELECT *
FROM NewFolioTitles AS n
WHERE n.idFolio = FolioTitles.idFolio
AND n.idTitre = FolioTitles.idTitre)

-- Step 2: UPDATE rows that have been changed
UPDATE FolioTitles
SET poids =
(SELECT poids
FROM NewFolioTitles AS n
WHERE n.idFolio = FolioTitles.idFolio
AND n.idTitre = FolioTitles.idTitre)
WHERE EXISTS
(SELECT *
FROM NewFolioTitles AS n
WHERE n.idFolio = FolioTitles.idFolio
AND n.idTitre = FolioTitles.idTitre
AND n.poids <> FolioTitles.poids)
-- Or, as an alternative, use the version below;
-- this is shorter and often faster, but uses
-- proprietary code and is therefor less portable
-- UPDATE f
-- SET poids = n.poids
-- FROM FolioTitles AS f
-- INNER JOIN NewFolioTitles AS n
-- ON n.idFolio = f.idFolio
-- AND n.idTitre = f.idTitre
-- WHERE n.poids <> f.poids

-- Step 3: INSERT new rows
INSERT INTO FolioTitles (idFolio, idTitre, poids)
SELECT n.idFolio, n.idTitre, i.poids
FROM NewFolioTitles AS n
LEFT JOIN FolioTitles AS f
ON f.idFolio = n.idFolio
AND f.idTitre = n.idTitre
WHERE f.idFolio IS NULL

This is just the basic outline - you should enclose it in a transaction
and add proper error handling.

--
Hugo Kornelis, SQL Server MVP|||Hi Hugo,

thank you very much for your excellent reply.
Is there a big performance difference between the two methods? I guess
the only difference is that the identity numbers increment a lot
quicker with the first method of deleting a batch then reinserting a
batch, even if only a few records differ.

If I have to run this kind of update quite frequently (once/twice a
day, with thousands of records), what method would you suggest me?

Thanks again! :)

ibiza|||On 16 Feb 2006 12:54:19 -0800, ibiza wrote:

>Hi Hugo,
>thank you very much for your excellent reply.
>Is there a big performance difference between the two methods? I guess
>the only difference is that the identity numbers increment a lot
>quicker with the first method of deleting a batch then reinserting a
>batch, even if only a few records differ.

Hi ibiza,

Why would you even want to have an identity on the linking table
FolioTitles? Are there many other tables that refer to rows in this
table? I don't think so, since that would make deleting and re-inserting
rows a very bad idea!

If not, then stick to using only the natural key (FolioID + TitreID) and
leave the identity column out.

>If I have to run this kind of update quite frequently (once/twice a
>day, with thousands of records), what method would you suggest me?

If performance matters, than you should test both versions on your
hardware, with your data. That's the only way to get relevant data.

(My recommended test procedure is: clear cache; run one method a few
times, then clear cache again and run second method the same number of
times. Do thin on a test system that is as much as possible like your
production system. Compare average execution times).

--
Hugo Kornelis, SQL Server MVP|||ibiza (lambertb@.gmail.com) writes:
> thank you very much for your excellent reply.
> Is there a big performance difference between the two methods? I guess
> the only difference is that the identity numbers increment a lot
> quicker with the first method of deleting a batch then reinserting a
> batch, even if only a few records differ.

Why is there an IDENTITY columns at all? Surely a pair of
(foliotitle_idfolio, foliotitle_idtable) can only appear once in table?
Then this should be the primary key, and not any identity column.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Yes, you are both right, I will remove it then.

I thought it was always a good idea to have an identity primary key on
any table.

Thanks for the tip!|||ibiza (lambertb@.gmail.com) writes:
> Yes, you are both right, I will remove it then.
> I thought it was always a good idea to have an identity primary key on
> any table.

If you ask some people, they will tell you that it is never a good idea!

Personally, I say it is a good idea when there is no useful natural key,
which often is the case for base entities: customers, financial
instruments, addresses etc. However for connection table, or tables
describing composed entities, articifical keys are usually not needed.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How to correctly propogate data back to the database

I have the following code but do not know the best way to return the updated
DataTable back to the database. I believe I can use the Update method of the
Data Adapter, BUT if true, I also believe I have to 'long-hand' write code
for each individual column data that's being added.....this seems a bit
daft considering that the data is already in the disconnected data table.
Have I lost the plot?? Based on the code below, what is the correct
approach?

Note: sqlcnn is defined at module level.

Public Sub AddRequest(ByVal Eng As String, ByVal Bran As String, ByVal Req
As String) Implements IHelpSC.AddRequest

Dim dtNew As New DataTable("dtNew")
Dim drNew As DataRow
sqlda = New SqlDataAdapter("SELECT * FROM ActiveCalls", sqlcnn)
sqlda.Fill(dtNew)

'Add and populate the new datarow with
'data passed into this subroutine

drNew = dtNew.NewRow
drNew("CallTime") = Format(Date.Now, "dd MMM yyyy").ToString
drNew("Engineer") = Eng
drNew("Branch") = Bran
drNew("Request") = Req
dtNew.Rows.Add(drNew)

End Sub

Hope one of you wizards can help.

Rgds....and Merry Christmas.

PhilPhil (Phil@.nospam.com) writes:
> I have the following code but do not know the best way to return the
> updated DataTable back to the database. I believe I can use the Update
> method of the Data Adapter, BUT if true, I also believe I have to
> 'long-hand' write code for each individual column data that's being
> added.....this seems a bit daft considering that the data is already in
> the disconnected data table.

Yes and no.

First, if memory serves, you don't have to write any extra code, if
you use the default .Update method on the data adapter, but it will
include all columns. But you are better of asking about that in group
like microsoft.public.dotnet.framework.adonet that is devoted to ADO .Net.
Or, instead of asking, just conduct an experiment.

Then comes the next question, whether you actually want it. For a
application of any size, it is usually best to perform all access to
SQL Server through stored procedure. In this way, users does not have
to have direct access to the tables, but only access to the stored
procedures. This makes a big difference for the security of the database.
In many shops, the DBA will not permit anything but stored procedures
anyway.

And if you use stored procedures, it follows by necessity that if you
add another column to a query, that you will have to add it to the
SelectCommand, UpdateCommand and InsertCommand of the DataAdapater as
well. And, yes, that means some extra overhead when you add a new
column, but it is not really a big deal.

Finally some notes about your code:

> sqlda = New SqlDataAdapter("SELECT * FROM ActiveCalls", sqlcnn)

This may be just an example, but permit me to point out that "SELECT *"
should never occur in production code. It may look convenient, but it
isn't. It gives you an extra overhead of retrieving columns, you don't
nead. And it makes it very difficult to find out if a column is actually
used or not, in case you are looking into to drop a column.

> drNew("CallTime") = Format(Date.Now, "dd MMM yyyy").ToString

I don't really know what happens in the end, but you should probably
pass dates as dates. If you format dates and passes them as strings to
SQL Server, they may not be understood by SQL Server, if there are
some unexpected dateformat and langauge settings.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

It sounds like you know what you are talking about! :-).....and you raise
some excellent points. I am extremely new to db programming and just finding
my feet. The comments you make about using 'SELECT *' and stored procedures
I have read in my 'programming databases using .NET ' book. THANKS for the
confirmation. I will eventually change to using stored procedures but I am
trying to pick up the basics regarding 'connected classes' and 'disconnected
classes', Data Adapters, Data Tables, Datasets etc etc etc.

I will take a look at the suggested newsgroups, thanks for your response.

All the best,

Phil

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95CB77CF3AB42Yazorman@.127.0.0.1...
> Phil (Phil@.nospam.com) writes:
> > I have the following code but do not know the best way to return the
> > updated DataTable back to the database. I believe I can use the Update
> > method of the Data Adapter, BUT if true, I also believe I have to
> > 'long-hand' write code for each individual column data that's being
> > added.....this seems a bit daft considering that the data is already in
> > the disconnected data table.
> Yes and no.
> First, if memory serves, you don't have to write any extra code, if
> you use the default .Update method on the data adapter, but it will
> include all columns. But you are better of asking about that in group
> like microsoft.public.dotnet.framework.adonet that is devoted to ADO .Net.
> Or, instead of asking, just conduct an experiment.
> Then comes the next question, whether you actually want it. For a
> application of any size, it is usually best to perform all access to
> SQL Server through stored procedure. In this way, users does not have
> to have direct access to the tables, but only access to the stored
> procedures. This makes a big difference for the security of the database.
> In many shops, the DBA will not permit anything but stored procedures
> anyway.
> And if you use stored procedures, it follows by necessity that if you
> add another column to a query, that you will have to add it to the
> SelectCommand, UpdateCommand and InsertCommand of the DataAdapater as
> well. And, yes, that means some extra overhead when you add a new
> column, but it is not really a big deal.
> Finally some notes about your code:
> > sqlda = New SqlDataAdapter("SELECT * FROM ActiveCalls", sqlcnn)
> This may be just an example, but permit me to point out that "SELECT *"
> should never occur in production code. It may look convenient, but it
> isn't. It gives you an extra overhead of retrieving columns, you don't
> nead. And it makes it very difficult to find out if a column is actually
> used or not, in case you are looking into to drop a column.
> > drNew("CallTime") = Format(Date.Now, "dd MMM yyyy").ToString
> I don't really know what happens in the end, but you should probably
> pass dates as dates. If you format dates and passes them as strings to
> SQL Server, they may not be understood by SQL Server, if there are
> some unexpected dateformat and langauge settings.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

how to correctly finding memory usage.

i have a box running very slow. it has 16 gb of ram, running
a SQL 2005 Enterprise Edition (64-bit), build 9.00.3161.00, SP2 on
a win2003 R2 enterprise x64 with SP2.
during the non-busy hours, Task Manager shows 4-50 KB ram available. what
is the right way to free up the memory? is there a way to see what are the
resources not released? (like top or ps -ef |grep....)
tried the neat Activity Monitor, nothing shows where the 15 gb are used.
thanks.You need to ensure that the max memory option of sql server is set to leave
memory for the OS and any other apps that may run on the server. With 16GB
total I would recommend setting it to 14GB and see how that goes. You can
set this via sp_configure or SSMS.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:FD5F89D0-9942-4BB1-A08C-D87516983F85@.microsoft.com...
>i have a box running very slow. it has 16 gb of ram, running
> a SQL 2005 Enterprise Edition (64-bit), build 9.00.3161.00, SP2 on
> a win2003 R2 enterprise x64 with SP2.
> during the non-busy hours, Task Manager shows 4-50 KB ram available.
> what
> is the right way to free up the memory? is there a way to see what are
> the
> resources not released? (like top or ps -ef |grep....)
> tried the neat Activity Monitor, nothing shows where the 15 gb are used.
> thanks.|||setting the max memory is great.
how will i know what will be occupying the 14 GB?|||That 14GB will be used by the buffer pool of the instance. For the
distribution of the buffer pool buffers, I find DBCC MEMORYSTATUS handy.
Linchi
"light_wt" wrote:
> setting the max memory is great.
> how will i know what will be occupying the 14 GB?
>|||wow...
thank you so much Linchi. that command is so cool.
i've g* and played with it a bit. it pulls very useful info.
just wondering here, if i want to pin point to see if certain BI things
(such as: cubes, proc, partition, aggregation, and etc) is the casue of the
memory issue. will there be a way to zoom in/ narrow down the issue?
afterall, the box is a sql05 for data mining...
Thanks.|||The memory will all be used by SQL Servers buffer pool. To see how that is
broken down have a look at the sys.dm_os_buffer_descriptors DMV in
BooksOnLine.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:790A960F-9625-447C-A09E-A1818101F1FD@.microsoft.com...
> setting the max memory is great.
> how will i know what will be occupying the 14 GB?
>

How to correct registry in renamed server

If a log shipping server must take on the name of another server during
failover, the name in the server registry will not be the same as the name of
the server. What is the best way to correct this?
Regards,
Jamie
Found it
SQL Server 2000
sp_dropserver 'old server name'
sp_addserver 'new server name', 'local'
Delete the entry in Enterprise Manager for the old server name and then
you’re golden. You will have to stop and start the SQL Server service to
complete the process.
Regards,
Jamie
"thejamie" wrote:

> If a log shipping server must take on the name of another server during
> failover, the name in the server registry will not be the same as the name of
> the server. What is the best way to correct this?
> --
> Regards,
> Jamie
|||And if you are on 2000, you need to handle the Agent jobs as well:
http://www.karaszi.com/SQLServer/info_change_server_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:DFACE678-CD17-4B17-9E15-D5FDC74A0A2C@.microsoft.com...[vbcol=seagreen]
> Found it
> SQL Server 2000
> sp_dropserver 'old server name'
> sp_addserver 'new server name', 'local'
> Delete the entry in Enterprise Manager for the old server name and then
> you’re golden. You will have to stop and start the SQL Server service to
> complete the process.
> --
> Regards,
> Jamie
>
> "thejamie" wrote:

How to correct registry in renamed server

If a log shipping server must take on the name of another server during
failover, the name in the server registry will not be the same as the name o
f
the server. What is the best way to correct this?
--
Regards,
JamieFound it
SQL Server 2000
sp_dropserver 'old server name'
sp_addserver 'new server name', 'local'
Delete the entry in Enterprise Manager for the old server name and then
you’re golden. You will have to stop and start the SQL Server service to
complete the process.
--
Regards,
Jamie
"thejamie" wrote:

> If a log shipping server must take on the name of another server during
> failover, the name in the server registry will not be the same as the name
of
> the server. What is the best way to correct this?
> --
> Regards,
> Jamie|||And if you are on 2000, you need to handle the Agent jobs as well:
http://www.karaszi.com/SQLServer/in...server_name.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:DFACE678-CD17-4B17-9E15-D5FDC74A0A2C@.microsoft.com...[vbcol=seagreen]
> Found it
> SQL Server 2000
> sp_dropserver 'old server name'
> sp_addserver 'new server name', 'local'
> Delete the entry in Enterprise Manager for the old server name and then
> you’re golden. You will have to stop and start the SQL Server service to
> complete the process.
> --
> Regards,
> Jamie
>
> "thejamie" wrote:
>

How to correct registry in renamed server

If a log shipping server must take on the name of another server during
failover, the name in the server registry will not be the same as the name of
the server. What is the best way to correct this?
--
Regards,
JamieFound it
SQL Server 2000
sp_dropserver 'old server name'
sp_addserver 'new server name', 'local'
Delete the entry in Enterprise Manager for the old server name and then
youâ're golden. You will have to stop and start the SQL Server service to
complete the process.
--
Regards,
Jamie
"thejamie" wrote:
> If a log shipping server must take on the name of another server during
> failover, the name in the server registry will not be the same as the name of
> the server. What is the best way to correct this?
> --
> Regards,
> Jamie|||And if you are on 2000, you need to handle the Agent jobs as well:
http://www.karaszi.com/SQLServer/info_change_server_name.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:DFACE678-CD17-4B17-9E15-D5FDC74A0A2C@.microsoft.com...
> Found it
> SQL Server 2000
> sp_dropserver 'old server name'
> sp_addserver 'new server name', 'local'
> Delete the entry in Enterprise Manager for the old server name and then
> youâ're golden. You will have to stop and start the SQL Server service to
> complete the process.
> --
> Regards,
> Jamie
>
> "thejamie" wrote:
>> If a log shipping server must take on the name of another server during
>> failover, the name in the server registry will not be the same as the name of
>> the server. What is the best way to correct this?
>> --
>> Regards,
>> Jamie

How to correct index problems in system tables

Hi friends, I have one corrupted index in sysobjects from
one user database (not master). The SQL Server don't
accept to use DBCC or drop and recreate the index to
system tables. In my backup the index alread is corrupted.
How can I fix this problem? I'm using SQL 2000 with the
last SP.DBCC DBREINDEX would help in rebuilding the corrupted index. I notice you
say the SQL Server doesn't accept DBCC though.
Why is this..? Permissions .. ?
--
HTH
Ryan Waight, MCDBA, MCSE
"Khayman" <himura@.click21.com.br> wrote in message
news:08d101c3b997$f77eabe0$a401280a@.phx.gbl...
> Hi friends, I have one corrupted index in sysobjects from
> one user database (not master). The SQL Server don't
> accept to use DBCC or drop and recreate the index to
> system tables. In my backup the index alread is corrupted.
> How can I fix this problem? I'm using SQL 2000 with the
> last SP.|||Hi ,
Please use the below undocumented system procedure This stored procedure can
be used to fix a corruption in a system table.
sp_fixindex dbname,tabname,indid
Before using this stored procedure the database has to be in single user
mode
See this link for more information: "How can I fix a corruption in a system
table?"
http://www.windows2000faq.com/Articles/Index.cfm?ArticleID=14051
Thanks
Hari
MCDBA
"Khayman" <himura@.click21.com.br> wrote in message
news:08d101c3b997$f77eabe0$a401280a@.phx.gbl...
> Hi friends, I have one corrupted index in sysobjects from
> one user database (not master). The SQL Server don't
> accept to use DBCC or drop and recreate the index to
> system tables. In my backup the index alread is corrupted.
> How can I fix this problem? I'm using SQL 2000 with the
> last SP.|||i have tried this yet.
This only works for nonclustered indexes, but the
corrupted index is clustered.
Khayman.
>--Original Message--
>Hi ,
>Please use the below undocumented system procedure This
stored procedure can
>be used to fix a corruption in a system table.
>sp_fixindex dbname,tabname,indid
>Before using this stored procedure the database has to be
in single user
>mode
>See this link for more information: "How can I fix a
corruption in a system
>table?"
>http://www.windows2000faq.com/Articles/Index.cfm?
ArticleID=14051
>Thanks
>Hari
>MCDBA
>
>"Khayman" <himura@.click21.com.br> wrote in message
>news:08d101c3b997$f77eabe0$a401280a@.phx.gbl...
>> Hi friends, I have one corrupted index in sysobjects
from
>> one user database (not master). The SQL Server don't
>> accept to use DBCC or drop and recreate the index to
>> system tables. In my backup the index alread is
corrupted.
>> How can I fix this problem? I'm using SQL 2000 with the
>> last SP.
>
>.
>|||In Microsoft Documentation says:
"DBCC DBREINDEX is not supported for use on system tables."
I don't know why.
Khayman
>--Original Message--
>DBCC DBREINDEX would help in rebuilding the corrupted
index. I notice you
>say the SQL Server doesn't accept DBCC though.
>Why is this..? Permissions .. ?
>--
>HTH
>Ryan Waight, MCDBA, MCSE
>"Khayman" <himura@.click21.com.br> wrote in message
>news:08d101c3b997$f77eabe0$a401280a@.phx.gbl...
>> Hi friends, I have one corrupted index in sysobjects
from
>> one user database (not master). The SQL Server don't
>> accept to use DBCC or drop and recreate the index to
>> system tables. In my backup the index alread is
corrupted.
>> How can I fix this problem? I'm using SQL 2000 with the
>> last SP.
>
>.
>|||Please contact Product Support who are best placed to help you with this.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Khayman" <himura@.click21.com.br> wrote in message
news:000101c3b9b7$53f1dfe0$a301280a@.phx.gbl...
> i have tried this yet.
> This only works for nonclustered indexes, but the
> corrupted index is clustered.
> Khayman.
>
> >--Original Message--
> >Hi ,
> >
> >Please use the below undocumented system procedure This
> stored procedure can
> >be used to fix a corruption in a system table.
> >
> >sp_fixindex dbname,tabname,indid
> >
> >Before using this stored procedure the database has to be
> in single user
> >mode
> >
> >See this link for more information: "How can I fix a
> corruption in a system
> >table?"
> >
> >http://www.windows2000faq.com/Articles/Index.cfm?
> ArticleID=14051
> >
> >Thanks
> >Hari
> >MCDBA
> >
> >
> >"Khayman" <himura@.click21.com.br> wrote in message
> >news:08d101c3b997$f77eabe0$a401280a@.phx.gbl...
> >> Hi friends, I have one corrupted index in sysobjects
> from
> >> one user database (not master). The SQL Server don't
> >> accept to use DBCC or drop and recreate the index to
> >> system tables. In my backup the index alread is
> corrupted.
> >> How can I fix this problem? I'm using SQL 2000 with the
> >> last SP.
> >
> >
> >.
> >|||We have the same issue. Did you resolve your problem? How did it go?

how to correct error on server side

I have a report that uses a stored procedure (DB2). I sometimes get this
error when running certain parameter combinations from the report deployed on
a portal.
"Server Application Unavailable
The web application you are attempting to access on this web server is
currently unavailable. Please hit the "Refresh" button in your web browser to
retry your request.
Administrator Note: An error message detailing the cause of this specific
request failure can be found in the application event log of the web server.
Please review this log entry to discover what caused this error to occur. "
I am not sure where to intercept this. I assume it is coming from the
reporting services server but I dont know where to step in from requesting
the report ... to the execution ... to the display of this rather unfriendly
message. And where do I find the application event log ? Have looked
everywhere on report server for such a log to no avail :-(
Thanks!You might find information in one of the files at: C:\Program
Files\Microsoft SQL Server\MSSQL\Reporting Services\LogFiles
However, the error actually sounds like it comes from ASP.Net, not
Reporting Services. I searched for the error, and there can be many reasons
why this happens. Hope there is something in one of the logs to help you.
--
| Thread-Topic: how to correct error on server side
| thread-index: AcUfV6qsL0pjpLP9THCLVqoegvsWsQ==| X-WBNR-Posting-Host: 192.85.50.2
| From: "=?Utf-8?B?TUogVGFmdA==?=" <MJTaft@.discussions.microsoft.com>
| Subject: how to correct error on server side
| Date: Wed, 2 Mar 2005 10:43:03 -0800
| Lines: 19
| Message-ID: <6E08FFDD-6C09-49DA-AED3-5776FAAB6F67@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTCMTY1.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:44361
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I have a report that uses a stored procedure (DB2). I sometimes get this
| error when running certain parameter combinations from the report
deployed on
| a portal.
|
| "Server Application Unavailable
| The web application you are attempting to access on this web server is
| currently unavailable. Please hit the "Refresh" button in your web
browser to
| retry your request.
| Administrator Note: An error message detailing the cause of this specific
| request failure can be found in the application event log of the web
server.
| Please review this log entry to discover what caused this error to occur.
"
|
| I am not sure where to intercept this. I assume it is coming from the
| reporting services server but I dont know where to step in from
requesting
| the report ... to the execution ... to the display of this rather
unfriendly
| message. And where do I find the application event log ? Have looked
| everywhere on report server for such a log to no avail :-(
|
| Thanks!
||||Thanks for the reply Brad. I have looked in the log files in the reporting
services location and should have put that in my initial post. There was
nothing there to help with this. I did see something in the system app event
log stating something like aspnet_wp.exe stopped unexpectedly but it didnt
give any more info than that. Also ... I have noticed in this newgroup that
there are several people that seem to be getting that error message however I
have not seen many suggestions as to how to correct it or where it comes
from. I will keep hoping that someone will help. Thanks.
""Brad Syputa - MS"" wrote:
> You might find information in one of the files at: C:\Program
> Files\Microsoft SQL Server\MSSQL\Reporting Services\LogFiles
> However, the error actually sounds like it comes from ASP.Net, not
> Reporting Services. I searched for the error, and there can be many reasons
> why this happens. Hope there is something in one of the logs to help you.
> --
> | Thread-Topic: how to correct error on server side
> | thread-index: AcUfV6qsL0pjpLP9THCLVqoegvsWsQ==> | X-WBNR-Posting-Host: 192.85.50.2
> | From: "=?Utf-8?B?TUogVGFmdA==?=" <MJTaft@.discussions.microsoft.com>
> | Subject: how to correct error on server side
> | Date: Wed, 2 Mar 2005 10:43:03 -0800
> | Lines: 19
> | Message-ID: <6E08FFDD-6C09-49DA-AED3-5776FAAB6F67@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTCMTY1.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:44361
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | I have a report that uses a stored procedure (DB2). I sometimes get this
> | error when running certain parameter combinations from the report
> deployed on
> | a portal.
> |
> | "Server Application Unavailable
> | The web application you are attempting to access on this web server is
> | currently unavailable. Please hit the "Refresh" button in your web
> browser to
> | retry your request.
> | Administrator Note: An error message detailing the cause of this specific
> | request failure can be found in the application event log of the web
> server.
> | Please review this log entry to discover what caused this error to occur.
> "
> |
> | I am not sure where to intercept this. I assume it is coming from the
> | reporting services server but I dont know where to step in from
> requesting
> | the report ... to the execution ... to the display of this rather
> unfriendly
> | message. And where do I find the application event log ? Have looked
> | everywhere on report server for such a log to no avail :-(
> |
> | Thanks!
> |
>

How to copy..........

I have 2 files with its same name (at folder C:\a\ and C:\b\, both Name
files is SData.mdf). I wanna access the files in enterprise manager at the
time. Can I do it? Because I wanna copy a object (table, store procedure,
etc) from C:\a\Sdata.mdf to C:\b\Sdata.mdf.
How to do it efficiently?Hi
You cannot 'access/open' them . Its data files SQL Server used for. RESTORE
DATABASE with a different names and make your copies
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:eLt2paOTGHA.4384@.tk2msftngp13.phx.gbl...
>I have 2 files with its same name (at folder C:\a\ and C:\b\, both Name
> files is SData.mdf). I wanna access the files in enterprise manager at the
> time. Can I do it? Because I wanna copy a object (table, store procedure,
> etc) from C:\a\Sdata.mdf to C:\b\Sdata.mdf.
> How to do it efficiently?
>

How to copy views between servers?

I want to copy some views (not data) from one server to another.
How can I do that?
Sorry! I am using Sqlserver 2005
"ad" <flying@.wfes.tcc.edu.tw> glsD:ePT0y4ZhIHA.3940@.TK2MSFTNGP05.phx.g bl...
>I want to copy some views (not data) from one server to another.
> How can I do that?
>
|||Hi
Probably the easiest way is to script them and run the scripts. If you are
using version control you could just run the scripts you have already
(assuming they are autonomous).
Alternatively you can use SSIS and a "transfer object" task or SMO.
John
"ad" wrote:

> Sorry! I am using Sqlserver 2005
> "ad" <flying@.wfes.tcc.edu.tw> ???g?ó?l￥ó·s?D:ePT0y4ZhIHA.3940@.TK2MSFTN GP05.phx.gbl...
>
>
|||What is SSIS?
How can I do that?
"John Bell" <jbellnewsposts@.hotmail.com> glsD:8C605E23-915D-4E44-9626-1E720FA3BE2F@.microsoft.com...[vbcol=seagreen]
> Hi
> Probably the easiest way is to script them and run the scripts. If you are
> using version control you could just run the scripts you have already
> (assuming they are autonomous).
> Alternatively you can use SSIS and a "transfer object" task or SMO.
> John
> "ad" wrote:
|||Hi
SSIS is SQL Server Integration Services, it is only available on the
Standard and Enterprise Editions and you use SQL Server Business Intelligence
Development Studio (BIDS) to write the packages. You have not said which
version of SQL Server you are using! If you are on Express or Workgroup you
would need to script the procedures or use SMO. If you don't have Books
Online it can be downloaded from
http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en
This has sections on SMO (just look it up in the index!)
John
"ad" wrote:

> What is SSIS?
> How can I do that?
> "John Bell" <jbellnewsposts@.hotmail.com> ???g?ó?l￥ó·s?D:8C605E23-915D-4E44-9626-1E720FA3BE2F@.microsoft.com...
>
>

How to copy views between servers?

I want to copy some views (not data) from one server to another.
How can I do that?Sorry! I am using Sqlserver 2005
"ad" <flying@.wfes.tcc.edu.tw> ¼¶¼g©ó¶l¥ó·s»D:ePT0y4ZhIHA.3940@.TK2MSFTNGP05.phx.gbl...
>I want to copy some views (not data) from one server to another.
> How can I do that?
>|||Hi
Probably the easiest way is to script them and run the scripts. If you are
using version control you could just run the scripts you have already
(assuming they are autonomous).
Alternatively you can use SSIS and a "transfer object" task or SMO.
John
"ad" wrote:
> Sorry! I am using Sqlserver 2005
> "ad" <flying@.wfes.tcc.edu.tw> Â¼Â¶Â¼gÂ©Ã³Â¶lÂ¥Ã³Â·sÂ»D:ePT0y4ZhIHA.3940@.TK2MSFTNGP05.phx.gbl...
> >I want to copy some views (not data) from one server to another.
> > How can I do that?
> >
>
>|||What is SSIS?
How can I do that?
"John Bell" <jbellnewsposts@.hotmail.com> ¼¶¼g©ó¶l¥ó·s»D:8C605E23-915D-4E44-9626-1E720FA3BE2F@.microsoft.com...
> Hi
> Probably the easiest way is to script them and run the scripts. If you are
> using version control you could just run the scripts you have already
> (assuming they are autonomous).
> Alternatively you can use SSIS and a "transfer object" task or SMO.
> John
> "ad" wrote:
>> Sorry! I am using Sqlserver 2005
>> "ad" <flying@.wfes.tcc.edu.tw> ?gco?l¢Do¡Ps?D:ePT0y4ZhIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> >I want to copy some views (not data) from one server to another.
>> > How can I do that?
>> >
>>|||Hi
SSIS is SQL Server Integration Services, it is only available on the
Standard and Enterprise Editions and you use SQL Server Business Intelligence
Development Studio (BIDS) to write the packages. You have not said which
version of SQL Server you are using! If you are on Express or Workgroup you
would need to script the procedures or use SMO. If you don't have Books
Online it can be downloaded from
http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en
This has sections on SMO (just look it up in the index!)
John
"ad" wrote:
> What is SSIS?
> How can I do that?
> "John Bell" <jbellnewsposts@.hotmail.com> Â¼Â¶Â¼gÂ©Ã³Â¶lÂ¥Ã³Â·sÂ»D:8C605E23-915D-4E44-9626-1E720FA3BE2F@.microsoft.com...
> > Hi
> >
> > Probably the easiest way is to script them and run the scripts. If you are
> > using version control you could just run the scripts you have already
> > (assuming they are autonomous).
> >
> > Alternatively you can use SSIS and a "transfer object" task or SMO.
> >
> > John
> >
> > "ad" wrote:
> >
> >> Sorry! I am using Sqlserver 2005
> >>
> >> "ad" <flying@.wfes.tcc.edu.tw> ?gco?lÂ¢DoÂ¡Ps?D:ePT0y4ZhIHA.3940@.TK2MSFTNGP05.phx.gbl...
> >>
> >> >I want to copy some views (not data) from one server to another.
> >> > How can I do that?
> >> >
> >>
> >>
> >>
>
>

Friday, February 24, 2012

How to count distinct members and keep good performance

How to count across multiple tables in a DB?

How to count a very large volume of request

How to count a very large volume of request

How to count

How to cotrol format of datetime attributes?

How to cosolidate databases of SQL Express from multiple installations?

How to corrupt data

how to corrupt a db

how to corrupt a db

how to corrupt a db

how to correctly work with two (or more) databases?

how to correctly work with two (or more) databases?

How to correctly update a table which values can be either inserted/updated/deleted on upd

How to correctly update a table which values can be either inserted/updated/deleted on upd

How to correctly propogate data back to the database

how to correctly finding memory usage.

How to correct registry in renamed server

How to correct registry in renamed server

How to correct registry in renamed server

How to correct index problems in system tables

how to correct error on server side

How to copy..........

How to copy views between servers?

How to copy views between servers?

copy database

Blog Archive

About Me