Does SQLSERVER support fuzzy text searching(like the function of agrep).
For example, given the input keyword [homogenos] and similarity
parameter [2 characters], the function will find out valid result from
datasource by either replacing,inserting or deleting upto two different
characters from word [homogenos].
Both homogenooos(homogeno[+oo]s) and homogeos(homoge[-n]os are valid
result.
thx.No, not directly. You need to build a function that does Levenstein Edit
distance. You will find an implementation of this in the Fuzzy functions
which ship with SSIS in SQL 2005.
You can also use the expansion options in the thesaurus capabilities in
FullText search so a search on homogenos could be expanded to search on
homogenos homogenoos, homogeneous or homogeos, but you have to know in
advance what all the expansions might be and hard code them into your
thesaurus file.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"zlf" <zlfcn@.hotmail.com> wrote in message
news:uGyRG55YGHA.4944@.TK2MSFTNGP02.phx.gbl...
> Does SQLSERVER support fuzzy text searching(like the function of agrep).
> For example, given the input keyword [homogenos] and similarity
> parameter [2 characters], the function will find out valid result from
> datasource by either replacing,inserting or deleting upto two different
> characters from word [homogenos].
> Both homogenooos(homogeno[+oo]s) and homogeos(homoge[-n]os are valid
> result.
> thx.
>
Showing posts with label example. Show all posts
Showing posts with label example. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
Does SQL have a function that return "null" for records which dont exist in a FK realation
Does SQL have a function that return "null" for records which don't exist? Per example in a FK relation ship, that not all records in the first table have a "child" in the second table, so it returns null records.
Thank you very much.
when doing select with 'join's over two tables it would return null.
For example, you have customers and customerdetails table
if you do
select *
from customers
left join customerdetails on customers.customerId = customerdetails.customerId
if there is no customer details for a customer the fields corresponding to the customer details table would be null.
Check the different types of joins 'left join', 'inner join', 'outer join', 'cross join'...i think that's all :)
Cheers,
Yani
|||Thank you very much indeedYani Dzhurov.
Monday, March 19, 2012
Does many tables matters
Does it matter if we have hugh number of tables vs few tables.
One example is this.
We have a table called Vendors where VendorID is the Primary key, and
another table VendorNotes (VendorID Int, Note varchar(500)) where VendorID
is the foreign key. This is one to one relation, and all vendors won't have
notes.
Is it good practice to do like this, or just add Note column to the vendors
table, and let it be null.
And does it matter if we add many columns to a table without using it.
Please give me some advices/suggestions. I need it desperately.
Thanks for any help you can provide
SteveDatabases with hundreds, even thousands, of tables is not that unusual.
Unused columns take small amounts of storage space (space is inexpensive).
The trade off is storage/retreival cost vs. development/programming cost. In
general, I think it's a 'non issue'.
Having Vendors and VendorNotes is quite acceptable -especially if the notes
are subject to frequent change and growth in size. Of course, the db purists
would say NO, all data related directly to the Vendor key *should* be in the
same table. Others would accept this arrangement for performance and
stability reasons.
So, the tried and true response is: It Depends. It depends upon what works
best for your design and the skill sets of those that have to develop and
maintian the applications that use the database.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Steve, Putman" <Steve@.noemailcom> wrote in message
news:OY$nP5tlGHA.4144@.TK2MSFTNGP05.phx.gbl...
> Does it matter if we have hugh number of tables vs few tables.
> One example is this.
> We have a table called Vendors where VendorID is the Primary key, and
> another table VendorNotes (VendorID Int, Note varchar(500)) where VendorID
> is the foreign key. This is one to one relation, and all vendors won't
> have notes.
> Is it good practice to do like this, or just add Note column to the
> vendors table, and let it be null.
> And does it matter if we add many columns to a table without using it.
> Please give me some advices/suggestions. I need it desperately.
> Thanks for any help you can provide
> Steve
>
>|||Thanks Arnie for your quick reply.
My assumption was that it does not take any storage space if column values
are null.
Steve,
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OXLSCFulGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Databases with hundreds, even thousands, of tables is not that unusual.
> Unused columns take small amounts of storage space (space is inexpensive).
> The trade off is storage/retreival cost vs. development/programming cost.
> In general, I think it's a 'non issue'.
> Having Vendors and VendorNotes is quite acceptable -especially if the
> notes are subject to frequent change and growth in size. Of course, the db
> purists would say NO, all data related directly to the Vendor key *should*
> be in the same table. Others would accept this arrangement for performance
> and stability reasons.
> So, the tried and true response is: It Depends. It depends upon what works
> best for your design and the skill sets of those that have to develop and
> maintian the applications that use the database.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Steve, Putman" <Steve@.noemailcom> wrote in message
> news:OY$nP5tlGHA.4144@.TK2MSFTNGP05.phx.gbl...
>|||Oh NULL values take up storage space alright.
"Steve, Putman" <Steve@.noemailcom> wrote in message
news:eo2IHYwlGHA.748@.TK2MSFTNGP02.phx.gbl...
> Thanks Arnie for your quick reply.
> My assumption was that it does not take any storage space if column values
> are null.
> Steve,
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:OXLSCFulGHA.4512@.TK2MSFTNGP04.phx.gbl...
>|||NULL values do take up storage space. Not as much as, say, a CHAR(40), but
unfortunately representing NULLs does take up some space. Is space your
primary concern?
"Steve, Putman" <Steve@.noemailcom> wrote in message
news:eo2IHYwlGHA.748@.TK2MSFTNGP02.phx.gbl...
> Thanks Arnie for your quick reply.
> My assumption was that it does not take any storage space if column values
> are null.
> Steve,
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:OXLSCFulGHA.4512@.TK2MSFTNGP04.phx.gbl...
>|||I tend to avoid one-to-one joins if I can. I recognize that NULLs can
take up space, and uing a 1-to-1 is a solution for that, but having a
1-to-1 join means that everytime I want to pull back information about
a vendor (including notes), I have to perform a table join. This (in
my opinion) is unnecessary in most scenarios. If an attribute of an
entity exists, then it should belong with that entity.
However, why do your vendors only have one note? This seems like a
great scenario for a 0-to-many join; if you want to add information to
a vendor, INSERT another row in your notes table. If two people are
adding notes about a vendor, then there is minimal opportunity for
concurrency issues.
In sum: if your entity (Vendor) truly has only one instance of an
attribute (VendorNote), then I would include it in the table, and allow
for NULLs (again, a design choice). However, I would first question
why is a VendorNote a singularity.
Stu
Steve, Putman wrote:
> Does it matter if we have hugh number of tables vs few tables.
> One example is this.
> We have a table called Vendors where VendorID is the Primary key, and
> another table VendorNotes (VendorID Int, Note varchar(500)) where VendorID
> is the foreign key. This is one to one relation, and all vendors won't hav
e
> notes.
> Is it good practice to do like this, or just add Note column to the vendor
s
> table, and let it be null.
> And does it matter if we add many columns to a table without using it.
> Please give me some advices/suggestions. I need it desperately.
> Thanks for any help you can provide
> Steve|||>> We have a table called Vendors where VendorID is the Primary key, and an
other table .. <<
The other table is tricky than your pseudo-code:
CREATE TABLE VendorNotes
(vendor_id INTEGER NOT NULL PRIMARY KEY
REFERENCES Vendors(vendor_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
vendor_note VARCHAR(500) NOT NULL);
The **required** uniqueness constraint has overhead. The **required**
DRI actions have overhead. Or you can take the attitude that the
database can fill up with orphans and other crap until it chokes or has
no integrity. And every time you use it, you need an OUTER JOIN. My
favorite was one of these things where a series of identifiers got
re-used and inherited orphans in the un-constrainted 1:1 table.
The cost of adding a few of NULLs is basically a bit flag to mark a
column as NULL-able, or you can default it to an empty string. That is
not looking so bad now.|||Thanks Guys,
Actualy Vendors-VendorNote is just an example I gave.
Actually there are around 40 columns which we have added to a table, and
which is very very rarely used, or may never be used.
In this scenerio should be ok to keep in on a same table or better to
seperate it.
My original question was related to this.
I am still in a learning stage. So I need to follow some good practice.
Steve
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1151201152.819334.35550@.y41g2000cwy.googlegroups.com...
> The other table is tricky than your pseudo-code:
> CREATE TABLE VendorNotes
> (vendor_id INTEGER NOT NULL PRIMARY KEY
> REFERENCES Vendors(vendor_id)
> ON DELETE CASCADE
> ON UPDATE CASCADE,
> vendor_note VARCHAR(500) NOT NULL);
> The **required** uniqueness constraint has overhead. The **required**
> DRI actions have overhead. Or you can take the attitude that the
> database can fill up with orphans and other crap until it chokes or has
> no integrity. And every time you use it, you need an OUTER JOIN. My
> favorite was one of these things where a series of identifiers got
> re-used and inherited orphans in the un-constrainted 1:1 table.
> The cost of adding a few of NULLs is basically a bit flag to mark a
> column as NULL-able, or you can default it to an empty string. That is
> not looking so bad now.
>|||When learning, it's always best to rely on theory. You can develop
"practical" work-arounds later in your career (when theory fails to
perform as well as needed in real-world scenarios). Doing a 1-to-1
join in order to build a complete entity (to avoid the storage of
NULLS) is a practical solution, not a theoretical one.
My .02
Stu
Steve, Putman wrote:
> Thanks Guys,
> Actualy Vendors-VendorNote is just an example I gave.
> Actually there are around 40 columns which we have added to a table, and
> which is very very rarely used, or may never be used.
> In this scenerio should be ok to keep in on a same table or better to
> seperate it.
> My original question was related to this.
> I am still in a learning stage. So I need to follow some good practice.
> Steve
>
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1151201152.819334.35550@.y41g2000cwy.googlegroups.com...
One example is this.
We have a table called Vendors where VendorID is the Primary key, and
another table VendorNotes (VendorID Int, Note varchar(500)) where VendorID
is the foreign key. This is one to one relation, and all vendors won't have
notes.
Is it good practice to do like this, or just add Note column to the vendors
table, and let it be null.
And does it matter if we add many columns to a table without using it.
Please give me some advices/suggestions. I need it desperately.
Thanks for any help you can provide
SteveDatabases with hundreds, even thousands, of tables is not that unusual.
Unused columns take small amounts of storage space (space is inexpensive).
The trade off is storage/retreival cost vs. development/programming cost. In
general, I think it's a 'non issue'.
Having Vendors and VendorNotes is quite acceptable -especially if the notes
are subject to frequent change and growth in size. Of course, the db purists
would say NO, all data related directly to the Vendor key *should* be in the
same table. Others would accept this arrangement for performance and
stability reasons.
So, the tried and true response is: It Depends. It depends upon what works
best for your design and the skill sets of those that have to develop and
maintian the applications that use the database.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Steve, Putman" <Steve@.noemailcom> wrote in message
news:OY$nP5tlGHA.4144@.TK2MSFTNGP05.phx.gbl...
> Does it matter if we have hugh number of tables vs few tables.
> One example is this.
> We have a table called Vendors where VendorID is the Primary key, and
> another table VendorNotes (VendorID Int, Note varchar(500)) where VendorID
> is the foreign key. This is one to one relation, and all vendors won't
> have notes.
> Is it good practice to do like this, or just add Note column to the
> vendors table, and let it be null.
> And does it matter if we add many columns to a table without using it.
> Please give me some advices/suggestions. I need it desperately.
> Thanks for any help you can provide
> Steve
>
>|||Thanks Arnie for your quick reply.
My assumption was that it does not take any storage space if column values
are null.
Steve,
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OXLSCFulGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Databases with hundreds, even thousands, of tables is not that unusual.
> Unused columns take small amounts of storage space (space is inexpensive).
> The trade off is storage/retreival cost vs. development/programming cost.
> In general, I think it's a 'non issue'.
> Having Vendors and VendorNotes is quite acceptable -especially if the
> notes are subject to frequent change and growth in size. Of course, the db
> purists would say NO, all data related directly to the Vendor key *should*
> be in the same table. Others would accept this arrangement for performance
> and stability reasons.
> So, the tried and true response is: It Depends. It depends upon what works
> best for your design and the skill sets of those that have to develop and
> maintian the applications that use the database.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Steve, Putman" <Steve@.noemailcom> wrote in message
> news:OY$nP5tlGHA.4144@.TK2MSFTNGP05.phx.gbl...
>|||Oh NULL values take up storage space alright.
"Steve, Putman" <Steve@.noemailcom> wrote in message
news:eo2IHYwlGHA.748@.TK2MSFTNGP02.phx.gbl...
> Thanks Arnie for your quick reply.
> My assumption was that it does not take any storage space if column values
> are null.
> Steve,
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:OXLSCFulGHA.4512@.TK2MSFTNGP04.phx.gbl...
>|||NULL values do take up storage space. Not as much as, say, a CHAR(40), but
unfortunately representing NULLs does take up some space. Is space your
primary concern?
"Steve, Putman" <Steve@.noemailcom> wrote in message
news:eo2IHYwlGHA.748@.TK2MSFTNGP02.phx.gbl...
> Thanks Arnie for your quick reply.
> My assumption was that it does not take any storage space if column values
> are null.
> Steve,
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:OXLSCFulGHA.4512@.TK2MSFTNGP04.phx.gbl...
>|||I tend to avoid one-to-one joins if I can. I recognize that NULLs can
take up space, and uing a 1-to-1 is a solution for that, but having a
1-to-1 join means that everytime I want to pull back information about
a vendor (including notes), I have to perform a table join. This (in
my opinion) is unnecessary in most scenarios. If an attribute of an
entity exists, then it should belong with that entity.
However, why do your vendors only have one note? This seems like a
great scenario for a 0-to-many join; if you want to add information to
a vendor, INSERT another row in your notes table. If two people are
adding notes about a vendor, then there is minimal opportunity for
concurrency issues.
In sum: if your entity (Vendor) truly has only one instance of an
attribute (VendorNote), then I would include it in the table, and allow
for NULLs (again, a design choice). However, I would first question
why is a VendorNote a singularity.
Stu
Steve, Putman wrote:
> Does it matter if we have hugh number of tables vs few tables.
> One example is this.
> We have a table called Vendors where VendorID is the Primary key, and
> another table VendorNotes (VendorID Int, Note varchar(500)) where VendorID
> is the foreign key. This is one to one relation, and all vendors won't hav
e
> notes.
> Is it good practice to do like this, or just add Note column to the vendor
s
> table, and let it be null.
> And does it matter if we add many columns to a table without using it.
> Please give me some advices/suggestions. I need it desperately.
> Thanks for any help you can provide
> Steve|||>> We have a table called Vendors where VendorID is the Primary key, and an
other table .. <<
The other table is tricky than your pseudo-code:
CREATE TABLE VendorNotes
(vendor_id INTEGER NOT NULL PRIMARY KEY
REFERENCES Vendors(vendor_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
vendor_note VARCHAR(500) NOT NULL);
The **required** uniqueness constraint has overhead. The **required**
DRI actions have overhead. Or you can take the attitude that the
database can fill up with orphans and other crap until it chokes or has
no integrity. And every time you use it, you need an OUTER JOIN. My
favorite was one of these things where a series of identifiers got
re-used and inherited orphans in the un-constrainted 1:1 table.
The cost of adding a few of NULLs is basically a bit flag to mark a
column as NULL-able, or you can default it to an empty string. That is
not looking so bad now.|||Thanks Guys,
Actualy Vendors-VendorNote is just an example I gave.
Actually there are around 40 columns which we have added to a table, and
which is very very rarely used, or may never be used.
In this scenerio should be ok to keep in on a same table or better to
seperate it.
My original question was related to this.
I am still in a learning stage. So I need to follow some good practice.
Steve
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1151201152.819334.35550@.y41g2000cwy.googlegroups.com...
> The other table is tricky than your pseudo-code:
> CREATE TABLE VendorNotes
> (vendor_id INTEGER NOT NULL PRIMARY KEY
> REFERENCES Vendors(vendor_id)
> ON DELETE CASCADE
> ON UPDATE CASCADE,
> vendor_note VARCHAR(500) NOT NULL);
> The **required** uniqueness constraint has overhead. The **required**
> DRI actions have overhead. Or you can take the attitude that the
> database can fill up with orphans and other crap until it chokes or has
> no integrity. And every time you use it, you need an OUTER JOIN. My
> favorite was one of these things where a series of identifiers got
> re-used and inherited orphans in the un-constrainted 1:1 table.
> The cost of adding a few of NULLs is basically a bit flag to mark a
> column as NULL-able, or you can default it to an empty string. That is
> not looking so bad now.
>|||When learning, it's always best to rely on theory. You can develop
"practical" work-arounds later in your career (when theory fails to
perform as well as needed in real-world scenarios). Doing a 1-to-1
join in order to build a complete entity (to avoid the storage of
NULLS) is a practical solution, not a theoretical one.
My .02
Stu
Steve, Putman wrote:
> Thanks Guys,
> Actualy Vendors-VendorNote is just an example I gave.
> Actually there are around 40 columns which we have added to a table, and
> which is very very rarely used, or may never be used.
> In this scenerio should be ok to keep in on a same table or better to
> seperate it.
> My original question was related to this.
> I am still in a learning stage. So I need to follow some good practice.
> Steve
>
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1151201152.819334.35550@.y41g2000cwy.googlegroups.com...
Wednesday, March 7, 2012
Does anyone know how to change the Document Map root text? - DISPLAYNAME property
hi, guys
Does anyone know how to change the Document Map root text? For example, i have report, the file name is sc.rdl, and then the root is sc. Apparently this is not good. I am thinking is there a way to change it.If you are using the ReportViewer controls in VS 2005, set DisplayName on either ReportViewer.LocalReport or ReportViewer.ServerReport, depending on the mode you are using. Setting DisplayName will also affect the generated file name for export. If you are connecting to the server directly via url access or using Report Server 2000, there is no way to change this value.|||
Does anyone know how to change the Document Map root text? For example, i have report, the file name is sc.rdl, and then the root is sc. Apparently this is not good. I am thinking is there a way to change it.If you are using the ReportViewer controls in VS 2005, set DisplayName on either ReportViewer.LocalReport or ReportViewer.ServerReport, depending on the mode you are using. Setting DisplayName will also affect the generated file name for export. If you are connecting to the server directly via url access or using Report Server 2000, there is no way to change this value.|||
Brian, I want to be able to assign displayname at report design time....then be able to extract it out for display purposes when using the reportviewer control connecting to a server report. Doesn't sound like it's possible but is this something for a future enhancement. Currently, using the report viewer control, there's limited properties exposed when using ReportViewer.ServerReport.<property>. Would be nice to get at the author, description, etc. (and have an additional displayname property available).
Does anyone know how to change the Document Map root text?
hi, guys
Does anyone know how to change the Document Map root text? For example, i have report, the file name is sc.rdl, and then the root is sc. Apparently this is not good. I am thinking is there a way to change it.If you are using the ReportViewer controls in VS 2005, set DisplayName on either ReportViewer.LocalReport or ReportViewer.ServerReport, depending on the mode you are using. Setting DisplayName will also affect the generated file name for export. If you are connecting to the server directly via url access or using Report Server 2000, there is no way to change this value.|||
Does anyone know how to change the Document Map root text? For example, i have report, the file name is sc.rdl, and then the root is sc. Apparently this is not good. I am thinking is there a way to change it.If you are using the ReportViewer controls in VS 2005, set DisplayName on either ReportViewer.LocalReport or ReportViewer.ServerReport, depending on the mode you are using. Setting DisplayName will also affect the generated file name for export. If you are connecting to the server directly via url access or using Report Server 2000, there is no way to change this value.|||
Brian, I want to be able to assign displayname at report design time....then be able to extract it out for display purposes when using the reportviewer control connecting to a server report. Doesn't sound like it's possible but is this something for a future enhancement. Currently, using the report viewer control, there's limited properties exposed when using ReportViewer.ServerReport.<property>. Would be nice to get at the author, description, etc. (and have an additional displayname property available).
Sunday, February 26, 2012
Does anyone have example of opening SQL Server with ODBC using MFC
Does anyone have example of opening SQL Server with ODBC using MFCTry doing a search on http://msdn.microsoft.com/ using keywords "ODBC MFC".
One example I found was
http://msdn.microsoft.com/library/d...-us/vccore/html
/_core_ODBC_and_MFC.asp
It seems like it contains some useful infromation.
Good luck!
--
| From: "Bhavin Patel" <bpatel@.epcon.com>
| Subject: Does anyone have example of opening SQL Server with ODBC using
MFC
| Date: Tue, 13 Sep 2005 00:13:42 -0500
| Lines: 2
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
| Message-ID: <eqjHuHCuFHA.444@.TK2MSFTNGP15.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: h86.74.29.71.ip.alltel.net 71.29.74.86
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP15.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2699
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
|
|
|
One example I found was
http://msdn.microsoft.com/library/d...-us/vccore/html
/_core_ODBC_and_MFC.asp
It seems like it contains some useful infromation.
Good luck!
--
| From: "Bhavin Patel" <bpatel@.epcon.com>
| Subject: Does anyone have example of opening SQL Server with ODBC using
MFC
| Date: Tue, 13 Sep 2005 00:13:42 -0500
| Lines: 2
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
| Message-ID: <eqjHuHCuFHA.444@.TK2MSFTNGP15.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: h86.74.29.71.ip.alltel.net 71.29.74.86
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP15.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2699
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
|
|
|
Does anyone have example of opening SQL Server with ODBC using MFC
Does anyone have example of opening SQL Server with ODBC using MFCTry doing a search on http://msdn.microsoft.com/ using keywords "ODBC MFC".
One example I found was
http://msdn.microsoft.com/library/de...us/vccore/html
/_core_ODBC_and_MFC.asp
It seems like it contains some useful infromation.
Good luck!
| From: "Bhavin Patel" <bpatel@.epcon.com>
| Subject: Does anyone have example of opening SQL Server with ODBC using
MFC
| Date: Tue, 13 Sep 2005 00:13:42 -0500
| Lines: 2
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
| Message-ID: <eqjHuHCuFHA.444@.TK2MSFTNGP15.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: h86.74.29.71.ip.alltel.net 71.29.74.86
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP15.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2699
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
|
|
|
One example I found was
http://msdn.microsoft.com/library/de...us/vccore/html
/_core_ODBC_and_MFC.asp
It seems like it contains some useful infromation.
Good luck!
| From: "Bhavin Patel" <bpatel@.epcon.com>
| Subject: Does anyone have example of opening SQL Server with ODBC using
MFC
| Date: Tue, 13 Sep 2005 00:13:42 -0500
| Lines: 2
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
| Message-ID: <eqjHuHCuFHA.444@.TK2MSFTNGP15.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: h86.74.29.71.ip.alltel.net 71.29.74.86
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP15.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2699
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
|
|
|
Sunday, February 19, 2012
DocumentID not returned in Adventureworks
The Large Binary Object example installed with SQL Server 2005 has a table with the DocumentID as the key. When the row is inserted it should return the DocumentID that was created. When you debug the example stored procedure you can see the documentID is created and correct. When you run the sample unmodified it always returns -1 as the documentID. It appears the stored procedure is catching an exception and assigning the documentID a value of -1 as seen here in the Adventureworks LOB sample sored procedure (below).
However, it appears that something with the datbase engine or proce is broken and Microsoft knew this becasue the assignment to documentID upon return from the stored proc is commented out (see below). In fact the whole sample is faked because when you follow it you realize that it is reading into the database one document and writing out one that already existed in the database..
Does anyone know how to get the DocumentID back from the stored procedure that inserted the binary object into the Documents table of Adventureworks?
Thanks in advance.
Portion of Adventure works usp_InsertDocument
RAISERROR ('Insert failed.', 16, 1);
END TRY
BEGIN CATCH
SET @.DocumentID = -1;
RETURN(1);
END CATCH;
END -- END of usp_InsertDocument
;
Portion of LargeBinaryObject.cs
using (SqlConnection sqlConn = new SqlConnection(connectionString))
{
using (SqlCommand sprocCommand = new SqlCommand("[Production].[usp_InsertDocument]", sqlConn))
{
sqlConn.Open();
sprocCommand.CommandType = CommandType.StoredProcedure;
// Add time to the title because there is an unique constraint on this column.
sprocCommand.Parameters.Add(new SqlParameter("@.Title", SqlDbType.NVarChar, 50));
sprocCommand.Parameters[0].Value = fileName + DateTime.Now.TimeOfDay.ToString();
sprocCommand.Parameters.Add(new SqlParameter("@.FileName", SqlDbType.NVarChar, 400));
sprocCommand.Parameters[1].Value = fullFileName;
sprocCommand.Parameters.Add(new SqlParameter("@.FileExtension", SqlDbType.NVarChar, 8));
sprocCommand.Parameters[2].Value = fileExtension;
sprocCommand.Parameters.Add(new SqlParameter("@.Status", SqlDbType.TinyInt));
sprocCommand.Parameters[3].Value = 1;
sprocCommand.Parameters.Add(new SqlParameter("@.Document", SqlDbType.Image));
sprocCommand.Parameters[4].Value = bytes;
sprocCommand.Parameters.Add(new SqlParameter("@.DocumentID", SqlDbType.Int));
sprocCommand.Parameters[5].Direction = ParameterDirection.Output;
sprocCommand.ExecuteNonQuery();
//int DocumentID = (int)sprocCommand.Parameters[5].Value;
}
This here is a pretty easy one:
SqlCommand cmd = new SqlCommand("usp_YourProc");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
SqlParameter param_InputoutputTest = new SqlParameter("@.InputoutputTest", SqlDbType.Int);
param_InputoutputTest.Value = "1";
param_InputoutputTest.Direction = ParameterDirection.InputOutput;
SqlParameter param_ReturntTest = new SqlParameter("@.ReturntTest", SqlDbType.Int);
param_ReturntTest.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(param_InputoutputTest);
cmd.Parameters.Add(param_ReturntTest);
conn.Open();
cmd.ExecuteNonQuery();
Console.WriteLine(cmd.Parameters["@.ReturntTest"].Value.ToString());
Console.WriteLine(cmd.Parameters["@.InputoutputTest"].Value.ToString());
conn.Close();
ALTER PROCEDURE usp_YourProc
(
@.InputoutputTest INT OUTPUT
)
AS
SELECT @.InputoutputTest = -1
RETURN 2
HTH, Jens K. Suessmeyer.
-
http://www.sqlserver2005.de
-
documentation on SQL OLE methods...Where to find?
I've seen a few examples in BOL and various websites where sp_OAMethod is
used to generates scripts, return rows, etc. For example:
SET @.exec_str = 'Databases("'+@.dbname+'").Tables("'+RTRIM(UPPER(@.tbname))+'").Script(74077,"
'+ @.filename +'")'
EXEC @.hr = sp_OAMethod @.object, @.exec_str, @.return OUT
I can't seem to find any documentation that explains the possible methods
(like databases().Tables().Script).
Can someone point me to where I could find a list of these methods?
Thanks in advancesp_OACreate invokes an extermal program that has a COM OO interface.
The documentation you're looking for will reside with whichever external COM
application you're referring to.
You haevn't given us the name of the particular application you're looking
for in your post, so it's a bit hard to answer this. Your post only gives us
information on a method - perhaps if you go back to whever you got that code
snippet from & give us the part that has "sp_OACreate" in it, we may be able
to help you further.
Regards,
Greg Linwood
SQL Server MVP
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:#$OwJjrsDHA.1680@.TK2MSFTNGP12.phx.gbl...
> I've seen a few examples in BOL and various websites where sp_OAMethod is
> used to generates scripts, return rows, etc. For example:
> SET @.exec_str =>
'Databases("'+@.dbname+'").Tables("'+RTRIM(UPPER(@.tbname))+'").Script(74077,"
> '+ @.filename +'")'
> EXEC @.hr = sp_OAMethod @.object, @.exec_str, @.return OUT
> I can't seem to find any documentation that explains the possible methods
> (like databases().Tables().Script).
> Can someone point me to where I could find a list of these methods?
> Thanks in advance
>|||Hi Greg,
Thanks for the reply. Here's the sp_OACreate statement:
EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.object OUT
Thanks,
Tom
"Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote in
message news:uIDLvwrsDHA.2416@.TK2MSFTNGP10.phx.gbl...
> sp_OACreate invokes an extermal program that has a COM OO interface.
> The documentation you're looking for will reside with whichever external
COM
> application you're referring to.
> You haevn't given us the name of the particular application you're looking
> for in your post, so it's a bit hard to answer this. Your post only gives
us
> information on a method - perhaps if you go back to whever you got that
code
> snippet from & give us the part that has "sp_OACreate" in it, we may be
able
> to help you further.
> Regards,
> Greg Linwood
> SQL Server MVP
> "TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
> news:#$OwJjrsDHA.1680@.TK2MSFTNGP12.phx.gbl...
> > I've seen a few examples in BOL and various websites where sp_OAMethod
is
> > used to generates scripts, return rows, etc. For example:
> > SET @.exec_str => >
>
'Databases("'+@.dbname+'").Tables("'+RTRIM(UPPER(@.tbname))+'").Script(74077,"
> > '+ @.filename +'")'
> > EXEC @.hr = sp_OAMethod @.object, @.exec_str, @.return OUT
> >
> > I can't seem to find any documentation that explains the possible
methods
> > (like databases().Tables().Script).
> >
> > Can someone point me to where I could find a list of these methods?
> >
> > Thanks in advance
> >
> >
>|||Most COM (or Automation) objects come with their own
documentation. For instance, the object hierarchy of SQL-
DMO can be found in SQL Server Books Online. If you need
to interact with an object hierarchy that doesn't seem to
have online/printed documentation, you can try tools such
as OLE-COM Object Viewer that comes with NT Resource kit.
The object viewer allows you browse the object hierarchy
and see all the objects, methods, and proerties.
By the way, if you can stay away from sp_OAxxx stuff, stay
away from it. It's pretty ugly and you can't import the
symbolic constants.
Linchi
>--Original Message--
>I've seen a few examples in BOL and various websites
where sp_OAMethod is
>used to generates scripts, return rows, etc. For example:
>SET @.exec_str =>'Databases("'+@.dbname+'").Tables("'+RTRIM(UPPER(@.tbname))
+'").Script(74077,"
>'+ @.filename +'")'
>EXEC @.hr = sp_OAMethod @.object, @.exec_str, @.return OUT
>I can't seem to find any documentation that explains the
possible methods
>(like databases().Tables().Script).
>Can someone point me to where I could find a list of
these methods?
>Thanks in advance
>
>.
>
used to generates scripts, return rows, etc. For example:
SET @.exec_str = 'Databases("'+@.dbname+'").Tables("'+RTRIM(UPPER(@.tbname))+'").Script(74077,"
'+ @.filename +'")'
EXEC @.hr = sp_OAMethod @.object, @.exec_str, @.return OUT
I can't seem to find any documentation that explains the possible methods
(like databases().Tables().Script).
Can someone point me to where I could find a list of these methods?
Thanks in advancesp_OACreate invokes an extermal program that has a COM OO interface.
The documentation you're looking for will reside with whichever external COM
application you're referring to.
You haevn't given us the name of the particular application you're looking
for in your post, so it's a bit hard to answer this. Your post only gives us
information on a method - perhaps if you go back to whever you got that code
snippet from & give us the part that has "sp_OACreate" in it, we may be able
to help you further.
Regards,
Greg Linwood
SQL Server MVP
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:#$OwJjrsDHA.1680@.TK2MSFTNGP12.phx.gbl...
> I've seen a few examples in BOL and various websites where sp_OAMethod is
> used to generates scripts, return rows, etc. For example:
> SET @.exec_str =>
'Databases("'+@.dbname+'").Tables("'+RTRIM(UPPER(@.tbname))+'").Script(74077,"
> '+ @.filename +'")'
> EXEC @.hr = sp_OAMethod @.object, @.exec_str, @.return OUT
> I can't seem to find any documentation that explains the possible methods
> (like databases().Tables().Script).
> Can someone point me to where I could find a list of these methods?
> Thanks in advance
>|||Hi Greg,
Thanks for the reply. Here's the sp_OACreate statement:
EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.object OUT
Thanks,
Tom
"Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote in
message news:uIDLvwrsDHA.2416@.TK2MSFTNGP10.phx.gbl...
> sp_OACreate invokes an extermal program that has a COM OO interface.
> The documentation you're looking for will reside with whichever external
COM
> application you're referring to.
> You haevn't given us the name of the particular application you're looking
> for in your post, so it's a bit hard to answer this. Your post only gives
us
> information on a method - perhaps if you go back to whever you got that
code
> snippet from & give us the part that has "sp_OACreate" in it, we may be
able
> to help you further.
> Regards,
> Greg Linwood
> SQL Server MVP
> "TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
> news:#$OwJjrsDHA.1680@.TK2MSFTNGP12.phx.gbl...
> > I've seen a few examples in BOL and various websites where sp_OAMethod
is
> > used to generates scripts, return rows, etc. For example:
> > SET @.exec_str => >
>
'Databases("'+@.dbname+'").Tables("'+RTRIM(UPPER(@.tbname))+'").Script(74077,"
> > '+ @.filename +'")'
> > EXEC @.hr = sp_OAMethod @.object, @.exec_str, @.return OUT
> >
> > I can't seem to find any documentation that explains the possible
methods
> > (like databases().Tables().Script).
> >
> > Can someone point me to where I could find a list of these methods?
> >
> > Thanks in advance
> >
> >
>|||Most COM (or Automation) objects come with their own
documentation. For instance, the object hierarchy of SQL-
DMO can be found in SQL Server Books Online. If you need
to interact with an object hierarchy that doesn't seem to
have online/printed documentation, you can try tools such
as OLE-COM Object Viewer that comes with NT Resource kit.
The object viewer allows you browse the object hierarchy
and see all the objects, methods, and proerties.
By the way, if you can stay away from sp_OAxxx stuff, stay
away from it. It's pretty ugly and you can't import the
symbolic constants.
Linchi
>--Original Message--
>I've seen a few examples in BOL and various websites
where sp_OAMethod is
>used to generates scripts, return rows, etc. For example:
>SET @.exec_str =>'Databases("'+@.dbname+'").Tables("'+RTRIM(UPPER(@.tbname))
+'").Script(74077,"
>'+ @.filename +'")'
>EXEC @.hr = sp_OAMethod @.object, @.exec_str, @.return OUT
>I can't seem to find any documentation that explains the
possible methods
>(like databases().Tables().Script).
>Can someone point me to where I could find a list of
these methods?
>Thanks in advance
>
>.
>
Friday, February 17, 2012
Document Object Code
Hi all,
I've spent the last two hours looking for an example of this, but I've
come up short. What I was trying to do was create a 'test' which would
toggle a rectangles hidden property. Unfortunately, I can't seem to
figure out how to send my toggle function a document object.
Here's what I'm after. A 'bundle' report for my sales team. From
parameters, they choose a client to print for, and then select a series
of true/false parameters. Each 'sub-report' is contained in a
rectangle which will render only if the corresponding true/false is = true. In the rectangle hidden property, I had set the expression
=Code.setHidden(this)
Hoping that it would send the 'rectangle2' object to the setHidden(),
which would test the parameters, and correspondingly hide where
appropriate.
Any ideas?
Thanks much.
Brian AckermannIs it not possible to pass a document object to a code block?
Thanks,
Brian Ackermann
I've spent the last two hours looking for an example of this, but I've
come up short. What I was trying to do was create a 'test' which would
toggle a rectangles hidden property. Unfortunately, I can't seem to
figure out how to send my toggle function a document object.
Here's what I'm after. A 'bundle' report for my sales team. From
parameters, they choose a client to print for, and then select a series
of true/false parameters. Each 'sub-report' is contained in a
rectangle which will render only if the corresponding true/false is = true. In the rectangle hidden property, I had set the expression
=Code.setHidden(this)
Hoping that it would send the 'rectangle2' object to the setHidden(),
which would test the parameters, and correspondingly hide where
appropriate.
Any ideas?
Thanks much.
Brian AckermannIs it not possible to pass a document object to a code block?
Thanks,
Brian Ackermann
Subscribe to:
Posts (Atom)