I have a SQL2005 table that I'm needing to export to a 'formatted' csv flat-file to emulate a mainframe output. I'm new to SSIS so don't beat me up to bad... The current output of my ssis csv file is as follows:
Basically I need to pad the first 2 columns with trailing spaces to a fixed width of 11 characters, 3rd column convert number format and pad to fixed 5, 4th column pad to fixed 3, 5th column add double quotes around 20 character fixed. 6th column add double quotes around and pad to fixed 2. Finally adding a , 0 to each row which is used a LF/CR.
Any feedback in regards to getting pointed in the right direction would be greatly appreciated...
1. How can I Put all the Q documents from Microsoft that are related to SQL server? So that I can search fast.
2. How can I search SWYNK for all the ANSWERS that MAK/CRAIG/RAY replied to the threads. Its so weird that I answered someone and now I forgot the answer what I posted. Now I am having the same problem. I cant search.
3. I wanna put all SWYNK's Questions responses in one database say SQLSERVER, so that I can search all the threads.
I have been trying to rescue my company's helpdesk database because the company moved premesis and we lost that particular server.
It was originally running SQL Server 6.5, and my line manager simply copied the database.DAT files straight out of the MSSQLData directory without running a backup through Enterprise Manager.
Does anyone know how i can rescue these files so that we can get our helpdesk up and running again?!
I have a HUGE project (at least for me) and need some guidance.I am trying to create a database for a local university movie clubthat allows users to input there basic personal information (name,address, telephone number) as well as movies in there collection. Themovies will be categorized by genre (comedy, romance, horror, etc.)and title. I want to be able to let the users add and remove moviesto their list of movies they own, I'll call it "MOVIES I OWN".The user will also need to be able to create a SECOND list of moviesthey would like to see, again they can choose by genre and title.They need to also be able to add and remove from this list also, i'llcall it "MOVIES I WANT TO SEE".The last part of the project will be to match the users of "movies iwant to see" with "movies I own" users. It will be displayed on thelocal university website when the user logs in and will alert the userto the match. If there is a match at a later time, maybe the user canbe emailed? Also if there is a match, perhaps the two movies can betaken off record after the user acknowledges the match. I would needit to be able to handle a small amount of users now logged in at thesame time , but would like for it to eventually handle several hundredusers logged on at the same time in the distant future with outperformance problems.I am not sure if sql 2000 is the best to get this done or perhapsoracle. I am currently trying this on mySQL with PHP but currently amlost in a forest of data. Any guidance suggestions will be greatlyappreciated. I am pretty new to this so please be kind...
Hi Guys I'm new to this Forum and I downloaded SQL express advanced today, I'm more a network secuirty but I wanna save my company some money in create and quoting system and will work in the travel arena. However I'm not sure what querying program to use to pull quotes out and have options to email them as well. This would be the client side part, which records data.What program could I use to do this???
We've decided to use SSIS for an ETL type project. We are going to convert some files to our special flat file format. Our special flat file format will not change, but we will need to convert about 40 different style flat files into this format. We will receive any of these files at random intervals. We will likely have to add more file formats to the 40 at some point, too.
I'd like to use a flexible design, but I just started with SSIS and its going to be hard to test all the possible combinations, so I hope I can find some guidance in this forum.
What would be a good approach to allow for the most reuse of work to support these various file formats?
Hello Dear Forum! I am new to this web. I am now days working with .NET Tecs especailly ASP.NET. I have developed a small school system in which i used SQL MDF file. I want to know about its approach if they want to make it online. and one more question, is MDF file is best for more than 10,000 Students? Can i convert the MDF into normal table format like SQL Server 2005 , i am using SQL Server Express. Take Care.Wating for reply. Developer
I am the IS manager for a medium business. I am also the DBA and the programmer and help desk and trainer etc etc.
In 2003 I embarked on a project which went live in 2005. We contracted to have our order processing system rewritten into SQL. I went from a character based 4GL language called Progress running in a UNIX enviroment to a knock down, drag out, full blown microsoft solution. I have been operating this new system for two years making modest enhancements and improvements as my skills with microsoft are increasing and improving (retraining my brain to think objects)and hiring contractors to do the really cool fun stuff.
I have to learn Server 2003, SQL Server 2005, SQL Express, T-SQL, Replication, Reporting Services, SSIS, IIS, DTS, Visual Source Safe, Visual Studio, VB, C#, ADO.NET and the .NET Framework fits in here too although I don't really know where.
I hope you are starting to understand my feeling of being 'in over my head'. Right now everything is running perfect. I do backups, restore them and create new reports. Basic stuff.
On top of this our company has merged with a larger one and this bigger company does not have a microsoft solution. They have an AS400 home grown application that I do not want to learn. I will not go backwards!!!!! I will loose my job/position in the next couple of years. I want certifications (to get that interview).
I just don't have the skills to back up the certification (I don't want to be one of those exam cram people) I do a plethora of unrelated taks all day long. Not because I have to do them I choose to do them. I like both DBA and Programming.
So, if you were me, and could get certifications and have experience to back them up what would you do first?
im trying to convert a sql stored procedure to a ssis project as part of a learning experience, however, im not sure where to even begin. im looking to see if someone can help my find my footstep.
basically, the sproc looks through a table and pulls two fields into a cursor, there is a third field, the where clause is if say that field is greater than 1.
basically the first field it pulls is the name of another sproc to run, and the this field says if i want to execute that sproc or not. so i want to rewrite this sproc in ssis to go through the table, and execute each sproc name based off of the first table.
my initial ssis thought was to add a for each loop, but i couldnt figure out how to go through the table. anyone offer some help on how this could be done? thanks
I have a large (4 GB) xml file. I use the SSIS XML Source, generate an xsd and conversions/destinations and this does a great job of creating the multiple outputs including cross reference tables with autogenerated id's (I have no script task or component, so they must be auto). These load nicely into the six tables.
The guidance I seek is what should I do for the next xml source file as it will be an incremental data addition to the base file. I don't see any obvious way to keep the package from autogenerating id's that may conflict with the existing ids.
Am I missing the obvious? It may be, as I am enjoying SSIS, but I am not an expert at all. I can post pertinent items if needed.
What some good reasons to select 64-bit rather than 32-bit for SQL Server 2005?
We are preparing to install a very large SQL Server 2005 cluster. Initially, the primary database is estimated at 1 Terabyte, with 5 more databases that will add up to another Terabyte. After a few years, the estimate jumps up to 20 Terabytes for the largest DB and another 20 for the other five, combined.
We've made the assumption that a database this large, with up to 1000 concurrent users, that this is a good candidate for a 64-bit installation. And that we're better off starting out with 64-bit, rather than migrating later.
I am gonig into interview for a junior developer position. The role involves a lot of SQL based work. Training is on the job, and they know I am new to this, but they want to know what I can do with SQL server by wednesday, and obviosuly I stand a better chance if I can do a reasonable amount by then.I am assuming I can practise with offline databases, so I would like to do that. Also I was wondering if there were any simple example databases I can load up.
I have downloaded and installed the software, and would like to know how to connect and create a test database. There will be a small test in the interview and the questions are: 1. SQL Management and Data Extraction For this task you will need to be familiar with database tables, data types and constraints. There will be some administration work using SQL Management Studio along with some T-SQL queries. You will need to show use of the SELECT, INSERT, UPDATE and DELETE statements. If you do not have SQL Server, you can download the express edition for free at the following URL. http://msdn2.microsoft.com/en-us/express/bb410791.aspx
2. XSLT For this task I will be asking you to produce some HTML output displaying the data from an XML file. The concept is similar to ASP.
3. Database Design You will be given a scenario for a company that requires some database software for the smooth running of their organisation. You will need to plan and design a data structure to accommodate these requirements. You will be allowed to spend as much time on this part of the test as you wish. Key information here is going to be database normalization.
These questions dont make a whole lot of sense to be at the moment, so would appreciate a breakdown in simpler terms.
This job will be a fantastic opportunity for me to get into development, and would appreaciate any help that you guys have to offer, thanks in advance.
The ALTER TABLE statement conflicted with the CHECK constraint "CK__Consumers__Zipco__4316F928".
Based on this message, i would think that there is another constraint, however the build script does not have any constraints. So i am somewhat confused.
I know I already have an open thread and this is naughty, but just digging my other into a hole.
As stated in my other thread I am going into interview next week for a junior developer role, and they want to know as a test what I can find out and achieve with SSMS.
I would really appreachiate it if someone could give me some of their time to answer a few questions and guild me though the very basics. Either via MSN or Skype or something, sometime this weekend, as I really want this job on wednesday!
Is there any guidance on using Xp_Cmdshell from the SSIS team at Microsoft? We're needing to remotely execute SSIS packages on the server where MS SQL Server 2005 is installed.
FYI, I've researched this heavily and know about the common ways--re: Using SQL Agent or the Microsoft.Dts.Runtime library in .NET with a web service etc.
I'm learning by going through the tutorials and such and modifying the code to try and learn how to do more and different things.
I have a MSDE Database, and I'm building a query from the WebMatrix Code Builder.
I can get a Select and Where to work on a "Category" colum as String; to return from a text box control (where I enter the name) and on button click.
What I would like to learn how to do and am having trouble is do a select Where "Category" (string) and "Status"(Int) are the same.
I have two Seperate Textboxes after i make the Where / And Query, but I'm not sure i have the button click code right.
I have included the code below - Sorry if this is so basic ;-) - The Sub_Button1 Click is at the end.
Function ShowRecords(ByVal category As String, ByVal status As Integer) As System.Data.DataSet Dim connectionString As String = "server='(local)'; trusted_connection=true; database='SalesContacts'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT [Contact].* FROM [Contact] WHERE (([Contact].[Category] = @Category) AND ("& _ "[Contact].[Status] = @Status))" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection
Dim dbParam_category As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_category.ParameterName = "@Category" dbParam_category.Value = category dbParam_category.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_category) Dim dbParam_status As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_status.ParameterName = "@Status" dbParam_status.Value = status dbParam_status.DbType = System.Data.DbType.Int32 dbCommand.Parameters.Add(dbParam_status)
Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter dataAdapter.SelectCommand = dbCommand Dim dataSet As System.Data.DataSet = New System.Data.DataSet dataAdapter.Fill(dataSet)
Return dataSet End Function
Sub Button1_Click(sender As Object, e As EventArgs) DataGrid1.DataSource = ShowRecords(CStr(TextBox1.Text)&(CInt(TextBox2.Text))) DataGrid1.DataBind() End Sub
I'm new to SSIS (and quite new to SQL Server). I have a process which I'd like to automize via SSIS - just don't know how and couldn't figure it out yet by playing around with the program. Shouldn't be too difficult though.
First of all, that's the process as I do it now:
1) Load several flatfile sources (dumps of SQL tables) into an SQL database. 2) Add identifier rows (to some tables), set the primary and foreign keys so the database is "recreated" and I can work on it. 3) Do several simple transformations, aggregations and selects across tables and finally write a new table containing information for reporting stuff.
I succeded in loading flatfiles within the data flow view, doing some transformations and saving the output to a flatfile. What I didn't find out: how can I "recreate" the database enabling me to perform "SELECT/FROM/WHERE" statements across tables? Will I have to write the imported files to tables within a db (how?) or can I avoid this step?
A little guide (newbie friendly) would be great help!
Hi Can someone give me proper guidance .I am a data warehouse architect with 13 years in IT. What are the prospects if I move to data mining.Will it be wise decision to add data mining. Please guide.
I have been working SQL server for many years as a web developer, however in my current position we are lacking an expert in maintenance and admninistration as the company has recently switched over to SQL Server 2005.
I therefore need to develop a greater understanding of the best practices with regards to administration and am a bit lost as there are so many options which seem like they might match my requirements. As I have never looked beyond the basic administration/maintenance side of things before I have no idea which options to investigate.
Basically I want to clear any records older than 3 months old on a scheduled/automatic basis. There seem to be various options that could do this, however I want to know what the best option is, rather than waste a lot of time and risk problems by resorting to trial and error.
We are already backing up this data, however I am not sure if I should combine these tasks. It is highly unlikely that we will ever have to restore these older records, however we do need to store the data for legal reasons.
I dont expect anyone to tell me everything, as I am happy to investigate, I just need a nudge in the right direction.
I've been given the task of migrating a DTS package to SSIS (neither of which I am particularly familar with). The first job in the DTS package is to read a .ini file and set a bunch of variables. These variables are then used throughout the DTS package. After running the DTS package through the SSIS migration wizard this job turns into an execute script task and I can't see if it is still reading the .ini file. However, the only real purpose of this step is to allow different parameters to be passed in development, test, production etc. So I am thinking this whole step can be removed and effectively replaced with a package configuration (I'll probably use an XML file). My understanding is that by selecting the name/value pairs as appropriate in the XML package configuration file means this values will be passed in at runtime and achieve the same functionality. Is this the correct way to do this in SSIS, or do I still need the .ini file and variables?
My boss has a crazy idea that he want to implement using SQL 2005 and ASP.NET 2. Its something like this... If a user retrieve a record and someone is updating that same record a control on a asp.net page would warn that user that its being updated. Just like a stop light, green for go, yellow for warning, and red for stop. Were implementing optimistic here not pessimistic. Is this possible? I know pessimistic can lock that certain record. But what about in optimistic? Is it possible to monitor or know if a certain record or row in SQL 2005 is being access, updated, and deleted? How?
Hi,I have a table in which two fields(FirstID, SecondID) together make theprimary key for the table. Let's look at the following example:FirstID SecondID******* ********2 22 32 55 55 78 79 1011 12Here is what I am trying to do. For a given FirstID (say, 2) find allSecondID with this FirstID (2,3,5). Now for these second ID's is therea FirstID other than 2? (Yes, 5). I would say FirstID=2 is related toFirstID = 5. Extending the logic FirstID = 5 is related to FirstID = 8.I am trying to write a SQL (some kind of self join, I think) which willgive me all related FirstID for a given First ID. For First ID = 2 theSQL will give me 5 and 8 in the above example. For First ID = 9 the SQLwill give me 11.Any help will be greatly appreciated. Thanks!!-Raj
Hi guys,we has accidently restored our Database with a back up file created months ago. we have no back up for the huge data and Stored procedures ( I know it is very stupid :( and i m sad abt it all ), guys is there any way by which i can get restore my db back to what it was before restoration.the flks in team have also deleted(shift + delete) the transaction log file ( I know it is very stupid again :( and i m very sad abt it all )..please adviceThanx in advance ashish
We need to return the results of a query as a single string.For example if the query returns 10 rows,all the 10 rows should be concatenated and put it in an output @returnstring. Is that possible in SQL stored procedures ?? Thanks!
There is no inherent mechanism available in SQL Server (replication, log shipping, or clustering) which allows you to load balance you database server. Clustering is only useful for a failover situation and does not allow active/active balancing.
Is is possible to use merge replication between two identical OLTP servers and manage transactions via MSMQ? Will this mechanism allow for a load balanced OLTP server?
Will this work? If not, why not? What will work? Will federated servers work for an entire database??
I need to determine the differences between two tables with the same structure. The primary key for these tables would be a combination of all the columnhs. They use the ID field to join all their records. (i know ID is a reserved word - the client made the tables) Below is the structure of the tables.
TableA and TableB ----------
ID int ComBank int inboth int EqorMA int TranType int
I need to know the rows that Are different in TableA from TableB.
I'm trying to insert a MSWord/Bitmap/Powerpoint file into a table in SQL 2000. Unfortunately, I haven't had the luck to get it work. Here is my table structure :
i realize i am starting to ask a lot of questions here so, thanks to all who tolerate me.
In my previous post, i figured out (with the help of r937) how to create a non-identity column using a cursor. I now have some resulting questions.
1. where do cursors live? there is no folder like triggers or stored procedures. is a cursor its own file or is it embedded in a stored procedure?
2. how do i get my cursor to "act" upon my stored procedure? I want the values in my cursor's "relativeposition" column to replace the values in my stored procedure's photo_number column. how do i do this?
The Scenario:We have some data that can be in three states. One is a saved state,a temporary state, and a complete state.The complete state is the bulk of the data. This data will berequested 1% of the time, maybe even less.The saved state and temporary state will only number 10-15 records.This data will be used 99% of the time, very frequently.So to summarize there will be 10-15 records that will be used ALL thetime…. And 50,000-100,000 that will be used very infrequently.The programmer that I work with wants to combine the tables. I wantto keep the tables sepperate.My logic is that it will be much faster to work with 10-15 recordsthan sift threw 50-100k most of which are unnecessary to 99% of therequests. Also there are frequent deletions/modifications in thetemporary table(10-15 records). The complete table can NOT havemodifications or deletions (50-100k records). The optimization forthese tables is also very different.He says that since the structures are almost identical they should bein the same table, that we should just use a field to denote thedifferent types of records. He insists that my view is wrong and thatI know nothing about databases.I may be new to SQL, but am I really that out to lunch? Would it notmake sense to keep these tables separate?(Thank you for taking the time to read this)Sat
