I am a beginner with .net. I faced issue with the following error
"The transaction operation cannot be performed because there are pending requests working on this transaction.".
i read somewhere in the blog .i appended my connection string with enlist=true and the issue was resolved.
Note: i am upgrading my DB from sql server 2005 to sql server 2008R2.
Please help to understand the importance of using enlist.
I have a reporting requirement from the following tables. I created a new database with these tables and imported data from the live database for reporting purpose.
The report parameter is a date range. I read the following and found that DATE_CORRELATION_OPTIMIZATION can be used to make the query work faster by utilizing seek instead of scan. I made the required settings – still the query is using same old plan and same execution time. What additional changes need to be made to make the query utilize the date correlation?
Note: I am using SQL Server 2005
REFERENCES
SQL
--Database change made for date correlation
ALTER DATABASE BISourcingTest
SET DATE_CORRELATION_OPTIMIZATION ON;
GO
--Settings made
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO
--Test Setting
IF ( (sessionproperty('ANSI_NULLS') = 1) AND
(sessionproperty('ANSI_PADDING') = 1) AND
(sessionproperty('ANSI_WARNINGS') = 1) AND
(sessionproperty('ARITHABORT') = 1) AND
(sessionproperty('CONCAT_NULL_YIELDS_NULL') = 1) AND
(sessionproperty('QUOTED_IDENTIFIER') = 1) AND
(sessionproperty('NUMERIC_ROUNDABORT') = 0)
)
PRINT 'Everything is set'
ELSE
PRINT 'Different Setting'
--Query
SELECT C.ContainerID, C.CreatedOnDate,OLIC.OrderID
FROM ContainersTest C
INNER JOIN OrderLineItemContainers OLIC
ON OLIC.ContainerID = C.ContainerID
WHERE C.CreatedOnDate > '1/1/2015'
AND C.CreatedOnDate < '2/01/2015'
TABLES
CREATE TABLE [dbo].[ContainersTest](
[ContainerID] [varchar](20) NOT NULL,
[Weight] [decimal](9, 2) NOT NULL DEFAULT ((0)),
[CreatedOnDate] [datetime] NOT NULL DEFAULT (getdate()),
CONSTRAINT [XPKContainersTest] PRIMARY KEY CLUSTERED
(
[CreatedOnDate] ASC,
[ContainerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[OrderLineItemContainers](
[OrderID] [int] NOT NULL,
[LineItemID] [int] NOT NULL,
[ContainerID] [varchar](20) NOT NULL,
[CreatedOnDate] [datetime] NOT NULL DEFAULT (getdate()),
CONSTRAINT [PK_POLineItemContainers] PRIMARY KEY CLUSTERED
(
[OrderID] ASC,
[LineItemID] ASC,
[ContainerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_OrderLineItemContainers] UNIQUE NONCLUSTERED
(
[ContainerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[OrderLineItemContainers] WITH CHECK ADD CONSTRAINT [FK_POLineItemContainers_Containers] FOREIGN KEY([ContainerID])
REFERENCES [dbo].[Containers] ([ContainerID])
GO
ALTER TABLE [dbo].[OrderLineItemContainers] CHECK CONSTRAINT [FK_POLineItemContainers_Containers]
Plan
--
Table : item
+---------+------------+
| item_id | item_sc |
+---------+------------+
| 63247 | 313150069 |
| 47558 | 2S27500EVW |
+---------+------------+
Table: incident
+-------------+-------------+---------+
| incident_id | date_logged | Item_id |
+-------------+-------------+---------+
| 10074329 | 1-Nov-2015 | 63247 |
| 10074869 | 31-Jan-2016 | 63247 |
| 10074399 | 20-Jan-2016 | 63247 |
| 10075274 | 7-Feb-2016 | 63247 |
| 10035727 | 2-Aug-2013 | 47558 |
| 10050239 | 8-Jul-2014 | 47558 |
| 10076340 | 25-Feb-2016 | 47558 |
| | | |
+-------------+-------------+---------+
Table: item_maint
+---------------+---------+---------------+-------------+-------------+
| item_maint_id | item_id | maint_crct_id | start_date | end_date |
+---------------+---------+---------------+-------------+-------------+
| 71748 | 63247 | 2132 | 11-Nov-2015 | 10-Nov-2016 |
| 62281 | 63247 | 1819 | 11-Nov-2014 | 10-Nov-2015 |
| 40786 | 47558 | 921 | 1-May-2013 | 8-Sep-2016 |
+---------------+---------+---------------+-------------+-------------+
Table: maint_crtc
+---------------+----------------+----------------+-------------+
| maint_crct_id | maint_crct_sc | effective_date | expiry_date |
+---------------+----------------+----------------+-------------+
| 1819 | ACSS-2015-0011 | 11-Nov-2014 | 10-Nov-2015 |
| 2132 | ACSS-2015-0091 | 11-Nov-2015 | 10-Nov-2016 |
| 921 | ACSS-2013-0066 | 1-May-2013 | 8-Sep-2016 |
+---------------+----------------+----------------+-------------+
Expected Result
+-------------+-------------+-----------+----------------+
| incident_id | date_logged | item_sc | maint_crct_sc |
+-------------+-------------+-----------+----------------+
| 10074869 | 31-Jan-2016 | 313150069 | ACSS-2015-0091 |
| 10074399 | 20-Jan-2016 | 313150069 | ACSS-2015-0091 |
| 10075274 | 7-Feb-2016 | 313150069 | ACSS-2015-0091 |
+-------------+-------------+-----------+----------------+
My Query
select DISTINCT incident.incident_id,incident.date_logged,ITEM.item_id, item.item_sc,maint_crct.maint_crct_id, maint_crct.maint_crct_sc
FROM incident INNER JOIN
dbo.item ON dbo.incident.item_id = dbo.item.item_id INNER JOIN
dbo.item_maint INNER JOIN dbo.maint_crct ON dbo.item_maint.maint_crct_id = dbo.maint_crct.maint_crct_id
ON dbo.incident.item_id = dbo.item_maint.item_id
WHERE maint_crct.maint_crct_n='ACSS-2015-0091'
As per my above query I am getting result like below,
+-------------+-------------+-----------+----------------+
| incident_id | date_logged | item_sc | maint_crct_sc |
+-------------+-------------+-----------+----------------+
| 10074869 | 31-Jan-2016 | 313150069 | ACSS-2015-0091 |
| 10074399 | 20-Jan-2016 | 313150069 | ACSS-2015-0091 |
| 10075274 | 7-Feb-2016 | 313150069 | ACSS-2015-0091 |
| 10074329 | 1-Nov-2015 | 313150069 | ACSS-2015-0091 |
+-------------+-------------+-----------+----------------+
How can I get the result without 10074329.
I'm using SQL SERVER 2005 with FULL recovery model. I accidentally executed the UPDATE statement without specify the condition using WHERE clause.
UPDATE TABLE SET Column1='All'
Now all Column1 data are set to 'All'. And I need to undo the column1 to have previous data.
Any idea? Thanks...
I have some SQL CLR and stored procedures which I created on a SQL Server 2005 database using Visual Studio 2012 but I need to have a SQL Server 2005 database associated rather than the default SQL Server 2012.
How can I create a 2005 database from VS 2012? I tried changing the target DB version to SQL Server 2005 - but it does not work.
I have a job that I want to run that passes a variable to an ssis package. The variable is a filename but the filename changes daily. I have an access front end that the user enters the filename into. The access program runs a stored procedure which writes the filename to a temp table and then runs the job. I would like the job to query that table for the filename and pass it along to my package variable.
I can get the job to work using a static filename. On the set values tab I used the property path \Package.Variables[User::FileName] and the value \myserver......\filename.txt. But I don't know how to replace that filename with the results of the query
Thanks in advance.
Scott
How to pivot tables with out aggregate functions and with boolean fields from three tables
CREATE TABLE [dbo].[TmpData](
[TmpDataID] [int] IDENTITY(1,1) NOT NULL,
[TmpDataName] [nvarchar](255) NOT NULL,
[StatusID] [int] NOT NULL,
[SignOffDate] [datetime] NULL,
[SignOffUserID] [int] NULL,
[SignOffComments] [nvarchar](max) NULL,
[IsClosed] [bit] NOT NULL,
[ClosedDate] [datetime] NULL,
CONSTRAINT [PK_dbo.TmpData] PRIMARY KEY CLUSTERED
(
[TmpDataID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
here is the sample data TmpDataID | TmpDataName |StatusID |SignOffDate |SignOffUserID |SignOffComments |IsClosed |ClosedDate 1 |TmpData |1 |00:00.0 |25 |xthda |1 |00:00.0
2 |TmpDtata2 |1 |00:00.0 |22 |eeeee |1 |00:00.0
CREATE TABLE [dbo].[TmpSectionData](
[TmpSectionDataID] [int] IDENTITY(1,1) NOT NULL,
[TmpDataID] [int] NOT NULL,--foreign key
[TmpSection] [nvarchar](255) NULL,
[TmpSectionOverall] [bit] NOT NULL,
[TmpSectionCusID] [int] NULL,
[TmpSectionOutcome] [bit] NULL,
[TmpSectionHeading] [bit] NOT NULL,
[TmpSectionActive] [bit] NOT NULL,
[TmpSectionHasOutcome] [bit] NOT NULL,
CONSTRAINT [PK_dbo.TmpSectionData] PRIMARY KEY CLUSTERED
(
[TmpSectionDataID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
here is the sample data
TmpSectionDataID |TmpDataID |TmpSection |TmpSectionOverall |TmpSectionCusID |TmpSectionOutcome |TmpSectionHeading |TmpSectionActive |TmpSectionHasOutcome| 1 |1 |TmpSection1 |1 |1 |1 |1 |0 |0 2 |1 |TmpSection2 |1 |5 |1 |0 |1 |1 3 |1 |TmpSection3 |0 |6 |0 |0 |1 |1 CREATE TABLE [dbo].[TmpSampleData]( [TmpSampleDataID] [int] IDENTITY(1,1) NOT NULL, [TmpSectionDataID] [int] NOT NULL, --foreighn key [TmpSampleText] nvarchar NULL, [TmpSampleValidation] [bit] NOT NULL, [TmpSampleCusConID] [int] NULL, [TmpSampleQPRID] [int] NOT NULL, [TmpSampleHasOutcome] [bit] NOT NULL, CONSTRAINT [PK_dbo.TmpSampleData] PRIMARY KEY CLUSTERED ( [TmpSampleDataID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
And the data needed in the format:
TmpDataID TmpDataName StatusID SignOffDate SignOffUserID SignOffComments IsClosed ClosedDate TmpSection1+TmpSectionOverall TmpSection1+TmpSectionCusID TmpSection1+TmpSectionOutcome TmpSection1+TmpSectionHeading TmpSection1+TmpSectionActive TmpSection1+TmpSectionHasOutcome TmpSection2+TmpSectionOverall TmpSection2+TmpSectionCusID TmpSection2+TmpSectionOutcome TmpSection2+TmpSectionHeading TmpSection2+TmpSectionActive TmpSection2+TmpSectionHasOutcome TmpSection3+TmpSectionOverall TmpSection3+TmpSectionCusID TmpSection3+TmpSectionOutcome TmpSection3+TmpSectionHeading TmpSection3+TmpSectionActive TmpSection3+TmpSectionHasOutcome
1 TmpData 1 00:00.0 25 xthda 1 00:00.0 1 1 1 1 0 0 1 5 1 0 1 1 0 6 0 0 1 1
All the column data from the first table along with the data from second table TmpSectionData based on TmpSection which should come as header and the related data one after another in a single row. If i use joins i will get multiple columns and i have seen some of the question with PIVOT where they use aggregate function. here the columns i wanted to pivot is bool fields.
I have gone through some of the answers by few of stackoverflow collegues with pivot single column and there all use a amount field and made use of aggregate funstions.those didnt give me a clear idea on how i can work with more tables and more columns with one column as base for all the pivot. I tried to implement http://ift.tt/1QkZfsV . There also i am not getting the requeired format. Hope some one can help me with it.I have seen some of bluefeet answers and realized i may be able to achive the same with pivot in sql.I could manage to get one transpose one column
I am using SqlDataAdapter to update my dataTable which I had queried from Database in another method and in that method there were columns, Status and Is_processed which were updated.
Now, I want these changes to be persisted (updated) in Database. Following is what have done to achieve:
Code
batchSize = 10;
string cmd;
int updatedRows;
try
{
string connString = db.ConnectionString;
using (SqlConnection conn = new SqlConnection(connString))
using (SqlDataAdapter adapter = new SqlDataAdapter())
{
cmd = "UPDATE IMPORTED_ACCOUNTS SET STATUS = @Status , IS_PROCESSED = @IsProcessed " +
", CREATED_ON = @CreatedOn , CREATED_BY = @CreatedBy , UPDATED_ON = @UpdatedOn , UPDATED_BY = @UpdatedBy " +
"WHERE CONVENTIONAL_ACCOUNT = @ConAcct";
adapter.UpdateCommand = new SqlCommand(cmd, conn);
adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;
adapter.UpdateCommand.Parameters.AddWithValue("@Status", "Status");
adapter.UpdateCommand.Parameters.Add("@IsProcessed", SqlDbType.Bit, 1, dTable.Columns["IsProcessed"].ColumnName);
adapter.UpdateCommand.Parameters.Add("@CreatedOn",SqlDbType.DateTime,30, dTable.Columns["CreatedOn"].ColumnName);
adapter.UpdateCommand.Parameters.AddWithValue("@CreatedBy", dTable.Columns["CreatedBy"].ColumnName);
adapter.UpdateCommand.Parameters.Add("@UpdatedOn",SqlDbType.DateTime,30, dTable.Columns["UpdatedOn"].ColumnName);
adapter.UpdateCommand.Parameters.AddWithValue("@UpdatedBy", dTable.Columns["UpdatedBy"].ColumnName);
adapter.UpdateCommand.Parameters.AddWithValue("@ConAcct", dTable.Columns["ConventionalAccount"].ColumnName);
cmd = "INSERT INTO IMPORTED_ACCOUNTS ([STATUS],[IS_PROCESSED],[CREATED_ON],[CREATED_BY],[UPDATED_ON],[UPDATED_BY], CONVENTIONAL_ACCOUNT) " +
"VALUES (@Status , @IsProcessed, @CreatedOn, @CreatedBy, @UpdatedOn, @UpdatedBy, @ConAcct) ";
adapter.InsertCommand = new SqlCommand(cmd, conn);
adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;
adapter.InsertCommand.Parameters.AddWithValue("@Status", dTable.Columns["Status"].ColumnName);
adapter.InsertCommand.Parameters.Add("@IsProcessed", SqlDbType.Bit, 1, dTable.Columns["IsProcessed"].ColumnName);
adapter.InsertCommand.Parameters.Add("@CreatedOn", SqlDbType.DateTime, 30, dTable.Columns["CreatedOn"].ColumnName);
adapter.InsertCommand.Parameters.AddWithValue("@CreatedBy", dTable.Columns["CreatedBy"].ColumnName);
adapter.InsertCommand.Parameters.Add("@UpdatedOn", SqlDbType.DateTime, 30, dTable.Columns["UpdatedOn"].ColumnName);
adapter.InsertCommand.Parameters.AddWithValue("@UpdatedBy", dTable.Columns["UpdatedBy"].ColumnName);
adapter.InsertCommand.Parameters.Add("@ConAcct", SqlDbType.VarChar, 100, dTable.Columns["ConventionalAccount"].ColumnName);
cmd = "DELETE FROM IMPORTED_ACCOUNTS WHERE CONVENTIONAL_ACCOUNT = @ConAcct";
adapter.DeleteCommand = new SqlCommand(cmd, conn);
adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;
adapter.DeleteCommand.Parameters.AddWithValue("@ConAcct", dTable.Columns["ConventionalAccount"].ColumnName);
adapter.UpdateBatchSize = batchSize;
updatedRows = adapter.Update(dTable); // point where code breaks
}
return updatedRows;
}
catch (Exception ex)
{
return 0;
}
Error
It ends up with the following error in catch block:
Violation of PRIMARY KEY constraint 'PK_ACCTS'. Cannot insert duplicate key in object 'dbo.IMPORTED_ACCOUNTS'.
Comment Why is it trying to insert rows in database when they should be updated as they already exists in the Database? What are the conditions for adapter.Update(dTable) method, when it triggers Update rather Insert?
Can't figure out... Help is indeed appreciated!
I have a table containing base64-encoded jpegs as well as some other data. The base64 string is stored in a VARCHAR(MAX) column.
How can I save these images out to actual files inside folders that are dynamically named using other data from the table in a stored procedure?
I have two tables with a Text data type as the common field between them.
I have created this query in SQL 2005:-
SELECT *
FROM [Table_A] A
INNER JOIN [Table_B] B
ON A.[EmailAddress] = B.[EmailAddress]
WHERE A.[FieldA] = B.[FieldB]
There is 1 record on Table_A with email (someone@this.com) and 3 records on Table_B with email (someone@this.com).
When I run the query I was expecting 3 records in my result, but only get one.
Any assistance with what I am missing would be appreciated.
I have been asked to do a bit of work on a SQL Server 2005 query that has 26 (!) nested Replace function calls.
There are two issues with this query.
The first is that the 26 replace functions are not enough, a few more are needed, but the query is hitting some size limitations (it's a stored procedure and its making using of sp_MSforEachDB).
The second problem is that the query is just not legible at this stage.
I would like to use table variable with a lookup, but the issue is that the replace query is being used to replace values in one column with values from another column. It does however all work, apparent from not replacing enough strings.
Here is a simplified example to hopefully better explain what I am trying to do.
Table to be converted
ID MainText Ptext1 Ptext2 Ptext3
1 Have a %Ptxt1 %Ptxt2 Apple Tonight
2 %Ptxt1 likes %Ptxt2 at %Ptxt3 Sally Cake Midnight
The desired result for 1 is "Have a Apple Tonight", the desired result for 2 is "Sally Likes Cake at Midnight".
The current SQL Looks a bit like
EXEC sp_MSForEachDB 'IF ''?'' LIKE ''%Database%''
Select Replace(Replace(Replace([Maintext], '%Ptxt1' , [Ptext1]),'%Ptxt2',[Ptext2]),'Ptxt3', [Ptext3]) from dbo.mytable
Please forgive any mistakes in the example.
I have seen nice examples of people using Table variables to store parameters for the replace function - but I haven't seen any where a column reference is used instead of a static string, is this possible ?
I have 3 table called stop,trip,and shift. I would like to do something so the result can like the image
I'm having hard time for this, please help me i will appreciate it if you guys can help me on this :D
Thanks in advance
Why script generated by SSMS shown in red is different from script stored in system tables
i.e. All these methods are giving me same script
sql_module
object_definition
sp_helptext
However when generated from SSMS, right click -> script as Create or Modify is giving a different script.
How is it possible and generating different scripts.
I am working on a conversion of a series of teradata statament to sql-server 2005. everything ok untill we met this :
select CODE, _year, _month, _day, product,
sum(DAILY_AMMOUNT) over(partition by CODE, _year, _month ,product order by _day ROWS UNBOUNDED PRECEDING) as DAY_AMM
from ...
as you for sure know there is no ROWS in sql-server 2005.
I know that in some cases I could replace it with RANGE, but I don't have access to the teradata database to check if this is indeed the case. Any idea on how i can replace the sum to be sure that the result will be matching?
Thanks in advance
I have a requirement to show values from multiple tables to a single row . CREATE TABLE [dbo].[Table1Result](
[Table1ResultID] [int] IDENTITY(1,1) NOT NULL,
[CreateDate] [datetime] NOT NULL,
[CreatedByUserID] [int] NOT NULL,
)
CREATE TABLE [dbo].[Table2Result](
[Table2ResultID] [int] IDENTITY(1,1) NOT NULL,
[Table1Result] [int] NOT NULL, --primary key of table1
[Table2Name] [nvarchar](255) NULL,
[Table2Overall] [bit] NOT NULL,
[Table2GenterID] [int] NULL,
[CombinedTable2Outcome] [bit] NULL,
[Display] [bit] NOT NULL,
[IsActive] [bit] NOT NULL,
[HasOutcome] [bit] NOT NULL,
)
CREATE TABLE [dbo].[Table3Result](
[Table3ResultID] [int] IDENTITY(1,1) NOT NULL,
[Table3ResultID] [int] NOT NULL, --primary key of table 2 ..here foreigh key
[Table3Text] [nvarchar](max) NULL,
[Table3Value] [int] NULL,
[Table3StringValue] [nvarchar](max) NULL,
[Table3BooleanValue] [bit] NULL,
[Table3DateTimeValue] [datetime] NULL,
[Table3DecimalValue] [decimal](18, 2) NULL,
[Table3Dropdown] [bit] NULL,
[Table3Validation] [bit] NOT NULL,
[Table3DetailID] [int] NULL,
[Table3QuestionID] [int] NOT NULL,
[CombinedTable3Outcome] [bit] NOT NULL,
)
Above tables are 3 tables among them . Here 1 st table will have only one row for a particulat table1resultid . There is a foreign key releation ship with the second table ie table2result. There for a table1resultid (from 1st table) there will be multiple records.similarly table2 and table3 are related and so on. My requirement is to display 1 row for a particular id. that is for a particulat table1resultid i need to fetch data from all the tables and display in a single row.I expect the column heading will come as column heading in my result.I googled it and found i can get it through pivot. I could not understand it thouroughly. If someone can help me to figure out it will make my day.
I have a database Table a (EMAILS) where EmailID is the Primary Key
EmailID Email_To Email_From Email_Subject Email_Registered Email_Read
If a user creates an email it is registered in this table.
For example, the user "Dave" who has id 3 sends an email to "John" who has id 4
So this would give
EmailID Email_To Email_From Email_Subject Email_Registered Email_Read
10 4 3 TEST 2/23/2016 11:00 False
To return results I do this select (joining the user profile database)
SELECT PROFILE_1.SellerID, PROFILE_1.Seller_UserName, EMAILS.EmailID, EMAILS.Email_From, EMAILS.Email_To, EMAILS.Email_Subject,
EMAILS.Email_Registered, EMAILS.Email_Read,
(SELECT Seller_UserName AS Epr2
FROM PROFILE
WHERE (SellerID = EMAILS.Email_To)) AS Expr2
FROM PROFILE AS PROFILE_1 LEFT OUTER JOIN
EMAILS ON EMAILS.Email_From = PROFILE_1.SellerID
WHERE (EMAILS.Email_From IS NOT NULL) AND (PROFILE_1.Seller_UserName = 'Dave')
ORDER BY EMAILS.Email_Registered DESC
So John Replies to Dave's email and it goes into the EMAILS_THREAD table and is registered as
EmailThreadID EmailID Email_To Email_From Email_Registered Email_Read
1 10 3 4 2/23/2016 11:05 False
What I am trying to do is a select that
SELECTS from EMAILS where Email_From is from Dave and return in the results the top 1 result from EMAIL_THREADS that is sent to Dave (based on Email_Registered) with the same EmailID as the EMAILS.EmailID if there is a entry in EMAIL_THREADS.
So in other words return the result of the EMAIL table and latest corresponding result in the EMAIL_THREADS table if there is one.
I hope this makes sense.
I've tried a ton of combinations and I can't figure this out.
At first I thought it was a subquery or a join or a group by...but i can't seem to nail the select and how it is structured.
Looking for some help.
One of our clients has SQL Server 2005 running on a Windows 2008 R2 Standard machine. Every once in a while, the server fails with the following error:
SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems. [CLIENT:
<local machine>]
The error occurs at a rate of about once per second, with the value for CLIENT: being the only thing that changes (sometimes, instead of <local machine> it shows the IP of the machine or the IP of other machines belonging to the client) and until the SQL Server is restarted, no connections can be made to it. After the restart, it works fine.
The problem happens about once or twice per month. There are no windows logs for the previous occurrence; I've since increased the max size for the Application log.
I want to insert those value also which have version null and and for version null i don't have status.
i am getting all datat from datatable to procedure table type and then how i check that the comming data has version null or not.
USE [LAndingPAgeitems_testDB]
GO
/****** Object: StoredProcedure [dbo].[uspInsertFilterData] Script Date:02/20/2016 16:53:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[uspInsertFilterData]
@sqlDataTable SqlTableType READONLY
as
Begin
if (@sqlDataTable.Version is NOT NULL or @sqlDataTable.Version != '')
Begin
Insert into FilterCombination (Product,Version,HotFix,Resourcetype,Language) (Select t1.Product,t1.Version,t1.HotFix, t1.Resourcetype, t1.Language from @sqlDataTable t1 , Product_Version_Mapping t2 where t1.Product = t2.Product and t1.Version = t2.Version and t2.Status = 'Correct')
End
Else
Begin
Insert into FilterCombination (Product,Version,HotFix,Resourcetype,Language) (Select t1.Product,t1.Version,t1.HotFix, t1.Resourcetype, t1.Language from @sqlDataTable t1 , Product_Version_Mapping t2 where t1.Product = t2.Product);
End
End
this query is not working. Please help
I simply paste this SQL code that I've carefully crafted in SQL Server Management Studio and it works.
SELECT v.StudentID, v.StudentName, s.StudentHomeEmailAddress, s.StudentStudyYear, s.StudentMobilePhone, t.TradeName, p.ProgramName, (CASE WHEN s.StudentHomeEmailAddress IS NULL THEN 1 ELSE 0 END) AS Ord1
FROM vwCurrentStudents AS v
INNER JOIN tblStudents AS s ON s.StudentID = v.StudentID
INNER JOIN tblStudentProgramReg AS r ON r.StudentID = s.StudentID AND r.StudProgEnrolStatusID IN (1, 3)
INNER JOIN tblPrograms AS p ON r.ProgramID = p.ProgramID AND p.ProgramCatID IN (1, 3) AND p.ProgramID NOT IN (23, 112, 113)
INNER JOIN tblTrades AS t ON t.TradeID = p.TradeID
WHERE (@Who = 'Stu')
AND (s.StudentStudyYear LIKE @StudyYear)
AND (
(CASE WHEN (SELECT Item FROM dbo.fnMakeTableFromList(@Programs, ',') AS mkTblP WHERE (LTRIM(RTRIM(UPPER(Item))) = LTRIM(RTRIM(UPPER(p.ProgramName))))) = p.ProgramName THEN 1 ELSE 0 END = 1 OR dbo.fnISEMPTY(@Programs) = 1 OR @Programs = '%')
AND (CASE WHEN (SELECT Item FROM dbo.fnMakeTableFromList(@Trades, ',') AS mkTblT WHERE (LTRIM(RTRIM(UPPER(Item))) = LTRIM(RTRIM(UPPER(t.TradeName))))) = t.TradeName THEN 1 ELSE 0 END = 1 OR dbo.fnISEMPTY(@Trades) = 1 OR @Trades = '%')
)
ORDER BY Ord1, v.StudentName
However, if I paste it into the Query Builder, where it attempts to generate table links etc... the query changes to this!
SELECT v.StudentID, v.StudentName, s.StudentHomeEmailAddress, s.StudentStudyYear, s.StudentMobilePhone, t.TradeName, p.ProgramName,
(CASE WHEN s.StudentHomeEmailAddress IS NULL THEN 1 ELSE 0 END) AS Ord1
FROM vwCurrentStudents AS v INNER JOIN
tblStudents AS s ON s.StudentID = v.StudentID INNER JOIN
tblStudentProgramReg AS r ON r.StudentID = s.StudentID AND r.StudProgEnrolStatusID IN (1, 3) INNER JOIN
tblPrograms AS p ON r.ProgramID = p.ProgramID AND p.ProgramCatID IN (1, 3) AND p.ProgramID NOT IN (23, 112, 113) INNER JOIN
tblTrades AS t ON t.TradeID = p.TradeID
WHERE (@Who = 'Stu') AND (s.StudentStudyYear LIKE @StudyYear) AND (CASE WHEN
(SELECT Item
FROM dbo.fnMakeTableFromList(@Programs, ',') AS mkTblP
WHERE (LTRIM(RTRIM(UPPER(Item))) = LTRIM(RTRIM(UPPER(p.ProgramName))))) = p.ProgramName THEN 1 ELSE 0 END = 1) AND
(CASE WHEN
(SELECT Item
FROM dbo.fnMakeTableFromList(@Trades, ',') AS mkTblT
WHERE (LTRIM(RTRIM(UPPER(Item))) = LTRIM(RTRIM(UPPER(t .TradeName))))) = t .TradeName THEN 1 ELSE 0 END = 1) OR
(@Who = 'Stu') AND (s.StudentStudyYear LIKE @StudyYear) AND (CASE WHEN
(SELECT Item
FROM dbo.fnMakeTableFromList(@Trades, ',') AS mkTblT
WHERE (LTRIM(RTRIM(UPPER(Item))) = LTRIM(RTRIM(UPPER(t .TradeName))))) = t .TradeName THEN 1 ELSE 0 END = 1) AND
(dbo.fnISEMPTY(@Programs) = 1) OR
(@Who = 'Stu') AND (s.StudentStudyYear LIKE @StudyYear) AND (CASE WHEN
(SELECT Item
FROM dbo.fnMakeTableFromList(@Trades, ',') AS mkTblT
WHERE (LTRIM(RTRIM(UPPER(Item))) = LTRIM(RTRIM(UPPER(t .TradeName))))) = t .TradeName THEN 1 ELSE 0 END = 1) AND
(@Programs = '%') OR
(@Who = 'Stu') AND (s.StudentStudyYear LIKE @StudyYear) AND (CASE WHEN
(SELECT Item
FROM dbo.fnMakeTableFromList(@Programs, ',') AS mkTblP
WHERE (LTRIM(RTRIM(UPPER(Item))) = LTRIM(RTRIM(UPPER(p.ProgramName))))) = p.ProgramName THEN 1 ELSE 0 END = 1) AND
(dbo.fnISEMPTY(@Trades) = 1) OR
(@Who = 'Stu') AND (s.StudentStudyYear LIKE @StudyYear) AND (dbo.fnISEMPTY(@Programs) = 1) AND (dbo.fnISEMPTY(@Trades) = 1) OR
(@Who = 'Stu') AND (s.StudentStudyYear LIKE @StudyYear) AND (@Programs = '%') AND (dbo.fnISEMPTY(@Trades) = 1) OR
(@Who = 'Stu') AND (s.StudentStudyYear LIKE @StudyYear) AND (CASE WHEN
(SELECT Item
FROM dbo.fnMakeTableFromList(@Programs, ',') AS mkTblP
WHERE (LTRIM(RTRIM(UPPER(Item))) = LTRIM(RTRIM(UPPER(p.ProgramName))))) = p.ProgramName THEN 1 ELSE 0 END = 1) AND
(@Trades = '%') OR
(@Who = 'Stu') AND (s.StudentStudyYear LIKE @StudyYear) AND (dbo.fnISEMPTY(@Programs) = 1) AND (@Trades = '%') OR
(@Who = 'Stu') AND (s.StudentStudyYear LIKE @StudyYear) AND (@Programs = '%') AND (@Trades = '%')
ORDER BY Ord1, v.StudentName
And it does not work. It's wrong. The placements of ORs and ANDs are wrong.
Is there an option or something I can do to stop this?
My only other option is to paste it into the ASP code view directly and carefully make sure it's SQL compliant as it does not like line breaks, ampersands, etc
I'm looking for a stored procedure to get the information of the last execution of my Management Plan and show it in a webpage so I can see if my plan was sucessfull or failed, etc.
In my plan I'm doing index reorganize, rebuild, backups, etc. etc.
I've found these stored procedures: http://ift.tt/247u5i6
But I don't think they are useful for what I want to achieve...?
I'm trying to insert multiple rows that correspond to each payment that several clients have to do in a given date.
Obviously there are 2 tables, the first being the clients table:
cust_id, name ...
1, John Doe
2, Jane Smith
...
And the second being the payments table, using a serialized payment_id and incremental dates by 1 month for each payment: (ID is an identity column)
ID, cust_id, payment_id, due_date
1, 1, 1, 2016-01-01
2, 1, 2, 2016-02-01
3, 1, 3, 2016-03-01
5, 2, 1, 2016-01-01
6, 2, 2, 2016-02-01
7, 2, 3, 2016-03-01
...
I've seen that using WHILE loops should be avoided as stated in this answer, and cursors would take a long time if we are speaking of thousands of clients and tens of payments for each one.
Any pointer would help, thanks.
-- Edited to correct a typo in the title.
i have to develop one application that fetch data from another application's database which have sql 2005 database and that data i need to use in my application.
suppose i have java based application is it possible to i can fetch data from sql 2005 database using this data i will action something and that data store in mysql database?
Restriction:modified data will store in mysql and i not need to change sql 2005 i juse need to only retrieve
can i create one bridge that can do this two action?
I've a vista like this:
Agenzia Codice Nome Globmaster 012 NORTHWEST AIRLINES Globmaster 020 LUFTHANSA CARGO Globmaster 023 FEDERAL EXPRESS Globmaster 024 EUROPEAN AIR EXPRESS EAE Globmaster 988 ASIANA Globmaster 994 LINEAS AEREAS AZTECA Globmaster 995 B & H AIRLINES Globmaster 997 BIMAN Gabbiano 012 NORTHWEST AIRLINES Gabbiano 020 LUFTHANSA CARGO Gabbiano 023 FEDERAL EXPRESS Gabbiano 400 PALESTINIAN AIRLINES Gabbiano 406 UPS AIRLINES Gabbiano 407 AIR SENEGAL INTERNATIONAL
I need to modify the query so that it won't give duplicate codes (Codice) at the result:
SELECT 'Gabbiano' AS Agenzia, Codice, Descrizione, FlBSP, MastroForn, CapocForn, ContoForn, SottocForn, CodIVANazAtt, CommNazAttiva, CommIntAttiva,
FlCancellato, DataUltModifica, IDUtente, IDTerminale, CodAnagrafico, NoteDirPortAnd, NoteDirPortRit, ImpDirPortPax, ImpDirPortVei, GGScad,
PathCondGen
FROM dbo.TVTV0000
UNION
SELECT 'Istantravel' AS Agenzia, Codice, Descrizione, FlBSP, MastroForn, CapocForn, ContoForn, SottocForn, CodIVANazAtt, CommNazAttiva, CommIntAttiva,
FlCancellato, DataUltModifica, IDUtente, IDTerminale, CodAnagrafico, NoteDirPortAnd, NoteDirPortRit, ImpDirPortPax, ImpDirPortVei, GGScad,
PathCondGen
FROM IstanTravel.dbo.TVTV0000 AS Statistiche_1
UNION
SELECT TOP (100) PERCENT 'Globmaster' AS Agenzia, Codice, Descrizione, FlBSP, MastroForn, CapocForn, ContoForn, SottocForn, CodIVANazAtt,
CommNazAttiva, CommIntAttiva, FlCancellato, DataUltModifica, IDUtente, IDTerminale, CodAnagrafico, NoteDirPortAnd, NoteDirPortRit, ImpDirPortPax,
ImpDirPortVei, GGScad, PathCondGen
FROM Globmaster.dbo.TVTV0000 AS Statistiche_2
ORDER BY Codice
I don't even know why I get that silly TOP (100) PERCENT anyway ;)
I am trying to populate a Gridview to have checkboxes enabled per student, but depending to certain values from this query:
@SelectedDate is provided via a TextBox as a date only
SELECT v1.StudentID,
v1.StudentPreferredName + ' ' + v1.StudentFamilyName AS StudentName,
bcs.CheckStatusName,
rce.DateSubmitted,
rcp.RollCallPeriod
FROM tblBoardingRollCallEntries AS rce
INNER JOIN vwBoardingTenants AS v1
ON v1.StudentID = rce.StudentID
AND v1.[Year] = YEAR(@SelectedDate)
INNER JOIN tblBoardingCheckStatus AS bcs
ON bcs.CheckStatusID = rce.CheckStatusID
AND bcs.StatusActive = 1
INNER JOIN tblBoardingRollCallPeriods AS rcp
ON rcp.RollCallPeriodID = rce.RollCallPeriodID
AND rcp.PeriodYear = YEAR(@SelectedDate)
AND @SelectedDate BETWEEN rcp.PeriodStart AND rcp.PeriodEnd
AND rcp.RowStatus = 1
WHERE dbo.fnDateOnly(rce.DateSubmitted) = dbo.fnDateOnly(@SelectedDate)
My gridview:
Shows the following:
The data:
I want to be able to basically condense the rows in the GridView to be one student per row and the checkboxes ticked according to RollCallPeriod text.
I am playing with SQL pivots, to get the data to be as close as possible to what I am after so as to avoid code-behind, etc. However, I cannot get this to work.
select StudentID, [1],[10],[2],[3],[4],[5],[6],[7],[8],[9]
from
(
select StudentID, RollCallID, CheckStatusID
from tblBoardingRollCallEntries
unpivot
(
value for name in ([RollCallID],[StudentID],[CheckStatusID],[DateSubmitted],[StaffID])
) unpiv
) src
pivot
(
sum(RollCallPeriodID)
for RollCallPeriodID in ([1],[10],[2],[3],[4],[5],[6],[7],[8],[9])
) piv
I receive the following error:
Lookup Error - SQL Server Database Error: The type of column "StudentID" conflicts with the type of other columns specified in the UNPIVOT list.
Any other ideas?
Thanks
I have two tables, first table section with schema as:
SecID | Date | SecReturn
-------|---------------|--------------
208 | 2015-04-01 | 0.00355
208 | 2015-04-02 | -0.00578
208 | 2015-04-03 | 0.00788
208 | 2015-04-04 | 0.08662
105 | 2015-04-01 | 0.00786
and the second table SectionDates with schema as:
SecID | MonthlyDate | DailyDate
------|---------------|-------------
208 | 2015-04-02 | 2015-04-03
105 | 2015-04-01 | 2015-04-01
I want to calculate the running product on SecReturn column of the table Section with date range (DailyDate to MonthlyDate) from second table SectionDates.
Running product will be calculated for each sectionID based on formula :
Date | SecReturn | SectionTotal
-----------|---------------|--------------------
2015-04-01 | X (lets say) | (1+x)-1
2015-04-01 | Y | (1+x)(1+y)-1
2015-04-01 | Z | (1+x)(1+y)(1+z)-1
After applying above calculation values will be computed in SectionTotal column as for date 2015-04-01 computed value will be (1+0.00355)-1. Similarly, for date 2015-04-02 computed value will be (1+0.00355)(1+-0.00578)-1 and for date 2015-04-03 computed value will be (1+0.00355)(1+-0.00578)(1+0.00788)-1 and so on.
The final output:
SecID | Date | SectionTotal
-------|------------|-----------------
105 | 2015-04-01 | 0.00786
208 | 2015-04-01 | 0.00355
208 | 2015-04-02 | -0.0022505
208 | 2015-04-03 | 0.0056117
This question already has an answer here:
I have read about windows authentication in MVC5 for ASP.net project.
As far as i can understand, if i am using the development server's users for authentication, i have to change settings in the server to enable windows authentication, install IIS, publish website and write C# code to grant access based on whether the user is admin or basic user.
Now in my case, i am using my projects database table where i have a list of users. do i have to follow the same steps here as well like enabling authentication mode to Windows in development server settings and then write C# code to authenticate database users to my site.
Can someone tell me what steps i need to perform for getting windows authentication using my projects database table. (where all the users list who can access the site are saved in the table)
We have been having some drive space issues on our SQL server (2005) and I just ran some queries on the sys.allocation_units table and found that we have 26GB of database mail attachments. Apparently we have just been stashing this away in our msdb without any kind of clean-up for a couple years.
So I attempted using sysmail_delete_mailitems_sp but we're filling up our log and it's hitting our space limitation.
I looked in this sys sproc and all it is really doing is running
DELETE FROM sysmail_allitems
with some parameters and error handling. This is a system view that I'm assuming deletes from a collection of sysmail_xyz tables.
We do not need ANY of this old mail data. Everything we mail is already logged and archived in our application layer. Can I just run
TRUNCATE TABLE sysmail_allitems
It's a view but it's being DELETE'd so I'm wondering if I can TRUNCATE also.
If not maybe I can
TRUNCATE TABLE sysmail_attachments
but I'm afraid I'll orphan something that will break my system.
Any suggestions?
Is it possible to get column names from a query like you can get them from a view? The view example is below.. I cannot use the view example as these are queries created by users and will potentially be amended often..
SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('dbo.ViewName')
Thank you in advance..
Derek.
I have 2 tables:
1) et_pics - here information about employees:
e_post, varchar, e.g. Chairman
SELECT * FROM et_pics:
ob_no | c_name | e_post
2020 | Dribbler D.E. | Chairman
2) et_vacations – here information about vacation is stored:
e_dateTo, date, e.g 2010-08-09 00:00:00.000
SELECT * FROM et_vacations vac returns
ob_no | e_pic |c_name | e_dateFrom |e_dateTo
| 777 | 2020 |Vacation blah blah |2010-08-08 00:00:00.000 | 2010-08-09 00:00:00.000 |777 | 2020 |Vacation blah blah |2015-08-08 00:00:00.000 | 2015-08-09 00:00:00.000 |
What I need to do is to connect et_vacations to et_pics with conditions:
Can’t understand how to write right subquery – tried in this way, but no luck:
SELECT
pics.c_name,
pics.e_post,
vac.e_dateTo
FROM et_pics pics
INNER JOIN et_division div on pics.e_division = div.ob_no
INNER JOIN et_vacations vac on vac.e_pic = pics.ob_no
WHERE
(pics.e_fireDate IS NULL OR pics.e_fireDate > getDate())
AND vac.e_dateTo IN (
SELECT MAX(vac.e_pic) from et_vacations vac
GROUP BY vac.e_pic
)
ORDER BY pics.c_name;
Thanks in advance for any help to solve this question.
I am not sure this should be in this category or not but here is my issue.
I have an SSIS package that I am trying to move to our production server. It works fine on the QA server but now that I have moved it to production I am getting errors:
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login timeout expired".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Named Pipes Provider: Could not open a connection to SQL Server [2]. ".
End Error
Error: 2016-02-08 14:01:51.24
Code: 0xC020801C
Source: Data Flow Task OLE DB Destination [39652]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "MYSERVER.MYDB" failed with error code 0xC0202009. There may be error messages posted before this with more
information on why the AcquireConnection method call failed.
These errors are occurring when I try to run the package from the stored procedure I created to run the package when initiated through Access.
ALTER PROCEDURE [dbo].[sp_import]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQLQuery AS VARCHAR(2000)
DECLARE @ServerName VARCHAR(200)
SET @ServerName = 'MYSERVER'
SET @SQLQuery = 'DTExec /F ^"\\MYSERVER\Import\Package.dtsx^" '
EXEC master..xp_cmdshell @SQLQuery
END
I should mention that the package, source file and database are all located on the same machine.
Thanks, Scott
I am trying to connect to a SQL server 2005 (MSSQLSERVER, not SERVEREXPRESS or anything like that) from my PC. I have set up on a machine running Windows Server to my labtop.
I have a publication which is intended to push data to a subscriber over the internet from a remote server to our onsite local subscriber but it ends up on a wrong subscriber.Here is the scenario:
Server A is a remote server hosted in south Dakota ( Publisher)
Server B is a local server ( Virtual Box) subscriber
Server C is also a local server on a virtual box ( subscriber)
Both server B and C listens on port 1433
What I'm trying to do is to replicate a table from server A to server C but replication is pushing the data to server B
Is replication pushing the table to server A because both server B and C listens on port 1433 or Am I missing something else. Any contributions or pointers will be appreciated... Thanks folks
The below query is taking a lot of time in executing i.e. upto almost 3 hr and sometime more also. The Tables LAR_PRODUCTS and STORE_RANGE_GRP_MATCH are physical tables and contain a record count of 432837 and 103038 respectively. Any suggestion to reduce the query execution time is welcomed.
Select 1 From LAR_PRODUCTS prd with (nolock)
Join STORE_RANGE_GRP_MATCH srg with (nolock)
On prd.Store_Range_Grp_Id = srg.Orig_Store_Range_Grp_ID
And srg.Match_Flag = 'Y'
And prd.Range_Event_Id = srg.LAR_Range_Event_Id
Where srg.Range_Event_Id Not IN (Select Range_Event_Id
From Last_Authorised_Range
I'm working on an ancient project made in VS2005 that deploys a database project to MSSQL2005.
The project has a database name configured in the Target Database field in the Build tab of the project properties. Testing the connection confirms that the settings are correct.
I usually overwrite this database using SSMS with backups downloaded from different production servers, then rebuild the database project to generate an SQL script with the statements required to apply recent schema changes to the production DB.
I'm not sure what might have changed, but recently it started to generate a different DB named like this: ProjectName_DB_GUID, interfering with the creation of the diff script.
It also deleted many rows in some tables, even though Block Incremental Deployment if data loss might occur was checked.
This apparently happens only with some of the downloaded databases, but so far I haven't been able to pinpoint the exact cause.
What might I be missing here? What can I try to diagnose the problem and restore the original behavior?
I'm working on an ancient project made in VS2005 that deploys a database project to MSSQL2005.
The project has a database name configured in the Target Database field in the Build tab of the project properties. Testing the connection confirms that the settings are correct.
I usually overwrite this database using SSMS with backups downloaded from different production servers, then rebuild the database project to generate an SQL script with the statements required to apply recent schema changes to the production DB.
I'm not sure what might have changed, but recently it started to generate a different DB named like this: ProjectName_DB_GUID, interfering with the creation of the diff script.
It also deleted many rows in some tables, even though Block Incremental Deployment if data loss might occur was checked.
This apparently happens only with some of the downloaded databases, but so far I haven't been able to pinpoint the exact cause.
What might I be missing here? What can I try to diagnose the problem and restore the original behavior?
I know that there are a lot of solutions for this but unfortunately I cannot use partition or keyword TOP. Nothing I tried on earlier posts works.
My table looks like this:
The result I want is when any completion percentage is NULL it should get the value from last non-value completion percentage, like this:
Lets say I have 2 tables
Student_table:
studentID, lname, fname
Test_table:
testid, studentid, testname, testdate
There are 2 tests student can take Test A and Test B.
Now I want to design a query or a procedure which allows to display data from student table and test_table with condition if student has taken both Test A.
Only test A info needs to displayed along with student info otherwise any test taken info should be displayed or no test info should be displayed if no test available.
SELECT DATEDIFF(minute,CONVERT(VARCHAR, CURRENT_TIMESTAMP, 102),convert(varchar, t.xxxxxx, 102)) AS daydifference FROM (SELECT MAX(autoid),xxxxxx FROM Xtable WHERE uid=3)t;
I am getting error at last "t". No column name was specified for column 1 of 't'.