Hello All,
I am migrating data from one database to another. I am using Multicast to seperate (legal street,legal mail and legal city) and (mail_street,mail_state,mail_zip,mail_city) also later after UNION of the above I am doing two lookups as I had to get contact ID and Customer ID from other two tables. In UNION i am matching (Mail street legal street) and so on.
I am getting double the data in the output. my input data is 1000000 and im gettin 2000000.
Hello everyone, I am new to mssql so please excuse all my silly questions.
I can create databases and tables. This is fairly easy, now I am having trouble with inputting data into the database using the data entry browser (or whatever it is called to enter data directly into a table).
I usually get an error when trying to submit the entry, firstly I have a "id" field which I think I have set to auto increase (Identity is ticked, seed and increment is set to 1).
when I go to add data all fields have "Null" in them and from what I understand it allows the fields to be empty, except the primary key which is the id field in this case and I expect that to be filled in automatically like with mysql but isn’t.
Can some kind person instruct or direct me somewhere on how to enter data into a table without it failing.
I've exposed my data (that exists in a proprietary format) with the ADO.NET provider interfaces (IDbConnection, IDataReader, IDbDataAdapter and IDbCommand). I can't seem to find any examples of how to get Integrated Services to hookup to this .NET code in my class library. Is it possible? My goal is for this provider to be both a destination and a source and for others to be able use IS to manipulate the data however they want.
Each day I receive a file with a different name. For example, the name is filename_mmddyyyy.txt where filename_ stays constant and mmddyyyy is the date of the file. The file is always in the same format.
I want to build an SSIS where I pass it this file name. I can write a script to generate the correct file name. How do I build the SSIS so it can accept the input parameter and find the correct file to process?
The following is a list of questions that I have not been able to obtain concrete answers. I am probably missing something: 1) ReadWriteVariables -- can the updated value for a ReadWriteVariable be accessed within the same data flow? It appears not as I think the PostExecute() fires at the completion of the data flow not the end of the Script Component. Secondarily, the Script Component is a non-blocking transformation so the component does not "see" the end of the pipeline prior to sending data down stream.
2) Record Count -- Because of #1 above, How could you calculate a record count for a data stream? It does not appear that one can calculate the number of records for a data stream within a data flow and then access the count from within the same data flow.
3) FinishOutputs() -- Is the concept of FinishOutputs() applicable to Script Component Destinations? Asked another way, is FinishOutputs() executed at the end of the data stream regardless of whether there are "real" outputs for the component? I can create a "Dummy" output to create FinishOutputs() but is this ok?
4) Script Component -- It appears that the Script Component Source, Transformation or Destination are really defined based on the columns defined in "Inputs and Outputs". Can you convert an Source script component to a transformation script component by simply adding an Output?
Sorry for these basic questions but I am not getting it completely. As you can tell...
I have a delimited text file with 650+ columns. The sum of the column lengths of a single row, if fully populated, exceeds 30K bytes. The "killer" fields lengthwise are the "Description" fields. If they were removed from the input file, the remainig columns would occupy about 5000 bytes, which is within SQL max row length.Â
Can SSIS be used to created these two tables? (one without  description fields, the other with those field but arranged vertically in the table rows).
The fundamental issue is I can not import a single file row into a sql table because that row length could exceed the max byte count for a row.
I'm new to SSIS. I'm looking for basic pointers to return temp table created by a proc to be written to file shares. Any pointers/examples appreciated.
Where interid in ('comp1', 'comp2', 'comp4', 'comp5')
what would be the best way to using these scripts pull the data to my testDW and not have duplicate data issues?
I was thinking of using a staging DB on the GP cluster and then building an import data package to run nightly. the issue i had was how do i avoid duplicate data ?
I have a .net web application and need to create a program that needs to check the database every two hours and if data has not been input send the user an email message. I have heard this can be done via SQL Server - events or triggers? Can anyone tell me where I can find example code or more information on this. Thank you in advance.
Hi, I have one data driven task set up in my DTS package. for some reason the connection to data source is not available when my package is scheduled to run,which fails the package. is there a way, I can set the task to try to execute again after a couple of minutes or 5 mins, maybe a predefined number of times before it fails the package? Thanks & Regards Naseem
I try to compile your query, and prompt error message in below 'conversion failed when converting the varchar value 'times' to data type int .Error code 245.
select user_member , sum(case when trx_date > dateadd(month, -3, getdate()) then 1 else 0 end) + ' times' , max(case when trx_date < dateadd(month, -3, getdate()) then trx_date else NULL end) from trx_hdr group by user_member
I am cleaning database backup history as "Delete history if 4 months old" for backup file, and "Delete history if 1 week old" for Log file back.
lets say, I have disaster after 3 months, and I recover database with data and log backup files. Will I be able to recover with 3 month old backup file with just 1 week Log file backup. As I am keeing my Log backups only for 1 week.
In Visual Web Developer I have created a data input form based on the documentation I found in the .NET Framework Class Library (SqlDataSource.InsertCommand Property). Originally my form contained 4 textboxes (FirstName, LastName, Phone, Email) and worked fine. All this data is nvarchar string data.
When I added a checkbox I get an error that “String was not recognized as a valid Boolean� when the checkbox is checked. When the checkbox is unchecked the data is input without a problem. This is a bit data field in the table.
Here is my VB code, Insert Parameters for my SqlDataSource1, and button code:
<script runat="server">
Private Sub InsertData(ByVal Source As Object, ByVal e As EventArgs)
I suspect that the problem is with the VB code which will only accept string input data, but don’t know how to fix it. Any thoughts on how to fix this problem? Thanks in advance for any help provided.
Hi everyone... sorry, but I'm really new at this! I'm trying to make a table out of an imported Excel file (which is automatically generated as an all-text data type). I wrote a query that takes this excel file and puts it into the format we need (i.e., appropriate column headers, removing "garbage" characters and spaces). Is there any way to format the data going into the table by a specified datatype?
Sorry if this is simplistic or wasting your time, but I'm really stumped :o. However, keep in mind that I started learning SQL this morning and I've already gotten this far :).
Hi there. This is only my second post ever- so please bear with me.
First, I am using SQL Server 2000.
I have built a DTS package using an Oracle ODBC and the following query;
select pv.segment1 vendor_nbr, pv.vendor_name,pv.attribute4 , pv.creation_date from po.po_vendors pv , po.po_vendor_sites_all pvs where pv.vendor_id = pvs.vendor_id(+) operating unit AND trunc(pv.creation_date) = trunc(sysdate)
This DTS package is not always run on the same day the records are created. So......
What I want to do is have the dts package ask the user for the date they are looking for-
I know I need to probably use a parameter, but I have been unable to figure out how to do this in a DTS pkg. I have several reSQL reference books and I don't even know what to search for in Books online. Could anyone help??
I'm trying to quantify the number of times folks use SQL Server Management Studio to change client data in one of our production databases. Does SQL Server keep this statistic? How do I get to this data?
I am just starting out with MSSQL, but have previous experience with MySQL and PGSQL. What I'm trying to do is do a nightly dump of data from a proprietary DB into MSSQL. Access to the prop DB is through an ODBC driver.
I already have a DTS script that will dump a specific table, but every time it runs it just appends the data to the end of the table.
What I am looking for is a way to download a list of tables, upload them into the MSSQL DB, and if something failed, rollback to the state before the data transfer and somehow alert of the fact.
Hi. I just set up my first sql server database and I've managed to connect to it via ASP as a test. I'm not sure how to add data to my tables. In MS Access, you can edit the table and add records. How do I do that in SQL Server? I'm using the Enterprise manager tool to create tables.... does it have something i can use?
It was suggested to me to try and post this here in the sql express forum.
I am trying to query a specific field from my sse database based on two different variables. And use this information to fill a textbox with an integer.
The first variable is a combobox that is filled with data in one of the other tables in the sse database. This table has a PKcolumn that is an integer and the identity of the column. The only other column is a nvarchar(30) string (which is the actual data in the combobox)
The second variable is a numeric up down that isn't actualy using info from the database but will be a reference. It has a max value written in to the code:
if NumericUpDown1.Value > 20 Then
MessageBox.Show("This form only supports characters up to level 20")
NumericUpDown1.Value = 20
The texbox value will be found in a table that has several columns, the first column is an identity column (also the PK) then it has a column that holds an integer value (the filter criteria for the numeric up down) the next column has the value that will fill the text box, then several other columns that are irrelevant for this, and then the final column is a foreign key that is linked to the primary key of information found in the table that feeds the combo box.
I will post a picture of the database diagram to show all of this information for a better understanding of this post.
I am not looking for someone to write the code for me, but I am totally stumped as to what I need to do, If someone can help me out with this I would greatly appreciate it, thanks in advanced.
http://static.flickr.com/75/154867997_a55d3b0b9e.jpg - this is the table diagram
I am able to do these actions interactively from SQL 2005 (not developers nor enterprise edition, just using SQL 2005 Mgmt Studio) and want to "script/batch" them so I can have them automatically run at a pre selected time.
First: I am able to delete the table by performing a right click on the table, then click Delete from Mgmt Studio SQL 2005. I verify the table is completely gone with a refresh. (I pulled the code that did this ..... DROP TABLE etc. to Notepad)
2nd: I am able to import the table (again from Mgmt Studio SQL 2005) and have saved this action as a SSIS. Execute the script and "waLa" I have all 17K rows of data. I pulled this create table code into notepad also.
Now I put the code of both of the above actions together (drop table and create table) into one SQL query and execute it. This does not give me the same results of above, instead my table is blank now.
Maybe there is a better way. The business problem I am attempting to solve: I am refreshing the data in a as/400 table weekly. I want that refreshed data to be available in the SQL2005 database without my having to press buttons first thing Monday morning. Can any one help? Thanks in advance.
Below is the Code:
USE [400kas] GO /****** Object: Table [dbo].[navar100] Script Date: 09/07/2007 16:09:04 ******/ DROP TABLE [dbo].[navar100] GO
USE [400kas] GO /****** Object: Table [dbo].[Query] Script Date: 09/07/2007 16:12:31 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[navar100]( [CMPNO] [decimal](3, 0) NOT NULL, [ARTDT] [datetime] NOT NULL, [AUDDT] [datetime] NOT NULL, [ARDDT] [datetime] NOT NULL, [CCUS#] [decimal](6, 0) NOT NULL, [CCNAM] [nvarchar](25) NOT NULL, [CUSNO] [decimal](6, 0) NOT NULL, [CNAME] [nvarchar](25) NOT NULL, [SHPNO] [decimal](4, 0) NOT NULL, [ARRCD] [nvarchar](1) NOT NULL, [AUDUS] [nvarchar](10) NOT NULL, [INVNO] [decimal](6, 0) NOT NULL, [CUSPO] [nvarchar](15) NOT NULL, [REFNO] [decimal](6, 0) NOT NULL, [COMNT] [nvarchar](10) NOT NULL, [SHPPO] [nvarchar](15) NOT NULL, [AMONT] [decimal](13, 2) NOT NULL, [AMOUNT] [decimal](24, 8) NOT NULL, [REMAN] [decimal](13, 2) NOT NULL, [INREG] [decimal](3, 0) NOT NULL, [INSAL] [decimal](3, 0) NOT NULL, [TMCOD] [nvarchar](2) NOT NULL, [CRHLD] [nvarchar](1) NOT NULL, [CRLIM] [decimal](13, 0) NOT NULL, [CRDAY] [decimal](3, 0) NOT NULL, [TCRCD] [nvarchar](3) NOT NULL, [TEXRT] [decimal](11, 6) NOT NULL, [R1RGL] [decimal](13, 2) NOT NULL, [TAXAM] [decimal](13, 2) NOT NULL, [TFRTX] [decimal](13, 3) NOT NULL, [TFRGT] [decimal](13, 2) NOT NULL, [TSPCH] [decimal](13, 2) NOT NULL, [SPCST] [decimal](13, 2) NOT NULL, [IRPFT] [decimal](13, 2) NOT NULL ) ON [PRIMARY]
Hi there. i have the following excel spreadsheet that needs to be moved into a database:
ADDRESS STREET SURNAME GIVEN PHONE CITY POSTAL NOTES TERR
40 Goodless Crt You M (416) 123-1234 SC M1B 1A1
SC-123-12
59 Bellevue Ave Doe Jon (416) 123-1234 SC M1B 1A3
SC-123-13
Most of these fields are going to be created as reference tables... and the main contact list table will only include a fk to the actual value. eg) the contact table for the above will look like:
ADDRESS STREET SURNAME GIVEN PHONE CITY POSTAL NOTES TERR
40 1 You M (416) 123-1234 1 M1B 1A1
5
59 2 Doe Jon (416) 123-1234 1 M1B 1A3
6
My question is, what is the best way to move all this data into SQL Server Express? I've been playing around with the LinkedServers section in sql and have managed to create a connection to my excel workbook. I am able to select all worksheets as tables... and i've also been able to select all records from the main contacts sheet. not knowing any better, my initial thoughts are to create separate sql statements for each reference table. eg) select street from excellink...contact$ and then combine it somehow with an insert statement into the streets table in my database. Is this the right approach? if it is, can you help with the syntax of the sql statement? Please and thanks.
hi there.. I have write a code in the submit button using vb code so that when people key in their email.. it will be saved in my created database called test.mdf. but when i debug it, i tried to write an my email address in the textbox in my website but when i click at the submit button, the web page display the email was not enter in my database. here is the code, can anybody help me. i'm really lost here.. Protected Sub SubmitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim TestDataSource As New SqlDataSource()TestDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("TestConnectionString1").ToString()
TestDataSource.InsertCommandType = SqlDataSourceCommandType.Text TestDataSource.InsertCommand = "INSERT INTO Email(EmailAddress, IPAddress, DateTimeStamp) VALUES (@EmailAddress,@IPAddress,@DateTimeStamp)" TestDataSource.InsertParameters.Add("EmailAddress", emailAddressTextBox.Text) TestDataSource.InsertParameters.Add("IPAddress", Request.UserHostAddress.ToString())TestDataSource.InsertParameters.Add("DateTimeStamp", DateTime.Now()) Dim rowsAffected As Integer = 0
Try rowsAffected = TestDataSource.Insert() Catch ex As Exception Server.Transfer("test_problem.aspx")
Finally
TestDataSource = Nothing
End Try
If rowsAffected <> 1 ThenServer.Transfer("test_problem.aspx") ElseServer.Transfer("Test_Confirm.aspx")
hi all , does SQL server able to input the date that is originally from 2/6/2004 11:07:46 AM to DD-MM-YY 06-02-04 and store in the DB. For example , this is the SQL insert query
INSERT INTO ADMIN ( ID , NAME , DATE ) VALUES ( 'A001' , 'karen' ,'2/6/2004 11:07:46 AM')
the date will automatically converted to 06-02-04 and store in DB .
Does it need to use SQL Rule or user-defined data type.
hi , i am new to this. how should i update the user input values into sql server database? i am using asp.net and c# FIELDS are- userId, name, description, startTime, endTime, audiencePassword, presenterPassword
i know it must be simple...but i haven't worked on this before.
This is very similar to the post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1901076&SiteID=1
I am trying to do this. Basically, if the field I am checking contains a space - not an empty string - I want to assign it an empty string so it will get converted to NULL when it hits the SQL destination. The space character from the source is causing the rows to fail I want to force a NULLin the database as a "workaround" from something I am working on.
I thought I had this set up correctly, but it is not working. I have the field in question as an input field set to ReadWrite
If Row.[COLNAME].ToString = " " Then Row.[COLNAME] = "" End If
I am getting the following error:
[Assign District Code [12469]] Error: System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)
Can someone help me to understand something about SQL Server? I will use Access for comparison.
Suppose you have a SQL Server database and you want users to be able to enter data into the fields just like you could with an Access database and forms. How would you set that up with SQL Server? I have noticed that you can convert an Access database into SQL Server, but you still need to use Access forms to enter data into the tables stored in SQL Server.
What if you wanted to create an SQL Server database from the ground up and set it up for user entry?
