Hi, I have a SQL Server Stored Procedure that looks like this: CREATE PROCEDURE [dbo].[my_stored_procedure] ( @num INT, @name VARCHAR(50), @start_date DATETIME, @end_date DATETIME ) AS BEGIN ... END And an Entity Object with a NamedNativeQuery that looks like this: @Entity @NamedNativeQuery( ...
I know Oracle offers several refreshmode options for their materialized views (on demand, on commit, periodically). Does Microsoft SQLServer offer the same functions for their indexed views? If not, how can I else use indexed views on SQLServer if my purpose is to export data on a daily+ on-demand basis, and want to avoid performance ov...
If I have a common table expression for a family with mother & father, how can I increment the 'Generation' counter? A family should have the child as generation zero, parents as generation 1, and the four grandparents as generation 2. But the loop is performed twice, one for each set of grandparents. ;WITH FamilyTree AS ( SELECT ...
We have a situation where table 'A' is a parent table and there are probably a dozen or so tables that are children of 'A'. In every case, there is a 1:M relationship between 'A' and the children tables. It was decided yesterday that these dozen 1:M relationships should be converted to a 1:1 relationship. So, for each of the dozen child...
Hello, i need to call sqlcmd from stored procedure to export table to text file? ...
Are there any utilities available that allow you to bulk extract binary data from a SQL Server table? For example, I have a number of Word documents stored in a table and I would like to extract all of these documents into files. I know could do this by writing some .NET code, but I only need to do it once. ...
We are testing our application for Unicode compatibility and have been selecting random characters outside the Latin character set for testing. On both Latin and Japanese-collated systems the following equality is true (U+3422): N'㐢㐢㐢㐢' = N'㐢㐢㐢' but the following is not (U+30C1): N'チチチチ' = N'チチチ' This was discovered when a test c...
I'm currently looking at ways to pass lists of integers in a SQL query, and try to decide which of them is best in which situation, what are the benefots of each, and what are the pitfalls, what should be avoided :) Right now I know of 3 ways that we currently use in our application. 1) Table valued parameter: Create a new Table Valued...
I have the following query: SELECT COUNT(*) FROM Address adr INNER JOIN Audit a on adr.UniqueId = a.UniqueId on a database (1.3 million addresses, more than 4 million audits) both UniqueId columns are clustered primary keys The query is taking quite long to complete. I feel dumb, but is there any way to optimize it? I want t...
Hello. I faced the following problem: there's a user who has to execute a stored porcedure (spTest). In spTest's body sp_trace_generateevent is called. sp_trace_generateevent requires alter trace permissions and I don't want user to have it. So I would like user to be able to execute spTest. How can I do that? Thank you for your help. ...
I have recently been working with a COM+ component that processes an input XML file, and makes a number of database updates based on the supplied data. Running SQL Profiler with EventClass Exception, and User Error Message selected, I see: Exception Error: 102, Severity: 15, State: 1 User Error Message Incorrect syntax near '3'. Ho...
I'm getting my first exposure to data warehousing, and I’m wondering is it necessary to have foreign key constraints between facts and dimensions. Are there any major downsides for not having them? I’m currently working with a relational star schema. In traditional applications I’m used to having them, but I started to wonder if they wer...
Background: I have a site built in ASP.NET with Sql Server 2005 as it's database. The site is the only site on a Windows Server 2003 box sitting in my clients server room. The client is a local school district, so for data security reasons there is no remote desktop access and no remote Sql Server connection, so if I have to service the...
i have money field in sql table when i m extracting record by table and binding it to datagrid it show money as well 100.0000 four character from decimal i need only two . Plz suggest. ...
I am using OPENQUERY to do an insert from MSSQL 2005 to MySQL. How do I get at the last inserted id on the MySQL db back into my MSSQL procedure? ...
Hello, I have Full Text catalog created in SQL Server 2005, when I search the text like "Bolivia's History", it returns all the result matching to that, but if I use "Bolivias History", it does not return anything, I am very new to Full Text Search stuff, any lead how to ignore the special character ("'"), in Full Text Search? Thanks ...
Hi, Is there a way to get the settings of a SQL Server instance via C# or TSQL? And then apply these settings to another SQL Server? Thanks ...
I have a stored procedure that inserts batches of millions of rows, emerging from a certain query, into an SQL database. It has one parameter selecting the batch; when this parameter is omitted, it will gather a list of batches and recursively call itself, in order to iterate over batches. In (pseudo-)code, it looks something like this: ...
How do I know when a project is just to big for MySQL and I should use something with a better reputation for scalability? Is there a max database size for MySQL before degradation of performance occurs? What factors contribute to MySQL not being a viable option compared to a commercial DBMS like Oracle or SQL Server? ...
In a table i want to ensure that only unique vales exist over the five-column key: Timestamp Account RatingDate TripHistoryKey EventAction ========= ======= ========== ============== =========== 2010511 1234 2010511 1 INSERT 2010511 1234 2010511 4 INSERT 2010511 1234 2010511 7 ...