Hi all,
please see if some one can suggest a better approach for this.
i need to move 5 million rows from one table to another.
LoanID is the only clumn in Source table
Source table structure
=========-->
==============================
loanID
1
1
2
2
2
3
4
=====================================
target or detination table should look like this
=========================================
loanID Sqeuence_number_with_in_LoanID
1 1
1 2
2 1
2 2
2 3
3 1
4 1
=========================================
this is what i have tried so far
1)there are about 4 & 1/2 million out of 5 million which will appear
only once (this is one tine load) in source table.
so if i do this
-----------
sleect loanID
from SourceTable
group by loanID
having count(*)=1
and then i will hard code Sqeuence_number_with_in_LoanID to 1 .because
i know they will only come once.this is set based operation so this is
fast.
2)now,problem is with remainder of the 1/2 million rows.
i had to use cursor for this which is very-very slow.
other option i was thinking of is to pull it in the front end
(.net)and do it there.i am hoping that will be faster.
Database i am working on is sql server 2000.
if some could suggest me some better approach of handling this
remainder
1/2 milion rows.
thanks in advance
-sidd
Hi guys, In the query below, I need to insert a new Column that shows the last Document date. In example, Document Date Last Date 2006/01/15 2007/11/30 2007/03/25 2007/11/30 2007/11/30 2007/11/30 I need to show the last (max) date for each row. Thanks in advance for any help, Aldo.
Code Block SELECT Stock.VALUEDATE AS 'Document Date'
FROM Stock As Stock INNER JOIN ACCOUNTS AS Accounts LEFT OUTER JOIN ACCSORTNAMES AS AccSortNames ON Accounts.SORTGROUP = AccSortNames.ACCSORTCODE LEFT OUTER JOIN EXTRANOTES AS ExtraNotes01 ON Accounts.ACCOUNTKEY = ExtraNotes01.KEF AND ExtraNotes01.NOTEID IN ('17') LEFT OUTER JOIN EXTRANOTES AS ExtraNotes02 ON Accounts.ACCOUNTKEY = ExtraNotes02.KEF AND ExtraNotes02.NOTEID IN ('18') ON Stock.ACCOUNTKEY = Accounts.ACCOUNTKEY INNER JOIN DOCUMENTSDEF AS DocumentsDef ON Stock.DOCUMENTID = DocumentsDef.DOCUMENTID
WHERE Accounts.SORTGROUP Between '0' AND '379' AND Accounts.SORTGROUP Not Between '100' AND '150' AND Stock.DOCUMENTID In ('1', '4', '5', '11', '78', '95') AND Stock.STATUS Not In ('0', '2') AND Accounts.SORTGROUP Between '0' AND '379' AND Accounts.ACCOUNTKEY Like '313%'
I have embedded a subreport in the detail section of my report. When I view the subreport in page preview mode it renders with three columns, but when it is rendered w/in the main report in page preview mode the 3 column layout is rendered as a single verticle column.
I've defined the columns in the Body/Columns. I have also set the report inteactivesize and pagesize heights in an attempt to remedy the issue. Margins are set to 0.
Hi There,I want to create a column that usto numbers upto a specified value, and the resets. I've tried using the identity column and then using DBCC CHECKIDENT, but this doesnt doesn't have the desired affect. Here's an example of what I'm after.AutoRow1234 -------- reset1234 --------resetAny help would be great, many thanksStuart
I have setup merge replication which successfully synchronizes with a group of desktop users using SQL Compact Edition.
However now I have setup Article Filters and when I attempt to regenerate the snapshot I get the following error:
Invalid column name 'rowguid'.
Failed to generate merge replication stored procedures for article 'AssignedCriteria'.
When I look at publication properties at the Articles page.. All my tables have the rowguid uniqueidentifier successfully added to tables and selected as a compulsory published column, apart from the table above "AssignedCriteria".. Even when I attempt to select this column in the article properties page and press ok, when I come back it is deselected again. ( The Rowguid column is however physically added to the table)
I have scripted the publication SQL and then totally reinstalled from scratch, including the database but for some reason it doesn't like this table. I remove the article filters, but still this "rowguid" is never "selected" in article properties.
We are using Uniqueidentifiers in other columns as well for historical reasons, but this doesn't appear to be a problem in other tables..
When I created my website in Visual Studio 2008, I ended up with two databases. The one I created (Speedway.mdf), then VS2008 added a new one (aspnetdb) when I added membership data (roles, users..etc). Since the host I'm using charges for each database, I just want to move all my membership information into Speedway.mdf. I exported the tables from aspnetdb.mdf to Speedway.mdf, but now I don't know how to tell the site to look at Speedway.mdf to find the membership info?
I'm trying to send data from a table on SQL server to a DB2 table on the Main Frame and I get this error. Any ideas?
[IBM][CLI Driver][DB2]SQL0904N Unsuccessful execution caused by an unavailable resource. Reason code: "UPDATABLE WORKFILE", type of resource: "100", and resource name: "TEMP DATABASE". SQLSTATE=57011
Can anybody tell me that how can i configure the things that will show my main report page when i enter Url like http://localhost/ReportServer. It gives directory struction first & then we have to select particular report
I gonna write some t-sql stored procedures,and need to log error information in a database table, but don't want that log entry to be a part of the logical transaction.
So basically i want to suspend the main transaction, perform SQL operations , commit or rollback those operations, and resume the main transaction.
I'm on a team that is developing a site in PHP / MySQL. We've been asked by the client to convert the database over to MSSQL. I've never used MSSQL, but after looking at the syntax available on php.net, there really isn't all that much difference in doing basic queries. I was wondering if any of you MSSQL gurus knew of some major differences between MySQL and MSSQL that I may be overlooking.
Anything, however basic or complex, would be helpful.
I have created several reports using SSRS which is installed in my local machine. But source data base is in the main server. Data source connection works properly. Report manager is also configured to local machine.
Now I tried to deploy these reports to main server. So I changed the target URL to http://mainservername/reportsserver in the internal report property change box. But it was not succeeded. I get a error message like this.
------ Build started: Project: Partner Reports -Internal, Configuration: Production ------
Build complete -- 0 errors, 0 warnings
------ Deploy started: Project: Partner Reports -Internal, Configuration: Production ------
Deploying to http://derby/ReportServer
Error rsRPCError : The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled)
I have 4 subreports that I need to display in the main report. I can pull a sub-report with in the main report howevere the subreport doesn't display any data. It just gives me a blank template like:
Customer name Customer # Address 1 Address2 city
The subreport itself is not empty when I open it otherwise it shows me a list of all the customers however, no data when I call it as a part of main report.
I have constructed my main report using the table item from the toolbox. In maintable I have two groupings.
In the maintable I have grouping2 set for a page break after each grouping.
I have set the subreport parameter to the field value of the maintable grouping2.
I want to display the contents of the subreport after grouping1 of the maintable on the same page.
I have achieved a result by placing the subreport in maintable_group2 footer. However when I save it as an excel sheet it says "Subreports within table/matrix cells are ignored".
I really don't like how the report displays like this in the report server however I will need to export this report to excel.
Hi, all here, I am having a question about stored procedures for data mining. What are the main benefits of stored procedures for data mining? (what stored procedures can do for data mining on sql server 2005? when they are useful for data mining?).
Could please any expert here give me any guidance for that? Thanks a lot in advance.
What are the tools available in MS SQL Server 2000 migrate data from Main Frames. Can we do with DTS if so are drivers available to connect to sql servers.
I have a 2 main tables in my system. One is main table has ticket information location and so on. And detail table which hold all actions on that record with multiple money fields (its around 5 fields). it goes trough some crazy calculations on the detail table and get 5 results for each ticket. And this calculation is done everytime the ticket is looked at. There is around 10 detail records per 1 main record. and the main table is around 7 mil. records
Now i got 2 options. I can create a trigger that does the calculation and update the main table show the results from there. This is going to affect all updates deletes and inserts.
OR
I can leave as it is so only when they view the ticket it does the calculation. Dont ask me how often they view it i dont have a clue.( if there is any suggestion how to get the count i could try to do it. ) :confused: Suggestions
(My attitude is going towards the trigger way but 100 percent sure)
Microsoft Data Transformation Services (DTS) Package 0 (1:Replicate D318_DOCKET) SubStep 'DTSStep_DTSDataPumpTask_2' failed with the following error: Provider generated code execution exception: EXCEPTION_ACCESS_VIOLATION
the package runs fine by itself from within Enterprise Manager but when i try to run the package from a Visual Basic project, i get this error. i tried to be careful to make sure i checked the "execute on main package thread" checkbox but it didnt make a difference...
I was wondering if anyone knew how to take a value from a subreport and compare it to a value iwthin the main report. We are trying to see if the value in the subreport is greater than the value returned in the main report. We want to make the value test red if it is greater than. I know how to compare these values if they were in the same report, but when it comes to subreports I am lost. Any ideas? Thanks in advance.
I have a query that gets a supplier, a month, a year, status and sum of recpits. returning the following. but my problem is I also need a col of totals. i tried to put a sub grouped query in the select statement but keep getting an error Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
SELECT ot_ven_num, ot_ven_name, sum(ot_rec_qty) as ot_rec_qty, ot_rec_stat, datepart(Month,ot_rec_dt) as mth, datepart(year,ot_rec_dt) as ryear, DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(mm, 0, ot_rec_dt), 0)) as ot_rpt_date, (SELECT ot_ven_num, ot_ven_name, sum(ot_rec_qty) as ot_rec_qty, 'Totals' as ot_rec_stat, datepart(Month,ot_rec_dt) as mth, datepart(year,ot_rec_dt) as ryear, DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(mm, 0, ot_rec_dt), 0)) as ot_rpt_date FROM supplierOT where ot_ven_name = 'ARROW ELECTRONICS' and datepart(year,ot_rec_dt) > 2007 group by ot_ven_num, ot_ven_name, DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(mm, 0, ot_rec_dt), 0)), datepart(Month,ot_rec_dt), datepart(year,ot_rec_dt)) as total
FROM supplierOT where ot_ven_name = 'ARROW ELECTRONICS' and datepart(year,ot_rec_dt) > 2007 group by ot_ven_num, ot_ven_name, ot_rec_stat, DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(mm, 0, ot_rec_dt), 0)), datepart(Month,ot_rec_dt), datepart(year,ot_rec_dt)
I have 2 reports where 1 is a subreport and the other one is a main report. Can i change or refer to a control(item) in the main report depending on the value of an item in the subreport, when the subreport is being run?
Is there anyway that i can access subreport field in main report? I just want it to be displayed. Thats it. I can not put subreport field in main report bcz it is based on the parameter passed by main report.
Here's my problem. I have a subreport that shows data and labels if data is returned. However, if no data is returned, the subreport doesn't show at all. I have a message on the subreport I would like to show if no data is returned, but the subreport won't show at all. Any ideas??
SUBREPORT (License info to the right) SHOULD PRINT TO RIGHT OF ADDRESS INFORMATION ON LEFT. LIKE THIS...
Name ABC-12345 Address line 1 DEF-12345 Address line 2 GH1-12345 City, State Zipcode
The data is from one table and looks like this. Both the main report and subreport use a shared data souce.
Name Address line 1 City State Zipcode License ------------- ------------------- ------------- --------- ---------- --------- Joe Schmoe 123 Maple Street Clearwater Florida 33777 ABC-12345 Joe Schmoe 123 Maple Street Clearwater Florida 33777 DEF-12345 Joe Schmoe 123 Maple Street Clearwater Florida 33777 GHI-12345
All lines are header lines. Subreport has been placed in top header line. IT IS PRINTING SIMILAR TO THIS BUT SHOULD PRINT AS FIRST STATED ABOVE...
Name ABC-12345 DEF-12345 GH1-12345 Address line 1 Address line 2 City, State Zipcode
ISSUE: I would like the subreport to PRINT ALONG WITH the address information to its left. In Crystal Report a subreport could "Underlay Following Sections" - a wonderful feature Microsoft maybe should have copied. Is there a way to duplicate "underlay" behavior in SQL Reporting ? Thanks.
UPDATE: Coworker found workaround. Can concatenate Name, Adress line 1, Address line 2, City, State and Zipcode into one field. Would still like to know a more straightforward way.
This is the part that I need help. I need to use an original value from the main report and a "return" value from the subreport. Main Report (Footer) Sum(FinalPayment) + SubReport.Sum(PaymentAdances)
Is it possible to pass a return value back to the main report from a subreport?
Here's my problem. I have an SSRS report with subreports on it. The main report and subreports work fine on my machine when I run the main report. However, when I deploy them to our test server and change the server name of the shared datasource, only the main report brings back data. The subreports show an error of something like "Error in displaying the subreport" for each subreport. The subreports require parameters that I believe I am supplying from the main report. What am I doing wrong? Is it really necessary to deploy the subreports with the main report to the new server. With Crystal Reports, I didn't have to do this. Any thoughts or suggestions would be appreciated.
I need to convert a Crystal report that contains three subreports. Each of these subreports uses a different stored procedure to obtain data. The main report uses data from these subreports to calculate averages in the footer etc.
How do i get the data contained in these subreports so i can use it in my main report?? Thanks in advance
I have a table which contains suppliers that can supply certain articles. There can be many suppliers to each article, and therefore there is a field that tells you if this supplier is the main supplier for the article, this field is called "arthuvudavt". However, you aren't forced to use this field in the table. As a alternative you can use priority. Each supplier has given priority where 0 means "most preferred" and any other means "less preferred" What I want to do is to pick the right supplier for a given article in one SQL-statement regardless of if you choose to use the priority or if you choose to use the main supplier field.
In the above case, I'd like the statement to return supplier 10202 for article A-10 (the one that has the arthuvudavt checked), and only one row for B-10, which one doesn't really matter, since someone has been making a fault here, this should not happen, but my SQL-statement should break because of this scenario. For C-10 supplier 10303 should be returned.
Is there any way this could be done?
The reason I ask is that I try to learn new ways of solving things, and this problem was kind'a easily solved using variables, but I was wondering if it was possible to do it without it.
Thanks in advance Jonas
Sorry for the poor formatting, I didn't have the time to make it right.