I have been struggling for several days trying to setup bidirectional,
transactional replication by Publishing the same table on each server with
NOT FOR REPLICATION and an even/odd partitioning of the IDENTITY columns:
CREATE TABLE test (
col1 INTEGER IDENTITY( 1, 2 ) NOT FOR REPLICATION NOT NULL PRIMARY
KEY,
col2 CHAR(10) );
CREATE TABLE test (
col1 INTEGER IDENTITY( 2, 2 ) NOT FOR REPLICATION NOT NULL PRIMARY
KEY,
col2 CHAR(10) );
I used the Articles/Snapshot Keep the existing table unchanged option and
choose not perform to perform a snapshot automatically to preserve the
schema. Unidirectional replication from the first server to the second
worked perfectly.
Most recently, when using SQL commands instead of Stored Procedures and with
the "Use column names in commands that are not replaced by stored
procedures", I encountered:
Violation of PRIMARY KEY constraint 'PK__test__3D5E1FD2'. Cannot insert
duplicate key in object 'test'.
IDENTITY column values seem to be generated properly on each server, but
when I setup the second subscription I encountered the error. Apparently
when I inserted a row in the second server, it propagated to the first
server which may have tried to send it back to the second server?
I am not interested in new GUIID columns being introduced into our schema,
nor can we tolerate Two Phase Commit as the connection to the servers must
be asynchronous. Primary keys will never change. Inserted rows at each
site will get their own even or odd key values.
Is MS SQL Server up to the task or should I use our own trigger-based,
asynchronous replication solution?
Thanks, Matt
================================================== ========================
Matthew J. Ramuta Enterprise Information Solutions, Inc.
Txt: 6306973359@.mobile.att.net 4910 Main Street, Downers Grove, IL 60515
Off: 630-512-0570 Fax: 630-512-0568 Cell: 630-697-3359
================================================== ========================
Yes it is entirely possible.
Are you doing this through the wizards, because the wizards don't support
bi-directional transactional replication.
What you should do is script out what you have and then edit the script and
change the sp_addsubscription proc to also have a parameter saying
@.loopback_detection='true'
Do this for both sides.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Matthew J. Ramuta" <mattr@.eisolution.com> wrote in message
news:quqUc.3719$Y94.920@.newssvr33.news.prodigy.com ...
> I have been struggling for several days trying to setup bidirectional,
> transactional replication by Publishing the same table on each server with
> NOT FOR REPLICATION and an even/odd partitioning of the IDENTITY columns:
> CREATE TABLE test (
> col1 INTEGER IDENTITY( 1, 2 ) NOT FOR REPLICATION NOT NULL PRIMARY
> KEY,
> col2 CHAR(10) );
> CREATE TABLE test (
> col1 INTEGER IDENTITY( 2, 2 ) NOT FOR REPLICATION NOT NULL PRIMARY
> KEY,
> col2 CHAR(10) );
> I used the Articles/Snapshot Keep the existing table unchanged option and
> choose not perform to perform a snapshot automatically to preserve the
> schema. Unidirectional replication from the first server to the second
> worked perfectly.
> Most recently, when using SQL commands instead of Stored Procedures and
with
> the "Use column names in commands that are not replaced by stored
> procedures", I encountered:
> Violation of PRIMARY KEY constraint 'PK__test__3D5E1FD2'. Cannot
insert
> duplicate key in object 'test'.
> IDENTITY column values seem to be generated properly on each server, but
> when I setup the second subscription I encountered the error. Apparently
> when I inserted a row in the second server, it propagated to the first
> server which may have tried to send it back to the second server?
> I am not interested in new GUIID columns being introduced into our schema,
> nor can we tolerate Two Phase Commit as the connection to the servers must
> be asynchronous. Primary keys will never change. Inserted rows at each
> site will get their own even or odd key values.
> Is MS SQL Server up to the task or should I use our own trigger-based,
> asynchronous replication solution?
> Thanks, Matt
> ================================================== ========================
> Matthew J. Ramuta Enterprise Information Solutions, Inc.
> Txt: 6306973359@.mobile.att.net 4910 Main Street, Downers Grove, IL 60515
> Off: 630-512-0570 Fax: 630-512-0568 Cell: 630-697-3359
> ================================================== ========================
>
Showing posts with label column. Show all posts
Showing posts with label column. Show all posts
Monday, March 19, 2012
Sunday, March 11, 2012
can insert into but can't update a text column
I can insert into but can't update a text column - see following example :
1) create a table with a text column
CREATE TABLE [dbo].[TestLongText] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[FileContent] [text] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
2) insert text from another table into this table
insert into TestLongText(FileContent) select FileContent from
OtherTableWithTextCol where Id = 2
-- works fine
3) try to update this text column with identical text from the other table
update TestLongText set FileContent = (select FileContent from
OtherTableWithTextCol where Id = 2) where id = 1
-- returns this error: The text, ntext, and image data types are invalid in
this subquery or aggregate expression.
Try something like:
[code]
update TestLongText
set FileContent = o.FileContent
from TestLongText t,OtherTableWithTextCol o
where o.Id = 2 and t.Id =1
[/code]
Cristian Lefter, SQL Server MVP
"David Laub" <dlaub@.wheels.com> wrote in message
news:Oa%23NfYUNFHA.3560@.TK2MSFTNGP14.phx.gbl...
>I can insert into but can't update a text column - see following example :
> 1) create a table with a text column
> CREATE TABLE [dbo].[TestLongText] (
> [Id] [int] IDENTITY (1, 1) NOT NULL ,
> [FileContent] [text] NOT NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> 2) insert text from another table into this table
> insert into TestLongText(FileContent) select FileContent from
> OtherTableWithTextCol where Id = 2
> -- works fine
> 3) try to update this text column with identical text from the other table
> update TestLongText set FileContent = (select FileContent from
> OtherTableWithTextCol where Id = 2) where id = 1
> -- returns this error: The text, ntext, and image data types are invalid
> in
> this subquery or aggregate expression.
>
|||Thanks!! That solved it - i forgot about doing an update based on a
join instead of a subquery!
*** Sent via Developersdex http://www.codecomments.com ***
1) create a table with a text column
CREATE TABLE [dbo].[TestLongText] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[FileContent] [text] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
2) insert text from another table into this table
insert into TestLongText(FileContent) select FileContent from
OtherTableWithTextCol where Id = 2
-- works fine
3) try to update this text column with identical text from the other table
update TestLongText set FileContent = (select FileContent from
OtherTableWithTextCol where Id = 2) where id = 1
-- returns this error: The text, ntext, and image data types are invalid in
this subquery or aggregate expression.
Try something like:
[code]
update TestLongText
set FileContent = o.FileContent
from TestLongText t,OtherTableWithTextCol o
where o.Id = 2 and t.Id =1
[/code]
Cristian Lefter, SQL Server MVP
"David Laub" <dlaub@.wheels.com> wrote in message
news:Oa%23NfYUNFHA.3560@.TK2MSFTNGP14.phx.gbl...
>I can insert into but can't update a text column - see following example :
> 1) create a table with a text column
> CREATE TABLE [dbo].[TestLongText] (
> [Id] [int] IDENTITY (1, 1) NOT NULL ,
> [FileContent] [text] NOT NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> 2) insert text from another table into this table
> insert into TestLongText(FileContent) select FileContent from
> OtherTableWithTextCol where Id = 2
> -- works fine
> 3) try to update this text column with identical text from the other table
> update TestLongText set FileContent = (select FileContent from
> OtherTableWithTextCol where Id = 2) where id = 1
> -- returns this error: The text, ntext, and image data types are invalid
> in
> this subquery or aggregate expression.
>
|||Thanks!! That solved it - i forgot about doing an update based on a
join instead of a subquery!
*** Sent via Developersdex http://www.codecomments.com ***
can insert into but can't update a text column
I can insert into but can't update a text column - see following example :
1) create a table with a text column
CREATE TABLE [dbo].[TestLongText] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[FileContent] [text] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
2) insert text from another table into this table
insert into TestLongText(FileContent) select FileContent from
OtherTableWithTextCol where Id = 2
-- works fine
3) try to update this text column with identical text from the other table
update TestLongText set FileContent = (select FileContent from
OtherTableWithTextCol where Id = 2) where id = 1
-- returns this error: The text, ntext, and image data types are invalid in
this subquery or aggregate expression.Try something like:
[code]
update TestLongText
set FileContent = o.FileContent
from TestLongText t,OtherTableWithTextCol o
where o.Id = 2 and t.Id =1
[/code]
Cristian Lefter, SQL Server MVP
"David Laub" <dlaub@.wheels.com> wrote in message
news:Oa%23NfYUNFHA.3560@.TK2MSFTNGP14.phx.gbl...
>I can insert into but can't update a text column - see following example :
> 1) create a table with a text column
> CREATE TABLE [dbo].[TestLongText] (
> [Id] [int] IDENTITY (1, 1) NOT NULL ,
> [FileContent] [text] NOT NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> 2) insert text from another table into this table
> insert into TestLongText(FileContent) select FileContent from
> OtherTableWithTextCol where Id = 2
> -- works fine
> 3) try to update this text column with identical text from the other table
> update TestLongText set FileContent = (select FileContent from
> OtherTableWithTextCol where Id = 2) where id = 1
> -- returns this error: The text, ntext, and image data types are invalid
> in
> this subquery or aggregate expression.
>|||Thanks!! That solved it - i forgot about doing an update based on a
join instead of a subquery!
*** Sent via Developersdex http://www.codecomments.com ***
1) create a table with a text column
CREATE TABLE [dbo].[TestLongText] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[FileContent] [text] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
2) insert text from another table into this table
insert into TestLongText(FileContent) select FileContent from
OtherTableWithTextCol where Id = 2
-- works fine
3) try to update this text column with identical text from the other table
update TestLongText set FileContent = (select FileContent from
OtherTableWithTextCol where Id = 2) where id = 1
-- returns this error: The text, ntext, and image data types are invalid in
this subquery or aggregate expression.Try something like:
[code]
update TestLongText
set FileContent = o.FileContent
from TestLongText t,OtherTableWithTextCol o
where o.Id = 2 and t.Id =1
[/code]
Cristian Lefter, SQL Server MVP
"David Laub" <dlaub@.wheels.com> wrote in message
news:Oa%23NfYUNFHA.3560@.TK2MSFTNGP14.phx.gbl...
>I can insert into but can't update a text column - see following example :
> 1) create a table with a text column
> CREATE TABLE [dbo].[TestLongText] (
> [Id] [int] IDENTITY (1, 1) NOT NULL ,
> [FileContent] [text] NOT NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> 2) insert text from another table into this table
> insert into TestLongText(FileContent) select FileContent from
> OtherTableWithTextCol where Id = 2
> -- works fine
> 3) try to update this text column with identical text from the other table
> update TestLongText set FileContent = (select FileContent from
> OtherTableWithTextCol where Id = 2) where id = 1
> -- returns this error: The text, ntext, and image data types are invalid
> in
> this subquery or aggregate expression.
>|||Thanks!! That solved it - i forgot about doing an update based on a
join instead of a subquery!
*** Sent via Developersdex http://www.codecomments.com ***
can insert into but can't update a text column
I can insert into but can't update a text column - see following example :
1) create a table with a text column
CREATE TABLE [dbo].[TestLongText] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[FileContent] [text] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
2) insert text from another table into this table
insert into TestLongText(FileContent) select FileContent from
OtherTableWithTextCol where Id = 2
-- works fine
3) try to update this text column with identical text from the other table
update TestLongText set FileContent = (select FileContent from
OtherTableWithTextCol where Id = 2) where id = 1
-- returns this error: The text, ntext, and image data types are invalid in
this subquery or aggregate expression.Try something like:
[code]
update TestLongText
set FileContent = o.FileContent
from TestLongText t,OtherTableWithTextCol o
where o.Id = 2 and t.Id =1
[/code]
Cristian Lefter, SQL Server MVP
"David Laub" <dlaub@.wheels.com> wrote in message
news:Oa%23NfYUNFHA.3560@.TK2MSFTNGP14.phx.gbl...
>I can insert into but can't update a text column - see following example :
> 1) create a table with a text column
> CREATE TABLE [dbo].[TestLongText] (
> [Id] [int] IDENTITY (1, 1) NOT NULL ,
> [FileContent] [text] NOT NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> 2) insert text from another table into this table
> insert into TestLongText(FileContent) select FileContent from
> OtherTableWithTextCol where Id = 2
> -- works fine
> 3) try to update this text column with identical text from the other table
> update TestLongText set FileContent = (select FileContent from
> OtherTableWithTextCol where Id = 2) where id = 1
> -- returns this error: The text, ntext, and image data types are invalid
> in
> this subquery or aggregate expression.
>
1) create a table with a text column
CREATE TABLE [dbo].[TestLongText] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[FileContent] [text] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
2) insert text from another table into this table
insert into TestLongText(FileContent) select FileContent from
OtherTableWithTextCol where Id = 2
-- works fine
3) try to update this text column with identical text from the other table
update TestLongText set FileContent = (select FileContent from
OtherTableWithTextCol where Id = 2) where id = 1
-- returns this error: The text, ntext, and image data types are invalid in
this subquery or aggregate expression.Try something like:
[code]
update TestLongText
set FileContent = o.FileContent
from TestLongText t,OtherTableWithTextCol o
where o.Id = 2 and t.Id =1
[/code]
Cristian Lefter, SQL Server MVP
"David Laub" <dlaub@.wheels.com> wrote in message
news:Oa%23NfYUNFHA.3560@.TK2MSFTNGP14.phx.gbl...
>I can insert into but can't update a text column - see following example :
> 1) create a table with a text column
> CREATE TABLE [dbo].[TestLongText] (
> [Id] [int] IDENTITY (1, 1) NOT NULL ,
> [FileContent] [text] NOT NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> 2) insert text from another table into this table
> insert into TestLongText(FileContent) select FileContent from
> OtherTableWithTextCol where Id = 2
> -- works fine
> 3) try to update this text column with identical text from the other table
> update TestLongText set FileContent = (select FileContent from
> OtherTableWithTextCol where Id = 2) where id = 1
> -- returns this error: The text, ntext, and image data types are invalid
> in
> this subquery or aggregate expression.
>
Saturday, February 25, 2012
Can I use IIF statement in the RS query?
I tried to insert this line of code to the Column in the query design but it
doesn't work. Similar command works in MS-ACCESS.
IIf('[Policy Received?]=No', DateDiff('y', tblFileInfo.EffDate, GETDATE), 0)
Can someone help, thanks in advance.You can put this kind of formula in the textbox that displays the data. The
query design is limited to the back-end functionality, and Access has some
extra VB capabilities most databases don't support. But the reporting front
end does support this.
Cheers,
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"LV" <LV@.discussions.microsoft.com> wrote in message
news:3210CAF1-91B7-401F-B1EF-E6B1D29161FC@.microsoft.com...
>I tried to insert this line of code to the Column in the query design but
>it
> doesn't work. Similar command works in MS-ACCESS.
> IIf('[Policy Received?]=No', DateDiff('y', tblFileInfo.EffDate, GETDATE),
> 0)
> Can someone help, thanks in advance.|||In addition to what Jeff says. What database are you going against? If it is
against Access MDB then you might be able to use the generic query window
(versus the graphical). This is basically passthrough window. It depends on
how the Access OLEDB provider handles it. If it is against SQL Server data
then this will definitely not work since it is not SQL Server SQL format. If
going against SQL Server then you can always test out your SQL using the
Query Analyzer.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:eyBkf%2391EHA.1300@.TK2MSFTNGP14.phx.gbl...
> You can put this kind of formula in the textbox that displays the data.
The
> query design is limited to the back-end functionality, and Access has some
> extra VB capabilities most databases don't support. But the reporting
front
> end does support this.
> Cheers,
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "LV" <LV@.discussions.microsoft.com> wrote in message
> news:3210CAF1-91B7-401F-B1EF-E6B1D29161FC@.microsoft.com...
> >I tried to insert this line of code to the Column in the query design but
> >it
> > doesn't work. Similar command works in MS-ACCESS.
> >
> > IIf('[Policy Received?]=No', DateDiff('y', tblFileInfo.EffDate,
GETDATE),
> > 0)
> >
> > Can someone help, thanks in advance.
>|||Thank you,
Yes I am using Access data, as you suggested I will try the textbox method.
"Jeff A. Stucker" wrote:
> You can put this kind of formula in the textbox that displays the data. The
> query design is limited to the back-end functionality, and Access has some
> extra VB capabilities most databases don't support. But the reporting front
> end does support this.
> Cheers,
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "LV" <LV@.discussions.microsoft.com> wrote in message
> news:3210CAF1-91B7-401F-B1EF-E6B1D29161FC@.microsoft.com...
> >I tried to insert this line of code to the Column in the query design but
> >it
> > doesn't work. Similar command works in MS-ACCESS.
> >
> > IIf('[Policy Received?]=No', DateDiff('y', tblFileInfo.EffDate, GETDATE),
> > 0)
> >
> > Can someone help, thanks in advance.
>
>|||Thank you,
I did try the generic SQL but did not get the correct results somehow it
return just he true side of the IIF statement. Here is how I get around my
problem, I created the query with the IIF statemet in Access, on RS report I
connect to the query and it works. I don't know if this is the correct way
to do it but for now at least I can get it to work.
"Bruce L-C [MVP]" wrote:
> In addition to what Jeff says. What database are you going against? If it is
> against Access MDB then you might be able to use the generic query window
> (versus the graphical). This is basically passthrough window. It depends on
> how the Access OLEDB provider handles it. If it is against SQL Server data
> then this will definitely not work since it is not SQL Server SQL format. If
> going against SQL Server then you can always test out your SQL using the
> Query Analyzer.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:eyBkf%2391EHA.1300@.TK2MSFTNGP14.phx.gbl...
> > You can put this kind of formula in the textbox that displays the data.
> The
> > query design is limited to the back-end functionality, and Access has some
> > extra VB capabilities most databases don't support. But the reporting
> front
> > end does support this.
> >
> > Cheers,
> >
> > --
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "LV" <LV@.discussions.microsoft.com> wrote in message
> > news:3210CAF1-91B7-401F-B1EF-E6B1D29161FC@.microsoft.com...
> > >I tried to insert this line of code to the Column in the query design but
> > >it
> > > doesn't work. Similar command works in MS-ACCESS.
> > >
> > > IIf('[Policy Received?]=No', DateDiff('y', tblFileInfo.EffDate,
> GETDATE),
> > > 0)
> > >
> > > Can someone help, thanks in advance.
> >
> >
>
>
doesn't work. Similar command works in MS-ACCESS.
IIf('[Policy Received?]=No', DateDiff('y', tblFileInfo.EffDate, GETDATE), 0)
Can someone help, thanks in advance.You can put this kind of formula in the textbox that displays the data. The
query design is limited to the back-end functionality, and Access has some
extra VB capabilities most databases don't support. But the reporting front
end does support this.
Cheers,
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"LV" <LV@.discussions.microsoft.com> wrote in message
news:3210CAF1-91B7-401F-B1EF-E6B1D29161FC@.microsoft.com...
>I tried to insert this line of code to the Column in the query design but
>it
> doesn't work. Similar command works in MS-ACCESS.
> IIf('[Policy Received?]=No', DateDiff('y', tblFileInfo.EffDate, GETDATE),
> 0)
> Can someone help, thanks in advance.|||In addition to what Jeff says. What database are you going against? If it is
against Access MDB then you might be able to use the generic query window
(versus the graphical). This is basically passthrough window. It depends on
how the Access OLEDB provider handles it. If it is against SQL Server data
then this will definitely not work since it is not SQL Server SQL format. If
going against SQL Server then you can always test out your SQL using the
Query Analyzer.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:eyBkf%2391EHA.1300@.TK2MSFTNGP14.phx.gbl...
> You can put this kind of formula in the textbox that displays the data.
The
> query design is limited to the back-end functionality, and Access has some
> extra VB capabilities most databases don't support. But the reporting
front
> end does support this.
> Cheers,
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "LV" <LV@.discussions.microsoft.com> wrote in message
> news:3210CAF1-91B7-401F-B1EF-E6B1D29161FC@.microsoft.com...
> >I tried to insert this line of code to the Column in the query design but
> >it
> > doesn't work. Similar command works in MS-ACCESS.
> >
> > IIf('[Policy Received?]=No', DateDiff('y', tblFileInfo.EffDate,
GETDATE),
> > 0)
> >
> > Can someone help, thanks in advance.
>|||Thank you,
Yes I am using Access data, as you suggested I will try the textbox method.
"Jeff A. Stucker" wrote:
> You can put this kind of formula in the textbox that displays the data. The
> query design is limited to the back-end functionality, and Access has some
> extra VB capabilities most databases don't support. But the reporting front
> end does support this.
> Cheers,
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "LV" <LV@.discussions.microsoft.com> wrote in message
> news:3210CAF1-91B7-401F-B1EF-E6B1D29161FC@.microsoft.com...
> >I tried to insert this line of code to the Column in the query design but
> >it
> > doesn't work. Similar command works in MS-ACCESS.
> >
> > IIf('[Policy Received?]=No', DateDiff('y', tblFileInfo.EffDate, GETDATE),
> > 0)
> >
> > Can someone help, thanks in advance.
>
>|||Thank you,
I did try the generic SQL but did not get the correct results somehow it
return just he true side of the IIF statement. Here is how I get around my
problem, I created the query with the IIF statemet in Access, on RS report I
connect to the query and it works. I don't know if this is the correct way
to do it but for now at least I can get it to work.
"Bruce L-C [MVP]" wrote:
> In addition to what Jeff says. What database are you going against? If it is
> against Access MDB then you might be able to use the generic query window
> (versus the graphical). This is basically passthrough window. It depends on
> how the Access OLEDB provider handles it. If it is against SQL Server data
> then this will definitely not work since it is not SQL Server SQL format. If
> going against SQL Server then you can always test out your SQL using the
> Query Analyzer.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:eyBkf%2391EHA.1300@.TK2MSFTNGP14.phx.gbl...
> > You can put this kind of formula in the textbox that displays the data.
> The
> > query design is limited to the back-end functionality, and Access has some
> > extra VB capabilities most databases don't support. But the reporting
> front
> > end does support this.
> >
> > Cheers,
> >
> > --
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "LV" <LV@.discussions.microsoft.com> wrote in message
> > news:3210CAF1-91B7-401F-B1EF-E6B1D29161FC@.microsoft.com...
> > >I tried to insert this line of code to the Column in the query design but
> > >it
> > > doesn't work. Similar command works in MS-ACCESS.
> > >
> > > IIf('[Policy Received?]=No', DateDiff('y', tblFileInfo.EffDate,
> GETDATE),
> > > 0)
> > >
> > > Can someone help, thanks in advance.
> >
> >
>
>
can i use a store procedure as default value for a column?
Is it possible to use a stored procedure to fill the default value of a column when i'm building the db?
i mean if i can use a stored procedure for the "colum property": "default value or bnding"
if yes how can i do it?
No, you won't be able to specify a stored procedure in your table definition as a default value for a column. Your best bet for this is to set up a trigger (either AFTER or INSTEAD OF) to enforce the default value.
|||You can, however, use a scalar user-defined function as the default value. So if you can convert the sproc to a UDF or call it from one, you're good to go.
Don
Can I use a computed column for this?
I have a table with two integers and I want another computed column like so
IIF ( colX >= colY, 1, 0 )
but enterprise manager keeps telling me there is an error in my Formula but
I do not see what it could be.
the computed column is defined to be an int
thanks,
J> but enterprise manager keeps telling me there is an error in my Formula
Egads, why are you using Enterprise Manager for this? Try Query Analyzer.
CREATE TABLE dbo.foo
(
x INT,
y INT,
z AS CONVERT(INT, CASE WHEN x >= y THEN 1 ELSE 0 END)
)
Also, not sure why you want to use INT. This could easily be BIT or TINYINT
if it is only ever going to contain two possible values.|||there is no IIF() in SQL - it's CASE
case when colx>=coly then 1 else 0 end
james wrote:
>I have a table with two integers and I want another computed column like so
>IIF ( colX >= colY, 1, 0 )
>but enterprise manager keeps telling me there is an error in my Formula but
>I do not see what it could be.
>the computed column is defined to be an int
>thanks,
>J
>
>|||James,
Use a "case" expression.
select colX, colY, case when colX >= colY then 1 else 0 end as colZ
from t1
AMB
"james" wrote:
> I have a table with two integers and I want another computed column like s
o
> IIF ( colX >= colY, 1, 0 )
> but enterprise manager keeps telling me there is an error in my Formula bu
t
> I do not see what it could be.
> the computed column is defined to be an int
> thanks,
> J
>
>|||CASE WHEN colx >= coly THEN 1 ELSE 0 END
Why would you put such a thing in a computed column? Put it in a view or
query rather than clutter your table with redundant information.
David Portas
SQL Server MVP
--|||Thanks Aaron, and all the other responses. All your comments are helpful
JIM
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23GfaOmjuFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Egads, why are you using Enterprise Manager for this? Try Query Analyzer.
> CREATE TABLE dbo.foo
> (
> x INT,
> y INT,
> z AS CONVERT(INT, CASE WHEN x >= y THEN 1 ELSE 0 END)
> )
> Also, not sure why you want to use INT. This could easily be BIT or
> TINYINT if it is only ever going to contain two possible values.
>
IIF ( colX >= colY, 1, 0 )
but enterprise manager keeps telling me there is an error in my Formula but
I do not see what it could be.
the computed column is defined to be an int
thanks,
J> but enterprise manager keeps telling me there is an error in my Formula
Egads, why are you using Enterprise Manager for this? Try Query Analyzer.
CREATE TABLE dbo.foo
(
x INT,
y INT,
z AS CONVERT(INT, CASE WHEN x >= y THEN 1 ELSE 0 END)
)
Also, not sure why you want to use INT. This could easily be BIT or TINYINT
if it is only ever going to contain two possible values.|||there is no IIF() in SQL - it's CASE
case when colx>=coly then 1 else 0 end
james wrote:
>I have a table with two integers and I want another computed column like so
>IIF ( colX >= colY, 1, 0 )
>but enterprise manager keeps telling me there is an error in my Formula but
>I do not see what it could be.
>the computed column is defined to be an int
>thanks,
>J
>
>|||James,
Use a "case" expression.
select colX, colY, case when colX >= colY then 1 else 0 end as colZ
from t1
AMB
"james" wrote:
> I have a table with two integers and I want another computed column like s
o
> IIF ( colX >= colY, 1, 0 )
> but enterprise manager keeps telling me there is an error in my Formula bu
t
> I do not see what it could be.
> the computed column is defined to be an int
> thanks,
> J
>
>|||CASE WHEN colx >= coly THEN 1 ELSE 0 END
Why would you put such a thing in a computed column? Put it in a view or
query rather than clutter your table with redundant information.
David Portas
SQL Server MVP
--|||Thanks Aaron, and all the other responses. All your comments are helpful
JIM
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23GfaOmjuFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Egads, why are you using Enterprise Manager for this? Try Query Analyzer.
> CREATE TABLE dbo.foo
> (
> x INT,
> y INT,
> z AS CONVERT(INT, CASE WHEN x >= y THEN 1 ELSE 0 END)
> )
> Also, not sure why you want to use INT. This could easily be BIT or
> TINYINT if it is only ever going to contain two possible values.
>
Friday, February 24, 2012
can I use * to specify 'Output Column' for OLD DB Source Editor?
I am working on a situation similar to 'Get all from Table A that isn't in Table B' http://www.sqlis.com/default.aspx?311
I noticed that if one column's name of source table changes,(say Year to Year2) I have to modify all 'data flow transformations' in the task.
I am new to SSIS.
thanks! -ZZ
ZZhang wrote:
I am working on a situation similar to 'Get all from Table A that isn't in Table B' http://www.sqlis.com/default.aspx?311
I noticed that if one column's name of source table changes,(say Year to Year2) I have to modify all 'data flow transformations' in the task.
I am new to SSIS.
thanks! -ZZ
You could use '*' if you wanted but this is about as bad as bad practice gets. Don't do it. If the name of a column changes then SSIS will break because it stored the metadata of the external data source. This is by design.
-Jamie
|||
Hi, Jamie,
Thanks so much for your quick response! I have two questions then.
1. How to use * ? I can not find it in the 'OLD DB Source Editor'.
2. Let me simplifing my case. I have a remote source table ( which has may columns, incluing 'ID' and 'Date'). The schema may change, but not 'ID' and 'Date' columns. The DTS job is to get all rows ( select * from myTable where [Date] = getdate() ), and output to a delimited flat file.
What is the best practice SSIS for this case?
Thanks again!
-ZZ
|||Like I said. You can't do it. If the external metadata changes then your data-flow will error.
-Jamie
|||
thanks, Jamie!
Honestly, this surprised me, if it can not use *. I will choose NOT to use SSIS for my simple job, because it does not make sense to modify ( and test) SSIS package every time the schema changes. I hope there is a workaround to meet my job requirement in SSIS.
-ZZ
|||What? Your problem is the fact that your schema is changing, not that SSIS can't handle it. Are you saying that its impossible to know what your schema will look like from one day to the next? I've never seen a company that would run its systems like that nor would i want to.
Sorry to sound rude but it just sounds crazy to me!
-Jamie
|||
Thanks Jamie for your time to answer my question!
I am new to SSIS, and have not used variable, expression, and sricpt much. I am open-mind, and believe there is a way (simple or difficult), to solve my issue. Maybe you are right, but here I am searching 'how-to' solution, like ( select * from MyTable). Should I use *? it is another question.
Thanks again!
-ZZhang
|||Again,
Yes you can use "SELECT * FROM MyTable"|||
It seems that MS has solution for 'Dynamic Metadata', although SSIS pipeline requires static metadata.
"Advacned ETL: Embedding Integration Services" from PDC05 mentioned this issue. SMO is needed.
I am still searching for the samples.
-ZZhang
can I use * to specify 'Output Column' for OLD DB Source Editor?
I am working on a situation similar to 'Get all from Table A that isn't in Table B' http://www.sqlis.com/default.aspx?311
I noticed that if one column's name of source table changes,(say Year to Year2) I have to modify all 'data flow transformations' in the task.
I am new to SSIS.
thanks! -ZZ
I am working on a situation similar to 'Get all from Table A that isn't in Table B' http://www.sqlis.com/default.aspx?311
I noticed that if one column's name of source table changes,(say Year to Year2) I have to modify all 'data flow transformations' in the task.
I am new to SSIS.
thanks! -ZZ
You could use '*' if you wanted but this is about as bad as bad practice gets. Don't do it. If the name of a column changes then SSIS will break because it stored the metadata of the external data source. This is by design.
-Jamie
|||
Hi, Jamie,
Thanks so much for your quick response! I have two questions then.
1. How to use * ? I can not find it in the 'OLD DB Source Editor'.
2. Let me simplifing my case. I have a remote source table ( which has may columns, incluing 'ID' and 'Date'). The schema may change, but not 'ID' and 'Date' columns. The DTS job is to get all rows ( select * from myTable where [Date] = getdate() ), and output to a delimited flat file.
What is the best practice SSIS for this case?
Thanks again!
-ZZ
|||Like I said. You can't do it. If the external metadata changes then your data-flow will error.
-Jamie
|||
thanks, Jamie!
Honestly, this surprised me, if it can not use *. I will choose NOT to use SSIS for my simple job, because it does not make sense to modify ( and test) SSIS package every time the schema changes. I hope there is a workaround to meet my job requirement in SSIS.
-ZZ
|||What? Your problem is the fact that your schema is changing, not that SSIS can't handle it. Are you saying that its impossible to know what your schema will look like from one day to the next? I've never seen a company that would run its systems like that nor would i want to.
Sorry to sound rude but it just sounds crazy to me!
-Jamie
|||
Thanks Jamie for your time to answer my question!
I am new to SSIS, and have not used variable, expression, and sricpt much. I am open-mind, and believe there is a way (simple or difficult), to solve my issue. Maybe you are right, but here I am searching 'how-to' solution, like ( select * from MyTable). Should I use *? it is another question.
Thanks again!
-ZZhang
|||Again,
Yes you can use "SELECT * FROM MyTable"|||
It seems that MS has solution for 'Dynamic Metadata', although SSIS pipeline requires static metadata.
"Advacned ETL: Embedding Integration Services" from PDC05 mentioned this issue. SMO is needed.
I am still searching for the samples.
-ZZhang
Sunday, February 19, 2012
Can I set DrillThrough Details column order?
AS2005 ... is there a way to set the order for drillthrough column details?
as far as I can see I can only select which attributes will be included ... is there a way to set the order?
would i have to redesign the dimension?
In the dimension desgin, in the tab attributes, select the field you want to sort, and right-click and Properties... there is the property orderBY and OrderByAttribute! change it!
regards!
Thursday, February 16, 2012
can I reset tbl ID to 1
I have a table in my db that has an identity column as the primary key. I have deleted all the data and have tried to use truncate table on it to reset the identity column. The table has a number of foreign key constraints so it will not truncate.
Is there any way to reset the indentity column without removing the constraints?
Hi Jack,
You can reset the seed of the identity using DBCC CHECKIDENT.
See this link for more info:
Resetting IDENTITY
Sunday, February 12, 2012
Can I move some column position by using SQL command ?
I created SQL table follow by XSD file
And when any users added new column to XSD in ordinal position = 3
But after my program successfully created new column, its position is the last position
What can I do ??
I suspect why I can't set it (I've looked for solution on MSDN already)
even though
We can see ordinal position by
this query
SELECT *
FROM INFORMATION_SCHEMA.Columns
What can I do for solving ?? Help me please
You're adding columns dynamically? They will go to the end and I'd be surprised if there was any way to change the position.
But more importantly, why are you adding columns dynamically and why do you care what position the columns are in? It makes no difference what the physical col position is in a table
Subscribe to:
Posts (Atom)