Calling All MSSQL 2005 Experts

Jul 27, 2006

Dear Group.

Wondered if any of you has any suggestion for the following?
Trying to install SQL Server 2005 Eval on a 'clean' machine. Well, my
mistake was probably that I had installed Visual Studio 2005
Professional Eval before which installed an MSSQL Express instance.
Since then I didn't get 'Enterprise Manager' (excuse my ignorance, I
know it's called differently in2005 but it's late and I'm tired) for
MSSQL 2005 installed.Don't remember the error exactly but after some
trying I received some error during the MSSQL Management Studio (is
that right?) installation that it needs to be upgraded in some ini file
which I looked for to no avail. ANYWAY, after some desperate registry
deletes and following MS Kbase articles I arrived at: 'The installer
has encountered an unexpected error installing this package. This may
indicate a problem with the package. The error code is 2718.' during
the installation of SQL Server Backward-Compatibility files.
Which is of course complete rubbish. We all now that Microsoft has done
an awful job in getting SQL Express and SQL Server installed
side-by-side but it never has been as bad as this on any machine
before. Needless to say the package is fine as I've used it many times
before. Any suggestions?

Gratefully yours,

Martin

'Just wanna get on with work. Tired of starring at error messages.'

View 1 Replies

Calling All SQL And Informix Experts!

Jan 15, 2004

Our Informix server is struggling with all the reports we run and so we are thinking of making a dedicated server for reporting.

SQL is an obvious choice because we have it already for our retail system.

However, the challenge is how to download the data we need each night. DTS works a treat but it is the volume of data that is the problem.

We are a retail operation and we need to download the transactions from our Informix server into SQL. This data gets into Informix from the EPOS system in our stores.

What we don't want to do is download everynight the entire back history of transactions. We could do this by using the date of the transactions but we discovered it wont work.

The problem is that if a store doesn't post their transactions e.g. because of a system failure then these will get missed.

What we need to do is record which transactions are downloaded into SQL and then compare this against what is on the Informix server and then download the difference each night.

We thought of adding a flag onto the Informix server but we are not able to make any modifications to it.

I think we could log the downloaded transactions in a SQL table and then use this as a record of what has been downloaded. We could then run a query that compares this to what is on the Informix server.

With the right indexes I think this could work really well. Any thoughts? Incidently the two servers are separated by a 512Kbps wan link......

View 1 Replies View Related

Deploying MSSQL 2005 Express DB To MSSQL 2005 WKGP Errors

Sep 29, 2006

DB is developed on local computer with MSSQL 2005 Express. My host is on MSSQL 2005 workgroup. Are they compatible, because I am getting errors? Is my approach wrong?

I have tried several approaches.

A) I created a backup of database on my local, then placed a copy on the server. Then I tried to restore through Server Management Studio. I get this error.

TITLE: Microsoft SQL Server Management Studio

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

ADDITIONAL INFORMATION:

The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database.

RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3169)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2039&EvtSrc=MSSQLServer&EvtID=3169&LinkId=20476

------------------------------

BUTTONS:

OK

------------------------------

B: I also have tried copying the database. I put it in the same path as the other databases that can be read with server management studio on the server. Then, tried to get to it through server managements studio and it did not appear. So I tried to attach it. Then I received this error:

TITLE: Microsoft SQL Server Management Studio

------------------------------

Attach database failed for Server 'MROACH1'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

------------------------------

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Could not find row in sysindexes for database ID 10, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.

Could not open new database 'LodgingDB'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 602)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=602&LinkId=20476

------------------------------

BUTTONS:

OK

------------------------------

C: I have also tried opening the Database, and back up file through Server Management Studio. without success.

D: I also tried Windows and Software update at microsoft update, but no updates were recommended for Version on Server.

I'm surprised this is so hard. My original data base was created in same family of software. 2005 MS SQL Express. I could use some direct help from someone experienced with this. Am I doing it wrong or are the DB versions incompatible.

Mark Roach

View 5 Replies View Related

Server Configuration For MSSQL 2000 And MSSQL 2005

Sep 6, 2006

Does enabling/disabling Data Execution Prevention have a performanceimpact on SQL 2000 or SQL 2005?For SQL best performance - how should I configure for:Processor Scheduling:Programs or Background servicesMemory Usage:Programs or System Cache

View 9 Replies View Related

Creating Index In MSSQL 2000 From MSSQL 2005

Mar 24, 2008

Hi,

I am a bit new to the MSSQL server. In our application, we use so many SQL queries. To imporve the performance, we used the Database enigine Tuning tool to create the indexes. The older version of the application supports MSSQL 2000 also. To re-create these new indexes, I have an issue in running these "CREATE INDEX" commands as the statements generated for index creation are done in MSSQL 2005. The statements include "INCLUDES" keyword which is supported in MSSQL 2005 but not in MSSQL 2000.

Ex:-

CREATE INDEX IND_001_PPM_PA ON PPM_PROCESS_ACTIVITY

(ACTIVITY_NAME ASC, PROCESS_NAME ASC, START_TIME ASC, ISMONITORED ASC)

INCLUDE

(INSTANCE_ID, ACTIVITY_TYPE, STATUS, END_TIME, ORGANIZATION);

Any help in creating such indexes in 2000 version is welcome.

Thanks,
Suresh.

View 2 Replies View Related

Generating Script For MSSQL 2000 From MSSQl 2005?

May 3, 2008

Hello
We are using SQL 2005 and now we are planning to use SQL 2000. what are the ways to do the process.

We taken the script spcificall for 2000 and run it in SQL 200. But we are getting the error in SCRIPT?

Could you please give me the step to do?

Thanks,
Sankar R

View 6 Replies View Related

Migrate MSSQL 2005 Express -&&> MSSQL 2005 Srv

Apr 25, 2006

Hi!

What is the best way to migrate MSSQL 2005 Express -> MSSQL 2005 Srv?

View 4 Replies View Related

MSSQL Express 2005 Vs. MSSQL 2000

Jun 15, 2006

Ben writes "I have a sql script that doesn't function very well when it's executed on a SQL 2000 server.

The scrpt looks like this:

---------------------------------------------------------------------------------------------------
USE [master]
GO
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME')
EXEC sp_addlogin N'SSDBUSERNAME', N'SSDBPASSWORD'
GO
GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME]
GO
GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME]
GO
GRANT CONNECT SQL TO [SSDBUSERNAME]
GO
GRANT CONTROL SERVER TO [SSDBUSERNAME]
GO
GRANT CREATE ANY DATABASE TO [SSDBUSERNAME]
GO
USE [master]
GO
If EXISTS (Select * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME')
ALTER LOGIN [SSDBUSERNAME] WITH PASSWORD=N'SSDBPASSWORD'
GO
GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME]
GO
GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME]
GO
GRANT CONNECT SQL TO [SSDBUSERNAME]
GO
GRANT CONTROL SERVER TO [SSDBUSERNAME]
GO
GRANT CREATE ANY DATABASE TO [SSDBUSERNAME]
GO
USE [master]
GO
IF EXISTS (select * from dbo.sysdatabases where name = 'ISIZ')
DROP DATABASE [ISIZ]
GO
USE [SurveyData]
GO
exec sp_adduser 'SSDBUSERNAME'
GRANT INSERT, UPDATE, SELECT, DELETE
TO SSDBUSERNAME
GO
USE [SurveyManagement]
GO
exec sp_adduser 'SSDBUSERNAME'
GRANT INSERT, UPDATE, SELECT, DELETE
TO SSDBUSERNAME
---------------------------------------------------------------

I need to be converted to a script that can be executed on both MSSQL 2000 and MSSQL 2005.

I was wondering if somebody there could help me with this problem?!

Thanks,
Ben"

View 1 Replies View Related

Migration MSSQL 2000 32-bit To MSSQL 2005 64-bit

Nov 17, 2007

I've been tasked to move our production databases on MSSQL 2000 to 2005. I've supported MSSQL since version 6.5 and performed migrations to successor versions.

Current Environment is MSSQL 2000 32-bit with current Service Packs.

I've performed mock migrations on Test servers upgrading all Production instances simultaneously from MSSQL 2000 to 2005 32-bit. The Test environment is identical to Production minus server name, IP etc. Also I have a separate server with MSSQL 2005 installed where I use the DETACH / ATTACH and BACKUP / RESTORE method for migration / acceptance testing. There are approximately 30 databases totaling 70 GB. This has gone as expected and fairly successful. Vendors have been coordinated with to update code and staff for acceptance testing.

I'd prefer going directly to MSSQL 2005 64-bit instead if possible due to memory benefits etc. This is where I'd like some feedback prior to borrowing a 64-bit server for testing.

Upgrade options:

1. Is it better to migrate from MSSQL 2000 32-bit to 2005 64-bit via:
a. DETACH / ATTACH
b. BACKUP / RESTORE
c. Is one method more advantageous relating to the end result?
2. Regarding XP clients, have issues been experienced with the default SQL Server driver or is an alternate recommended for XP clients to connect to a MSSQL 64-bit server databases?
3. If you have performed this migration and have relevant experience please pass them along.

View 3 Replies View Related

Conversion Of MSSql 2000 Database To MSSQL 2005 Database

Jan 18, 2008

How to convert a database in MSSQL 2000 to MSSQL 2005 database.Is there any tool or documentation available for this?

View 3 Replies View Related

MSSQL 2005 Vs. Visual Studio 2005 Service Pack 1

Mar 14, 2007

Hi,
for some reason, when I've installed visual studio 2005, I didn't install also SQL server.
Than I've updated VS with Service Pack 1 (thank god, I was trying unsucesfully for about 2 weeks :/ ).

Than I want to install MSSQL 2005 and an error came up:

Message displayed to user
SQL Server 2005 Setup has detected incompatible components from beta versions of Visual Studio, .NET Framework, or SQL Server 2005. Use Add or Remove Programs to remove these components, and then run SQL Server 2005 Setup again. For detailed instructions on uninstalling SQL Server 2005, see the SQL Server 2005 Readme.
SQL Server Setup has detected that the following required component is not installed: Microsoft Windows Installer 3.1. To proceed, download and install the Windows Installer 3.1 Redistributable from http://go.microsoft.com/fwlink/?LinkId=50380 , and then run SQL Server Setup again.

1. The http link leads to main page of MS Download Centre.
2. I have Installer 3.1. (version of file c:windowssystem32msiexec.exe said so)
3. I really do not have any beta version of any mentioned SW. I never had.

So I think the issue could be, that installer is checking for not updated version of Visual Studio.
(I was searching through net/msdn and seems no one had this problem (maybe wrong keywords, though) ).

I'm running setup.exe from CD1.

Uninstalling of VS Service Pack 1 is not an option. I really don't want to spent another week by installing it.
Please tell that there is some another way :) .
Thanks a lot.

Here are some logs:

SQLSetup0001_COMP_Datastore.xml

<Datastore>
<Scope Type="SetupBootstrapOptionsScope" Id="2336">
<Property Id="QuietSwitchPresent">0</Property>
<Property Id="LogSwitchPresent">0</Property>
<Property Id="ErrorReporting">0</Property>
<Property Id="DeleteManifestFileMode">1</Property>
<Property Id="MultiInstanceEnabled">0</Property>
<Property Id="ComponentUpdateOnly">0</Property>
<Property Id="ReducedUI">0</Property>
<Property Id="PatchedSetup">0</Property>
<Property Id="LocalSetup">0</Property>
<Property Id="InitiateReboot">0</Property>
<Property Id="HostSetup">1</Property>
<Property Id="RemoteMode">0</Property>
<Property Id="Remote">0</Property>
<Property Id="LoosenedSccChecks">{ }</Property>
<Property Id="DisabledSccChecks">{ }</Property>
<Property Id="LocalRebootPending">1</Property>
<Property Id="CmdLine">********</Property>
<Property Id="InstallMediaPath">Y:</Property>
</Scope>
<Scope Type="SetupStateScope" Id="">
<Property Id="machineName">COMP</Property>
<Property Id="logDirectory">C:Program FilesMicrosoft SQL Server90Setup BootstrapLOG</Property>
<Property Id="logSummaryFilename">C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGSummary.txt</Property>
<Property Id="logSequenceNumber">1</Property>
<Property Id="primaryLogFiles">{ ["C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_COMP_Core.log"], ["C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGSummary.txt"] }</Property>
<Property Id="watsonFailedAction">PerformSCCAction2</Property>
<Property Id="watsonFailedActionErrorCode">87</Property>
<Property Id="watsonFailedFunction">sqls::PerformSCCAction::perform</Property>
<Property Id="watsonFailedFunctionErrorCode">87</Property>
<Property Id="watsonSourceFileAndLineNo">setupsqlsetupactions.cpp@1390</Property>
<Property Id="watsonModuleAndVersion">setup.exe@2005.90.1399.0</Property>
<Property Id="watsonMsi">None</Property>
<Property Id="watsonMsiAndVersion">None</Property>
<Property Id="watsonSourceFile">setupsqlsetupactions.cpp</Property>
</Scope>
</Datastore>

SQLSetup0001_COMP_Core.log

Microsoft SQL Server 2005 Setup beginning at Wed Mar 14 18:23:08 2007
Process ID : 2336
Y:setup.exe Version: 2005.90.1399.0
Running: LoadResourcesAction at: 2007/2/14 18:23:8
Complete: LoadResourcesAction at: 2007/2/14 18:23:8, returned true
Running: ParseBootstrapOptionsAction at: 2007/2/14 18:23:8
Loaded DLL:Y:xmlrw.dll Version:2.0.3604.0
Complete: ParseBootstrapOptionsAction at: 2007/2/14 18:23:8, returned true
Running: ValidateWinNTAction at: 2007/2/14 18:23:8
Complete: ValidateWinNTAction at: 2007/2/14 18:23:8, returned true
Running: ValidateMinOSAction at: 2007/2/14 18:23:8
Complete: ValidateMinOSAction at: 2007/2/14 18:23:8, returned true
Running: PerformSCCAction at: 2007/2/14 18:23:8
Complete: PerformSCCAction at: 2007/2/14 18:23:8, returned true
Running: ActivateLoggingAction at: 2007/2/14 18:23:8
Complete: ActivateLoggingAction at: 2007/2/14 18:23:8, returned true
Delay load of action "DetectPatchedBootstrapAction" returned nothing. No action will occur as a result.
Action "LaunchPatchedBootstrapAction" will be skipped due to the following restrictions:
Condition "EventCondition: __STP_LaunchPatchedBootstrap__2336" returned false.
Running: PerformSCCAction2 at: 2007/2/14 18:23:8
Error: Action "PerformSCCAction2" threw an exception during execution.
Return Code: 87
Message displayed to user
SQL Server 2005 Setup has detected incompatible components from beta versions of Visual Studio, .NET Framework, or SQL Server 2005. Use Add or Remove Programs to remove these components, and then run SQL Server 2005 Setup again. For detailed instructions on uninstalling SQL Server 2005, see the SQL Server 2005 Readme.
SQL Server Setup has detected that the following required component is not installed: Microsoft Windows Installer 3.1. To proceed, download and install the Windows Installer 3.1 Redistributable from http://go.microsoft.com/fwlink/?LinkId=50380 , and then run SQL Server Setup again.

Class not registered.
Running: UploadDrWatsonLogAction at: 2007/2/14 18:23:9
Message pump returning: 87

View 2 Replies View Related

Converting SQL Express 2005 To MSSQL 2005 Database

Jul 14, 2007

Hello, im shure this must have been up before and i apologize for that. But i wonder if there is a way to convert the SQL server express databases to MSSQL 2005 databses?

View 3 Replies View Related

DTS Experts - Help

Feb 12, 2001

Hi,

I've taken up maintaining new server where there were
50+ DTS packages scheduled. How do I document those tasks?
I know the methods of copy to file as dts and cOM objects.
Is there a way to copy it in readable format like VB or Sql?

Thanks

Sam

View 1 Replies View Related

Need Help From Experts

Apr 26, 2004

[FONT=Garamond][COLOR=RoyalBlue][SIZE=2]

Hi guys,

Scenario:
We are currently running an Accounting software in Visual Foxpro 8. All are freetables. We are migrating the whole VFP database to sql server 2000 to provide better service to our users. The leagacy VFP system currently running on the production server. Our intension is to migrate the whole system concurrently without intervening/down the system. We found a feasible way to import data from VFP8 with the help or OLEDB VFP.Now I am the one who needs to take care of the Data Synchronization between VFP and Sql Server. I am quite new to DBA routines. I don't have fair idea to synchronize between VFP and SQL Server 2000. If anybody can suggest which replication methodology can I follow to replicate between VFP and SQL SERVER 2000 or even do some DTS routine to achive this. I hope so there should be way to do it in SQL SERVER 2000 :confused: :confused: .

Please guide me to do this....

View 2 Replies View Related

Where Are The SQL Experts At? Help Please.

Apr 16, 2008

I have the following query. I have two problems with this. For each Region I should only have a CustomerNumber listed once with their SOExtChargeAmount summed. I have some showing up more than once.

The second problem is that after my UNION ALL I need to sum 'AllOthers' as one row of data. I can't figure this out. Any help would be absolutely awesome. Thanks.

SELECT
Region, Location, WarehouseCode, CustomerNumber, CustomerName, MonthLessEleven, MonthLessTen, MonthLessNine, MonthLessEight, MonthLessSeven,
MonthLessSix, MonthLessFive, MonthLessFour, MonthLessThree, MonthLessTwo, MonthLessOne, CurrentMonth, CurrentYearTotal, LastYearYTD,
LastYearTotal, CustRank

FROM
(
SELECT

casewhen substring(gla.Account,5,3) = '936' then 'North Region'
when substring(gla.Account,5,3) = '908' then 'East Region' ELSE 'Unknown'END AS Region,

case
when substring(gla.Account,5,3)='900' then 'ALE'
when substring(gla.Account,5,3)='902' then 'ATO'
when substring(gla.Account,5,3)='904' then 'BOW'
when substring(gla.Account,5,3)='906' then 'BRY'
when substring(gla.Account,5,3)='908' then 'BPT'
when substring(gla.Account,5,3)='910' then 'BYD'
when substring(gla.Account,5,3)='912' then 'BUF'
when substring(gla.Account,5,3)='914' then 'CLE'
when substring(gla.Account,5,3)='916' then 'GRN'
when substring(gla.Account,5,3)='920' then 'DXN'
when substring(gla.Account,5,3)='924' then 'CTH'
when substring(gla.Account,5,3)='926' then 'ELC'
when substring(gla.Account,5,3)='928' then 'FTL'
when substring(gla.Account,5,3)='930' then 'FTW'
when substring(gla.Account,5,3)='932' then 'I35'
when substring(gla.Account,5,3) IN ('936','000') then 'GAI'
when substring(gla.Account,5,3)='939' then 'STW'
when substring(gla.Account,5,3)='940' then 'GRE'
when substring(gla.Account,5,3)='942' then 'HEN'
when substring(gla.Account,5,3)='944' then 'FTS'
when substring(gla.Account,5,3)='948' then 'JAC'
when substring(gla.Account,5,3)='952' then 'JEN'
when substring(gla.Account,5,3)='956' then 'KIL'
when substring(gla.Account,5,3)='957' then 'MCA'
when substring(gla.Account,5,3)='958' then 'MIN'
when substring(gla.Account,5,3)='960' then 'NOC'
when substring(gla.Account,5,3)='962' then 'ODE'
when substring(gla.Account,5,3)='964' then 'BTP'
when substring(gla.Account,5,3)='966' then 'RA'
when substring(gla.Account,5,3)='968' then 'RIF'
when substring(gla.Account,5,3)='970' then 'SWD'
when substring(gla.Account,5,3)='971' then '3PS'
when substring(gla.Account,5,3)='972' then 'ROC'
when substring(gla.Account,5,3)='976' then 'SJO'
when substring(gla.Account,5,3)='978' then 'SMB'
when substring(gla.Account,5,3)='980' then 'STO'
when substring(gla.Account,5,3)='982' then 'TOL'
when substring(gla.Account,5,3)='984' then 'VEL'
when substring(gla.Account,5,3)='985' then 'CFP'
when substring(gla.Account,5,3)='986' then 'CLM'
when substring(gla.Account,5,3)='988' then 'WHI'
when substring(gla.Account,5,3)='992' then 'WRA'
when substring(gla.Account,5,3)='995' then 'ADM' ELSE 'Unknown'END AS Location,

ihh.WarehouseCode,ihh.CustomerNumber, cm.CustomerName,

SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 11 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessEleven,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 10 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessTen,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 9 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessNine,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 8 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessEight,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 7 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessSeven,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 6 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessSix,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 5 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessFive,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 4 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessFour,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 3 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessThree,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 2 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessTwo,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 1 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessOne,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 0 THEN ihd.SOExtChargeAmount ELSE 0 END) AS CurrentMonth,
SUM(CASE WHEN DATEDIFF([YEAR], ihh.SOTransDate, getdate()) = 0 THEN ihd.SOExtChargeAmount ELSE 0 END) AS CurrentYearTotal,
SUM(CASE WHEN DATEADD([year], - 1, getdate()) > ihh.SOTransDate AND DATEDIFF([YEAR], ihh.SOTransDate, getdate()) = 1 THEN ihd.SOExtChargeAmount ELSE 0 END) AS LastYearYTD,
SUM(CASE WHEN DATEDIFF([YEAR], ihh.SOTransDate, getdate()) = 1 THEN ihd.SOExtChargeAmount ELSE 0 END) LastYearTotal,

ROW_NUMBER() OVER(PARTITION BY
casewhen substring(gla.Account,5,3) = '936' then 'North Region'
when substring(gla.Account,5,3) = '908' then 'East Region' ELSE ' 'END
ORDER BY
casewhen substring(gla.Account,5,3) = '936' then 'North Region'
when substring(gla.Account,5,3) = '908' then 'East Region' ELSE ' 'END,
SUM(CASE WHEN DATEDIFF([YEAR], ihh.SOTransDate, getdate()) = 0 THEN ihd.SOExtChargeAmount ELSE 0 END) desc) AS CustRank

FROMMAS_BIF_AR1_CustomerMaster AS cm INNER JOIN
MAS_BIF_ARN_InvHistoryHeader AS ihh ON cm.CustomerNumber = ihh.CustomerNumber INNER JOIN
MAS_BIF_ARO_InvHistoryDetail AS ihd ON ihh.InvoiceNumber = ihd.InvoiceNumber INNER JOIN
MAS_BIF_GL_Account AS gla ON ihd.SOGLSalesAcct = gla.AccountKey

GROUP BYihh.CustomerNumber, cm.CustomerName, gla.Account, ihh.WarehouseCode
) X

WHERECustRank < 20

union all

SELECT
Region, Location, WarehouseCode, 'AllOthers', CustomerName, MonthLessEleven, MonthLessTen, MonthLessNine, MonthLessEight, MonthLessSeven,
MonthLessSix, MonthLessFive, MonthLessFour, MonthLessThree, MonthLessTwo, MonthLessOne, CurrentMonth, CurrentYearTotal, LastYearYTD,
LastYearTotal, 20

FROM
(
SELECT

casewhen substring(gla.Account,5,3) = '936' then 'North Region'
when substring(gla.Account,5,3) = '908' then 'East Region' ELSE 'Unknown'END AS Region,

case
when substring(gla.Account,5,3)='900' then 'ALE'
when substring(gla.Account,5,3)='902' then 'ATO'
when substring(gla.Account,5,3)='904' then 'BOW'
when substring(gla.Account,5,3)='906' then 'BRY'
when substring(gla.Account,5,3)='908' then 'BPT'
when substring(gla.Account,5,3)='910' then 'BYD'
when substring(gla.Account,5,3)='912' then 'BUF'
when substring(gla.Account,5,3)='914' then 'CLE'
when substring(gla.Account,5,3)='916' then 'GRN'
when substring(gla.Account,5,3)='920' then 'DXN'
when substring(gla.Account,5,3)='924' then 'CTH'
when substring(gla.Account,5,3)='926' then 'ELC'
when substring(gla.Account,5,3)='928' then 'FTL'
when substring(gla.Account,5,3)='930' then 'FTW'
when substring(gla.Account,5,3)='932' then 'I35'
when substring(gla.Account,5,3) IN ('936','000') then 'GAI'
when substring(gla.Account,5,3)='939' then 'STW'
when substring(gla.Account,5,3)='940' then 'GRE'
when substring(gla.Account,5,3)='942' then 'HEN'
when substring(gla.Account,5,3)='944' then 'FTS'
when substring(gla.Account,5,3)='948' then 'JAC'
when substring(gla.Account,5,3)='952' then 'JEN'
when substring(gla.Account,5,3)='956' then 'KIL'
when substring(gla.Account,5,3)='957' then 'MCA'
when substring(gla.Account,5,3)='958' then 'MIN'
when substring(gla.Account,5,3)='960' then 'NOC'
when substring(gla.Account,5,3)='962' then 'ODE'
when substring(gla.Account,5,3)='964' then 'BTP'
when substring(gla.Account,5,3)='966' then 'RA'
when substring(gla.Account,5,3)='968' then 'RIF'
when substring(gla.Account,5,3)='970' then 'SWD'
when substring(gla.Account,5,3)='971' then '3PS'
when substring(gla.Account,5,3)='972' then 'ROC'
when substring(gla.Account,5,3)='976' then 'SJO'
when substring(gla.Account,5,3)='978' then 'SMB'
when substring(gla.Account,5,3)='980' then 'STO'
when substring(gla.Account,5,3)='982' then 'TOL'
when substring(gla.Account,5,3)='984' then 'VEL'
when substring(gla.Account,5,3)='985' then 'CFP'
when substring(gla.Account,5,3)='986' then 'CLM'
when substring(gla.Account,5,3)='988' then 'WHI'
when substring(gla.Account,5,3)='992' then 'WRA'
when substring(gla.Account,5,3)='995' then 'ADM' ELSE 'Unknown'END AS Location,

ihh.WarehouseCode,ihh.CustomerNumber, cm.CustomerName,

SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 11 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessEleven,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 10 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessTen,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 9 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessNine,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 8 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessEight,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 7 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessSeven,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 6 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessSix,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 5 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessFive,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 4 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessFour,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 3 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessThree,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 2 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessTwo,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 1 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessOne,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 0 THEN ihd.SOExtChargeAmount ELSE 0 END) AS CurrentMonth,
SUM(CASE WHEN DATEDIFF([YEAR], ihh.SOTransDate, getdate()) = 0 THEN ihd.SOExtChargeAmount ELSE 0 END) AS CurrentYearTotal,
SUM(CASE WHEN DATEADD([year], - 1, getdate()) > ihh.SOTransDate AND DATEDIFF([YEAR], ihh.SOTransDate, getdate()) = 1 THEN ihd.SOExtChargeAmount ELSE 0 END) AS LastYearYTD,
SUM(CASE WHEN DATEDIFF([YEAR], ihh.SOTransDate, getdate()) = 1 THEN ihd.SOExtChargeAmount ELSE 0 END) LastYearTotal,

ROW_NUMBER() OVER(PARTITION BY
casewhen substring(gla.Account,5,3) = '936' then 'North Region'
when substring(gla.Account,5,3) = '908' then 'East Region' ELSE ' 'END
ORDER BY
casewhen substring(gla.Account,5,3) = '936' then 'North Region'
when substring(gla.Account,5,3) = '908' then 'East Region' ELSE ' 'END,
SUM(CASE WHEN DATEDIFF([YEAR], ihh.SOTransDate, getdate()) = 0 THEN ihd.SOExtChargeAmount ELSE 0 END) desc) AS CustRank

FROMMAS_BIF_AR1_CustomerMaster AS cm INNER JOIN
MAS_BIF_ARN_InvHistoryHeader AS ihh ON cm.CustomerNumber = ihh.CustomerNumber INNER JOIN
MAS_BIF_ARO_InvHistoryDetail AS ihd ON ihh.InvoiceNumber = ihd.InvoiceNumber INNER JOIN
MAS_BIF_GL_Account AS gla ON ihd.SOGLSalesAcct = gla.AccountKey

GROUP BYihh.CustomerNumber, cm.CustomerName, gla.Account, ihh.WarehouseCode
) X

WHERECustRank > 19

View 8 Replies View Related

EXPERTS HELP

Jul 24, 2007

How can I download a report model from report server into a folder programmatically?

View 3 Replies View Related

Qs. For The Experts Here...Help!

Mar 15, 2007

We are doing a community wide project where we need to extract phone numbers and emails from over 1000s local non-profit and gov. webpages. Any suggestion on the best tool outthere that could help us automate this somewhat?

View 1 Replies View Related

EXPERTS HELP

Jun 19, 2007

ANYONE, FAMILIAR WITH THIS.....

REPORT MODEL> ADD NEW ITEM>AUTO GENERATE>CLOSE SOLUTION>OPEN SOLUTION>OPEN MODEL>VIEW DESIGNER

"THE PROCESS COMPLETED SUCCESSFULLY"

YET A WARNING MESSAGE OF ERROR...

View 7 Replies View Related

2005: Calling .NET Procedure

Jul 10, 2006

Hi,I am learning SQL Server 2005. I need to call .NET assembly procedurefrom T-SQL.Here's part of my assembly:using System;using System.Data.Sql;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;namespace DemoSQLServer{public sealed class Demo{[SqlProcedure(Name="PodajKsi��ki")]public static void PodajKsi��ki(){SqlCommand cmd = new SqlCommand("SELECT * FROM Ksi��ki");SqlDataReader dr = cmd.ExecuteReader();SqlContext.Pipe.Send(dr);}...}}I have created assembly in Object Explorer (Programmability /Assemblies).How to call procedure? I tried:exec DemoSQLServer.PodajKsi��kibut I got a message:Could not find stored procedure 'DemoSQLServer.PodajKsi��ki'.Please help.Thank you./RAM/

View 8 Replies View Related

How To Know When A Job Has Finished. For Experts I Think.,

Apr 29, 2004

I have this on my page
Dim backUpDB2 As SqlClient.SqlCommand
backUpDB2 = New SqlClient.SqlCommand
backUpDB2.CommandType = CommandType.StoredProcedure
backUpDB2.CommandText = "msdb.dbo.SP_RESUMENFAC"
backUpDB2.Connection = SqlConnection1
backUpDB2.ExecuteNonQuery()

The SP has this

CREATE PROCEDURE .[SP_RESUMENFAC] AS

EXEC sp_start_job @job_name = 'TransferirDatos(FACT) '

GO

WHen I execute the page after the SP it fills some datagrid but the data is not updated bacuase the job takes 1 minute or more to finish.

Is there anyway to prevent to show the old data? or to detect when the job has finished?

Thanks

View 6 Replies View Related

Help From Experts With A SQL Statement

Jun 17, 2003

Hello all I am trying to write a SQL Statement that will return the fields specified in the Select. However, in some occassion there is no data located in the PS_LEDGER_BUDG C table. I would like to return a zero if possible. When there is no data located in PS_LEDGER_BUDG certain data does not show up. Here is the SQL Statement:

SELECT
A.BUSINESS_UNIT,
A.JOURNAL_ID,
A.JOURNAL_DATE,
B.DEPTID,
B.ACCOUNT,
B.PROJECT_ID,
B.TOTAL_EST_AMOUNT,
C.POSTED_TOTAL_AMT,
B.LEGIS_REF_NBR,
A.OPRID,
B.BUDGET_PERIOD,
A.ACCOUNTING_PERIOD
FROM PS_BUD_JRNL_HEADER A, PS_BUD_JRNL_LN B,
PS_LEDGER_BUDG C
WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND A.JOURNAL_ID = B.JOURNAL_ID
AND A.JOURNAL_DATE = B.JOURNAL_DATE
AND A.UNPOST_SEQ = B.UNPOST_SEQ
AND A.BUSINESS_UNIT = C.BUSINESS_UNIT
AND B.ACCOUNT = C.ACCOUNT
AND B.DEPTID = C.DEPTID
AND B.BUDGET_PERIOD = C.BUDGET_PERIOD
AND B.PROJECT_ID = C.PROJECT_ID
AND C.FISCAL_YEAR = 9999
AND A.JRNL_HDR_STATUS = 'V'
AND A.OPRID = 'ENRI'

I would greatly appreciate any suggestions. Thank you in advance.

David.

View 3 Replies View Related

For You Crystal Experts

Feb 2, 2007

I just started using Crystal, and I have to say, its a pain in the arse.

Can I just run a simple SQL query and get the results I want, without having to use Crystal Syntax?

Here is the query I want to use:

Code:

select * from esmpvald, esmprmtr
where esmpvald.permit_id = esmprmtr.permit_id and
esmprmtr.issue_date >= ('01/01/06') and
esmprmtr.issue_date <= ('12/31/06') and
esmprmtr.permit_id in (select esmvardd.permit_id
from esmvardd, esmudfvr
where esmvardd.var_fld_id = esmudfvr.var_fld_id and
esmvardd.permit_id in
(select esmvardd.permit_id from esmvardd, esmudfvr where esmvardd.var_fld_id = esmudfvr.var_fld_id and
(esmvardd.var_fld_value = 'a') and
(esmudfvr.var_fld_name = 'Commercial Type')) and 1=1)

I will be looping through the "esmvardd.var_fld_value = 'a'" part, going a through f, but for now, I just want to start with the 'a'.

I could do this manually, and throw it in a Word doc, but I'm trying learn this thing. Ideas?

View 1 Replies View Related

Inner Join Experts Out There??

Jul 20, 2005

The scenario:two tablesCustomerTable---------------CustomerIDOrderIDCustomerNameCustomerEmailCustomerPhoneOrderTable---------------OrderIDProductIDProductNameProductCostThis database was handed to me and I was asked to solve a problem - it lookslike an inner join solution would apply, but I'm not 100% sure.There are 14 products total (numbers 1 through 14).I'm looking to get a list of all the customers who have ordered product #1,UNLESS they've ordered product #14 in which case I don't want to know aboutthat customer at all.Any help would be greatly appreciated! I'll watch the newsgroup for theanswer - hopefully your response can help someone else too. However, if youprefer to email me directly, you can send it to me at bunchah at yahoo dotcom.Thanks in advance!(if it'll help, I'll buy the person offering the correct solution a beer -pending age verification of course) ;)-Al

View 11 Replies View Related

T-SQL Experts Please Correct Me !

Feb 27, 2008

Here is the original script
select count(*) FROM (select * from aecprda where
AECPRDA.sales_cat_cd in ('02','10') and
(create_dt > '2008-02-17 18:10:22.000' or price_chg_dt > '2008-02-17 18:10:22.000')
) AS AECPRDA_1
left join (zfmt z inner join muzealbums on z.muzenbr=muzealbums.muzenbr) on AECPRDA_1.product_id = z.vendorcode
and z.Vendorname = N'Alliance'
LEFT OUTER JOIN AECMCAT aecmcat_c3
ON AECPRDA_1.mcat_cd3 = aecmcat_c3.Mcat_cd

I want to add a condition [(substring(AECPRDA_1.upc_1,1,11) = substring(z.upc,1,11) or
(AECPRDA_1.product_id = z.vendorcode))] instead of [AECPRDA_1.product_id = z.vendorcode] TO THE ABOVE STATEMENT.
It is a secondary match condition

1. If aecprda_1.upc_1 doesn��t find a match on zfmt.upc then
2. Match on aecprda_1. product_id = zfmt. Vendorcode
3. I also want to add a condition if there is a product match on both 1 & 2 , then take only the first one and don��t take the second one.

Something looks like below statement but needs one more condition mentioned at item 3 to be added.

select count(*) FROM (select * from aecprda where
AECPRDA.sales_cat_cd in ('02','10') and
(create_dt > '2007-10-22 00:00:00.000' or price_chg_dt > '2007-10-22 00:00:00.000')
) AS AECPRDA_1
left join (zfmt z inner join muzealbums on z.muzenbr=muzealbums.muzenbr) on (substring(AECPRDA_1.upc_1,1,11) = substring(z.upc,1,11) or
(AECPRDA_1.product_id = z.vendorcode))
and z.Vendorname = N'Alliance'
LEFT OUTER JOIN AECMCAT aecmcat_c3
ON AECPRDA_1.mcat_cd3 = aecmcat_c3.Mcat_cd --- I ran this query executing takes hour eventhough appropriate indices were added.

Any help is greatly appreciated.

View 7 Replies View Related

ADVISE.....Experts ! Please

May 15, 2008

I am an Application Developer.
I know just this about T-SQL :
Insert, UPDATE, delete aRecord.
JOIN tables.
Create a Hierarchy column.
execute MERGE Statement. [in order to create IDENTICAL tables .]

Create a Master-detail TABLE . [PrimaryKey-Foreig-Key]

WHAT ELSE SHOULD I KNOW widly speaking about T-SQL ??

What are ESSENTIAL things you should know to Be a BIT of an EXPERT ???

Please be STRAIGHT and SIMPLE.

THANKs a LOT.

View 8 Replies View Related

Calling A C# Assembly From SQL Server 2005

Dec 14, 2007

Hi,
I have got a simple C# assembly which call a ASP.net web service and executes a webmethod, now i want to call this assembly from sql server 2005 within the sql server function. can any body let me know how to do it.
Thanks

View 1 Replies View Related

Calling Functions In SQL Server 2005

Apr 23, 2008

Hi ,
I have a question about calling functions in SQl Server 2005.

Let's say that I have created as a dbo a function called Calculations.

If I want to call it from T-SQL I will write Select dbo.Calculations (arguments if any) etc.

My question is If I can skip the "dbo" part. Call the function without using the dbo .
Can I do that ?
Should I create the function as supervisor ? Does Sql Server has a property or something which will allow me to call the function without using the "dbo." ?

Thank you
zkar

View 4 Replies View Related

Calling A DTS (SSIS 2005) From A Vbscript

Jul 12, 2006

Hello,

I would like to run a DTS using a Vbscript, i managed to get the DTS running, but i would like to pass the value of a global variable, but im having lots of dificulties.

Since it is a VBS i only have access to the things registered on the registry of the machine, so i don't have access to the microsoft.sqlserver.dts.runtime, at is maximum.

So i do the following

Set objApplication = CreateObject("MSDTS.Application")

Set objDTSPackage = objApplication.LoadPackage("T.dtsx", True, Nothing)

'This should pass the variable but insted i have an error

set pkgVars = objDTSPackage.Variables

set myVar = objDTSPackage.Variables.Add("User::Name", false, "User", "TesteA")

'End of the error part

DTSResults = objDTSPackage.Execute()

The error that i got is the following

"Object doesn't support object or method"

What am i doing wrong?

Thank you

Nuno Ferreira

View 8 Replies View Related

Performance Experts Click Here

Jul 28, 1999

Scenario..

Table 1 = Product (one row per productID, also has second key, KEY2)
Table 2 = Usage (many rows per KEY2)

I have a view that aggregates Usage GROUP BY Key2 - Usage_VIEW

I create a view over Product and Usave_view joining on KEY2 - Final_VIEW

When I query Product with a where Product = 'x'
the time is under 1 second return.
I then take the Key2 value returned and query my Usage_View
the time is under 1 second to return.

BUT, if I query My Final_VIEW with where product = 'x'
I can take a coffee break.

Question? Does the aggregate of Usage get performed before limiting to the matching rows in Product?

Can I use "Force Order" hint to make the first table limit the search on table 2? If so, please give syntax on FORCE ORDER. I can't seem to figure it out.

View 2 Replies View Related

Microsoft Corporation Needs SQL Experts

Sep 6, 2006

Hi,

I am a Microsoft Recruiter, and we are hiring SQL Premier Field Engineers in DC, AZ, CA, CO, and MA. Is there a place on the site where I can post these roles? If you know anyone who may be interested, please have them to contact me. I would be happy to send a job description.

Thank you.

View 4 Replies View Related

One For The SQL Experts - Dare I Say TRICKY SQL!

Feb 7, 2006

Guys,Hopefully someone can help.We have a monitoring program that has threads which start and stopmonitoring at various times. There are two tables:THREADLIFECYCLEunique_idstart_time (always populated)end_time (not populated until the thread ends)MONITORRESULTSunique_idtime_of_measurementvalueWhat I am trying to do is find the average value for each of thenumbers of running threads. To explain further, threads will start,stop independently and overlap each other.I want an output that says:When 1 thread was running: average value was xWhen 3 threads were running: average value was yDue to the start and stop nature there could be 1 thread running at thebeginning of the test, mid way through, a number of occassions, etc.Also, the number of threads does not necessarily ramp sequantially -the number running at any time could be like this sequence: 1, 5, 10,7, 12, 4, 2ANY help would be much appreciated - it really has stumped me but lookslike it should be so simple .... But aren't they always the hard ones;-(ThanksGraham

View 22 Replies View Related

Need Experts On Vb.net CLR Intergration Problem

Feb 5, 2007

Hi all I have the following CLR stored procedure :

Partial Public Class StoredProcedures

<Microsoft.SqlServer.Server.SqlProcedure()> _

Public Shared Sub sssGetActiveRepositoryByTitle( _

ByVal title As String)

' Add your code here

Using conn As New SqlConnection("context connection=true")

Dim objCommand As New SqlCommand()

Dim TitleParam As New SqlParameter("@Title", SqlDbType.VarChar, 100)

TitleParam.Value = title

objCommand.Connection = conn

conn.Open()

'build the delete command

objCommand.CommandText = _

"select * from sstRepository where IsActive = 1 and Title =" & TitleParam.Value.ToString

SqlContext.Pipe.ExecuteAndSend(objCommand)

conn.Close()

End Using

End Sub

Now I have a windows service in my data layer that needs to access this stored procedure and convert it into a dataaset to pass to the client application :

Imports System.Data.SqlClient

Imports NBS.SURVEYSDATABASEservice.DBMS

Public Class clsClient

' it inherits the stored procedures from the DBMS class which is the name

'of the CLR dll

Inherits StoredProcedures

Public Function GetClientByVirtualPath(ByVal pstrVirtualPath As String) As DataSet

Try

Dim i As SqlDataReader

'parameters are stored in an array (zero based) for use in the base class

Dim parmArrSqlParms(0) As SqlClient.SqlParameter

' Dim fff As Int32

i = sdsGetClientByVirtualPath(pstrVirtualPath)

' Return MyBase.RunProcedure("dbo.sdsGetClientByVirtualPath", parmArrSqlParms)

Catch ex As Exception

'log the error

'cLogger.LogMessage("ACME", "SampleApplication", Logger.EntryTypes.RunError, System.Environment.MachineName, "clsDemoClass.SelectAllCompanies", ex.Message)

'raise the error to the caller for handling

Throw ex

End Try

End Function

I've tried a bunch of different things to no avail the error I keep getting trying to access the sqlpipe resulsts is " this expressions does not return any values"

any ideas ? I am basically converting around TSQL 50 stored procs into managed CLR code and the CLR funtions are created but I am really having problems accessing the resuluts on the client end .

Help please !

View 1 Replies View Related

Calling A .NET Dll From A SQL Server 2005 Stored Procedure

Sep 13, 2006

Setup: I have a C# 2.0 class library that contains certain business logic that is to be triggered by certain database states. There is a trigger that calls a stored procedure that is working properly (i.e. the stored procedure is being executed). Problem: I have not yet figured out how to call the dll from the stored procedure. Does anybody have any tutorials they could point me to or any advice to point me in the right direction? Thanks in advance for any help.

View 2 Replies View Related