SELECT [template].[id], [template].[navn], [tvalues].[id]
FROM template LEFT JOIN Tvalues ON [tvalues].[templateid]=[template].[id]
This returns ok. But I have a clause on the Tvalues like this:
WHERE [tvalues].[nr]=1;
Now I dont get what I want anymore? What I mean here is:
"return all records from Template and the corresponding values in TValues.
If no corresponding values can be found or those where TValues.nr<>1 then
return NULL".
There I was merrily working away on my SSIS package, when one of our lovely network people completely wiped the drive my SQL Server database was sitting on. I was a bit miffed.
I was even more miffed when I found out after restoring the database that the package I was editing would no longer connect to it. I've removed and recreated all my datasources and connection managers and no luck.
I get a number of errors - When trying to execute a simple task to take populate a table in my SQL Server database from a remote source I get: [ODD - PMP10 in ORBIT STAGING [652]] Error: SSIS Error Code
Code SnippetDTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ORBIT STAGING" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. I can however, connect to the database in design mode, using the same connection parameters - I can create tables too, so access rights look ok.
When trying to run a for each container that uses an ADO Connection, I get:
Code SnippetError 1 Error loading Orbit Extract.dtsx: The connection "ORBIT_STAGING - ADO.NET" is not found. This error is thrown by Connections collection when the specific connection element is not found. D:DevORBITExtractOrbit Extract.dtsx Well, the connection does exist, so it must be a corrupt internal pointer or something...
If I double click on the errors, It takes me to the top of the XML file, and I'm loath to mess with this directly, as I don't know what is what here...
I did something bad (I don't recall what it was) and I can no longer log into Reporting Services. I wish I could list everything I've tried but I've been at it so long I don't remember. And I've been through the ringer of error messages.
Is there a way I can completely reset and/or restart all the settings from the original install - without doing a new install? I am afraid I am going to mess up SSAS, SSMS, SSIS and something else.
When I try to view my report manager from the computer that SSRS is installed on, I cannot do any administration on it. How can I get this back?
Here's some more info: I browse to http://localhost/reports on the machine and it only shows links for "Home", "My Subscriptions" and "Help". there are no options to add a new folder, set up security on the root folder or any admin functions.
I have this #@!% installed on my machine and when it failed I made the major error of following some telephone instructions from their "help-less" support desk - - - well their $#@% products now runs again (oh whoopie) -but- nothing else related to sql server will work. This includes: Visual Developer 2005 Express - Database Explorer SQL Server Configuration Manager, Surface Area Configuration, etc., etc. (not found) I tried to re-install SQL Server -but- the install fails, fails, fails... The failures are always with a corrupt msi file that has just been downloaded / expanded - yet fails??? I have registered and sent error reports -but- I still don't have a functional sql server version running and available. I do have "their" version of SQL Server running again - this time it is named my-machine-nameGOFIGURE Like an idiot I deleted folders and un-installed sql server (as per 'their' instructions) NEVER again - if I ever get things back to "normal"
WHAT should I do - I would 'like' to continue to run their software -but- NOT at the expense of everything else.
If I have to uninstall anything - please list - thanks :)
Using SQL Server 2000 and moving to a new computer. We did a full backup ofthe existing database to tape, brought up the new computer with a cleaninstall using the same server name and IP address, and did a full restore.Not only were some permissions messed up, but Crystal Reports 10 and someAccess Data Projects refused to run. I finally discovered while running anSP_WHO that the individual database names that we'd created (meaning not'master' and the other standard tables) had several dozen blanks appendedonto the end of them. Looking at dbnames in the SP_WHO made it clear thatthis had happened, and once I knew what I was looking for it was apparent inEnterprise Manager as well when I'd select a database name in the left pane.Interestingly, VB6 applications have no trouble connecting to these tableswithout modification of the connection string. Every single CR10 report sofar has had to have it's tables relinked, and this has broken some othercode that looks at dbnames.1: How could something like this happen?2: How is it best fixed?Thanks!David
I need to import a unicode text file with a DTS. The textfile needs to be imported width fixed column width settings as there are no field delimeters.
The data in the file is messed up (some columns are concatenated) when opened in Notepad.
The data looks fine when opened in Wordpad and also all fields are nicely delimeted but then I end up with unicode characters which are not supported in Wordpad.
As the data for the application is growing by leaps and bounds, we were shortly going to run out of disk space on one and decided to move production to a new, larger box.
I took a full backup of the current production database nad loaded it onto the new server with no problems except one.
When users tried to log in, there were problems. Apparently the passwords for users were switched with other users or somehow the sysusers data got reversed. THe logins, or the data from msyslogins, looked fine.
I don't understand how a full backup and then a restore would have any effect on the sysusers data.
Has any one experienced this before? Other than bcp out the data from sysusers and bcp'ing the data after the restore, is there any thing else I can do?
Does any one have a script tp offload sysusers data?
Any help which can be provided would be appreciated. THanks.
I installed the evaluation version of SQL Server 2005 and now my DSL connection on Windows XP is all fouled up; I'm using Server 2003 at the moment. Will uninstalling it get rid of the problem? First Oracle eats my DSL connection, now SQL wants a piece! :eek:
Recently moved a couple of dbs from SQL 2000 -> SQL 2005 (different machine). Just restored the dbs on the new server from a full backup of the old one. Everything is fine but...
Some queries are returning out-of-order results (for records added since the migration). I tracked this down to the indexes. I completely rebuilt and reorganized the indexes (online) through the manager interface. This helped provide correct ordering for existing records, but new records added since the re index are still out-of-order.
Looking for a way to permanently fix the indexes so I don't have to constantly rebuild them.
I am using SQL7 Query Analyzer. A simple select * from myMLSview andthen I save results as a .csv file has the commas messed up in quite afew places. The data is messed up before I save it to the .csv file. Sothere are blank spaces being added here and there causing them to readas 'add comma' when saving to .csv format. Even the column data ismessed up... example: ,photo_mod_time,photo_mo,d_date, should read,photo_mod_time,photo_mod_date,I have a view on a remote MS-SQL7 Server, the view has about 200columns and the data returned from the my querys can be from 10 to 25MBin size. Connection is via Roadrunner/cable. Should I use anothermethod, maybe command line? Any suggestions?
I have (had!) SQL Server 2005 Express and Visual Studio Web Express 2005 (still do) installed but need to write some Reporting Services reports. I have now bought a Sql Server 2005 Developer license (£40 version) and have installed it. However I couldn't find any new installation in my program files so i uninstalled it and the express version and tried to reinstall the Developer edition but it kept stopping with all the options greyed out saying there's nothing to install. So i managed to install as an instance, can't install the 2nd disc though ?? Am i doing something wrong here ? how can i satrt again ??
I am using expressions for the textboxes in the Table control Header, because the header names should be displayed in both English as well as in Japanese based on the language selection.The report works fine and all the render formatts except CSV are working fine.when i export this report to CSV, the header names are not coming in the first row of CSV , but some other textbox names (eg textbox 34..) are being displayed on the first row of CSV.From second row onwards, i am getting the header names seperated by comma and the data is being displayed.This header names are being repeated for all the rows in the CSV along with the data.Please give me a solution regarding this.
I tried by setting Data Element as "NO" from "Auto".I could stop the header names being repeated from second row in CSV, but i couldnot get the names in the first row of CSV.
I need to have all the header names as first row in csv and from the second row, i need data.
I have a Data-Flow task embedded in a Sequence Container (does not fail component on error) on the Control Flow panel of the SSIS designer. This data flow task contains a connection to a Flat File Source -> A Data Transformation -> Into an OLE Db Destination.
The problem is that the Flat File isn't always delimited properly -> the client cannot be relied on to do this.
My question is when the delimiters are messed up, how can I capture the offending error row(s) from the Flat File Source?
What I've tried: 1) Set every column in the source flat file on error to: Redirect Row 2) Added a Script Transformation to pull the description and the record id out of the offending row 3) Added an Error file flat file destination to the end of the flow.
The package always fails on the Flat File Source and never Redirects the offending Row to the error output - I never see my onError Script Transformation go Green, Red, or Yellow - SSIS doesn't let it get there.
I'm really new to SSIS so sorry if this is a super basic question.
Here is the Error Text:
[Source - InventTable_csv [1]] Error: The column delimiter for column "RECID" was not found. [Source - InventTable_csv [1]] Error: An error occurred while processing file "C:------InventTable.csv" on data row 15228. [DTS.Pipeline] Error: The PrimeOutput method on component "Source - InventTable_csv" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. [DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.
I do have the MaxErrorCount set to 1 on the Data Flow Task but still think I should see my script task execute and a log entry be generated.
I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?
I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....
I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.
Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?
Hi... I'm reading the MS Press 70-442 Self-Paced Training kit, and I'm having problems with this example. I'd like help getting it to work correctly, or understand why it is isn't working the way I planned.
On page 67, the lab is about the APPLY operator (CROSS APPLY and OUTER APPLY). I first have to input a sample table-valued function into the AdventureWorks database:
Code Block CREATE FUNCTION fnGetAvgCost(@ProdID int) RETURNS @RetTable TABLE (AvgCost money) AS BEGIN WITH Product(stdcost) AS ( SELECT avg(standardcost) as AvgCost FROM Production.ProductCostHistory WHERE ProductID = @ProdID ) INSERT INTO @RetTable SELECT * FROM Product RETURN END
and then run a sample T-SQL statement
Code Block SELECT p.Name, p.ProductNumber, Convert(varchar, cost.AvgCost,1) AS 'Average Cost' FROM Production.Product p CROSS APPLY fnGetAvgCost(p.ProductID) AS cost WHERE cost.AvgCost IS NOT NULL ORDER BY cost.AvgCost desc
My problem is with the WHERE clause... According to page 56, CROSS APPLY returns only rows from the outer table that produces a result set, so why do I need to explicitly filter NULL values?
When I remove the WHERE clause, the query retrieves lots of NULL AvgCost values.
Again, according to page 56, it is the OUTER APPLY that returns all rows that return a result set and will include NULL values in the columns that are returned from the table-valued function.
So, in short, I don't see the difference between CROSS APPLY and OUTER APPLY, using this example, when I remove the WHERE clause?
(Please refrain from introducing another example into this question.)
Hi, can anyone shed some light on this issue?SELECT Status from lupStatuswith a normal query it returns the correct recordcountSELECT Status from lupStatus GROUP BY Statusbut with a GROUP By clause or DISTINCT clause it return the recordcount= -1
ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id
GROUP BY dbo.Track_ID.TrackID
)
) Once moved to SQL Server 2005 the statement would not return and showed SOS_SCHEDULER_YIELD to be the waittype when executed. This machine is SP1 and needs to be upgraded to SP2, something that is not going to happen near time.
I changed the SQL to the following, SQL Server now runs it in under a second, but now the app is not functioning correctly. Are the above and the following semantically the same?
UPDATE dbo.Track_ID
SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed
1) Rows ordered using textual id rather than numeric id
Code Snippet select cast(v.id as nvarchar(2)) id from ( select 1 id union select 2 id union select 11 id ) v order by v.id
Result set is ordered as: 1, 11, 2 I expect: 1,2,11
if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
2) SQL server reject query below with next message
Server: Msg 169, Level 15, State 3, Line 16 A column has been specified more than once in the order by list. Columns in the order by list must be unique.
Code Snippet select cast(v.id as nvarchar(2)) id from ( select 1 id union select 2 id union select 11 id ) v cross join ( select 1 id union select 2 id union select 11 id ) u order by v.id ,u.id
Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
It reproducible on
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
and
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
In both cases database collation is SQL_Latin1_General_CP1251_CS_AS
If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.
Could someone clarify - is it bug or expected behaviour?
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out How i can fetch the lotus notes data in my asp.net pages.
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out How i can fetch the lotus notes data in my asp.net pages.
what is differance between conditions put in ON clause and in WHERE clause in JOINS????
see conditions that shown in brown color
select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate, d1.Audit, d1.ExpirationDate, d1.Indicator from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2 on d1.SummaryID=d2.SummaryID AND d1.ListType = d2.ListType AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null) AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null) whered1.ImplicitID >= d2.ImplicitID AND (d1.SourceID<>d2.SourceID OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL) OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL) )
select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate, d1.Audit, d1.ExpirationDate, d1.Indicator from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2 on d1.SummaryID=d2.SummaryID AND d1.ImplicitID = d1.ImplicitIDAND d1.ListType = d2.ListType AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null) AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null) whered1.ImplicitID >= d2.ImplicitID AND (d1.SourceID<>d2.SourceID OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL) OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL) )
another thing...
if we put AND d1.ImplicitID = d1.ImplicitID condition in second query then shall we remove d1.ImplicitID >= d2.ImplicitID from WHERE clause????
Hi everyone, I saw some queries where SQL inner join clause and the where clause is used at the same time. I knew that "on" is used instead of the "where" clause. Would anyone please exaplin me why both "where" and "on" clause is used in some sql Select queries ?
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave
Hey guys, I'm a bit weak when it comes to doing ands and or's. I know what i want, but when I put it into statement, i dont get the results that i want. I have 3 fields in my where clause. ID, LW, and LWU. The code is as follows:WHERE (LASTVISIT BETWEEN '1 / 1 / 95 12 : 00 : 00 AM' AND '1 / 1 / 06 12 : 00 : 00 AM') AND (ID NOT LIKE '%6%') AND (ID NOT LIKE '%7%') AND (ID NOT LIKE '%8%') AND (LW <> 1) AND (LWU <> 'test') OR (LASTVISIT BETWEEN '1 / 1 / 95 12 : 00 : 00 AM' AND '1 / 1 / 06 12 : 00 : 00 AM') AND (ID IS NULL) AND (LW <> 1) AND (LWU <> 'test') I have a range of dates that I want to grab, in there I do not want any records where ID has 6,7,8 and I only want records where LW does not equal 1. UP to this point, it works fine. I get all the records that only return these values. However, the moment I add where LWU does not equal 'test'. it does not return the values I want. Furthermore, why can I not put this whole string into one and clause? I never understood why I had to create a second line following OR. the longer this query gets the more I get confused. Any help?
I have an insert statement that reads: SELECT AppointmentID, PatientNo, PatientSurname, PatientForename, ConsultantName, HospitalName, Date, CONVERT (varchar, Time, 8), AppointmentStatus FROM [Appointment] WHERE ([AppointmentId] = @AppointmentId) I also need to add another WHERE clause. This clause will mean that if the date is within 14 days of the actual date it will not ba able to be selected need help writing this not sure how to write it Thanks in advance Mike.
I'm having a heck of time with this where clause. I have a table that contains client addresses, a client can have more than one address. So some of the addresses may be seasonal. I need to return only the current address based on a flag MailTo (bit) and a date range, just the month and day, the start and end are datetime datatypes.
Here is what i have tried:
I would really would like it to work on a range of month and day based on the startdate and enddate fields and the MailTo flag. The table looks like this;
I am working on a project involving text searching. I created a fulltext catalog on the database and scheduled it for every one minute. I created a fulltext index on a table and added some columns. I scheduled it as the database catalog. I ran a simple query like this in the query analyzer but got an error message that the catalog does not exist!
SELECT * FROM tbl_extra_skills WHERE CONTAINS(ITSkills, 'Word')
hi alli need to create a sql statement that receives some values - my doubt is only about how to build that sql statementi've heard something about IN clause but could not apply it - could someone give any sample?First page: I have a textbox with some emails e.g. a@a.com, b@b.com, c@c.com etcSecond page: SELECT * FROM Table1 WHERE Field = ... IN ???thanks in advance
