Im having trouble putting all of this together, but here is the
scenario
ASP page with a Query String Variable,
Example: http://mywebsite/mywebpage.aspx?variable=x
now i need to query using openquery across a linked server using this
variable.
Like:
SELECT COLUMN1, COLUMN2
FROM OPENQUERY(LINKEDSERVER, '
SELECT COLUMN1, COLUMN2
FROM LINKEDSERVERTABLE
WHERE COLUMN3=@.VARIABLE')
I know this won't work, but this is the concept, if i run the query
against the linked server then my results take about 20 seconds to
return, but if i could pass the variable to an openquery then my
results would be returned in 1-2 seconds. which is acceptable. Thanks
for any help in advance!!You need to build the SQL statement as a string and
then pass that string to an EXEC. You can find more
information and an example in the following article:
HOW TO: Pass a Variable to a Linked Server Query
http://support.microsoft.com/?id=314520
-Sue
On 3 Aug 2006 16:07:15 -0700, joshd@.norrisinc.com wrote:
>Im having trouble putting all of this together, but here is the
>scenario
>ASP page with a Query String Variable,
>Example: http://mywebsite/mywebpage.aspx?variable=x
>now i need to query using openquery across a linked server using this
>variable.
>Like:
> SELECT COLUMN1, COLUMN2
> FROM OPENQUERY(LINKEDSERVER, '
> SELECT COLUMN1, COLUMN2
> FROM LINKEDSERVERTABLE
> WHERE COLUMN3=@.VARIABLE')
>I know this won't work, but this is the concept, if i run the query
>against the linked server then my results take about 20 seconds to
>return, but if i could pass the variable to an openquery then my
>results would be returned in 1-2 seconds. which is acceptable. Thanks
>for any help in advance!!
Showing posts with label trouble. Show all posts
Showing posts with label trouble. Show all posts
Thursday, March 22, 2012
Tuesday, March 20, 2012
ASP 1.1 & Report Server Login Problems
Hello everyone
we're having some strage trouble concerning ASP 1.1 and reporting services. We use a ASP 1.1 solution in which we authenticate Users using a self-written Login Mask. One page of the project consists of a report which is provided by report server. now, after displaying this report once, all other ASP 1.1 Forms do not work anymore, they simply keep empty.
My guess is that while conecting to the report server, an athenticated connection is established and kept up, even if the report is closed again.
thanks for any help
exmap
exmap wrote:
One page of the project consists of a report which is provided by report server. now, after displaying this report once, all other ASP 1.1 Forms do not work anymore, they simply keep empty.
I'd have to see it to believe it. Can you post a screenshot of what you are seeing? If not, then can you describe the situation a bit further. The problem description appears to be much too general.
sqlASP / SQLServer connection
Hi, I'm having trouble connecting to SQLServer in my ASP program.
here's the code
<%
dim objConnection, ObjRecorSet, strConnection, strOut, objCmd, objParameters
Set objConnection = Server.CreateObject ("ADODB.Connection")
strConnection = "DSN=SQL_Tracker;Database=Tracker;UID=sa;PWD=;"
objConnection.Open strConnection
...
%
I'm getting this error on the line that opens the connection.
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
'HOME\IUSR_HOME'.
/Tracker/list_team.asp, line 11
Notes:
I've set up the DSN called 'SQL_Tracker' in my ODBC settings.
When I use the Test Connection button it's successful.
The SQL Server is running on my own computer, and so I have
full DBA rights on it
any ideas what's wrong?Your connection string uses SQL authentication but the error message looks
like Windows authentication was attempted. Did you specify SQL
authentication or Windows authentication in your ODBC setup? Is your SQL
Server configured to allow both SQL and Windows authentication?
Also, note that you should never use 'sa' for application database
connections and you may get uninvited guests with a blank 'sa' password.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter Morris" <nospam.ple@.se> wrote in message
news:RmS9d.5432$xb.1658@.text.news.blueyonder.co.uk ...
> Hi, I'm having trouble connecting to SQLServer in my ASP program.
> here's the code
> <%
> dim objConnection, ObjRecorSet, strConnection, strOut, objCmd,
> objParameters
> Set objConnection = Server.CreateObject ("ADODB.Connection")
> strConnection = "DSN=SQL_Tracker;Database=Tracker;UID=sa;PWD=;"
> objConnection.Open strConnection
> ...
> %>
> I'm getting this error on the line that opens the connection.
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
> 'HOME\IUSR_HOME'.
> /Tracker/list_team.asp, line 11
>
> Notes:
> I've set up the DSN called 'SQL_Tracker' in my ODBC settings.
> When I use the Test Connection button it's successful.
> The SQL Server is running on my own computer, and so I have
> full DBA rights on it
> any ideas what's wrong?|||Stop using ODBC/DSN. This may not resolve the issue, but at least your
application will be faster & the database connection will be supported
in the future.
Please read the following pages.
http://www.aspfaq.com/show.asp?id=2126
http://msdn.microsoft.com/library/d..._components.asp
Peter Morris wrote:
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
> 'HOME\IUSR_HOME'.
> /Tracker/list_team.asp, line 11
--
Please do not contact me directly or ask me to contact you directly for
assistance.
If your question is worth asking, it's worth posting.
If its not worth posting you should have done a search on
http://www.google.com/ http://www.google.com/grphp?hl=en&tab=wg&q= or
http://news.google.com/froogle?hl=en&tab=nf&ned=us&q= before wasting our
time.sql
Monday, March 19, 2012
Ascending Date Column Statistics "dbcc traceon( 2389 )"
I am having trouble with query performance on SQL Server 2005. The database
is a basic book ordering application. A simple one to many Orders to Order
Items. The orders table contains 80,000 rows and the Order Items table
contains 280,000 rows. The problem query contains an inner join with Orders
and Order Items with where clause on Order Date. The problem is when you
execute the query on old data the inner join is based on a hash (1 second
execution). When you execute the query on new data the inner join is based
on a nested loop (2 minute execution). The reason for this is the stats.
The stats for the order date column do not contain the new data so the
estimated rows for the given date range is 1 (when the query actually returns
1320 rows). If I update the stats for order date the query will perform as
expected (hash join). The following blog explains the issue.
http://blogs.msdn.com/ianjo/archive/2006/04/24/582227.aspx
In order to fix this issue it appears I have to schedule a manual update of
the stats or use dbcc traceon( 2389 ). This application ran on SQL server
2000 for 4 years with no problems. Why did I not see an issue with SQL
server 2000? Does anyone have any better ideas except manually updating the
stats?First of all, have you tried a HASH JOIN hint? If the hash is working well,
force it.
I have also had a lot of statistics problems in some of the applications
I've been working on, and although I haven't had a chance to try these trace
flags yet, I have found workarounds to every issue (generally, involving
query hints and/or plan forcing). I agree, in some cases SQL Server 2005 is
not doing as well as SQL Server 2000 did, but overall I think performance is
improved in most scenarios. Hopefully as MS continues to evaluate use cases
these issues will go away and we can go back to thinking about other things!
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"achopp" <achopp@.discussions.microsoft.com> wrote in message
news:8E35C26D-A3F0-41BB-AE4B-303190B82483@.microsoft.com...
>I am having trouble with query performance on SQL Server 2005. The
>database
> is a basic book ordering application. A simple one to many Orders to
> Order
> Items. The orders table contains 80,000 rows and the Order Items table
> contains 280,000 rows. The problem query contains an inner join with
> Orders
> and Order Items with where clause on Order Date. The problem is when you
> execute the query on old data the inner join is based on a hash (1 second
> execution). When you execute the query on new data the inner join is
> based
> on a nested loop (2 minute execution). The reason for this is the stats.
> The stats for the order date column do not contain the new data so the
> estimated rows for the given date range is 1 (when the query actually
> returns
> 1320 rows). If I update the stats for order date the query will perform
> as
> expected (hash join). The following blog explains the issue.
> http://blogs.msdn.com/ianjo/archive/2006/04/24/582227.aspx
> In order to fix this issue it appears I have to schedule a manual update
> of
> the stats or use dbcc traceon( 2389 ). This application ran on SQL server
> 2000 for 4 years with no problems. Why did I not see an issue with SQL
> server 2000? Does anyone have any better ideas except manually updating
> the
> stats?
>|||Thanks for the help. The HASH Join hint does work.
"Adam Machanic" wrote:
> First of all, have you tried a HASH JOIN hint? If the hash is working well,
> force it.
> I have also had a lot of statistics problems in some of the applications
> I've been working on, and although I haven't had a chance to try these trace
> flags yet, I have found workarounds to every issue (generally, involving
> query hints and/or plan forcing). I agree, in some cases SQL Server 2005 is
> not doing as well as SQL Server 2000 did, but overall I think performance is
> improved in most scenarios. Hopefully as MS continues to evaluate use cases
> these issues will go away and we can go back to thinking about other things!
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "achopp" <achopp@.discussions.microsoft.com> wrote in message
> news:8E35C26D-A3F0-41BB-AE4B-303190B82483@.microsoft.com...
> >I am having trouble with query performance on SQL Server 2005. The
> >database
> > is a basic book ordering application. A simple one to many Orders to
> > Order
> > Items. The orders table contains 80,000 rows and the Order Items table
> > contains 280,000 rows. The problem query contains an inner join with
> > Orders
> > and Order Items with where clause on Order Date. The problem is when you
> > execute the query on old data the inner join is based on a hash (1 second
> > execution). When you execute the query on new data the inner join is
> > based
> > on a nested loop (2 minute execution). The reason for this is the stats.
> > The stats for the order date column do not contain the new data so the
> > estimated rows for the given date range is 1 (when the query actually
> > returns
> > 1320 rows). If I update the stats for order date the query will perform
> > as
> > expected (hash join). The following blog explains the issue.
> >
> > http://blogs.msdn.com/ianjo/archive/2006/04/24/582227.aspx
> >
> > In order to fix this issue it appears I have to schedule a manual update
> > of
> > the stats or use dbcc traceon( 2389 ). This application ran on SQL server
> > 2000 for 4 years with no problems. Why did I not see an issue with SQL
> > server 2000? Does anyone have any better ideas except manually updating
> > the
> > stats?
> >
>
>|||Or possibly the date_correlation_optimization_option database option might help. See also
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/275fb44b-417a-426a-bf7c-f23308ed72c0.htm.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"achopp" <achopp@.discussions.microsoft.com> wrote in message
news:609F8143-0EB4-4400-B84A-F3D75FBDDE95@.microsoft.com...
> Thanks for the help. The HASH Join hint does work.
> "Adam Machanic" wrote:
>> First of all, have you tried a HASH JOIN hint? If the hash is working well,
>> force it.
>> I have also had a lot of statistics problems in some of the applications
>> I've been working on, and although I haven't had a chance to try these trace
>> flags yet, I have found workarounds to every issue (generally, involving
>> query hints and/or plan forcing). I agree, in some cases SQL Server 2005 is
>> not doing as well as SQL Server 2000 did, but overall I think performance is
>> improved in most scenarios. Hopefully as MS continues to evaluate use cases
>> these issues will go away and we can go back to thinking about other things!
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>> "achopp" <achopp@.discussions.microsoft.com> wrote in message
>> news:8E35C26D-A3F0-41BB-AE4B-303190B82483@.microsoft.com...
>> >I am having trouble with query performance on SQL Server 2005. The
>> >database
>> > is a basic book ordering application. A simple one to many Orders to
>> > Order
>> > Items. The orders table contains 80,000 rows and the Order Items table
>> > contains 280,000 rows. The problem query contains an inner join with
>> > Orders
>> > and Order Items with where clause on Order Date. The problem is when you
>> > execute the query on old data the inner join is based on a hash (1 second
>> > execution). When you execute the query on new data the inner join is
>> > based
>> > on a nested loop (2 minute execution). The reason for this is the stats.
>> > The stats for the order date column do not contain the new data so the
>> > estimated rows for the given date range is 1 (when the query actually
>> > returns
>> > 1320 rows). If I update the stats for order date the query will perform
>> > as
>> > expected (hash join). The following blog explains the issue.
>> >
>> > http://blogs.msdn.com/ianjo/archive/2006/04/24/582227.aspx
>> >
>> > In order to fix this issue it appears I have to schedule a manual update
>> > of
>> > the stats or use dbcc traceon( 2389 ). This application ran on SQL server
>> > 2000 for 4 years with no problems. Why did I not see an issue with SQL
>> > server 2000? Does anyone have any better ideas except manually updating
>> > the
>> > stats?
>> >
>>
is a basic book ordering application. A simple one to many Orders to Order
Items. The orders table contains 80,000 rows and the Order Items table
contains 280,000 rows. The problem query contains an inner join with Orders
and Order Items with where clause on Order Date. The problem is when you
execute the query on old data the inner join is based on a hash (1 second
execution). When you execute the query on new data the inner join is based
on a nested loop (2 minute execution). The reason for this is the stats.
The stats for the order date column do not contain the new data so the
estimated rows for the given date range is 1 (when the query actually returns
1320 rows). If I update the stats for order date the query will perform as
expected (hash join). The following blog explains the issue.
http://blogs.msdn.com/ianjo/archive/2006/04/24/582227.aspx
In order to fix this issue it appears I have to schedule a manual update of
the stats or use dbcc traceon( 2389 ). This application ran on SQL server
2000 for 4 years with no problems. Why did I not see an issue with SQL
server 2000? Does anyone have any better ideas except manually updating the
stats?First of all, have you tried a HASH JOIN hint? If the hash is working well,
force it.
I have also had a lot of statistics problems in some of the applications
I've been working on, and although I haven't had a chance to try these trace
flags yet, I have found workarounds to every issue (generally, involving
query hints and/or plan forcing). I agree, in some cases SQL Server 2005 is
not doing as well as SQL Server 2000 did, but overall I think performance is
improved in most scenarios. Hopefully as MS continues to evaluate use cases
these issues will go away and we can go back to thinking about other things!
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"achopp" <achopp@.discussions.microsoft.com> wrote in message
news:8E35C26D-A3F0-41BB-AE4B-303190B82483@.microsoft.com...
>I am having trouble with query performance on SQL Server 2005. The
>database
> is a basic book ordering application. A simple one to many Orders to
> Order
> Items. The orders table contains 80,000 rows and the Order Items table
> contains 280,000 rows. The problem query contains an inner join with
> Orders
> and Order Items with where clause on Order Date. The problem is when you
> execute the query on old data the inner join is based on a hash (1 second
> execution). When you execute the query on new data the inner join is
> based
> on a nested loop (2 minute execution). The reason for this is the stats.
> The stats for the order date column do not contain the new data so the
> estimated rows for the given date range is 1 (when the query actually
> returns
> 1320 rows). If I update the stats for order date the query will perform
> as
> expected (hash join). The following blog explains the issue.
> http://blogs.msdn.com/ianjo/archive/2006/04/24/582227.aspx
> In order to fix this issue it appears I have to schedule a manual update
> of
> the stats or use dbcc traceon( 2389 ). This application ran on SQL server
> 2000 for 4 years with no problems. Why did I not see an issue with SQL
> server 2000? Does anyone have any better ideas except manually updating
> the
> stats?
>|||Thanks for the help. The HASH Join hint does work.
"Adam Machanic" wrote:
> First of all, have you tried a HASH JOIN hint? If the hash is working well,
> force it.
> I have also had a lot of statistics problems in some of the applications
> I've been working on, and although I haven't had a chance to try these trace
> flags yet, I have found workarounds to every issue (generally, involving
> query hints and/or plan forcing). I agree, in some cases SQL Server 2005 is
> not doing as well as SQL Server 2000 did, but overall I think performance is
> improved in most scenarios. Hopefully as MS continues to evaluate use cases
> these issues will go away and we can go back to thinking about other things!
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "achopp" <achopp@.discussions.microsoft.com> wrote in message
> news:8E35C26D-A3F0-41BB-AE4B-303190B82483@.microsoft.com...
> >I am having trouble with query performance on SQL Server 2005. The
> >database
> > is a basic book ordering application. A simple one to many Orders to
> > Order
> > Items. The orders table contains 80,000 rows and the Order Items table
> > contains 280,000 rows. The problem query contains an inner join with
> > Orders
> > and Order Items with where clause on Order Date. The problem is when you
> > execute the query on old data the inner join is based on a hash (1 second
> > execution). When you execute the query on new data the inner join is
> > based
> > on a nested loop (2 minute execution). The reason for this is the stats.
> > The stats for the order date column do not contain the new data so the
> > estimated rows for the given date range is 1 (when the query actually
> > returns
> > 1320 rows). If I update the stats for order date the query will perform
> > as
> > expected (hash join). The following blog explains the issue.
> >
> > http://blogs.msdn.com/ianjo/archive/2006/04/24/582227.aspx
> >
> > In order to fix this issue it appears I have to schedule a manual update
> > of
> > the stats or use dbcc traceon( 2389 ). This application ran on SQL server
> > 2000 for 4 years with no problems. Why did I not see an issue with SQL
> > server 2000? Does anyone have any better ideas except manually updating
> > the
> > stats?
> >
>
>|||Or possibly the date_correlation_optimization_option database option might help. See also
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/275fb44b-417a-426a-bf7c-f23308ed72c0.htm.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"achopp" <achopp@.discussions.microsoft.com> wrote in message
news:609F8143-0EB4-4400-B84A-F3D75FBDDE95@.microsoft.com...
> Thanks for the help. The HASH Join hint does work.
> "Adam Machanic" wrote:
>> First of all, have you tried a HASH JOIN hint? If the hash is working well,
>> force it.
>> I have also had a lot of statistics problems in some of the applications
>> I've been working on, and although I haven't had a chance to try these trace
>> flags yet, I have found workarounds to every issue (generally, involving
>> query hints and/or plan forcing). I agree, in some cases SQL Server 2005 is
>> not doing as well as SQL Server 2000 did, but overall I think performance is
>> improved in most scenarios. Hopefully as MS continues to evaluate use cases
>> these issues will go away and we can go back to thinking about other things!
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>> "achopp" <achopp@.discussions.microsoft.com> wrote in message
>> news:8E35C26D-A3F0-41BB-AE4B-303190B82483@.microsoft.com...
>> >I am having trouble with query performance on SQL Server 2005. The
>> >database
>> > is a basic book ordering application. A simple one to many Orders to
>> > Order
>> > Items. The orders table contains 80,000 rows and the Order Items table
>> > contains 280,000 rows. The problem query contains an inner join with
>> > Orders
>> > and Order Items with where clause on Order Date. The problem is when you
>> > execute the query on old data the inner join is based on a hash (1 second
>> > execution). When you execute the query on new data the inner join is
>> > based
>> > on a nested loop (2 minute execution). The reason for this is the stats.
>> > The stats for the order date column do not contain the new data so the
>> > estimated rows for the given date range is 1 (when the query actually
>> > returns
>> > 1320 rows). If I update the stats for order date the query will perform
>> > as
>> > expected (hash join). The following blog explains the issue.
>> >
>> > http://blogs.msdn.com/ianjo/archive/2006/04/24/582227.aspx
>> >
>> > In order to fix this issue it appears I have to schedule a manual update
>> > of
>> > the stats or use dbcc traceon( 2389 ). This application ran on SQL server
>> > 2000 for 4 years with no problems. Why did I not see an issue with SQL
>> > server 2000? Does anyone have any better ideas except manually updating
>> > the
>> > stats?
>> >
>>
Friday, February 24, 2012
Article Defaults > can't change
Hello,
We're running transactional replication with no trouble...except we can't
seem to change the article defaults. We are running SQL Server 2000, SP3,
Standard edition.
We don't want data deleted on the subscribers when data is deleted on the
publisher. I follow directions in chapter 4 of Hilary Cotter's book...type
the string 'NONE' in the 'Replace DELETE...' box.
I dropped the subscription before doing this. After saving Default Articles
Property, I exit it, go back in, and the 'NONE' string is gone.
I do this with table articles and indexed view articles...same results. I
try to change a property on the 'Snapshot' tab, same thing. The change
doesn't stick.
Any ideas?
Thanks...
Mojo Jojo
If the None is gone it means that nothing will be replicated. This is an
inconsistency in the GUI/Wizard.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mojo" <Mojo@.discussions.microsoft.com> wrote in message
news:F2F8C8CE-AA36-4957-BBA0-0B29F5CE6CEB@.microsoft.com...
> Hello,
> We're running transactional replication with no trouble...except we can't
> seem to change the article defaults. We are running SQL Server 2000, SP3,
> Standard edition.
> We don't want data deleted on the subscribers when data is deleted on the
> publisher. I follow directions in chapter 4 of Hilary Cotter's book...type
> the string 'NONE' in the 'Replace DELETE...' box.
> I dropped the subscription before doing this. After saving Default
Articles
> Property, I exit it, go back in, and the 'NONE' string is gone.
> I do this with table articles and indexed view articles...same results. I
> try to change a property on the 'Snapshot' tab, same thing. The change
> doesn't stick.
> Any ideas?
> Thanks...
> --
> Mojo Jojo
|||It doesn't seem to matter what I do in the GUI/Wizard...no changes I attempt
to make in article defaults are saved. The behavior of the database doesn't
change.
Mojo Jojo
"Hilary Cotter" wrote:
> If the None is gone it means that nothing will be replicated. This is an
> inconsistency in the GUI/Wizard.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Mojo" <Mojo@.discussions.microsoft.com> wrote in message
> news:F2F8C8CE-AA36-4957-BBA0-0B29F5CE6CEB@.microsoft.com...
> Articles
>
>
|||This is the default behavior. Once you have made this change you can't go
back, unless you use sp_changearticle.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mojo" <Mojo@.discussions.microsoft.com> wrote in message
news:73D45C24-6410-41E0-80FD-6345DE88DA6F@.microsoft.com...
> It doesn't seem to matter what I do in the GUI/Wizard...no changes I
attempt
> to make in article defaults are saved. The behavior of the database
doesn't[vbcol=seagreen]
> change.
> --
> Mojo Jojo
>
> "Hilary Cotter" wrote:
can't[vbcol=seagreen]
SP3,[vbcol=seagreen]
the[vbcol=seagreen]
book...type[vbcol=seagreen]
results. I[vbcol=seagreen]
We're running transactional replication with no trouble...except we can't
seem to change the article defaults. We are running SQL Server 2000, SP3,
Standard edition.
We don't want data deleted on the subscribers when data is deleted on the
publisher. I follow directions in chapter 4 of Hilary Cotter's book...type
the string 'NONE' in the 'Replace DELETE...' box.
I dropped the subscription before doing this. After saving Default Articles
Property, I exit it, go back in, and the 'NONE' string is gone.
I do this with table articles and indexed view articles...same results. I
try to change a property on the 'Snapshot' tab, same thing. The change
doesn't stick.
Any ideas?
Thanks...
Mojo Jojo
If the None is gone it means that nothing will be replicated. This is an
inconsistency in the GUI/Wizard.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mojo" <Mojo@.discussions.microsoft.com> wrote in message
news:F2F8C8CE-AA36-4957-BBA0-0B29F5CE6CEB@.microsoft.com...
> Hello,
> We're running transactional replication with no trouble...except we can't
> seem to change the article defaults. We are running SQL Server 2000, SP3,
> Standard edition.
> We don't want data deleted on the subscribers when data is deleted on the
> publisher. I follow directions in chapter 4 of Hilary Cotter's book...type
> the string 'NONE' in the 'Replace DELETE...' box.
> I dropped the subscription before doing this. After saving Default
Articles
> Property, I exit it, go back in, and the 'NONE' string is gone.
> I do this with table articles and indexed view articles...same results. I
> try to change a property on the 'Snapshot' tab, same thing. The change
> doesn't stick.
> Any ideas?
> Thanks...
> --
> Mojo Jojo
|||It doesn't seem to matter what I do in the GUI/Wizard...no changes I attempt
to make in article defaults are saved. The behavior of the database doesn't
change.
Mojo Jojo
"Hilary Cotter" wrote:
> If the None is gone it means that nothing will be replicated. This is an
> inconsistency in the GUI/Wizard.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Mojo" <Mojo@.discussions.microsoft.com> wrote in message
> news:F2F8C8CE-AA36-4957-BBA0-0B29F5CE6CEB@.microsoft.com...
> Articles
>
>
|||This is the default behavior. Once you have made this change you can't go
back, unless you use sp_changearticle.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mojo" <Mojo@.discussions.microsoft.com> wrote in message
news:73D45C24-6410-41E0-80FD-6345DE88DA6F@.microsoft.com...
> It doesn't seem to matter what I do in the GUI/Wizard...no changes I
attempt
> to make in article defaults are saved. The behavior of the database
doesn't[vbcol=seagreen]
> change.
> --
> Mojo Jojo
>
> "Hilary Cotter" wrote:
can't[vbcol=seagreen]
SP3,[vbcol=seagreen]
the[vbcol=seagreen]
book...type[vbcol=seagreen]
results. I[vbcol=seagreen]
Thursday, February 16, 2012
Arithmetic overflow error converting expression to data type bigint
I am attempting to setup a replication from SQL Server 2005 that will be read by SQL Server Compact Edition (beta). I'm having trouble getting the Publication Wizard to create the Publication. Sample table definition that I'm replicating:
USE dbPSMAssist_Development;
CREATE TABLE corporations (
id NUMERIC(19,0) IDENTITY(1964,1) NOT NULL PRIMARY KEY,
idWas NUMERIC(19,0) DEFAULT 0,
logIsActive BIT DEFAULT 1,
vchNmCorp VARCHAR(75) NOT NULL,
vchStrtAddr1 VARCHAR(60) NOT NULL,
vchNmCity VARCHAR(50) NOT NULL,
vchNmState VARCHAR(2) NOT NULL,
vchPostalCode VARCHAR(10) NOT NULL,
vchPhnPrimary VARCHAR(16) NOT NULL,
);
CREATE INDEX ix_corporations_nm ON corporations(vchNmCorp, id);
GO
When the wizard gets to the step where it is creating the publication, I get the following error message:
Arithmetic overflow error converting expression to data type bigint. Changed database context to 'dbPSMAssist_Development'. (Microsoft SQL Server, Error: 8115).
I can find no information on what this error is or why I am receiving the error. Any ideas on how to fix would be appreciated.
Thanks in advance ...
David L. Collison
Any day above ground is a good day.
We need more information - are you doing any filtering or joining of any articles that may cause this error?
You can also do a profile trace of the publisher when you click on the OK button to complete the wizard so you can see what stored proc and statement it's failing on. Let us know what you find.
|||Greg ...This has to be due to the size of my key on the file. I resized the field ID to NUMERIC(12,0) and now the replication wizard completes the setup properly.
I would have anticipated the replication engine to utilize the definition - obviously it doesn't like big numbers. Ok, I know it will take a long time to create that many records to worry about filling up the key at 12 digits let alone 19, but I like to plan ahead. ;-)
Have a good one!
David L. Collison
Any day above ground is a good day!
Subscribe to:
Posts (Atom)