DTS to .net: system

Showing posts with label system. Show all posts

Tuesday, March 27, 2012

Duplicate records - difference method?

Hi,
My scenario is that i have 2 system with name and adress (100.000 names),
that have to be merged into 1 system without any duplicates.
The problem is that the spelling is not 100% between the system.
One way to find duplicate is to group name,adress and count > 1.
My dream is to use the sound index "Difference" so can i get around the
spelling problem.
DIFFERENCE
Returns the difference between the SOUNDEX values of two character
expressions as an integer.
Syntax
DIFFERENCE ( character_expression , character_expression )
Is that possible to use DIFFERENCE to find duplicates?
And how should the t-sql look like?
Example
name adress city
charles way1 state1
charle waj1 stat1
charlez vay1 stat1
I want to find this example, that this 3 is duplicates.
Should i use ordinary way with group and count >1, this would not be
duplicates.
Help
Thanx
TwSo you want these to be considered duplicates:
FirstName LastName
Jon Smyth
John Smith
Jonathan Smythe
John Smyth
'|||Hi,
Yes, i want these to be considered duplicates.
But i also want to validate adress and city to see if these is duplicates.
FirstName LastName Adress City
Jon Smyth Street 1 Palace1
John Smith Stret1 Palac1
Jonathan Smythe Stret 1 Palac 1
John Smyth Street1 Palace 1
Who can i do that in a t-sql?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i
meddelandet news:uey1ZpUkFHA.2852@.TK2MSFTNGP15.phx.gbl...
> So you want these to be considered duplicates:
> FirstName LastName
> Jon Smyth
> John Smith
> Jonathan Smythe
> John Smyth
> '
>|||>> have 2 system with name and adress (100.000 names), that have to be mer
ged into 1 system without any duplicates. The problem is that the spelling i
s not 100% between the system. <<
Look up Melissa Data and get their software. Life is too short to
re-invent the wheel.|||But it cost a lot of money.
It should not be so difficult to do it in sql server.
I looking for a little help and after that i fix it, i hope :)
For the soundindex and difference function is in SQL Server.
Can i only run a t-sql and get out the difference integer, after that i fix
a algorithm.
But how should i run the t-sql to validate these values?
// tw
"--CELKO--" <jcelko212@.earthlink.net> skrev i meddelandet
news:1122318950.891968.136790@.g14g2000cwa.googlegroups.com...
> Look up Melissa Data and get their software. Life is too short to
> re-invent the wheel.
>|||>> But it cost a lot of money.<<
How much does doing it wrong cost you? How much is your time worth?|||> But how should i run the t-sql to validate these values?
T-SQL is supplying you the difference and soundex values, no?
So, you must do one of three things:
(a) determine beforehand what soundex/difference level means duplicate;
(b) inspect the results manually and make decisions; or,
(c) get software that does it right, and you might get some sleep at night.

Monday, March 19, 2012

Dumb question about SSIS

I just imported data from one database to another, I specified to save the
SSIS package on the database server (not on the file system).
Now I cannot find the package anywhere. There isn't a "Local Package" tree
like in 2000 Enterprise Manager. I can't even find a shortcut for starting
SSIS in my Start menu, and the SSIS Tutorial only shows how to create a new
project in VS, not how to open an existing package.Hello,
You could click "Connect" button in Server management studio and click to
select "Integration services", and then type the instance information you
want.
Hope this is helpful.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>Thread-Topic: Dumb question about SSIS
>thread-index: AcXx8eErD985vRkIQ9eWYJ2xuQK32Q==>X-WBNR-Posting-Host: 84.56.32.28
>From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
>Subject: Dumb question about SSIS
>Date: Fri, 25 Nov 2005 10:56:02 -0800
>Lines: 7
>Message-ID: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.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.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412281
>X-Tomcat-NG: microsoft.public.sqlserver.server
>I just imported data from one database to another, I specified to save the
>SSIS package on the database server (not on the file system).
>Now I cannot find the package anywhere. There isn't a "Local Package" tree
>like in 2000 Enterprise Manager. I can't even find a shortcut for starting
>SSIS in my Start menu, and the SSIS Tutorial only shows how to create a
new
>project in VS, not how to open an existing package.
>|||Thanks Peter, exactly what I was looking for.
But now I still cannot see my package, as I'm getting following error
message when clicking on MSDB under "Stored Packages" in the Object Explorer
tree. Any idea why?
Client unable to establish connection
Encryption not supported on SQL Server. (Microsoft SQL Native Client)
at
Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetDtsServerPackageInfos(String bstrPackageFolder, String bstrServerName)
at
Microsoft.SqlServer.Dts.Runtime.Application.GetDtsServerPackageInfos(String
sPackageFolder, String sServerName)
"Peter Yang [MSFT]" wrote:
> Hello,
> You could click "Connect" button in Server management studio and click to
> select "Integration services", and then type the instance information you
> want.
> Hope this is helpful.
>
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> >Thread-Topic: Dumb question about SSIS
> >thread-index: AcXx8eErD985vRkIQ9eWYJ2xuQK32Q==> >X-WBNR-Posting-Host: 84.56.32.28
> >From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
> >Subject: Dumb question about SSIS
> >Date: Fri, 25 Nov 2005 10:56:02 -0800
> >Lines: 7
> >Message-ID: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.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.server
> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> >Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
> >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412281
> >X-Tomcat-NG: microsoft.public.sqlserver.server
> >
> >I just imported data from one database to another, I specified to save the
> >SSIS package on the database server (not on the file system).
> >Now I cannot find the package anywhere. There isn't a "Local Package" tree
> >like in 2000 Enterprise Manager. I can't even find a shortcut for starting
> >SSIS in my Start menu, and the SSIS Tutorial only shows how to create a
> new
> >project in VS, not how to open an existing package.
> >
> >
>|||Hello,
To understand the issue better, will you confirm the default instance is
SQL server 2005 instance? SSIS does not support named instance anyway.
Also, from the error message, it seems that client force encryption but
server does not have certificate installed. Please check the following
settings:
Run SQL Server 2005 Network Configuration, right click SQL native client
network conigurations->Properties, and configure "force protocol
encryption" to NO.
Right click the protocol under server network configuration and also
configure "force protocol encryption" to NO.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>Thread-Topic: Dumb question about SSIS
>thread-index: AcX0F5fDrM411FMOTvOM/FE4ub5KUA==>X-WBNR-Posting-Host: 84.147.181.254
>From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
>References: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
<hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
>Subject: RE: Dumb question about SSIS
>Date: Mon, 28 Nov 2005 04:31:02 -0800
>Lines: 76
>Message-ID: <36EAC47F-E63C-4DA8-9F75-ED51B80E761B@.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.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412425
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Thanks Peter, exactly what I was looking for.
>But now I still cannot see my package, as I'm getting following error
>message when clicking on MSDB under "Stored Packages" in the Object
Explorer
>tree. Any idea why?
>Client unable to establish connection
>Encryption not supported on SQL Server. (Microsoft SQL Native Client)
> at
>Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetDtsServerPackag
eInfos(String bstrPackageFolder, String bstrServerName)
> at
>Microsoft.SqlServer.Dts.Runtime.Application.GetDtsServerPackageInfos(String
>sPackageFolder, String sServerName)
>"Peter Yang [MSFT]" wrote:
>> Hello,
>> You could click "Connect" button in Server management studio and click
to
>> select "Integration services", and then type the instance information
you
>> want.
>> Hope this is helpful.
>>
>> Best Regards,
>> Peter Yang
>> MCSE2000/2003, MCSA, MCDBA
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>>
>> This posting is provided "AS IS" with no warranties, and confers no
rights.
>>
>> --
>> >Thread-Topic: Dumb question about SSIS
>> >thread-index: AcXx8eErD985vRkIQ9eWYJ2xuQK32Q==>> >X-WBNR-Posting-Host: 84.56.32.28
>> >From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
>> >Subject: Dumb question about SSIS
>> >Date: Fri, 25 Nov 2005 10:56:02 -0800
>> >Lines: 7
>> >Message-ID: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.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.server
>> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>> >Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
>> >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412281
>> >X-Tomcat-NG: microsoft.public.sqlserver.server
>> >
>> >I just imported data from one database to another, I specified to save
the
>> >SSIS package on the database server (not on the file system).
>> >Now I cannot find the package anywhere. There isn't a "Local Package"
tree
>> >like in 2000 Enterprise Manager. I can't even find a shortcut for
starting
>> >SSIS in my Start menu, and the SSIS Tutorial only shows how to create a
>> new
>> >project in VS, not how to open an existing package.
>> >
>> >
>>
>|||Both encryption settings were set to No.
Also, SQL Server 2005 is a named instance. The default instance is SQL
Server 2000.
In the "Connect to Server" dialog of management studio, I connect to
Integration Service, and as server name, the name of the machine. Does that
mean that I'm attempting to connect to the default instance? That would be
SQL 2000.
Does that mean that you cannot use SSIS on a machine where there is default
instance of SQL 2000'
"Peter Yang [MSFT]" wrote:
> Hello,
> To understand the issue better, will you confirm the default instance is
> SQL server 2005 instance? SSIS does not support named instance anyway.
> Also, from the error message, it seems that client force encryption but
> server does not have certificate installed. Please check the following
> settings:
> Run SQL Server 2005 Network Configuration, right click SQL native client
> network conigurations->Properties, and configure "force protocol
> encryption" to NO.
> Right click the protocol under server network configuration and also
> configure "force protocol encryption" to NO.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> >Thread-Topic: Dumb question about SSIS
> >thread-index: AcX0F5fDrM411FMOTvOM/FE4ub5KUA==> >X-WBNR-Posting-Host: 84.147.181.254
> >From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
> >References: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
> <hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
> >Subject: RE: Dumb question about SSIS
> >Date: Mon, 28 Nov 2005 04:31:02 -0800
> >Lines: 76
> >Message-ID: <36EAC47F-E63C-4DA8-9F75-ED51B80E761B@.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.server
> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> >Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
> >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412425
> >X-Tomcat-NG: microsoft.public.sqlserver.server
> >
> >Thanks Peter, exactly what I was looking for.
> >But now I still cannot see my package, as I'm getting following error
> >message when clicking on MSDB under "Stored Packages" in the Object
> Explorer
> >tree. Any idea why?
> >
> >Client unable to establish connection
> >Encryption not supported on SQL Server. (Microsoft SQL Native Client)
> >
> > at
> >Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetDtsServerPackag
> eInfos(String bstrPackageFolder, String bstrServerName)
> > at
> >Microsoft.SqlServer.Dts.Runtime.Application.GetDtsServerPackageInfos(String
> >sPackageFolder, String sServerName)
> >
> >"Peter Yang [MSFT]" wrote:
> >
> >> Hello,
> >>
> >> You could click "Connect" button in Server management studio and click
> to
> >> select "Integration services", and then type the instance information
> you
> >> want.
> >>
> >> Hope this is helpful.
> >>
> >>
> >> Best Regards,
> >>
> >> Peter Yang
> >> MCSE2000/2003, MCSA, MCDBA
> >> Microsoft Online Partner Support
> >>
> >> When responding to posts, please "Reply to Group" via your newsreader so
> >> that others may learn and benefit from your issue.
> >>
> >> =====================================================> >>
> >>
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >>
> >>
> >> --
> >> >Thread-Topic: Dumb question about SSIS
> >> >thread-index: AcXx8eErD985vRkIQ9eWYJ2xuQK32Q==> >> >X-WBNR-Posting-Host: 84.56.32.28
> >> >From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
> >> >Subject: Dumb question about SSIS
> >> >Date: Fri, 25 Nov 2005 10:56:02 -0800
> >> >Lines: 7
> >> >Message-ID: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.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.server
> >> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> >> >Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
> >> >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412281
> >> >X-Tomcat-NG: microsoft.public.sqlserver.server
> >> >
> >> >I just imported data from one database to another, I specified to save
> the
> >> >SSIS package on the database server (not on the file system).
> >> >Now I cannot find the package anywhere. There isn't a "Local Package"
> tree
> >> >like in 2000 Enterprise Manager. I can't even find a shortcut for
> starting
> >> >SSIS in my Start menu, and the SSIS Tutorial only shows how to create a
> >> new
> >> >project in VS, not how to open an existing package.
> >> >
> >> >
> >>
> >>
> >
>|||Hello,
You could still store SSIS package in filesystem other than msdb under this
situation. Since SSIS will try to access msde of default instance, you
could not store the package in msdb because it is a 2000 database.
Also, you may try the following method to change the instance SSIS
connects. However, it is not officially documented and may have issues when
running package from there
1. Edit the Configuration file MsDtsSrvr.ini.xml in the following path:
C:\Program Files\Microsoft SQL Server\90\DTS\Binn
2. Change the string <ServerName>.</ServerName>?as following:
<ServerName>MachineName\InstanceName</ServerName>
3. Save the file and Re-Start SSIS Service
Please let me know if you have further concerns or questions on this.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>Thread-Topic: Dumb question about SSIS
>thread-index: AcX02Vvn5mIYncaZQVq2jfNzlN2UsA==>X-WBNR-Posting-Host: 84.147.129.198
>From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
>References: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
<hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
<36EAC47F-E63C-4DA8-9F75-ED51B80E761B@.microsoft.com>
<yI1K45L9FHA.4000@.TK2MSFTNGXA02.phx.gbl>
>Subject: RE: Dumb question about SSIS
>Date: Tue, 29 Nov 2005 03:38:03 -0800
>Lines: 159
>Message-ID: <43861D30-F658-4906-B197-CE2D9DE8D97C@.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.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412564
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Both encryption settings were set to No.
>Also, SQL Server 2005 is a named instance. The default instance is SQL
>Server 2000.
>In the "Connect to Server" dialog of management studio, I connect to
>Integration Service, and as server name, the name of the machine. Does
that
>mean that I'm attempting to connect to the default instance? That would be
>SQL 2000.
>Does that mean that you cannot use SSIS on a machine where there is
default
>instance of SQL 2000'
>
>"Peter Yang [MSFT]" wrote:
>> Hello,
>> To understand the issue better, will you confirm the default instance is
>> SQL server 2005 instance? SSIS does not support named instance anyway.
>> Also, from the error message, it seems that client force encryption but
>> server does not have certificate installed. Please check the following
>> settings:
>> Run SQL Server 2005 Network Configuration, right click SQL native client
>> network conigurations->Properties, and configure "force protocol
>> encryption" to NO.
>> Right click the protocol under server network configuration and also
>> configure "force protocol encryption" to NO.
>> Best Regards,
>> Peter Yang
>> MCSE2000/2003, MCSA, MCDBA
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>>
>> This posting is provided "AS IS" with no warranties, and confers no
rights.
>>
>> --
>> >Thread-Topic: Dumb question about SSIS
>> >thread-index: AcX0F5fDrM411FMOTvOM/FE4ub5KUA==>> >X-WBNR-Posting-Host: 84.147.181.254
>> >From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
>> >References: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
>> <hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
>> >Subject: RE: Dumb question about SSIS
>> >Date: Mon, 28 Nov 2005 04:31:02 -0800
>> >Lines: 76
>> >Message-ID: <36EAC47F-E63C-4DA8-9F75-ED51B80E761B@.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.server
>> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>> >Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
>> >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412425
>> >X-Tomcat-NG: microsoft.public.sqlserver.server
>> >
>> >Thanks Peter, exactly what I was looking for.
>> >But now I still cannot see my package, as I'm getting following error
>> >message when clicking on MSDB under "Stored Packages" in the Object
>> Explorer
>> >tree. Any idea why?
>> >
>> >Client unable to establish connection
>> >Encryption not supported on SQL Server. (Microsoft SQL Native Client)
>> >
>> > at
>Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetDtsServerPackag
>> eInfos(String bstrPackageFolder, String bstrServerName)
>> > at
>Microsoft.SqlServer.Dts.Runtime.Application.GetDtsServerPackageInfos(String
>> >sPackageFolder, String sServerName)
>> >
>> >"Peter Yang [MSFT]" wrote:
>> >
>> >> Hello,
>> >>
>> >> You could click "Connect" button in Server management studio and
click
>> to
>> >> select "Integration services", and then type the instance information
>> you
>> >> want.
>> >>
>> >> Hope this is helpful.
>> >>
>> >>
>> >> Best Regards,
>> >>
>> >> Peter Yang
>> >> MCSE2000/2003, MCSA, MCDBA
>> >> Microsoft Online Partner Support
>> >>
>> >> When responding to posts, please "Reply to Group" via your newsreader
so
>> >> that others may learn and benefit from your issue.
>> >>
>> >> =====================================================>> >>
>> >>
>> >>
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> >>
>> >>
>> >> --
>> >> >Thread-Topic: Dumb question about SSIS
>> >> >thread-index: AcXx8eErD985vRkIQ9eWYJ2xuQK32Q==>> >> >X-WBNR-Posting-Host: 84.56.32.28
>> >> >From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
>> >> >Subject: Dumb question about SSIS
>> >> >Date: Fri, 25 Nov 2005 10:56:02 -0800
>> >> >Lines: 7
>> >> >Message-ID: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.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.server
>> >> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>> >> >Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
>> >> >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412281
>> >> >X-Tomcat-NG: microsoft.public.sqlserver.server
>> >> >
>> >> >I just imported data from one database to another, I specified to
save
>> the
>> >> >SSIS package on the database server (not on the file system).
>> >> >Now I cannot find the package anywhere. There isn't a "Local
Package"
>> tree
>> >> >like in 2000 Enterprise Manager. I can't even find a shortcut for
>> starting
>> >> >SSIS in my Start menu, and the SSIS Tutorial only shows how to
create a
>> >> new
>> >> >project in VS, not how to open an existing package.
>> >> >
>> >> >
>> >>
>> >>
>> >
>>
>|||This sounds like a solution. Many thanks.
"Peter Yang [MSFT]" wrote:
> Hello,
> You could still store SSIS package in filesystem other than msdb under this
> situation. Since SSIS will try to access msde of default instance, you
> could not store the package in msdb because it is a 2000 database.
> Also, you may try the following method to change the instance SSIS
> connects. However, it is not officially documented and may have issues when
> running package from there
> 1. Edit the Configuration file MsDtsSrvr.ini.xml in the following path:
> C:\Program Files\Microsoft SQL Server\90\DTS\Binn
> 2. Change the string <ServerName>.</ServerName>?as following:
> <ServerName>MachineName\InstanceName</ServerName>
> 3. Save the file and Re-Start SSIS Service
> Please let me know if you have further concerns or questions on this.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> >Thread-Topic: Dumb question about SSIS
> >thread-index: AcX02Vvn5mIYncaZQVq2jfNzlN2UsA==> >X-WBNR-Posting-Host: 84.147.129.198
> >From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
> >References: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
> <hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
> <36EAC47F-E63C-4DA8-9F75-ED51B80E761B@.microsoft.com>
> <yI1K45L9FHA.4000@.TK2MSFTNGXA02.phx.gbl>
> >Subject: RE: Dumb question about SSIS
> >Date: Tue, 29 Nov 2005 03:38:03 -0800
> >Lines: 159
> >Message-ID: <43861D30-F658-4906-B197-CE2D9DE8D97C@.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.server
> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> >Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
> >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412564
> >X-Tomcat-NG: microsoft.public.sqlserver.server
> >
> >Both encryption settings were set to No.
> >Also, SQL Server 2005 is a named instance. The default instance is SQL
> >Server 2000.
> >In the "Connect to Server" dialog of management studio, I connect to
> >Integration Service, and as server name, the name of the machine. Does
> that
> >mean that I'm attempting to connect to the default instance? That would be
> >SQL 2000.
> >Does that mean that you cannot use SSIS on a machine where there is
> default
> >instance of SQL 2000'
> >
> >
> >"Peter Yang [MSFT]" wrote:
> >
> >> Hello,
> >>
> >> To understand the issue better, will you confirm the default instance is
> >> SQL server 2005 instance? SSIS does not support named instance anyway.
> >>
> >> Also, from the error message, it seems that client force encryption but
> >> server does not have certificate installed. Please check the following
> >> settings:
> >>
> >> Run SQL Server 2005 Network Configuration, right click SQL native client
> >> network conigurations->Properties, and configure "force protocol
> >> encryption" to NO.
> >>
> >> Right click the protocol under server network configuration and also
> >> configure "force protocol encryption" to NO.
> >>
> >> Best Regards,
> >>
> >> Peter Yang
> >> MCSE2000/2003, MCSA, MCDBA
> >> Microsoft Online Partner Support
> >>
> >> When responding to posts, please "Reply to Group" via your newsreader so
> >> that others may learn and benefit from your issue.
> >>
> >> =====================================================> >>
> >>
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >>
> >>
> >> --
> >> >Thread-Topic: Dumb question about SSIS
> >> >thread-index: AcX0F5fDrM411FMOTvOM/FE4ub5KUA==> >> >X-WBNR-Posting-Host: 84.147.181.254
> >> >From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
> >> >References: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
> >> <hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
> >> >Subject: RE: Dumb question about SSIS
> >> >Date: Mon, 28 Nov 2005 04:31:02 -0800
> >> >Lines: 76
> >> >Message-ID: <36EAC47F-E63C-4DA8-9F75-ED51B80E761B@.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.server
> >> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> >> >Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
> >> >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412425
> >> >X-Tomcat-NG: microsoft.public.sqlserver.server
> >> >
> >> >Thanks Peter, exactly what I was looking for.
> >> >But now I still cannot see my package, as I'm getting following error
> >> >message when clicking on MSDB under "Stored Packages" in the Object
> >> Explorer
> >> >tree. Any idea why?
> >> >
> >> >Client unable to establish connection
> >> >Encryption not supported on SQL Server. (Microsoft SQL Native Client)
> >> >
> >> > at
> >>
> >Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetDtsServerPackag
> >> eInfos(String bstrPackageFolder, String bstrServerName)
> >> > at
> >>
> >Microsoft.SqlServer.Dts.Runtime.Application.GetDtsServerPackageInfos(String
> >>
> >> >sPackageFolder, String sServerName)
> >> >
> >> >"Peter Yang [MSFT]" wrote:
> >> >
> >> >> Hello,
> >> >>
> >> >> You could click "Connect" button in Server management studio and
> click
> >> to
> >> >> select "Integration services", and then type the instance information
> >> you
> >> >> want.
> >> >>
> >> >> Hope this is helpful.
> >> >>
> >> >>
> >> >> Best Regards,
> >> >>
> >> >> Peter Yang
> >> >> MCSE2000/2003, MCSA, MCDBA
> >> >> Microsoft Online Partner Support
> >> >>
> >> >> When responding to posts, please "Reply to Group" via your newsreader
> so
> >> >> that others may learn and benefit from your issue.
> >> >>
> >> >> =====================================================> >> >>
> >> >>
> >> >>
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >> >>
> >> >>
> >> >> --
> >> >> >Thread-Topic: Dumb question about SSIS
> >> >> >thread-index: AcXx8eErD985vRkIQ9eWYJ2xuQK32Q==> >> >> >X-WBNR-Posting-Host: 84.56.32.28
> >> >> >From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
> >> >> >Subject: Dumb question about SSIS
> >> >> >Date: Fri, 25 Nov 2005 10:56:02 -0800
> >> >> >Lines: 7
> >> >> >Message-ID: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.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.server
> >> >> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> >> >> >Path:
> TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
> >> >> >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412281
> >> >> >X-Tomcat-NG: microsoft.public.sqlserver.server
> >> >> >
> >> >> >I just imported data from one database to another, I specified to
> save
> >> the
> >> >> >SSIS package on the database server (not on the file system).
> >> >> >Now I cannot find the package anywhere. There isn't a "Local
> Package"
> >> tree
> >> >> >like in 2000 Enterprise Manager. I can't even find a shortcut for
> >> starting
> >> >> >SSIS in my Start menu, and the SSIS Tutorial only shows how to
> create a
> >> >> new
> >> >> >project in VS, not how to open an existing package.
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >>
> >>
> >
>

Dumb question about SSIS

Sunday, March 11, 2012

dumb question

Just bought a new computer, and want to install vb express. There is an icon in my system tray for Microsoft SQL Server Service Manager ver. 8.00.2039. Does this mean that I won't have to install SQL Server Express? I can't find anything in the Add/Remove programs that shows any version of SQL Server that was pre-installed.

thanks

richard

Hi Richard,

You have SQL Server 2000, probably MSDE. You will want to have SQL Express for tight integration with VB Express. I would go install SQL Express from http://msdn.microsoft.com/vstudio/express/sql/download first, then install VB Express. VB Express will offer to install SQL if it isn't already there, but it is the RTM version, not SP1.

Mike

Dual Transaction Log

I have just taken over an SQL Server 7.0 database which has two transaction
log files. One is on the system drive, and the other is on the same drive as
the database..
The transaction log on the system drive is huge, leaving very little free
space, whilst the database drive has tonnes of free space..
How do I remove the log file on the system drive, and just use the second
one?
Thnak you
Nick
Hi,
Best way is :-
1. Detach the database using sp_detach_db
2. copy the LDF file in system drive to new location
3. Attach the database using SP_ATTACH_DB, specify the new LDF location.
One more method to remove ldf , is backup the database and TX log , shrink
the LDF file using DBCC SHRINKFILE and REMOVE the FILE using Alter database
see the below link for details
http://groups.google.com/groups?q=re...t .com&rnum=5
Thanks
Hari
MCDBA
"nh" <anonymous@.discussions.microsoft.com> wrote in message
news:egnAeCymEHA.2372@.TK2MSFTNGP10.phx.gbl...
>I have just taken over an SQL Server 7.0 database which has two transaction
> log files. One is on the system drive, and the other is on the same drive
> as
> the database..
> The transaction log on the system drive is huge, leaving very little free
> space, whilst the database drive has tonnes of free space..
> How do I remove the log file on the system drive, and just use the second
> one?
> Thnak you
> Nick
>

Dual Transaction Log

I have just taken over an SQL Server 7.0 database which has two transaction
log files. One is on the system drive, and the other is on the same drive as
the database..
The transaction log on the system drive is huge, leaving very little free
space, whilst the database drive has tonnes of free space..
How do I remove the log file on the system drive, and just use the second
one?
Thnak you
NickHi,
Best way is :-
1. Detach the database using sp_detach_db
2. copy the LDF file in system drive to new location
3. Attach the database using SP_ATTACH_DB, specify the new LDF location.
One more method to remove ldf , is backup the database and TX log , shrink
the LDF file using DBCC SHRINKFILE and REMOVE the FILE using Alter database
see the below link for details
http://groups.google.com/groups?q=remove+log+file+in+sql+7&hl=en&lr=&ie=UTF-8&selm=us%24RwvUb%24GA.223%40cppssbbsa02.microsoft.com&rnum=5
Thanks
Hari
MCDBA
"nh" <anonymous@.discussions.microsoft.com> wrote in message
news:egnAeCymEHA.2372@.TK2MSFTNGP10.phx.gbl...
>I have just taken over an SQL Server 7.0 database which has two transaction
> log files. One is on the system drive, and the other is on the same drive
> as
> the database..
> The transaction log on the system drive is huge, leaving very little free
> space, whilst the database drive has tonnes of free space..
> How do I remove the log file on the system drive, and just use the second
> one?
> Thnak you
> Nick
>

Dual Quad Core - CPU settings?

I have a IBM Dual Quad Core CPU system running SQL 2000 and all
SP/Updates. I'm seeing a log of blocking because of a vendors app that I
have no control over, causes the CPU Cores to run around 50% during the
extended periods of blocking. System is using 3.2GB of the 4GB (Windows
2003 Std, SQL 2000 Std CPU Licenses) and SQL is set to not use CPU Core
0 (actually, it was set to use all cores, but it has not made any
performance difference by not allowing access to Core 0 - this is a
Domain Controller and normally shows 3% load across all cores).
Should I limit the access to Cores to just 4-7 (second CPU) or just Core
0/4 and not the others, or should I just allow all cores?
Also, with Win 2003 Std and SQL 2000 Std (CPU licenses), should I be
using AWE or PAE modes? I'm currently setup for the default startup
mode.
Thanks - sorry for the ramble, been a long night.
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)You really seem to have underpowered the system when it comes to memory
compared to CPU's. SQL 2000 Std edition can only use 2GB of memory and the
server only has 4GB total anyway. The server is obviously being used for
more than SQL if 3.2GB is being used. If you want more memory you need to
move to either Enterprise Edition of SQL Server or upgrade to 2005 Std. I
don't recall what Win2003 Std supports but I believe it is only 4GB so you
should consider upgrading that as well.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Leythos" <void@.nowhere.lan> wrote in message
news:MPG.21768b1c15ad1052989a7d@.adfree.Usenet.com...
>I have a IBM Dual Quad Core CPU system running SQL 2000 and all
> SP/Updates. I'm seeing a log of blocking because of a vendors app that I
> have no control over, causes the CPU Cores to run around 50% during the
> extended periods of blocking. System is using 3.2GB of the 4GB (Windows
> 2003 Std, SQL 2000 Std CPU Licenses) and SQL is set to not use CPU Core
> 0 (actually, it was set to use all cores, but it has not made any
> performance difference by not allowing access to Core 0 - this is a
> Domain Controller and normally shows 3% load across all cores).
> Should I limit the access to Cores to just 4-7 (second CPU) or just Core
> 0/4 and not the others, or should I just allow all cores?
> Also, with Win 2003 Std and SQL 2000 Std (CPU licenses), should I be
> using AWE or PAE modes? I'm currently setup for the default startup
> mode.
> Thanks - sorry for the ramble, been a long night.
>
> --
> Leythos
> - Igitur qui desiderat pacem, praeparet bellum.
> - Calling an illegal alien an "undocumented worker" is like calling a
> drug dealer an "unlicensed pharmacist"
> spam999free@.rrohio.com (remove 999 for proper email address)|||In article <OH#AjM1CIHA.1168@.TK2MSFTNGP02.phx.gbl>,
sqlmvpnooospam@.shadhawk.com says...
> You really seem to have underpowered the system when it comes to memory
> compared to CPU's. SQL 2000 Std edition can only use 2GB of memory and the
> server only has 4GB total anyway. The server is obviously being used for
> more than SQL if 3.2GB is being used. If you want more memory you need to
> move to either Enterprise Edition of SQL Server or upgrade to 2005 Std. I
> don't recall what Win2003 Std supports but I believe it is only 4GB so you
> should consider upgrading that as well.
Yea, I'm stuck - the database app vendor suggested a single CPU, and we
moved from a Dual Xeon 2Ghz machine to this Dual Quad Core system and
believed it would help.
I bought Sql 2005 and CPU licenses and then downgraded to SQL 2000
because the app didn't convert to 2005 (tried to do a restore and it
didn't go well - schema security screwed it).
So, I'm stuck with bad stored proc's and block each other, which is
where I'm sure the real issue lies, but I don't have rights/permission
to change the vendors code.
In the old days there was speculation that Hyper Threading cause the
system to run slower, but, in testing I was seeing about 30% increase
using HT enabled under Xeon CPU's on a quality server. So, I've got Dual
Quad Core CPU's now, and I don't have the option to play - so I was
wondering if I should be only using some cores or just the second CPU's
cores (instead of all 0-7 cores, use cores 4-7 so that I'm hitting just
the one CPU)...
What about AWE/PAE modes on Win 2003 Std with SQL 2000 Std?
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||You should not have an issue with allowing SQL Server to use all the
available cores. But make sure to set the MAXDOP to something less than max.
I would start with 4 or maybe even 2. When you upgraded to 2005 did you try
the db in 80 compatibility mode? That can make a difference in app
compatibility or not. PAE is ignored on Win2003 Std and SQL2000 Std will
only ever use 2GB regardless of the OS. SQL2005 will use all that the OS can
offer.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Leythos" <void@.nowhere.lan> wrote in message
news:MPG.2176da90b52cdb30989a7f@.adfree.Usenet.com...
> In article <OH#AjM1CIHA.1168@.TK2MSFTNGP02.phx.gbl>,
> sqlmvpnooospam@.shadhawk.com says...
>> You really seem to have underpowered the system when it comes to memory
>> compared to CPU's. SQL 2000 Std edition can only use 2GB of memory and
>> the
>> server only has 4GB total anyway. The server is obviously being used for
>> more than SQL if 3.2GB is being used. If you want more memory you need to
>> move to either Enterprise Edition of SQL Server or upgrade to 2005 Std. I
>> don't recall what Win2003 Std supports but I believe it is only 4GB so
>> you
>> should consider upgrading that as well.
> Yea, I'm stuck - the database app vendor suggested a single CPU, and we
> moved from a Dual Xeon 2Ghz machine to this Dual Quad Core system and
> believed it would help.
> I bought Sql 2005 and CPU licenses and then downgraded to SQL 2000
> because the app didn't convert to 2005 (tried to do a restore and it
> didn't go well - schema security screwed it).
> So, I'm stuck with bad stored proc's and block each other, which is
> where I'm sure the real issue lies, but I don't have rights/permission
> to change the vendors code.
> In the old days there was speculation that Hyper Threading cause the
> system to run slower, but, in testing I was seeing about 30% increase
> using HT enabled under Xeon CPU's on a quality server. So, I've got Dual
> Quad Core CPU's now, and I don't have the option to play - so I was
> wondering if I should be only using some cores or just the second CPU's
> cores (instead of all 0-7 cores, use cores 4-7 so that I'm hitting just
> the one CPU)...
> What about AWE/PAE modes on Win 2003 Std with SQL 2000 Std?
> --
> Leythos
> - Igitur qui desiderat pacem, praeparet bellum.
> - Calling an illegal alien an "undocumented worker" is like calling a
> drug dealer an "unlicensed pharmacist"
> spam999free@.rrohio.com (remove 999 for proper email address)|||In article <egVTKn2CIHA.1208@.TK2MSFTNGP05.phx.gbl>,
sqlmvpnooospam@.shadhawk.com says...
> You should not have an issue with allowing SQL Server to use all the
> available cores. But make sure to set the MAXDOP to something less than max.
> I would start with 4 or maybe even 2.
I hate to sound ignorant, but MAXDOP?
> When you upgraded to 2005 did you try
> the db in 80 compatibility mode? That can make a difference in app
> compatibility or not.
Yes, all DB were in 80 mode, but, the dang schema permissions cause
massive problems. The same Vendors app installed native on 2005 didn't
have a problem and I've done a few of them that work fine, but the ones
we've moved from 2000 to 2005 all have schema permission problems.
> PAE is ignored on Win2003 Std and SQL2000 Std will
> only ever use 2GB regardless of the OS. SQL2005 will use all that the OS can
> offer.
Yea, that's what I thought, SQL Store is using 1.75GB currently. I could
try limiting it to 1.4GB to free up memory for the server itself, since
it's a AD controller. I can't believe that it needs more than 1GB for
the store when all of the blocking is based on a single sproc that is
being called 600+ times by 4 users, it's an update of some type, but I
have not looked directly at their code, just the blocking showing all
the time.
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||As a note, I've set the Max Query Plan option to 3 from 5 and set max
memory from FULL to 1.4GB and CPU load has gone from 50% to 17% and
users are not reporting any loss of application performance - they are
not reporting any increase yet.
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||MAXDOP stands for MAX Degree of Parallelism. Please refer to BooksOnLine
for more details. I don't know what schema permission issues you had but I
am willing to bet they were minor and had to do with the fact the logins,
users and schemas has mismatches. If it works from scratch it should work
with an upgrade. Have a look at these to see if they help:
http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
Moving Users
http://support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
http://support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://support.microsoft.com/?id=240872 How to Resolve Permission Issues
When a Database Is Moved Between SQL Servers
> Yea, that's what I thought, SQL Store is using 1.75GB currently. I could
> try limiting it to 1.4GB to free up memory for the server itself, since
> it's a AD controller.
You have at least 4GB right? SQL will only use 2GB max so why would you
want to limit it even more?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Leythos" <void@.nowhere.lan> wrote in message
news:MPG.2176ed223bb957aa989a83@.adfree.Usenet.com...
> In article <egVTKn2CIHA.1208@.TK2MSFTNGP05.phx.gbl>,
> sqlmvpnooospam@.shadhawk.com says...
>> You should not have an issue with allowing SQL Server to use all the
>> available cores. But make sure to set the MAXDOP to something less than
>> max.
>> I would start with 4 or maybe even 2.
> I hate to sound ignorant, but MAXDOP?
>> When you upgraded to 2005 did you try
>> the db in 80 compatibility mode? That can make a difference in app
>> compatibility or not.
> Yes, all DB were in 80 mode, but, the dang schema permissions cause
> massive problems. The same Vendors app installed native on 2005 didn't
> have a problem and I've done a few of them that work fine, but the ones
> we've moved from 2000 to 2005 all have schema permission problems.
>> PAE is ignored on Win2003 Std and SQL2000 Std will
>> only ever use 2GB regardless of the OS. SQL2005 will use all that the OS
>> can
>> offer.
> Yea, that's what I thought, SQL Store is using 1.75GB currently. I could
> try limiting it to 1.4GB to free up memory for the server itself, since
> it's a AD controller. I can't believe that it needs more than 1GB for
> the store when all of the blocking is based on a single sproc that is
> being called 600+ times by 4 users, it's an update of some type, but I
> have not looked directly at their code, just the blocking showing all
> the time.
> --
> Leythos
> - Igitur qui desiderat pacem, praeparet bellum.
> - Calling an illegal alien an "undocumented worker" is like calling a
> drug dealer an "unlicensed pharmacist"
> spam999free@.rrohio.com (remove 999 for proper email address)|||In article <#M0PNZ3CIHA.4584@.TK2MSFTNGP03.phx.gbl>,
sqlmvpnooospam@.shadhawk.com says...
> MAXDOP stands for MAX Degree of Parallelism. Please refer to BooksOnLine
> for more details. I don't know what schema permission issues you had but I
> am willing to bet they were minor and had to do with the fact the logins,
> users and schemas has mismatches. If it works from scratch it should work
> with an upgrade. Have a look at these to see if they help:
> http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
> Moving Users
> http://support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
> Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
> http://support.microsoft.com/?id=168001 User Logon and/or Permission
> Errors After Restoring Dump
> http://support.microsoft.com/?id=240872 How to Resolve Permission Issues
> When a Database Is Moved Between SQL Servers
> > Yea, that's what I thought, SQL Store is using 1.75GB currently. I could
> > try limiting it to 1.4GB to free up memory for the server itself, since
> > it's a AD controller.
> You have at least 4GB right? SQL will only use 2GB max so why would you
> want to limit it even more?
Yes, IBM 3800 series with 4GB RAM and 4 additional used as fail-over
RAM. I've never seen the RAM in use exceed 3.3GB, in fact I've never
seen a Windows Std server use more than 3.3GB on anything.
I was worried about the 2GB limit for apps and wondered if allowing it
to run (auto) at 1.78GB left enough for the OS and server functions -
that's why I limited it to 1.4GB - this was a shotgun move (sorry, I
know it's not professional, but sometimes you have to just try
something).
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||> Yes, IBM 3800 series with 4GB RAM and 4 additional used as fail-over
> RAM. I've never seen the RAM in use exceed 3.3GB, in fact I've never
> seen a Windows Std server use more than 3.3GB on anything.
Some motherboards or chipsets will not use all 4GB as you would expect. They
simply can't address 4GB or they will hide some of the memory to be used for
specific memory calls such as onboard video etc. I suspect that is what you
are seeing here and in reality you are using all the available memory. You
are most likely paging to disk a lot as well. I would find out what else
other than SQL Server is using the memory and see if you can address that.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Leythos" <void@.nowhere.lan> wrote in message
news:MPG.2176fd277dd6df39989a88@.adfree.Usenet.com...
> In article <#M0PNZ3CIHA.4584@.TK2MSFTNGP03.phx.gbl>,
> sqlmvpnooospam@.shadhawk.com says...
>> MAXDOP stands for MAX Degree of Parallelism. Please refer to BooksOnLine
>> for more details. I don't know what schema permission issues you had but
>> I
>> am willing to bet they were minor and had to do with the fact the logins,
>> users and schemas has mismatches. If it works from scratch it should work
>> with an upgrade. Have a look at these to see if they help:
>> http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
>> Moving Users
>> http://support.microsoft.com/?id=246133 How To Transfer Logins and
>> Passwords Between SQL Servers
>> http://support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
>> Restore
>> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
>> users
>> http://support.microsoft.com/?id=168001 User Logon and/or Permission
>> Errors After Restoring Dump
>> http://support.microsoft.com/?id=240872 How to Resolve Permission
>> Issues
>> When a Database Is Moved Between SQL Servers
>> > Yea, that's what I thought, SQL Store is using 1.75GB currently. I
>> > could
>> > try limiting it to 1.4GB to free up memory for the server itself, since
>> > it's a AD controller.
>> You have at least 4GB right? SQL will only use 2GB max so why would you
>> want to limit it even more?
> Yes, IBM 3800 series with 4GB RAM and 4 additional used as fail-over
> RAM. I've never seen the RAM in use exceed 3.3GB, in fact I've never
> seen a Windows Std server use more than 3.3GB on anything.
> I was worried about the 2GB limit for apps and wondered if allowing it
> to run (auto) at 1.78GB left enough for the OS and server functions -
> that's why I limited it to 1.4GB - this was a shotgun move (sorry, I
> know it's not professional, but sometimes you have to just try
> something).
> --
> Leythos
> - Igitur qui desiderat pacem, praeparet bellum.
> - Calling an illegal alien an "undocumented worker" is like calling a
> drug dealer an "unlicensed pharmacist"
> spam999free@.rrohio.com (remove 999 for proper email address)|||In article <MPG.21768b1c15ad1052989a7d@.adfree.Usenet.com>,
void@.nowhere.lan says...
> I have a IBM Dual Quad Core CPU system running SQL 2000 and all
> SP/Updates. I'm seeing a log of blocking because of a vendors app that I
> have no control over, causes the CPU Cores to run around 50% during the
> extended periods of blocking. System is using 3.2GB of the 4GB (Windows
> 2003 Std, SQL 2000 Std CPU Licenses) and SQL is set to not use CPU Core
> 0 (actually, it was set to use all cores, but it has not made any
> performance difference by not allowing access to Core 0 - this is a
> Domain Controller and normally shows 3% load across all cores).
> Should I limit the access to Cores to just 4-7 (second CPU) or just Core
> 0/4 and not the others, or should I just allow all cores?
> Also, with Win 2003 Std and SQL 2000 Std (CPU licenses), should I be
> using AWE or PAE modes? I'm currently setup for the default startup
> mode.
> Thanks - sorry for the ramble, been a long night.
Well, it's been a full day and I've found that changing the setting from
5 to 3 made all the difference in the world. So, a crappy coded Proc
(that I have no control over), caused massive blocking and now, it's
fine again.
I should point out that a typical back of this server took 6+ hours
normally, now it takes under 3 hours. Users have found a little
improvement in performance (DB App), but the server is no longer showing
a load and all functions are responsive.
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||Something doesn't connect. Typically, if you see massive blocking, your CPU
consunption should go down because these processes are being blocked and are
not doing anything. And I'd expect to see CPU usage go up when you have
resolved blocking.
Linchi
"Leythos" wrote:
> As a note, I've set the Max Query Plan option to 3 from 5 and set max
> memory from FULL to 1.4GB and CPU load has gone from 50% to 17% and
> users are not reporting any loss of application performance - they are
> not reporting any increase yet.
>
> --
> Leythos
> - Igitur qui desiderat pacem, praeparet bellum.
> - Calling an illegal alien an "undocumented worker" is like calling a
> drug dealer an "unlicensed pharmacist"
> spam999free@.rrohio.com (remove 999 for proper email address)
>|||In article <E7A06FE9-A2D7-433F-9DC4-9AF34EA25F2B@.microsoft.com>,
LinchiShea@.discussions.microsoft.com says...
> Something doesn't connect. Typically, if you see massive blocking, your CPU
> consunption should go down because these processes are being blocked and are
> not doing anything. And I'd expect to see CPU usage go up when you have
> resolved blocking.
The same 600+ updates now don't consume CPU time and don't cause
blocking that I can see (at least not during production periods where it
was causing blocking).
So, it's just looking good now.
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||What setting are you referring to that you changed from 5 to 3? As I have
said all along I think you have issues with MAXDOP and need to reduce the
parallelism.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Leythos" <void@.nowhere.lan> wrote in message
news:MPG.2176f66415ad1f61989a85@.adfree.Usenet.com...
> As a note, I've set the Max Query Plan option to 3 from 5 and set max
> memory from FULL to 1.4GB and CPU load has gone from 50% to 17% and
> users are not reporting any loss of application performance - they are
> not reporting any increase yet.
>
> --
> Leythos
> - Igitur qui desiderat pacem, praeparet bellum.
> - Calling an illegal alien an "undocumented worker" is like calling a
> drug dealer an "unlicensed pharmacist"
> spam999free@.rrohio.com (remove 999 for proper email address)|||In article <ObHjn3NDIHA.4360@.TK2MSFTNGP06.phx.gbl>,
sqlmvpnooospam@.shadhawk.com says...
> What setting are you referring to that you changed from 5 to 3? As I have
> said all along I think you have issues with MAXDOP and need to reduce the
> parallelism.
Yea, this is SQL 2000, and the max query plan option was set for 5,
changing it to 3 cleaned up the lack of proper coding from the vendors
app.
Thanks.
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||I don't have a 2000 instance to look at right now but I don't remember a
setting called that. Are you referring to the parallel query plan cost
option? If so then again I suggest you change the MAXDOP instead.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Leythos" <void@.nowhere.lan> wrote in message
news:MPG.2179ea0474b0e377989ac3@.adfree.Usenet.com...
> In article <ObHjn3NDIHA.4360@.TK2MSFTNGP06.phx.gbl>,
> sqlmvpnooospam@.shadhawk.com says...
>> What setting are you referring to that you changed from 5 to 3? As I
>> have
>> said all along I think you have issues with MAXDOP and need to reduce the
>> parallelism.
> Yea, this is SQL 2000, and the max query plan option was set for 5,
> changing it to 3 cleaned up the lack of proper coding from the vendors
> app.
> Thanks.
> --
> Leythos
> - Igitur qui desiderat pacem, praeparet bellum.
> - Calling an illegal alien an "undocumented worker" is like calling a
> drug dealer an "unlicensed pharmacist"
> spam999free@.rrohio.com (remove 999 for proper email address)|||In article <#nSn#ZbDIHA.5980@.TK2MSFTNGP04.phx.gbl>,
sqlmvpnooospam@.shadhawk.com says...
> I don't have a 2000 instance to look at right now but I don't remember a
> setting called that. Are you referring to the parallel query plan cost
> option? If so then again I suggest you change the MAXDOP instead.
I've looked all around and only see MAXDOP in SQL 2005 and as a query
hint when the option is specified.
I do not have permission to edit any of the vendors code, so I've used
the Memory/CPU settings tabs to adjust what can be run in parallel.
If you have a setting, in SQL 2000, that applies to ALL proc's without
having to edit any of the code, just changing SQL settings, then I would
love to see it - thanks.
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||MAXDOP is not a SQL2005 specific setting. It is there in 2000 as well. Again
I don't have an instance of 2000 so I am going from memory here but if you
right click on the server in EM and choose properties there should be a
Processor tab. On that tab there should be a dropdown box (I believe) to
choose the maximum number of processors that can be used in parallel. It
will be set to 0 by default. You can also change or see this in
sp_configure. It is the "max degree of parallelism" option. You can see more
details in BOL under max degree of parallelism. If you set it here it
affects everything on the server. You can also specify a hint at a statement
level but that is not what you want.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Leythos" <void@.nowhere.lan> wrote in message
news:MPG.217ac0fd1c33cfbc989ad6@.adfree.Usenet.com...
> In article <#nSn#ZbDIHA.5980@.TK2MSFTNGP04.phx.gbl>,
> sqlmvpnooospam@.shadhawk.com says...
>> I don't have a 2000 instance to look at right now but I don't remember a
>> setting called that. Are you referring to the parallel query plan cost
>> option? If so then again I suggest you change the MAXDOP instead.
> I've looked all around and only see MAXDOP in SQL 2005 and as a query
> hint when the option is specified.
> I do not have permission to edit any of the vendors code, so I've used
> the Memory/CPU settings tabs to adjust what can be run in parallel.
> If you have a setting, in SQL 2000, that applies to ALL proc's without
> having to edit any of the code, just changing SQL settings, then I would
> love to see it - thanks.
> --
> Leythos
> - Igitur qui desiderat pacem, praeparet bellum.
> - Calling an illegal alien an "undocumented worker" is like calling a
> drug dealer an "unlicensed pharmacist"
> spam999free@.rrohio.com (remove 999 for proper email address)|||In article <OxdtLgcDIHA.4752@.TK2MSFTNGP04.phx.gbl>,
sqlmvpnooospam@.shadhawk.com says...
> MAXDOP is not a SQL2005 specific setting. It is there in 2000 as well. Again
> I don't have an instance of 2000 so I am going from memory here but if you
> right click on the server in EM and choose properties there should be a
> Processor tab. On that tab there should be a dropdown box (I believe) to
> choose the maximum number of processors that can be used in parallel. It
> will be set to 0 by default. You can also change or see this in
> sp_configure. It is the "max degree of parallelism" option. You can see more
> details in BOL under max degree of parallelism. If you set it here it
> affects everything on the server. You can also specify a hint at a statement
> level but that is not what you want.
Thanks for your time - somehow I seem to have missed this when looking
in those areas. I will check again.
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||Just a small point, the hint is MAXDOP but the configuration setting
is called "max degree of parallelism". If type that into the SQL
Server 2000 Books On Line index you will find more information.
Roy Harvey
Beacon Falls, CT
On Mon, 15 Oct 2007 07:21:49 -0400, Leythos <void@.nowhere.lan> wrote:
>In article <OxdtLgcDIHA.4752@.TK2MSFTNGP04.phx.gbl>,
>sqlmvpnooospam@.shadhawk.com says...
>> MAXDOP is not a SQL2005 specific setting. It is there in 2000 as well. Again
>> I don't have an instance of 2000 so I am going from memory here but if you
>> right click on the server in EM and choose properties there should be a
>> Processor tab. On that tab there should be a dropdown box (I believe) to
>> choose the maximum number of processors that can be used in parallel. It
>> will be set to 0 by default. You can also change or see this in
>> sp_configure. It is the "max degree of parallelism" option. You can see more
>> details in BOL under max degree of parallelism. If you set it here it
>> affects everything on the server. You can also specify a hint at a statement
>> level but that is not what you want.
>Thanks for your time - somehow I seem to have missed this when looking
>in those areas. I will check again.

Tuesday, March 27, 2012

Duplicate records - difference method?

Monday, March 19, 2012

Dumb question about SSIS

Dumb question about SSIS

Dumb question about SSIS

Sunday, March 11, 2012

dumb question

Dual Transaction Log

Dual Transaction Log

Dual Quad Core - CPU settings?

DTS to .net

Blog Archive

About Me