What would be the most efficient method of constructing a sql statement to retrieve certain record into (this gets tricky) rows of a table with 3 columns date.desc.
We have a SSIS project where we load lot of image files using an OLE DB Source component and put it across the database as varbinary into OLEDB Destination component.
Things were fine until now where am getting an error like this. alongside the log also reads like,
There is not enough space on the disk. Long data was retrieved and can't be added to the data flow task buffer.
Is this method of loading the files using an OleDb Souce not the efficient one ? If not is there a way I can get this done. Comments are highly appreciated!!
I am trying to set up a data flow task. The source is "SQL Command" which is a stored procedure. The proc has a few temp tables that it outputs the final resultset from. When I hit preview in the ole db source editor, I see the right output. When I select the "Columns" tab on the right, the "Available External Column List" is empty. Why don't the column names appear? What is the work around to get the column mappings to work b/w source and destination in this scenario.
In DTS previously, you could "fool" the package by first compiling the stored procedure with hardcoded column names and dummy values, creating and saving the package and finally changing the procedure back to the actual output. As long as the columns remained the same, all would work. Thats not working for me in SSIS.
I want to retrieve SQL 2000 Encrypted Column Data From SQL 2005 strored proc. My Stored Procedure was on SQL 2000 and it works fine....Then I restore Database From SQL 2000 to SQL 2005. The Following Statement is on my store proce.
select user_id , Encrypt(user_pass) from OpenRowset('SQLOLEDB','myserver';'sa';'mypass',databasename.dbo.users) as a
The Following Error I get When I execute the above statement.
Msg 195, Level 15, State 10, Line 1
'Encrypt' is not a recognized built-in function name.
I am having a big problem, trying to create datebase. I have 3 tables: SUPERAVATAR, MASTERAVATAR, MEGAAVATAR. - SuperAvatars are heroes in an online role playing gaming. They have an ID, ‘superavatarID’ which contain an UNIQUE NUMBER NOT NULL PRIMARY KEY to identify them. A wisdom – ‘trickster’,’conjuror’,’magician’, etc.. A current owner… who is the user or player of the game and has that Super Avatar– we associate the link to USERID to know the person. SuperAvatars can be fathers or mothers of Mega Avatars. -MegaAvatars are the children of SuperAvatars…. They also have an ID UNIQUE NUMBER NOT NULL PRIMARY KEY to indentify them. A magic power – it can be a ‘Leader’ or ‘Sheep’… A ‘parent’ – parent is the number identifying to know to who Super Avatar belongs. e.g. SUPERAVAT WISD CURRENTOWNER FATHEROF MOTHEROF 1 Thick 3 1 2 Mentally Ch. 11 3 3 Smart 9 2 4 Genius 16 4 5 Thick 19
-We see that MEGAAVATAR 1 has a magic power as Magician and his father is ‘1’. ‘1’ identifies the SUPERAVATAR. We see SUPERAVATARID…. who has the number ‘1’? the first in the row… who has wisdom – thick and he belongs to the USER with ID number 3. -We see MEGAAVATARID… we choose the number 2…. His magic power is as WIZARD… and his father is the number 3. We see SUPERAVATARID now… we look up the SuperavatarID 3…. We can see he has a wisdom – GENIUS… who belongs to the USERID 16 and he is father of MEGAAVATAR number 2. The list can carry on and never stop.
I have this Problems: We create in this example 3 tables: Users, SuperAvatar, MegaAvatar
SQL CREATE TABLE users(userID NUMBER CONSTRAINT pk_user PRIMARY KEY,email VARCHAR2(50) NOT NULL UNIQUE,password VARCHAR2(15) NOT NULL UNIQUE,subscription CHAR(8) NOT NULL CHECK (subscription IN('ACTIVE' , 'INACTIVE' ) ) );
CREATE TABLE superavatar(superavatarID NUMBER CONSTRAINT pk_superavatar PRIMARY KEY, wisdom VARCHAR2(19) NOT NULL CHECK (wisdom IN ('THICK', 'MENTALLY CHALLENGED', 'AWAKE', 'SMART', 'GENIUS')), currentOwner NUMBER NOT NULL, fatherOf NUMBER,motherOf NUMBER);
CREATE TABLE megaavatar (megaavatarID NUMBER CONSTRAINT pk_megaavatar PRIMARY KEY, magicPower VARCHAR2(12) NOT NULL CHECK (magicPower IN('TRICKSTER','CONJUROR','MAGICIAN','WIZARD','SORCERER')), parent NUMBER);
Now, the 3 tables are created….. What happen when we try to insert values to this table? In this case we insert to User Table some examples: INSERT INTO users VALUES('3','john@hotmail.com','great78','ACTIVE'); INSERT INTO users VALUES('9','chrisandsandra@gmail.com','chrisandra)','ACTIVE'); Now, we insert to SuperAvatar some examples; INSERT INTO superavatar VALUES('1','THICK','3','1',''); INSERT INTO superavatar VALUES('3','SMART','9','3',''); | | ‘9’ is the UserID that we already insert to the User table What’s the problem? We have to insert manually the data from USERID to CURRENTOWNER as we didn’t match or link CURRENTOWNER from SUPERAVATAR Table to USERID from USER Table with a SQL CODE. What happen if we have thousands of USERS that they register to this game…? We will never know to how belongs that SUPERAVATAR but if someone do it manually can spend a year.
I am trying to fix this problem …. I add in SUPERAVATAR TABLE ‘CHECK’ in currentOwner..
SQL> CREATE TABLE superavatar (superavatarID NUMBER CONSTRAINT pk_superavatar PRIMARY KEY, wisdom VARCHAR2(19) NOT NULL CHECK (wisdom IN ('THICK', 'MENTALLY CHALLENGED', 'AWAKE', 'SMART', 'GENIUS')), currentOwner NUMBER NOT NULL CHECK (currentOwner IN(SELECT userID FROM users)), fatherOf NUMBER, motherOf NUMBER); * ERROR: ORA-02251: subquery not allowed here
Hi, Can any one help me to retrieve an image data from a table. I wanted to show the images on my web page using ASP. I am trying with pub_info table of pubs database, in this table logo is a image data type colum. I opened a record set in ASP page and I am not able to retrieve the image from the record set.
I have 4 tables I am pulling data from. Table A and B can be joined and Table C and D can be joined via foreign keys but A and/or B can not be Joined to C and/or D via foreign Key.
My statement basically looks like this to start
SELECT s.HTENANT, s.HPROP, s.HUNIT, s.SHEADNAME3B, s.DTOT7I FROM H8summ S join tenant t on s.htenant = t.hmyperson
And returns this :
29226 128 20577 Almukhtar, Khadijah M 4242.00 26574 128 17980 Archie, Mary 20218.00
The last number is the key it is the income.
In another table we have an income limit table setup that would have the low, medium and high settings for the range.
For instance it may have 11400 as the elimit attribute and say 22000 as the vlimit attribute.
Then in the fourth table we the description that can be linked back to the income limit table via the hinclimit attribute. In this case the desc attribute would be ELI for elimit and VLI for vlimit.
So I want to take the income compare it to the income limit table and then return the description
The output would look like this : 29226 128 20577 Almukhtar, Khadijah M 4242.00 eli 26574 128 17980 Archie, Mary 20218.00 vli
I just not sure how to reference the income limit table without the foreign key in the from statement. When I just add it it returns 6 rows for each person (number of rows on the income limit table)
Can we retrieve data from table type variable as an array in oracle key values..
E.g.
ALTER PROCEDURE prn1( @p_prn as KeyValuePair readonly) -- proc which having parameter table type as parameter AS declare @v_formatted_str varchar(50) BEGIN set @v_formatted_str = v_formatted_str + @p_prn(1)(1) END
Hi, I am working on inserting information into a DB and then retrieving the ID created for that Data to use elsewhere in my code. I have the code below but I do not know how to get toOutput parameter. Can anyone please help?
AS INSERT INTO PRODUCTION (DATEOUT,DATEREQUIRED, PREPAREDBY, COMMENTID, TOTALQUANTITY, VENDORID, WPO, TCAPONUMBER, APPROVEDBY) VALUES( @DATEOUT, @DATEREQUIRED, @PREPAREDBY, @COMMENTID, @TOTALQUANTITY, @VENDORID, @WPO, @TCAPONUMBER, @APPROVEDBY) SET @Identity = SCOPE_IDENTITY()
'collect all the information from the form and then apply all and then update 'Get a reference to the Production table. Dim dtProduction As DataTable = DS.Tables("Production") Dim dtLineItem As DataTable = DS.Tables("LineItems") ' Create the SqlCommand to execute the stored procedure. Production.InsertCommand = New SqlCommand("dbo.InsertProduction", connection) Production.InsertCommand.CommandType = CommandType.StoredProcedure ' Add the parameter for the CategoryName. Specifying the ' ParameterDirection for an input parameter is not required. 'Production.InsertCommand.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15, "CategoryName") Production.InsertCommand.Parameters.Add("@DATEOUT", SqlDbType.DateTime, 8, "CategoryName") Production.InsertCommand.Parameters.Add("@DATEREQUIRED", SqlDbType.DateTime, 8, "CategoryName") Production.InsertCommand.Parameters.Add("@PREPAREDBY", SqlDbType.VarChar, 50, "CategoryName") Production.InsertCommand.Parameters.Add("@COMMENTID", SqlDbType.Int, 4, "CategoryName") Production.InsertCommand.Parameters.Add("@TOTALQUANTITY", SqlDbType.Int, 4, "CategoryName") Production.InsertCommand.Parameters.Add("@VENDORID", SqlDbType.Int, 4, "CategoryName") Production.InsertCommand.Parameters.Add("@WPO", SqlDbType.VarChar, 50, "CategoryName") Production.InsertCommand.Parameters.Add("@TCAPONUMBER", SqlDbType.Int, 4, "CategoryName") Production.InsertCommand.Parameters.Add("@APPROVEDBY", SqlDbType.VarChar, 50, "CategoryName") ' Add the SqlParameter to retrieve the new identity value. ' Specify the ParameterDirection as Output. Dim parameter As SqlParameter = Production.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "ProductionID") parameter.Direction = ParameterDirection.Output ' Create a new row with the same schema. Dim dr As DataRow = dtProduction.NewRow() 'you need the ID from this to insert into the Production DB ' Set the value of all the columns. dr("DateOut") = CDate(DateTimePicker1.Text) dr("DateRequired") = CDate(DateTimePicker2.Text) dr("VendorID") = CInt(vendorbox.SelectedValue) dr("HomeAddress") = txtApproved.Text.ToString dr("ApprovedBy") = txtPrepared.Text.ToString dr("TCAPO") = CInt(txtTCAPO.Text.Trim) dr("CommentID") = CommentID dr("TotalCost") = CDec(txtTotals.Text) dr("TotalQuantity") = CInt(txtQtyTotal.Text) ' Add to the Rows collection or table . dtProduction.Rows.Add(dr) 'Update the Production Table and then retrieve the ID created in this case Production.Update(dtProduction)
I want to get the first 2 columnns from the "dbo.Samples" table and the 3rd, 4th, 5th, 6th, and 7th columnns from the "dbo.TestResults" table. I think I can create a new "dbo.Report" table with the 7 Columnn Names, and retrieve the data of SampleID and SampleName from the "dbo.Samples" table and put them into the "dbo.Report" table. But I do not know how to retrieve the data of Unit, Acetone, Benzene, Trichloroethene, and Xylenes from the "dbo.TestResults" table and put them into the "dob.Report" table formatted and presented in the above blue-colored table. The following is my T-SQL programming plan: -- Create a new table called "Report" CREATE TABLE Report ( SampleID int, SampleName nvarchar(25), Unit nvarchar(25), Acetone decimal(9,3), Benzene decimal(9,3), Trichloroethene(9,3), Xylenes decimal(9,3), );
-- Insert the data of SampleID and SampleName from dbo.Samples -- SELECT SampleID, SampleName FROM dbo.Samples
-- Insert the data of Unit, Acetone, Benzene, Trichloroethene, and Xylenes from dbo.TestResults -- I guess I may be able to use the following code: INSERT INTO dbo.Reort(Unit, Acetone, Benzene, Trichloroethene, Xylenes) SELECT x???? FROM y???? WHERE z???? (SELECT xxx?? FROM yyy?? WHILE zzz='##??") GO
I do not know how I can get and use the above planned "subquery" correctly to get the data I want!!?? Please help and give me a delaied instructions or direction to achieve this task.
I am using C# in Visual Studio 2008 and remote database as sql server 2008 R2. I want to read remote database table's field value and i have to move that read value to string variable. how to do it.
And my code is :
string sql = "Select fldinput from tmessage_temp where fldTo=IDENT_CURRENT('tmessage_temp')"; SqlCommand exesql = new SqlCommand(sql, cn); exesql.CommandType = CommandType.Text; SqlDataReader rd1 = default(SqlDataReader); rd1 = exesql.ExecuteReader();
Hi all, I am not over familiar with SQL, I am a VB programmer, simply I need to achieve the following within Enterprise Manager.
I have 2 tables, different designs, different number of rows, I simply need to check whether the contents of a column in the first table is in a column in the second table, just simply a table/column to table/column data check for the same data content.
Easy Peasy for you guys, any help would be appreciated.
If on the source I have a new column, the script generated by SqlPackage.exe recreates the table on the background with moving the data into a temp storage. If the table is big, such approach can cause issues.
Example of the script is below: in the source project I added columns [MyColumn_LINE_1] and [MyColumn_LINE_5].
Is there any way I can make it generating an alter statement instead?
BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET XACT_ABORT ON; CREATE TABLE [dbo].[tmp_ms_xx_MyTable] ( [MyColumn_TYPE_CODE] CHAR (3) NOT NULL,
[Code] ....
The same script is generated regardless the table having data or not, having a clustered or nonclustered PK.
I have a requirement of table partitioning. we have 10 years of data on a table which is 30 billion up rows on 2005 server we are upgrading it to 2014. we have to keep 7 years of data. there is no keys on table or date column. since its a huge amount of data and many users its slow down the process speed. we are thinking to do partition on 7 years for Quarterly based. but as i said there is no date column on table we have to use reference table to get date. is there a way i can do the partitioning with out adding date column on table? also does partition will make query faster?
I have think three ways to do it. 1. leave as it is. 2. 7 years partition on one server 3. 3 years partition on server1 and 4 years partition on server2 (for 4 years is snapshot better?)
i have a table that has 9 columns that belongs to other tables. i need to retrieve one column from this table. In order to do this, do i have to join all the other tables that has similar columns?
I am studying indexes and keys. I have a table that has a fixed width of data to be loaded in the first column which is parsed in a view based on data types within the fixed width specifications.
Example column A: (name phone house cost of house,zipcodecountystatecountry) -a view will later split this large varchar string based column b: is the source filename of the data load (varchar 256) ....
a. would there be a benefit of adding a clustered or nonclustered index (if so which/point in direction on why)
b. is there benefit of making one of these two columns a primary key (millions of records) or for adding a 3rd new column as a pk?
c. view: this parses the data in column a so it ends up looking more like "name phone house cost of house zipcode county state country" each having their own column.
-any pros/cons of adding indexes (if so which) to the view instead of the tables or both for once the data is parsed?
I am trying to create a table that holds info about a user; with the usual columns for firstName, lastName, etc.... no problem creating the table or it's columns, but how can I "restrict" the values of my State column in the 'users' table so that it only accepts values from the 'states' table?
Name x y z z1 A 1 2 3 3 B 0 2 0 3 C 1 0 3 3 if i pass parameter as @param=1, only column of x should be displayed, if i send parameter as @param=2 only y column should be displayed, if @param=3 is parameter then Z column And Z1 column should be displayed. plz help to create sql query.
Hi, I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time. thanks. varun
Hi, all.. I want to know the query to retrieve Column information that we can see from table Design view of Enterprise manager, such as Column name, Pk or not, FK table, Data Type, Null or not, Description(Specially Descrition).
I have search for and see how to put the columns for the last, first mi together (Name_Last + ', ' + Name_First + ' ' + Name_MI) as Name But how can I test the value of the Name_Type field to determine how the Name column looks if Name_Type = 1 then Name = (Name_Last + ', ' + Name_First + ' ' + Name_MI) else Name = Name_Last
The Name_Type represents Individual versus an Entity 0,ABC Pipeline,, 1,Williams, John,A
I am trying to retrieve all rows with the largest value in a particular column. The largest value could return many rows for a particular users. Here is what I have thus far.
SELECT DISTINCT ID, NAME, FOP, ACCT, CTNUM, ENDDATE, DEBIT, CREDIT, TRANSACTION_DATE, EXPORTED, CALENDAR_YEAR, FISCAL_YEAR, PAYROLL_IDENTIFIER, PAYROLL_NUMBER, [EARN-SEQNO], EVENT_SEQUENCE_NUMBER FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY ID, ACCT, PAYROLL_NUMBER,EVENT_SEQUENCE_NUMBER ORDER BY EVENT_SEQUENCE_NUMBER DESC) AS RN FROM PAYROLLYEAREND ) s WHERE RN = 1 AND ID = '16443' AND PAYROLL_NUMBER ='7'
In the above example, the EVENT_SEQUENCE_NUMBER is populated with values from 0 to 12. Could vary per user and PAYROLL_NUMBER. The query above returns 48 rows. However, all I want are the rows where EVENT_SEQUENCE_NUMBER is equal to the highest, which is in the above example is 12. The result would be 29 rows. The where clause is not part of overall query. Just isolating on one ID.
I have two tables something like this : Table : Account AccountID UserName Password
Table : Address AddressID AccountID Line1 Line2 State
My NHibernate mapping file looks like this : For Table Account <id name="ID" type="long" column="AccountID" access="property"> <generator class="identity"/> </id>
My Problem is it does entry in the Account table perfectly, but when it comes to do entry in Address Table it says AccountID can't be null, I'm wondering shouldn't it pick it up from the Account table as I'm passing in that object.
For the Workaround I was thinking maybe I can get the identity of the last entry saved and somehow save it to the other table myself.
I have a table with an Identity column. Is there a way to get the automatically generated primary key after I insert something to the table? If so, what is the syntax?
Hi, All, I have agentID in product table. Now I add agentID column in transaction table. Now I want to copy all agentID from product table to transaction table based on the order_id in both table. Can you show me an example? Thanks Betty
SO when i try to load from Master table to parent and child table i am using using expresssion like
select B.ID,A.* FROM FLATFILE_INVENTORY AS A JOIN DMS_INVENTORY AS B ON A.ACDealerID=B.DMSDEALERID AND A.StockNumber=B.STOCKNUMBER AND A.InventoryDate=B.INVENTORYDATE AND A.VehicleVIN=B.VEHICLEVIN WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE()) and convert(date,B.Ftpdate) = convert(date,getdate()) ;
If i use this Expression i am getting the current system date data's only from Master table to parent and child tables.
My Problem is If i do this in my local sserver using the above Expression if i loaded today date and if need to load yesterday date i can change my system date to yesterday date and i can run this Expression.so that yeserday date data alone will get loaded from Master to parent and child tables.
If i run this expression to remote server i cannot change the system date in server.
while using this Expression for current date its loads perfectly but when i try to load yesterday data it takes current date date only not the yesterday date data.
What is the Expression on which ever date i am trying load in the master table same date need to loaded in Parent and child table without changing the system Date.