hello guys !
i need a little help in an update statement.
i have two tables as
table1(id1,name)
table2(id2,date,val2)
what i want is to set val2=45 in table2 where date=4/4/2007 (say) and id2, now id is to be obtained from table1 based on the name;
like
table1
---------
(1,"akmal")
(2,"peter")
(3,"kamal")
I know the name as peter, but i want to used its id1 (2) in the update statement. You might have got the idea that id2 is a foreign key referring to id1.
Requirements: We have Our Local DataBase(SQL Server) Guess eg. DBLocal We have a requirements to update this DBLocal Table Data with Other Database DBRemote(SQL Server) table data. This Task is Schedule in a Week. Means We needs to update this DBLocal Table Every Week from DBRemote Data.
Both Side we have only one table. Means Source is One Table and Destination is Table One.
Right Now I have a connection string for DBRemote(SQL Server) and SQL Statement for getting Data from DBRemote(SQL Server).
can any one tell me what I need to do for achive this requirement?
please provide me link also; from there i can get enough information for my requirements.
In the Operating environment databases, may be made tables in the database on a temporary basis but they are still yet and they are not removed, how to identify tables that have been made on a temporary basis are not used (don’t have any read & write records)?
Hi,I have table with three columns as belowtable name:expNo(int) name(char) refno(int)I have data as belowNo name refno1 a2 b3 cI need to update the refno with no values I write a query as belowupdate exp set refno=(select no from exp)when i run the query i got error asSubquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I need to update one colum with other column value.What is the correct query for this ?Thanks,Mani
How do I format a date value in SQL 2000 sp? Tired the following none are returning the quivilent rates. What is the syntax please? SELECT *FROM tblCompanyWHERE ( C_LastBuyDate = '03/21/2008') SELECT *FROM tblCompanyWHERE ( C_LastBuyDate = '2008/03/2008') SELECT *FROM tblCompanyWHERE ( C_LastBuyDate = '03-21-2008')
Hi SQL fans,I realized that I often encounter the same situation in a relationdatabase context, where I really don't know what to do. Here is anexample, where I have 2 tables as follow:__________________________________________ | PortfolioTitle|| Portfolio |+----------------------------------------++-----------------------------+ | tfolio_id (int)|| folio_id (int) |<<-PK----FK--| tfolio_idfolio (int)|| folio_name (varchar) | | tfolio_idtitle (int)|--FK----PK->>[ Titles]+-----------------------------+ | tfolio_weight(decimal(6,5)) |+-----------------------------------------+Note that I also have a "Titles" tables (hence the tfolio_idtitlelink).My problem is : When I update a portfolio, I must update all theassociated titles in it. That means that titles can be either removedfrom the portfolio (a folio does not support the title anymore), addedto it (a new title is supported by the folio) or simply updated (atitle stays in the portfolio, but has its weight changed)For example, if the portfolio #2 would contain :[ PortfolioTitle ]id | idFolio | idTitre | poids1 2 1 102 2 2 203 2 3 30and I must update the PortfolioTitle based on these values :idFolio | idTitre | poids2 2 202 3 352 4 40then I should1 ) remove the title #1 from the folio by deleting its entry in thePortfolioTitle table2 ) update the title #2 (weight from 30 to 35)3 ) add the title #4 to the folioFor now, the only way I've found to do this is delete all the entriesof the related folio (e.g.: DELETE TitrePortefeuille WHERE idFolio =2), and then insert new values for each entry based on the new givenvalues.Is there a way to better manage this by detecting which value has to beinserted/updated/deleted?And this applies to many situation :(If you need other examples, I can give you.thanks a lot!ibiza
I have to import data into 2 tables on a daily basis. The data is provided as a flatfile. In order to fullfill this task the tables have to be truncated first. Are there any possibilities to do this job automatically with dts, or do I have to write an Interface in VB or something like that?
Hi All I am using following code for my application SELECT convert(varchar(10),acr._eventTime,120) [Date], sum(case r.Severity WHEN 'Warning' THEN 1 end)[Warning], isnull(sum(case r.Severity WHEN 'OK' THEN 1 end),0)[OK], FROM ActiveCheckIssueResult r GROUP BY convert(varchar(10),acr._eventTime,120)
it gives me Result as,
Date warning OK --------- --------- ----- 1/1/1008 1 1 2/1/2008 0 2
i want count on weekly basis Could any one help me?
I want to set my job agent to schedule for second basis but in EM i found by right clicking job in EDIT Recuring Job Schedule i m getting option in Hr and MM but not in SS so where i can set this in seconds (SS) or through QA????
Currently database1 and database2 are planned to be synchronized with the interval of 5 seconds!! ( dont know exact, but i select run continoulsly on subscriber screen ) This could cause serious performance issues as both applications will be live on difference servers.
how can i Synchronization should be performed on day basis or it should be modification basis to avoid any performance issue on live application
i am using merge replication Local distributor and subscriber on another server .
I'm just a newbie with XML. Now we're moving all our DTS to SSIS packages. Most of them are simply processes that takes a plain file and loading these data into Sql table or in inverse way. So that I wonder what role will play XML here.
I mean, are there big differences between store data as XML format and nvarchar?
I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.
Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),
I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.
I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?
On a webpage, there are filters to choose from. Like date, amount, SSN (multiple filters can be choosen).I have a single query so far. SqlCommand cmd = new SqlCommand("SELECT [column1], [column2], [column3], [column4], [column5] FROM [table] WHERE [column4] = 'condition4' AND [column5] = @total_bill AND [last_change] >= @txtStartDate AND [last_change] <= @txtEndDate ", Conn) ; cmd.Parameters.Add(new SqlParameter("@total_bill", total_bill1.Text)); cmd.Parameters.Add(new SqlParameter("@txtStartDate", txtStartDate.Text)); cmd.Parameters.Add(new SqlParameter("@txtEndDate", txtEndDate.Text)); I want to break the query so that it executes on the basis of different sets of conditions (filters). If I dont select date filter, then the above query will not execute properly.Please help.
I have recently upgraded one of our servers using the in place upgrade method to SQL Server 2005 from SQL Server 2000.
We are currently having an issue with stored procedures as they appear to be running slowly and the only way to correct this issue is to recompile them on a daily basis.
Has any one else had a similar issue, any ideas what could be causing this to occur.
I currently have a series of stored procedures that capture stock prices on a daily basis, then summarize the results into a daily, and further, a weekly summary of the "index" of a group of stocks. The data is accumulated from a (to use a highly technical unit of measurement...) bunch-O-individual rows of data using aggregate functions such as AVG and SUM.
The problem is that I occasionally get a warning on such aggregate statements which is the common one complaining thusly: "Warning: Null value is eliminated by an aggregate or other SET operation"
I know where it comes from, and I know how to code to protect the aggregate from complaining (i.e., AVG(ISNULL(yadayada,0)) ) but I am interested in figuring out a way to REPORT the statement that contains null values. I can, of course, capture ERRORS in selects, but is the same mechanism used to capture these NULL warnings on my aggregate statements? I don't necessarily want to know which individual row is causing it, just want to "tag" somehow the statement that results in the warning so I can go back after the run and check into it (after capturing local "pointer" info at the time the offending aggregate is invoked).
The code I use to capture errors and trace information follows: UPDATE PortfolioPerformance SET PrevDate = @PrevDate, DailyPerChg = GPP.DailyPerChg, DailySumPriceChg = GPP.DailySumPriceChg, SumCurrPrice = GPP.SumCurrPrice, SumPrevPrice = GPP.SumPrevPrice, StockCount = GPP.StockCnt, AvgHighPriceRatio = GPP.AvgHighPriceRatio, AvgLowPriceRatio = GPP.AvgLowPriceRatio, Volume = GPP.Volume FROMPortfolioPerformance PP (nolock), VIEW_Get_PortfolioPerformance GPP WHERE PP.PortfolioID = GPP.PortfolioID AND (PP.CreateDate = GPP.CreateDate AND PP.CreateDate = @CreateDate) AND PP.PrevDate IS NULL
NOTE: the aggregation in the above code is performed in the view referenced as "GPP", but that's outside the realm of the question, I think, so I won't bore you with the details of that just yet.
So I think if I can capture the warning like I do the errors, I can accomplish what I want to accomplish. I haven't yet been able to find any guidance in the Books Online, so do any of you have any pointers?
How would you solve this problem? Would you use replication? SSIS? Schedule stored procs? A third party data tool?
We have about 50 databases located at offices around the world. There are several key tables which we need a central copy of at our Head Office.
We need only the new INSERTS or UPDATES for the day to be copied to head office. The copying process should happen after business hours for that office.
Some cleaning of the data needs to happen before updating head office.
Our database is being installed at new offices every other month, so it's important to us that this be easy to add a new database into the schedule, ideally without needing a SQL DBA or Developer.
We want something robust, performant and easy to troubleshoot.
On what basis, SQL server decides the sequence of these rows?
I need to fetch data from a table with very large number of rows. Because of the datasize I need to do this in chunks. I am thinking of passing row counter and fetch N rows at a time. I want to know if there is a need for sorting a table before I apply the above logic or I can rely on default sorting.
I have 10 oracle o/p tables. I have to transfer data in monthly or adhoc basis. Each table will have millions of records. How to transfer Oracle to SQL Server 2005. Which is the best way to transfer the data.
Here is my Query, I don't know whether I'm getting it right?
--Quarter 1 SELECTD.MerchantName, A.MID, A.TID, ISNULL(SUM(A.SumTrxnMon), 0) AS SumTrxnMon, E.FullName, E.DxBEmail INTO#Quarter1 FROMdbo.tblRPT_Spend AS A INNER JOIN dbo.tblMer_DeployORetrieveTerm AS B ON A.MID = B.MID AND A.TID = B.TID INNER JOIN
now I want to call all the sp on the basis of input like If filename is Fdoor then it shold fire the SP_Archive_using_merge_Fdoor , if file name is Fdoop then it shoilud fire the SP_Archive_using_merge_Fdoop like that .
below is the 2 sp .
--First SP ALTER PROCEDURE [dbo].[SP_Archive_using_merge_Fdoor] AS BEGIN SET NOCOUNT ON DECLARE @Source_RowCount int DECLARE @New_RowCount int
Anyone have any experience in connecting to a Basis database? I want to build an SSIS package to ETL data out of a Basis database and into a sql server 2005 db. I have downloaded the ODBC driver, but am not usre how to plug it in to SSIS to do teh extraction.
Also I am totally new to Basis...it is not an RDBMS at all but the literature says it supports SQL queries, so I am not sure about where to start.
I've just spent the most frustrating 4 hours trying to install SP2.
I kept getting "unable to start SQL Server service....see BOL ...re starting the service manually" messages; with the only options being to Retry or Cancel. Retry resulted in the same message. Cancel resulted in all components except SQL (sqlexpress) being installed - therefore totally useless
No mater what I did i could start the SQLservice as it did'nt show in the list of Services; nor did the SQL EE programme show in the Add/Remove Programs...evenn though the other components did.
I deleted/removed everything that was istalled; cleaned up the Registry; and tried again...
..and again
..and again
FIVE install / re-install failures; numerous registry cleanups!!!!
I was at the point where it looked like i'd have to reformat my hard disk, and completely re-install XP, all aplications i.e. Office 2003 etc in order for the thing to work.
I gave it one more shot, but this time, during install
- selected all components except SDK
- I unchecked the Start SQL / BROWSER at startup
- Checked the Enable User Instances and Add to Administrators boxes
Lo and behold....it installed!!!
I'm still very worried as the registry is full of "duplicate" entries i.e. Blah blah blah and blah blah blah.1
But at least the thing is installed.
Would interested to hear if anyone has had similar or other problems with the SP2 install/upgrade
----------------------
For info:
I ran the install as Administrator
IIS not installed
Win XP Sp2 fully updated
Dell Inspiron XPS M1710 laptop with 2gb Ram; 2ghz Duo core processors
I would like to force only certain users on certain databases to use encrypted connections for accessing our 2005 SQL Server. I've found a clunky way to do it, via registering a login event notification and using the kill statement. However, that's ugly, and I don't think there is any guarantee that the event will always fire before an evil client could get off a few evil commands. Is this supported, or is the only supported feature the forceencryption option, which is server-wide?
I'm creating 15 reports based on one data source. Once I created, I'm supposed to run this on daily and monthly basis. I'm going to use snapshot option in Report Manager. Is it fine? Do I need to create a data base too?
Also when I change the target URL address in the report property box, can I deploy it to any other server? Do I have to change the settings of report configuration manager too, inorder to publish reports on another server(not in my local machine)
Hello All,i have 2 excel files which i want to upload to sql database. i know how to upload to the folder. my question how to i use these excel files and convert them into tables in sql database.i have to do this on a regular basis. is there anyway i can do this. the 2 files when converted to tables, i need to create relationships etc. any suggestions or references please. i appreciate it. Thanks
I want the count of orders of a particular table on weekly basis i.e if date given to me is 10/3/2014 then my output should be count of orders from date 10/3/2014 to 09/3/2014(one week) then count of orders from 2/3/2014 to 08/3/2014(another week) and then from 24/2/2014 to 01/3/2014(another week).....
Split function. I have records of multiple users, the last value of every record is a contact number (10 Digits- Numeric), I want a split function which can take the whole text and split the records on the basis of contact number.
In order words i want SQL to locate the contact number and move to the next record after that and so on till the end of the text.
create table tbl_1 (txt varchar (max))
insert into tbl_1 values ('john asfasdf 535 summit ave franklin lks nj 15521 510_644_1079 na na 5,8/12 executive, finance finance and planning far 5537 21133 8.25 126 ronald d hensor jr. 5575621596
[Code] .....
Output john jimenez 535 summit ave franklin lks nj 15521 510_644_1079 na na 5,8/12 executive,finance finance and planning far 5537 21133 8.25 126 ronald d hensor jr. 5575621596 jeffrey galione 57 allen dr wayne nj 15810 562_434_0710 na na 5,8/12 executive, technical sales and support good 8137 91630 8.25 126 eileen oneal 8258364083
