Sql Case sensitivity: stored

Showing posts with label stored. Show all posts

Thursday, March 29, 2012

Change location of RS log files?

Hi,
How can I change the location where the RS log files are
stored/created? The .config file seems to have options to change the
filename, but will this allow me to specify a different folder as
well?
We would like to put these files on a seperate hard drive from our
main application drive, in case the log files ever happen to grow
exponentially and fill the entire DB again.
Matt BrownEach of the following files for MS Reporting Services has a section called
RStrace that contains the settings for the log files.
...\ReportServer\bin\ReportingServicesService.exe.config
â?¦\ReportServer\web.config
â?¦\ReportManager\web.config
A default installation writes the log files to the directory where Reporting
Services is installed under the LogFiles folder. In order to change the
default location of the log files you need to add the following line under
the RStrace section for each of the above files. Once you have added the
directory line you will need to restart the ReportServer service.
<add name=â'Directoryâ' value=â'your dirâ' />
Example:
<RStrace>
<add name="Directory" value="d:\RSLogs" />
<add name="FileName" value="ReportServerService_" />
<add name="FileSizeLimitMb" value="32" />
<add name="KeepFilesForDays" value="14" />
<add name="Prefix" value="tid, time" />
<add name="TraceListeners" value="debugwindow, file"
/>
<add name="TraceFileMode" value="unique" />
<add name="Components" value="all" />
</RStrace>

Tuesday, March 27, 2012

Change From when using xp_sendmail

I am writing a stored procedure that will send an email, using xp_sendmail, in the error handling if the procedure fails. However, I want the from and return address to list a shared mailbox, so that any questions from the user are directed back to the work queue. Can this be done?
DavidLook into CDO or xp_smtp_sendmail. (http://www.sqldev.net/download/xp/80/xpsmtp80-v1.1.0.8.zip) It's much easier and more flexible (bofem)

Sunday, March 25, 2012

Change Format of Dates

I don't know why my company did this, but dates are being stored in a char field within our database. Meanwhile, I've been setting up new pages using datetime.

That's just a little back story. My question is, is it possible to change the format of all dates in the table from yyyy/MM/dd to MM/dd/yyyy in the char field? I'm just trying to think of an easier way to change a thousand or so records instead of doing it manually.

Thanks.

Try this:

string temp ="2006/12/16";
string[] field = temp.Split(("/").ToCharArray());
string reversedate = field[2] +"/" + field[1] +"/" + field[0];
DateTime newdate = DateTime.Parse(reversedate);

|||

Or better still, run this SQL

Update table set newdatefield = parsename(replace(oldchardate, '/', '.'), 2) + '/' + parsename(replace(oldchardate, '/', '.'), 1) + '/' + parsename(replace(oldchardate, '/', '.'), 3)

|||

try this logic

convert(varchar(20),convert(datetime,'2002/02/23',111),101)

so you can just do update on your table date field content by

update YourTable
set datefield=convert(varchar(20),convert(datetime,datefield,111),101)

Thanks

|||

I ended up using this logic in a SQL statement:

datefield=right(datefield, 5)&'/'&left(datefield, 4)

sql

Tuesday, March 20, 2012

Change Date Format from mm/dd/yyyy to dd/mm/yyyy

Hi,
I have a query to retrieve data from a table for the give two dates(from and
to) in a stored procedure.
select * from employees where (DateJoined BETWEEN @.FromDate AND @.ToDate)
now I have give the dates like this to get the results
@.FromDate =01/01/2005(mm/dd/yyyy)
@.ToDate=6/2/2005(mm/dd/yyyy)
but I would like to give the date in this format (dd/mm/yyyy) like
@.FromDate =01/01/2005(dd/mm/yyyy)
@.ToDate=2/6/2005(dd/mm/yyyy)
can someone help me with this(I know there is a way in which you can specify
in select query itself, but forgot :-) )
Thanks
KiranWhy do you need to express the date in a special format? Why not let the end
user use the format the
end user want (based on regional settings) and let the application send a fo
rmat to SQL server which
is language neutral?
See http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kiran" <kiran_nospam@.gmail.com> wrote in message news:Ot61f56ZFHA.3840@.tk2msftngp13.phx.gb
l...
> Hi,
> I have a query to retrieve data from a table for the give two dates(from a
nd to) in a stored
> procedure.
> select * from employees where (DateJoined BETWEEN @.FromDate AND @.ToDate)
> now I have give the dates like this to get the results
> @.FromDate =01/01/2005(mm/dd/yyyy)
> @.ToDate=6/2/2005(mm/dd/yyyy)
> but I would like to give the date in this format (dd/mm/yyyy) like
> @.FromDate =01/01/2005(dd/mm/yyyy)
> @.ToDate=2/6/2005(dd/mm/yyyy)
> can someone help me with this(I know there is a way in which you can speci
fy in select query
> itself, but forgot :-) )
> Thanks
> Kiran
>|||Kiran wrote:
> Hi,
> I have a query to retrieve data from a table for the give two
> dates(from and to) in a stored procedure.
> select * from employees where (DateJoined BETWEEN @.FromDate AND
> @.ToDate)
> now I have give the dates like this to get the results
> @.FromDate =01/01/2005(mm/dd/yyyy)
> @.ToDate=6/2/2005(mm/dd/yyyy)
> but I would like to give the date in this format (dd/mm/yyyy) like
> @.FromDate =01/01/2005(dd/mm/yyyy)
> @.ToDate=2/6/2005(dd/mm/yyyy)
> can someone help me with this(I know there is a way in which you can
> specify in select query itself, but forgot :-) )
> Thanks
> Kiran
Always use a portable date format in SQL... or else.
YYYYMMDD
or
YYYY-MM-DDThh:mm:ss.mmm
David Gugick
Quest Software
www.imceda.com
www.quest.com

Monday, March 19, 2012

change data format in stored procedure

Hi All,

My scenario is that I want to change the default SQL server format in my stored procedure more preferably only during the course of stored procedure execution (not permanent changes does any one have idea that how will I able to achieve this simple task...

regards,

Anas

You can use the CONVERT function for this. As a common approach Date formatting should be done on the client and only on the server if you have no client application or the client application does not support it.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

change current database in stored procedure

I am trying to move whole bunch of stored procedures from 1 db to another.
Instead of going into each of them and change the table names to fully
qualified name, is there a command that I can set the current database? I
know USE does not work in sp. Any help will be highly appreciated!USE will work in dynamic SQL I think, e.g.
EXEC('USE somedb; EXEC someproc;')
However, if you're going to bother going in and putting USE statements or
something similar, why not just go in and put in the fully qualified name?
I don't see what you will be saving by NOT putting in the fully qualified
names...
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:41263B68-0B3A-4472-9FFE-54BF6CF9066C@.microsoft.com...
>I am trying to move whole bunch of stored procedures from 1 db to another.
> Instead of going into each of them and change the table names to fully
> qualified name, is there a command that I can set the current database? I
> know USE does not work in sp. Any help will be highly appreciated!|||Thanks for your response, Chew. It was just because we have too many stored
procedures that we need to move. Each of them uses so many tables. Going
into each of them and change the table names will literally kill me.
However, I do think change them to fully qualified names is the right thing
to do. At the mean time, we just want a quick solution. I tried doing
exec('use db') and it did not work.
alter procedure junk
as
begin
exec ('use abc')
select * from iv00101 where itemnmbr = '12345'
end
"Foo Man Chew" wrote:
> USE will work in dynamic SQL I think, e.g.
> EXEC('USE somedb; EXEC someproc;')
> However, if you're going to bother going in and putting USE statements or
> something similar, why not just go in and put in the fully qualified name?
> I don't see what you will be saving by NOT putting in the fully qualified
> names...
>
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:41263B68-0B3A-4472-9FFE-54BF6CF9066C@.microsoft.com...
> >I am trying to move whole bunch of stored procedures from 1 db to another.
> > Instead of going into each of them and change the table names to fully
> > qualified name, is there a command that I can set the current database? I
> > know USE does not work in sp. Any help will be highly appreciated!
>
>|||1. Move all the stored procecures from DatabaseA to DatabaseB
2. Create views in databaseB with same name as in DatabaseA
Database A
=======TableA
CREATE PROC Proc1
AS
SELECT * FROM TableA
DatabaseB
create a View
CREATE VIEW TableA
AS
SELECT * FROM DatabaseA.dbo.TableA
Move the Procedures
This way you don't have to touch you code. All you need to do create buch of
views
Thanks
Ravi
"Ed" wrote:
> Thanks for your response, Chew. It was just because we have too many stored
> procedures that we need to move. Each of them uses so many tables. Going
> into each of them and change the table names will literally kill me.
> However, I do think change them to fully qualified names is the right thing
> to do. At the mean time, we just want a quick solution. I tried doing
> exec('use db') and it did not work.
> alter procedure junk
> as
> begin
> exec ('use abc')
> select * from iv00101 where itemnmbr = '12345'
> end
> "Foo Man Chew" wrote:
> > USE will work in dynamic SQL I think, e.g.
> >
> > EXEC('USE somedb; EXEC someproc;')
> >
> > However, if you're going to bother going in and putting USE statements or
> > something similar, why not just go in and put in the fully qualified name?
> > I don't see what you will be saving by NOT putting in the fully qualified
> > names...
> >
> >
> > "Ed" <Ed@.discussions.microsoft.com> wrote in message
> > news:41263B68-0B3A-4472-9FFE-54BF6CF9066C@.microsoft.com...
> > >I am trying to move whole bunch of stored procedures from 1 db to another.
> > > Instead of going into each of them and change the table names to fully
> > > qualified name, is there a command that I can set the current database? I
> > > know USE does not work in sp. Any help will be highly appreciated!
> >
> >
> >|||Ed wrote:
> Thanks for your response, Chew. It was just because we have too many
> stored procedures that we need to move. Each of them uses so many
> tables. Going into each of them and change the table names will
> literally kill me. However, I do think change them to fully qualified
> names is the right thing to do. At the mean time, we just want a
> quick solution. I tried doing exec('use db') and it did not work.
> alter procedure junk
> as
> begin
> exec ('use abc')
> select * from iv00101 where itemnmbr = '12345'
> end
> "Foo Man Chew" wrote:
>> USE will work in dynamic SQL I think, e.g.
>> EXEC('USE somedb; EXEC someproc;')
>> However, if you're going to bother going in and putting USE
>> statements or something similar, why not just go in and put in the
>> fully qualified name? I don't see what you will be saving by NOT
>> putting in the fully qualified names...
>>
>> "Ed" <Ed@.discussions.microsoft.com> wrote in message
>> news:41263B68-0B3A-4472-9FFE-54BF6CF9066C@.microsoft.com...
>> I am trying to move whole bunch of stored procedures from 1 db to
>> another. Instead of going into each of them and change the table
>> names to fully qualified name, is there a command that I can set
>> the current database? I know USE does not work in sp. Any help
>> will be highly appreciated!
EXEC runs in its own context. Just fully qualify using DB.OWNER.SPNAME.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||> exec('use db') and it did not work.
Because that is not what I said to do.
You need to do everything inside a single EXEC.
EXEC('Use somedb; EXEC someproc')

change current database in stored procedure

Change Control Application

Is there a decent application that costs under $1000 that can be used to
maintain change control on SQL Server database schemas and stored
procedures/triggers? Embarcadero had such a utility a few years ago, and
we did an early beta only to find it didn't actually work. I went back to
their site and see they apparently no longer market it.
It's incredibly useful to be able to point to a database on a server in
realtime, instantly create a snapshot of the files needed to recreate the
database, and then place those change files into a source control system.
Something that could automate much of this work would be a welcome tool.
--
WillHi
Apart from the scripting options available in Enterprise Manager you may
want to look at dbghost www.dbghost.com or red gate tools www.red-gate.com,
you would require a separate source code control system such as Source Safe
or PVCS.
John
"Will" wrote:
> Is there a decent application that costs under $1000 that can be used to
> maintain change control on SQL Server database schemas and stored
> procedures/triggers? Embarcadero had such a utility a few years ago, and
> we did an early beta only to find it didn't actually work. I went back to
> their site and see they apparently no longer market it.
> It's incredibly useful to be able to point to a database on a server in
> realtime, instantly create a snapshot of the files needed to recreate the
> database, and then place those change files into a source control system.
> Something that could automate much of this work would be a welcome tool.
> --
> Will
>
>|||Hi,
Embarcadero still market that tool and i have it .
It is very effective and good.They have emproved a lot.
u can search again on there site\.
from
Killer

Thursday, March 8, 2012

Change attribute value in stored procedure

Can someone show me the syntax (if it is possible) to modify an attribute value in a stored proc? For example if I have an element that is being passed in as text like
<CustomerName id="2" /> Is there SQLXML functionality that I can use to change the 2 to something else (like a 3) depending on a condition? Or do I have to use the built in SQL String parsing funtions (e.g. SUBSTRING) to change the value? Will someone
please let me know? Many Thanks!
There's no XML manipulation functionality built into SQL Server 2000 (other
than shredding the XML into a table, performing your update, and retrieving
it back out as XML again using a FOR XML query - definitely not an efficient
approach!) You could use SUBSTRING as you suggest, but to be honest you'd be
better performing this kind of logic in the client application before
submitting it to the stored procedure (apply a style sheet or use an XML API
like the DOM or the XmlDocument in .NET).
The next release of SQL Server includes some extensions to XQuery that allow
you to modify data within an XML value, which is great if you can wait until
then, but probably of no practical use to you at the moment - sorry!
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"vonbrownz" <vonbrownz@.discussions.microsoft.com> wrote in message
news:62DFF8F0-CF80-46E6-8F42-673D8C0E3BFA@.microsoft.com...
Can someone show me the syntax (if it is possible) to modify an attribute
value in a stored proc? For example if I have an element that is being
passed in as text like
<CustomerName id="2" /> Is there SQLXML functionality that I can use to
change the 2 to something else (like a 3) depending on a condition? Or do I
have to use the built in SQL String parsing funtions (e.g. SUBSTRING) to
change the value? Will someone please let me know? Many Thanks!
|||Thanks Graeme! That is exactly what I was looking for.
"Graeme Malcolm" wrote:

> There's no XML manipulation functionality built into SQL Server 2000 (other
> than shredding the XML into a table, performing your update, and retrieving
> it back out as XML again using a FOR XML query - definitely not an efficient
> approach!) You could use SUBSTRING as you suggest, but to be honest you'd be
> better performing this kind of logic in the client application before
> submitting it to the stored procedure (apply a style sheet or use an XML API
> like the DOM or the XmlDocument in .NET).
> The next release of SQL Server includes some extensions to XQuery that allow
> you to modify data within an XML value, which is great if you can wait until
> then, but probably of no practical use to you at the moment - sorry!
> Cheers,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
> www.microsoft.com/mspress/books/6137.asp
>
> "vonbrownz" <vonbrownz@.discussions.microsoft.com> wrote in message
> news:62DFF8F0-CF80-46E6-8F42-673D8C0E3BFA@.microsoft.com...
> Can someone show me the syntax (if it is possible) to modify an attribute
> value in a stored proc? For example if I have an element that is being
> passed in as text like
> <CustomerName id="2" /> Is there SQLXML functionality that I can use to
> change the 2 to something else (like a 3) depending on a condition? Or do I
> have to use the built in SQL String parsing funtions (e.g. SUBSTRING) to
> change the value? Will someone please let me know? Many Thanks!
>
>

Wednesday, March 7, 2012

Challenging Search Engine Like Stored Porcedure Needed

(DML below)
First, thank you in advance for your help. Here's one for those of you that
like a little challenge, or perhaps this is a cake walk for you (I'm jealous
).
This DB is to hold information so that our helpdesk can search it for
support information. The front end will be a Visual Basic application
calling stored procedures.
Note "tblDocument.Document" and "tblSystem.Document" will hold an HTML
document. We will Full Text Indexed both of those columns so as to enable a
granular search. I'm guessing the "freetext" t-sql command is the tool for
this job to create a search engine like stored procedure.(if there's a bette
r
way, please suggest it).
The Front End VB application will have the following interface, so the
Stored Procedure will need to accomodate the following:
1) A text box to enter the search terms which searches the
tblDocument.document and tblSystem.Document columns for words that are
entered.
2) Dropdown box: Choice between a System Document (from tblSystem), or a
support document (from tblDocument). If System is choosen, it only searches
tblSystem. If Document is choosen, it only searches tblDocument. If left
blank will search both.
3) Dropdown box: Choice of a category to search (from tblcatagories), If a
category is choosen, the stored procedure will only return those documents
meeting that criteria. if left blank will search all categories.
4) Dropdown box: Choice of a SUBCategory to search (from tblsubcategories),
If Subcategory is choosen, the stored procedure will only return those
documents meeting that criteria. if left blank will search all SUBcategorie
s.
5) Dropdown box: Ability to choose whom submitted the document. If
SubmitterName is choosen, the stored procedure will only return thos documen
t
OR systems meeting that criteria. If left blank will search all systems AND
documents from all submitters.
The Stored Procedure will need to return results that look like this:
(if search terms hit a document) DocumentID, Title, SubmitterName
(if search terms hit a system) SystemID, SystemName, SubmitterName
Thanks again
Jeff
CREATE DATABASE [eDOC] ON (NAME = N'eDOC_Data', FILENAME =
N'C:\MSSQL7\Data\eDOC_Data.MDF' , SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME =
N'eDOC_Log', FILENAME = N'C:\MSSQL7\Data\eDOC_Log.LDF' , SIZE = 1, FILEGROWT
H
= 10%)
GO
exec sp_dboption N'eDOC', N'autoclose', N'false'
GO
exec sp_dboption N'eDOC', N'bulkcopy', N'false'
GO
exec sp_dboption N'eDOC', N'trunc. log', N'false'
GO
exec sp_dboption N'eDOC', N'torn page detection', N'true'
GO
exec sp_dboption N'eDOC', N'read only', N'false'
GO
exec sp_dboption N'eDOC', N'dbo use', N'false'
GO
exec sp_dboption N'eDOC', N'single', N'false'
GO
exec sp_dboption N'eDOC', N'autoshrink', N'false'
GO
exec sp_dboption N'eDOC', N'ANSI null default', N'false'
GO
exec sp_dboption N'eDOC', N'recursive triggers', N'false'
GO
exec sp_dboption N'eDOC', N'ANSI nulls', N'false'
GO
exec sp_dboption N'eDOC', N'concat null yields null', N'false'
GO
exec sp_dboption N'eDOC', N'cursor close on commit', N'false'
GO
exec sp_dboption N'eDOC', N'default to local cursor', N'false'
GO
exec sp_dboption N'eDOC', N'quoted identifier', N'false'
GO
exec sp_dboption N'eDOC', N'ANSI warnings', N'false'
GO
exec sp_dboption N'eDOC', N'auto create statistics', N'true'
GO
exec sp_dboption N'eDOC', N'auto update statistics', N'true'
GO
use [eDOC]
GO
if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1
exec sp_fulltext_database N'enable'
GO
if not exists (select * from dbo.sysfulltextcatalogs where name =
N'DocumentFullText')
exec sp_fulltext_catalog N'DocumentFullText', N'create'
GO
CREATE TABLE [dbo].[tblCategories] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryName] [char] (100) ,
[CreattionDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblDocument] (
[DocumentID] [int] IDENTITY (1000, 1) NOT NULL ,
[CategoryID] [int] NOT NULL ,
[SubCategoryID] [int] NOT NULL ,
[Title] [char] (100) ,
[KeyWords] [char] (100) ,
[SubmitterID] [int] NOT NULL ,
[CreationDate] [datetime] NULL ,
[RevisedBy] [int] NOT NULL ,
[RevisedDate] [datetime] NULL ,
[Document] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblSubCategories] (
[SubCategoyID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryID] [int] NOT NULL ,
[SubcategoryName] [char] (100) NOT NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblSubmitter] (
[SubmitterID] [int] IDENTITY (1, 1) NOT NULL ,
[SubmitterName] [char] (40) NOT NULL ,
[Username] [varchar] (20) NULL ,
[Password] [varchar] (20) NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblSystem] (
[SystemID] [int] IDENTITY (10000, 5) NOT NULL ,
[SubmitterID] [int] NOT NULL ,
[SystemName] [char] (100) NOT NULL ,
[RevisedDate] [datetime] NOT NULL ,
[RevisedBy] [char] (100) NOT NULL ,
[Document] [text] NOT NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCategories] WITH NOCHECK ADD
CONSTRAINT [PK_tblCategories] PRIMARY KEY CLUSTERED
(
[CategoryID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDocument] WITH NOCHECK ADD
CONSTRAINT [PK_tblDocument] PRIMARY KEY CLUSTERED
(
[DocumentID]
) ON [PRIMARY]
GO
USE edoc
EXEC sp_fulltext_database 'enable'
go
if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1
exec sp_fulltext_database N'enable'
GO
if not exists (select * from dbo.sysfulltextcatalogs where name =
N'DocumentFullText')
exec sp_fulltext_catalog N'DocumentFullText', N'create'
GO
exec sp_fulltext_table N'[dbo].[tblDocument]', N'create',
N'DocumentFullText', N'PK_tblDocument'
GO
exec sp_fulltext_column N'[dbo].[tblDocument]', N'Document', N'add', 1033
GO
exec sp_fulltext_table N'[dbo].[tblDocument]', N'activate'
GO
ALTER TABLE [dbo].[tblSubCategories] WITH NOCHECK ADD
CONSTRAINT [PK_tblSubCategories] PRIMARY KEY CLUSTERED
(
[SubCategoyID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblSubmitter] WITH NOCHECK ADD
CONSTRAINT [PK_tblSubmitter] PRIMARY KEY CLUSTERED
(
[SubmitterID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblSystem] WITH NOCHECK ADD
CONSTRAINT [PK_tblSystem] PRIMARY KEY CLUSTERED
(
[SystemID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCategories] WITH NOCHECK ADD
CONSTRAINT [DF_tblCategories_CreattionDate] DEFAULT (getdate()) FOR
[CreattionDate]
GO
ALTER TABLE [dbo].[tblDocument] WITH NOCHECK ADD
CONSTRAINT [DF_tblDocument_CreationDate] DEFAULT (getdate()) FOR
[CreationDate]
GO
ALTER TABLE [dbo].[tblSubCategories] WITH NOCHECK ADD
CONSTRAINT [DF_tblSubCategories_CreationDate] DEFAULT (getdate()) FOR
[CreationDate]
GO
ALTER TABLE [dbo].[tblSubmitter] WITH NOCHECK ADD
CONSTRAINT [DF_tblSubmitter_CreationDate] DEFAULT (getdate()) FOR
[CreationDate]
GO
ALTER TABLE [dbo].[tblSystem] WITH NOCHECK ADD
CONSTRAINT [DF_tblSystem_RevisedDate] DEFAULT (getdate()) FOR [RevisedDate],
CONSTRAINT [DF_tblSystem_CreationDate] DEFAULT (getdate()) FOR [CreationDate]
GO
ALTER TABLE [dbo].[tblDocument] ADD
CONSTRAINT [FK_tblDocument_tblCategories] FOREIGN KEY
(
[CategoryID]
) REFERENCES [dbo].[tblCategories] (
[CategoryID]
),
CONSTRAINT [FK_tblDocument_tblSubCategories1] FOREIGN KEY
(
[SubCategoryID]
) REFERENCES [dbo].[tblSubCategories] (
[SubCategoyID]
),
CONSTRAINT [FK_tblDocument_tblSubmitter2] FOREIGN KEY
(
[SubmitterID]
) REFERENCES [dbo].[tblSubmitter] (
[SubmitterID]
)
GO
ALTER TABLE [dbo].[tblSubCategories] ADD
CONSTRAINT [FK_tblSubCategories_tblCategories] FOREIGN KEY
(
[CategoryID]
) REFERENCES [dbo].[tblCategories] (
[CategoryID]
)
GO
ALTER TABLE [dbo].[tblSystem] ADD
CONSTRAINT [FK_tblSystem_tblSubmitter1] FOREIGN KEY
(
[SubmitterID]
) REFERENCES [dbo].[tblSubmitter] (
[SubmitterID]
)
GO
use edoc
go
INSERT into tblcategories(CategoryName) VALUES ('hardware')
insert into tblcategories(CategoryName) values ('Software')
insert into tblsubcategories(categoryID,subcategoryn
ame) values ('1','Dell
Server')
insert into tblsubcategories(categoryID,subcategoryn
ame) values
('2','Outlook')
insert into tblsubmitter(submittername, username, [password]) values ('Joe
Williams', 'WilliamsJ', 'password')
insert into tblsubmitter(submittername, username, [password]) values ('Mike
Smith', 'SmithM', 'password')
insert into tblsystem(submitterID, SystemName, RevisedBy, Document) values
('1', 'SQL Server', '2', 'Long HTML document')
insert into tblsystem(submitterID, SystemName, RevisedBy, Document) values
('2', 'Exchange', '1', 'Very Long HTML document')
insert into tbldocument(categoryID, subcategoryID, Title, Keywords,
submitterID, Revisedby, document) values ('1', '2', 'Outlook Crashes',
'run32.dll abend', '1', '2', 'Outlook document goes here')
insert into tbldocument(categoryID, subcategoryID, Title, Keywords,
submitterID, Revisedby, document) values ('2', '1', 'Dell Post Error 151',
'0x8765432', '2', '1', 'Dell document goes here')
goOn Sat, 10 Sep 2005 11:47:08 -0700, Jeff wrote:

>(DML below)
Hi Jeff,
Thanks for supplying DDL and sample data!

>Note "tblDocument.Document" and "tblSystem.Document" will hold an HTML
>document.
Prefixing table names with "tbl" is considered a bad habit in the world
of relational databases. I'd rename these tables "Systems" and
"Documents" (since they store information about more than one system and
more than one document).

> We will Full Text Indexed both of those columns so as to enable a
>granular search. I'm guessing the "freetext" t-sql command is the tool for
>this job to create a search engine like stored procedure.(if there's a bett
er
>way, please suggest it).
I have no experience with full text indexing. From the sound of it, it's
probably the tool for the job, though.
Note that there is a special group where the full text experts prefer to
hang their hats: microsoft.public.sqlserver.fulltext.

>The Front End VB application will have the following interface, so the
>Stored Procedure will need to accomodate the following:
>1) A text box to enter the search terms which searches the
>tblDocument.document and tblSystem.Document columns for words that are
>entered.
That would be a job for full text search, I guess.

>2) Dropdown box: Choice between a System Document (from tblSystem), or a
>support document (from tblDocument). If System is choosen, it only searche
s
>tblSystem. If Document is choosen, it only searches tblDocument. If left
>blank will search both.
My choice would be to have two stored procedures. One searches the
Systems table, the other searches the Documents table. The front end
uses this dropdown box to decide whether to call the first, the second,
ot both and merge the results in the front end. (The added advantage is
that your front end has no trouble figuring out if a returned row refers
to a system or to a document).

>3) Dropdown box: Choice of a category to search (from tblcatagories), If a
>category is choosen, the stored procedure will only return those documents
>meeting that criteria. if left blank will search all categories.
>4) Dropdown box: Choice of a SUBCategory to search (from tblsubcategories)
,
>If Subcategory is choosen, the stored procedure will only return those
>documents meeting that criteria. if left blank will search all SUBcategori
es.
>5) Dropdown box: Ability to choose whom submitted the document. If
>SubmitterName is choosen, the stored procedure will only return thos docume
nt
>OR systems meeting that criteria. If left blank will search all systems AN
D
>documents from all submitters.
For all these three requirements: www.sommarskog.se/dyn-search.html.
(big snip)
>CREATE TABLE [dbo].[tblCategories] (
> [CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
> [CategoryName] [char] (100) ,
> [CreattionDate] [datetime] NOT NULL
> ) ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblCategories] WITH NOCHECK ADD
> CONSTRAINT [PK_tblCategories] PRIMARY KEY CLUSTERED
> (
> [CategoryID]
> ) ON [PRIMARY]
>GO
* If not all categories have names with a length of >90 characters,
change the datatype to varchar(100).
* An identity can never be the only key of a table. You'll need a
natural key as well, to prevent duplicates. Assuming that no two
categories share the same name, add a UNIQUE constraint on CategoryName.
* Oh, and fix the typo in CreattionDate before deploying your system. It
is still an easy fix now.
* Why is there no NOT NULL constraint for CategoryName?

>CREATE TABLE [dbo].[tblDocument] (
> [DocumentID] [int] IDENTITY (1000, 1) NOT NULL ,
> [CategoryID] [int] NOT NULL ,
> [SubCategoryID] [int] NOT NULL ,
> [Title] [char] (100) ,
> [KeyWords] [char] (100) ,
> [SubmitterID] [int] NOT NULL ,
> [CreationDate] [datetime] NULL ,
> [RevisedBy] [int] NOT NULL ,
> [RevisedDate] [datetime] NULL ,
> [Document] [text] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblDocument] WITH NOCHECK ADD
> CONSTRAINT [PK_tblDocument] PRIMARY KEY CLUSTERED
> (
> [DocumentID]
> ) ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblDocument] ADD
> CONSTRAINT [FK_tblDocument_tblCategories] FOREIGN KEY
> (
> [CategoryID]
> ) REFERENCES [dbo].[tblCategories] (
> [CategoryID]
> ),
> CONSTRAINT [FK_tblDocument_tblSubCategories1] FOREIGN KEY
> (
> [SubCategoryID]
> ) REFERENCES [dbo].[tblSubCategories] (
> [SubCategoyID]
> ),
> CONSTRAINT [FK_tblDocument_tblSubmitter2] FOREIGN KEY
> (
> [SubmitterID]
> ) REFERENCES [dbo].[tblSubmitter] (
> [SubmitterID]
> )
>GO
* Change char to varchar in this table as well (and in the remainging
tables of course - I won't repeat this advice)
* Since there are no tables that refer to this table, there's no need
for a surrogate key. You should remove the identity column.
* I don't know your data well enough to propose what set of columns make
up the business key in your business, but I'm sure you do. Maybe just
"Title"? Maybe the combination of Category, Subcategory and Title?
* If subcategory S1 belongs to Category C1, is it then possible for a
document to belong to subcategory S1, but to category C2? If not, then
the column CategoryID should not be in this table at all, unless you
have compelling reasons (probably performance-driven) to use a partly
denormalized design - and in that case, you should probably add some
extra constraints to make sure that you'll never get any data like the
example I gave. (Suggestion: use a foreign key on (Category,
Subcategory) - you'll have to create a "redundant" UNIQUE constraint in
the Subcategories table for that)
* Why is there no NOT NULL constraint for Title, Keywords, and Document?
* Why is there a NOT NULL constraint for RevisedBy, but none for
RevisedDate? I think RevisedBy shouyld be NULLable as well?
* Why is there no foreign key constraint for RevisedBy?

>CREATE TABLE [dbo].[tblSubCategories] (
> [SubCategoyID] [int] IDENTITY (1, 1) NOT NULL ,
> [CategoryID] [int] NOT NULL ,
> [SubcategoryName] [char] (100) NOT NULL ,
> [CreationDate] [datetime] NOT NULL
> ) ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblSubCategories] WITH NOCHECK ADD
> CONSTRAINT [PK_tblSubCategories] PRIMARY KEY CLUSTERED
> (
> [SubCategoyID]
> ) ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblSubCategories] ADD
> CONSTRAINT [FK_tblSubCategories_tblCategories] FOREIGN KEY
> (
> [CategoryID]
> ) REFERENCES [dbo].[tblCategories] (
> [CategoryID]
> )
>GO
* Assuming that two subcategories of the same category can't have the
same name, but two subcategories of different categories can, the real
key is (CategoryID, SubcategoryName). Use a UNIQUE constraint to prevent
duplicates.

>CREATE TABLE [dbo].[tblSubmitter] (
> [SubmitterID] [int] IDENTITY (1, 1) NOT NULL ,
> [SubmitterName] [char] (40) NOT NULL ,
> [Username] [varchar] (20) NULL ,
> [Password] [varchar] (20) NULL ,
> [CreationDate] [datetime] NOT NULL
> ) ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblSubmitter] WITH NOCHECK ADD
> CONSTRAINT [PK_tblSubmitter] PRIMARY KEY CLUSTERED
> (
> [SubmitterID]
> ) ON [PRIMARY]
>GO
* Storing passwords in a table is not really safe.
* The UNIQUE constraint should go on SubmitterName, I guess.
* Why is there no NOT NULL constraint for Username and Password?

>CREATE TABLE [dbo].[tblSystem] (
> [SystemID] [int] IDENTITY (10000, 5) NOT NULL ,
> [SubmitterID] [int] NOT NULL ,
> [SystemName] [char] (100) NOT NULL ,
> [RevisedDate] [datetime] NOT NULL ,
> [RevisedBy] [char] (100) NOT NULL ,
> [Document] [text] NOT NULL ,
> [CreationDate] [datetime] NOT NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblSystem] WITH NOCHECK ADD
> CONSTRAINT [PK_tblSystem] PRIMARY KEY CLUSTERED
> (
> [SystemID]
> ) ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblSystem] ADD
> CONSTRAINT [FK_tblSystem_tblSubmitter1] FOREIGN KEY
> (
> [SubmitterID]
> ) REFERENCES [dbo].[tblSubmitter] (
> [SubmitterID]
> )
>GO
* Most of the comments that relate to the Documents table relate to this
table as well. I won;t repeat them here.
* Why the non-standard seed and increment for the IDENTITY property? Is
this table part of some distributed replication setup?
* Why did you define RevisedBy as int in the Documents table, but as
char(100) in this table? Having two columns with the sme name but with
different datatypes (and different contents) will cause you a wealthy
supply of bugs!
Best, Hugo
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I really appreciate your input. In short to your comments, some were errors
,
some are on my to-do list, like adding the constraints, some are due to the
evoluation of the DB and we need to go back and fix, and the rest are things
that I aparently need to further learn.
And I especially appreciate the advice on the best way to handle the stored
procedure which I where I'm stuck since DBs are not my strong point as you
can tell. I'm not a programmer, I'm a newtorking guy that only dabbles in
this.
But where I'm really at a loss is the stored procedures themselves. So how
can I turn the below basic procedure into one that will accept the various
options and return results. Obviously, as it stands now, I can't leave a
variable blank...
@.searchterm must have something entere in it
@.Category must accept either a 1, 2, or be left blank to return both 1 & 2.
@.Subcategory same as above
@.Submitter same as above
Thanks again in advance for all your help!
CREATE PROCEDURE SearchDocument
(
@.SearchTerm char (200),
@.Category int,
@.Subcategory int,
@.Submitter int
)
as
SELECT dbo.tblDocument.DocumentID,
dbo.tblDocument.Title,
dbo.tblSubmitter.SubmitterName
FROM dbo.tblDocument join dbo.tblSubmitter
on (tbldocument.submitterid = tblsubmitter.submitterid)
where freetext (document, @.searchterm) and
dbo.tblDocument.SubCategoryID = @.subcategory AND
dbo.tblDocument.CategoryID = @.category AND
dbo.tblDocument.SubmitterID = @.submitter
"Hugo Kornelis" wrote:

> On Sat, 10 Sep 2005 11:47:08 -0700, Jeff wrote:
>
> Hi Jeff,
> Thanks for supplying DDL and sample data!
>
> Prefixing table names with "tbl" is considered a bad habit in the world
> of relational databases. I'd rename these tables "Systems" and
> "Documents" (since they store information about more than one system and
> more than one document).
>
> I have no experience with full text indexing. From the sound of it, it's
> probably the tool for the job, though.
> Note that there is a special group where the full text experts prefer to
> hang their hats: microsoft.public.sqlserver.fulltext.
>
> That would be a job for full text search, I guess.
>
> My choice would be to have two stored procedures. One searches the
> Systems table, the other searches the Documents table. The front end
> uses this dropdown box to decide whether to call the first, the second,
> ot both and merge the results in the front end. (The added advantage is
> that your front end has no trouble figuring out if a returned row refers
> to a system or to a document).
>
> For all these three requirements: www.sommarskog.se/dyn-search.html.
> (big snip)
> (...)
> * If not all categories have names with a length of >90 characters,
> change the datatype to varchar(100).
> * An identity can never be the only key of a table. You'll need a
> natural key as well, to prevent duplicates. Assuming that no two
> categories share the same name, add a UNIQUE constraint on CategoryName.
> * Oh, and fix the typo in CreattionDate before deploying your system. It
> is still an easy fix now.
> * Why is there no NOT NULL constraint for CategoryName?
>
> (...)
> (...)
> * Change char to varchar in this table as well (and in the remainging
> tables of course - I won't repeat this advice)
> * Since there are no tables that refer to this table, there's no need
> for a surrogate key. You should remove the identity column.
> * I don't know your data well enough to propose what set of columns make
> up the business key in your business, but I'm sure you do. Maybe just
> "Title"? Maybe the combination of Category, Subcategory and Title?
> * If subcategory S1 belongs to Category C1, is it then possible for a
> document to belong to subcategory S1, but to category C2? If not, then
> the column CategoryID should not be in this table at all, unless you
> have compelling reasons (probably performance-driven) to use a partly
> denormalized design - and in that case, you should probably add some
> extra constraints to make sure that you'll never get any data like the
> example I gave. (Suggestion: use a foreign key on (Category,
> Subcategory) - you'll have to create a "redundant" UNIQUE constraint in
> the Subcategories table for that)
> * Why is there no NOT NULL constraint for Title, Keywords, and Document?
> * Why is there a NOT NULL constraint for RevisedBy, but none for
> RevisedDate? I think RevisedBy shouyld be NULLable as well?
> * Why is there no foreign key constraint for RevisedBy?
>
> (...)
> (...)
> * Assuming that two subcategories of the same category can't have the
> same name, but two subcategories of different categories can, the real
> key is (CategoryID, SubcategoryName). Use a UNIQUE constraint to prevent
> duplicates.
>
> (...)
> * Storing passwords in a table is not really safe.
> * The UNIQUE constraint should go on SubmitterName, I guess.
> * Why is there no NOT NULL constraint for Username and Password?
>
> (...)
> (...)
> * Most of the comments that relate to the Documents table relate to this
> table as well. I won;t repeat them here.
> * Why the non-standard seed and increment for the IDENTITY property? Is
> this table part of some distributed replication setup?
> * Why did you define RevisedBy as int in the Documents table, but as
> char(100) in this table? Having two columns with the sme name but with
> different datatypes (and different contents) will cause you a wealthy
> supply of bugs!
> Best, Hugo
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||Jeff (Jeff@.discussions.microsoft.com) writes:
> But where I'm really at a loss is the stored procedures themselves. So
> how can I turn the below basic procedure into one that will accept the
> various options and return results. Obviously, as it stands now, I
> can't leave a variable blank...
> @.searchterm must have something entere in it
> @.Category must accept either a 1, 2, or be left blank to return both 1 &
> 2.
> @.Subcategory same as above
> @.Submitter same as above
You cannot leave it blank, but you can set it to NULL, and you can even make
that a default value:
@.SearchTerm char (200),
@.Category int = NULL,
@.Subcategory int = NULL,
@.Submitter int = ULL

> where freetext (document, @.searchterm) and
> dbo.tblDocument.SubCategoryID = @.subcategory AND
> dbo.tblDocument.CategoryID = @.category AND
> dbo.tblDocument.SubmitterID = @.submitter
Then you can say
AND (dbo.tblDocument.SubCategoryID = @.subcategory OR @.subcategory IS NULL
Note that if any of the columns are indexed, those indexes will not be
used. If you want an index on, say, CategoryID to be used, you will
need to use IF statements that leads to different SELECT statements,
or use dynamic SQL.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Oh yes! I remember that trick of setting the variables to null now...
Thanks!!
"Jeff" wrote:

> (DML below)
> First, thank you in advance for your help. Here's one for those of you th
at
> like a little challenge, or perhaps this is a cake walk for you (I'm jealo
us).
> This DB is to hold information so that our helpdesk can search it for
> support information. The front end will be a Visual Basic application
> calling stored procedures.
> Note "tblDocument.Document" and "tblSystem.Document" will hold an HTML
> document. We will Full Text Indexed both of those columns so as to enable
a
> granular search. I'm guessing the "freetext" t-sql command is the tool fo
r
> this job to create a search engine like stored procedure.(if there's a bet
ter
> way, please suggest it).
> The Front End VB application will have the following interface, so the
> Stored Procedure will need to accomodate the following:
> 1) A text box to enter the search terms which searches the
> tblDocument.document and tblSystem.Document columns for words that are
> entered.
> 2) Dropdown box: Choice between a System Document (from tblSystem), or a
> support document (from tblDocument). If System is choosen, it only search
es
> tblSystem. If Document is choosen, it only searches tblDocument. If left
> blank will search both.
> 3) Dropdown box: Choice of a category to search (from tblcatagories), If
a
> category is choosen, the stored procedure will only return those documents
> meeting that criteria. if left blank will search all categories.
> 4) Dropdown box: Choice of a SUBCategory to search (from tblsubcategories
),
> If Subcategory is choosen, the stored procedure will only return those
> documents meeting that criteria. if left blank will search all SUBcategor
ies.
> 5) Dropdown box: Ability to choose whom submitted the document. If
> SubmitterName is choosen, the stored procedure will only return thos docum
ent
> OR systems meeting that criteria. If left blank will search all systems A
ND
> documents from all submitters.
>
> The Stored Procedure will need to return results that look like this:
> (if search terms hit a document) DocumentID, Title, SubmitterName
> (if search terms hit a system) SystemID, SystemName, SubmitterName
>
> Thanks again
> Jeff
>
> CREATE DATABASE [eDOC] ON (NAME = N'eDOC_Data', FILENAME =
> N'C:\MSSQL7\Data\eDOC_Data.MDF' , SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME
=
> N'eDOC_Log', FILENAME = N'C:\MSSQL7\Data\eDOC_Log.LDF' , SIZE = 1, FILEGRO
WTH
> = 10%)
> GO
> exec sp_dboption N'eDOC', N'autoclose', N'false'
> GO
> exec sp_dboption N'eDOC', N'bulkcopy', N'false'
> GO
> exec sp_dboption N'eDOC', N'trunc. log', N'false'
> GO
> exec sp_dboption N'eDOC', N'torn page detection', N'true'
> GO
> exec sp_dboption N'eDOC', N'read only', N'false'
> GO
> exec sp_dboption N'eDOC', N'dbo use', N'false'
> GO
> exec sp_dboption N'eDOC', N'single', N'false'
> GO
> exec sp_dboption N'eDOC', N'autoshrink', N'false'
> GO
> exec sp_dboption N'eDOC', N'ANSI null default', N'false'
> GO
> exec sp_dboption N'eDOC', N'recursive triggers', N'false'
> GO
> exec sp_dboption N'eDOC', N'ANSI nulls', N'false'
> GO
> exec sp_dboption N'eDOC', N'concat null yields null', N'false'
> GO
> exec sp_dboption N'eDOC', N'cursor close on commit', N'false'
> GO
> exec sp_dboption N'eDOC', N'default to local cursor', N'false'
> GO
> exec sp_dboption N'eDOC', N'quoted identifier', N'false'
> GO
> exec sp_dboption N'eDOC', N'ANSI warnings', N'false'
> GO
> exec sp_dboption N'eDOC', N'auto create statistics', N'true'
> GO
> exec sp_dboption N'eDOC', N'auto update statistics', N'true'
> GO
> use [eDOC]
> GO
> if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1
> exec sp_fulltext_database N'enable'
> GO
> if not exists (select * from dbo.sysfulltextcatalogs where name =
> N'DocumentFullText')
> exec sp_fulltext_catalog N'DocumentFullText', N'create'
> GO
> CREATE TABLE [dbo].[tblCategories] (
> [CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
> [CategoryName] [char] (100) ,
> [CreattionDate] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblDocument] (
> [DocumentID] [int] IDENTITY (1000, 1) NOT NULL ,
> [CategoryID] [int] NOT NULL ,
> [SubCategoryID] [int] NOT NULL ,
> [Title] [char] (100) ,
> [KeyWords] [char] (100) ,
> [SubmitterID] [int] NOT NULL ,
> [CreationDate] [datetime] NULL ,
> [RevisedBy] [int] NOT NULL ,
> [RevisedDate] [datetime] NULL ,
> [Document] [text] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblSubCategories] (
> [SubCategoyID] [int] IDENTITY (1, 1) NOT NULL ,
> [CategoryID] [int] NOT NULL ,
> [SubcategoryName] [char] (100) NOT NULL ,
> [CreationDate] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblSubmitter] (
> [SubmitterID] [int] IDENTITY (1, 1) NOT NULL ,
> [SubmitterName] [char] (40) NOT NULL ,
> [Username] [varchar] (20) NULL ,
> [Password] [varchar] (20) NULL ,
> [CreationDate] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblSystem] (
> [SystemID] [int] IDENTITY (10000, 5) NOT NULL ,
> [SubmitterID] [int] NOT NULL ,
> [SystemName] [char] (100) NOT NULL ,
> [RevisedDate] [datetime] NOT NULL ,
> [RevisedBy] [char] (100) NOT NULL ,
> [Document] [text] NOT NULL ,
> [CreationDate] [datetime] NOT NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblCategories] WITH NOCHECK ADD
> CONSTRAINT [PK_tblCategories] PRIMARY KEY CLUSTERED
> (
> [CategoryID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblDocument] WITH NOCHECK ADD
> CONSTRAINT [PK_tblDocument] PRIMARY KEY CLUSTERED
> (
> [DocumentID]
> ) ON [PRIMARY]
> GO
>
> USE edoc
> EXEC sp_fulltext_database 'enable'
> go
> if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1
> exec sp_fulltext_database N'enable'
> GO
> if not exists (select * from dbo.sysfulltextcatalogs where name =
> N'DocumentFullText')
> exec sp_fulltext_catalog N'DocumentFullText', N'create'
> GO
> exec sp_fulltext_table N'[dbo].[tblDocument]', N'create',
> N'DocumentFullText', N'PK_tblDocument'
> GO
> exec sp_fulltext_column N'[dbo].[tblDocument]', N'Document', N'add', 1033
> GO
> exec sp_fulltext_table N'[dbo].[tblDocument]', N'activate'
> GO
> ALTER TABLE [dbo].[tblSubCategories] WITH NOCHECK ADD
> CONSTRAINT [PK_tblSubCategories] PRIMARY KEY CLUSTERED
> (
> [SubCategoyID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblSubmitter] WITH NOCHECK ADD
> CONSTRAINT [PK_tblSubmitter] PRIMARY KEY CLUSTERED
> (
> [SubmitterID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblSystem] WITH NOCHECK ADD
> CONSTRAINT [PK_tblSystem] PRIMARY KEY CLUSTERED
> (
> [SystemID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblCategories] WITH NOCHECK ADD
> CONSTRAINT [DF_tblCategories_CreattionDate] DEFAULT (getdate()) FOR
> [CreattionDate]
> GO
> ALTER TABLE [dbo].[tblDocument] WITH NOCHECK ADD
> CONSTRAINT [DF_tblDocument_CreationDate] DEFAULT (getdate()) FOR
> [CreationDate]
> GO
> ALTER TABLE [dbo].[tblSubCategories] WITH NOCHECK ADD
> CONSTRAINT [DF_tblSubCategories_CreationDate] DEFAULT (getdate()) FOR
> [CreationDate]
> GO
> ALTER TABLE [dbo].[tblSubmitter] WITH NOCHECK ADD
> CONSTRAINT [DF_tblSubmitter_CreationDate] DEFAULT (getdate()) FOR
> [CreationDate]
> GO
> ALTER TABLE [dbo].[tblSystem] WITH NOCHECK ADD
> CONSTRAINT [DF_tblSystem_RevisedDate] DEFAULT (getdate()) FOR [RevisedDate],
> CONSTRAINT [DF_tblSystem_CreationDate] DEFAULT (getdate()) FOR [CreationDate]
> GO
> ALTER TABLE [dbo].[tblDocument] ADD
> CONSTRAINT [FK_tblDocument_tblCategories] FOREIGN KEY
> (
> [CategoryID]
> ) REFERENCES [dbo].[tblCategories] (
> [CategoryID]
> ),
> CONSTRAINT [FK_tblDocument_tblSubCategories1] FOREIGN KEY
> (
> [SubCategoryID]
> ) REFERENCES [dbo].[tblSubCategories] (
> [SubCategoyID]
> ),
> CONSTRAINT [FK_tblDocument_tblSubmitter2] FOREIGN KEY
> (
> [SubmitterID]
> ) REFERENCES [dbo].[tblSubmitter] (
> [SubmitterID]
> )
> GO
> ALTER TABLE [dbo].[tblSubCategories] ADD
> CONSTRAINT [FK_tblSubCategories_tblCategories] FOREIGN KEY
> (
> [CategoryID]
> ) REFERENCES [dbo].[tblCategories] (
> [CategoryID]
> )
> GO
> ALTER TABLE [dbo].[tblSystem] ADD
> CONSTRAINT [FK_tblSystem_tblSubmitter1] FOREIGN KEY
> (
> [SubmitterID]
> ) REFERENCES [dbo].[tblSubmitter] (
> [SubmitterID]
> )|||Jeff,
> But where I'm really at a loss is the stored procedures themselves...
So, the column document "freetext (document, @.searchterm)" is the only
column that is FT-enabled. Correct?
Could you provide more details on the SQL Server version (@.@.version) as well
as the OS platform, and language of text in the document column? As all of
these variables are important for functionality as is the number of rows in
your FT-enabled table.
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:BDC416B4-331A-46C4-A6C3-85F9844DC90C@.microsoft.com...
> Oh yes! I remember that trick of setting the variables to null now...
> Thanks!!
> "Jeff" wrote:
>|||On Sun, 11 Sep 2005 11:22:04 -0700, Jeff wrote:
(snip)
>But where I'm really at a loss is the stored procedures themselves. So how
>can I turn the below basic procedure into one that will accept the various
>options and return results. Obviously, as it stands now, I can't leave a
>variable blank...
>@.searchterm must have something entere in it
>@.Category must accept either a 1, 2, or be left blank to return both 1 & 2.
>@.Subcategory same as above
>@.Submitter same as above
(snip)
Hi Jeff,
I attempted to answer that question by providing a link to Erland's page
about this: http://www.sommarskog.se/dyn-search.html. Sure, it's a long
read, but (IMO) well worth the time you spend reading it. You'll find
that Erland presents many ways to achieve what you are trying to do
(searching with optional search arguments), and elaborates on the pros
and cons of all the various methods.
I see that Erland now has also posted a solution taht uses one of the
method's described on his page. I trust that this was enough to get you
going (but feel free to ask again if it's not!).
But I still recommend that you take the time to read the complete
article - as I said: time well spent!!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> This DB is to hold information so that our helpdesk can search it for sup
port information. <<
Have you looked into a Help Desk package instead of writing your own?

Chaging the Min and Max Scale of a Chart at run time

I have a stored procedure which will bring me back the Min, Max and Mean of different result sets. What I want to do with the Y-Axis is set the Min scale value of the chart to be Min -%5 and the Max scale value to be Max + 5%.

Is there a way to change the Y-Axis values at report run time without spitting my own RDL?

In RS 2005, the Min/Max/CrossAt/MajorInterval/MinorInterval settings can be expression based. Note that by using an aggregate function such as first you can even reference certain values from another dataset than the chart is bound to. For example, you could use an expression similar to:

=CDbl(First(Fields!MinValue.Value, "StoredProcDataset")) - 0.05

-- Robert

|||That's great Robert but it's not apparent seeing that it doesn't follow the usual standard of having the combo box with the <expression> as a selection. Worked for me though and I thank you.|||

Hi!

I want to change my y-axis into always showing integers, start with zero and show an y-axis longer than the maximum value of the points.
How do I do that in csharp?

My code is like this now: (Chart looks ok for values between 0 and 25, but makes a chart with a max value 1 have an Y axis that is divided into 0.1 and up to 1.0 ):

chartGraf.Axis.Y.TickmarkStyle = AxisTickStyle.Smart;

chartGraf.Axis.Y.LineThickness = 1;

chartGraf.Axis.Y.RangeMin = 0;

if ( MyMaximumValue < 3) {

chartGraf.Axis.Y.RangeMax = 3;

}

-Heidi

Chaging the Min and Max Scale of a Chart at run time

Is there a way to change the Y-Axis values at report run time without spitting my own RDL?

=CDbl(First(Fields!MinValue.Value, "StoredProcDataset")) - 0.05

-- Robert

|||That's great Robert but it's not apparent seeing that it doesn't follow the usual standard of having the combo box with the <expression> as a selection. Worked for me though and I thank you.|||

Hi!

I want to change my y-axis into always showing integers, start with zero and show an y-axis longer than the maximum value of the points.
How do I do that in csharp?

My code is like this now: (Chart looks ok for values between 0 and 25, but makes a chart with a max value 1 have an Y axis that is divided into 0.1 and up to 1.0 ):

chartGraf.Axis.Y.TickmarkStyle = AxisTickStyle.Smart;

chartGraf.Axis.Y.LineThickness = 1;

chartGraf.Axis.Y.RangeMin = 0;

if ( MyMaximumValue < 3) {

chartGraf.Axis.Y.RangeMax = 3;

}

-Heidi

Saturday, February 25, 2012

Certain rows to excel files

Hello,

I have a table T1 with ID and Desc fields. I want to export this table to excel files based on the ID field. So my stored procedure select all the same T1.ID and export it to ID.xls files. How can I do this?

You can easily achieve this in DTS Import/Export Wizard. Suppose you're using SQL2000, open Enterprise Manager->go to the table from which you want to export data->right click choose All Tasks-> Export Data-> in the Specify Table Copy or Query step, choose Use a Query...->enter the SELECT command (e.g. SELECT * FROM Orders WHERE EmployeeID=6), or use Query Builder->complete the wizard.

|||

Thanks for the reply, How should I run the same queries for all the IDs separately in DTS and save the results to excel files?

certain rows to Excel files

Hello,

Hi,

While Using DTS Import/Export Wizard,
After Choosing Data Source and DataDestination,
Wizard asks to Specify Table Copy or Query,
At the Time Select the Radio Button [Use a Query to Specify the Data To Transfer].

It will Take you To Type Sql Statement where Query Statement Panel is there ,you can Type Query with your Condition.

|||

Thanks for the reply.

This should be done once every day , so I am trying to automate it. How should I develop my query and dts based on this criteria? Any example will be greatly appreciated.

|||

Select Jobs From SQL Server Agent Under Management of the Selected Server(In EnterpriseManager)

Select New Job and Name it
Select Steps Tab in New Job Properties
Click New step and Name Step and Write Query in Command Panel
Click New Schedule and Name Schedule and Select Schedule Type

Now you Achieve the Required Thing

|||

I still do not see how the query that I am writing will export data to a different excel file based on the ID. All the rows that have the same ID should go to a single excel file, so I am expecting more than one excel file based on the distinct values of ID to be created. How can I do this in DTS?

Certain rows to excel files

Hello,
I have a table T1 with ID and Desc fields. I want to export this table to
excel files based on the ID field. So my stored procedure select all the sam
e
T1.ID and export it to ID.xls files. How can I do this?Jim,
is this a one-off? If so I'd use the import/export wizard. If it is a
regular process, I'd look at using a scheduled DTS package (you can use the
import/export one as a template for this). There are other options namely
BCP and OSQL which run from the command-line which might also be relevant
for you.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hi Paul,
This should be done once every day , so I am trying to automate it. How
should I develop my query and dts based on this criteria? Any example will b
e
greatly appreciated.
"Paul Ibison" wrote:

> Jim,
> is this a one-off? If so I'd use the import/export wizard. If it is a
> regular process, I'd look at using a scheduled DTS package (you can use th
e
> import/export one as a template for this). There are other options namely
> BCP and OSQL which run from the command-line which might also be relevant
> for you.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>|||Jim,
is the ID list hardcoded or do new IDs appear? If it is hardcoded, then
you'll have a single 'Transform data' task for each ID and the destination
will be also hardcoded in this case.
If this is not the case, then I'd have a lookup table which contains the
ID/path as a pair of columns. You'll need to then iterate through that
table. Each iteration could call the package, sending in the 2 details as
global variables. These global variables will be used to modify the package
to run with the correct ID values. This is done through the Dynamic
Properties task, which would be mapped to the excel file path and the
parameter used for the stored proc.
I realize this sounds complicated, but it's actually quite easy to set up.
The main issue is whether the ID's are known in advance or not. If they are,
then it is quite simple.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||This table has IDs and Desc already in it. IDs might be duplicated and I am
trying to export the rows with the same IDs to a single excel file, so
expecting many excel files based on the distinct values of IDs. DTS seems
quite complicated to me since I have not done anything with that yet.
"Paul Ibison" wrote:

> Jim,
> is the ID list hardcoded or do new IDs appear? If it is hardcoded, then
> you'll have a single 'Transform data' task for each ID and the destination
> will be also hardcoded in this case.
> If this is not the case, then I'd have a lookup table which contains the
> ID/path as a pair of columns. You'll need to then iterate through that
> table. Each iteration could call the package, sending in the 2 details as
> global variables. These global variables will be used to modify the packag
e
> to run with the correct ID values. This is done through the Dynamic
> Properties task, which would be mapped to the excel file path and the
> parameter used for the stored proc.
> I realize this sounds complicated, but it's actually quite easy to set up.
> The main issue is whether the ID's are known in advance or not. If they ar
e,
> then it is quite simple.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
>|||JIM.H. wrote:
> This table has IDs and Desc already in it. IDs might be duplicated and I a
m
> trying to export the rows with the same IDs to a single excel file, so
> expecting many excel files based on the distinct values of IDs. DTS seems
> quite complicated to me since I have not done anything with that yet.
>
Jim, here is a link to a message thread that describes how to use a
template XLS file, some VBScript, and OPENROWSET to create a new Excel
document and export data to it, without using DTS. Might prove useful
to you:
http://www.sqlteam.com/forums/topic...926&whichpage=6
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Jim,
if you know all the IDs in advance, then you could hardcode these into the
DTS package as data flow tasks and this would be surprisingly easy (use the
export wizard to create the main template to see how it works), or if you
are more familiar with BCP, you could use a separate bcp commandline for
each excel file.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Great. Thanks Tracy.
"Tracy McKibben" wrote:

> JIM.H. wrote:
> Jim, here is a link to a message thread that describes how to use a
> template XLS file, some VBScript, and OPENROWSET to create a new Excel
> document and export data to it, without using DTS. Might prove useful
> to you:
> http://www.sqlteam.com/forums/topic...926&whichpage=6
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Certain rows to excel files

Hello,
I have a table T1 with ID and Desc fields. I want to export this table to
excel files based on the ID field. So my stored procedure select all the same
T1.ID and export it to ID.xls files. How can I do this?Jim,
is this a one-off? If so I'd use the import/export wizard. If it is a
regular process, I'd look at using a scheduled DTS package (you can use the
import/export one as a template for this). There are other options namely
BCP and OSQL which run from the command-line which might also be relevant
for you.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hi Paul,
This should be done once every day , so I am trying to automate it. How
should I develop my query and dts based on this criteria? Any example will be
greatly appreciated.
"Paul Ibison" wrote:
> Jim,
> is this a one-off? If so I'd use the import/export wizard. If it is a
> regular process, I'd look at using a scheduled DTS package (you can use the
> import/export one as a template for this). There are other options namely
> BCP and OSQL which run from the command-line which might also be relevant
> for you.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>|||Jim,
is the ID list hardcoded or do new IDs appear? If it is hardcoded, then
you'll have a single 'Transform data' task for each ID and the destination
will be also hardcoded in this case.
If this is not the case, then I'd have a lookup table which contains the
ID/path as a pair of columns. You'll need to then iterate through that
table. Each iteration could call the package, sending in the 2 details as
global variables. These global variables will be used to modify the package
to run with the correct ID values. This is done through the Dynamic
Properties task, which would be mapped to the excel file path and the
parameter used for the stored proc.
I realize this sounds complicated, but it's actually quite easy to set up.
The main issue is whether the ID's are known in advance or not. If they are,
then it is quite simple.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||This table has IDs and Desc already in it. IDs might be duplicated and I am
trying to export the rows with the same IDs to a single excel file, so
expecting many excel files based on the distinct values of IDs. DTS seems
quite complicated to me since I have not done anything with that yet.
"Paul Ibison" wrote:
> Jim,
> is the ID list hardcoded or do new IDs appear? If it is hardcoded, then
> you'll have a single 'Transform data' task for each ID and the destination
> will be also hardcoded in this case.
> If this is not the case, then I'd have a lookup table which contains the
> ID/path as a pair of columns. You'll need to then iterate through that
> table. Each iteration could call the package, sending in the 2 details as
> global variables. These global variables will be used to modify the package
> to run with the correct ID values. This is done through the Dynamic
> Properties task, which would be mapped to the excel file path and the
> parameter used for the stored proc.
> I realize this sounds complicated, but it's actually quite easy to set up.
> The main issue is whether the ID's are known in advance or not. If they are,
> then it is quite simple.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
>|||JIM.H. wrote:
> This table has IDs and Desc already in it. IDs might be duplicated and I am
> trying to export the rows with the same IDs to a single excel file, so
> expecting many excel files based on the distinct values of IDs. DTS seems
> quite complicated to me since I have not done anything with that yet.
>
Jim, here is a link to a message thread that describes how to use a
template XLS file, some VBScript, and OPENROWSET to create a new Excel
document and export data to it, without using DTS. Might prove useful
to you:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926&whichpage=6
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Jim,
if you know all the IDs in advance, then you could hardcode these into the
DTS package as data flow tasks and this would be surprisingly easy (use the
export wizard to create the main template to see how it works), or if you
are more familiar with BCP, you could use a separate bcp commandline for
each excel file.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Great. Thanks Tracy.
"Tracy McKibben" wrote:
> JIM.H. wrote:
> > This table has IDs and Desc already in it. IDs might be duplicated and I am
> > trying to export the rows with the same IDs to a single excel file, so
> > expecting many excel files based on the distinct values of IDs. DTS seems
> > quite complicated to me since I have not done anything with that yet.
> >
> Jim, here is a link to a message thread that describes how to use a
> template XLS file, some VBScript, and OPENROWSET to create a new Excel
> document and export data to it, without using DTS. Might prove useful
> to you:
> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926&whichpage=6
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Centralizing Two Stored Procedure Queries (one using Cursor Ou

Alejandro,
Sorry, one more question: In your example, would there be any way to return
only a subset of the fields in the stored proc query. For example, if you
wanted a temporary table with only the orderid field. Like this:
create table #t (orderid int)
insert into #t(orderid)
exec dbo.p1 @.sd, @.ed
I'll need it like this because my store proc "spTrucker" has many, many
fields, and I only need about 4 or 5 of them in the cursor.
Thanks again,
John
"Alejandro Mesa" wrote:
> John,
> You do not need a cursor output parameter. You can grab the result of the
sp
> "spTrucker" in sp "spDistinctCodes". See "insert into ... exec ..." in BOL
.
> Example:
> use northwind
> go
> create procedure dbo.p1
> @.sd datetime,
> @.ed datetime
> as
> set nocount on
> select
> orderid, orderdate, customerid
> from
> dbo.orders
> where
> orderdate >= convert(char(8), @.sd, 112)
> and orderdate < convert(char(8), dateadd(day, 1, @.ed), 112)
> return @.@.error
> go
> create procedure dbo.p2
> @.sd datetime,
> @.ed datetime
> as
> set nocount on
> declare @.orderid int
> declare @.orderdate varchar(25)
> declare @.customerid nchar(5)
> create table #t (orderid int, orderdate datetime, customerid nchar(5))
> insert into #t(orderid, orderdate, customerid)
> exec dbo.p1 @.sd, @.ed
> declare my_cursor cursor local fast_forward
> for
> select orderid, orderdate, customerid
> from #t
> order by orderdate
> open my_cursor
> while 1 = 1
> begin
> fetch next from my_cursor into @.orderid, @.orderdate, @.customerid
> if @.@.error != 0 or @.@.fetch_status != 0 break
> raiserror('%d %s %s', 10, 1, @.orderid, @.orderdate, @.customerid) with nowa
it
> end
> close my_cursor
> deallocate my_cursor
> go
> exec dbo.p2 '19970701', '19970731'
> go
> drop procedure p2, p1
> go
> How to share data between stored procedures
> http://www.sommarskog.se/share_data.html
>
> AMB
>
> "John Walker" wrote:
>John,
The ddl for the temporary table need to match all columns returned by the
sp. You can not grab just a subset.
AMB
"John Walker" wrote:
> Alejandro,
> Sorry, one more question: In your example, would there be any way to retu
rn
> only a subset of the fields in the stored proc query. For example, if you
> wanted a temporary table with only the orderid field. Like this:
> create table #t (orderid int)
> insert into #t(orderid)
> exec dbo.p1 @.sd, @.ed
> I'll need it like this because my store proc "spTrucker" has many, many
> fields, and I only need about 4 or 5 of them in the cursor.
> Thanks again,
> John
> "Alejandro Mesa" wrote:
>

Friday, February 24, 2012

central place for stored procedure variables

Hello,
I have numerous stored procedures with the following. Every time I make a
change I have to update all of my stored procedures. Is there a more
efficient way of doing this?
--Set Web Page Path
DECLARE
@.strWebDocPath_home varchar(300), @.strWebDocPath_aspx varchar(300),
@.strPhoto_home varchar(300), @.strPhoto_aspx varchar(300),
@.strRightArrow_home varchar(300), @.strRightArrow_aspx varchar(300),
--Set path for web pages
SET @.strWebDocPath_home = '<a href="http://links.10026.com/?link=web_doc/'
SET @.strWebDocPath_aspx = '<a href="http://links.10026.com/?link=../../web_doc/'
SET @.strPhoto_home = '<img src="http://pics.10026.com/?src=/library/images/photo/'
SET @.strPhoto_aspx = '<img src="http://pics.10026.com/?src=../../images/photo/'
SET @.strRightArrow_home = '<img src="http://pics.10026.com/?src=/library/images/icons/rightarrow.gif"
width="8" height="5">'
SET @.strRightArrow_aspx = '<img src="http://pics.10026.com/?src=../../images/icons/rightarrow.gif"
width="../../8" height="../../5">'
Thanks in advance,
sck10Why don't you put this data in a table?
David Portas
SQL Server MVP
--|||Depending on your overall system architecture, security requirements, system
complexity and maintenance provisions, you can either have the paths
represented as values in a table within the database, or an external config
file, or in rare instances in a registry or in .ini files etc.
Anith|||sck10 wrote:
> Hello,
> I have numerous stored procedures with the following. Every time I
> make a change I have to update all of my stored procedures. Is there
> a more efficient way of doing this?
>
> --Set Web Page Path
> DECLARE
> @.strWebDocPath_home varchar(300), @.strWebDocPath_aspx
> varchar(300), @.strPhoto_home varchar(300),
> @.strPhoto_aspx varchar(300), @.strRightArrow_home varchar(300),
> @.strRightArrow_aspx varchar(300),
> --Set path for web pages
> SET @.strWebDocPath_home = '<a href="http://links.10026.com/?link=web_doc/'
> SET @.strWebDocPath_aspx = '<a href="http://links.10026.com/?link=../../web_doc/'
> SET @.strPhoto_home = '<img src="http://pics.10026.com/?src=/library/images/photo/'
> SET @.strPhoto_aspx = '<img src="http://pics.10026.com/?src=../../images/photo/'
> SET @.strRightArrow_home = '<img src="http://pics.10026.com/?src=/library/images/icons/rightarrow.gif"
> width="8" height="5">'
> SET @.strRightArrow_aspx = '<img
> src="http://pics.10026.com/?src=../../images/icons/rightarrow.gif" width="../../8"
> height="../../5">'
Put the HTML snippets in a table and write a function to access the
table by string ID. For example:
Create Table HTMLSnippet (
HTMLID CHAR(15) NOT NULL PRIMARY KEY,
HTMLSnip VARCHAR(100),
HTMLDesc VARCHAR(255))
-- insert values into table
Insert Into HTMLSnippet Values ('DOCPATHHOME, '<a href="http://links.10026.com/?link=web_doc/', 'The
Doc path home')
-- etc
Create Function dbo.GetHTMLSnippet (
@.HTMLID CHAR(15) )
RETURNS VARCHAR(100)
AS
DECLARE @.HTMLCode VARCHAR(100)
SELECT @.HTMLCode = HTMLCode From HTMLSnippet Where @.HTMLID = @.HTMLID
RETURN @.HTMLCode
-- to use function
Declare @.test varchar(1000)
Select @.test = dbo.GetHTMLSnippet('DOCPATHHOME') + ...
Not tested, but should give you a good start.
David Gugick
Imceda Software
www.imceda.com|||I agree with David, put it into a table and then SELECT the values in your
SP's. Then if they change you only have to change them in the table on
time, all your SP's will be able to use the updated info appropriately...
"sck10" <sck10@.online.nospam> wrote in message
news:Oa2Oy0oUFHA.3140@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have numerous stored procedures with the following. Every time I make a
> change I have to update all of my stored procedures. Is there a more
> efficient way of doing this?
>
> --Set Web Page Path
> DECLARE
> @.strWebDocPath_home varchar(300), @.strWebDocPath_aspx varchar(300),
> @.strPhoto_home varchar(300), @.strPhoto_aspx varchar(300),
> @.strRightArrow_home varchar(300), @.strRightArrow_aspx
> varchar(300),
> --Set path for web pages
> SET @.strWebDocPath_home = '<a href="http://links.10026.com/?link=web_doc/'
> SET @.strWebDocPath_aspx = '<a href="http://links.10026.com/?link=../../web_doc/'
> SET @.strPhoto_home = '<img src="http://pics.10026.com/?src=/library/images/photo/'
> SET @.strPhoto_aspx = '<img src="http://pics.10026.com/?src=../../images/photo/'
> SET @.strRightArrow_home = '<img src="http://pics.10026.com/?src=/library/images/icons/rightarrow.gif"
> width="8" height="5">'
> SET @.strRightArrow_aspx = '<img src="http://pics.10026.com/?src=../../images/icons/rightarrow.gif"
> width="../../8" height="../../5">'
> --
> Thanks in advance,
> sck10
>|||One more vote for using SQL Server to store data! Alternatively you might
consider building something (or finding something) to do macro's for SQL
code to have this automatically done for you (it might be slightly faster to
do what you have done rather than a table, if you have ultra-high
performance needs:
For example in your code put:
--<start macro replace-name_of_macro>
--Set Web Page Path
DECLARE
@.strWebDocPath_home varchar(300), @.strWebDocPath_aspx varchar(300),
@.strPhoto_home varchar(300), @.strPhoto_aspx varchar(300),
@.strRightArrow_home varchar(300), @.strRightArrow_aspx varchar(300),
--Set path for web pages
SET @.strWebDocPath_home = '<a href="http://links.10026.com/?link=web_doc/'
SET @.strWebDocPath_aspx = '<a href="http://links.10026.com/?link=../../web_doc/'
SET @.strPhoto_home = '<img src="http://pics.10026.com/?src=/library/images/photo/'
SET @.strPhoto_aspx = '<img src="http://pics.10026.com/?src=../../images/photo/'
SET @.strRightArrow_home = '<img src="http://pics.10026.com/?src=/library/images/icons/rightarrow.gif"
width="8" height="5">'
SET @.strRightArrow_aspx = '<img src="http://pics.10026.com/?src=../../images/icons/rightarrow.gif"
width="../../8" height="../../5">'
--<end macro replace-name_of_macro>
Now it would be easy to go through all of the files with stored procedures
in them and do a find and replace for this stuff. I would probably
suggest just using a table and cross joining to it whenever you need these
values in a few cases to try first. In the end it will be a far better
solution than putting data into variables. SQL Server does not now, and
forseeable future will not have constants like this.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:Oa2Oy0oUFHA.3140@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have numerous stored procedures with the following. Every time I make a
> change I have to update all of my stored procedures. Is there a more
> efficient way of doing this?
>
> --Set Web Page Path
> DECLARE
> @.strWebDocPath_home varchar(300), @.strWebDocPath_aspx varchar(300),
> @.strPhoto_home varchar(300), @.strPhoto_aspx varchar(300),
> @.strRightArrow_home varchar(300), @.strRightArrow_aspx
> varchar(300),
> --Set path for web pages
> SET @.strWebDocPath_home = '<a href="http://links.10026.com/?link=web_doc/'
> SET @.strWebDocPath_aspx = '<a href="http://links.10026.com/?link=../../web_doc/'
> SET @.strPhoto_home = '<img src="http://pics.10026.com/?src=/library/images/photo/'
> SET @.strPhoto_aspx = '<img src="http://pics.10026.com/?src=../../images/photo/'
> SET @.strRightArrow_home = '<img src="http://pics.10026.com/?src=/library/images/icons/rightarrow.gif"
> width="8" height="5">'
> SET @.strRightArrow_aspx = '<img src="http://pics.10026.com/?src=../../images/icons/rightarrow.gif"
> width="../../8" height="../../5">'
> --
> Thanks in advance,
> sck10
>

Sunday, February 19, 2012

CDOSYS object not working

I am now trying to send email using CDOSYS from a stored procedure on a
Windows 2003 server and Sql Server 2000.
I got this code from the net and trying to figure out why it won't work.
It seems to work ok until I do the "send".
I am getting an error:
Source: CDO.Message.1
Description: The transport failed to connect to the server.
Is this the SMTP server or the code that is using the microsoft addresses
(ie. http://schemas.microsoft.com/cdo/co...ation/sendusing)?
My code is:
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%%%%%%%%%%%%%%%%
Declare @.From varchar(100) , @.To varchar(100) , @.Subject varchar(100), @.Body
varchar(4000)
/ ****************************************
**
This stored procedure takes the parameters and sends an e-mail. All the mail
configurations are hard-coded in the stored procedure. Comments are added to
the stored procedure where necessary. References to the CDOSYS objects are
at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/
library/en-us/cdosys/html/_cdosys_messaging.asp
****************************************
***/
Declare @.iMsg int
Declare @.hr int
Declare @.source varchar(255)
Declare @.description varchar(500)
Declare @.output varchar(1000)
Select @.From = 'tfs@.ftsolutions.com',@.To = 'tfs@.ftsolutions.com', @.Subject =
'Backup Notification', @.Body='The Body of the message'
--***** Create the CDO.Message Object *****
EXEC @.hr = sp_OACreate 'CDO.Message', @.iMsg OUT
print '@.hr after Creating CDO.Message'
print @.hr
--*****Configuring the Message Object *****
-- This is to configure a remote SMTP server.
--
http://msdn.microsoft.com/library/d...n_sendusing.asp
EXEC @.hr = sp_OASetProperty @.iMsg, 'Configuration.fields
("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
print '@.hr after the sendUsing line'
print @.hr
-- This is to configure the Server Name or IP address.
print 'Before the Smtp Server Setup'
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @.hr = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpse
rver").Value',
'smtp.earth.com'
print '@.hr After setting up the SMTP server'
print @.hr
-- Save the configurations to the message object.
EXEC @.hr = sp_OAMethod @.iMsg, 'Configuration.Fields.Update', null
print '@.hr after Update'
print @.hr
-- Set the e-mail parameters.
EXEC @.hr = sp_OASetProperty @.iMsg, 'To', @.To
print @.hr
EXEC @.hr = sp_OASetProperty @.iMsg, 'From', @.From
print @.hr
EXEC @.hr = sp_OASetProperty @.iMsg, 'Subject', @.Subject
print @.hr
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @.hr = sp_OASetProperty @.iMsg, 'TextBody', @.Body
print @.hr
EXEC @.hr = sp_OAMethod @.iMsg, 'Send', NULL
print @.hr
-- Sample error handling.
IF @.hr <>0
select @.hr
BEGIN
EXEC @.hr = sp_OAGetErrorInfo NULL, @.source OUT, @.description OUT
IF @.hr = 0
BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @.hr = sp_OADestroy @.iMsg
GO
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%%%%%%%%%%%%%%%%%%%%%%%%%%%
All the "print @.hr" lines are all showing 0, except the last one that is
showing: -2147220973.
Thanks,
TomHave you tried using xp_smtp_sendmail instead? I believe it's easier
to use than CDOSYS.
I found these links on Google Groups:
http://www.sqldev.net/xp/sp_smtp_sendmail.htm
http://www.aspfaq.com/2403
HTH
Barry|||"Barry" <barry.oconnor@.manx.net> wrote in message
news:1139940520.616802.88260@.g47g2000cwa.googlegroups.com...
> Have you tried using xp_smtp_sendmail instead? I believe it's easier
> to use than CDOSYS.
I did get it to work if I used the IP address instead of the name address.
I was going to look at sp_smtp_sendmail when Tibor mentioned it. But I did
get CDOSYS working. Is sendmail better?
Thanks,
Tom
> I found these links on Google Groups:
> http://www.sqldev.net/xp/sp_smtp_sendmail.htm
> http://www.aspfaq.com/2403
> HTH
> Barry
>|||Personally, I've never used it. I have read about it and seems quite
good.
I searched Google and I think general opinion is that it's easier to
use and has better scalability.
I guess it's a question of personal preference.
Barry|||I was reading about it also and found that the drawback was that you need an
email client to use it. CDOSYS doesn't need this.|||xp_smtp_sendmail doesn't need an email client. The old x_sendmail does.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ezp9AvZMGHA.1536@.TK2MSFTNGP11.phx.gbl...
>I was reading about it also and found that the drawback was that you need a
n email client to use
>it. CDOSYS doesn't need this.
>

Thursday, March 29, 2012

Change location of RS log files?

Tuesday, March 27, 2012

Change From when using xp_sendmail

Sunday, March 25, 2012

Change Format of Dates

Tuesday, March 20, 2012

Change Date Format from mm/dd/yyyy to dd/mm/yyyy

Monday, March 19, 2012

change data format in stored procedure

change current database in stored procedure

change current database in stored procedure

change current database in stored procedure

Change Control Application

Thursday, March 8, 2012

Change attribute value in stored procedure

Wednesday, March 7, 2012

Challenging Search Engine Like Stored Porcedure Needed

Chaging the Min and Max Scale of a Chart at run time

Chaging the Min and Max Scale of a Chart at run time

Saturday, February 25, 2012

Certain rows to excel files

certain rows to Excel files

Certain rows to excel files

Certain rows to excel files

Centralizing Two Stored Procedure Queries (one using Cursor Ou

Friday, February 24, 2012

central place for stored procedure variables

Sunday, February 19, 2012

CDOSYS object not working

Sql Case sensitivity

Blog Archive

About Me