SQL Server 2005 Express Limitation

Functionally, SQL Server Express is in many ways the same as the full edition of SQL Server. Many databases created in other editions of SQL Server can work transparently.

The limitations to Express, however, need to be spelled out in detail:

• No built-in management tools. Microsoft has a query tool called Express Manager that is available as a separate download, but as of this writing, it is an unsupported pre-release package.



• Support for only 1 CPU. In systems with multiple CPUs, Express will only bind to one CPU at a time, and it cannot run queries in parallel.



• 1 GB RAM. Express cannot use more than 1 GB of RAM at a time for queries and data pages. The program's own memory footprint is not counted.



• 4 GB maximum database size. No one database in Express can be larger than 4 GB, but there is no limit on the number of databases you can use in Express.



• No analysis or reporting services. Data mining, Data Transformation Services (DTS) and reporting functions are not available for Express.



• Other functions not available in Express include clustering or mirroring, full-text indexing or searching, SQLMail, indexed views, partitioned views and SQL Agent.
  

If the environment or application you're working on requires any of these, the full version of SQL Server may be the better choice rather than trying to work around the limitations of Express. Sometimes workarounds are possible -- SQL Agent functions, for instance, might be mimicked through VBScript and the Task Scheduler -- but for projects with a broad scope, it might be easier to use the full edition of SQL Server.

restore database with backup and transection logs

USE master
GO
-- First determine the number and names of the files in the backup.
RESTORE FILELISTONLY
FROM disk = 'C:\yourfolder\yourdbbackupfile.BAK'
RESTORE DATABASE yourdatabasename
FROM disk = 'C:\yourfolder\yourdbbackupfile.BAK'
WITH NORECOVERY,
-- change location
MOVE 'xxx_Data' TO 'C:\newfolder\newfile.MDF',
MOVE 'xxx_Log' TO 'C:\newfolder\newfile.LDF'
GO

RESTORE LOG yourdatabasename
FROM disk = 'C:\yourfolder\logfile1.TRN'
FROM disk = 'C:\yourfolder\logfile2.TRN'
WITH NORECOVERY
GO


-- WITH NORECOVERY need attach database, or WITH RECOVERY then dont need attach.

Parse text field in sql server

Parsing TEXT field with Long String

ALTER PROCEDURE [dbo].[DrawMap_KMLImport]
@ProcedureID int,
@ProjectID int,
@Shape varchar(30) = null,
@ObjName varchar(100) = null,
@Notes varchar(1000) = null,
@Allcoords text = null,
@Fillcolor int = null,
@ForeColor int = null

AS
declare @selectByte int
declare @coords varchar(7600)
declare @startI int
set @selectByte = 7600
if @ProcedureID =0
begin


if right(@coords,1) <> ' '
set @coords = @coords + ' '
declare @libID integer
declare @objID varchar(40)
if @shape = 'point'
set @libID = 58
else if @shape = 'polyline'
set @libID = 32
else
set @libID = 30
set @objID = NEWID()
insert into drawmap_objects (ObjID, objName, ProjectID, libID, visible, fillcolor, forecolor)
values(@objID, @objName, @projectID, @libID, 1, @fillcolor, @forecolor)

declare @latlong varchar(30)
declare @long varchar(30)
declare @lat varchar(30)
declare @index int
declare @pointID varchar(40)
declare @i int
set @i = 0
set @startI = 1
while @startI + @selectByte - 1 <= datalength(@Allcoords) begin set @coords = substring(@Allcoords, @startI, @selectByte) WHILE CHARINDEX(',0 ',@coords) > 0
BEGIN
set @i = @i + 1
set @index = CHARINDEX(',0 ',@coords)
SELECT @latlong = ltrim(rtrim(SUBSTRING(@coords,1,@index -1)))
select @long = substring(@latlong, 1, CHARINDEX(',',@latlong)-1)
select @lat = substring(@latlong, CHARINDEX(',',@latlong)+1, datalength(@latlong))

set @pointID = NEWID()
insert into drawmap_point(pointID, objID, pointindex, lat, long)
values(@pointID, @objID, @i,@lat, @long)

SELECT @coords = SUBSTRING(@coords,@index+3,DATALENGTH(@coords))
END
set @startI = @startI + @selectByte
end
set @coords = @coords + substring(@Allcoords, @startI, datalength(@Allcoords) - (@starti) + 1)

WHILE CHARINDEX(',0 ',@coords) > 0
BEGIN
set @i = @i + 1
set @index = CHARINDEX(',0 ',@coords)
SELECT @latlong = ltrim(rtrim(SUBSTRING(@coords,1,@index -1)))
select @long = substring(@latlong, 1, CHARINDEX(',',@latlong)-1)
select @lat = substring(@latlong, CHARINDEX(',',@latlong)+1, datalength(@latlong))

set @pointID = NEWID()
insert into drawmap_point(pointID, objID, pointindex, lat, long)
values(@pointID, @objID, @i,@lat, @long)

SELECT @coords = SUBSTRING(@coords,@index+3,DATALENGTH(@coords))
END

return
end

merge process was unable to create a new generation at the subscriber

the merge process was unable to create a new generation at the subscriber


Spent the day with MS trying to fix a similar problem. Found that they have a bug with no fix yet it is caused by generation leveling throwing sp_MSmakegeneration into a loop. The work around that I was given is to turn off generation leveling.update sysmergepublications set [generation_leveling_threshold] = 0

Load/Post Images to database with C#

protected void LoadImage()
{
try
{
this.openFileDialog1.ShowDialog(this);
string strFn=this.openFileDialog1.FileName;
this.pictureBox1.Image=Image.FromFile(strFn);
FileInfo fiImage=new FileInfo(strFn);
this.m_lImageFileLength=fiImage.Length;
FileStream fs=new FileStream(strFn,FileMode.Open,
FileAccess.Read,FileShare.Read);
m_barrImg=new byte[Convert.ToInt32(this.m_lImageFileLength)];
int iBytesRead = fs.Read(m_barrImg,0,
Convert.ToInt32(this.m_lImageFileLength));
fs.Close();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}


private void btnSave_Click(object sender, System.EventArgs e)
{
try
{
this.sqlConnection1.Open();
if (sqlCommand1.Parameters.Count ==0 )
{
this.sqlCommand1.CommandText="INSERT INTO tblImgData(ID," +
" Name,Picture) values(@ID,@Name,@Picture)";
this.sqlCommand1.Parameters.Add("@ID",
System.Data.SqlDbType.Int,4);
this.sqlCommand1.Parameters.Add("@Name",
System.Data.SqlDbType.VarChar,50);
this.sqlCommand1.Parameters.Add("@Picture",
System.Data.SqlDbType.Image);
}
this.sqlCommand1.Parameters["@ID"].Value=this.editID.Text;
this.sqlCommand1.Parameters["@Name"].Value=this.editName.Text;
this.sqlCommand1.Parameters["@Picture"].Value=this.m_barrImg;
int iresult=this.sqlCommand1.ExecuteNonQuery();
MessageBox.Show(Convert.ToString(iresult));
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
this.sqlConnection1.Close();
}
}


private void btnLoad_Click(object sender, System.EventArgs e)
{
try
{
SqlCommand cmdSelect=new SqlCommand("select Picture" +
" from tblImgData where ID=@ID",this.sqlConnection1);
cmdSelect.Parameters.Add("@ID",SqlDbType.Int,4);
cmdSelect.Parameters["@ID"].Value=this.editID.Text;
this.sqlConnection1.Open();
byte[] barrImg=(byte[])cmdSelect.ExecuteScalar();
string strfn=Convert.ToString(DateTime.Now.ToFileTime());
FileStream fs=new FileStream(strfn,
FileMode.CreateNew, FileAccess.Write);
fs.Write(barrImg,0,barrImg.Length);
fs.Flush();
fs.Close();
pictureBox1.Image=Image.FromFile(strfn);
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
this.sqlConnection1.Close();
}
}