OLEDB Access PB

SQL Server Integration Services

Index ‹ SQL Server

- Previous
  - 1
    - DTS old style and Dynamic Properties Tasks Im using embedded SS2000 DTS packages in SSIS. These legacy packages use Dynamic Properties Tasks to pick up connection settings from a named ini file. The name of that ini file needs to change occasionally. To date the only way we have of doing this is resetting all the dynamic properties to point at the new ini file. Unfortuntely there are a lot of connections and properties and there is no budget to recreate these packages in SSIS. There must be an easier way ! Does anyone know how to quickly change Dynamic Properties Tasks to look at a new ini Tag: OLEDB Access PB
  - 2
    - Help plz - need query to get the following kind of output i want result to be in the required format given below: here is the result column A ---------------- 5 7 10 14 22 30 .... i want output to be in this form with a single sql statement: column A column B ---------------- ---------------- 5 2(difference b/n the current record and the next record) 7 3 10 4 14 8 22 8 30 0 can any one plz post the query which gives the above kind of result.. thnx in advance.. Tag: OLEDB Access PB
  - 3
    - RAISERROR does not cause SQL task to fail - why? Greetings, I have a stored procedure with a TRY / CATCH block. In the catch block I capture information about the error. I then use RAISERROR to "rethrow" the exception so that it will be available to SSIS. I execute the stored procedure through a SQL task. I observe that SSIS reports the SQL task succeeds (the task box turns green) when RAISERROR is invoked. If I comment the catch block with RAISERROR then SSIS reports the task failed. (I created a simple procedure that does a divide by zero to force an error.) The expected error message is displayed when the sproc is run from the SQL Server Management Studio command line so I believe that the stored procedure is doing what I intended. I would like to handle an error within my stored procedure without destroying SSIS's ability to detect a task failure. Is this possible Is there an alternative to using RAISERROR Thanks, BCB Tag: OLEDB Access PB
  - 4
    - Moving Average using Select Statement or Cursor based? ID DATE(dd/mm/yy) TYPE QTYIN COST_IN_AMT COST_OUT_AMT(MOVING AVERAGE) 1 01/01/2007 PURCHASE 10 1000 2 01/01/2007 PURCHAES 5 1100 3 01/01/2007 SALES -5 *TobeCalculated 4 02/01/2007 Purchase 20 9000 5 02/01/2007 SALES -10 *TobeCalculated 5 02/01/2007 purchase 50 8000 6 03/01/2007 Sales -10 *TobeCalculate 7 01/01/2007 Purchase 20 12000 I have a table when user add new sales or puchase will be added to this table ITEM_TXNS. The above date is part of the table for a ProductID . (The field is removed here) In order to calculate the balance amount using moving average, I must calculated the cost_out_amt first on the fly. When user add new sales I also need to determine the cost/unit for a product id using moving average. The problem is I can not just use sum, because i need to determine cost_out_amt for each sales first which will be calculated on the fly. The reason i dont store the cost_out_amt (instead calculate on the fly) because User could Edit the previous sales/purchase txn or Insert new sales for a previous date. Example THe record with ID 9. By Adding this txn with ID 9, would cause all the cost_out_amt will be incorrect (Using moving Average) if i store the cost_amout_out on entrying txn and need to be recalculated. Instead I just want to calculate on the fly and able to determine the cost avr for a specific point of time. Should I just use Cursor and loop all the record and calculate the cost or maybe I can just use on Select Statement Tag: OLEDB Access PB
  - 5
    - Grouping Hi, I have a stored procedure which is returning results in the following format: No.of substances Count 1 29 2 89 3 876 .. .. 15 56 16 89 Now i need to display like this: No.of substances Count % 1 20 2 89 .. .. >=9 8766 Total All the substances which are >=9 have display no.of substances =>9 and Count is sum of all counts which have the no.of substances>=9 How to achieve this Thanks in advance Tag: OLEDB Access PB
  - 6
    - Problem with "phantom rows" Hi, I am seeing something strange. I have a data file that has 6 rows that looks like: ABC, "OPENING BALANCE", 1234, etc ABC, "CLOSING BALANCE", 1235, etc ABC, garbage data, etc XYZ, "OPENING BALANCE", 1234, etc XYZ, "CLOSING BALANCE", 1235, etc [][] -- weird box things that shows up in my flat file conn mgr I have a script transformation that reads the incoming rows as a single line, then checks for the value of the row.line, whether it's "OPENING BALANCE", or "CLOSING BALANCE". It ignores all other lines. I even added a message box that pops up when it finds "OPENING BALANCE" or "CLOSING BALANCE". It only pops up 4 times, like it should. However, when I check the database, it has 6 rows! The 4 good rows are there, and 2 garbage rows with a bunch of NULLS in them. I really don't understand how this is happening. Please, any ideas. Thanks Tag: OLEDB Access PB
  - 7
    - SQL Express Help Files Where do I find the Help files for SQL Express. When I click on help it says, help is not available. Tag: OLEDB Access PB
  - 8
    - Design question - Foreign key indexes A rather elementary design question - is it a good idea to index foreign keys in fact tables I understand this will slow down inserts/deletes/updates, but when the cube is processing will it speed up processing time Does the cube generate all dimension members then query fact tables for them Does it query fact tables with group by's on participating dimensions I see example cubes that have this, but am unable to find documentation saying this is a good idea. I would hate to miss out on a big performance boost, but don't want to take the chance to slow things down... Any suggestions / supporting documentation is very much welcomed. Thanks in advance, John Hennesey Tag: OLEDB Access PB
  - 9
    - ADO Error: an insufficient number of arguments.... Im currently using Access 2000 which is connected to a SQL database. I have a query (Or View) that runs every month. The problem i had was i had to manually make some changes every month to get to the data i needed so i changed this View into a Function with a parameter so i can input the detail(s) i need and it will run correctly - this works so far but the original View was also used within other queries that were created. Now the problem i have now is some of the other views that are now connected with the newly created Function comes up with the error: ADO error: an insufficient number of arguments were supplied for the procedure or function Name_Of_Function. I called the newly created function the exact name as the original view to ensure i had no problems. Any idea of whats happening here and how to resolve Thanks Tag: OLEDB Access PB
  - 10
    - Replication looking for errant sysserver links We have replication successfully running but when I tried to set up a new test instance on completely different servers (Server A to Server B, say) the push agent to create the susbcription on Server B complains 'Could not find server 'Server C' in sysservers. Execute sp_addlinkedserver. . . .' Server C was never configured as a subscriber for the publication in the first place. What's more, Server C is actually a working linked server and definitely is in sysservers for Server A. I originally tried this on yet another server pair and got a similar message referencing a server name that no longer exists. I stopped messing with that one, figuring it was some historical naming issue I didn't want to mess with. But now I'm stumped and intrigued. Can anyone help Tag: OLEDB Access PB
  - 11
    - When to reindex? We have a fairly large database (320 tables, 70GB+) on SQL 2000 where reindexing using 'dbcc dbreindex' currently takes almost 3 hours. We are reindexing all tables and dbreindex takes up 80-90% of the system cpu making any other job slow. All indexes are setup with a fill factor of 70%. As the database grows we would like to minimize the time spend on reindexing. One approach could be to use the output from 'dbcc showcontig' for deciding on on whether to reindex a table or not. E.g. use 'ExtentFragmentation > 10%' as the criteria for starting reindexing on a particular table. Is this a suitable approach or are there any other better parameters to consider Rgds Bertrand Tag: OLEDB Access PB
  - 12
    - Calling CLR stored procedure from within a CLR table-valued function giving errors We are trying to create a TVF that executes a CLR Stored Procedure we wrote to use the results from the SP and transform them for the purposes of returning to the user as a table. Code Snippet [ SqlFunction ( FillRowMethodName = "FillRow" , TableDefinition = "CustomerID nvarchar(MAX)" , SystemDataAccess = SystemDataAccessKind .Read, DataAccess = DataAccessKind .Read, IsDeterministic= false )] public static IEnumerable GetWishlist () { using ( SqlConnection conn = new SqlConnection ( "Context Connection=true" )) { List < string > myList = new List < string > (); conn.Open (); SqlCommand command = conn.CreateCommand (); command.CommandText = "GetObject" ; command.Parameters.AddWithValue ( "@map" , "Item" ); command.CommandType = System.Data. CommandType .StoredProcedure; using ( SqlDataReader reader = command.ExecuteReader ( System.Data. CommandBehavior .SingleRow )) { if (reader.Read ()) { myList.Add ( reader[0] as string ); } } return ( IEnumerable )myList; } } When command.ExecuteReader is called, I am getting an "Object not defined" error. However, the stored procedure can be used in SQL Management Studio just fine. Code Snippet EXEC GetObject 'Item' Is there some sorf of trick I am missing Thank you! Tag: OLEDB Access PB
  - 13
    - Scheduling an automatic backup Hi , I am using Microsoft SQL Server Management Studio Express 9.00.3042.00. I want to take the database backup daily at specific timing for instance, 8:00am daily. How can I take it automatically It should be take the backup on daily basis without my interference. with programatically Can anyone help me i neeed answer with clear example or screen shots. Thanks in advance Tag: OLEDB Access PB
  - 14
    - Emailing Report I need to export a view into excel and mail to my manager daily morning. Is there a way i can automate this process. I know how to convert view to excel but i need a better solution to automate this process with emailing to my mgr. Please suggest a better approach of doing this. Thanks, Tag: OLEDB Access PB
  - 15
    - Documents in VLDB Hello All, I have been experimenting with SQL Server 2005 partitions. I loaded a terabyte of information into 2 tables. The first holds the document information and the second holds the actual binary document (in this case pdf). Most of the documents are about 1 megabyte in size, but the largest is 212 megabytes. SQL Server has no problem storing the blobs. The problem occurs when I attempt to get the data. I did some quick tests to test how fast I could pull the documents out. The largest took about 24 seconds. The 1 meg documents are sub-second. Here is how the 212 meg doc breaks down: Time to load datatable: 18.79 seconds Time to load byte array: 3.84 seconds Time to Write and open document: 0.01 seconds If I access the file from a file server, the time is 0.04 seconds to begin showing the document. As you can see, the longest time period is related to retrieving the data from SQL, and it is much slower that launching it from disk across the network. (note: the sql server and file server used to test are next to each other). My question is, how can I speed up the access from SQL Server I believe the keys are "partition aligned". Any suggestions would be appreciated. I will add the table definitions and partition information as a reply since only 5000 chars are allowed in the post. Tag: OLEDB Access PB
  - 16
    - Scheduled Backup suddenly started giving errors Hi, I have scheduled full backup daily at morning 6:00. It was running fine till yesterday. Suddenly today morning it failed giving following error.... I have executed maintenance plan manually and again it gave same error.... 10/19/2007 11:03:10,FullDBBackup.Subplan_1,Error,1,*****,FullDBBackup.Subplan_1,Subplan_1,,Executed as user: ****\SYSTEM. ...9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:03:10 AM Error: 2007-10-19 11:03:10.98 Code: 0xC0010018 Source: Back Up Database (Full) Description: Error loading a task. The contact information for the task is "Back Up Database Task; Microsoft Corporation; Microsoft SQL Server v9; 2004 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1". This happens when loading a task fails. End Error Error: 2007-10-19 11:03:11.25 Code: 0xC00291EC Source: {C7FB73D1-ADF1-42C0-B3D5-9F8014CC206B} Execute SQL Task Description: Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Warning: 2007-10-19 11:03:11.25 Code: 0x80019002 Source: OnPreExecute Description: SSIS Warning Code DT... The package execution fa... The step failed.,00:00:01,0,0,,,,0 Pls help me as daily backups are very crucial to me... Tag: OLEDB Access PB
- Next
  - 1
    - Maximum number of users (licensing) I need a small SQL server for up to 10 users. If I understand the EULAs and FAQs correctly, there is no limit on the number of users imposed by the SQL Server 2005 Express license. If so, then the number of users is only limited by the OS's license so all I need is SQL Server 2005 Express (free), Windows XP Pro (10 users limit) and no CALs for either Windows or SQL Server. I'd be thankful if someone could verify this. Tag: OLEDB Access PB
  - 2
    - Scheduling sqlcmd script in sql agent Is it possible to schedule a sqlcmd script within sqlagent That is without running a cmdexec Thanks, Tag: OLEDB Access PB
  - 3
    - measure group not showing any data inspite the facttable having data Hi, I could successfuly deploy and process the cube. But when I go to the 'Browse' tab of the cube and drag and drop a few dimensions and measures I don't see any data. I even used simple MDX queries and even that returns 'NULL' facts. I have set the 'unknown' member as visible in the time dimension because some of the dates in the fact tables are null. Any thoughts on this would be greatly appreciated. Thanks. Tag: OLEDB Access PB
  - 4
    - How do i change collation? I created a new server registration under my SQL Server Group. After creating the registration via EM, I looked under properties and the collation is SQL_Latin1_General_CP437_CI_AS. It needs to be Latin1_General_BIN because this is the collation order in the production server. Please bear in mind that I am not a DBA and this is all new to me, but unfortunately I'm the one who has to fix this. Please keep the terms simple in any response. Thanks. Tag: OLEDB Access PB
  - 5
    - connecting to sql server from mobile device How can I connect to sql server from a mobile device the server is on pc and my app is on the device. I want to perform commands and stored procedures which will be performed online on the pc and get immediate results. is there any way of achiving that Tag: OLEDB Access PB
  - 6
    - Scheduling report delivery for the first WORKING day in the month? Has anyone figured out a good way of doing this The only suggestions I have seen along these lines involve scheduling the job every day and then manually editing the SQL Agent job that is created. However the problem with this is that it completely removes the ability for non programmers to set the schedule themselves using the web interface. Any ideas Tag: OLEDB Access PB
  - 7
    - Pivot Table Bug with AdventureWorks A user pointed out an odd bug / inconsistency that occurs when using Excel pivot tables. Depending on how attributes are related to each other, Excel does some odd things when cross joining attributes from the same dimension on an axis. Example 1. "Correct" cross join of attributes: Connect to AdventureWorks from Excel pivot table. Drop in "Internet Sales Amount". On rows add "State-Province" and "Total Children". Row 4 should read "State-Province, Total Children, Total". Click the drop-down on "Total Children" and select "1". The results should be filtered so that you only see states where there are internet sales for families with 1 child. Example 2. "Incorrect" cross join of attributes: Connect to AdventureWorks from Excel pivot table. Drop in "Internet Sales Amount". On rows add "State-Province" and "Country". Row 4 should read "State-Province, Country, Total". Click the drop-down on "Country" and select "United States". The results *should* be filtered so that you only see states belonging to the United States. Instead, we still see states belonging to other countries. Shouldn't the "United States" selection on the rows preclude the states from other countries I don't understand why the pairs of attributes in examples 1 and 2 interact so differently. I would appreciate any meaningful explanation than I can pass on to the user. Tag: OLEDB Access PB
  - 8
    - Pivot Select Hi, I'm able to get Pivot to work but I'm having trouble limiting the record set. I want it to select only records from this FiscalYear. I have a table with a field called CurrentBudgetYear that I use as a control. So FiscalYear should equal CurrentBudgetYear but my results include all FiscalYears. SELECT ProjNo, TaskCode, [1] AS P1, [2] AS P2, [3] AS P3, [4] AS P4, [5] AS P5, AS P6, [7] AS P7, AS P8, [9] AS P9, [10] AS P10, [11] AS P11, [12] AS P12 FROM (SELECT e.ProjNo, e.TaskCode, e.FiscalPeriod, e.ActualAmt FROM tblActualExpend AS e INNER JOIN tblBudgetConfig ON e.FiscalYear = tblBudgetConfig.CurrentBudgetYear )p PIVOT ( SUM(ActualAmt) FOR FiscalPeriod IN ( [1], [2], [3], [4], [5], , [7], , [9], [10], [11], [12]) )AS pvt ORDER BY ProjNo, TaskCode; Thanks in advanced for any help. Tag: OLEDB Access PB
  - 9
    - Difference of compact edition and mobile edition Hello I recently downloaded the sql mobile edition, and then I found out about the compact edition. I understand that these are relational lite version of sql server that can run on mobile devices. However, what is the difference between the CF and mobile edition Why should I choice one over the other I have just heard about sql everywhere, seems there are all these edtions and how would you choice the right tool for the right job Many thanks if someone could explain further, Steve Tag: OLEDB Access PB
  - 10
    - how sql server handles nvarchar Hi i'd like to know about varchar , when sql store data as nvarchar it store the actual length besides 2 bytes but the question is if there is another field does sql server store the next field besids nvarchar so there is no seprated bytes and is this exist and i update data of nvarchar does sql sqerver shifted the reamin data exist in the same track please send me the answering Tag: OLEDB Access PB
  - 11
    - Error Message when restoring Database I am receiving the following error message when I am trying to restore a database in SQL Express 2005. Has anyone ever seen it before microsoft.sqlserver.express.connectionInfo Tag: OLEDB Access PB
  - 12
    - SSRS Report Parameters Hi, I have kept two Report Parameters "StartDate" and "EndDate" and i have selected From Query and viewed the preview. I got the reults but when i am selecting the two dates it is not validating the condition. I need the results between StartDate and EndDate. For this i have to set any where in the parameters or is there any other way can you please help me in this Thanks Dinesh Tag: OLEDB Access PB
  - 13
    - Email task - no error - no mail I am trying to explore the email task in SSIS. I have a task which has only the email task. When I run its not throwing any error instead it says successfuly run but I am not recieving mail. I dont know how to trace this, could someone help me on this Tag: OLEDB Access PB
  - 14
    - Openrowset to Oracle doesnt work after upgrading to SQL Server 2005 Hi, I just upgraded to SQL server 2005 and some of my Openrowset to Oracle doesnt work. I found out that the issue occur with field with structure Numeric. The issue is not solve even when I try to cast the field to varchar. sometimes it succeeded and sometimes I get error 7320 & 7321. I'm using Oracle connector from SQL to Oracle. I've 64 bit SQL Server. Attach the code, the Numeric field is SCORE. select * FROM OPENROWSET ( 'ORAOLEDB.ORACLE' , aaaa ; bbbb ; cccc , 'SELECT AUDIT_OPRID, AUDIT_STAMP, AUDIT_ACTN,EMPLID, ACCOMPLISHMENT, MAJOR_CODE, CAST(SCORE AS Varchar(250)) FROM SYSADM.PS_RTA_ACCOMPLISHM WHERE 1=2' ) Anyone can advice on resolution Thanks, Assaf Tag: OLEDB Access PB
  - 15
    - Force Package Completion? Is there a way (during the Control Flow) to force a package to complete successfully when a specific condition is met Here is what I'm trying to do: A package will be scheduled to execute once a day The first thing it does is downloads Excel files from an FTP site. If there are no files to download, I don't want the package to fail, I simply want the package to stop (preventing the subsequent Data Flow tasks from executing), returning a successful completion. The reason, I need a successful completion is because I plan to have MOM monitor the Windows event logs, notifying us of any errors that this package logs. Tag: OLEDB Access PB
  - 16
    - dropdown menu in sql reporting Is it possible in SQL Reporting 2005 to have dropdown menu(eg..on mouseover list of static menu appears from which i can navigate to other rdl files while passing all parameters) .. Tag: OLEDB Access PB

plab

I definite a source OLEDB MS Access and my fields of the type ��Text�� are seen in type of field DT_WSTR (Unicode) instead of DT_STR.

I do not include/understand why and like then, I must integrate them in fields varchar and not nVarchar, I do not find the solution

thank for your solution

Top

Re: OLEDB Access PB

jwelch

You can use a Derived Column transform to convert between Unicode and Ansi. Use the operators under the Type Casts section.

Code Snippet

(DT_STR, 50, 1252) [Your_Column]

Top

Re: OLEDB Access PB

Subhash

Hi plab,

You may even use a DataConversion transformation to change type DT_WSTR to DT_STR with a required length.

Thanks

Subhash Subramanyam

Top