I have subscriptions setup to deliver reports in PDF format to users email
address. No problem there. When the email is opened in the Outlook client,
the attachment appears as expected and can be opened. The problem is when
the user accesses their email using Outlook web access (OWA). The email
appears and even has the paper clip identifying the attachment. However,
when the email is opened. the attachment is gone."chicagoclone" <chicagoclone@.discussions.microsoft.com> wrote in message
news:F54988C0-2987-484D-AE80-F0B50F002E1D@.microsoft.com...
>I have subscriptions setup to deliver reports in PDF format to users email
> address. No problem there. When the email is opened in the Outlook
> client,
> the attachment appears as expected and can be opened. The problem is when
> the user accesses their email using Outlook web access (OWA). The email
> appears and even has the paper clip identifying the attachment. However,
> when the email is opened. the attachment is gone.
Can they open other pdf-files from their OWA?
Any security settings in play?
Did they test their OWA on a machine they knew there was a PDF-reader
installed on?
I know that sometimes I need to download pdf-files on my computer before
opening them in my OWA. But I'm quite sure the link for downloading is there
when I open the message, though.
Kaisa M. Lindahl|||they can open other pdf's through OWA, just not the ones delivered through
reporting services.
"Kaisa M. Lindahl" wrote:
> "chicagoclone" <chicagoclone@.discussions.microsoft.com> wrote in message
> news:F54988C0-2987-484D-AE80-F0B50F002E1D@.microsoft.com...
> >I have subscriptions setup to deliver reports in PDF format to users email
> > address. No problem there. When the email is opened in the Outlook
> > client,
> > the attachment appears as expected and can be opened. The problem is when
> > the user accesses their email using Outlook web access (OWA). The email
> > appears and even has the paper clip identifying the attachment. However,
> > when the email is opened. the attachment is gone.
> Can they open other pdf-files from their OWA?
> Any security settings in play?
> Did they test their OWA on a machine they knew there was a PDF-reader
> installed on?
> I know that sometimes I need to download pdf-files on my computer before
> opening them in my OWA. But I'm quite sure the link for downloading is there
> when I open the message, though.
> Kaisa M. Lindahl
>
>
Showing posts with label access. Show all posts
Showing posts with label access. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
outer join similar to Access
Ok fellas, I need your help. I have a crosstab query that I wrote in Access
that lists all my marketers regardless of their referrals. The outer joins
selects all rows from marketer table. Now in SSRS I recreated the same query,
setup the matrix and populated it correctly. Problem is, that if the marketer
had no referrals, their name does not show up. I assumed that the outer join
would take care of that. Do I need to alter the query in some way to treat
nulls as zeros? Its a very simple query but I cannot determine where to put
the conditioning. Many thanks.isnull(numofreferralscolumn, 0) as numofreferralscolumn
in the select would be the easiest way.
--
Michael Abair
Programmer / Analyst
Chicos FAS Inc.
"Brian L" <BrianL@.discussions.microsoft.com> wrote in message
news:D5DBC2BE-55FA-413E-97A9-99A262C48EAC@.microsoft.com...
> Ok fellas, I need your help. I have a crosstab query that I wrote in
> Access
> that lists all my marketers regardless of their referrals. The outer joins
> selects all rows from marketer table. Now in SSRS I recreated the same
> query,
> setup the matrix and populated it correctly. Problem is, that if the
> marketer
> had no referrals, their name does not show up. I assumed that the outer
> join
> would take care of that. Do I need to alter the query in some way to treat
> nulls as zeros? Its a very simple query but I cannot determine where to
> put
> the conditioning. Many thanks.|||or you might need to do some more hocus-pocus.. i have to do things
like this to get 0 members showing up in some cubes for example
say you got a table that lists OrderTotals.. right?
Select CustomerID, OrderID, OrderTotalA, OrderTotalB From OrderTotals
UNION ALL
Select CustomerID, 0,0,0 From Customers
Michael Abair wrote:
> isnull(numofreferralscolumn, 0) as numofreferralscolumn
> in the select would be the easiest way.
> --
> Michael Abair
> Programmer / Analyst
> Chicos FAS Inc.
>
> "Brian L" <BrianL@.discussions.microsoft.com> wrote in message
> news:D5DBC2BE-55FA-413E-97A9-99A262C48EAC@.microsoft.com...
> > Ok fellas, I need your help. I have a crosstab query that I wrote in
> > Access
> > that lists all my marketers regardless of their referrals. The outer joins
> > selects all rows from marketer table. Now in SSRS I recreated the same
> > query,
> > setup the matrix and populated it correctly. Problem is, that if the
> > marketer
> > had no referrals, their name does not show up. I assumed that the outer
> > join
> > would take care of that. Do I need to alter the query in some way to treat
> > nulls as zeros? Its a very simple query but I cannot determine where to
> > put
> > the conditioning. Many thanks.
that lists all my marketers regardless of their referrals. The outer joins
selects all rows from marketer table. Now in SSRS I recreated the same query,
setup the matrix and populated it correctly. Problem is, that if the marketer
had no referrals, their name does not show up. I assumed that the outer join
would take care of that. Do I need to alter the query in some way to treat
nulls as zeros? Its a very simple query but I cannot determine where to put
the conditioning. Many thanks.isnull(numofreferralscolumn, 0) as numofreferralscolumn
in the select would be the easiest way.
--
Michael Abair
Programmer / Analyst
Chicos FAS Inc.
"Brian L" <BrianL@.discussions.microsoft.com> wrote in message
news:D5DBC2BE-55FA-413E-97A9-99A262C48EAC@.microsoft.com...
> Ok fellas, I need your help. I have a crosstab query that I wrote in
> Access
> that lists all my marketers regardless of their referrals. The outer joins
> selects all rows from marketer table. Now in SSRS I recreated the same
> query,
> setup the matrix and populated it correctly. Problem is, that if the
> marketer
> had no referrals, their name does not show up. I assumed that the outer
> join
> would take care of that. Do I need to alter the query in some way to treat
> nulls as zeros? Its a very simple query but I cannot determine where to
> put
> the conditioning. Many thanks.|||or you might need to do some more hocus-pocus.. i have to do things
like this to get 0 members showing up in some cubes for example
say you got a table that lists OrderTotals.. right?
Select CustomerID, OrderID, OrderTotalA, OrderTotalB From OrderTotals
UNION ALL
Select CustomerID, 0,0,0 From Customers
Michael Abair wrote:
> isnull(numofreferralscolumn, 0) as numofreferralscolumn
> in the select would be the easiest way.
> --
> Michael Abair
> Programmer / Analyst
> Chicos FAS Inc.
>
> "Brian L" <BrianL@.discussions.microsoft.com> wrote in message
> news:D5DBC2BE-55FA-413E-97A9-99A262C48EAC@.microsoft.com...
> > Ok fellas, I need your help. I have a crosstab query that I wrote in
> > Access
> > that lists all my marketers regardless of their referrals. The outer joins
> > selects all rows from marketer table. Now in SSRS I recreated the same
> > query,
> > setup the matrix and populated it correctly. Problem is, that if the
> > marketer
> > had no referrals, their name does not show up. I assumed that the outer
> > join
> > would take care of that. Do I need to alter the query in some way to treat
> > nulls as zeros? Its a very simple query but I cannot determine where to
> > put
> > the conditioning. Many thanks.
Outer Join problem
I know this is not an Access group, but there doesn't seem to be one,
and I'm guessing MS SQLs are all quite similar - at least in relatively
simple cases like this one.
and I'm guessing MS SQLs are all quite similar - at least in relatively
simple cases like this one.
I'm using this SQL statement,
"SELECT [Cape Town 25/04].IDNUMBER, [Cape Town 25/04].SURNAME,
[Cape Town 25/04].NAMES, Misc.PHONE_NUMBER,
Misc.VOTE FROM [Cape Town 25/04] LEFT JOIN Misc
ON [Cape Town 25/04].IDNUMBER = Misc.IDNUMBER
WHERE STREETNAME='FIRTH ROAD' AND STREETNO='3FI' AND BLDNGNAME IS NULL
AND BLDNGNO IS NULL"
and getting this error message: "[Microsoft][ODBC Microsoft Access
Driver] Too few parameters. Expected 2. in EXEC"
Where is the missing parameter? I think it's something to do with the
LEFT JOIN, but I'm not sure - I'm a SQL newbie.
I'm accessing an MS Access database from Python via the PythonWin obdc
interface, if it makes a difference.
--MaxOn Tue, 24 Jan 2006 20:13:49 +0200, Max <rabkin@.mweb[DOT]co[DOT]za>
wrote:
>I know this is not an Access group, but there doesn't seem to be one,
Hi Max,
There's comp.databases.ms-access. And over 20 groups in the
microsoft.public.access hierarchy. There are also access groups in many
international hierarchies, or in international sub-hierarchies of the
microsoft.public hierarchy.
>and I'm guessing MS SQLs are all quite similar - at least in relatively
>simple cases like this one.
Don't count on it - there are many major differences between Jet SQL
(used in Access) and Trasact SQL (used in SQL Server). T-SQL tends to be
a lot closer to the ANSI-defined SQL standards.
>I'm using this SQL statement,
>"SELECT [Cape Town 25/04].IDNUMBER, [Cape Town 25/04].SURNAME,
> [Cape Town 25/04].NAMES, Misc.PHONE_NUMBER,
>Misc.VOTE FROM [Cape Town 25/04] LEFT JOIN Misc
>ON [Cape Town 25/04].IDNUMBER = Misc.IDNUMBER
>WHERE STREETNAME='FIRTH ROAD' AND STREETNO='3FI' AND BLDNGNAME IS NULL
>AND BLDNGNO IS NULL"
>and getting this error message: "[Microsoft][ODBC Microsoft Access
>Driver] Too few parameters. Expected 2. in EXEC"
>Where is the missing parameter? I think it's something to do with the
>LEFT JOIN, but I'm not sure - I'm a SQL newbie.
According to the error message, two parameters where expected in EXEC.
The code you posted does not contain the word "EXEC". Maybe you should
double-check if the error is not produced by another part of your code?
Anyway, the query you posted passes the syntax check of SQL Server 2000
without problems.
>I'm accessing an MS Access database from Python via the PythonWin obdc
>interface, if it makes a difference.
>--Max
--
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis wrote:
> There's comp.databases.ms-access. And over 20 groups in the
> microsoft.public.access hierarchy. There are also access groups in many
> international hierarchies, or in international sub-hierarchies of the
> microsoft.public hierarchy.
Thank you. Sorry my ISP seems not to offer them.
> Don't count on it - there are many major differences between Jet SQL
> (used in Access) and Trasact SQL (used in SQL Server). T-SQL tends to be
> a lot closer to the ANSI-defined SQL standards.
Good to know.
> >Where is the missing parameter? I think it's something to do with the
> >LEFT JOIN, but I'm not sure - I'm a SQL newbie.
> According to the error message, two parameters where expected in EXEC.
> The code you posted does not contain the word "EXEC". Maybe you should
> double-check if the error is not produced by another part of your code?
Nope, all my error messages end with a period and "in EXEC". I'm pretty
sure it's that line - although I'm getting the same problem elsewhere
> Anyway, the query you posted passes the syntax check of SQL Server 2000
> without problems.
Thanks for checking.
> --
> Hugo Kornelis, SQL Server MVP
--Max|||I'd check the spelling of the column names as this is often where
Access throws this sort of error. If you spelt something wrong, it may
give this error thinking that you are going to pass a parameter into
the query. SQL would show something different (and probably more close
to the actual error).
BTW, have a look at the table naming you use as it's terrible.
Ryan|||The slashes in the table name might be causing you grief.
Max wrote:
>"SELECT [Cape Town 25/04].IDNUMBER, [Cape Town 25/04].SURNAME,
> [Cape Town 25/04].NAMES, Misc.PHONE_NUMBER,|||On 24 Jan 2006 23:32:21 -0800, rabkin@.mweb.co.za wrote:
>Hugo Kornelis wrote:
>> There's comp.databases.ms-access. And over 20 groups in the
>> microsoft.public.access hierarchy. There are also access groups in many
>> international hierarchies, or in international sub-hierarchies of the
>> microsoft.public hierarchy.
>Thank you. Sorry my ISP seems not to offer them.
Hi Max,
If you need help from an Access group often, consider switching ISP or
buying a pay server subscription. For a one-time issue, you could use
Google groups.
http://groups.google.com/group/comp...ms-access/about
--
Hugo Kornelis, SQL Server MVP|||(rabkin@.mweb.co.za) writes:
> Hugo Kornelis wrote:
>> There's comp.databases.ms-access. And over 20 groups in the
>> microsoft.public.access hierarchy. There are also access groups in many
>> international hierarchies, or in international sub-hierarchies of the
>> microsoft.public hierarchy.
> Thank you. Sorry my ISP seems not to offer them.
You can access Microsoft's newsgroups at msnews.microsoft.com.
> Nope, all my error messages end with a period and "in EXEC". I'm pretty
> sure it's that line - although I'm getting the same problem elsewhere
My news provider has had a hiatus, so I have seen all of the thread, but
did you ever post the full error message. Is it possible that the message
comes from Python? (I know neither Python nor Access.)
--
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|||On Wed, 25 Jan 2006 22:51:23 +0000 (UTC), Erland Sommarskog wrote:
(snip)
>> Nope, all my error messages end with a period and "in EXEC". I'm pretty
>> sure it's that line - although I'm getting the same problem elsewhere
>My news provider has had a hiatus, so I have seen all of the thread, but
>did you ever post the full error message. Is it possible that the message
>comes from Python? (I know neither Python nor Access.)
Hi Erland,
Max did, in his first post. Here's what he posted:
>I know this is not an Access group, but there doesn't seem to be one,
>and I'm guessing MS SQLs are all quite similar - at least in relatively
>simple cases like this one.
>I'm using this SQL statement,
>"SELECT [Cape Town 25/04].IDNUMBER, [Cape Town 25/04].SURNAME,
> [Cape Town 25/04].NAMES, Misc.PHONE_NUMBER,
>Misc.VOTE FROM [Cape Town 25/04] LEFT JOIN Misc
>ON [Cape Town 25/04].IDNUMBER = Misc.IDNUMBER
>WHERE STREETNAME='FIRTH ROAD' AND STREETNO='3FI' AND BLDNGNAME IS NULL
>AND BLDNGNO IS NULL"
>and getting this error message: "[Microsoft][ODBC Microsoft Access
>Driver] Too few parameters. Expected 2. in EXEC"
>Where is the missing parameter? I think it's something to do with the
>LEFT JOIN, but I'm not sure - I'm a SQL newbie.
>I'm accessing an MS Access database from Python via the PythonWin obdc
>interface, if it makes a difference.
>--Max
--
Hugo Kornelis, SQL Server MVPsql
Outer Join in MS Access
Anyone has the idea of how to running outer join query in MS Access?Looks like MS Access does not support Outer Join query. Please correct me if this is not ture.i do outer joins in access all the time
show us yours, and the error message you got
rudysql
show us yours, and the error message you got
rudysql
Tuesday, March 20, 2012
Ouestion on port 1433/1434
Hi
Is it wise to open port 1433/1434 so that a remote user can access sql server thats on my dmz
ThanksBetter to use non-standard ports, if possible.
Sincerely,
Invotion Engineering Team
Advanced Microsoft Hosting Solutions
http://www.Invotion.com
>--Original Message--
>Hi,
>Is it wise to open port 1433/1434 so that a remote user
can access sql server thats on my dmz?
>Thanks
>.
>|||Hi
Where can I find info on changing the ports
Thanks
Is it wise to open port 1433/1434 so that a remote user can access sql server thats on my dmz
ThanksBetter to use non-standard ports, if possible.
Sincerely,
Invotion Engineering Team
Advanced Microsoft Hosting Solutions
http://www.Invotion.com
>--Original Message--
>Hi,
>Is it wise to open port 1433/1434 so that a remote user
can access sql server thats on my dmz?
>Thanks
>.
>|||Hi
Where can I find info on changing the ports
Thanks
Monday, March 12, 2012
OT: "Profiler" for Access?
Guys,
Is there a tool that I can see what queries are running against an Access
database? The one is similar to Profiler for SQL.
Thanks.Hi
AFAIK three is no such tool, if you are using ODBC then you may want to try
using ODBC tracing, but this would need to be enabled on each client and
would severely impact performance. Another alternative would be to make the
m
linked tables to a SQL Server database and to actually use profiler.
You may want to ask this in an Access News Group.
John
"ME" wrote:
> Guys,
> Is there a tool that I can see what queries are running against an Access
> database? The one is similar to Profiler for SQL.
> Thanks.
>
>|||thanks anyway.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0CC54221-14C3-42D9-A296-46C75C5B1C29@.microsoft.com...[vbcol=seagreen]
> Hi
> AFAIK three is no such tool, if you are using ODBC then you may want to
> try
> using ODBC tracing, but this would need to be enabled on each client and
> would severely impact performance. Another alternative would be to make
> them
> linked tables to a SQL Server database and to actually use profiler.
> You may want to ask this in an Access News Group.
> John
> "ME" wrote:
>
Is there a tool that I can see what queries are running against an Access
database? The one is similar to Profiler for SQL.
Thanks.Hi
AFAIK three is no such tool, if you are using ODBC then you may want to try
using ODBC tracing, but this would need to be enabled on each client and
would severely impact performance. Another alternative would be to make the
m
linked tables to a SQL Server database and to actually use profiler.
You may want to ask this in an Access News Group.
John
"ME" wrote:
> Guys,
> Is there a tool that I can see what queries are running against an Access
> database? The one is similar to Profiler for SQL.
> Thanks.
>
>|||thanks anyway.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0CC54221-14C3-42D9-A296-46C75C5B1C29@.microsoft.com...[vbcol=seagreen]
> Hi
> AFAIK three is no such tool, if you are using ODBC then you may want to
> try
> using ODBC tracing, but this would need to be enabled on each client and
> would severely impact performance. Another alternative would be to make
> them
> linked tables to a SQL Server database and to actually use profiler.
> You may want to ask this in an Access News Group.
> John
> "ME" wrote:
>
OT: "Profiler" for Access?
Guys,
Is there a tool that I can see what queries are running against an Access
database? The one is similar to Profiler for SQL.
Thanks.Hi
AFAIK three is no such tool, if you are using ODBC then you may want to try
using ODBC tracing, but this would need to be enabled on each client and
would severely impact performance. Another alternative would be to make them
linked tables to a SQL Server database and to actually use profiler.
You may want to ask this in an Access News Group.
John
"ME" wrote:
> Guys,
> Is there a tool that I can see what queries are running against an Access
> database? The one is similar to Profiler for SQL.
> Thanks.
>
>|||thanks anyway.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0CC54221-14C3-42D9-A296-46C75C5B1C29@.microsoft.com...
> Hi
> AFAIK three is no such tool, if you are using ODBC then you may want to
> try
> using ODBC tracing, but this would need to be enabled on each client and
> would severely impact performance. Another alternative would be to make
> them
> linked tables to a SQL Server database and to actually use profiler.
> You may want to ask this in an Access News Group.
> John
> "ME" wrote:
>> Guys,
>> Is there a tool that I can see what queries are running against an Access
>> database? The one is similar to Profiler for SQL.
>> Thanks.
>>
Is there a tool that I can see what queries are running against an Access
database? The one is similar to Profiler for SQL.
Thanks.Hi
AFAIK three is no such tool, if you are using ODBC then you may want to try
using ODBC tracing, but this would need to be enabled on each client and
would severely impact performance. Another alternative would be to make them
linked tables to a SQL Server database and to actually use profiler.
You may want to ask this in an Access News Group.
John
"ME" wrote:
> Guys,
> Is there a tool that I can see what queries are running against an Access
> database? The one is similar to Profiler for SQL.
> Thanks.
>
>|||thanks anyway.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0CC54221-14C3-42D9-A296-46C75C5B1C29@.microsoft.com...
> Hi
> AFAIK three is no such tool, if you are using ODBC then you may want to
> try
> using ODBC tracing, but this would need to be enabled on each client and
> would severely impact performance. Another alternative would be to make
> them
> linked tables to a SQL Server database and to actually use profiler.
> You may want to ask this in an Access News Group.
> John
> "ME" wrote:
>> Guys,
>> Is there a tool that I can see what queries are running against an Access
>> database? The one is similar to Profiler for SQL.
>> Thanks.
>>
Wednesday, March 7, 2012
OSQL Performance Problem
System info:
Win 2003 Server, SQL Server 2000, test server - no outside access from other users (no other applications running)
Table info:
DocId(int), a(nvarchar(10)), b(nvarchar(20)), c(nvarchar(10)), d(nvarchar(20)), e(nvarchar(10))
DocId has a number, all other columns are null. No index
I am using a stored procedure that updates the values based on the DocId. I have an program that creates a sql script file that should be executed. Approx. 440000 lines.
Example:
Using TableName
Go
SET NOCOUNT ON
GO
exec sp_SPNAME @.docId=1, @.a = 'blah', @.b = 'blah', @.c = 'blah', @.d = 'blah', @.e = 'blah'
GO
exec sp_SPNAME @.docId=2, @.a = 'blah', @.b = 'blah', @.c = 'blah', @.d = 'blah', @.e = 'blah'
GO
repeats 440K.
Question: When I execute this script per osql.exe, the update takes more the 24 hours... Any suggestions?
Thanks in advance.System info:
Win 2003 Server, SQL Server 2000, test server - no outside access from other users (no other applications running)
Table info:
DocId(int), a(nvarchar(10)), b(nvarchar(20)), c(nvarchar(10)), d(nvarchar(20)), e(nvarchar(10))
DocId has a number, all other columns are null. No index
INDEX !!!
Put a clustered index on DocID.
CREATE CLUSTERED INDEX IXc_TableName_DocID ON TableName (DocID)
GO
Regards,
hmscott|||another route is to bulkcopy/insert all the new data into a table then do a single update against the base table. Index on docid would be desired when you start dml.|||Hi hmscott,
thanks for the reply and the sql. I added the index and, while it is much faster, it still takes more than 8 hours. Maybe this is normal for executing 440,000 statements?
Thanks,
Lens|||Hi oj,
thanks to you as well for the reply. I will change my program to make a csv file and see if a bulk update increases the speed.
Thanks,
Lens|||Hi,
just a quick status update. I changed my program to create a csv-file. Approx. 440,000 lines imported into temp table; less than 2 minutes. Update into final table, less than two minutes...zoinks.
Thanks again.
Win 2003 Server, SQL Server 2000, test server - no outside access from other users (no other applications running)
Table info:
DocId(int), a(nvarchar(10)), b(nvarchar(20)), c(nvarchar(10)), d(nvarchar(20)), e(nvarchar(10))
DocId has a number, all other columns are null. No index
I am using a stored procedure that updates the values based on the DocId. I have an program that creates a sql script file that should be executed. Approx. 440000 lines.
Example:
Using TableName
Go
SET NOCOUNT ON
GO
exec sp_SPNAME @.docId=1, @.a = 'blah', @.b = 'blah', @.c = 'blah', @.d = 'blah', @.e = 'blah'
GO
exec sp_SPNAME @.docId=2, @.a = 'blah', @.b = 'blah', @.c = 'blah', @.d = 'blah', @.e = 'blah'
GO
repeats 440K.
Question: When I execute this script per osql.exe, the update takes more the 24 hours... Any suggestions?
Thanks in advance.System info:
Win 2003 Server, SQL Server 2000, test server - no outside access from other users (no other applications running)
Table info:
DocId(int), a(nvarchar(10)), b(nvarchar(20)), c(nvarchar(10)), d(nvarchar(20)), e(nvarchar(10))
DocId has a number, all other columns are null. No index
INDEX !!!
Put a clustered index on DocID.
CREATE CLUSTERED INDEX IXc_TableName_DocID ON TableName (DocID)
GO
Regards,
hmscott|||another route is to bulkcopy/insert all the new data into a table then do a single update against the base table. Index on docid would be desired when you start dml.|||Hi hmscott,
thanks for the reply and the sql. I added the index and, while it is much faster, it still takes more than 8 hours. Maybe this is normal for executing 440,000 statements?
Thanks,
Lens|||Hi oj,
thanks to you as well for the reply. I will change my program to make a csv file and see if a bulk update increases the speed.
Thanks,
Lens|||Hi,
just a quick status update. I changed my program to create a csv-file. Approx. 440,000 lines imported into temp table; less than 2 minutes. Update into final table, less than two minutes...zoinks.
Thanks again.
OSQL is Unable to see SQL Server and MSDE 2000 on the same machine
I have installed SQL Server and MSDE 2000 on my laptop under Windows XP. But I am not able to access the MSDE 2000 instance via OSQL. When I issue the command "osql -L" it lists only the SQL server (named as VSDOTNET). The MSDE 2000 instance is named as M
SSQLSERVER. The enterprise manager does not see MSSQLSERVER either. When I use the command "osql -Slocalhost\mssqlserver -Usa" and enter the password, I get the error message "[DBNETLIB]Invalid Connection.." The SQLMODE is set to mixed mode. I can access
MSSQLSERVER through a third party software (DB2KManager) but not through OSQL. Any suggestion would be greatly appreciated.
hi
"Surya Yadav" <Surya.Yadav@.ttu.edu> ha scritto nel messaggio
news:ED1D3FFF-4398-4407-8676-A79A31018BC0@.microsoft.com...
> I have installed SQL Server and MSDE 2000 on my laptop under Windows XP.
But I am not able to
>access the MSDE 2000 instance via OSQL. When I issue the command "osql -L"
it lists only the SQL >server (named as VSDOTNET). The MSDE 2000 instance is
named as MSSQLSERVER. The >enterprise manager does not see MSSQLSERVER
either. When I use the command "osql ->Slocalhost\mssqlserver -Usa" and
enter the password, I get the error message "[DBNETLIB]Invalid
>Connection.." The SQLMODE is set to mixed mode. I can access MSSQLSERVER
through a third >party software (DB2KManager) but not through OSQL. Any
suggestion would be greatly appreciated.
when installing a named instance, it's full name is
ComputerName\InstanceName or (local)\InstanceName or LocalHost\InstanceName,
those , your instance will be known as
YourCoumpeterName\VSDOTNET
(Local)\VSDOTNET
Localhost\VSDOTNET
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi,
Can you please explain it again? What am I doing wrong when I type "osql -Slocalhost\mssqlserver -Usa" ? The service is running as MSSQLSERVER when I browse under the Services icon from the Administrative tools. At the installation time I did not specify
an instance name. Setup.exe created the default instance as MSSQLSERVER. Thanks.
|||hi,
"Surya Yadav" <surya.yadav@.ttu.edu> ha scritto nel messaggio
news:0ED30CB4-1E7C-4CDC-9BAF-FA3F0742C9F0@.microsoft.com...
> Hi,
> Can you please explain it again? What am I doing wrong when I type
"osql -Slocalhost\mssqlserver -
> Usa" ? The service is running as MSSQLSERVER when I browse under the
Services icon from the
> Administrative tools. At the installation time I did not specify an
instance name. Setup.exe created the
> default instance as MSSQLSERVER. Thanks.
you said your named instance was named VSDOTNET... in this case, the full
instance name is
ComputerName\InstanceName = YourComputerName\VSDOTNET
if the installed instance is the default instance, then it's name is just
(Local)
and/or
YourComputerName
c:\>osql -S(local) -Usa
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi I have the same problem:
Installed MSDE with default instance and could then connect to it from
QSQL.
Then I uninstalled it and installed with a named instance. Now I cant
connect:
With "osql -U sa -S servername\instancename" command... -
"[DBMSLPCN]SQL Server does not exist or access denied.
[DBMSLPCN]ConnectionOpen (Connect())."
But the SQL server Manager icon next to the clock indicate that it is
running...
Is the port 1050 correct - from the log file below..
Any help / ideas will be much appreciated. More detail follow below:
I use "MSDE2000A" with the service pack 3a
My install options was:
[Options]
INSTANCENAME="instancename"
TARGETDIR="E:\SQLServer_instancename"
SECURITYMODE=SQL
DISABLENETWORKPROTOCOLS=0
In the error log file I found these:
====================================
2004-05-08 14:41:14.06 server Copyright (C) 1988-2002 Microsoft
Corporation.
2004-05-08 14:41:14.06 server All rights reserved.
2004-05-08 14:41:14.06 server Server Process ID is 2700.
2004-05-08 14:41:14.06 server Logging SQL Server messages in file
'E:\SQLServer_instancenameMSSQL$instancename\LOG\E RRORLOG'.
2004-05-08 14:41:14.06 server SQL Server is starting at priority
class 'normal'(2 CPUs detected).
2004-05-08 14:41:14.21 server SQL Server configured for thread mode
processing.
2004-05-08 14:41:14.21 server Using dynamic lock allocation. [500]
Lock Blocks, [1000] Lock Owner Blocks.
2004-05-08 14:41:14.26 spid4 Starting up database 'master'.
2004-05-08 14:41:14.32 server Using 'SSNETLIB.DLL' version
'8.0.766'.
2004-05-08 14:41:14.32 spid5 Starting up database 'model'.
2004-05-08 14:41:14.32 spid4 Server name is
'servername\instancename'.
2004-05-08 14:41:14.32 spid4 Skipping startup of clean database id
4
2004-05-08 14:41:14.34 server SQL server listening on 192.168.1.99:
1050.
2004-05-08 14:41:14.34 server SQL server listening on 127.0.0.1:
1050.
2004-05-08 14:41:14.37 spid5 Clearing tempdb database.
2004-05-08 14:41:14.40 spid5 Starting up database 'tempdb'.
2004-05-08 14:41:14.42 spid4 Recovery complete.
2004-05-08 14:41:14.42 spid4 SQL global counter collection task is
created.
2004-05-08 14:41:16.60 server SQL server listening on TCP, Shared
Memory, Named Pipes.
2004-05-08 14:41:16.60 server SQL Server is ready for client
connections
SSQLSERVER. The enterprise manager does not see MSSQLSERVER either. When I use the command "osql -Slocalhost\mssqlserver -Usa" and enter the password, I get the error message "[DBNETLIB]Invalid Connection.." The SQLMODE is set to mixed mode. I can access
MSSQLSERVER through a third party software (DB2KManager) but not through OSQL. Any suggestion would be greatly appreciated.
hi
"Surya Yadav" <Surya.Yadav@.ttu.edu> ha scritto nel messaggio
news:ED1D3FFF-4398-4407-8676-A79A31018BC0@.microsoft.com...
> I have installed SQL Server and MSDE 2000 on my laptop under Windows XP.
But I am not able to
>access the MSDE 2000 instance via OSQL. When I issue the command "osql -L"
it lists only the SQL >server (named as VSDOTNET). The MSDE 2000 instance is
named as MSSQLSERVER. The >enterprise manager does not see MSSQLSERVER
either. When I use the command "osql ->Slocalhost\mssqlserver -Usa" and
enter the password, I get the error message "[DBNETLIB]Invalid
>Connection.." The SQLMODE is set to mixed mode. I can access MSSQLSERVER
through a third >party software (DB2KManager) but not through OSQL. Any
suggestion would be greatly appreciated.
when installing a named instance, it's full name is
ComputerName\InstanceName or (local)\InstanceName or LocalHost\InstanceName,
those , your instance will be known as
YourCoumpeterName\VSDOTNET
(Local)\VSDOTNET
Localhost\VSDOTNET
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi,
Can you please explain it again? What am I doing wrong when I type "osql -Slocalhost\mssqlserver -Usa" ? The service is running as MSSQLSERVER when I browse under the Services icon from the Administrative tools. At the installation time I did not specify
an instance name. Setup.exe created the default instance as MSSQLSERVER. Thanks.
|||hi,
"Surya Yadav" <surya.yadav@.ttu.edu> ha scritto nel messaggio
news:0ED30CB4-1E7C-4CDC-9BAF-FA3F0742C9F0@.microsoft.com...
> Hi,
> Can you please explain it again? What am I doing wrong when I type
"osql -Slocalhost\mssqlserver -
> Usa" ? The service is running as MSSQLSERVER when I browse under the
Services icon from the
> Administrative tools. At the installation time I did not specify an
instance name. Setup.exe created the
> default instance as MSSQLSERVER. Thanks.
you said your named instance was named VSDOTNET... in this case, the full
instance name is
ComputerName\InstanceName = YourComputerName\VSDOTNET
if the installed instance is the default instance, then it's name is just
(Local)
and/or
YourComputerName
c:\>osql -S(local) -Usa
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi I have the same problem:
Installed MSDE with default instance and could then connect to it from
QSQL.
Then I uninstalled it and installed with a named instance. Now I cant
connect:
With "osql -U sa -S servername\instancename" command... -
"[DBMSLPCN]SQL Server does not exist or access denied.
[DBMSLPCN]ConnectionOpen (Connect())."
But the SQL server Manager icon next to the clock indicate that it is
running...
Is the port 1050 correct - from the log file below..
Any help / ideas will be much appreciated. More detail follow below:
I use "MSDE2000A" with the service pack 3a
My install options was:
[Options]
INSTANCENAME="instancename"
TARGETDIR="E:\SQLServer_instancename"
SECURITYMODE=SQL
DISABLENETWORKPROTOCOLS=0
In the error log file I found these:
====================================
2004-05-08 14:41:14.06 server Copyright (C) 1988-2002 Microsoft
Corporation.
2004-05-08 14:41:14.06 server All rights reserved.
2004-05-08 14:41:14.06 server Server Process ID is 2700.
2004-05-08 14:41:14.06 server Logging SQL Server messages in file
'E:\SQLServer_instancenameMSSQL$instancename\LOG\E RRORLOG'.
2004-05-08 14:41:14.06 server SQL Server is starting at priority
class 'normal'(2 CPUs detected).
2004-05-08 14:41:14.21 server SQL Server configured for thread mode
processing.
2004-05-08 14:41:14.21 server Using dynamic lock allocation. [500]
Lock Blocks, [1000] Lock Owner Blocks.
2004-05-08 14:41:14.26 spid4 Starting up database 'master'.
2004-05-08 14:41:14.32 server Using 'SSNETLIB.DLL' version
'8.0.766'.
2004-05-08 14:41:14.32 spid5 Starting up database 'model'.
2004-05-08 14:41:14.32 spid4 Server name is
'servername\instancename'.
2004-05-08 14:41:14.32 spid4 Skipping startup of clean database id
4
2004-05-08 14:41:14.34 server SQL server listening on 192.168.1.99:
1050.
2004-05-08 14:41:14.34 server SQL server listening on 127.0.0.1:
1050.
2004-05-08 14:41:14.37 spid5 Clearing tempdb database.
2004-05-08 14:41:14.40 spid5 Starting up database 'tempdb'.
2004-05-08 14:41:14.42 spid4 Recovery complete.
2004-05-08 14:41:14.42 spid4 SQL global counter collection task is
created.
2004-05-08 14:41:16.60 server SQL server listening on TCP, Shared
Memory, Named Pipes.
2004-05-08 14:41:16.60 server SQL Server is ready for client
connections
OSQL compact database
please help
Osql command?
how can I compact a msde database
like I do with access database
osql -E -Q "DBCC SHRINKDATABASE (db_name)"
Jacco Schalkwijk
SQL Server MVP
"Carlos AZ" <CarlosAZ@.discussions.microsoft.com> wrote in message
news:5D9C3029-BA92-4954-BB2A-C683C1D09591@.microsoft.com...
> please help
> Osql command?
> how can I compact a msde database
> like I do with access database
>
|||Hi,
To add on to Jacco, do the steps below shrinking:-
1. Backup the transaction log if the database is not SIMPLE recovery
2. Use either DBCC SHRINKDATABASE or DBCC SHRINKFILE (See books online for
usage)
Thanks
Hari
SQL Server DBA
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:ux%23r82nPFHA.1392@.TK2MSFTNGP10.phx.gbl...
> osql -E -Q "DBCC SHRINKDATABASE (db_name)"
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Carlos AZ" <CarlosAZ@.discussions.microsoft.com> wrote in message
> news:5D9C3029-BA92-4954-BB2A-C683C1D09591@.microsoft.com...
>
Osql command?
how can I compact a msde database
like I do with access database
osql -E -Q "DBCC SHRINKDATABASE (db_name)"
Jacco Schalkwijk
SQL Server MVP
"Carlos AZ" <CarlosAZ@.discussions.microsoft.com> wrote in message
news:5D9C3029-BA92-4954-BB2A-C683C1D09591@.microsoft.com...
> please help
> Osql command?
> how can I compact a msde database
> like I do with access database
>
|||Hi,
To add on to Jacco, do the steps below shrinking:-
1. Backup the transaction log if the database is not SIMPLE recovery
2. Use either DBCC SHRINKDATABASE or DBCC SHRINKFILE (See books online for
usage)
Thanks
Hari
SQL Server DBA
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:ux%23r82nPFHA.1392@.TK2MSFTNGP10.phx.gbl...
> osql -E -Q "DBCC SHRINKDATABASE (db_name)"
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Carlos AZ" <CarlosAZ@.discussions.microsoft.com> wrote in message
> news:5D9C3029-BA92-4954-BB2A-C683C1D09591@.microsoft.com...
>
Monday, February 20, 2012
OS Defrag?
My 220GB DB resides mostly on one RAID 5 logical drive. This drive reports
fragmentation. Will I see any potential improvements in disk access if I
choose to defrag? Due to text columns and fairly large/wide rows,
performance is rock bottom. As a for instance, I have a 2.2GB file and
shrinking 1MB ran overnight without completing.
Yes, it can help. see
http://www.microsoft.com/technet/abo...ps_083104.mspx
for more details.
Why are you trying to shrink your data files btw?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:90631664-5FA2-4EC2-93FC-CBA5F47ADD8E@.microsoft.com...
> My 220GB DB resides mostly on one RAID 5 logical drive. This drive
> reports
> fragmentation. Will I see any potential improvements in disk access if I
> choose to defrag? Due to text columns and fairly large/wide rows,
> performance is rock bottom. As a for instance, I have a 2.2GB file and
> shrinking 1MB ran overnight without completing.
|||The original problem was the disk performance, specifically disk reads, was
slow enough to make the system unusable. I since discoved that the purging
of some historical tables had a dramatic effect on what objects where in
cache which virtually eliminated the disk reads. The 220GB database was in 4
files on one logical drive(80GB, 40GB, 40GB, 40GB). I had created an
additional file on the same logical drive before creating files on other
logical drives in hopes of spreading out this and other DBs across 3 logical
drives each on their own channel. For the smaller database, this worked out
OK. For the 220GB DB, there are several GB free in each file. Neither DBCC
shrinkfile(emptyfile) or truncate_only finish and eventually deadlock. For
the smaller DB, I wrote a loop that shrunk the files a few MB at a time. I'm
trying to close the large files so no more data gets written to them. At 80
and 40GB, the files are too large to manage given my current level of
performance.
"Paul S Randal [MS]" wrote:
> Yes, it can help. see
> http://www.microsoft.com/technet/abo...ps_083104.mspx
> for more details.
> Why are you trying to shrink your data files btw?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:90631664-5FA2-4EC2-93FC-CBA5F47ADD8E@.microsoft.com...
>
>
fragmentation. Will I see any potential improvements in disk access if I
choose to defrag? Due to text columns and fairly large/wide rows,
performance is rock bottom. As a for instance, I have a 2.2GB file and
shrinking 1MB ran overnight without completing.
Yes, it can help. see
http://www.microsoft.com/technet/abo...ps_083104.mspx
for more details.
Why are you trying to shrink your data files btw?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:90631664-5FA2-4EC2-93FC-CBA5F47ADD8E@.microsoft.com...
> My 220GB DB resides mostly on one RAID 5 logical drive. This drive
> reports
> fragmentation. Will I see any potential improvements in disk access if I
> choose to defrag? Due to text columns and fairly large/wide rows,
> performance is rock bottom. As a for instance, I have a 2.2GB file and
> shrinking 1MB ran overnight without completing.
|||The original problem was the disk performance, specifically disk reads, was
slow enough to make the system unusable. I since discoved that the purging
of some historical tables had a dramatic effect on what objects where in
cache which virtually eliminated the disk reads. The 220GB database was in 4
files on one logical drive(80GB, 40GB, 40GB, 40GB). I had created an
additional file on the same logical drive before creating files on other
logical drives in hopes of spreading out this and other DBs across 3 logical
drives each on their own channel. For the smaller database, this worked out
OK. For the 220GB DB, there are several GB free in each file. Neither DBCC
shrinkfile(emptyfile) or truncate_only finish and eventually deadlock. For
the smaller DB, I wrote a loop that shrunk the files a few MB at a time. I'm
trying to close the large files so no more data gets written to them. At 80
and 40GB, the files are too large to manage given my current level of
performance.
"Paul S Randal [MS]" wrote:
> Yes, it can help. see
> http://www.microsoft.com/technet/abo...ps_083104.mspx
> for more details.
> Why are you trying to shrink your data files btw?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:90631664-5FA2-4EC2-93FC-CBA5F47ADD8E@.microsoft.com...
>
>
OS Defrag?
My 220GB DB resides mostly on one RAID 5 logical drive. This drive reports
fragmentation. Will I see any potential improvements in disk access if I
choose to defrag? Due to text columns and fairly large/wide rows,
performance is rock bottom. As a for instance, I have a 2.2GB file and
shrinking 1MB ran overnight without completing.Yes, it can help. see
http://www.microsoft.com/technet/abouttn/subscriptions/flash/tips/tips_083104.mspx
for more details.
Why are you trying to shrink your data files btw?
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:90631664-5FA2-4EC2-93FC-CBA5F47ADD8E@.microsoft.com...
> My 220GB DB resides mostly on one RAID 5 logical drive. This drive
> reports
> fragmentation. Will I see any potential improvements in disk access if I
> choose to defrag? Due to text columns and fairly large/wide rows,
> performance is rock bottom. As a for instance, I have a 2.2GB file and
> shrinking 1MB ran overnight without completing.|||The original problem was the disk performance, specifically disk reads, was
slow enough to make the system unusable. I since discoved that the purging
of some historical tables had a dramatic effect on what objects where in
cache which virtually eliminated the disk reads. The 220GB database was in 4
files on one logical drive(80GB, 40GB, 40GB, 40GB). I had created an
additional file on the same logical drive before creating files on other
logical drives in hopes of spreading out this and other DBs across 3 logical
drives each on their own channel. For the smaller database, this worked out
OK. For the 220GB DB, there are several GB free in each file. Neither DBCC
shrinkfile(emptyfile) or truncate_only finish and eventually deadlock. For
the smaller DB, I wrote a loop that shrunk the files a few MB at a time. I'm
trying to close the large files so no more data gets written to them. At 80
and 40GB, the files are too large to manage given my current level of
performance.
"Paul S Randal [MS]" wrote:
> Yes, it can help. see
> http://www.microsoft.com/technet/abouttn/subscriptions/flash/tips/tips_083104.mspx
> for more details.
> Why are you trying to shrink your data files btw?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:90631664-5FA2-4EC2-93FC-CBA5F47ADD8E@.microsoft.com...
> > My 220GB DB resides mostly on one RAID 5 logical drive. This drive
> > reports
> > fragmentation. Will I see any potential improvements in disk access if I
> > choose to defrag? Due to text columns and fairly large/wide rows,
> > performance is rock bottom. As a for instance, I have a 2.2GB file and
> > shrinking 1MB ran overnight without completing.
>
>
fragmentation. Will I see any potential improvements in disk access if I
choose to defrag? Due to text columns and fairly large/wide rows,
performance is rock bottom. As a for instance, I have a 2.2GB file and
shrinking 1MB ran overnight without completing.Yes, it can help. see
http://www.microsoft.com/technet/abouttn/subscriptions/flash/tips/tips_083104.mspx
for more details.
Why are you trying to shrink your data files btw?
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:90631664-5FA2-4EC2-93FC-CBA5F47ADD8E@.microsoft.com...
> My 220GB DB resides mostly on one RAID 5 logical drive. This drive
> reports
> fragmentation. Will I see any potential improvements in disk access if I
> choose to defrag? Due to text columns and fairly large/wide rows,
> performance is rock bottom. As a for instance, I have a 2.2GB file and
> shrinking 1MB ran overnight without completing.|||The original problem was the disk performance, specifically disk reads, was
slow enough to make the system unusable. I since discoved that the purging
of some historical tables had a dramatic effect on what objects where in
cache which virtually eliminated the disk reads. The 220GB database was in 4
files on one logical drive(80GB, 40GB, 40GB, 40GB). I had created an
additional file on the same logical drive before creating files on other
logical drives in hopes of spreading out this and other DBs across 3 logical
drives each on their own channel. For the smaller database, this worked out
OK. For the 220GB DB, there are several GB free in each file. Neither DBCC
shrinkfile(emptyfile) or truncate_only finish and eventually deadlock. For
the smaller DB, I wrote a loop that shrunk the files a few MB at a time. I'm
trying to close the large files so no more data gets written to them. At 80
and 40GB, the files are too large to manage given my current level of
performance.
"Paul S Randal [MS]" wrote:
> Yes, it can help. see
> http://www.microsoft.com/technet/abouttn/subscriptions/flash/tips/tips_083104.mspx
> for more details.
> Why are you trying to shrink your data files btw?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:90631664-5FA2-4EC2-93FC-CBA5F47ADD8E@.microsoft.com...
> > My 220GB DB resides mostly on one RAID 5 logical drive. This drive
> > reports
> > fragmentation. Will I see any potential improvements in disk access if I
> > choose to defrag? Due to text columns and fairly large/wide rows,
> > performance is rock bottom. As a for instance, I have a 2.2GB file and
> > shrinking 1MB ran overnight without completing.
>
>
OS Defrag?
My 220GB DB resides mostly on one RAID 5 logical drive. This drive reports
fragmentation. Will I see any potential improvements in disk access if I
choose to defrag? Due to text columns and fairly large/wide rows,
performance is rock bottom. As a for instance, I have a 2.2GB file and
shrinking 1MB ran overnight without completing.Yes, it can help. see
http://www.microsoft.com/technet/ab...04.mspx
for more details.
Why are you trying to shrink your data files btw?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:90631664-5FA2-4EC2-93FC-CBA5F47ADD8E@.microsoft.com...
> My 220GB DB resides mostly on one RAID 5 logical drive. This drive
> reports
> fragmentation. Will I see any potential improvements in disk access if I
> choose to defrag? Due to text columns and fairly large/wide rows,
> performance is rock bottom. As a for instance, I have a 2.2GB file and
> shrinking 1MB ran overnight without completing.|||The original problem was the disk performance, specifically disk reads, was
slow enough to make the system unusable. I since discoved that the purging
of some historical tables had a dramatic effect on what objects where in
cache which virtually eliminated the disk reads. The 220GB database was in
4
files on one logical drive(80GB, 40GB, 40GB, 40GB). I had created an
additional file on the same logical drive before creating files on other
logical drives in hopes of spreading out this and other DBs across 3 logical
drives each on their own channel. For the smaller database, this worked out
OK. For the 220GB DB, there are several GB free in each file. Neither DBCC
shrinkfile(emptyfile) or truncate_only finish and eventually deadlock. For
the smaller DB, I wrote a loop that shrunk the files a few MB at a time. I'
m
trying to close the large files so no more data gets written to them. At 80
and 40GB, the files are too large to manage given my current level of
performance.
"Paul S Randal [MS]" wrote:
> Yes, it can help. see
> http://www.microsoft.com/technet/ab...04.ms
px
> for more details.
> Why are you trying to shrink your data files btw?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:90631664-5FA2-4EC2-93FC-CBA5F47ADD8E@.microsoft.com...
>
>
fragmentation. Will I see any potential improvements in disk access if I
choose to defrag? Due to text columns and fairly large/wide rows,
performance is rock bottom. As a for instance, I have a 2.2GB file and
shrinking 1MB ran overnight without completing.Yes, it can help. see
http://www.microsoft.com/technet/ab...04.mspx
for more details.
Why are you trying to shrink your data files btw?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:90631664-5FA2-4EC2-93FC-CBA5F47ADD8E@.microsoft.com...
> My 220GB DB resides mostly on one RAID 5 logical drive. This drive
> reports
> fragmentation. Will I see any potential improvements in disk access if I
> choose to defrag? Due to text columns and fairly large/wide rows,
> performance is rock bottom. As a for instance, I have a 2.2GB file and
> shrinking 1MB ran overnight without completing.|||The original problem was the disk performance, specifically disk reads, was
slow enough to make the system unusable. I since discoved that the purging
of some historical tables had a dramatic effect on what objects where in
cache which virtually eliminated the disk reads. The 220GB database was in
4
files on one logical drive(80GB, 40GB, 40GB, 40GB). I had created an
additional file on the same logical drive before creating files on other
logical drives in hopes of spreading out this and other DBs across 3 logical
drives each on their own channel. For the smaller database, this worked out
OK. For the 220GB DB, there are several GB free in each file. Neither DBCC
shrinkfile(emptyfile) or truncate_only finish and eventually deadlock. For
the smaller DB, I wrote a loop that shrunk the files a few MB at a time. I'
m
trying to close the large files so no more data gets written to them. At 80
and 40GB, the files are too large to manage given my current level of
performance.
"Paul S Randal [MS]" wrote:
> Yes, it can help. see
> http://www.microsoft.com/technet/ab...04.ms
px
> for more details.
> Why are you trying to shrink your data files btw?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:90631664-5FA2-4EC2-93FC-CBA5F47ADD8E@.microsoft.com...
>
>
Subscribe to:
Posts (Atom)