I have a problem during my etl process. my customer do not want that everytime the ssis package running, the dw table been cleen up/delete all. so i just have to process only with the new data. For example is like this:
Source Database: db1
Table DataEmp
id name address
----------------------------
1 Mike California
2 David New York
3 Bruce Ohio
Destination Database: db2
Table DimDataEmp
DataEmpKey DataEmpName DataEmpAddress
----------------------------------------------------------------------
1 Mike California
2 David New York
My goal now is, if i run the ssis package the only data transfer is (3, Bruce, Ohio). how am i do it? i already try the merge, merge join, or lookup control, but still don't work. basically i want to "select not exist" the destination table but in different database. please help me, cos i still new in ssis. Thanks
I am working with several similar tables that for the most part contain the same columns. I need to export these tables to different csv files, and the csv file needs to have all possible column names and blank fields if the column does not exist in the table. Basically a set and subset thing.
for example TABLE AA has three columns A, B, C and TABLE AB has 2 columns B, D
I am working with over 200 tables, and am planning on automating this with c#. I could use the information_schema to create the SQL statement for each tabel but it sems like I should also be able to do this using one select statement.
I want to write a select statement that combines this. SELECT [A], [B], [C], '' AS [D] FROM [AA]
I am trying to write a script that will only insert records that do not exist to a local table. Whenever a form is changed, it keeps the same name, but a new record is created with a new ID. I need to be able to pull the new record by checking for ID's that are not on the local table, but using a form name to isolate. The nested select I am attempting is
select distinct qf.[eform_id] , qf.[name] , qf.[description] from [qfiniti].[qfiniti_platform].[dbo].[eval_forms] qf inner join FORMS_REF fr on qf.[name] collate database_default = fr.form_name collate database_default where not exists (select fr.eform_id from forms_ref fr inner join [qfiniti].[qfiniti_platform].[dbo].[eval_forms] qf on fr.eform_id = qf.eform_id);
It is outputting nothing, but I have intentionally deleted one form record to force it to show up.
I have a table of row IDs, order numbers, and status. Status is a value of 1 or 2. Some rows only exist as a 1, some only as a 2, and some exist twice -- once as 1 and once as 2. I want to select orders that are 2 as well as rows that are 1 but do not also exist in the table as 2. Ultimately I will only see each order number once. Is there a better/faster way than doing multiple selects and comparing them to each other?
I'm trying to be slick, but so far am just all wet *snicker*
I've got a table with rows that may or may not exist, and am trying to retrieve a column if an associate row DOES exist.
For argument's sake, my PIndex table looks like: PID int CreateDate smalldatetime CloseID float
Here is my select logic that I thought would save an extra select to load my local variable if the row is actually in the table...
DECLARE @CloseID float
IF NOT EXISTS (SELECT @CloseID = CloseID FROM PIndex WHERE ((PID = '14') and (CreateDate = '2004-02-06'))) SET @CloseID = 100
The SET afterwards is just to initialize the variable if it can't be had from an existing row in the table.
The trouble is, that it fails to compile with the following error: >>>>> Line 3: Incorrect syntax near '='.
Any insights? My goal is to use a single select to load the value into my local variable if the associated row exists, or to set my local variable to 100 if it doesn't.
Ok,I have a data warehouse that I am pulling records from using OracleSQL. I have a select statement that looks like the one below. Now whatI need to do is where the astrics are **** create a case statement orwhatever it is in Oracle to say that for this record if a 1/19/2005record exists then End_Date needs to be=1/19/2005 else getEnd_Date=12/31/9999. Keep in mind that a record could have both a1/19/2005 and 12/31/9999 instance of that account record. If 1/19exists that takes presedent if it doesnt then 12/31/9999. The problemis that the fields I pull from the table where the end_date is inquestion change based on which date I pull(12/31/9999 being the mostrecient which in some cases as you see I dont want.) so they are notidentical. This is tricky.Please let me know if you can help.SELECTCOLLECTOR_RESULTS.USER_ID,COLLECTOR_RESULTS.LETTER_CODE,COLLECTOR_RESULTS.ACCT_NUM AS ACCT_NUM,COLLECTOR_RESULTS.ACTIVITY_DATE,COLLECTOR_RESULTS.BEGIN_DATE,COLLECTOR_RESULTS.COLLECTION_ACTIVITY_CODE,COLLECTOR_RESULTS.PLACE_CALLED,COLLECTOR_RESULTS.PARTY_CONTACTED_CODE,COLLECTOR_RESULTS.ORIG_FUNC_AREA,COLLECTOR_RESULTS.ORIG_STATE_NUMBER,COLLECTOR_RESULTS.CACS_FUNCTION_CODE,COLLECTOR_RESULTS.CACS_STATE_NUMBER,COLLECTOR_RESULTS.STATE_POSITION,COLLECTOR_RESULTS.TIME_OBTAINED,COLLECTOR_RESULTS.TIME_RELEASED,COLLECT_ACCT_SYS_DATA.DAYS_DELINQUENT_NUM,sum(WMB.COLLECT_ACCT_SYS_DATA.PRINCIPAL_AMT)As PBal,FROMCOLLECTOR_RESULTS,COLLECT_ACCT_SYS_DATA,COLLECT_ACCOUNTWHERECOLLECT_ACCOUNT.ACCT_NUM=COLLECT_ACCT_SYS_DATA.ACC T_NUM(+)ANDCOLLECT_ACCOUNT.LOCATION_CODE=COLLECT_ACCT_SYS_DAT A.LOCATION_CODE(+)AND COLLECT_ACCOUNT.ACCT_NUM=COLLECTOR_RESULTS.ACCT_NU M(+)AND COLLECT_ACCOUNT.LOCATION_CODE=COLLECTOR_RESULTS.LO CATION_CODE(+)AND COLLECTOR_RESULTS.ACTIVITY_DATE =to_date(''01/19/2005'',''mm/dd/yyyy'')AND COLLECT_ACCOUNT.END_DATE = to_date(''12/31/9999'',''mm/dd/yyyy'')AND COLLECT_ACCT_SYS_DATA.END_DATE = *****************
I've got a big problem that I'm trying to figure out:I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.I don't need this for an individual ID select, I need it applied to every record from the table.My address table has some columns that look like: [AddressID] [int][LocationID] [int][Type] [nvarchar](10)[Address] [varchar](50)[City] [varchar](50)[State] [char](2)[Zip] [varchar](5)[AddDate] [datetime][EditDate] [datetime]AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table.So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.How can I do this efficiently with perfomance in mind???Thank you in advance for any and all replies...
I've got a big problem that I'm trying to figure out:
I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.
I don't need this for an individual ID select, I need it applied to every record from the table.
My address table has some columns that look like: [AddressID] [int] [LocationID] [int] [Type] [nvarchar](10) [Address] [varchar](50) [City] [varchar](50) [State] [char](2) [Zip] [varchar](5) [AddDate] [datetime] [EditDate] [datetime]
AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table. So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.
How can I do this efficiently with perfomance in mind???
alo people.. so..this is my question regarding to gridview.. is possible to put the field inside the gridview if it is not exist in the database.. example here is the database: Name salaryADAM 30 BOY 50 so right now i want to check their yearly salary..so mysql command will look like thisSELECT NAME,SALARY,SALARY*12 AS YEARLY_SALARYFROM TABLE1; right now the result of the query will look like thisName Salary yearly_salaryADAM 30 360BOY 50 600my question again is, is it possible to put the yearly_salary inside the gridview since it is not originally from the table 1? ps;/ i;m using vb.net(asp.net) and mysql..and also i use the sqldatasource.. thanx everyone for the help...
helloWhat query shoul I send to SQL serwer ( in transact SQL language ) to checkdoes some database exist on serwer ? It similar to problem "does some tableexist in database" - resolve to it is query:use db_silnikIF EXISTS (SELECT * FROM prad)PRINT 'table exist'but what is the query to check does some database exist on serwer ?best regardsAdam
I have 1 table to be merge replicated to 2 other servers (DATA5 and DATA5R). Whichever server I push a subscription to first works just fine. The second server get the following error.
Table 'UPDATE_LOG' does not exist in the Subscriber database. (Source: DATA5R (Data source); Error number: 21078) --------------------------------------------------------------------------------------------------------------- '. Check sysobjects. (Source: DATA5R (Data source); Error number: 2501) --------------------------------------------------------------------------------------------------------------- The process could not deliver the snapshot to the Subscriber. (Source: Merge Replication Provider (Agent); Error number: -2147201001) ---------------------------------------------------------------------------------------------------------------
I'm not sure why it expect the table to be there already. It doesn't on the first server. Am I missing something. If I create the substription for DATA5R first then it works and DATA5 fails.
Hello. I need some help constructing a query i need to run on my database. I need to add 2 fields to every table in my databse. However, some of the tables already have1 or both these fields so i need to somehow do a check if the dield already exists. If it does not create the fields. Im using a MS SQL express 2005 server. Could anyone help me construct this. Im pretty novice at SQL. Thanks.
What SQL statement can i run to return a list of new tables or columns that exist in the Sales Database on Server A, which do not exist in the Sales Database in Server B.
I am trying to pull a min value of a date column from table 1, if table 1 does not have any record I need pull a date column from table 2. Table 2 will always have a unique record ID (No duplicate ID's).
Table 1
ID date 1 1/1/2014 1 1/5/2014
Table 2
ID date 1 1/5/2014 2 10/15/2014
Here is the desired result when running for ID = 1 (select Min(date) where ID = 1, I should be getting 1/1/2014 from Table 1.
If I am running the same query where ID=2 then I should be getting 10/15/2014 from Table 2. So basically I need to check if a value exists on Table 1 first, if there is no value on Table 1 and then to grab the value from Table 2.
I installed SQL 2005 including backward compatibility, MSDN libraries and SP2 a new Windows 2003 server (chose mixed mode authentication). Installation was successful and I then installed an off the shelf database with Windows authentication which also installed successfully. I created a new group in AD, added the database users into the group and gave db-owner rights to the group in SQL as advised by the installation guide. However, when I try to open the database it gives an error message saying "Cannot open database.[DBNETLIB][ConnectionOpen(Connect()).] SQL server does not exist or access denied". I also tested it adding an individual user (myself) as a user to no avail. When I set up DSN in ODBC on my computer I can successfully test the connection but can't run the application. The connection is via TCP/IP. Any suggestions?
An error has occurred during report processing. (rsProcessingAborted) Query execution failed for data set 'TestID'. (rsErrorExecutingCommand) For more information about this error navigate to the report server on the local server machine, or enable remote errors
The log file reads:
---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'TestID'. ---> System.Data.SqlClient.SqlException: SELECT permission denied on object 'TableID', database 'Database', schema 'dbo'.
***Background***
General Users got an error message when trying to access any reports we have created. All admin have no problems with the reports. Users (Domain Users) are given rights (Browser) to the reports and the Data Sources (Browser) and yet cannot view the reports.
An error has occurred during report processing. (rsProcessingAborted) Cannot create a connection to data source 'DS2'. (rsErrorOpeningConnection) For more information about this error navigate to the report server on the local server machine, or enable remote errors
I'll add this from the report logs...
w3wp!processing!1!3/20/2007-11:43:25:: e ERROR: Data source €˜DS2€™: An error has occurred. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source €˜DS2€™. ---> System.Data.SqlClient.SqlException: Cannot open database €œDatabase€? requested by the login. The login failed. Login failed for user €˜DOMAINUsername€™.
The user has rights via a local group to the report and data source (Browser rights) and the local group has been added as a SQL login.
I gave rights to the databases themselves instead of just to SQL and the error changed (Ah-ha...progress, but why!?!?)
I inherited a screwed up Database and I have to straighten out the pemissions and owners. There were several tables listed with an owner of DSSADMIN. However when I logged into Query Analyzer as DSSADMIN, I could not query the DSSADMIN owned tables unless I prefaced the table with DSSADMIN as the owner (ex: DSSADMIN.table).
At the recommendation of our Developer we restored the database from tape thinking that this would reset the proper permissions and table owners. After the restore a user called BCUSER had ownership of the previously mentioned tables (that DSSADMIN owned before the restore); however when I open the Users list, BCUSER is not listed as a user.
If I try to add BCUSER to the database I get an error stating that the USER or ROLE BCUSER already exist in this database. There is a role called DSS listed for this Database that BCUSER was listed a member of. I removed BCUSER from the DSS role and yet I still am not able to add BCUSER as a user for this database. (Yet, as I stated earlier BCUSER is now the owner of the tables the DSSADMIN was the owner of before the restore)
BCUSER is a user in the other databases on this SQL Server.
Does anyone out there understand this problem and/ or can suggestion some possible solutions?
I have created a sql login account called "webuser" and has given public role in my database. In my asp.net application i build connection string using above account and its password . We give permission on store procedure for for the above account to execute .We dont give table level permission for the above account . When we run the application with the above settings it runs fine on test server . However Now i have transfered the databse object to live server with its permissions . Now while I executing the aspx page , I am getting above error . I have checked that the store procedure has execute permission for webuser account and dbo(i.e SA) has all the permissions for all database objects . Still why i am getting error ? (Please note , the thing is working fine in test server)
Not totally sure if this is the best place for this question, but regarding maintainability / good practice issues vs speed I have a question regarding what is the best way to SELECT some data from a database.I have the item_id of an item bought. I want to get a customer's billing information based on the item he bought. Should I do this with two queries or one using joins? Here is my dbase schema: customer_info--------------customer_idnamecc_numexpdate item_bought------------item_idcustomer_id And here is a basic pseudocode algorithm:function getCustomerInfo(customerID){ SELECT customer_info.name, customer_info.cc_num, customer_info.expdate FROM customer_info WHERE customer_id=customerID; return results;} function getCustomerInfoWithItemId(itemID){ SELECT item_bought.customer_id FROM item_bought WHERE item_bought.item_id=itemID; results = getCustomerInfo(customer_Id); return results;} results = getCustomerInfoWithItemId(400);OR function getCustomerInfoWithItemId(itemID){ SELECT customer_info.name, customer_info.cc_num, customer_info.expdate FROM customer_info, item_bought WHERE item_bought.item_id=itemIDAND customer_info.customer_id = item_bought.customer_id; return results;} results = getCustomerInfoWithItemId(400);
I've been having some trouble finding the correct syntax for the following query, where I am simply trying to move selected records from table A in database A to table A in database B.
Here is what I have:
Code:
SELECT pd.phoneID, phonebookID, timezoneID, phoneNumber, lastName, firstName, address, city, state, country, zip, custom, custom2, custom3, memo, fax, email INTO history.set_appointment1 FROM pro_PhoneData pd LEFT JOIN pro_Campaign_1_Results cr ON pd.phoneID = cr.phoneID WHERE ((phonebookID='3') OR (phonebookID='1')) AND descriptionID = '203' AND callTime < GETDATE()-365 ORDER BY callTime DESC
When I execute this query, I get the following message: "The specified schema name "history" either does not exist or you do not have permission to use it."
I've looked up examples for select into queries and have played around with it a bit, but no such luck.
I am trying to select info from a database (MS-SQL) and show that whit a label. And don’t really get every thing to work. So I am glad for that help I can get.
I have a single form that the users can do lookups for items. We have two locations each with its own SQL Server and database. Trying to use an IF...THEN statement as they select the DB they want to query it then creates the connection string for that particular database. I haven't a clue on how to do this. If someone could point me to the documentation to do this I would really appreciate it. <%@ Page Language="VB" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDB" %> <%@ Import Namespace="System.Data.SqlClient" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server"> Sub get_Dies(ByVal sender As System.Object, ByVal e As System.EventArgs)
Dim dbConnection As New SqlConnection
If ddlDatabase.SelectedValue = "db2" Then ??? dbConnection = "server=server2;Database=db2;UID=user;PWD=pass" dbConnection.Open() Else ??? dbConnection = "server=server1;Database=db1;UID=user;PWD=pass" dbConnection.Open() End If Dim sqlString As String = " sql statement" Dim dbCommand As New SqlCommand(sqlString, dbConnection)
Dim dbDataReader As SqlDataReader dbDataReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection)
I want to be able to select data from a database without using grid view etc. I can set up a connection and bind it to a grid view but I would like to do some data processing without showing it. Something like
select id from table where name = "x" store id in idvar store the row count to a variable rowcount if rowcount > 0 select name from table2 where id = idvar endif
etc
I can see how to set the sqldatasource select command but not how to run it and get results out of it. Please help
