How to run a stored procedure when SQL Server Express Edition starts?
How is it possible to run a stored procedure when SQL Server Express Edition starts? ...
sql-server
XML Timezone - Daylight Saving
I am currently handling concurrency on my page using a DateTime field. The data is passed through ASP.NET using an XML which comes with Timezone. So to adjust timezone I am adding 4 hrs to time received (EST) in my procedure and then comparing. Now it was all working fine till Nov 1st. Now the time I am receiving is 5 hrs behind the t...
SQL Data Source - store procedure and return parameter
I have a store procedure in SQL Server which returns a value: CREATE PROCEDURE [dbo].[insertProc] @value1 INT, @value2 INT AS BEGIN SET NOCOUNT ON; INSERT INTO table1(value1,value2) VALUES (@value1,@value2) RETURN SCOPE_IDENTITY(); END I connect to the DB from ASP.NET using SQL Data Source, which is configured li...
SQL Server Isolation Levels - Repeatable Read
Hello I'm having problems getting my head round why this is happening. Pretty sure I understand the theory, but something else must be going on that I don't see. Table A has the following schema: ID [Primary Key] Name Type [Foreign Key] SprocA sets Isolation Level to Repeatable Read, and Selects rows from Table A that have Type=1. ...
Check if select query has results inside SQL Server stored procedure
I've done this before but can't find where :/ I want to create a variable inside a stored proc and return its value which will be set depending on whether or not other selects return results. basically something like this: @myVar int = 0 BEGIN IF SELECT SomeThing FROM SomeTable @myVar = 1 ELSE IF SELECT SomeOther From SomeO...
How can I determine if a SQL Server stored procedure parameter has a default?
Is there a way to determine programmatically if a SQL Server stored procedure parameter has a default? (Bonus points if you can determine what the default is.) SqlCommandBuilder.DeriveParameters() doesn't even try. Thanks in advance for your help! EDIT: I honestly don't care if it's a SQL Query, an SMO object, etc. ...
Replication - Issues with turning on SQL Agent on Local Machine
Hello, I want to learn more about replication and want to turn it on for my local copy of SQL2008. I watched one video that said I can create a publication on my local machine and then replicate to a different database on the same machine. Their example works and I want to try it. Is there anything I should be concerned about regardi...
Nervous Running Queries (SQL Server): What do you think about this?
I'm a frequent SQL Server Management Studio user. Sometimes I'm in situations where I have an update or delete query to run, but I'm afraid some typo or logic error on my part is going to cause me to make undesired, massive changes to a table (like change 1000 rows when I meant to change 2). In the past, I would just clench my fists ...
Single or multiple databases
SQL Server 2008 database design problem. I'm defining the architecture for a service where site users would manage a large volume of data on multiple websites that they own (100MB average, 1GB maximum per site). I am considering whether to split the databases up such that the core site management tables (users, payments, contact detail...
T-SQL: Update first row of recordset
I have a query (A) that can returns multiple rows in date order: SELECT encounter_id, department_id, effective_time FROM adt WHERE event_type IN (1,3,7) ORDER BY effective_time I have another query (B) that returns a single row: SELECT encounter_id, department_id, arrival_time FROM ed WHERE event_type = 50 I would like to joi...
How to query from a stored procedure in SQL Server?
Let say I have a simple Stored Procedure: ALTER PROCEDURE [dbo].[myProc] AS BEGIN SELECT * FROM myTable END How can I do a WHERE statement in Microsoft SQL Server Management Studio to the stored procedure? Something like that: SELECT * FROM myProc WHERE x = 'a'; -- But that doesn't work... ...
tsql to know if the server has integration,reporting,analysis,notification services
Hi, I want to know if a server has integration,reporting,analysis,notification services irrespective of the version(2000 or 2005). Currently, i am using xp_cmdshell 'net start' but it has too much information. Any help? ...
How do you force linq to sql to use the correct data type for sql parameters?
I have a situation where a certain linq query that runs against a multi-million row table is taking far to long to run. I dissected the linq query output and found that it was creating the parameters for the where clause using the wrong data type. For instance, one field was defined as a Char(12) in the database, but the parameter it was...
NHibernate Aggregate Subquery
Hi all, I have a problem with NHibernate that I don't seem to be able to find a simple way around. I have the following database: Game: ID, Score, Match_ID Match: ID A match consists of 3 games. I want to find out what the maximum Match score is, so the following SQL would do the trick: select max(a.total) from (select Match.ID...
Is using the built in asp.net authentication provider a bad idea due to guid being used as primary key etc
I'm at the stage in a project where I need to extend the user membership fields in a asp.net mvc website. I have gone through these different thought processes. Using "username" to join users to records. Very bad idea due to a user wanting to change their username etc. Then I thought I could connect the guid of userId to the other tab...
Invoke or BeginInvoke cannot be called on a control until the window handle has been created.
Hi I am trying to install SQL Server 2008 Developer Edition x64 on a Windows Server 2008 VM. I run the installer for a standalone installation and this works fine for the setup support rules bit. That passes ok but then I get this error: TITLE: SQL Server Setup failure. SQL Server Setup has encountered the following error: Invoke or ...
Using PIVOT in SQL Server 2008
Let's say I have some data, either in a SQL Server 2008 table or a [table]-typed variable: author_id review_id question_id answer_id 88540 99001 1 719 88540 99001 2 720 88540 99001 3 721 88540 99001 4 722 8...
Adjusting Timezone - Convert XML DateTime to SQL DateTime
We are using TypedDataSet in our application. Data is passed to procedure in form of XML for insert/update. Now after populating DE with data, datetime remains the same though timezone information is added as below. Date in DB: 2009-10-29 18:52:53.43 Date in XML: 2009-10-29T18:52:53.43-05:00 Now when I am trying to convert below XML...
Change app.config connection string depending on PC
Hi guys I'm doing a c# course at university, and now I'm beginning linQ to xml, linQ to Sql-Server, etc. I work with the example projects in two PCs (university PC and office PC) Is there an easy way to change connection string (from app.config) at runtime, or designtime (using a constant in main.cs) so I can use a connection string at u...
Grouping Query Help in sql server 2005?
My table TEST has the following rows: test | 1 test | 2 test | 3 How I query it to get the following result? test | 1 - 2 - 3 ...