Is a SqlServer View always up to date?
Will a sqlserver view always be up to date or can there be a delay on it? ...
sql-server
Can you improve performance with sqlserver views?
I have a sql function which looks up the parent of a certain node in a left right tree. This takes a lot of time. If I create a view that stores for each node its parent, will that speed up things? (I would then ask the view to get the parent node). ...
Most efficient way of getting record counts from a related table?
I have a stored procedure that has this select statement in it: select (select Count(FaqId) from faq_read_stats where faqid = Faq.Id) as ReadCount, Faq.* from Faq where Faq.Show = 1 and Faq.Active = 1 This gives me the results I want but it seems like having the ad-hoc select count in there might not be optimized for SQL Ser...
SQL Server and windows authentication in IIS7
I'm trying to get an ASP.NET website running on Vista (IIS7), using SQL Server and Windows Authentication. No matter what I do, when I connect to the database, I get the exception: SqlException was unhandled Login failed for user 'MyDomain\MachineName$'. It doesn't seem to matter what settings I apply, I cannot get IIS7 to pa...
Select two columns for two different tables in SQL Server 2000.
I have a database with two tables (Table1 and Table2). Table1 has one column ColumnA and Table2 has one column ColumnB i want to select both the columns, looking for something like: ColumnA in Table1: a b c ColumnA in Table2: d e f Result set should be: a d b e c f Thanks in advance.. ...
C# Display Join Query in DataGridView at designTime using TableAdapter, etc.
I have a DataGridView I also have some tableAdapters (groupTableAdapter, userTableAdapter) generated from sqlserver database. I have created a JOIN query in userTableAdapter that shows users with their correspoding groupname. Of course, I've got the classic warning Visual Studio throws regarding it is not the original bla bla bla.....
Does GetDate() get re-evaluated for each iteration within loop within a transaction?
I have a scenario where I am looping through a resultset within a transaction and I need to INSERT a unique datetime value within a table for each iteration through the resultset - will GetDate() be recalculated each time or will it only be calculated the first time and then be the same for each iteration through the loop? My pseudo-c...
How do you select a stored procedure's return value?
It seems like this would would be trivial, but my SQL is not up to par and I can't figure it out. I just want to select a returned scalar from a stored procedure. Basically like this: select dbo.sProcedure @param But that throws an error. It's not really an option to return a result set from within the procedure. Is it possible to do ...
Use Where In with Param
How can I use a param for mulitple values in a "where in" query: Select [..] where str in ('AB','BC') Of course, this will not allow multiple values: Select [..] where str in (@param) ...
How does Microsoft Navision keep track of users/how to check at login myself
Hi, We have a Navision server with 3 licenses. One license should ALWAYS be allocated to be used by the eCommerce website. The other two are for the admin peeps to use. Unfortuneately, from time to time we get this dreaded error on the website: System.Web.HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' w...
Are there any built in SQL Server 2005 functions to serialize / deserialize string parameters to a table?
The big question is are there any "built in" SQL Server 2005 functions to serialize / deserialize string parameters to a table variables? The rest explains my need further, but may not be necessary. I have a SQL SP I would like to refactor. It currently accepts five customers ids and five sets of order ids relevant to those customers....
LINQ to SQL problem with bit/booleans
I'm having an issue with LINQ2Sql where the value in the database is not getting to my code correctly. Everything is OK except for columns defined as a bit field in the database. In my code after the LINQ query, the value for bit fields is always false. What the heck is going on? I've already tried deleting the table from the SQL desige...
SQL Server and SqlDataReader - Trillion Records - Memory
I've never tried this - so I don't know if I'd run into memory issues. But can a SqlDataReader read a trillion records? It's all streamed correct? I'm a little green to what the SQL/TDS protocol is doing under the covers. UPDATE Translate Trillion to mean very large number. I probably should have said something like 1 billion or 100 mi...
End User Ad-Hoc Reporting Tool: Microsoft SQL Server Management Studio or Microsoft Access?
Our centralized IT department has suggested two primary ad hoc query tools for our general user base of approximately 200 staff members: Microsoft SQL Server Management Studio 2008 (SSMS) Microsoft Access 2003 Environment The backend database is a read-only Microsoft SQL Server 2005 database. The schema is 400+ tables; allowing a...
T-SQL's equivalent of Oracle's %TYPE operator?
When writing a procedure in PL/SQL, I can declare a parameter's type as myTable.myColumn%TYPE so that when I alter myColumn's type from say varchar2(20) to varchar2(50) I don't have to change the procedure's parameter type. Is there something similar in T-SQL? ...
Is it possible to insert data in MySQL from a SQL Server trigger?
A fun question for a Friday afternoon, no? As the question states, is it possible to write a SQL Server trigger that inserts data into a MySQL table? EDIT: Yes, I know this is basically a dumb idea and that the application should be doing it and so on. But this is a legacy Classic ASP application which we're transitioning to MySQL; a...
Programatically populating ValidValues property of ReportParameters in .NET for SSRS
I am trying to populate an SSRS parameter's ValidValues property at run-time without writing custom stored procedures. The issue is that the available values for a certain parameter change depending on a user's security level. I'd like to keep the logic for it in the code rather than in stored procedures. Is there a way to populated Vali...
Database cascading error
I have three tables in my database: Events, Jobs, and CollectableEntities. Events has a nullable FK pointing to Jobs' PK. Events and Jobs both have non-nullable FKs (that are also those table's PKs) pointing to CollectableEntities' PK. These FK relationships are set to cascade on update and delete. Basically, when you delete a Collecta...
How do I find all stored procedures that insert, update, or delete records?
Is it possible, without parsing source, to select a list of all sproc names that insert, update, or delete records? I need to create a TSQL utility script that will do this. Efficiency is not an issue because it will be run only a few times a year (Curse'rs I mean Cursors are ok). Ideally this script would not include updates to temp or ...
Stored procedure performance randomly plummets; trivial ALTER fixes it. Why?
I have a couple of stored procedures on SQL Server 2005 that I've noticed will suddenly take a significantly long time to complete when invoked from my ASP.NET MVC app running in an IIS6 web farm of four servers. Normal, expected completion time is less than a second; unexpected anomalous completion time is 25-45 seconds. The problem doe...