I am trying to run a sequence from a stored procedure. I am passing the name of the sequence to the stored procedure and the stored procedure will return the sequence value, but the stored procedure is not recognizing the passed sequence name. the error says:Incorrect syntax near '@SeqName'.
Here what I have tried:
ALTER PROCEDURE [dbo].[GetSeqNextValue] (@SeqName varchar(50), @NewNum bigint output)
AS
BEGIN
SET @NewNum = NEXT VALUE FOR @SeqName
END
What is the correct way to setup a SQL Server datasource on Widlfly?
I need to access a SQL Server database from my web application which runs on Wildfly.
I have setup the datasource as follows:
<datasource jta="false" jndi-name="java:jboss/db" pool-name="db" enabled="true" use-ccm="false">
<connection-url>jdbc:jtds:http://sqlserverIP_ADDRESS;instance=SQLEXPRESS;DatabaseName=DB</connection-url>
<driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>
<driver>jtds-1.3.1.jar</driver>
</datasource>
This works fine except that when the SQL Server is restarted, the connection is lost and the datasource doesn't manage to recreate one. So I get errors like:
Invalid state, the Connection object is closed.
This post suggests adding some validation, so I did this:
<validation>
<check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
<validate-on-match>false</validate-on-match>
<background-validation>false</background-validation>
</validation>
But that does not solve the problem and I still get the same "connection closed" error from time to time.
This other post suggests using a DataSource instead of a Driver, so I have added this to my configuration:
<datasource-class>net.sourceforge.jtds.jdbcx.JtdsDataSource</datasource-class>
But when I test the connection I get an exception:
java.sql.SQLException: The serverName property has not been set.
at net.sourceforge.jtds.jdbcx.JtdsDataSource.getConnection(JtdsDataSource.java:150)
I am really struggling with a query. I want the sum of each month between Aug 2014 and July 2015. If I specify the between dates in the where clause it sums all the months.
Here is my query:
DECLARE @CurrentYear int = DATEpart(year,getdate())
DECLARE @PreviousYear int = DATEpart(year,getdate()) -1
SELECT
SUM(CASE WHEN a.fin_period = concat(@PreviousYear,'08') THEN a.balance ELSE 0 END) AS BalanceAug ,
SUM(CASE WHEN a.fin_period = concat(@PreviousYear,'09') THEN a.balance ELSE 0 END) AS BalanceSep ,
SUM(CASE WHEN a.fin_period = concat(@PreviousYear,'10') THEN a.balance ELSE 0 END) AS BalanceOct ,
SUM(CASE WHEN a.fin_period = concat(@PreviousYear,'11') THEN a.balance ELSE 0 END) AS BalanceNov ,
SUM(CASE WHEN a.fin_period = concat(@PreviousYear,'12') THEN a.balance ELSE 0 END) AS BalanceDec ,
...etc.
FROM subaccount_history a with (nolock)
WHERE fin_period between concat(@PreviousYear,'08') and concat(@CurrentYear,'12')
The issue is with the between clause, i tried group by but that also doesn't work. It sums everything between the 2 dates specified.
For starters, I'm fairly new to SQL.
So, I am trying to develop an application in asp.net with blogs and categories in two languages. One of the functionalities is returning the name of a category in a particular language. Not all categories have two languages. Some have English, others have Italian, others have both. Here's the function that performs the select i was talking about.
DECLARE @Name NVARCHAR(250)
IF EXISTS(SELECT Name = Coalesce(BlogCategoryTranslation.Name, ' ')
FROM BlogCategoryTranslation WHERE BlogCategoryID = @CategoryID AND LanguageID=@LanguageID)
BEGIN
SET @Name=(SELECT Name
FROM BlogCategoryTranslation WHERE BlogCategoryID = @CategoryID AND LanguageID=@LanguageID)
END
ELSE
BEGIN
SET @Name=(SELECT top 1 Name FROM
BlogCategoryTranslation WHERE BlogCategoryID = @CategoryID AND LanguageID=@LanguageID);
END
RETURN @Name
Now I have a stored procedure that returns the Category Name for a given language. What I want is to always return a Category. If it doesn't exists in a particular language, i want to display the record in other language. Is this possible and if it is, what would be the suggestions in order to do that.
ALTER PROCEDURE [dbo].[BlogCategoryLanguage]
@BlogCategoryID INT,
@LanguageID INT
AS
BEGIN
SELECT BlogCategoryID AS 'CategoryID',
Language.Name AS 'Language',
dbo.BlogNameCategoryByLanguage(1,1) as 'Name',
dbo.Blog_PublishedInCategory(1,BlogCategory.ID) AS 'Published'
FROM BlogCategoryTranslation
INNER JOIN BlogCategory
ON BlogCategory.ID = BlogCategoryTranslation.BlogCategoryID
INNER JOIN Language
ON Language.ID = BlogCategoryTranslation.LanguageID
WHERE BlogCategoryID = 1
I would really appreciate a few tips. It's the first time i have posted a question here and I'm not quite sure how this works. If this is a repost somehow, sorry for that.
In my c# code I have the following method that creates a document in the database, adds metadata regarding the document to the database and then updates some information regarding the date the repository was last updated. This method is often called numerous times in quick succession as multiple file uploads are common. However I am having problems with the code failing due to deadlock in sql server.
private IEnumerable<DocumentMetadata> CreateDoc(int? jobId, int?repositoryId, int? folderId, string documentTypeString, IEnumerable<DocumentModel> files)
{
if ((jobId == null && repositoryId == null) || (jobId != null && repositoryId != null))
{
throw new InvalidOperationException("Either job id or repository id must be specified");
}
using (var tran = new TransactionScope())
{
List<DocumentMetadata> newDocuments = new List<DocumentMetadata>();
var documentType = GetDocumentTypeByPrefix(documentTypeString);
if (folderId == null)
{
// Find the root folder
var job = getJob(jobId);
var rootFolder = getRootFolder(job);
// If we can't find a root folder, create one
if (rootFolder == null)
{
rootFolder = CreateRootDirectory(job);
}
folderId = rootFolder.FolderId;
}
User currentUser = _userService.GetCurrentUser();
foreach (var file in files)
{
var document = new Document() { Document1 = file.Data };
var documentMetadata = new DocumentMetadata
{
Document = document,
CreatedDate = file.CreatedDate,
FileName = file.Filename,
FileSize = file.Data.Length,
FolderId = folderId,
DocumentType = documentType,
JobId = jobId,
RepositoryId = repositoryId,
User = currentUser
};
_unitOfWork.DocumentMetadata.Add(documentMetadata);
newDocuments.Add(documentMetadata);
}
// set repository updated date
if (repositoryId != null)
{
DocumentRepository repo = GetDocumentRepository(repositoryId);
if (repo != null)
{
repo.UpdatedDate = new DateTimeOffset(DateTime.Now);
}
}
_unitOfWork.SaveChanges();
tran.Complete();
return newDocuments;
}
}
After some debugging it would appear that the updating of the repository id is causing the deadlock problem. If I remove this code block outside of the transaction all files are saved with no errors.
Why would this code block
if (repositoryId != null)
{
DocumentRepository repo = GetDocumentRepository(repositoryId);
if (repo != null)
{
repo.UpdatedDate = new DateTimeOffset(DateTime.Now);
}
}
cause the deadlock? No other access is being made to the DocumentRepository table apart from in this method - as the locks are obtained in the same order surely there should be no deadlock?
What is it about this code that is leading to deadlock?
Updated: The code for GetDocumentRepository is:
public DocumentRepository GetDocumentRepository(int repositoryId)
{
var result = DocumentRepositories.SingleOrDefault(x => x.RepositoryId == repositoryId); return result;
}
Here is what I am currently doing in one of my repository classes:
private IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnString"].ConnectionString);
public IEnumerable<Product> GetProducts(int categoryId = null, bool? active = null)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT * ");
sql.AppendLine("FROM Product ");
sql.AppendLine("WHERE @CategoryId IS NULL OR CategoryId = @CategoryId ");
sql.AppendLine(" AND @Active IS NULL OR Active = @Active");
return this.db.Query<Product>(sql.ToString(), new { CategoryId = categoryId, Active = active }).ToList();
}
One thing I want to do is put the IDbConnection property in a BaseRepository that all of my other repos inherit from. What do I do to ensure my database connection opens and closes properly in each of my data access functions like the example above? Here is what I currently do with Entity Framework (w/ a using statement around each function, but now I am switching the DAL to use pure Dapper:
using (var context = new MyAppContext())
{
var objList = (from p in context.Products
where (categoryId == null || p.CategoryId == categoryId) &&
(active == null || p.Active == active)
select p).ToList();
return objList;
}
I noticed in the Dapper examples that everything is wrapped in a using statement like I would expect, but occasionally I see them wrapping their functions in the follow using:
using (var connection = Program.GetClosedConnection())
GetClosedConnection() returns a new SqlConnection, but what is the difference between the two?
public static SqlConnection GetOpenConnection(bool mars = false)
{
var cs = connectionString;
if (mars)
{
SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder(cs);
scsb.MultipleActiveResultSets = true;
cs = scsb.ConnectionString;
}
var connection = new SqlConnection(cs);
connection.Open();
return connection;
}
public static SqlConnection GetClosedConnection()
{
return new SqlConnection(connectionString);
}
How to truncate selective tables in SQL Server 2008, I have a list of tables which may be excluded during truncate process.
Anybody can guide?