"Storing an image into Sql Server Database "

Is there any way of storing an image file into a specific Table .
It would be of great help for me if i come to know something about it.One simple way is to store the physical path of the image

Madhivanan|||(santosh_stb@.indiatimes.com) writes:
> Is there any way of storing an image file into a specific Table .
> It would be of great help for me if i come to know something about it.

You can use the image datatype. You can also opt to store the image
in the file system and store only the path in the database as
Madhivanan suggested. This latter is simpler to implement, in the
short-term at least, but is less reliable since you have poorer
transaction scope, the file can more easily disappear.

Thus using image is more robust, but admittedly it takes more code to
come there.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

"non-trusted connection" what does it mean?

What does it mean when a users is connected but it is a "non-trusted connection". This is showing up in the log file after my users connect. How does this effect the system/user? We have been experiencing speed issues and I was wondering if this might be a possible source of the problem.

Thanks in advance.A "trusted connection" is made using Windoze authentication. A "non-trusted connection" is made using SQL authentication. That is is the only difference that I know of between the two kinds of connections.

-PatP

"Linked Table" from a .txt file in SQL Server?

OKay, I'm really, really good with MS Access, but am just getting up to speed
on SQL Server. Is there a SQL Server equivalent of linking (not importing) a
table from a .txt file?
Thanks!
Something similar would be to create a linked server to
access the text file. You use the Jet provider and set the
data source to the directory which has the text files you
want to access.
EXEC sp_addlinkedserver @.server ='TextLinkServer',
@.srvproduct='',
@.provider ='Microsoft.Jet.OLEDB.4.0',
@.datasrc='D:\',
@.provstr='Text'
You could then get a list of all text files in the directory
if you execute:
EXEC sp_tables_ex 'TextLinkServer'
You can access the files as listed in the directory. Use the
table_name listed when you execute sp_tables_ex which is the
text file name.
select *
from TextLinkServer...[YourFile#txt]
to access D:\YourFile.txt
-Sue
On Wed, 6 Apr 2005 16:05:04 -0700, "Joel"
<Joel@.discussions.microsoft.com> wrote:

>OKay, I'm really, really good with MS Access, but am just getting up to speed
>on SQL Server. Is there a SQL Server equivalent of linking (not importing) a
>table from a .txt file?
>Thanks!
|||Almost there...
The only other thing is that I'm trying to use a text file that exports from
SAP, which wants to export to the 'C:\Documents and
Settings\USERNAME\SapWorkDir' directory. When specifying a datasource, is
there a way to use a variable to put in the currently logged on user in place
of USERNAME?
Thanks!
"Sue Hoegemeier" wrote:

> Something similar would be to create a linked server to
> access the text file. You use the Jet provider and set the
> data source to the directory which has the text files you
> want to access.
> EXEC sp_addlinkedserver @.server ='TextLinkServer',
> @.srvproduct='',
> @.provider ='Microsoft.Jet.OLEDB.4.0',
> @.datasrc='D:\',
> @.provstr='Text'
> You could then get a list of all text files in the directory
> if you execute:
> EXEC sp_tables_ex 'TextLinkServer'
> You can access the files as listed in the directory. Use the
> table_name listed when you execute sp_tables_ex which is the
> text file name.
> select *
> from TextLinkServer...[YourFile#txt]
> to access D:\YourFile.txt
> -Sue
> On Wed, 6 Apr 2005 16:05:04 -0700, "Joel"
> <Joel@.discussions.microsoft.com> wrote:
>
>
|||If the data source is always changing and varies from user
to user, you may want to look at using Openrowset instead.
-Sue
On Thu, 7 Apr 2005 07:33:05 -0700, "Joel"
<Joel@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Almost there...
>The only other thing is that I'm trying to use a text file that exports from
>SAP, which wants to export to the 'C:\Documents and
>Settings\USERNAME\SapWorkDir' directory. When specifying a datasource, is
>there a way to use a variable to put in the currently logged on user in place
>of USERNAME?
>Thanks!
>"Sue Hoegemeier" wrote:

"Linked Table" from a .txt file in SQL Server?

OKay, I'm really, really good with MS Access, but am just getting up to spee
d
on SQL Server. Is there a SQL Server equivalent of linking (not importing) a
table from a .txt file?
Thanks!Something similar would be to create a linked server to
access the text file. You use the Jet provider and set the
data source to the directory which has the text files you
want to access.
EXEC sp_addlinkedserver @.server ='TextLinkServer',
@.srvproduct='',
@.provider ='Microsoft.Jet.OLEDB.4.0',
@.datasrc='D:',
@.provstr='Text'
You could then get a list of all text files in the directory
if you execute:
EXEC sp_tables_ex 'TextLinkServer'
You can access the files as listed in the directory. Use the
table_name listed when you execute sp_tables_ex which is the
text file name.
select *
from TextLinkServer...[YourFile#txt]
to access D:\YourFile.txt
-Sue
On Wed, 6 Apr 2005 16:05:04 -0700, "Joel"
<Joel@.discussions.microsoft.com> wrote:

>OKay, I'm really, really good with MS Access, but am just getting up to spe
ed
>on SQL Server. Is there a SQL Server equivalent of linking (not importing)
a
>table from a .txt file?
>Thanks!|||Almost there...
The only other thing is that I'm trying to use a text file that exports from
SAP, which wants to export to the 'C:\Documents and
Settings\USERNAME\SapWorkDir' directory. When specifying a datasource, is
there a way to use a variable to put in the currently logged on user in plac
e
of USERNAME?
Thanks!
"Sue Hoegemeier" wrote:

> Something similar would be to create a linked server to
> access the text file. You use the Jet provider and set the
> data source to the directory which has the text files you
> want to access.
> EXEC sp_addlinkedserver @.server ='TextLinkServer',
> @.srvproduct='',
> @.provider ='Microsoft.Jet.OLEDB.4.0',
> @.datasrc='D:',
> @.provstr='Text'
> You could then get a list of all text files in the directory
> if you execute:
> EXEC sp_tables_ex 'TextLinkServer'
> You can access the files as listed in the directory. Use the
> table_name listed when you execute sp_tables_ex which is the
> text file name.
> select *
> from TextLinkServer...[YourFile#txt]
> to access D:\YourFile.txt
> -Sue
> On Wed, 6 Apr 2005 16:05:04 -0700, "Joel"
> <Joel@.discussions.microsoft.com> wrote:
>
>|||If the data source is always changing and varies from user
to user, you may want to look at using Openrowset instead.
-Sue
On Thu, 7 Apr 2005 07:33:05 -0700, "Joel"
<Joel@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Almost there...
>The only other thing is that I'm trying to use a text file that exports fro
m
>SAP, which wants to export to the 'C:\Documents and
>Settings\USERNAME\SapWorkDir' directory. When specifying a datasource, is
>there a way to use a variable to put in the currently logged on user in pla
ce
>of USERNAME?
>Thanks!
>"Sue Hoegemeier" wrote:
>

"Ldf" file lose, and "Mdf" file is ok, can I still Attach database?

"Ldf" file lose, and "Mdf" file is ok, can I still Attach database?Colin
Have you last backup of your database?
sp_attach_single_file_db [ @.dbname = ] 'dbname'
, [ @.physname = ] 'physical_name'
"Colin Chen" <colin08@.21cn.com> wrote in message
news:#B206UYvDHA.1876@.TK2MSFTNGP09.phx.gbl...
> Help!!!
>|||Use sp_attach_single_file_db
It will create a new log...
In the future, start doing some database backups as well...You'll be better
off..
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Colin Chen" <colin08@.21cn.com> wrote in message
news:#B206UYvDHA.1876@.TK2MSFTNGP09.phx.gbl...
> Help!!!
>|||Just to add to the other posts:
SQL Server documentation states that you should be able to use
sp_attach_single_file_db and only attach the mdf file if the database
consists of only one data and one log file. And if you also really did
detach he database first. So, assuming that you didn't actually detached the
database first, there's a risk that you won't be able to attach it. In that
case, be prepared to open a case with Microsoft Support or do a restore from
the latest clean backup.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Colin Chen" <colin08@.21cn.com> wrote in message
news:%23B206UYvDHA.1876@.TK2MSFTNGP09.phx.gbl...
> Help!!!
>

"GO" statement in SQK2K

Hi,
I have been using a lot of "GO" statement in my SQL
script (say a file called abc.sql) with SQL7.0
For Example :
--
Select * from A
GO
Update A Set COL1 = Null
GO
Since I upgraded to SQL2K, I have go a SYNTAX
error.
Help Needed
Thanks
EJChewFrom where do you execute the SQL code? QA? Check configuration if someone changed the batch
separator. Can you post the exact errormessage?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"EJChew" <ejchew@.cyberoffice.com.sg> wrote in message news:3f94db83@.news.starhub.net.sg...
> Hi,
> I have been using a lot of "GO" statement in my SQL
> script (say a file called abc.sql) with SQL7.0
> For Example :
> --
> Select * from A
> GO
> Update A Set COL1 = Null
> GO
> Since I upgraded to SQL2K, I have go a SYNTAX
> error.
> Help Needed
> Thanks
> EJChew
>
>|||ejchew:
just a stab in the dark, but do you have the GO statments located on the
same line as another sql statement? this will result in an error
for example:
USE master
GO
SELECT name FROM dbo.sysobjects;
GO
SELECT id FROM dbo.sysobjects; GO
now try the following queries:
USE master
GO
SELECT name FROM dbo.sysobjects;
GO
SELECT id FROM dbo.sysobjects;
GO
hth
jeff clausius
sourcegear corporation
"EJChew" <ejchew@.cyberoffice.com.sg> wrote in news:3f94db83
@.news.starhub.net.sg:
> Hi,
> I have been using a lot of "GO" statement in my SQL
> script (say a file called abc.sql) with SQL7.0
> For Example :
> --
> Select * from A
> GO
> Update A Set COL1 = Null
> GO
> Since I upgraded to SQL2K, I have go a SYNTAX
> error.
> Help Needed
> Thanks
> EJChew
>
>

"Filling in the gaps" with a single-line query

Hi,
I've got the following scenario:
Files are being stored in a database, with a number of name-value
pairs associated with each file. This happens by storing the files in
one table (File), the list of property names in another table
(FileMetaDataSchema) and the values of properties in a third table
(FileMetaData), which references both File and FileMetaDataSchema.
The frontend of my application assumes there is a record for each
property of each file in the FileMetaData table, even if the value is
an empty string. In other words, if i have a list of 3 properties and
2 files, FileMetaData will contain 6 records.
Due to a bug in the system, this does not always happen. Suppose one
adds a new property and neglects to insert the "blank" records for the
new properties for all the files, or a new file is added, but the
associated meta-data records are not... (why and how this happens is
not the topic of discussion, so don't worry about that).
I have written a sql script to insert all the missing "blank" records,
but I feel it is very clumsy and intuitively, I just know there must
be a simpler way, my knowledge is just too limited. What it does is,
it iterates (using cursors) through all the files and all the
properties, checks if there is a record for each combination FileID
and FileMetaDataSchemaID and if not, it inserts one. I am looking for
a better way out of curiosity, for my own benefit.
Here's the script and thanks for any input:
DECLARE MetadataSchemaCursor CURSOR FOR
SELECT FileMetaDataSchemaID FROM FileMetaDataSchema
DECLARE @.FileMetaDataSchemaID INT,
@.FileID INT
OPEN MetadataSchemaCursor
FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
DECLARE FileCursor CURSOR FOR
SELECT FileID FROM [File]
OPEN FileCursor
FETCH NEXT FROM FileCursor INTO @.FileID
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
IF NOT EXISTS (SELECT 1 FROM FileMetaData WHERE FileID = @.FileID AND
FileMetaDataSchemaID = @.FileMetaDataSchemaID)
INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID,
PropertyValue) SELECT @.FileID, @.FileMetaDataSchemaID, ''
FETCH NEXT FROM FileCursor INTO @.FileID
END
CLOSE FileCursor
DEALLOCATE FileCursor
FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
END
CLOSE MetadataSchemaCursor
DEALLOCATE MetadataSchemaCursor>I just know there must
>be a simpler way, my knowledge is just too limited.
You are correct, there is a simpler way.
INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID, PropertyValue)
SELECT A.FileID, B.FileMetaDataSchemaID, ''
FROM [File] as A
CROSS
JOIN FileMetaDataSchema as B
WHERE NOT EXISTS
(select * from FileMetaData as X
where A.FileID = X.FileID
and B.FileMetaDataSchemaID = X.FileMetaDataSchemaID)
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 06:48:53 -0800, "Velislav" <vgebrev@.gmail.com> wrote:

>Hi,
>I've got the following scenario:
>Files are being stored in a database, with a number of name-value
>pairs associated with each file. This happens by storing the files in
>one table (File), the list of property names in another table
>(FileMetaDataSchema) and the values of properties in a third table
>(FileMetaData), which references both File and FileMetaDataSchema.
>The frontend of my application assumes there is a record for each
>property of each file in the FileMetaData table, even if the value is
>an empty string. In other words, if i have a list of 3 properties and
>2 files, FileMetaData will contain 6 records.
>Due to a bug in the system, this does not always happen. Suppose one
>adds a new property and neglects to insert the "blank" records for the
>new properties for all the files, or a new file is added, but the
>associated meta-data records are not... (why and how this happens is
>not the topic of discussion, so don't worry about that).
>I have written a sql script to insert all the missing "blank" records,
>but I feel it is very clumsy and intuitively, I just know there must
>be a simpler way, my knowledge is just too limited. What it does is,
>it iterates (using cursors) through all the files and all the
>properties, checks if there is a record for each combination FileID
>and FileMetaDataSchemaID and if not, it inserts one. I am looking for
>a better way out of curiosity, for my own benefit.
>Here's the script and thanks for any input:
>DECLARE MetadataSchemaCursor CURSOR FOR
> SELECT FileMetaDataSchemaID FROM FileMetaDataSchema
>DECLARE @.FileMetaDataSchemaID INT,
> @.FileID INT
>OPEN MetadataSchemaCursor
>FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
>WHILE (@.@.FETCH_STATUS = 0)
>BEGIN
> DECLARE FileCursor CURSOR FOR
> SELECT FileID FROM [File]
> OPEN FileCursor
> FETCH NEXT FROM FileCursor INTO @.FileID
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> IF NOT EXISTS (SELECT 1 FROM FileMetaData WHERE FileID = @.FileID AND
>FileMetaDataSchemaID = @.FileMetaDataSchemaID)
> INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID,
>PropertyValue) SELECT @.FileID, @.FileMetaDataSchemaID, ''
> FETCH NEXT FROM FileCursor INTO @.FileID
> END
> CLOSE FileCursor
> DEALLOCATE FileCursor
>FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
>END
>CLOSE MetadataSchemaCursor
>DEALLOCATE MetadataSchemaCursor|||On Feb 22, 5:42 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> You are correct, there is a simpler way.
> INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID, PropertyValue)
> SELECT A.FileID, B.FileMetaDataSchemaID, ''
> FROM [File] as A
> CROSS
> JOIN FileMetaDataSchema as B
> WHERE NOT EXISTS
> (select * from FileMetaData as X
> where A.FileID = X.FileID
> and B.FileMetaDataSchemaID = X.FileMetaDataSchemaID)
> Roy Harvey
> Beacon Falls, CT
>
Thank you
Note to self - look up cross joins.

"Filling in the gaps" with a single-line query

Hi,
I've got the following scenario:
Files are being stored in a database, with a number of name-value
pairs associated with each file. This happens by storing the files in
one table (File), the list of property names in another table
(FileMetaDataSchema) and the values of properties in a third table
(FileMetaData), which references both File and FileMetaDataSchema.
The frontend of my application assumes there is a record for each
property of each file in the FileMetaData table, even if the value is
an empty string. In other words, if i have a list of 3 properties and
2 files, FileMetaData will contain 6 records.
Due to a bug in the system, this does not always happen. Suppose one
adds a new property and neglects to insert the "blank" records for the
new properties for all the files, or a new file is added, but the
associated meta-data records are not... (why and how this happens is
not the topic of discussion, so don't worry about that).
I have written a sql script to insert all the missing "blank" records,
but I feel it is very clumsy and intuitively, I just know there must
be a simpler way, my knowledge is just too limited. What it does is,
it iterates (using cursors) through all the files and all the
properties, checks if there is a record for each combination FileID
and FileMetaDataSchemaID and if not, it inserts one. I am looking for
a better way out of curiosity, for my own benefit.
Here's the script and thanks for any input:
DECLARE MetadataSchemaCursor CURSOR FOR
SELECT FileMetaDataSchemaID FROM FileMetaDataSchema
DECLARE @.FileMetaDataSchemaID INT,
@.FileID INT
OPEN MetadataSchemaCursor
FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
DECLARE FileCursor CURSOR FOR
SELECT FileID FROM [File]
OPEN FileCursor
FETCH NEXT FROM FileCursor INTO @.FileID
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
IF NOT EXISTS (SELECT 1 FROM FileMetaData WHERE FileID = @.FileID AND
FileMetaDataSchemaID = @.FileMetaDataSchemaID)
INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID,
PropertyValue) SELECT @.FileID, @.FileMetaDataSchemaID, ''
FETCH NEXT FROM FileCursor INTO @.FileID
END
CLOSE FileCursor
DEALLOCATE FileCursor
FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
END
CLOSE MetadataSchemaCursor
DEALLOCATE MetadataSchemaCursor>I just know there must
>be a simpler way, my knowledge is just too limited.
You are correct, there is a simpler way.
INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID, PropertyValue)
SELECT A.FileID, B.FileMetaDataSchemaID, ''
FROM [File] as A
CROSS
JOIN FileMetaDataSchema as B
WHERE NOT EXISTS
(select * from FileMetaData as X
where A.FileID = X.FileID
and B.FileMetaDataSchemaID = X.FileMetaDataSchemaID)
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 06:48:53 -0800, "Velislav" <vgebrev@.gmail.com> wrote:
>Hi,
>I've got the following scenario:
>Files are being stored in a database, with a number of name-value
>pairs associated with each file. This happens by storing the files in
>one table (File), the list of property names in another table
>(FileMetaDataSchema) and the values of properties in a third table
>(FileMetaData), which references both File and FileMetaDataSchema.
>The frontend of my application assumes there is a record for each
>property of each file in the FileMetaData table, even if the value is
>an empty string. In other words, if i have a list of 3 properties and
>2 files, FileMetaData will contain 6 records.
>Due to a bug in the system, this does not always happen. Suppose one
>adds a new property and neglects to insert the "blank" records for the
>new properties for all the files, or a new file is added, but the
>associated meta-data records are not... (why and how this happens is
>not the topic of discussion, so don't worry about that).
>I have written a sql script to insert all the missing "blank" records,
>but I feel it is very clumsy and intuitively, I just know there must
>be a simpler way, my knowledge is just too limited. What it does is,
>it iterates (using cursors) through all the files and all the
>properties, checks if there is a record for each combination FileID
>and FileMetaDataSchemaID and if not, it inserts one. I am looking for
>a better way out of curiosity, for my own benefit.
>Here's the script and thanks for any input:
>DECLARE MetadataSchemaCursor CURSOR FOR
> SELECT FileMetaDataSchemaID FROM FileMetaDataSchema
>DECLARE @.FileMetaDataSchemaID INT,
> @.FileID INT
>OPEN MetadataSchemaCursor
>FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
>WHILE (@.@.FETCH_STATUS = 0)
>BEGIN
> DECLARE FileCursor CURSOR FOR
> SELECT FileID FROM [File]
> OPEN FileCursor
> FETCH NEXT FROM FileCursor INTO @.FileID
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> IF NOT EXISTS (SELECT 1 FROM FileMetaData WHERE FileID = @.FileID AND
>FileMetaDataSchemaID = @.FileMetaDataSchemaID)
> INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID,
>PropertyValue) SELECT @.FileID, @.FileMetaDataSchemaID, ''
> FETCH NEXT FROM FileCursor INTO @.FileID
> END
> CLOSE FileCursor
> DEALLOCATE FileCursor
>FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
>END
>CLOSE MetadataSchemaCursor
>DEALLOCATE MetadataSchemaCursor|||On Feb 22, 5:42 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> You are correct, there is a simpler way.
> INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID, PropertyValue)
> SELECT A.FileID, B.FileMetaDataSchemaID, ''
> FROM [File] as A
> CROSS
> JOIN FileMetaDataSchema as B
> WHERE NOT EXISTS
> (select * from FileMetaData as X
> where A.FileID = X.FileID
> and B.FileMetaDataSchemaID = X.FileMetaDataSchemaID)
> Roy Harvey
> Beacon Falls, CT
>
Thank you :)
Note to self - look up cross joins.

"Filling in the gaps" with a single-line query

Hi,
I've got the following scenario:
Files are being stored in a database, with a number of name-value
pairs associated with each file. This happens by storing the files in
one table (File), the list of property names in another table
(FileMetaDataSchema) and the values of properties in a third table
(FileMetaData), which references both File and FileMetaDataSchema.
The frontend of my application assumes there is a record for each
property of each file in the FileMetaData table, even if the value is
an empty string. In other words, if i have a list of 3 properties and
2 files, FileMetaData will contain 6 records.
Due to a bug in the system, this does not always happen. Suppose one
adds a new property and neglects to insert the "blank" records for the
new properties for all the files, or a new file is added, but the
associated meta-data records are not... (why and how this happens is
not the topic of discussion, so don't worry about that).
I have written a sql script to insert all the missing "blank" records,
but I feel it is very clumsy and intuitively, I just know there must
be a simpler way, my knowledge is just too limited. What it does is,
it iterates (using cursors) through all the files and all the
properties, checks if there is a record for each combination FileID
and FileMetaDataSchemaID and if not, it inserts one. I am looking for
a better way out of curiosity, for my own benefit.
Here's the script and thanks for any input:
DECLARE MetadataSchemaCursor CURSOR FOR
SELECT FileMetaDataSchemaID FROM FileMetaDataSchema
DECLARE @.FileMetaDataSchemaID INT,
@.FileID INT
OPEN MetadataSchemaCursor
FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
DECLARE FileCursor CURSOR FOR
SELECT FileID FROM [File]
OPEN FileCursor
FETCH NEXT FROM FileCursor INTO @.FileID
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
IF NOT EXISTS (SELECT 1 FROM FileMetaData WHERE FileID = @.FileID AND
FileMetaDataSchemaID = @.FileMetaDataSchemaID)
INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID,
PropertyValue) SELECT @.FileID, @.FileMetaDataSchemaID, ''
FETCH NEXT FROM FileCursor INTO @.FileID
END
CLOSE FileCursor
DEALLOCATE FileCursor
FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
END
CLOSE MetadataSchemaCursor
DEALLOCATE MetadataSchemaCursor
>I just know there must
>be a simpler way, my knowledge is just too limited.
You are correct, there is a simpler way.
INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID, PropertyValue)
SELECT A.FileID, B.FileMetaDataSchemaID, ''
FROM [File] as A
CROSS
JOIN FileMetaDataSchema as B
WHERE NOT EXISTS
(select * from FileMetaData as X
where A.FileID = X.FileID
and B.FileMetaDataSchemaID = X.FileMetaDataSchemaID)
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 06:48:53 -0800, "Velislav" <vgebrev@.gmail.com> wrote:

>Hi,
>I've got the following scenario:
>Files are being stored in a database, with a number of name-value
>pairs associated with each file. This happens by storing the files in
>one table (File), the list of property names in another table
>(FileMetaDataSchema) and the values of properties in a third table
>(FileMetaData), which references both File and FileMetaDataSchema.
>The frontend of my application assumes there is a record for each
>property of each file in the FileMetaData table, even if the value is
>an empty string. In other words, if i have a list of 3 properties and
>2 files, FileMetaData will contain 6 records.
>Due to a bug in the system, this does not always happen. Suppose one
>adds a new property and neglects to insert the "blank" records for the
>new properties for all the files, or a new file is added, but the
>associated meta-data records are not... (why and how this happens is
>not the topic of discussion, so don't worry about that).
>I have written a sql script to insert all the missing "blank" records,
>but I feel it is very clumsy and intuitively, I just know there must
>be a simpler way, my knowledge is just too limited. What it does is,
>it iterates (using cursors) through all the files and all the
>properties, checks if there is a record for each combination FileID
>and FileMetaDataSchemaID and if not, it inserts one. I am looking for
>a better way out of curiosity, for my own benefit.
>Here's the script and thanks for any input:
>DECLARE MetadataSchemaCursor CURSOR FOR
>SELECT FileMetaDataSchemaID FROM FileMetaDataSchema
>DECLARE @.FileMetaDataSchemaID INT,
>@.FileID INT
>OPEN MetadataSchemaCursor
>FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
>WHILE (@.@.FETCH_STATUS = 0)
>BEGIN
>DECLARE FileCursor CURSOR FOR
>SELECT FileID FROM [File]
>OPEN FileCursor
>FETCH NEXT FROM FileCursor INTO @.FileID
>WHILE (@.@.FETCH_STATUS = 0)
>BEGIN
>IF NOT EXISTS (SELECT 1 FROM FileMetaData WHERE FileID = @.FileID AND
>FileMetaDataSchemaID = @.FileMetaDataSchemaID)
>INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID,
>PropertyValue) SELECT @.FileID, @.FileMetaDataSchemaID, ''
>FETCH NEXT FROM FileCursor INTO @.FileID
>END
>CLOSE FileCursor
>DEALLOCATE FileCursor
>FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
>END
>CLOSE MetadataSchemaCursor
>DEALLOCATE MetadataSchemaCursor
|||On Feb 22, 5:42 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> You are correct, there is a simpler way.
> INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID, PropertyValue)
> SELECT A.FileID, B.FileMetaDataSchemaID, ''
> FROM [File] as A
> CROSS
> JOIN FileMetaDataSchema as B
> WHERE NOT EXISTS
> (select * from FileMetaData as X
> where A.FileID = X.FileID
> and B.FileMetaDataSchemaID = X.FileMetaDataSchemaID)
> Roy Harvey
> Beacon Falls, CT
>
Thank you
Note to self - look up cross joins.

"File not in a recognizeable format" when rendering to excel

I am using a WinXP SP2 machine with SQL2000 SP4, and using Reporting
Services SP2 when I get "File not in a recognizeable format" when
rendering to excel. Same goes for trying to render to PDF.
The weird thing is that on a virtual I have with Reporting Services
RTM, the excel opens fine...so I don't know what is going on.
The way I am generating the report is by manually creating a
request/response, adding on the POST vars, and then writing the output
to the browser (changing the content-type to excel, pdf, etc). As I
said, with the RTM version, the excel generates fine, but with SP2 it
doesnt.
Here are the parameters that I am adding onto the URL when I make the
request, along with my custom params.
//SQL REPORTING SERVICES SPECIAL PARAMS
sb.Append("&rs:Command=Render");
sb.Append("&rs:Format=" + exportType);
sb.Append("&rc:Parameters=false");
sb.Append("&rc:Toolbar=false&rs:Encoding=ASCII&rc:NoHeader=true");
I then I generate and return the request with the code below
(GeneratePostData() basically just creates the POST vars from above)
public static string generateReport(string url)
{
String result = "";
//get the data to post.
string postData = GeneratePostData();
System.IO.StreamWriter myWriter = null;
HttpWebRequest objRequest = (HttpWebRequest)WebRequest.Create(url);
objRequest.PreAuthenticate = true;
// Way 1:
//objRequest.Credentials = CredentialCache.DefaultCredentials;
// Way 2:
objRequest.Credentials = new
NetworkCredential(REPORT_USER,REPORT_PASS,REPORT_DOMAIN);
objRequest.Method = "POST";
objRequest.ContentLength = postData.Length;
objRequest.ContentType = "application/x-www-form-urlencoded";
try
{
myWriter = new
System.IO.StreamWriter(objRequest.GetRequestStream());
myWriter.Write(postData);
}
catch (Exception e)
{
return e.Message;
}
finally
{
myWriter.Close();
}
try
{
HttpWebResponse objResponse = (HttpWebResponse)objRequest.GetResponse();
using(System.IO.StreamReader sr = new
System.IO.StreamReader(objResponse.GetResponseStream()) )
{
result = sr.ReadToEnd();
// Close and clean up the StreamReader
sr.Close();
}
}
catch (Exception e)
{
return "An error occured while generating your report.\n" +
e.Message;
}
return result;
}
anyone have any ideas/things I can try because I am at a loss. Thanks
in advance.I figured out my problem.
I was taking the stream reader and passing the results back to the
output stream and writing it out just using Response.Write. This
worked fine for Reporting Svcs RTM, but NOT for SP2.
After going through various postings, I gather a few ways that others
did similar web request/response calls and found that if I just passed
the raw response stream and wrote it out with
Response.outputstream.write that it worked for BOTH RTM and SP2..
System.IO.Stream rs =ReportBuilder.generateReportStream(ReportBuilder.ReportViewerFullPath);
Byte[] read = new Byte[1024];
int count = rs.Read(read, 0, read.Length);
while(count > 0)
{
Response.OutputStream.Write(read, 0, count);
count = rs.Read(read, 0, count);
}

"File not created" from sp_trace_create

I am getting a return code of 12 (""File not created"") from sp_trace_create.

I have checked to ensure that the path is valid. I am using Windows Authentication and the Windows Administrator account.

SQL Server is using the Windows userid that I created for SQL Server. I have verified (at least tried to verify) that the user for SQL Server has access to the folder containing it.

The following is the relevant portion of what I am attempting.

DECLARE @.RC int, @.TraceId int
Exec @.RC = sp_trace_create @.TraceId OUTPUT, 0, N'C:\TraceFile'


I have tried other paths that also do not work.I used a path that was for a FAT partition and that worked, so it was a file permission problem. I don't know how to determine what path would be valid for the SQL Server account but that is a different question.

"File and Print Sharing for Microsoft Networks"

In Win 2k or above, how to check PC that is
installed "File and Print Sharing for Microsoft Networks"
by programming or Windows API?
hi,
"Allcomp" <fa097770@.nospam.skynet.be> ha scritto nel messaggio
news:40c41274$0$9536$a0ced6e1@.news.skynet.be...
> Hello,
> I am interested too, but I don't have a suscription. Do yuu have another
> solution too?
> Do you know if it it possible to install this service if it is not enabled
> on the computer (to allow a user to install MSDE without having to
> understand anything on his computer)?
unfortunately not...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

"Discard Saved Data on Open” in Crystal Report.

Hi,

I wanted to specify the option “Discard Saved Data on Open” in Crystal Report.
To do this, first I go to File > Options then select Reporting Tabpage.
Here I am trying to specify “Discard Saved Data on Open” option. After specify this option I saved the crystal report.
But when I open the report next time this option was unchecked.
How should we specify this option “Discard Saved Data on Open” permanenetly?

Regards

JummaHi,
Make sure that save data with report option is unchecked.

Madhivanan|||Hi Jumma

Use the below code. Trust same will help.

Set crystal = New CRAXDRT.Application 'MANAGES REPORTS
Set Report = crystal.OpenReport(a) 'OPEN OUR REPORT

// BELOW ONE LINE, YOU HV TO USE FOR YOUR REQUIREMENT \\
Report.DiscardSavedData

Report.Database.SetDataSource rs

"CREATE TABLE" for files *.dbf with a custom float column

How to create dbase file based on SQL-92 which have a FLOAT column like
float(14,2):
=> xxxxxxxxxxxxxx,xx <=
Thanks in advance,
PatriceN(14,2)
"news.microsoft.com" <reprotechnic@.wanadoo.fr> escribi en el mensaje
news:u37grkt#DHA.2072@.TK2MSFTNGP11.phx.gbl...
> How to create dbase file based on SQL-92 which have a FLOAT column like
> float(14,2):
> => xxxxxxxxxxxxxx,xx <=
> Thanks in advance,
> Patrice
>|||this solution doesn't run. it makes a float column of (20,4).
With more precision, i test this with odbc.net from Framework.NET v1.1
"Luis Camacho" <luibrac@.yahoo.com.ar> a crit dans le message de
news:%23VMvv5K$DHA.1036@.TK2MSFTNGP10.phx.gbl...
> N(14,2)
> "news.microsoft.com" <reprotechnic@.wanadoo.fr> escribi en el mensaje
> news:u37grkt#DHA.2072@.TK2MSFTNGP11.phx.gbl...
>|||N(14,2): This does not work and gives a syntax error on command.
AndreB.
"Luis Camacho" <luibrac@.yahoo.com.ar> a crit dans le message de
news:%23VMvv5K$DHA.1036@.TK2MSFTNGP10.phx.gbl...
> N(14,2)
> "news.microsoft.com" <reprotechnic@.wanadoo.fr> escribi en el mensaje
> news:u37grkt#DHA.2072@.TK2MSFTNGP11.phx.gbl...
>

"Best Practices" way to distribute MSDE

i have decided that when distributing MSDE2000 i want to use the Microsoft
MSI file (rather than using Merge Modules).
What is the (THE, the one) best way to:
1. Install MSDE?
- checking existing named instances
- use CreateProcess and wait? Launch msiexec directly?
2. Start MSDE engine?
- SQL DMO?
- install DMO?
3. Put my database into MSDE?
- restore empty database?
- attach empty database?
- scripts?
- what about creating logins?
- what about fixing users-logins mappings?
- using TSQL?
- using DMO?
4. Backup my database once it's in production?
- TSQL?
- DMO?
5. Restore a database once it's in production?
- TSQL?
- DMO?
6. If they have to re-install MSDE, and they then restore a database, how to
re-create the logins? How do i catch that it has happened and i need to
re-map logins to users?
A quick search of Microsoft reveals about 20 different methods for
accomplishing the same things.
i want to know which one is the right one.
Personally, I've always used DMO.
On Tue, 6 Jul 2004 13:21:25 -0400, Ian Boyd wrote:

> i have decided that when distributing MSDE2000 i want to use the Microsoft
> MSI file (rather than using Merge Modules).
> What is the (THE, the one) best way to:
> 1. Install MSDE?
> - checking existing named instances
> - use CreateProcess and wait? Launch msiexec directly?
> 2. Start MSDE engine?
> - SQL DMO?
> - install DMO?
> 3. Put my database into MSDE?
> - restore empty database?
> - attach empty database?
> - scripts?
> - what about creating logins?
> - what about fixing users-logins mappings?
> - using TSQL?
> - using DMO?
>
> 4. Backup my database once it's in production?
> - TSQL?
> - DMO?
> 5. Restore a database once it's in production?
> - TSQL?
> - DMO?
> 6. If they have to re-install MSDE, and they then restore a database, how to
> re-create the logins? How do i catch that it has happened and i need to
> re-map logins to users?
>
> A quick search of Microsoft reveals about 20 different methods for
> accomplishing the same things.
> i want to know which one is the right one.
|||hi Paul, Ian,
"Paul Buxton" <psb@.NOSPAMspireite.demon.co.uk> ha scritto nel messaggio
news:knuxcy7j42cd.f6k90yoseg6f.dlg@.40tude.net...
> Personally, I've always used DMO.
>
me too, but do not attach database(s)...
instead I do execute DDL sql scripts to create objects and pre-load them...
I actually use BCP too to preload heavy populated objects...
actually, running DDL sql scripts, as long as INSERT INTO scripts can be
done via Ado/Ado.Net too...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
All depends on what data you have to put in. In my instance, a BCP of all
the data we ship does comes up to a little under a gig - for me shipping
the mdf/ldf and attaching is far more preferable
|||How do you determine if an SQL Server is already installed?
If so, how do you detect if your database is already installed?
How do you detect if you login is already created?
If you login is not already created, but your database is, how do you relink
them?
If you login is already there, but the database isn't how do you handle it?
etc etc
i think i'm beginning why Microsoft didn't write an MSDE installer that
could just install MSDE...
"Paul Buxton" <psb@.NOSPAMspireite.demon.co.uk> wrote in message
news:knuxcy7j42cd.f6k90yoseg6f.dlg@.40tude.net...[vbcol=seagreen]
> Personally, I've always used DMO.
>
> On Tue, 6 Jul 2004 13:21:25 -0400, Ian Boyd wrote:
Microsoft[vbcol=seagreen]
how to[vbcol=seagreen]
|||hi Ian,
"Ian Boyd" <admin@.SWIFTPA.NET> ha scritto nel messaggio
news:%23fiy0DIZEHA.2516@.TK2MSFTNGP10.phx.gbl...
> How do you determine if an SQL Server is already installed?
you can (locally) check the registry, instantiate SQL-DMO (which only tells
if SQL-DMO is locally present) and try a connection to the server... this
can be done via Ado/Ado.Net too...

> If so, how do you detect if your database is already installed?
query the database catalogue for it's name... SQL-DMO/Ado/Ado.Net

> How do you detect if you login is already created?
if you can't connect with that login, it's not there... or SQL-DMO or
sp_helplogins 'login2test' via Ado/Ado.Net

> If you login is not already created, but your database is, how do you
relink
> them?
strange situation, isn't it? =;-D
eventually, just re-add your login and grant db access, as long as all
object privileges... SQL-DMO/Ado/Ado.Net

> If you login is already there, but the database isn't how do you handle
it?
you just create the database... and then go on granting db access and object
privileges... SQL-DMO/Ado/Ado.Net
just use traditional common sense =;-)
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Now is there any Microsoft document that describes this typical situation of
a client's computer was re-installed, and they need their data back...
What is the best practice to do this?
Ways to put a database into a server:
Attach my empty db
Restore my empty db
Run scripts to create my empty db
Restore a users's selected backup.
Attach a users db from a previous install.
Is there a concensus on an interface, API, and routines to perform these
things are automatically as possible?
Remember, if i'm supposed to be using MSDE now instead of Jet: Jet was very
easy for the client to backup and restore. They copied the file when they
wanted a backup, and they put it into the folder when they wanted to
restore.
What is the MSDE equivalent procedures to accomplish the same task as
seamlessly for the end user without any vendor intervention or hand-holding?
What are the "Best Practices" way to distribute MSDE?
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2l3qjaF8auodU1@.uni-berlin.de...
> hi Ian,
> "Ian Boyd" <admin@.SWIFTPA.NET> ha scritto nel messaggio
> news:%23fiy0DIZEHA.2516@.TK2MSFTNGP10.phx.gbl...
> you can (locally) check the registry, instantiate SQL-DMO (which only
tells
> if SQL-DMO is locally present) and try a connection to the server... this
> can be done via Ado/Ado.Net too...
>
> query the database catalogue for it's name... SQL-DMO/Ado/Ado.Net
>
> if you can't connect with that login, it's not there... or SQL-DMO or
> sp_helplogins 'login2test' via Ado/Ado.Net
> relink
> strange situation, isn't it? =;-D
> eventually, just re-add your login and grant db access, as long as all
> object privileges... SQL-DMO/Ado/Ado.Net
> it?
> you just create the database... and then go on granting db access and
object
> privileges... SQL-DMO/Ado/Ado.Net
> just use traditional common sense =;-)
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Ian,
"Ian Boyd" <admin@.SWIFTPA.NET> ha scritto nel messaggio
news:e1pXkyVZEHA.2216@.TK2MSFTNGP10.phx.gbl...
> Now is there any Microsoft document that describes this typical situation
of
> a client's computer was re-installed, and they need their data back...
> What is the best practice to do this?
> Ways to put a database into a server:
> Attach my empty db
> Restore my empty db
> Run scripts to create my empty db
> Restore a users's selected backup.
> Attach a users db from a previous install.
as MSDE is distributed without management tools but oSql.Exe, the guidlines
I've found are in
http://support.microsoft.com/default...N-US;q325003..
in my understanding, you, as an ISV, are supposed to provide all the
functionnalities to support and manage your database(s) inside your
application(s) and/or with companion tools (thrid party or home built)
so you have to provide backup/restore functionalities, login/users
management and so on...
as regards database(s) creation, I do personally provides Transact-SQL
scripts, but attaching a shipped .mdf + .ldf solution is viable too, and is
often recommended as the simplest solution, but I do personally don't like
it, perhaps for a "purist" point of view...
anyway... for the sake of simplicity, you can use the method you better
like, as, for instance, Red-Gate new Packager tool
(http://www.red-gate.com/sql/sql_packager.htm), which provides a single file
installer for your database(s)...

> Is there a concensus on an interface, API, and routines to perform these
> things are automatically as possible?
for dayly house-keeping I do usually provide scripted job for database
backup, that rely on the SQL Server Agent, but my apps all feature (not
scheduled) backup functionalities, as long as login/user management..
database restore shoul'd usually not be a daly operation [=;-D ] , but is
provided as well... one shot operation...
again, personally I choose SQL-DMO API becouse I already provided it's
dependencies for my companion database general management tool, but
ADO/ADO.Net are good as well.. it all depends on your needs/API
skill/preferred method...

> Remember, if i'm supposed to be using MSDE now instead of Jet: Jet was
very
> easy for the client to backup and restore. They copied the file when they
> wanted a backup, and they put it into the folder when they wanted to
> restore.
>
actually you can even choose this solution, as long as your database all
have the 'Auto close' property set... this option will actually close (and
free) the physical database and transaction log files on the file system, so
that file copy operation (not SQL Server backup!) can be performed.. please
keep in mind connection pool time, which usually requires about 1 minute to
effectively close...
but, again... my personala advice is to perform dayly house-keeping actions
withour user's intervention.. that's to say: do what your users usually
skip... automatically backup their data ...
one draw back... SQLExpress, the replacement for MSDE in the SQL Server 2005
code base, will remove SQL Server Agent from the SKU, so keep in mind this
for the future.. personally I've still have to choose a scheduled
alternative, but I'll provide it for sure.
remember, you are supposed to provide all the manage/support
functionnalities

> What is the MSDE equivalent procedures to accomplish the same task as
> seamlessly for the end user without any vendor intervention or
hand-holding?
as MSDE is NOT Jet, you have a lot of extra features at a resonably price
[=;-D], but you have to take some new sagacity in your work...
nowdays, all our users do have some Access skill [potentially dangerous
=;-D ], so you shoul'd even train your users a little more, but this is
another story...

> What are the "Best Practices" way to distribute MSDE?
Microsoft provides ton of information as
http://msdn.microsoft.com/library/de...eddingmsde.asp
http://www.microsoft.com/sql/techinf...swithmsdes.asp
http://www.microsoft.com/sql/msde/te...ntegration.asp
http://msdn.microsoft.com/library/de...stsql_7b91.asp
....
personally I do provide a separate setup for MSDE, with a home built user
interface that takes care and handles all required parameter to then shell
to the setup.exe boostrap installer...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thank you all for you input and feedback;
and tolerating my agressive, insulting, condescending, patronizing writing
style - to everyone in every post i've ever done.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2l7qo3F9s4e1U1@.uni-berlin.de...[vbcol=seagreen]
> hi Ian,
> "Ian Boyd" <admin@.SWIFTPA.NET> ha scritto nel messaggio
> news:e1pXkyVZEHA.2216@.TK2MSFTNGP10.phx.gbl...
situation
> of
> as MSDE is distributed without management tools but oSql.Exe, the
guidlines
> I've found are in
> http://support.microsoft.com/default...N-US;q325003..
> in my understanding, you, as an ISV, are supposed to provide all the
> functionnalities to support and manage your database(s) inside your
> application(s) and/or with companion tools (thrid party or home built)
> so you have to provide backup/restore functionalities, login/users
> management and so on...
> as regards database(s) creation, I do personally provides Transact-SQL
> scripts, but attaching a shipped .mdf + .ldf solution is viable too, and
is
> often recommended as the simplest solution, but I do personally don't like
> it, perhaps for a "purist" point of view...
> anyway... for the sake of simplicity, you can use the method you better
> like, as, for instance, Red-Gate new Packager tool
> (http://www.red-gate.com/sql/sql_packager.htm), which provides a single
file[vbcol=seagreen]
> installer for your database(s)...
>
> for dayly house-keeping I do usually provide scripted job for database
> backup, that rely on the SQL Server Agent, but my apps all feature (not
> scheduled) backup functionalities, as long as login/user management..
> database restore shoul'd usually not be a daly operation [=;-D ] , but is
> provided as well... one shot operation...
> again, personally I choose SQL-DMO API becouse I already provided it's
> dependencies for my companion database general management tool, but
> ADO/ADO.Net are good as well.. it all depends on your needs/API
> skill/preferred method...
> very
they
> actually you can even choose this solution, as long as your database all
> have the 'Auto close' property set... this option will actually close (and
> free) the physical database and transaction log files on the file system,
so
> that file copy operation (not SQL Server backup!) can be performed..
please
> keep in mind connection pool time, which usually requires about 1 minute
to
> effectively close...
> but, again... my personala advice is to perform dayly house-keeping
actions
> withour user's intervention.. that's to say: do what your users usually
> skip... automatically backup their data ...
> one draw back... SQLExpress, the replacement for MSDE in the SQL Server
2005
> code base, will remove SQL Server Agent from the SKU, so keep in mind this
> for the future.. personally I've still have to choose a scheduled
> alternative, but I'll provide it for sure.
> remember, you are supposed to provide all the manage/support
> functionnalities
> hand-holding?
> as MSDE is NOT Jet, you have a lot of extra features at a resonably price
> [=;-D], but you have to take some new sagacity in your work...
> nowdays, all our users do have some Access skill [potentially dangerous
> =;-D ], so you shoul'd even train your users a little more, but this is
> another story...
>
> Microsoft provides ton of information as
>
http://msdn.microsoft.com/library/de...eddingmsde.asp
>
http://www.microsoft.com/sql/techinf...swithmsdes.asp
> http://www.microsoft.com/sql/msde/te...ntegration.asp
>
http://msdn.microsoft.com/library/de...stsql_7b91.asp
> ...
> personally I do provide a separate setup for MSDE, with a home built user
> interface that takes care and handles all required parameter to then shell
> to the setup.exe boostrap installer...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Let's just focus on how to determine if i should even install MSDE in the
first place.
1. How do i check if SQL Server 2000/MSDE2000 is already installed?
- i've read 3 about 3 different registry keys, as well as checking a
process list, and checking the services list, as well as using SQL DMO.
What if SQLDMO is not registered (or registered properly)? Do i install it
first?
i can't just look for a process, because SQL may be stopped
i can't just look at the services list, because it may be disabled. Also, i
probably can't use that instance, since it's server settings can be
different than what i require. Or it may be a trial version that will be
expiring tomorrow.
i could just always blindly install a new instance. But what if an existing
instance name conflicts with mine? What if the conflicting instance name is
actually an instance i installed last time? How do i know it's me or a
co-incidentally named instance from some other vendor? What if i'm the
client machine is out of instances, how do i handle it with a zero-user
interaction method (since the user will not have the client tools; and no
idea how to deal with it).
What if there is an existing instance, and it's mine, but noboby knows the
sa password? Do i install a fresh instance because an existing instance is
locked out?
All these questions that need to be dealt with when someone actually has to
integrate MSDE.
Compare that to Jet: put a file in my %ApplicationData% folder. And Jet is
guaranteed to be installed on any OS that Microsoft still supports.

"backup log {Database_Name} with no_log" issue

I issue the "backup log {Database_Name} with no_log" and
also "Dump transaction {Datebase_Name} with no_log". The
LDF file still got the same size. Any idea?It's usually best to leave the size of the .ldf file as is after backing up
a log because this saves sql server from having to go through the effort of
manually growing it whilst the database is in operation. This is because
having to grow the log file during operation slows down the performance of
SQL Server. This is why most production environments leave the .ldf file as
it is & simply truncate the "logical" log records from the file. There are
also other factors that come into play when considering recovery times as
well.
So, backing up a log & shrinking a .ldf file aren't two things you should
expect to happen automatically.
If you really want to shrink the .ldf file for some reason, there is a good
article on the topic in SQL Server Books Online here:
http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_1uzr.asp
HTH
Regards,
Greg Linwood
SQL Server MVP
"KL" <anonymous@.discussions.microsoft.com> wrote in message
news:034801c3c744$e685fc70$a101280a@.phx.gbl...
> I issue the "backup log {Database_Name} with no_log" and
> also "Dump transaction {Datebase_Name} with no_log". The
> LDF file still got the same size. Any idea?
>|||Just to add to Greg's comments you should note that backingup a log file
will not shrink it. That will only happen with a DBCC SHRINKDATABASE or
SHRINKFILE command. It may not be able to actually shrink until the backup
is completed but that alone does not shrink the log file.
--
Andrew J. Kelly SQL MVP
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:%23LGr8z2xDHA.1616@.TK2MSFTNGP11.phx.gbl...
> It's usually best to leave the size of the .ldf file as is after backing
up
> a log because this saves sql server from having to go through the effort
of
> manually growing it whilst the database is in operation. This is because
> having to grow the log file during operation slows down the performance of
> SQL Server. This is why most production environments leave the .ldf file
as
> it is & simply truncate the "logical" log records from the file. There are
> also other factors that come into play when considering recovery times as
> well.
> So, backing up a log & shrinking a .ldf file aren't two things you should
> expect to happen automatically.
> If you really want to shrink the .ldf file for some reason, there is a
good
> article on the topic in SQL Server Books Online here:
> http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_1uzr.asp
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "KL" <anonymous@.discussions.microsoft.com> wrote in message
> news:034801c3c744$e685fc70$a101280a@.phx.gbl...
> > I issue the "backup log {Database_Name} with no_log" and
> > also "Dump transaction {Datebase_Name} with no_log". The
> > LDF file still got the same size. Any idea?
> >
>