Showing posts with label row. Show all posts
Showing posts with label row. Show all posts

Sunday, March 11, 2012

Database Row Size Limit

Hi, All,

I came cross a problem like this.

Cannot create a row of size which is greater than the allowable maximum of 8060

Is there any method to solve this ?

This is by design, determined by data page size 8KB. Do you really need such a large row? You can put large columns like VARCHAR(8000) into TEXT columns.

Saturday, February 25, 2012

Database Replication

We currently have multiple tables being replicated using merge snapshot. In
the majority of cases the whole table is required or a row filter is applied
based on a value in that table.
We have two sets of identical databases, except for their data, that
replicate their data from a backend database towards a frontend database.
The problem is that for database 1, all the necesarry data is replicated,
but for the other database 2, which has the same parameters, replication
settings etc.. certain blob files containing pdf files will not replicate
from the backend database towards the frontend database.
The replication only replicates the .doc files, so the replication is
partially done for the table that contains the .doc and .pdf files.
When I run the initial snapshot, the pdf files are replicated fine, but when
the agent runs, the pdf files are not replicated.
Has anyone encountered similar problems? Can someone identify what is
causing the problem by explaining why this is not working? My first idea
would be the sheduling the snapshot to run every night, instead of the
agent, but this would be a far from ideal solution and one that I would only
consider as a last choice. I fail to understand why the replication works
for database 1, but not for database 2.
parnold8104@.hotmail.com
can you run this command sp_configure 'max text repl size'
and then figure out what your maximum data size in the blob columns you are
replicating?
"Peter A" <parnold8104@.hotmail.com> wrote in message
news:%23sFpoRuJEHA.2456@.TK2MSFTNGP12.phx.gbl...
> We currently have multiple tables being replicated using merge snapshot.
In
> the majority of cases the whole table is required or a row filter is
applied
> based on a value in that table.
> We have two sets of identical databases, except for their data, that
> replicate their data from a backend database towards a frontend database.
> The problem is that for database 1, all the necesarry data is replicated,
> but for the other database 2, which has the same parameters, replication
> settings etc.. certain blob files containing pdf files will not replicate
> from the backend database towards the frontend database.
> The replication only replicates the .doc files, so the replication is
> partially done for the table that contains the .doc and .pdf files.
> When I run the initial snapshot, the pdf files are replicated fine, but
when
> the agent runs, the pdf files are not replicated.
> Has anyone encountered similar problems? Can someone identify what is
> causing the problem by explaining why this is not working? My first idea
> would be the sheduling the snapshot to run every night, instead of the
> agent, but this would be a far from ideal solution and one that I would
only
> consider as a last choice. I fail to understand why the replication works
> for database 1, but not for database 2.
> parnold8104@.hotmail.com
>
>
|||Hi Hilary,
thanks for replying, when I run the command sp_configure I get these values:
max text repl size (B) minimum: 0
maximum: 2147483647
config_value: 52428800
run_value: 52428800
I am not sure if the data size is the problem, because the .doc files that
are replicated are larger in size then the pdf files. Another thing I
noticed is that I can replicate my pdf records in the 'faulty' database, but
only if I do an update of the record in my backend database. So when the
record is 'new' the replication skips it, but if I update the record, by for
example changing a letter in a description field, the replication picks up
the record and pushes it towards the frontend database.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:eJKebhuJEHA.3592@.TK2MSFTNGP09.phx.gbl...
> can you run this command sp_configure 'max text repl size'
> and then figure out what your maximum data size in the blob columns you
are[vbcol=seagreen]
> replicating?
> "Peter A" <parnold8104@.hotmail.com> wrote in message
> news:%23sFpoRuJEHA.2456@.TK2MSFTNGP12.phx.gbl...
> In
> applied
database.[vbcol=seagreen]
replicated,[vbcol=seagreen]
replicate[vbcol=seagreen]
> when
> only
works
>
|||can you post your schema and publication scripts here?
What version of SQL are you using? SP?
"Peter A" <parnold8104@.hotmail.com> wrote in message
news:%23whCdr3JEHA.232@.TK2MSFTNGP12.phx.gbl...
> Hi Hilary,
> thanks for replying, when I run the command sp_configure I get these
values:
> max text repl size (B) minimum: 0
> maximum: 2147483647
> config_value: 52428800
> run_value: 52428800
> I am not sure if the data size is the problem, because the .doc files that
> are replicated are larger in size then the pdf files. Another thing I
> noticed is that I can replicate my pdf records in the 'faulty' database,
but
> only if I do an update of the record in my backend database. So when the
> record is 'new' the replication skips it, but if I update the record, by
for[vbcol=seagreen]
> example changing a letter in a description field, the replication picks up
> the record and pushes it towards the frontend database.
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:eJKebhuJEHA.3592@.TK2MSFTNGP09.phx.gbl...
> are
snapshot.[vbcol=seagreen]
> database.
> replicated,
replication[vbcol=seagreen]
> replicate
but[vbcol=seagreen]
idea[vbcol=seagreen]
would
> works
>

Sunday, February 19, 2012

Database problem

Hi,
I am trying to add a row to my database and although it does not give me an
error message, it is not adding it to my database. I am using C# and SQL
2005 Express editions.
When I look into my Database Explorer I do not see the row added. What are
the steps that have to be taken to create a database and then add rows to
it? Do I have to do anything in the Solution Explorer? I am using a Window
Console application as I do not need a form as I am trying to access the
database directly without a dataset. Here is my code below.
TIA
Roy
using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
namespace testing
{
class Class1
{
// private System.Data.DataSet dataSet;
[STAThread]
static void Main(string[] args)
{
int lnSOBN = 900;
int lnBN1 = 188;
string strConnection = @."Data
Source=. \SQLEXPRESS;AttachDbFilename=|DataDirect
ory|\Database1.mdf;Integrate
d
Security=True;Connect Timeout=30;User Instance=True";
SqlConnection conn = new SqlConnection(strConnection);
string strInsert = "INSERT INTO tblDatabase1 (SOBN, BN1) VALUES (@.par0,
@.par1) ";
SqlCommand cmd = new SqlCommand(strInsert, conn);
SqlParameter parameter1 = new SqlParameter("@.par0", SqlDbType.Int, 32);
SqlParameter parameter2 = new SqlParameter("@.par1", SqlDbType.Int, 32);
parameter1.Value = lnSOBN;
parameter2.Value = lnBN1;
cmd.Parameters.Add(parameter1);
cmd.Parameters.Add(parameter2);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
}
}
TIA
RoyHi Roy,

> I am trying to add a row to my database and although it does not give me
> an error message, it is not adding it to my database. I am using C# and
> SQL 2005 Express editions.
> When I look into my Database Explorer I do not see the row added.
After you insert the record, do you refresh the query that displays your
table results? From the Database Explorer, you locate the table in your
database, right click on it and select Show Table Data. When this opens up,
it shows a static view of the data, any records added to the table while
this view is open will not be reflected unless you re-run the underlying
query. You can do this by pressing the bold, red exclaimation mark in the
toolbar, or pressing CTRL+R.
Regards,
Jeff Papiez
jeff . papiez @. microsoft . com
SQL Server 2005 Upgrade Advisor Team
http://blogs.msdn.com/jpapiez
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.|||Hi Jeff,
I have tried that but it does not work? Do I have to do anything with the
Solution Explorer. It's pretty frustrating because I have tried so many
things and nothing seems to be working.
Any suggestions.
Thanks
Roy
"Jeff Papiez [MSFT]" <jpapiez@.online.microsoft.com> wrote in message
news:O9AeJ0T6FHA.1276@.TK2MSFTNGP09.phx.gbl...
> Hi Roy,
>
> After you insert the record, do you refresh the query that displays your
> table results? From the Database Explorer, you locate the table in your
> database, right click on it and select Show Table Data. When this opens
> up, it shows a static view of the data, any records added to the table
> while this view is open will not be reflected unless you re-run the
> underlying query. You can do this by pressing the bold, red exclaimation
> mark in the toolbar, or pressing CTRL+R.
>
> --
> Regards,
> Jeff Papiez
> jeff . papiez @. microsoft . com
> SQL Server 2005 Upgrade Advisor Team
> http://blogs.msdn.com/jpapiez
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm.
>|||Hi Roy,
No you shouldn't have to do anything with Solution Explorer, it should "just
work". If you write a query to select all results from your table, using the
same connection string, and write it out to the console, do you still see no
results? If no error is raised after executing the insert, the data is being
sent somewhere successfully.
I can feel your frustration... this has happened to me before where I was
expecting the data to show up in one database, but it was going to
another... oops :)
Jeff.
"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:xA3ef.5204$wc.488038@.wagner.videotron.net...
> Hi Jeff,
> I have tried that but it does not work? Do I have to do anything with the
> Solution Explorer. It's pretty frustrating because I have tried so many
> things and nothing seems to be working.
> Any suggestions.
> Thanks
> Roy
> "Jeff Papiez [MSFT]" <jpapiez@.online.microsoft.com> wrote in message
> news:O9AeJ0T6FHA.1276@.TK2MSFTNGP09.phx.gbl...
>|||Hi Jeff,
I just want to make sure of something in the strConnection string that I
have.
Data Source = .\SQLEXPRESS is this not the name of the server?
AttachDbFilename = |DataDirectory|Database1.mdf what directory or database
does this point to.
Can I use something like AttachDbFilename = C:\MyDB\Database1.mdf instead?
string strConnection = @."Data
Source=. \SQLEXPRESS;AttachDbFilename=|DataDirect
ory|\Database1.mdf;Integrate
d
Security=True;Connect Timeout=30;User Instance=True";
How do I write a query to the console? I don't know how?
Thanks
Roy
"Jeff Papiez [MSFT]" <jpapiez@.online.microsoft.com> wrote in message
news:urY$3KU6FHA.2036@.TK2MSFTNGP14.phx.gbl...
> Hi Roy,
> No you shouldn't have to do anything with Solution Explorer, it should
> "just work". If you write a query to select all results from your table,
> using the same connection string, and write it out to the console, do you
> still see no results? If no error is raised after executing the insert,
> the data is being sent somewhere successfully.
> I can feel your frustration... this has happened to me before where I was
> expecting the data to show up in one database, but it was going to
> another... oops :)
> Jeff.
> "Roy Gourgi" <royng@.videotron.ca> wrote in message
> news:xA3ef.5204$wc.488038@.wagner.videotron.net...
>|||Hi Jeff,
I just noticed something. You might be right about it being written
somewhere else. When I go look into the directory where I have my
Database1.mdf I also see a sub-directory called bin\Debug where there is
another copy of my Database1.mdf which seems to change the date modified
when I run my program. On the other hand the other one does not change time.
How do I open or see the one in the bin\Debug directory?
Roy
"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:ZO2ef.5200$wc.484253@.wagner.videotron.net...
> Hi,
> I am trying to add a row to my database and although it does not give me
> an error message, it is not adding it to my database. I am using C# and
> SQL 2005 Express editions.
> When I look into my Database Explorer I do not see the row added. What are
> the steps that have to be taken to create a database and then add rows to
> it? Do I have to do anything in the Solution Explorer? I am using a Window
> Console application as I do not need a form as I am trying to access the
> database directly without a dataset. Here is my code below.
> TIA
> Roy
> using System;
> using System.Data;
> using System.Data.Common;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using System.Text;
>
> namespace testing
> {
> class Class1
> {
> // private System.Data.DataSet dataSet;
> [STAThread]
>
> static void Main(string[] args)
> {
> int lnSOBN = 900;
> int lnBN1 = 188;
>
> string strConnection = @."Data
> Source=. \SQLEXPRESS;AttachDbFilename=|DataDirect
ory|\Database1.mdf;Integra
ted
> Security=True;Connect Timeout=30;User Instance=True";
> SqlConnection conn = new SqlConnection(strConnection);
> string strInsert = "INSERT INTO tblDatabase1 (SOBN, BN1) VALUES (@.par0,
> @.par1) ";
> SqlCommand cmd = new SqlCommand(strInsert, conn);
> SqlParameter parameter1 = new SqlParameter("@.par0", SqlDbType.Int, 32);
> SqlParameter parameter2 = new SqlParameter("@.par1", SqlDbType.Int, 32);
> parameter1.Value = lnSOBN;
> parameter2.Value = lnBN1;
> cmd.Parameters.Add(parameter1);
> cmd.Parameters.Add(parameter2);
> cmd.Connection.Open();
> cmd.ExecuteNonQuery();
> cmd.Connection.Close();
> }
> }
> }
> TIA
> Roy
>|||Finally it worked.:) I just changed my connection on the Database Explorer
to the other location on bin\Debug and it wrote the row. Funny though when I
run it a couple of times it just rewrites over the same record over and over
again without append any other records. So I only have 1 record at a time.
BTW why is it that VS creates 2 copies of my database?
Thanks
Roy
"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:ZO2ef.5200$wc.484253@.wagner.videotron.net...
> Hi,
> I am trying to add a row to my database and although it does not give me
> an error message, it is not adding it to my database. I am using C# and
> SQL 2005 Express editions.
> When I look into my Database Explorer I do not see the row added. What are
> the steps that have to be taken to create a database and then add rows to
> it? Do I have to do anything in the Solution Explorer? I am using a Window
> Console application as I do not need a form as I am trying to access the
> database directly without a dataset. Here is my code below.
> TIA
> Roy
> using System;
> using System.Data;
> using System.Data.Common;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using System.Text;
>
> namespace testing
> {
> class Class1
> {
> // private System.Data.DataSet dataSet;
> [STAThread]
>
> static void Main(string[] args)
> {
> int lnSOBN = 900;
> int lnBN1 = 188;
>
> string strConnection = @."Data
> Source=. \SQLEXPRESS;AttachDbFilename=|DataDirect
ory|\Database1.mdf;Integra
ted
> Security=True;Connect Timeout=30;User Instance=True";
> SqlConnection conn = new SqlConnection(strConnection);
> string strInsert = "INSERT INTO tblDatabase1 (SOBN, BN1) VALUES (@.par0,
> @.par1) ";
> SqlCommand cmd = new SqlCommand(strInsert, conn);
> SqlParameter parameter1 = new SqlParameter("@.par0", SqlDbType.Int, 32);
> SqlParameter parameter2 = new SqlParameter("@.par1", SqlDbType.Int, 32);
> parameter1.Value = lnSOBN;
> parameter2.Value = lnBN1;
> cmd.Parameters.Add(parameter1);
> cmd.Parameters.Add(parameter2);
> cmd.Connection.Open();
> cmd.ExecuteNonQuery();
> cmd.Connection.Close();
> }
> }
> }
> TIA
> Roy
>|||Hi Jeff,
Resolved everything as I created a new solution and database and everything
now is ok. Somehow, it must have created a 2nd database and that is probably
why I had all the problems.
Thanks again
Roy
"Jeff Papiez [MSFT]" <jpapiez@.online.microsoft.com> wrote in message
news:urY$3KU6FHA.2036@.TK2MSFTNGP14.phx.gbl...
> Hi Roy,
> No you shouldn't have to do anything with Solution Explorer, it should
> "just work". If you write a query to select all results from your table,
> using the same connection string, and write it out to the console, do you
> still see no results? If no error is raised after executing the insert,
> the data is being sent somewhere successfully.
> I can feel your frustration... this has happened to me before where I was
> expecting the data to show up in one database, but it was going to
> another... oops :)
> Jeff.
> "Roy Gourgi" <royng@.videotron.ca> wrote in message
> news:xA3ef.5204$wc.488038@.wagner.videotron.net...
>|||Hi Roy,

> Finally it worked.:) I just changed my connection on the Database Explorer
> to the other location on bin\Debug and it wrote the row. Funny though when
> I run it a couple of times it just rewrites over the same record over and
> over again without append any other records. So I only have 1 record at a
> time.
> BTW why is it that VS creates 2 copies of my database?
I'm glad you've gotten it working, finally :)
With SQL Express we support this new feature where you can automatically
attach a database, as noted in the connection string you are using. When you
add a database to a project (Project -> Add New Item -> SQL Database) the
default option is to copy the database on every build to the output
directory, in your case the bin\debug directory. In addition, a new database
connection is added to the Database Explorer which points to the database in
your project directory. Anything you add to the database through the Data
Explorer will be added to the database file in your project folder. When you
build your project, a copy of the database overwrites the database files in
your build directory (bin\debug).
Are you yet? I am! :)
If this isn't the behavior you're after there are a few things you can do.
1. Add a new data connection. In the Database file name (new or existing)
text box, give the path to the new file as a path on your filesystem that's
outside the project, something like "C:\MSSQL\TestDB". You'll be prompted to
create the new database file. After it's done you can copy the connection
string from the properties grid for the new connection.
2. Create a new database on the server using sqlcmd. Assuming your Express
instance is SQLEXPRESS:
Start->Run: cmd.exe
In the console type:
sqlcmd -E -S.\SQLEXPRESS
1> create database TESTDB
2> go
The connection string to this database will look like the following:
Data Source=.\SQLEXPRESS;Initial Catalog=TESTDB;Integrated
Security=True;User Instance=True
3. Select the database in the Solution Explorer, press F4 to bring up the
Properties window.
Set the "Copy to Output Directory" = Do not copy
Then you can change your connection back to the path of your project
folder. Now, all changes you make to your database will be in the same file.
Regards,
Jeff Papiez
jeff . papiez @. microsoft . com
SQL Server 2005 Upgrade Advisor Team
http://blogs.msdn.com/jpapiez
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.|||Thanks Jeff,
I am a little bit , but there must be a good reason for it to make a
copy which I presently do not fully understand as I am a newby.
Roy
"Jeff Papiez [MSFT]" <jpapiez@.online.microsoft.com> wrote in message
news:uBoKBXV6FHA.252@.TK2MSFTNGP15.phx.gbl...
> Hi Roy,
>
> I'm glad you've gotten it working, finally :)
> With SQL Express we support this new feature where you can automatically
> attach a database, as noted in the connection string you are using. When
> you add a database to a project (Project -> Add New Item -> SQL Database)
> the default option is to copy the database on every build to the output
> directory, in your case the bin\debug directory. In addition, a new
> database connection is added to the Database Explorer which points to the
> database in your project directory. Anything you add to the database
> through the Data Explorer will be added to the database file in your
> project folder. When you build your project, a copy of the database
> overwrites the database files in your build directory (bin\debug).
> Are you yet? I am! :)
> If this isn't the behavior you're after there are a few things you can do.
> 1. Add a new data connection. In the Database file name (new or existing)
> text box, give the path to the new file as a path on your filesystem
> that's outside the project, something like "C:\MSSQL\TestDB". You'll be
> prompted to create the new database file. After it's done you can copy the
> connection string from the properties grid for the new connection.
> 2. Create a new database on the server using sqlcmd. Assuming your Express
> instance is SQLEXPRESS:
> Start->Run: cmd.exe
> In the console type:
> sqlcmd -E -S.\SQLEXPRESS
> 1> create database TESTDB
> 2> go
> The connection string to this database will look like the following:
> Data Source=.\SQLEXPRESS;Initial Catalog=TESTDB;Integrated
> Security=True;User Instance=True
> 3. Select the database in the Solution Explorer, press F4 to bring up the
> Properties window.
> Set the "Copy to Output Directory" = Do not copy
> Then you can change your connection back to the path of your project
> folder. Now, all changes you make to your database will be in the same
> file.
>
> --
> Regards,
> Jeff Papiez
> jeff . papiez @. microsoft . com
> SQL Server 2005 Upgrade Advisor Team
> http://blogs.msdn.com/jpapiez
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm.
>