Dear friends
I am a new developer. I need some SQL Language examples to write some quaries.
Ex.
how you collect last record that relevent for the last record updated for a relevent employee in a record table which includes RecordNo and EmployeeNo, salses
Thanks
Any help is greatly apprciated
Amila
Hi,e.g.
SELECT TOP 1
RecordNo, EmployeeNo, salses
From SomeTable
WHERE EmployeeNo = 'SomeValue'
ORDER BY TheColumnWheretheSalesDateisStored DESC
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Hi,
I neew a small sample code in VB.NET to create a custom data flow component for SSIS.
All the examples I was able to find it are in C#.
Answer very apreciated.
Go to http://msdn2.microsoft.com/en-US/library/ms136112.aspx
Thanks,
Loonysan
http://mystutter.blogspot.com/
SQL Server ships with several simple samples of SSIS components. Please install the SQL samples. The December update is available from MSDN at http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en.
-Doug
Here is an example. Collate ensures that the CustomerID is compared based on the same collation.
Code Snippet
select c.customer_full_name, count(*) cnt
from customers c join orders o
on c.CustomerID collate Latin1_General_CS_AS = o.CustomerID collate Latin1_General_CS_AS
|||thanks,
It could be helpfull if u explain what is a collate why we should go for collate|||Depending on the collation of a table/database, queries can give different results or may not even work at all (in the case of a join between 2 columns with different collations).
Check out Books Online:
http://msdn2.microsoft.com/en-us/library/ms143726.aspx
HTH!
|||HI,
Could you give example of different collation of table like table structure so that i can work and clarify.
Why i need structure is in the below example i placed "join" in place collate it showed error.when i replaced it to collate it works fine with SQL2000..need help.
Select
c.Version_id,wit.catalog_tour_id,wit.contentsite_id,wit.sequence,wit.dayrange,wit.short_description,wit.long_description,
'Photon',getdate(),'Photon',getdate(),0,wit.itinerary_image,wit.itinerary_image_description
From webeftours..TourItinerary wit(NoLock)
Inner Join CatalogTour c
on wit.Catalog_Tour_id = c.Catalog_tour_id collate SQL_Latin1_General_CP1_CI_AI
WHERE wit.catalog_tour_id in (
'GSJ D'
)
From Books Online:
Code Snippet
USE tempdb
GO
CREATE TABLE TestTab (
id int,
GreekCol nvarchar(10) collate greek_ci_as,
LatinCol nvarchar(10) collate latin1_general_cs_as
)
INSERT TestTab VALUES (1, N'A', N'a')
GO
SELECT *
FROM TestTab
WHERE GreekCol = LatinCol
GO
SELECT *
FROM TestTab
WHERE GreekCol = LatinCol COLLATE greek_ci_as
I would suggest in your case, the defaut collation of the database webeftours is different and incompatible with the database containing CatalogTour.
You can change the collation of a column using ALTER TABLE. You can also change the default COLLATION of a database (ie when a collation is not specified in DDL statements, it takes the database default) though only new objects will have the new collation. You'd have to change existing objects manually.
Thanks,
When i tried to create the table it throws an error(Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'collate'.)..need help
Can you post the statement you are trying to execute?
|||use scratch
go
CREATE TABLE TestTab (
id int,
GreekCol nvarchar(10) collate greek_ci_as ,
LatinCol nvarchar(10) collate latin1_general_cs_as
)
What version of sql server are you running that query on? If 2000 or above, can you check what the result of:
sp_dbcmptlevel 'scratch'
If its 70 or below then that may be your problem.
|||Result i got is"The current compatibility level is 70."|||Ok, you need to update your compatibility level.
If you are running SQL2000 run:
sp_dbcmptlevel 'scratch', 80
This should solve the syntax problem.
Good luck!
|||Collate was introduced in sql2k. So, you'd want to set your data compatibility to 80.
Code Snippet
exec sp_dbcmptlevel 'your_db_name', 80
