I'm trying to use LIKE in report builder and I got this error:
An error occurred while reading data from the query result set.
Conversion failed when converting the varchar value '%' to data type int.
(Microsoft SQL Server, Error: 245)
this is my code:
select count(*) from projects where received LIKE '%'+@received+'%'
and institution# = @institution and program# = @program
how could i solve it?
I have got a table where one of the column has text mixed with HTML data. This was due to a bug (fixed) in a script, but the SQL data needs to be edited to reflect the changes. The column has a type nvarchar(max, null). All I need to do is find tags such as <a img="lkss">,<div attr=val> and replace them with empty string "". I looked into this, but the solution says how to replace the entire contents based on one pattern. My problem is that I need to replace contents partly, but preserve clean text (i.e. not HTML tags/attributes). Any suggestion/help is appreciated.
Test column data:
<div attr=val; color=somecolor>inside text<div some=val><a some=val>Inside anchor
Expected result:
inside textInside anchor
I am creating a table collaborating a lot of mapped data, but the query result would be far better suited to produce one row per unique ID than it currently is.
For example, I currently have:
UNIQUE ID | ID NAME | SourceName | SourceDate | StoreName | StoreOrder
1 | First | Example 1 | 1990 | Barnes | 1
1 | First | Example 1 | 1990 | Noble | 2
1 | First | Example 2 | 1996 | Barnes | 1
1 | First | Example 2 | 1996 | Noble | 2
2 | Second | Example 1 | 1990 | Barnes | 1
.... And so on ...
Source info and Store info are not related, they are only related through UniqueID.
What would be ideal is:
UNIQUE ID | ID NAME | SourceName1 | SourceDate1 | SourceName2 | SourceDate2 | StoreName1 | StoreOrder1| StoreName2 | StoreOrder2
1 | First | Example 1 | 1990 | Example 2 | 1996 | Barnes | 1 | Noble | 2 |
I know this is a bit of a messy solution, but it is required for further analysis. A single table, single row per unique ID would be perfect, no matter the column names etc.
It would also be great to have the column names dynamically allocated. For example if one ID has 4 stores allocated then there would be StoreName1 through to StoreName4 (Empty data entries would be 0 or NULL).
Does anyone have any suggestions on how to perform this?
I'm using EF6 code first with ASP.NET Web API.
Suppose there are two model classes
public class RawMaterial {
public int ID { get; set; }
public string Name { get; set; }
}
public class Furniture {
public int ID { get; set; }
public string Name { get; set; }
public virtual ICollection<RawMaterial> RawMaterials { get; set; }
}
DbContext
public class FurnitureContext : DbContext {
public DbSet<RawMaterial> RawMaterials { get; set; }
public DbSet<Furniture> Furnitures { get; set; }
}
And in the initializer class,
protected override void Seed (FurnitureContext context) {
var glass = new RawMaterial { Name = "glass" };
var wood = new RawMaterial { Name = "wood" };
var paint = new RawMaterial { Name = "paint" };
context.RawMaterials.AddRange(new RawMaterial[] { glass, wood, paint });
var chair = new Furniture {
Name = "chair",
RawMaterials = new RawMaterial[] { wood, paint }
};
var coffeeTable = new Furniture {
Name = "coffee table",
RawMaterials = new RawMaterial[] { wood, glass }
};
context.Furnitures.AddRange(new Furnitures[] { chair, coffeeTable });
context.SaveChanges();
}
I encountered a run time error complaining "an item cannot be removed from fixed size array". So clearly the program's trying to remove wood from chair before adding it to coffeeTable. So I changed the initializations to use Lists, as
var chair = new Furniture {
Name = "chair",
RawMaterials = new List<RawMaterial> { wood, paint }
};
After that, I could clearly see that wood was indeed removed from one of the furnitures' RawMaterials.
I also tried selecting wood from the context by
var chair = new Furniture {
Name = "chair",
RawMaterials = new RawMaterial[] {
context.RawMaterials.Where(r => r.Name == wood.Name).FirstOrDefault()
}
};
The result is still the same.
So my question is: how can I add the test data such that wood is present in both chair and coffeeTable? I am aware that this is not typically how many-to-many relations are defined, since RawMaterial does not know of Furniture. Or should I define the models the other way?
Thank you.
Edit: I check the database tables in SQL Server Object Explorer, and the SQL for RawMaterial is
CREATE TABLE [dbo].[RawMaterials] (
[ID] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (MAX) NULL,
[Furniture_ID] INT NULL,
CONSTRAINT [PK_dbo.RawMaterials] PRIMARY KEY CLUSTERED ([ID] ASC),
CONSTRAINT [FK_dbo.RawMaterials_dbo.Furnitures_Furniture_ID] FOREIGN KEY ([Furniture_ID]) REFERENCES [dbo].[Furnitures] ([ID])
);
GO
CREATE NONCLUSTERED INDEX [IX_Furniture_ID]
ON [dbo].[RawMaterials]([Furniture_ID] ASC);
And the SQL for Furniture is
CREATE TABLE [dbo].[Furnitures] (
[ID] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (MAX) NULL,
CONSTRAINT [PK_dbo.Furnitures] PRIMARY KEY CLUSTERED ([ID] ASC)
);
So basically entity framework is not creating the database the way I need. That's why I cannot add wook to both chair and coffeeTable. How should I modify the Entity Models?
My data somewhat looks like this:
Product Attribute1 Attribute2 P1 1 -1 P1 1 -1 P1 1 -1 P1 1 -1 P1 1 -1 P2 1 1 P2 1 1 P2 1 1 P2 1 1 P2 1 1 . . .
Now I need to find out the correlation between attribute1 and attribute2 for each product individually. My actual data set contains around 100 products. Hence my output should be somewhat like this" Product Correlation P1 -1 P2 1 . . . How will I go about that in SAS/SQL?
I know this has come up a lot in the past but none of the fixes I've Googled or found on here has worked for me in this instance.
I'm running a fairly standard SQL Server Agent Job as a Transact-SQL script with the follow details: (replaced some stuff as *** for my boss's sanity)
-- Start T-SQL
USE msdb
EXEC sp_send_dbmail
@profile_name = 'MainProfile',
@recipients = 'test@test.co.uk',
@subject = 'T-SQL Query Result',
@execute_query_database = 'test30',
@query = 'SELECT ReferralReceivedDate,testRef,testcoMetadata.testcoRef,testcoMetadata.TimeSpentWithtester
FROM TestCasesTable, TestcoMetadata
WHERE testcasestable.CaseID = TestcoMetadata.CaseID AND AgencyName = [Test Injury] AND TestcoMetadata.TestcoRef IS NOT NULL AND TestcoRef <> ''
order by ReferralReceivedDate desc',
@attach_query_result_as_file=1,
@query_attachment_filename = 'Results.csv',
@query_result_separator = ','
-- End T-SQL --
The query itself runs fine as a normal query with no issues. The owner of this job has been used on other jobs again with no problems. In the step properties the Database selected is the same one as that mentioned in the @execute line.
I have a feeling this is either falling over the way it's trying to create the csv or something to do with permissions with dbmail part. I'm only a part time DBA so this has now lost me and I need help.
I have sql server developer edition in my computer and im doing many databases to same customer, so which edition of sql server should i install in customer server pc to give him th ability to work in all databases at same time frm differnt applecations.