Difference between jobs and maintenance plan in sql server
What is the difference between jobs and maintenance plan in sql server? I am confused about where I should use job and where maintenance plan. ...
sql-server
How to replace leading null values with the first non-null value in a row?
I'll try to avoid describing the background here. I now have a query result (not a table) which contains rows like this: ID SP1 SP2 SP3 SP4 SP5 SP6 SP7 SP8 1 null null 2500 1400 700 null null null There may be leading and/or trailing null values around a section of non-null v...
How to do automatic data archiving in SQL Server?
Hi, I have table for which every day I want to do automatic archiving. So to be clear every day I want to take information generated during that day and move it into another partition (of same table) not in another archive table. That's because I want old data to be accessible with same query as new ones. I'm using SQL Server 2005, I'v...
How to check which stored procedure is taking maximum time in sql server
I want to know what are various methods by which I can monitor which of my procedure, query is taking more time on various components(CPU cycle, scan time etc.) than already set threshold value. I want it to be logged as well. Whenever uses my site and calling some procedure. I want to make a log of all procedures crossing my threshold....
Acquiring lock - C# SQL server
Database : SQL server 2005 Programming language : C# I have a method that does some processing with the User object passed to it. I want to control the way this method works when it is called by multiple threads with the same user object. I have implemented a simple locking which make use of database. I can't use the C#'s lock statemen...
SQL Server Computed Columns - split on line break
I have a column with name and address data in a single column: Jo Bloggs Address Line 1 Address Line 2 PostCode Is it possible to then create two computed columns based on this, and if so how? e.g. RecipientName Jo Bloggs Address Address Line 1 Address Line 2 PostCode I'm using SQL Server 2005 ...
Error 42S02 from the Microsoft Drivers for PHP for SQL Server
Hi I am trying to extract some data from a SQL Sever database via PHP using the Microsoft Drivers for PHP for SQL Server. Some tables I can retrieve fine but others give the following error message: Array ( [0] => Array ( [0] => 42S02 [SQLSTATE] => 42S02 [1] => 208 [code] => 208 [2] => [Microsoft][SQL Server Native Client 10.0][SQL...
MS SQL Manegement Studio Create Database task
I need to know if the "Backup Database Task" in MS SQL Management Studio, maintenance plans, takes the free space into account or that it only backsup the data part. For e.g. if the db size is 100 MB where 60Mb is data and 40Mb is free space. Will take the backup of the whole db of size 100 or that it will take backup of the 60Mb data. ...
DataTable optimal saving to sql database
Hello, I have DataTable with same structure in SqlServer DB Table. To save data in a this DataTable to Sql DB I use code below: sqlCommand mcd; for(int i=0;i<dataTable.Rows.Count;i++) { mcd=new SqlCommand(); cmd.CommandText="Insert into MSSQLtable values("+dataTable.Rows[i][0]+dataTable.Rows[i][1]+")"; cmd.ExecuteNonQuery(); } How c...
Populating parent + child objects from SQL Server 2008 (via XML?)
I have simple DAL that consists of a SalesEnquiry object, which includes a List<T> of a Vehicle object, which is used to process incoming enquiries (XML) and write them to a DB. So far so good. However, I'm writing another app that further processes data in this DB, so I'm wanting to use these same DAL objects to retrieve and manipulate...
Create nonclustered primary keys using NHibernate and SchemaExport
We're using SchemaExport via ActiveRecord. By default it generates a table like this: create table List ( Id UNIQUEIDENTIFIER not null, Name NVARCHAR(255) null, OwnerId UNIQUEIDENTIFIER null, primary key ( Id )) SQL Server then defaults to adding a clustered index for the primary key. But I want this to be nonc...
SQL LIKE Command trouble searching for '
Hi I am facing a problem with the like command in SQL, I want to search for special characters within a column . The special characters are a single quotation mark ' and { and }.. I have tried placing these special characters under [] but still it doesn't work for ' I have also used the except option but that was also of no help.. Wa...
Microsoft Sql Server Managment studio backup size goes negative
The problem is that I need to explain the different sizes of backups that are being made of a database in a plant. Sometimes the difference between the sizes is in negative, even though that there is no data being deleted from the system. Datum Backupfile-file Size KB Diff 6/1/10 backup201006010100.bak 3355914 ...
Retrieve last row inserted with Uniqueidentifier that it is not IDENTITY
Hi, I am puzzled with a query. I need find out the LAST row added in a table with a column with datatype Uniqueidentifier column is: aspnet_Applications.ApplicationId Notes: This column Uniqueidentifier it is NOT and IDENTITY Column. I need also take the last row inserted and update it on a different table aspnet_Users.ApplicationId I...
SQL DataSet using Model
Help I am attempting to create some report on a report server model. I need to create a text type DataSet, using sql. Once I have written the SQL and click on OK it attempt to load back into Design>>Data view, but fall over. It show the below error A connection cannot be made to the database. Set and test the connection string. Add...
Drilldown Reports
Help I am attempting to create a drill down report using report server and Visual Studio 2005. I have been able to create a tree structure, but the original report looks like it opened a new report. (Crystal Report) This was done by the report hiding all other data apart from the drilled data and the data title. I can’t seem to be abl...
Livecycle: SQL Statement Info Editor
I have a datatype in a table as uniqueidentifer. In the SQL statement Editor I'm trying to map my Xpath to the statement. An Error is thrown: cannot cast uniqueidentifier to varchar. I have tried all the functions and data types I can think of. ex EXEC SetSomeSP @ProcessID = {$/process_data/@ProcessID$} I have tired with quotes, cast...
What is the use of "&" operator in SQL SERVER.
What is the use of & operator in the code specified below. IS there any benefit of using & instead of "AND". Please elaborate. CASE ( C.[Status] & F.[Status] & D.[Status] & DWT.[Status] & P.[Status] ) WHEN 1 THEN CASE ( C.IsDeleted & F.IsDeleted & D.IsDeleted & P.IsDeleted ) WHEN 0 THEN NULL ELSE 7 EN...
"String data, right truncation" warning on a select statement
I am upscaling an access 2003 database to SQL Server Express 2008. The tables appear to be created ok and the data looks ok. I have an MFC application that connects to this database. It worked fine connecting to access, but when I connect to SQL Server I am getting the following error on a select statement. DBMS: Microsoft SQL Server...
SQL Linq Question
I have the following working TSQL query in ms SQL 2008 SELECT Date, COUNT(click) AS clicks, COUNT(sale) AS sales, count(lead) as leads FROM ( SELECT ClickDate as date ,ID AS click ,CAST(NULL AS int) AS sale , CAST(null as int) as lead FROM clicks UNION ALL SELECT Date,null, ID ,NULL FROM sales UNION ALL SELECT Date,...