We are planning for a DW solution based upon SQL Server 2005 EE. We will use a 64 bit machine for the Database engine and Analysis Services. Should we have seperate server for SSIS or should we use the same 64 bit box? If we go for a sepaerate server for SSIS should we use 32 bit or 64 bit ?
The primary datasources will be a new developed .NET application and a financial system based upon SQL Server 2000.
Every good advice/experiance is greatly appriciated!
I have the following transact SQL code which I want to change to a set of SSIS components.
SELECT blah, blah
FROM PSTAGE..[stage_OFFER_PRICE_DIVIDEND] AS SOPD LEFT OUTER JOIN PSTAGE..[stage_PRICE_GRP] AS SPG ON SOPD.PRICE_GRP_ID = SPG.PRICE_GRP_ID LEFT OUTER JOIN PSTAGE..[stage_type] AS TYP ON TYP.TYPE_CD=SPG.PRICE_TYPE_TYPE4_CD and TYP.TYPE_CL_CD = '0017'
I know I can join two data sets using a merge join (left join) but how do I combine a third merge join? Should I be doing this or should I just stick my code in a SQL Task instead?
I have a table containing a list of source table names to be transferred to destination.I need to pass each and every table name as a variable and based on the name of the variable I need to select data from source and insert into destination.The source and destinations are on 2 different servers.The source tables have different metadata (different column names and data types).Any help how to implement this would be greatly appreciated.
To be more clear I have List table which has below format
TABLE NAME CUSTOMER PRODUCT INVENTORY
Customer , product, inventory are tables names. So my package should first go and collect the table name from LIST table and then transfer the Customer table data from Server A to Server B. This should be repeat untill all the tables listed in LIST table are transferred.
Hi All, I would like to say thank you in advance for all your ideas. Here is my case i want to implement slowly changing dimension, i know that i can use SCD component of the SSIS, but because of performance issue i am thinking to use something else that can subtitute the SCD component, i want some idea from you guys if anyone has implemented before without Slowly Changing Dimension component. If not, do you have any comment/suggestion to use the SCD component i mean if the worest comes and i use it, what draw backs does it has, for example interms of data size, performance. Note that i use Dedicated Server for the ETL in Production.
it is taken from SQL2K5 SP2 readme.txt. Anyone have idea what to do to implement this ? Our sp2 is failing. we suspect the above problem and researching it.we are running on default instance of SQL2K5 on win2003 ent sp2
"When you apply SP2, Setup upgrades system databases. If you have implemented restrictions on the ALTER DATABASE syntax, this upgrade may fail. Restrictions to ALTER DATABASE may include the following:
Explicitly denying the ALTER DATABASE statement.
A data definition language (DDL) trigger on ALTER DATABASE that rolls back the transaction containing the ALTER DATABASE statement.
If you have restrictions on ALTER DATABASE, and Setup fails to upgrade system databases to SP2, you must disable these restrictions and then re-run Setup."
Hi All, I am now working on the design phase of my project, we are looking to implement Change Data Capture (CDC) but i need some help if you guys has implemented before using the SSIS 2005 componets. I am trying to use the Following:
Source---------Derived Column---------Lookup---------------Conditional Split (to split New records and Updated Records)-----------Destination. Respectively. Lets make it clear, my source holds (Old records and newly added or Updated records), the Derived Column is to Derive new columns called Insert_Date and Update_Date. The Lookup i am Using is to look the Fact_Table(the Old Records) as Reference, and then based on this lookup i will split the records on timely based using the Conditional Split. My question is 1. Am i using the right components? 2. what consideration should i have to see to make it true (some Logics on the conditional split)? 3. Any script which helps in this strategy? 4. If you have a better idea please try to help me, i need you help badly.
I am new to SSIS. I need to transfer data from SQL Server 2005 Operational Database to SQL Server 2005 Report Database. The upload needs to work every night. There are few master tables and remaining are transactions tables.
I am planning to create 2 packages one for master tables and other for transactions tables. Is it the good approach?
Also few of transaction tables are heavy in terms of number of records. Will it better if i further break them in many packages?
I am using book "Microsoft SQL Server 2005 Integration Services Step by Step". Can you suggest any other good book?
I have a requirement of migrating DTS package which is done in Sql Server 2000 to SSIS 2012.
I started with one package having data driven query task and done with source for which i chose OLE DB Source and given the required select query in ssis 2012
I'm stuck now and i'm unable to choose the relevant tools in ssis 2012 for binding, transformation,queries and lookup tabs used in dts 2000 for this DDQT.
SSIS data flow transformation - Lookup task - best practice concerning Cachetype
I would like to know if there's any best practice concerning the CacheType property for the the Lookup task. Default value is "Full", but if the SSIS package is working with at lot of data, i.e. +10 mill. records from the OLE DB source to be handled through a variated numbers of data flow transformation tasks, it must have an impact memory usage if the lookup table also is a large table, i.e. +8 mill. records? When should I consider turning the property value to "none"
Hi all, I had many groups of data which column A store number & column B store location. My syntax as below :- select count(columnA) as no, columnB from table group by columnB No columnB 20 A 10 B 5 C How can I select 5 rows for Each ColumnB? (total 15 rows of record) Thanks!
hi friends....... I have one doubt. how can we use cursor in sql. in oracle they implement cursors on stored procedure, can we implement same concept in sql stored procedure...regards Samuel chandradoss . J
I have place an question, but in some other place in the same forum.
My apologies for that
I wanted to implement mirroring I hereby let u know the steps
1. Created a database in Principal server named Mirror1 and one table named tblMirror1.( no values inside the table)
2. Repeated the same in Mirror in Witness
3. Selected the Principal server's database -->right click-->tasks-->mirror
4. from the left hand panel selected Options -->recovery model-->full
5. selected Mirroring from left hand panel-->configure secuirty-->entered mirror and witness name--Success
6. When I click on start mirroring I have an error stating
" Alter failed for database mirror1"
"Database mirroring cannot be enabled because Mirror1 database may have bulk logged changes that have not been backed up etc...."
7. followed this one BACKUP database mirror1 TO DISK='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK'
got the answer as
Processed 160 pages for database 'mirror1', file 'Mirror1' on file 2. Processed 1 pages for database 'mirror1', file 'Mirror1_log' on file 2. BACKUP DATABASE successfully processed 161 pages in 0.318 seconds (4.147 MB/sec).
According to sources Run a restore of this backup on your mirror. This can be done through the GUI or using a T-SQL command.
The database restore must use the NO RECOVERY option, so the database stays in a loading state.
Also the database name on the mirror must be the exact same name as the principal
RESTORE TestMirror FROM DISK='C:Backup TestMirror_FULL.BAK' WITH NORECOVERY
Hence did the below
RESTORE database mirror1 FROM DISK='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK' WITH NORECOVERY
ERROR
Msg 3201, Level 16, State 2, Line 1 Cannot open backup device 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK'. Operating system error 2(The system cannot find the file specified.). Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
HENCE MODIFIED AS
use master go RESTORE database mirror1 FROM DISK='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK' WITH NORECOVERY
Msg 3201, Level 16, State 2, Line 1 Cannot open backup device 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK'. Operating system error 2(The system cannot find the file specified.). Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
I've been using a piece of transact code to generate random strings of characters in a lot of scripts and I wanted to wrap it up all nice and neat into a function. I came up with the script below.
However, this fails with the following error.. quote:Invalid use of 'rand' within a function. I've allready tried using dynamic sql and sp_executesql inside the function hoping that a new scope would let me use rand() but that isn't allowed either.
I'm Using sql server 2000. Any ideas?
Charlie.
-- snip ----
CREATE FUNCTION dbo.randstr ( @lengthINT) RETURNS VARCHAR
AS BEGIN DECLARE @outputVARCHAR(8000) DECLARE @allowedVARCHAR(8000)
SET @allowed = '234679abcdefghjkmnpqrstuvwxyz234679ACDEFGHIJKLMNPQRTUVWXYZ'
SET NOCOUNT ON DECLARE @charmapTABLE (charpos INT IDENTITY (1,1), symbol CHAR) DECLARE @counterINT DECLARE @characterINT DECLARE @maplengthINT
SET @maplength = LEN(@allowed) SET @character = 0 SET @output = ''
-- Insert the allowed characters into the character map table SET @counter = 1 WHILE (@counter < @maplength + 1) BEGIN INSERT INTO @charmap SELECT SUBSTRING(@allowed,@counter,1) SET @counter = @counter + 1 END
-- Start the loop to generate @output SET @counter = 1 WHILE (@counter < @length + 1) BEGIN
-- RAND() should output a floating point number between 0 and 1 however... -- Sometimes the rand() function spits out a number greater than 1 -- or the multiplication results in 0 WHILE (@character < 1) OR (@character > @maplength) BEGIN SET @character = CAST(@maplength * RAND()+1 AS INT) END
-- Add the random character to @output SET @output = @output + (SELECT symbol FROM @charmap WHERE charpos = @character)
-- Increment and Reset the @character variable SET @counter = @counter + 1 SET @character = 0 END
i hav a text file in this format.. currency,exchangerate(INR),date dollar,45,20/04/2006 dollar,46,22/04/2006 britishpound,65,20/04/06 dirham,12,20/04/06..etc..
now,i want that using this as source.. 2 tables should be created and filled with appropriate data.. CurrencyMaster..Currencyid(PK),Currencyname CurrencyDailyRate..ID,Currencyid(FK),rate,date
My report is taking long time to display hundered of records.So we want to display 15 records per page and Next page link at the end of report.Please help me how to do this.
I have a table search the record. The user can search by name, id, status, address and other information.I want to make the search function more robust. For example, when the database has a record with name = "Michael Jackson", either typing any subset of the name will show this record.I would like to know if there is any easy way to implement this with VS2005 and SQL2005 Express.Thank you
I am using Visual Studio 2005 & SQL Server. How do i implement a DDL for users to select which value to input. like i can with Access. i do not need a table i think. if not the table would have only ID & Value.?
if (!ProcessNonQuery(oCommand, sMethodName)) goto Failed;
Failed:
if (ErrorCode > 0) bReturn = false;
oConn.Close(); } } return bReturn;
p_gID, p_gAuthorityID is of type guid. sNewRole is of type string. I get the error " Object must implement IConvertible at oCommand.ExecuteNonQuery(); whihch is in the function if (!ProcessNonQuery(oCommand, sMethodName))
Now, the root level items will have a ParentID of NULL, and the child nodes will have it's parent's ID in it's ParentID column (a simple self-referencing relationship).
However, when I select it, I would like to Order by ID, but then have all it's children right after it. For example, if this is the unsorted view:
Code:
ID ParentID Name ---------------------- 1 <NULL> Test 2 1 Test Child 1 3 <NULL> Test 2 4 1 Test Child 2 5 3 Test 2 Child 1
I would like the sorted to look like:
Code:
ID ParentID Name ---------------------- 1 <NULL> Test 2 1 Test Child 1 4 1 Test Child 2 3 <NULL> Test 2 5 3 Test 2 Child 1
Is this possible with my current structure? If not, how could I change it?
Iam Executing the sp logic.suppose incase if any problem occurs inbetween execution(NO SPACE,communication failure,log full) data is getting commited partially insteady of rollbacking entire transaction.
CREATE procedure RBI_Control_sp as begin
set nocount on --Checking the count before truncating exec fin_ods..count_sp
--Truncating the Table exec fin_ods..trun_sp
--Data Transfer exec fin_ods..RBI_Data_Transfer_sp
--Checking the count after Data transfer exec fin_ods..count_sp
--temp table Table population,Fetching data from the fin_ods[erp Table] exec FIN_wh..RBI_SPExecution_sp
Hi all, I was hoping I could get some ideas about how to implement a share portfolio. Right now I've got a transaction table that lists the name of the share, quantity, amount and a few other details of every transaction I make. I think I'll be better off breaking it up into two tables; sales and purchases.
I want to create a portfolio using this data. I figured that I could set a query to show one column with names of all the shares (selected from the share purchase table) and another column to work out the current number of shares I hold. Thing is, I can't seem to work out a formula that will pick up all purchases of a certain share, then subtract of sales of the same and then place it in the column for that share in the portfolio.
There's probably a much cleaner way to do it that I'm completely missing. Any ideas? Perhaps edit the input form for the transaction table(s) so that it notes the name of the share and creates a new record if the share isn't already in the portfolio or adjusts it if it is.
Using: VB.Net 2003, SQL Server 2000 and nText Column...
Examples: "Writing BLOB Values to a Database [Visual Basic]" and "Conserving Resources When Writing BLOB Values to SQL Server [Visual Basic]"
I modified the following code from the samples that were available in Help and MSDN. When my application reaches the ExecuteNonQuery statement it informs me that the Byte Array oDocument must implement IConvertible. I have even tried the extended version which uses a SQL Insert followed by UPDATETEXT with Pointers, and I get the same result with the Byte Array used in that example. My goal is to have Users Append certain documents to Proposals within my database, as I understand that is what I would be doing anyway if I used SharePoint.
Following the examples I have not trouble whatsoever reading an Image File from SQL Server 2000, and therefore I expect no trouble with nText Fields. I just cannot seem to insert Documents or Images via VB Code.
What is wrong with the examples that I cannot use the same code?
---- VB Code Follows ----
Private Sub btnSelectFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSelectFile.Click
Dim oSQLConnection As System.Data.SqlClient.SqlConnection
Dim oSQLCommand As System.Data.SqlClient.SqlCommand
Dim oSQLDataReader As System.Data.SqlClient.SqlDataReader
Dim oDocument() As Byte
Try
'Define the Initial Directory to be the "My Documents" Directory.
ofdAttachment.InitialDirectory = "My Documents"
'Filter for File of Word, PDF, and Text. This will suffice for now.
Is there any way to do things with filesystem in SQL 2005 ? I want to check a file and if exists I could replace data into it or create the file if not exists. I tried build a DLL file using C# to do those things and registered it in SQL 2005 but when I execute the procedure it returns an error like this below :
Msg 6522, Level 16, State 1, Procedure testfsq, Line 0A .NET Framework error occurred during execution of user-defined routine or aggregate "testfsq": System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.System.Security.SecurityException: at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet) at System.Security.CodeAccessPermission.Demand() at System.IO.FileInfo..ctor(String fileName) at FSQuery.fsq()
So, is that means that we can't use another namespace in .NET framework but Microsoft.SQLServer.Server ? thanks in advance.
I am totally new to MS SQL and need some help. Finding the so called "help" not very helpful, surprisingly Google not turning up much either, maybe not doing using the right search criteria?
I am using MS SQL Server Express 2005.
What I want to do is when a user adds a grower to the GrowerList (via an ASP.NET page) it is done so that the GrowerID increments automatically to the next unique number (currently set GrowerID as primary key, type int).
I'm currently learning how to implement Encryption into my SQL database, and I've run into something I don't know how to explain or understand. I'm working on creating a set encryption for a set of columns in a database, and I don't know how to figure out what size the VARBINARY column needs to be. After some trial and error, I've noticed that a clear text of 9 characters needs 68 varbinary characters, 100 cleartext=148 varbinary, and 254 cleartext= 308 varbinary characters. How is the minimum number of varbinary characters calculated?
