I have to determine the "standing" (WIN - TIE - LOSS) from
confrontations between two teams on a contest. The table matchResults
has fields cont_id, team_id and contest_result (int).
In one recent project we used a concept of views to validate and modify our data in the DB table. We wrote several views, each contained certain modifications or validation routines. Each view used as input the result of the previous one (in the stack) and its output served as input for the next view.
At the present we are going to exploit SSIS more instensively. And I have question/issue: are there any benchmarks or tests about performance of SSIS transformations (like "Charachter mapping" or "Condiitonal split") in comparison with views? This would be interesting to know...
I'm so desperate, I'll pay $100 to the proper solution to this problem. I'm sure it's an easy fix, but I'm wasting more money every day trying to figure it out...
I have a table with hierarchial data in it (see the bottom tabledef) and I need to query an "indented outline" of the records in it for a tree control on a website. To do that I have to perform some sort of recursive or nested query or I can do all that manipulation in a temporary table/cursor... However, even though the resultset will display when I check the query, when I try to open it using ADO, I get a recordcount of -1.... it's very frustrating and extremely important.
I'd rather pay an expert here than try to navigate a tech help line.
ConnIS is defined in an earlier include file...
Set oCmd = Server.CreateObject("ADODB.Command") Set oCmd.ActiveConnection = ConnIS oCmd.CommandText = "dbo.Expandset" 'Name of SP oCmd.CommandType = adCmdStoredProc 'ADO constant for 4 Set oTmp = oCmd.CreateParameter("@current", adInteger, adParamInput,, 892) oCmd.Parameters.Append oTmp Set oRs = Server.CreateObject("ADODB.Recordset") oRs.Open oCmd Response.Write oRs.RecordCount & "<hr>" oRs.Close Set oRs=Nothing
This code generates the following result when run from an active server page:
-1<hr>
When I execute the raw SQL code ("exec Expandset 892") against the stored proc in the query analyzer, I get:
INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache
When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'."
Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values.
The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why.
I need to run a SELECT DISTINCT query acrossmultiple fields, but I need to add another field that is NON-DISTINCTto my record set.Here is my query:SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, genderFROM gpresultsWHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis query runs perfect. No problems whatsoever. However, I need toalso include another field called "admitdate" that should be treatedas NON-DISTINCT. How do I add this in to the query?I've tried this but doesn't work:SELECT admitdateFROM (SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, gender from gpresults)WHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis has to be simple but I do not know the syntax to accomplishthis.Thanks
Hello, I have written a small asp.net application, which keeps record of the proposals coming from the branch offices of a bank in a tableCREATEd as a TABLE Proposals ( ID smallint identity(7,1), BranchID char(5), Proposal_Date datetime ) This app also calculates the total number of proposals coming from a specific branch in a given date bySELECTing COUNT(BranchID) FROM Proposals WHERE BranchID=@prmBranchID AND Proposal_Date=@prmDateand prints them in a table (my target table). This target table has as many rows as the result of the "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals"and excluding the first column which displays those DISTINCT Proposal_Dates, it also has as many columns as the result of the"SELECT DISTINCT BranchID FROM Proposals". This target table converts the DateTime values ToShortDateString so that we are able to see comfortably which branch office has sent how many proposals in a given day. So far so good, and everything works fine except one thing: Certain DateTime values in the Proposals table which are of the same day but of different hours (for ex: 11.11.2005 08:30:45 and11.11.2005 10:45:30) cause some trouble in the target table, where "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals" is executed, because (as you might already guess) it displays two identical dates in ShortDateString form, and this doesn't make much sense (i.e. it causes redundant rows) What I need to do is to get a result like (in a neat fashion :) "SELECT COUNT( DISTINCT Proposal_Date ) <<DISTINCT ONLY IN THE DAYS AND NOT IN HOURS OR MINUTES OR SECONDS>> FROM Proposals" So, how to do it in a suitable way? Thanks in advance.
Hey, take a look at the below. I need to sum the real balance of a line with the credit with the line above, or, n + 1. I don't wanna to have to walk across the lines to do the sum. Any idea of how to do that in a easier and better way? I tried using T-SQL, but I couldn't figure out how to do a reference to the next line on the SQL without moving the cursor.
Date Reference Description Debit Credit Real Balance Bank Balance
I'm using SQL mail to send an email from a stored procedure which works fine. However I need a line break in the middle of this email which I am struggling to achieve. I've tried adding char(13)+char(10) to the middle of the email body string but this does not seem to do anything. Any ideas?
I am using SQL Server 2005 Reporting Services and all my reports preview with many more pages, as the preview will show Page 1 of 20, but if I print the report I get say 12 pages. The reports are always correct, but the preview show less data per page? Is there a way to get the preview to be what is going to print out?
This occurs in MS Visual Studio 2005 also as well as from the web. On a drill down report I get on some reports only 10 line when the print out will have 30 lines perpage
I am generating hundreds of lines of sql with a tool and wish to execute is in runtime. The sql consists of table creation, procedure creation, aswell as inserts and updates. When i try with SqlDataAdapter I get an error. Does anyone have a solution on what I can do?
hi im making a page where i want a single line at a time to be pulled from my MS Database, basically at the moment i have a list of questions, but the page is displaying all the questions from my database, i only want it to pull out 1 and then if the user clicks the true button then it goes to another page and displays another question?
I take some data from a textbox and then dump it in to SQL.. and then try to show it on the page but the new lines in code dont show in HTML.I grab the data via a <%#Eval(xxx>%>so doing a replace isnt really an option there...any other ideas?
Assuming I have a line, is there a function I can call to create a parallel line at a given distance away.i.e - with the below I would want to draw a parallel line to the one output.
We have a very long and complicated SQL script which we run to upgradea version of our software from old to new. It works great in QueryAnalyzer, but when run through osql it takes errors on lines that arevery long and (I think) stops reading after a certain amount ofcharacters. I've searched the net but haven't found anyone mentioningthis before. I have tried the -w 5000 parm to no avail.Any suggestions?
I used to be able to enter new lines into the result pane cell for text(and varchar) data in Enterprise Manager, but now that I am using SQL2005 Management Studio, this feature is gone.Is there any way to do this?Also, copying to/from excel chops off part of the text in a cell and isvery infuriating.Any help would be appreciated.Dan
Hi, The chart is set to simple line I can not get my SSRS chart to show just the dots, instead it is joining the points together and show the points joining together and therefor appear as lines.. even tried setting the chart to simple scatter but then nothing appears on graph. Any thoughts please?
i wanna create two line types on the same graph. I'm using the following queries.
Code Block select count(l.created) as LoanOriginationNumberPreApproved , convert(char( 11), l.Created) as PreApprovedDate from LoanApplication l where l.Status <> 'Cancelled' Group by l.Created
and
Code Block select count(l.SubmittedOn) as LoanOriginationNumber , convert(char( 11), l.SubmittedOn) as SubmittedDate from LoanApplication l where l.Status <> 'Cancelled' Group by l.SubmittedOn
I combined these queries. I add clumns of the second query to select statement of first code. also added group by l.submittedon to group by l,created as group by l.created, l.submiited on. This is the problem.(I mean grouping)
But then SSRS does not allow me to put preaaproveddate and submitteddate to drop category filed? Does anyone know how to do that?
"Document contains one or more extremely long lines of text. These lines will cause the editor to respond slowly when you open the file. Do you still want to open the file."
I click Yes and my project open normally. Someone know why this happen? My project is small, have one package with any imports excel files to Sql Server 2005.
I would like to know how to set the grouping lines or the detailed line to get the function like in Excel: group plan Example:
+ Group line GL1 Detailed Line 1 Detailed Line 2
When the grouping function is active, you see the Group line GL1 only. By clicking on the + icone, you will see all the detailed lines (like in Excel document).
I have a multiline textbox where users enter comments, then the comments are put into a SQL table, then a datagrid retrieves the comments from the table and displays them. The problem is when a user has blank lines between paragraphs, the datagrid simply puts the paragraphs together. I know I can use the <br> command to display the text properly in the grid, but how do I get the <br> command to store in the SQL table where the paragraph breaks would be. The paragraph breaks go into the SQL table as nothing more than several spaces. How do I get this to work?
I have an app which uses SQLDataSource to update the information to database (SQL Server 2000). Is there a way to trim the input of users somehow (blank lines) ? For example if I have an Comments-textarea on my form and users enter text to it, can I somehow take out the extra blank lines out of that textarea before submitting the data to database ? I know how to do this programmatically, but was just wondering if this could be done when using SQLDataSource-control. I'm using DataReader to extract the information from database to an CSV-file. Because of the blank lines, occasionally CSV are really messed up because of that. Is there a way to remove the blanklines when iterating the DataReader values ?
I import from CSV files into MSSQL tables. My hierarchy absolutely wants to keep the CSV standard format. Some string have quotes inside. With Sybase it jumps these corrupted lines. With MSSQL and DTS, it breaks all the importation.
Does anyone know how to jump (aloso is there a possibility to detect the number of corrupted lines, Sybase can do that) ?
Hi All, I'm saving the output of a query into an html file. The dashed lines above rows that have a select in it are showing up in the final doc and are playing havoc with my page. Does anyone know of a command to stop them from appearing. nocount just suppresses the rows affected message. ANY help is appreciated.
Hi, I need to convert from mssqlto Postgres and I need to export all MS-SQL table data to a CSV or TXT file (one file per table)
Presumably, all data per row (of a table) must be in one line. Then when you copy to another database, a new line of data means a new row in the table.
However, MS SQL is exporting a large varchar text field as multiple lines. The data itself is many lines, so exporting it causes the data for one row to fall onto many lines.
My question: How do I escape new lines? When MS SQL exports the data, I want to replace all NEW LINES / carriage returns by /n or by <br> tag (since the data will be for web use).
(pls note I am not actually handling the ms sql database, so any response would be greatly appreciated as I advise the person in charge of the mssql db accordingly).
I am running this stored Prcedure and getting multiple lines for the output. Below are the queries the create the temp table (its holding the data), and the query that generates the output:
/* this creates the temp table */ Select count(*) as 'Donations', d_vst_id into Donations_temp from dnr_vst_db_rec where convert(varchar(10),d_vst_date) between convert(varchar(10), @Beg_Vst_Date, 112) and convert(varchar(10), @End_Vst_Date, 112) and d_vst_status = 'DN' and d_vst_dontyp in ('E1', 'E2') group by d_vst_id
/* this query generates the output */
select distinct cast(getdate() as varchar(30)) as 'TODAY' ,CONVERT(varchar(10), @Beg_Vst_Date,101) as 'BEGDTE' ,CONVERT(varchar(10), @End_Vst_Date,101) as 'ENDDTE' ,case Donations when '1' then sum(1) else 0 end as 'ONE1' ,case Donations when '2' then sum(1) else 0 end as 'ONE2' ,case Donations when '3' then sum(1) else 0 end as 'ONE3' ,case Donations when '4' then sum(1) else 0 end as 'ONE4' ,case Donations when '5' then sum(1) else 0 end as 'ONE5' ,case Donations when '6' then sum(1) else 0 end as 'ONE6' ,case Donations when '7' then sum(1) else 0 end as 'ONE7' ,case Donations when '1' then Sum(0) when '2' then Sum(0) when '3' then Sum(0) when '4' then Sum(0) when '5' then Sum(0) when '6' then Sum(0) when '7' then Sum(0) else Sum(1) end as 'ONEA' from Donations_temp group by Donations
Does anyone know a wayto compress data between two database connections over "low bandwidth" lines in order to speedup datatransfer? Used connections are Oracle<->SQL2000 and SQL2000<->SQL2000.
I have a DTS that reads in a bunch of transactions daily to a tran history table. I read them in from a text file each day. The problem is, that about half of the lines in the text file contain semicolons because they are comments. What I do now is, import the whole thing, and then do a Delete on my tran hist table for all lines with semicolons. As my tranhist table grows, this Delete will start to take a long time. How do I filter it so it doesn't even import the lines with semicolons to start with, that will run faster and save me time later.
