This problem concerns files FTP'd (using True64) from an AIX app, to an WIN server
These files can't be loaded into the SQL Server DB using BCP (String data, right truncation) nor into an ACCESS DB ("Overflow"), (but can be loaded into Excel).
If the file is opened and saved in a Windows/DOS environment it loads fine, or contents copied to an empty win/dos file.
If the same file is sent from to the Win server using DOS FTP then it loads fine.
It seems to be that these DBMS's don't like something about the operating system level file properties (hdr or EOF?).
I installed the samples and tutoriales on the same server as SQL Server 2005 and VS 2005 Team Suite. Living in Belgium, the local settings are: language = Duch (Belgium) code page = 850 e.g. in the database samples (AdventureWorks database €“ AdventureWorks Warehouse database) my date notation is: 26-12-2005 my number notation is: 1,0013016921998599
all demo samples and tutorials are developed using language = English (United States) code page 1252 e.g. in SampleCurrencyData.txt the date notation is: 12/26/2005 00:00:00 the number notation is: 1.0013016921998599
I do not have any problems to test the diferent services (Data Base Engine Services, Analysis Services, reporting & notifications services ..) but I do not succeed to deploy any samples of integration services !
e.g. for Integration Services Tutorial - lesson 1: €œCreating the Project and Basic Package€? there are no error messages, 1097 rows are processed but, checking the result in the database, no data is updated in the FactCurrencyRate table of the AdventureWorksDW database !
Why? - is the problem related to the local language settings? how to solve this? - what is the influence of the code page ? is there any compatibility between 1252 and 850 as code page? - Server collation (e.g. Latin_1_General_CI_AS) is reported as key for the Unicode notation for character strings but what about notation of numbers? - when to use float data type DT_R4 or DT_R8? - I have remarked that the DT_DBTimeStamp is undependent from the source time notation €“ Is this correct? - what is the difference between DT_Date and DT_DBDate or DT_DBTime, or DT_DBTimeStamp? - Is Integration Services dependant of the local settings of the database engine? - how to set / modify additional regional properties in a SSIS and SSRS package? - how to change the default setting of the Flat File Connection Manager [starting the wizard, the local setting for the language €“ Dutch appears and this is OK for me but as codepage appears 1252 (ANSI Latin) and this is not OK as my server code page = 850]? - how to work with e.g. US based data as source and Belgium settings for reports?
I have a task for which I have to load csv file from a shared directory into sql table. Right now I'm stuck with a road blocker, The issue is the shared drive contains all the history files as well and I have to pick only the latest file. But I cannot identify latest file based on the file name because it doesn't contain any date in the file name. However by seeing file properties I can pull latest file.Â
I am testing SSIS and have created a Flat File Destination. I defined the Flat File Connection as New for the first time and it worked fine. Now, I would like to go back and modify the Flat File Connection in the Flat File Destination Editor, but it allows only to create a New connection rather allowing me to edit the existing one. For testing, I can go back and create a new connection, but if my connection had 50-100 columns then it would be an issue to re-create it from scratch.
This problem is a bit weird but I'm just wondering if anybody else experienced this.
I have a package that has file system tasks (copying dtsx files actually). Basically the package copies other packages to a pre-defined destination. Thing is, it only works if one of the packages it is configured to copy has some sort of sensitive data (e.g., a connectionstring with a password), otherwise it reports a success message on execution but doesn't actually do anything. I've checked the forcedexecutionresult and it is set to None for that matter.
Just wondering if anybody else experienced this problem and of course if there's a way to solve it.
Here's a table function that returns a table variable containing various file properties (creation date, size, etc.). The one limitation to remember is that it won't work with files on mapped drives; you have to use the full \<Server><Share><Folder1..Folder n><Filename> syntax. Still, it's a pretty useful thing to have around. :)
/****************************************************************************/ CREATE FUNCTION fnc_GetFileProps
AS BEGIN DECLARE @OLEResult INT DECLARE @FS INT DECLARE @FileID INT DECLARE @Message VARCHAR (8000) DECLARE @ErrorSource VARCHAR (255) DECLARE @ErrorDesc VARCHAR (255) DECLARE @INT INT DECLARE @VARCHAR VARCHAR (1024) DECLARE @DATETIME DATETIME DECLARE @BIGINT BIGINT
-- Create an instance of the file system object EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT IF @OLEResult <> 0 BEGIN EXEC sp_OAGetErrorInfo @FS, @ErrorSource OUT, @ErrorDesc OUT
I would like to store the file properties like (title, created by , created time, modified time) and load in to a audit table along with file name. I know how to design this process inside a foreach lookup with script task and execute sql task.
I'm trying to achieve this by editing my existing code which captures the most recent file based on its file properties.Â
public void Main() { // TODO: Add your code here var directory = new DirectoryInfo(Dts.Variables["User::Csv_Source"].Value.ToString()); FileInfo[] files = directory.GetFiles("*.csv"); DateTime lastModified = DateTime.MinValue;
I put togehter a package that opens a flat file, parses the data based on the semi-colon delimeter, and imports the rows into a database table. Thats the fun easy part.
What I cant figure out is how to add a variable that will hold a constant ID value that will be persisted with the same value to all rows inserted to the DB. Making the problem harder, I would like that this value be defined in a properties file or database table of some sort so that I can do a lookup based on the file name / location to find out what value should be used.
Any suggestions? I hope my explanation makes at least some sense - but basically I want to do a look up in a configuration of some sort, pull out a single value, and add it to a data import.
We€™ve got almost 250 old dts packages which simply loading data into Sql tables from plain files or at the reverse point. Most of them are defined with fixed fields and its fixed positions one after one. We don€™t want to migrate them using Import wizard, on the contrary we€™re producing them from the beggining taking advantatge of SSIS architecture to the full. And now, we€™re trying to imagine how to migrate automatically that valuable info from Sql Server 2000 to Sql Server 2005 without efforts€¦ You know, any program be able to move that detailed info to SSIS.
So we would avoid to select again all these positions per each file -very tedious and we're lazy
I don€™t see how except, of course, migrate them directly
Let me know if you need further explanations or more clarity on that.
I know that I think as sql2k programmer-dba yet but I can€™t avoid.
I€™ve got Flat File Connection Manager Editor dragged with a text file as €˜ragged right€™ format and CRLF as header row limiter. When from properties page and Columns option I€™m going to alter just a few colums I am not be able. It seems that you must erase all of them in order to define one or two. And in the case you€™d have 50???? When I ran sql2k DTS designer did that without problems, alter columns again and again.
As far as I know it€™s a lose of flexibility, or not? Or is there any way for do that without deleting nothing else?
I need to open a File through File connection manager and want to assign these file properties to SSIS precreated varibale or Newly created varibale. I want to show file properties in Propertygrid. Properties grid will conatin File Propeties Column and SSIS varibale Combobox column. The combo box will contain New variable field. When user select New Variable field, then a new SSIS varibale window will open and we can able create New variable and that Newly created variable should add to that property comboBox.
For Instance if we create a new varibale Name "Creationdate" by clicking on New Varible in ComboBox, then that CreationDate variable should add to Property ComboBox in PropertyGrid. After adding when we select that variable Name "Creationdate" then that selected file Creation date should assign to SSIS varibale "Creationdate" field.
I have a Users table with UserName and Password columns, in SQL Server 2005. Currently the SQL Server is not set up to use case-sensitive criteria in the sprocs. How can I write my sproc to be case sensitive when searching on username and password?
I transferred data from Oracle to sql server 2005. Now what i want is to make data in the tables case-sensitive .(it has to be data inside the tables only and not table and column names). what i tried is :
alter database test collate Latin1_General_CS_AS
But to my horror it made the tables name case-sensitive . Plz help me out asap.
I have just been given a new SQL Server 2000 box to look after in production. I just tried to run a standard t-sql script I use for setting up backup jobs and so on. However, it failed with a long list of errors - quite a surprise at first since I have run the same script on many other servers wihtout a hitch. On close examination, the problem appears to be that the new serer is setup with a server default collation... Latin1_General_BIN (I think a binary based collation makes this a case sensitive server).
This is quite an urgenet one since I have to get this wrapped up today. I don't think I can change the server's default collation without a lot of red-tape. Is there a quick way to run my scripts in a 'case insenstive' context within Query Analyzer? If so, how?
I have a table called SrcReg which is having a column name called IsSortSeqNo smallint. I am mapping this column in SSIS and the problem comes when I try to execute against different database which has this table but the column name as ISSortSeqNo. I mean both databases having same name but one with upper case. So SSIS fails executing due to meta validation issue.Is there any way to check whether the column name is in small case or upper case through query?
Hi, SELECT UserID, UserName, Password, PublisherID, CurrencyFROM [User]WHERE (Password = 'Anitha') I am using the above mentioned it is working but int the password field i had given it as anitha. Now the querry is retriving the record for anitha, it shouldnot happen. The querry should retrive the record of anitha only for where condition anitha and not for Anitha or ANITHA etc.. Thanks Vishwanath
Over the weekend I decided to give it the ability to do a case sensitive character swap. Updating the code was pretty straight forward but when I was through, I noticed that I was getting Cardinality Estimate warnings that I wasn't getting before.
Anyway, here is some test data and two versions of the executed SQL (the base code is all dynamic and the two code versions are the result of toggling the @MatchCase parameter).
/* ======================================== CREATE TABLE ======================================== */ CREATE TABLE [dbo].[PersonInfoSmall]( [PersonID] [BIGINT] NOT NULL, [FirstName] [NVARCHAR](50) NOT NULL, [MiddleName] [NVARCHAR](50) NULL, [LastName] [NVARCHAR](50) NOT NULL,
Hi there ,1. i have a database and i want to encrypt my passwords before storing my records in a database plus i will later on would require to authenticate my user so again i have to encrypt the string provided by him to compare it with my encrypted password in database below is my code , i dont know how to do it , plz help 2. one thing more i am storing IP addresses of my users as a "varchar" is there a better method to do it , if yes plz help me try { SqlConnection myConnection = new SqlConnection(); myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["projectConnectionString"].ConnectionString; SqlDataAdapter myAdapter = new SqlDataAdapter("SELECT *From User_Info", myConnection); SqlCommandBuilder builder = new SqlCommandBuilder(myAdapter); DataSet myDataset = new DataSet(); myAdapter.Fill(myDataset, "User_Info"); //Adding New Row in User_Info Table DataRow myRow = myDataset.Tables["User_Info"].NewRow(); myRow["user_name"] = this.user_name.Text; myRow["password"] = this.password.Text; // shoule be encrypted //not known till now how to do it myRow["name"] = this.name.Text; myRow["ip_address"] = this.ip_address.Text; myDataset.Tables["User_Info"].Rows.Add(myRow); myAdapter.Update(myDataset, "User_Info"); myConnection.Close(); myConnection.Dispose(); } catch (Exception ex) { this.error.Text = "Error ocurred in Creating User : " + ex.Message; }
A common issue that I run across with clients is they want only want to process a file if it's finished transmitting to the server. This SQL Server 2005 task reads the properties of a file and writes the values to a series of variables. For example, you can use this task to determine if the file is in use (still be uploaded or written to) and then conditionally run the Data Flow task to load the file if it's not being used. You can also use it to determine when the file was created in order to determine if it must be archived.
I'm very new new at this. I'm try to deploy a report model and got this message. I have no idea what its going on about.
Can anyone help me?
Aku
------------------------------
System.Web.Services.Protocols.SoapException: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) ---> Microsoft.ReportingServices.Diagnostics.Utilities.RPCException: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) ---> System.Exception: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) ---> System.Exception: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) ---> System.Exception: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) ---> System.Exception: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) ---> System.Exception: Bad Data. (Exception from HRESULT: 0x80090005) --- End of inner exception stack trace --- --- End of inner exception stack trace --- --- End of inner exception stack trace --- --- End of inner exception stack trace --- --- End of inner exception stack trace --- at Microsoft.ReportingServices.Diagnostics.DataProtection.ProtectData(Byte[] unprotectedData, String tag) at Microsoft.ReportingServices.Library.ConnectionManager.ConnectStorage() at Microsoft.ReportingServices.Library.Storage.NewStandardSqlCommand(String storedProcedureName) at Microsoft.ReportingServices.Library.DBInterface.GetAllConfigurationInfo() at Microsoft.ReportingServices.Library.RSService.GetSystemProperties(Property[] requestedProperties) at Microsoft.ReportingServices.WebServer.ReportingService.GetSystemProperties(Property[] Properties, Property[]& Values) --- End of inner exception stack trace --- at Microsoft.ReportingServices.WebServer.ReportingService.GetSystemProperties(Property[] Properties, Property[]& Values) (System.Web.Services)
Hello! We are having problems with a single SQL Server where the Server properties will not save, and always revert to the default. It occurs only on those tabs with "configured" and "running" options at the bottom. Other tabs will keep the settings we custom. The big problem is with the memory and processor tabs which even when we've made only a single change and stopped and started the service, reverts back to a default. Any clues - we are logged in as "sa" and have not removed the SA priviledges.
Help;Im New with a company and the sql server ismaxing out the cpu'sWe have 3 web servers load balanced....large volume of datathe current PropertiesComputer:4 amd 2.88 processors4 g MememoryRecomendations of good sql setupmemory, cpu etcThank youmike
Is there no longer a graphic interface for the parameters that are output with sp_configure? There are many options that are settable that are not in the Sql Server Propreties, but these items were in the 6.5 graphic interface through EM ex. number of open objects. Is there a script to save these settings so the installation can be recreated?
DECLARE@Stage TABLE ( RowID INT IDENTITY(0, 1) PRIMARY KEY CLUSTERED, Data VARCHAR(90), Section INT )
INSERT@Stage ( Data ) EXECxp_cmdshell 'ipconfig /all'
DECLARE@Section INT
SET@Section = 0
UPDATE@Stage SET@Section = Section =CASE WHEN ASCII(LEFT(Data, 1)) > 32 THEN @Section + 1 ELSE @Section END
SELECTMAX(CASE WHEN x.minRowID IS NULL THEN NULL ELSE s.Data END) AS Header, MAX(CASE WHEN s.Data LIKE '%Host Name%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END) AS HostName, MAX(CASE WHEN s.Data LIKE '%Media State%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END) AS MediaState, MAX(CASE WHEN s.Data LIKE '%Description%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END) AS [Description], MAX(CASE WHEN s.Data LIKE '%IP Address%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END) AS IPaddress, MAX(CASE WHEN s.Data LIKE '%Physical Address%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END) AS PhysicalAddress FROM@Stage AS s LEFT JOIN( SELECTMIN(RowID) AS minRowID FROM@Stage GROUP BYSection ) AS x ON x.minRowID = s.RowID WHEREs.Section > 0 GROUP BYs.Section E 12°55'05.25" N 56°04'39.16"
