Please help -- So stuck now -- Cant connect to sql from external source

Hi all,

I am having real problems allowing a client to connect to sql 2000
through enterprise manager.. I have tried all sorts but to no avail:

From our local network all works fine.. Using the adsl line.. Even if
i try and connect to the external IP and use login details..(As
opposed to connecting local and using windows accounts) I can ping and
telnet the server and port.

The server is behind a firewall so i opened up port 1433 and port
1434(? is it needed).. To replicate the client i took the same laptop
home that i use to connect in the office.. However using the exact
same config on my dial up at home i can ping the server but cannot
telnet onto port 1433.. I think that this is the vital missing step
but do not know hpw to resolve it..

Any ideas anyone

Cheers
Antanthonykallay@.hotmail.com (Anthony) wrote in message news:<77112a1e.0309040245.6d426db9@.posting.google.com>...
> Hi all,
> I am having real problems allowing a client to connect to sql 2000
> through enterprise manager.. I have tried all sorts but to no avail:
> From our local network all works fine.. Using the adsl line.. Even if
> i try and connect to the external IP and use login details..(As
> opposed to connecting local and using windows accounts) I can ping and
> telnet the server and port.
> The server is behind a firewall so i opened up port 1433 and port
> 1434(? is it needed).. To replicate the client i took the same laptop
> home that i use to connect in the office.. However using the exact
> same config on my dial up at home i can ping the server but cannot
> telnet onto port 1433.. I think that this is the vital missing step
> but do not know hpw to resolve it..
> Any ideas anyone
> Cheers
> Ant

This KB article explains what firewall configuration is required:

http://support.microsoft.com/defaul...kb;en-us;287932

Simon

Please help - DTS problems in SQL 7 --> AS/400

I was wondering if any of you could shed some light on the following
problem?
We have 4 Servers (WinNT SP6, SQL 7 SP3, IBM Client Access ODBC ver 6,
DR, Production, Development, Test) on our Datawarehousing Project.
They in turn connect to AS/4000 systems (Live, Dev, Test, 1st two
running version 4.5, last one upgraded to 5.1) for data refreshes,
using SP's and lots of DTS Packages.
Problem is that whenever we try to run DTS (using dtsrun.exe, works
fine in Designer Mode) against the upgraded AS/400 system on our Test
Server it gives out the following error:
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart:
DTSStep_DTSActiveScriptTask_5 DTSRun OnFinish:
DTSStep_DTSActiveScriptTask_5 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnError:
DTSStep_DTSExecuteSQLTask_1, Error = -2147217900 (80040E14) Error
string: up_processDTS Failed Error source: Microsoft OLE DB
Provider for SQL Server Help file: Help context: 0
Error Detail Records: Error: -2147217900 (80040E14); Provider
Error: 50000 (C350) Error string: up_processDTS Failed
Error source: Microsoft OLE DB Provider for SQL Server Help
file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 Error: -2147220440 (80040428);
Provider Error: 0 (0) Error string: Package failed because Step
'DTSStep_DTSExecuteSQLTask_1' failed. Error source: Microsoft
Data Transformation Services (DTS) Package Help file: sqldts.hlp
Help context: 700. Process Exit Code 1. The step failed.
However, when I run the same DTS Packages from our Live, Dev and DR
machines, it works fine!
I have checked and double-checked the revision details, all four boxes
are the same. I have run the error message against MS KB and IBM,
nothing. Before the AS/400 was upgraded the DTS Packages DID work, now
they only work from other NT boxes and not the Test Server which is
most bizarre. I've rebooted twice, nada.
Any help, suggestions, pointers etc. would be much appreciated.
Regards
ALIIt is only the one step that is failing
DTSStep_DTSExecuteSQLTask_1
Can you isolate this and run it. What does it do? If everything alse runs
fine and the ONLY difference is the version (5.1) then I would suggest that
as a possible cause
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"ALI" <ali.shah@.itnetplc.com> wrote in message
news:8487385c.0308070353.6f5ee16@.posting.google.com...
> I was wondering if any of you could shed some light on the following
> problem?
> We have 4 Servers (WinNT SP6, SQL 7 SP3, IBM Client Access ODBC ver 6,
> DR, Production, Development, Test) on our Datawarehousing Project.
> They in turn connect to AS/4000 systems (Live, Dev, Test, 1st two
> running version 4.5, last one upgraded to 5.1) for data refreshes,
> using SP's and lots of DTS Packages.
> Problem is that whenever we try to run DTS (using dtsrun.exe, works
> fine in Designer Mode) against the upgraded AS/400 system on our Test
> Server it gives out the following error:
> DTSRun: Loading... DTSRun: Executing... DTSRun OnStart:
> DTSStep_DTSActiveScriptTask_5 DTSRun OnFinish:
> DTSStep_DTSActiveScriptTask_5 DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_1 DTSRun OnError:
> DTSStep_DTSExecuteSQLTask_1, Error = -2147217900 (80040E14) Error
> string: up_processDTS Failed Error source: Microsoft OLE DB
> Provider for SQL Server Help file: Help context: 0
> Error Detail Records: Error: -2147217900 (80040E14); Provider
> Error: 50000 (C350) Error string: up_processDTS Failed
> Error source: Microsoft OLE DB Provider for SQL Server Help
> file: Help context: 0 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_1 Error: -2147220440 (80040428);
> Provider Error: 0 (0) Error string: Package failed because Step
> 'DTSStep_DTSExecuteSQLTask_1' failed. Error source: Microsoft
> Data Transformation Services (DTS) Package Help file: sqldts.hlp
> Help context: 700. Process Exit Code 1. The step failed.
> However, when I run the same DTS Packages from our Live, Dev and DR
> machines, it works fine!
> I have checked and double-checked the revision details, all four boxes
> are the same. I have run the error message against MS KB and IBM,
> nothing. Before the AS/400 was upgraded the DTS Packages DID work, now
> they only work from other NT boxes and not the Test Server which is
> most bizarre. I've rebooted twice, nada.
> Any help, suggestions, pointers etc. would be much appreciated.
> Regards
> ALI

Please evaluate this approach to shrinking log files

I've been tasked with taking over the support for a client's SQL Server
database. I'm not a DBA by profession, I'm a software developer who
uses SQL Server as a database designer.

The clients have reported that the server is running out of disk space
and examination shows that the log files for several of the databases
are at 5Gb or more.

After reading around the subject I suggested the following sequence of
operations:

-- Select the name of the database you want to shrink
USE MyDB

-- Dump unwanted transactions
dump tran MyDB with truncate_only

-- Get the name of the logfile
SELECT * from sysfiles

-- Having examined the rows returned by this use the log file...

-- Shrink the file to required size (in MB)
DBCC SHRINKFILE('MyDB_log', 10)

Is this a reasonable approach? Please bear in mind that I'm pretty new
to this, and I have many other tasks to do besides manage the server.
A previous DBA has set up good maintenance plans etc. so everything is
being properly backed up (well, I think it is)

If this IS a good approach, would it be reasonable to do this on, say,
a monthly basis as a scheduled job? Obviously the step

SELECT * from sysfiles

which gives us the physical name of the log file would be removed and
the job would operate explicitly on each log file for each database in
turn.

Many thanks for reading.

William Balmer.A previous DBA has set up good maintenance plans etc. so everything is

Quote:

Originally Posted by

being properly backed up (well, I think it is)

A common cause of unruly log files is that the database is in the FULL
recovery model but regular transaction log backups are not scheduled. Since
committed transactions won't get removed from the log until the log backup,
manual action is required to reduce the log size.

If you need to minimize data loss, the best approach is to schedule regular
log backups to run periodically between full database backups. You can
include this as part of a database maintenance plan. If more data loss is
acceptable (i.e. your plan is to simply restore from the last full backup),
you can use the SIMPLE recovery model so that committed data are
automatically removed from the log and you don't need to bother with log
backups.

In any case, the log should be sized to accommodate the activity between log
backups (FULL recovery) or the largest transaction (SIMPLE). The high-water
mark of this log space is simply the cost of doing business. IMHO, log file
shrinking should be done only after the log grows due to unusual activity.
I wouldn't schedule log shrinking because automating the process will only
hide the underlying cause.

Keep in mind that the number one responsibility of a DBA (or one that wears
a DBA hat) is to implement and test a backup/recovery plan. If you haven't
already done so, I suggest you run a restore test. You don't want any
surprises when you need to do it for real.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"William" <williambalmer@.hotmail.comwrote in message
news:1157713799.376752.153540@.i3g2000cwc.googlegro ups.com...

Quote:

Originally Posted by

I've been tasked with taking over the support for a client's SQL Server
database. I'm not a DBA by profession, I'm a software developer who
uses SQL Server as a database designer.
>
The clients have reported that the server is running out of disk space
and examination shows that the log files for several of the databases
are at 5Gb or more.
>
After reading around the subject I suggested the following sequence of
operations:
>
-- Select the name of the database you want to shrink
USE MyDB
>
-- Dump unwanted transactions
dump tran MyDB with truncate_only
>
-- Get the name of the logfile
SELECT * from sysfiles
>
-- Having examined the rows returned by this use the log file...
>
-- Shrink the file to required size (in MB)
DBCC SHRINKFILE('MyDB_log', 10)
>
Is this a reasonable approach? Please bear in mind that I'm pretty new
to this, and I have many other tasks to do besides manage the server.
A previous DBA has set up good maintenance plans etc. so everything is
being properly backed up (well, I think it is)
>
If this IS a good approach, would it be reasonable to do this on, say,
a monthly basis as a scheduled job? Obviously the step
>
SELECT * from sysfiles
>
which gives us the physical name of the log file would be removed and
the job would operate explicitly on each log file for each database in
turn.
>
Many thanks for reading.
>
William Balmer.
>

|||Dan Guzman wrote:

snip

Many thanks, Dan - much to think about. I'll read up on the various
topics and may post later if I come up against a brick wall!

Thanks again

William Balmer

Planning for Merge - security

Synchronizing passes information (sometimes sensitive) over a wire.
My client, the U.S. Government, will be sync'n over a VPN tunnel to the main
SQL Server database from various field office locations across the country.
My question is: can the sync operation (bulk load/transactions passed) be
encrypted, irrespective of any hardware/network method, by SQL Server during
the process? Any thoughts are appreciated..
Normally when you use a VPN the communication is encrypted enough. For a
greater level of encryption, you can configure your server using Server
Network Utility to force protocol encryption.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"JimMac" <JimMac@.discussions.microsoft.com> wrote in message
news:A201999B-B63B-415B-BA47-B0D1AF1EF622@.microsoft.com...
> Synchronizing passes information (sometimes sensitive) over a wire.
> My client, the U.S. Government, will be sync'n over a VPN tunnel to the
main
> SQL Server database from various field office locations across the
country.
> My question is: can the sync operation (bulk load/transactions passed) be
> encrypted, irrespective of any hardware/network method, by SQL Server
during
> the process? Any thoughts are appreciated..

Pivottable 10/11 with SSAS 2005 cube 'hang' when using filter fields

Dear all,

I am running into some difficulties with an SSAS 2005 SP2 cube in combination with an OWC 11 pivottable client. Everything basically works fine.

The problem occurs when I make a selection with one or more dimensions in the 'filter' section.

For instance, if I build a pivottable which shows:

YEAR in the Colums

PRODUCT GROUP in the ROWS

CUSTOMER NAME in the ROWS

PRODUCT FAMILY in FILTER SECTION

SALES as Measure

As soon as I make a selection on YEAR (show only 2007) and PRODUCT FAMILY (show only 'bikes' and 'minibikes') , the pivottable basically hangs. On the server the memory usage of SSAS shoots up to 1.8(!) GB

After +/- 60 seconds I get a blank screen and sometimes: 'Cannot display data because of structural changes in the database'.

I tried to make the same selecion in my Excel2007 eval, which worked fine. Pivottable 10 gives the same error. The error also occurs in Visual Studio (off course also OWC11).

Altering the cube's storage settings did not help.

My cube's main facttable containts approx 2.5 mln records.

Is this a known problem, what can I do to fix it?

Many many thanks

Rex

Dear all,

After doing some research it seems that SP2 is causing the errors. When downgrading my SSAS 2005 to SP1 all problems vanished. Only problem now is the performance compared to SP2 which is significantly lower.

To really make sure it was SP2 I installed a fresh SSAS2005 machine without any servicepacks and built my cube, after intensive testing no problems.

Upgraded to SP1, no problems.

Upgrade to SP2, massive memory usage of the SSAS service and total lockup.

I also installed the cumulative 3161 post SP2 update, but this did not fix the issue.

The problem only occurs when filtering data in the pivottable's 'filter' section.

Any ideas?

|||

Problem solved!!

After installing the Cumulative Hoftfixes 2 for SP2 (build 3175), released Jun 22nd the problem has disappeared and the performance of my cubes is great!

Link to the hotfix: http://support.microsoft.com/kb/936305