|
big_nige wrote: Initial Catalog=SHAKTI" provider
Why do I see an extra quote after SHAKTI?
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Hi,
How can I create a DTS Package using Sql-DMO in a Stored Procedure. I am
using Sql Server 2000.
Saswat.
|
|
|
|
|
1-To connect to the distribution database and provide published data to the DTS package (source connection), a special OLE DB provider, the Microsoft SQL Server Replication OLE DB Provider for DTS is used. When you create a transformable subscription, this provider is installed automatically on the
DTS package, and cannot be changed. This provider can be used only with transformable subscriptions.
The connection from the DTS package to the Subscriber (destination connection) does not use the Microsoft SQL Server Replication OLE DB Provider for DTS; it uses whatever OLE DB provider is required to connect to the Subscriber. For example, you would use the Microsoft OLE DB Provider for SQL Server to send transformed data to a Microsoft® SQL Server™ 2000 Subscriber.
2-Only SQL Server (the Microsoft OLE DB Provider for SQL Server) Subscribers and other OLE DB Subscribers can use transformable subscriptions; ODBC Subscribers will not work with transformable subscriptions.
Rami Abdalhalim
|
|
|
|
|
I'm writing an ASP .NET 2.0 application, and I have the following code:
Public Function GetRow(ByVal Id As Guid) As DataRow
Return Me.mDataSet.Table(0).Rows.Find(Id)
End Function
But an exception is thrown at the line: Return Me.mDataSet.Table(0).Rows.Find(Id). I copy the exception details bellow:
Error de servidor en la aplicación '/Proyecto03'.
--------------------------------------------------------------------------------
Se esperaban los valores 2 para la clave indizada, pero se han recibido valores 1.
Descripción: Excepción no controlada al ejecutar la solicitud Web actual. Revise el seguimiento de la pila para obtener más información acerca del error y dónde se originó en el código.
Detalles de la excepción: System.ArgumentException: Values 2 expected, but instead values 1 were received
This is anoying, because the Find method has two overloaded versions: one accepts an Object as parameter and the other accepts an array of Object, so a Guid object should pass without problems. The only thing that comes to my mind is this: There is a special way to make it with the Guid type, but I can't find anything neither the MSDN Library, nor the internet.
Please, help!!!
Bye
|
|
|
|
|
reinaldo.aru wrote: Se esperaban los valores 2 para la clave indizada, pero se han recibido valores 1.
Descripción: Excepción no controlada al ejecutar la solicitud Web actual. Revise el seguimiento de la pila para obtener más información acerca del error y dónde se originó en el código.
Detalles de la excepción:
To tell you the truth I dont understand any of that!
but as far as ur error is concerned I think what ur doin is that you're passing a single object of GUID type to Find and if you see its discription it says that the method takes the primary key value. I think your primary key must be something else and its datatype is not GUID hence the error... (I can be wrong too coz its an assumption)
Rocky
You can't climb up a ladder with your hands in your pockets.
|
|
|
|
|
I developed a web application usin c# and mysql as my database. Deploying it to my web server is giving me the following error message:
Configuration Error
Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.
Parser Error Message: Could not load file or assembly 'MySql.Data, Version=5.0.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d' or one of its dependencies. The system cannot find the file specified.
Source Error:
Line 20: <compilation debug="true">
Line 21: <assemblies>
Line 22: <add assembly="MySql.Data, Version=5.0.3.0, Culture=neutral, PublicKeyToken=C5687FC88969C44D">
Line 23:
|
|
|
|
|
Have you installed the MySQL provider libraries on the server? .NET does not support MySQL out-of-the-box, as far as I know.
|
|
|
|
|
I'm having to store the Year-End associated with accounts. While there is a static list of 12 dates that have been provided for me, the final day of each month, I wondering about what year value to use when I store these values into a database with a datetime type. I will be using the value to initialise other datetime types for specifying date ranges for subsequent query operations. For example, consider an account that is define to have a year-end of May 31. If I have to provide quarterly information on this account, then going forward I will be generating date ranges such as June 1 2007 to August 31 2007, September 1 2007 to November 30 2007 and December 1 2007 to February 29 2008. I now how I'm going to be generating these date ranges, but just wondering if there is a best practice for stroing year end dates where you ignore or don't care about the year value? Thanks.
Chris Meech
I am Canadian. [heard in a local bar]
|
|
|
|
|
You may want to look at AdventureWorksDW DimTime table. Its a OLAP concept where you use a DayTimeKey to refer to a lookup table DimTime for actual date and other pre-aggregated data.
You might be able to insert just one date for every day with FiscalYear and then use
SELECT [FiscalYear]
FROM [dbo].[DimTime]dt
INNER JOIN MyTable t1 ON CONVERT(varchar(23), t1.DateColumn, 101) = dt.[FullDateAlternateKey]
CREATE TABLE [dbo].[DimTime]
(
[DayTimeKey] [bigint] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[FullDateAlternateKey] [datetime] NULL,
[DayNumberOfWeek] AS (datepart(weekday,[FullDateAlternateKey])),
[DayNameOfWeek] AS (datename(weekday,[FullDateAlternateKey])),
[DayNumberOfMonth] AS (datepart(day,[FullDateAlternateKey])),
[CalendarYear] AS (datepart(year,[FullDateAlternateKey])),
[MonthName] AS (datename(month,[FullDateAlternateKey])),
[MonthNumberOfYear] AS (DATEPART(mm, [FullDateAlternateKey])),
[CalendarQuarter] [tinyint] NULL,
[CalendarYear] [char](4) NULL,
[CalendarSemester] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL,
[FiscalYear] [char](4) NULL,
[FiscalSemester] [tinyint] NULL
)
Dave Evans
|
|
|
|
|
Thanks for the comment, Dave. That looks to be a little more than what I'm after, but it has given me an idea of using a separate table to contain all of the date ranges I may every use and then have an identifer map to the date range.
Chris Meech
I am Canadian. [heard in a local bar]
|
|
|
|
|
Hi.
How can I select some (5 for example) random record from a table?
i.e I want to select 5 records in random from a table ?
Best wishes
|
|
|
|
|
Use the function NEWID() to calculate a GUID for each row and sort by that.
SELECT TOP 5 *
FROM [MyTable]
ORDER BY NEWID()
-- modified at 14:54 Thursday 23rd August, 2007
|
|
|
|
|
I do something similar; I have a table with two columns, one for IDs and one for random numbers.
The benefits are:
A) I don't have to add a column to the main (Employee) table
B) I can weight the selection process; that is, some records (employees) may have multiple entries
in the "hat", or none at all
The table is normally empty, but when it's time to select (Employee) records, I populate it, and make my selections (after an employee is selected I remove all the entries for that employee).
Then I clear the table again when selecting is complete.
One could also add a column to indicate that that ID has already been selected, and after selections delete the non-selected rows, leaving a table with the selected IDs which can then be used in a join.
|
|
|
|
|
My idea is to get the max and min ID's from that Table.
Then use the Math.Random Function and give the limits in that range taken in previous step.
Select 5 Random ID's this way
Then select those records using this kindof construct in your where clause
ID IN(rand1,rand2,rand3,rand4,rand5)
Hope that's helpful for u
Rocky
You can't climb up a ladder with your hands in your pockets.
|
|
|
|
|
That'll work; unless IDs have been removed or deactivated or something.
Maybe select ten random numbers in the range and use TOP 5.
Or loop until you have five.
Rocky# wrote: You can't climb up a ladder with your hands in your pockets.
Sure I can, done it many times, but not on a steep ladder.
|
|
|
|
|
Hi,
I have a table with column named EmpID, EmpName, Salary,
I want to find out second highest salary.
Thanks![Rose | [Rose]](https://codeproject.freetls.fastly.net/script/Forums/Images/rose.gif)
|
|
|
|
|
SELECT MAX(Salary)
FROM [EmployeeTable]
WHERE Salary < (SELECT MAX(Salary) FROM [EmployeeTable])
|
|
|
|
|
thanks for your help.
Now I have a query that if I want find 3rd highest or 4th highest salary then how to write query?
thanks!
|
|
|
|
|
select TOP 5 salary,empID
FROM EMP
Order by Salary DESC
Use this to find whatever number of top salaried personeel u want. Use a Cursor to fetch the records one by one. In this way u can ignore the records you dont want. Like for finding the 4th highest salary you can ignore the 1st 3 records and get the fourth one.
Rocky
You can't climb up a ladder with your hands in your pockets.
|
|
|
|
|
No need to use a cursor. Here is a query to get the fifth salary.
SELECT TOP 1 Salary, EmpId
FROM
(SELECT TOP 5 Salary, empID
FROM EMP
Order by Salary DESC) AS TopSalary
ORDER BY Salary ASC
|
|
|
|
|
I have an ntext column in a table. I wanted this column value to be uploaded to a text file. I was using some code of my own, but it is not working out.Does anybody has any pointers on this? Any simple method to do this programmatically would be highly appreciated. I need to pull this code in one of my button click event on a windows page.
Thanks in Advance!
Santhosh Kumar Edukulla
|
|
|
|
|
I am trying to create a reporting system which will give me the "Customer ID", "Number of Messages sent", "Is Message a part of a thread or Distinct", Message Status (i.e. New, Under Review, Cancelled or Resolved) within a given period. I honestly do not see no need for a curso in here but I believe one can be used but I do not understand the benefit of using one. The search is working fine except for the last "Select" prior to dropping the table is not providing Distinct User's. Users with multiple messages/Threads are being pulled up as many time as the amount of records found for them. It is the result set from the Last select that is displayed in the GUI.
Any assistance given will be appreciated.
SET NOCOUNT ON /* Don't return row counting to caller */
CREATE TABLE #MS_MessagesFromCustomer(
NumOfMessages BIGINT,
CustomerID VARCHAR(100),
Period datetime,
ThreadID int,
MessageStatus int
)
CREATE TABLE #MS_InquiriesByCustomer(
ThreadID int,
CustomerID VARCHAR(100),
TotalMessages int,
New int,
UnderReview int,
Cancelled int,
Resolved int,
TotalThreads int
)
Insert into #MS_MessagesFromCustomer(CustomerID, NumOfMessages, Period, ThreadID, MessageStatus)
Select MSM.SenderID, count(MSM.MessageID)Messages, Convert(varchar, MSM.SentDate, 101) [Sent Date],MSM.ThreadID,MST.CurrentStatusID [Current Status]
from dbo.MS_Messages MSM INNER JOIN dbo.MS_Threads MST ON MSM.ThreadID = MST.ThreadID
where MSM.SentDate >'3/1/2005'
and MSM.SentDate < '3/31/2005'
group by MSM.SenderID, MSM.SentDate, MSM.ThreadID, MST.CurrentStatusID
order by SenderID
--Select * from #MS_MessagesFromCustomer
INSERT INTO #MS_InquiriesByCustomer(ThreadID,CustomerID, TotalMessages, New, UnderReview, Cancelled,Resolved, TotalThreads)
Select Distinct(ThreadID)'ThreadID', CustomerID'Customer' , count(NumOfMessages) 'Total Messages Recevied',COUNT(CASE MessageStatus when 19 THEN MessageStatus end) AS 'New',
COUNT(CASE MessageStatus WHEN 20 THEN MessageStatus end) AS 'UnderReview',
COUNT(CASE MessageStatus WHEN 21 THEN MessageStatus end) AS 'Cancelled',
COUNT(CASE MessageStatus WHEN 22 THEN MessageStatus end) AS 'Resolved',
(COUNT(CASE MessageStatus WHEN 19 THEN MessageStatus end) +
COUNT(CASE MessageStatus WHEN 20 THEN MessageStatus end) +
COUNT(CASE MessageStatus WHEN 21 THEN MessageStatus end) +
COUNT(CASE MessageStatus WHEN 22 THEN MessageStatus end)) AS 'Total Threads'
from #MS_MessagesFromCustomer
group by customerID, ThreadID
order by customerID
SELECT Distinct(CustomerID),* FROM #MS_InquiriesByCustomer
Select * from #MS_MessagesFromCustomer
Select Distinct(CustomerID), TotalMessages 'Total Messages Recevied', New 'New', UnderReview 'Under Review', Cancelled 'Cancelled',Resolved 'Resolved', count(TotalThreads) 'Total Threads'
FROM #MS_InquiriesByCustomer
GROUP BY customerID,ThreadID, TotalThreads,TotalMessages, New, UnderReview, Cancelled, Resolved
ORDER BY CustomerID
DROP TABLE #MS_MessagesFromCustomer
DROP TABLE #MS_InquiriesByCustomer
Skan
If you knew it would not compile why didn't you tell me?!?!?!
|
|
|
|
|
DISTINCT used as an argument of a SELECT clause does not eliminate rows due to a single duplicate column. It eliminates rows when all columns are equivalent. The whole row would have to be the same for it to begin its removal process.
DISTINCT can be used as an argument in an aggregate like AVG(DISTINCT price). Here it will eliminate duplicate prices before calculating the average.
I think you need to re-examine how you have coded your store procedure. It seems a little over done. Not sure what your desired end result is but, I would think a much simpler LEFT JOIN would handle most of the work.
|
|
|
|
|
Hellow everybody
I want a query that will return number of rows in the database , but it will take three parametes from the calling method.
I implemented a query which is as follows:
Select Count(*)
From MainRecord
Where (MealType = @MealType) AND (ID = @ID) AND ( CAST(FLOOR(CAST(DATE AS FLOAT)) AS DATETIME = @DATE)
The purpose of this query is that I want to count number of rows if the ID, MealType and DATE are in the database. Also, the @DATE I am giving in the calling query is now date which is for example ( today date [08/23/2007 00:00:00]). I wanted to be checked with the date that is stored in the database where the DATE that is stored in the database is DATETIME which is in this form (08/22/2007 13:14:50). Therefore, the CAST(FLOOR(CAST(DATE AS FLOOT)) AS DATETIME will convert that datetime to (08/22/2007 00:00:00) so we could compare then just in the date.
I don’t know if this way is right or not and if not how can I do it.
Thaanks,
-- modified at 11:36 Thursday 23rd August, 2007
|
|
|
|
|
CanadianBoy wrote: FLOOT
Did you mean FLOAT?
CanadianBoy wrote: I don’t if this way is right or not and if not how can I do it.
Have you tried experimenting? Building up a part of the query to see if it does what you want?
SELECT GETDATE()
SELECT CAST(GETDATE() AS FLOAT)
SELECT FLOOR(CAST(GETDATE() AS FLOAT))
SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
Looks like it works to me.
-- Always write code as if the maintenance programmer were an axe murderer who knows where you live.
Upcoming FREE developer events:
* Glasgow: Agile in the Enterprise Vs. ISVs, Mock Objects, SQL Server CLR Integration, Reporting Services, db4o ...
* Reading: SQL Bits
My website
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
