How to save pdf files to SQL database?

Hi all,

Can someone please brief me on how to save 'pdf' files(there are about 15 files) into a SQL database and retrieve/open it from a datagrid?

I have about 15 pdf files on my webserver and need to save it in the SQL database, so every pdf file saved in the database will have a primary key and the file will be saved either as 'ntext' or 'binary' type...I just have this rough idea.

If someone has a ready code in VB.net then nothing better( i know thats a shortcut :) but it will help me like a tutorial. C# code will do too, I can then convert the code to Vb.net

Thanks a ton.

OFF:

Do you really need to save 'pdf' files into a database? Wouldn't it be better to save 'pdf' files in the filesystem and save their path in the database? I think the performance in this case would be better.|||Hi there,
Firstly thanks a lot for the reply. actually thats what my manager wants me to do...but I agree with you about the file system and saving it's path, I am sure it will also reduce the complexity of code behind.I already have the pdf files on the web server so probably saving the path will work well...in any case if you have a ready solution for doing this or if you can help me with how to go about it, I can handle it further on.
thankyou once again, your help is appreciated.

how to save or retrieve a picture from a table in sql server 2000

hi ,
am wondering how to save or retrieve an image in sql server 2000 table
using vb application or access thx
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi
http://support.microsoft.com/defaul...kb;en-us;153238
http://support.microsoft.com/defaul...kb;en-us;194975
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Joe Saliba" <josephs73@.hotmail.com> wrote in message
news:OWE7fj3HFHA.3472@.TK2MSFTNGP09.phx.gbl...
> hi ,
> am wondering how to save or retrieve an image in sql server 2000 table
> using vb application or access thx
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||You just "select " the colname from the table the same way you would any
other column data. On the client side, you need t otreat the output as a
byte array, and "eed" it to whatever it is being used in ... The techniques
for that part are dependant on what and how it is being used, and the
technology you are doing it in (VB6, VB/C#.Net, ASP, HTML, etc. etc. )
"Joe Saliba" wrote:

> hi ,
> am wondering how to save or retrieve an image in sql server 2000 table
> using vb application or access thx
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>

How to save a file in SQL Server

I have the need to allow a user to upload a file via the web and then save it into a database. THen I also need to be able to retrieve the file for download.

Can someone give me guidance as to code or a sample on how to do this.

Thanks.the sql column is usually set to a type of "image" as this holds binary data.
the file gets uploaded to the webserver and saved to a temp folder.
iv seen samples in the forum where the file is read into a byte array and then from the byte array, is put into the db via a stored proc.|||Look at BLOB, although it's normally better not to store binary data in your DB.

How to return specific Row in sql server

Hi,
Suppose I have 100 rows in my table, I want to retrieve say row no 5(I just want any thing on row no 5), I don't want to use identity column. Is there any other way by which I can retrieve specified row.
Thanks,
Sajidin relational databases, rows do not have a position -- there is no "5th row in the table" because the rows are not stored in sequence

the only way to get a specific row is to select it based on the value of one or more of its columns

if you sort the rows of the table into a specific sequence based on the values of one or more columns, then yes, you can get the 5th row

so, tell me what columns you want to sort the table on, and i'll show you how to get the 5th row in that sequence

rudy
http://r937.com/|||Originally posted by r937
in relational databases, rows do not have a position -- there is no "5th row in the table" because the rows are not stored in sequence

the only way to get a specific row is to select it based on the value of one or more of its columns

if you sort the rows of the table into a specific sequence based on the values of one or more columns, then yes, you can get the 5th row

so, tell me what columns you want to sort the table on, and i'll show you how to get the 5th row in that sequence

rudy
http://r937.com/

Thanks for answering my question.

Say suppose I have Name column and i want the 5th record on name column.

Thanks
Sajid|||Hi

U are asking how to select a row without having to specify anything unique about that row other than it's Ordinal position within the table.

Are U sure thats what U want

Are U Saying you want to select a row based on it's position within a table?

Ooops r937 U posted whilst I was Posting - I'll Post anyway

GW|||Originally posted by GWilliy
Hi

U are asking how to select a row without having to specify anything unique about that row other than it's Ordinal position within the table.

Are U sure thats what U want

Are U Saying you want to select a row based on it's position within a table?

Ooops r937 U posted whilst I was Posting - I'll Post anyway

GW

Yes I need the same.|||select top 1
from (
select top 5
Name
from yourtable
order
by Name
)
order
by Name desc

rudy|||Originally posted by r937
select top 1
from (
select top 5
Name
from yourtable
order
by Name
)
order
by Name desc

rudy

Thanks, It works.

If suppose i want to retrieve all columns, then do I need to specific each column.|||U could actualy do a sraight select into a cursor & then go straight to the Row

DECLARE @.a VarChar(20),@.b,@.c

DECLARE myCursor CURSOR SCROLL
FOR
SELECT a,b,c FROM myTable
OPEN myCursor
FETCH ABSOLUTE 5 INTO @.a,@.b,@.c
BEGIN
PRINT @.a
PRINT @.b
PRINT @.c
END
CLOSE myCursor
DEALLOCATE myCursor

example Code to give U an Idea - There are many options available when declaring Cursors etc.

May be more suitable for whatever Sajidrep is Doing

GW

How to return large amount of data in the XML format

I have SQL 2000 and need to retrieve fairly large amout of data (~
50.000 characters) in XML format and then insert it into the field of
the text type.
As 'FOR XML' can't be used with either local variables, INSERT INTO or
SELECT INTO this makes "XML support" quite useless in many aspects.

Can anyone please help me in solving this.
Thanks a lot for your help and time.

PavelPavel (p.golobokov@.ausbulk.com.au) writes:
> I have SQL 2000 and need to retrieve fairly large amout of data (~
> 50.000 characters) in XML format and then insert it into the field of
> the text type.
> As 'FOR XML' can't be used with either local variables, INSERT INTO or
> SELECT INTO this makes "XML support" quite useless in many aspects.

You can try:

INSERT tbl (xmlcol)
SELECT * FROM OPENQUERY(LOCALSVR, 'SELECT ... FOR XML')

Where LOCALSVR has been created as

EXEC sp_addlinkedserver
@.server = 'LOCALVR',
@.srvproduct = '',
@.provider = 'MSDASQL',
@.datasrc = 'LocalServer'

That is, you use the deprecated OLE DB over ODBC provider. This works
so far that you get XML back. However, you may find that the text
has been broken into many rows. (If you would use SQLOLEDB, the real
SQL Server provider, you get a blob back.)

If this does not work out, you will have a find a client to pick up the
XML and send it back.

In SQL 2005, the XML support is considerably enhanced, and you should
be able to do this without weird workarounds.

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

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

how to return event description from stored proceedure?

I am using C# to insert the form details and passing event id (numeric) to the same stored procedure in my eror handler and need to retrieve the description from event_db to display in MessageBox..

can the stored proceedure send the text?

Could you describe this abit in detail and send your DDL over ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

How to retrieve xml element name in sql server 2005?

Hi
Here's a simple example of what I'm trying to achieve:
declare @.xmlNode xml
set @.xmlNode='
<a id="1">
<b id="2" />
<c id="3" />
</a>'
select @.xmlNode.query('//*[@.id="1"]/name()') -- fails since name()
function not allowed
I need to be able to return the name of an element for a particular id,
so the above example should return the value 'a'. If the search were
for id="3" the the result should be 'c'.
However, it seems that the name() function is not (yet?) supported in
sql server 2005. I've looked into local-name() but that's only
supported in a predicate/selector so I can't retrieve an actual value
from it.
I've also looked into the possibility of using the mp:localname
metaproperty in openxml but it doesn't seem like the right solution
since it should be possible to accomplish from within xquery.
It's probably staring me in the face, but I just can't seem to find the
solution.
Any help very much appreciated.
Regards, JamesCorrect syntax of name() and local-name() is,
name(xpath)
not,
xpath/name()
Yes, name() is not yet supported. Use local-name().
Additionally, xpath2.0 or xquery1.0 is occurence sensitive. So called
singleton is required for the local-name(). This is an error.
local-name(//*[@.id="1"])
Do instead,
local-name((//*[@.id="1"])[1])
Pohwan Han. Seoul. Have a nice day.
"jamesagnew" <jamesagnew@.hotmail.com> wrote in message
news:1125962687.996958.18470@.o13g2000cwo.googlegroups.com...
> Hi
> Here's a simple example of what I'm trying to achieve:
> --
> declare @.xmlNode xml
> set @.xmlNode='
> <a id="1">
> <b id="2" />
> <c id="3" />
> </a>'
> select @.xmlNode.query('//*[@.id="1"]/name()') -- fails since name()
> function not allowed
> --
> I need to be able to return the name of an element for a particular id,
> so the above example should return the value 'a'. If the search were
> for id="3" the the result should be 'c'.
> However, it seems that the name() function is not (yet?) supported in
> sql server 2005. I've looked into local-name() but that's only
> supported in a predicate/selector so I can't retrieve an actual value
> from it.
> I've also looked into the possibility of using the mp:localname
> metaproperty in openxml but it doesn't seem like the right solution
> since it should be possible to accomplish from within xquery.
> It's probably staring me in the face, but I just can't seem to find the
> solution.
> Any help very much appreciated.
> Regards, James
>|||That's it! I've got it working as follows:
select @.xmlNode.value('local-name((//*[@.id="1"])[1])','nvarchar(55)')
Han, you're a great asset to this list and your help is much
appreciated.
Thanks again, James

How to retrieve xml element name in sql server 2005?

Hi
Here's a simple example of what I'm trying to achieve:
declare @.xmlNode xml
set @.xmlNode='
<a id="1">
<b id="2" />
<c id="3" />
</a>'
select @.xmlNode.query('//*[@.id="1"]/name()') -- fails since name()
function not allowed
I need to be able to return the name of an element for a particular id,
so the above example should return the value 'a'. If the search were
for id="3" the the result should be 'c'.
However, it seems that the name() function is not (yet?) supported in
sql server 2005. I've looked into local-name() but that's only
supported in a predicate/selector so I can't retrieve an actual value
from it.
I've also looked into the possibility of using the mp:localname
metaproperty in openxml but it doesn't seem like the right solution
since it should be possible to accomplish from within xquery.
It's probably staring me in the face, but I just can't seem to find the
solution.
Any help very much appreciated.
Regards, James
Correct syntax of name() and local-name() is,
name(xpath)
not,
xpath/name()
Yes, name() is not yet supported. Use local-name().
Additionally, xpath2.0 or xquery1.0 is occurence sensitive. So called
singleton is required for the local-name(). This is an error.
local-name(//*[@.id="1"])
Do instead,
local-name((//*[@.id="1"])[1])
Pohwan Han. Seoul. Have a nice day.
"jamesagnew" <jamesagnew@.hotmail.com> wrote in message
news:1125962687.996958.18470@.o13g2000cwo.googlegro ups.com...
> Hi
> Here's a simple example of what I'm trying to achieve:
> --
> declare @.xmlNode xml
> set @.xmlNode='
> <a id="1">
> <b id="2" />
> <c id="3" />
> </a>'
> select @.xmlNode.query('//*[@.id="1"]/name()') -- fails since name()
> function not allowed
> --
> I need to be able to return the name of an element for a particular id,
> so the above example should return the value 'a'. If the search were
> for id="3" the the result should be 'c'.
> However, it seems that the name() function is not (yet?) supported in
> sql server 2005. I've looked into local-name() but that's only
> supported in a predicate/selector so I can't retrieve an actual value
> from it.
> I've also looked into the possibility of using the mp:localname
> metaproperty in openxml but it doesn't seem like the right solution
> since it should be possible to accomplish from within xquery.
> It's probably staring me in the face, but I just can't seem to find the
> solution.
> Any help very much appreciated.
> Regards, James
>
|||That's it! I've got it working as follows:
select @.xmlNode.value('local-name((//*[@.id="1"])[1])','nvarchar(55)')
Han, you're a great asset to this list and your help is much
appreciated.
Thanks again, James

How to retrieve Views and Stored Procedure statement text?

Hi,
Is there any way that I can find where the SQL server stored our Create
View or Create Procedure statement (the text), I try to find on all
system tables but can not find it.
Thanks,
RicardCREATE statements for textual objects are stored in syscomments.
Hope this helps.
Dan Guzman
SQL Server MVP
"ricard" <ricard_notrealmail@.example.com> wrote in message
news:uodgYY%23OFHA.3156@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Is there any way that I can find where the SQL server stored our Create
> View or Create Procedure statement (the text), I try to find on all system
> tables but can not find it.
> Thanks,
> Ricard|||Hi Ricard
The stored procedure to see the definition of stored procedures and views is
sp_helptext.
If you want to see how sp_helptext gets the definition, you can look at IT'S
definition. :-)
USE master
GO
EXEC sp_helptext sp_helptext
GO
You will see that after some error checking, sp_helptext basically just does
a SELECT from the syscomments table.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"ricard" <ricard_notrealmail@.example.com> wrote in message
news:uodgYY%23OFHA.3156@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Is there any way that I can find where the SQL server stored our Create
> View or Create Procedure statement (the text), I try to find on all system
> tables but can not find it.
> Thanks,
> Ricard|||ricard wrote:
> Hi,
> Is there any way that I can find where the SQL server stored our Create
> View or Create Procedure statement (the text), I try to find on all
> system tables but can not find it.
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Views:
SELECT TABLE_NAME, VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'view name'
Procedures and Functions:
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'procedure or function name'
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQlYtxYechKqOuFEgEQId7QCcDENj6rwofI0D
eqt8hsZclqc0+McAn2Sr
g+1dfblirMiFk+jMbbf8TWZZ
=HvW0
--END PGP SIGNATURE--

How to retrieve video file from sql server database

Hi

I uploaded a video file into the sql database converting into binary data.

i have a problem that to retrieve video file(.wmv) from sql server 2000 database and which has to play on media player.

Plz help me any one .

Itz urgent for my website..........

Plz plz Plz Help me Urgent...............

Thanks in Advance

Regards Shiva

Re retrieving the file

Have a look at http://msdn2.microsoft.com/en-us/library/ms952601.aspx
http://www.akadia.com/services/dotnet_load_blob.html
http://www.sqlteam.com/item.asp?ItemID=986

|||

Hi,

I have the same problem. But i'm using oracle database. Similarly i'm retrieving images and place them into the imagebox. But i cant retrive video(as a byte in db) and send it to the media player. How can I do?

How to retrieve values from one db to another db

Hi
Could someone please give me the correct TSQL statement, for the below
"pseudo".
USE db_a INSERT INTO tbl_1 VALUES(USE db_b SELECT * FROM tbl_2)
given that tbl_1 and tbl_2 is identical.
Any hints appreciated
Regrds.
Mr. SmithAssuming both tables are owned by dbo:
INSERT INTO db_a.dbo.tbl_1
SELECT * FROM db_b.dbo.tbl_2
Jacco Schalkwijk
SQL Server MVP
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message
news:efdpI0ARFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hi
> Could someone please give me the correct TSQL statement, for the below
> "pseudo".
> USE db_a INSERT INTO tbl_1 VALUES(USE db_b SELECT * FROM tbl_2)
> given that tbl_1 and tbl_2 is identical.
> Any hints appreciated
> Regrds.
> Mr. Smith
>|||Hi,
No need of USE Database command here. you could use:-
Insert into database1.tableowner.tablename select * from
database2.tableowner.tablename
Thanks
Hari
SQL Server MVP
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message
news:efdpI0ARFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hi
> Could someone please give me the correct TSQL statement, for the below
> "pseudo".
> USE db_a INSERT INTO tbl_1 VALUES(USE db_b SELECT * FROM tbl_2)
> given that tbl_1 and tbl_2 is identical.
> Any hints appreciated
> Regrds.
> Mr. Smith
>|||INSERT INTO DB1.tbl_1
SELECT * from DB2.tbl_2
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Mr. Smith" <nospam@.blindfolded.gone> schrieb im Newsbeitrag
news:efdpI0ARFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hi
> Could someone please give me the correct TSQL statement, for the below
> "pseudo".
> USE db_a INSERT INTO tbl_1 VALUES(USE db_b SELECT * FROM tbl_2)
> given that tbl_1 and tbl_2 is identical.
> Any hints appreciated
> Regrds.
> Mr. Smith
>|||Thanks all of you! Hari, Jacco and Jens for a quick and easy answer.
Regards
Mr. Smith
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message
news:efdpI0ARFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hi
> Could someone please give me the correct TSQL statement, for the below
> "pseudo".
> USE db_a INSERT INTO tbl_1 VALUES(USE db_b SELECT * FROM tbl_2)
> given that tbl_1 and tbl_2 is identical.
> Any hints appreciated
> Regrds.
> Mr. Smith
>|||Sorry, forgot the owner
INSERT INTO DB1.dbo.tbl_1
SELECT * from DB2.dbo.tbl_2
Jens Smeyer.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> schrieb
im Newsbeitrag news:ur9bJ6ARFHA.3288@.TK2MSFTNGP14.phx.gbl...
> INSERT INTO DB1.tbl_1
> SELECT * from DB2.tbl_2
> HTH, Jens Smeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Mr. Smith" <nospam@.blindfolded.gone> schrieb im Newsbeitrag
> news:efdpI0ARFHA.204@.TK2MSFTNGP15.phx.gbl...
>|||Hi,
Try using OpenRowSet.
User db_a
INSERT INTO tbl('col1','col2')
SELECT a.col1,a.col2
FROM OPENROWSET('SQLOLEDB','servername';'user
id';'password','SELECT
col1,col2 FROM db_b.dbo.tbl_2') a
Hope this helps.
Regards,
Sambath
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message
news:efdpI0ARFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hi
> Could someone please give me the correct TSQL statement, for the below
> "pseudo".
> USE db_a INSERT INTO tbl_1 VALUES(USE db_b SELECT * FROM tbl_2)
> given that tbl_1 and tbl_2 is identical.
> Any hints appreciated
> Regrds.
> Mr. Smith
>

how to retrieve values from four tables at a time

i have to retrieve all columns of four table except common field(which is primary key in 1 table and foreign key in other table) will come only once from main table where condition is given by user. eg

table1- id(pk), name sex
table2- id(fk), address,contactno
table3- salary, pf,other,id(fk)
table4-id(fk), language,department

now i have to pick --id,name, sex,address,contactno,salary,pf, other,language,department-- where id= given by user.
i am using sql server,asp.net,c#
please reply fast
regards
sudha

Quote:

Originally Posted by sudhashekhar30

i have to retrieve all columns of four table except common field(which is primary key in 1 table and foreign key in other table) will come only once from main table where condition is given by user. eg

table1- id(pk), name sex
table2- id(fk), address,contactno
table3- salary, pf,other,id(fk)
table4-id(fk), language,department

now i have to pick --id,name, sex,address,contactno,salary,pf, other,language,department-- where id= given by user.
i am using sql server,asp.net,c#
please reply fast
regards
sudha

hi
try this>>>

select table1.id,table1.name, table1.sex,table2.address,table2.contactno,table3. salary,table3.pf, table3.other,table4.language,table4.departments from table1,table2,table3,table4

where table1.id=table2.id=table3.id=table4.id;

by
sankar|||

Quote:

Originally Posted by sanbala

hi
try this>>>

select table1.id,table1.name, table1.sex,table2.address,table2.contactno,table3. salary,table3.pf, table3.other,table4.language,table4.departments from table1,table2,table3,table4

where table1.id=table2.id=table3.id=table4.id;

by
sankar

thanks 4r ur reply mr shankar.
i did it. its something like dis--
"select e.*,paddress,pstate,ptaddress,designation,basicsal ary from empinfo e join address on e.empcode=address.empcode and e.empcode= @.id join salary s on e.empcode=s.empcode and e.empcode=@.id"
here is only 3 table.|||

Quote:

Originally Posted by sudhashekhar30

thanks 4r ur reply mr shankar.
i did it. its something like dis--
"select e.*,paddress,pstate,ptaddress,designation,basicsal ary from empinfo e join address on e.empcode=address.empcode and e.empcode= @.id join salary s on e.empcode=s.empcode and e.empcode=@.id"
here is only 3 table.

Hi sudha
i coundn't get you.what you trying to asking.
specify clearly...

by
sankar.b
bsankarit@.gmail.com

How to retrieve value from Exec stmt?

hi, all..
the following is part of my sp
I want to know how to assign result of stmt.1 to @.tp

declare @.tp datetime
declare @.tableName varchar(100)
SET @.tableName = 'tblState'
Exec ('SELECT MAX(UpdateTime) FROM ' + @.tableName) -- stmt.1

thank you..Try putting everything in an EXEC statement:

EXEC('
declare @.tp datetime
declare @.tableName varchar(100)
SET @.tableName = ''tblState''
SELECT MAX(UpdateTime) FROM @.tableName
')

I can't say this is the optimal way to do it, but it should work. Note: Those are two single quotes around tblState, not double quotes.|||declare @.tp datetime
declare @.tableName varchar(100)
SET @.tableName = 'tblState'
EXEC sp_executesql 'SELECT @.tp=MAX(UpdateTime) FROM ' + @.tableName, N'@.tp datetime OUTPUT',@.tp OUTPUT

select @.tp

it should give u the exact result.|||Thank you for reply,
however it gives following err...
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '+'.|||Modifying the previous example, I'd use:declare @.sql VARCHAR(250)declare @.tp datetime
declare @.tableName varchar(100)
SET @.tableName = 'tblState'
SET @.sql = 'SELECT @.tp=MAX(UpdateTime) FROM ' + @.tableName

EXEC sp_executesql @.sql, N'@.tp datetime OUTPUT',@.tp OUTPUT

select @.tp-PatP|||USE Northwind
GO

SET NOCOUNT ON

DECLARE @.tp datetime, @.TABLE_NAME sysname, @.COLUMN_NAME sysname, @.SQL varchar(8000)

SELECT @.TABLE_NAME = 'Orders', @.COLUMN_NAME = 'OrderDate'

CREATE TABLE #x(tp datetime)

SET @.SQL = 'INSERT INTO #x(tp) SELECT MAX('+@.COLUMN_NAME+') FROM '+@.TABLE_NAME

EXEC(@.SQL)

SELECT @.tp = tp FROM #x

SELECT @.tp

DROP TABLE #x

SET NOCOUNT OFF
GO|||Thank you all..
All are good hint..
Now it works..

my version is following..

DECLARE @.SQLString NVARCHAR(500), @.tbl nvarchar(100)
DECLARE @.ParmDefinition NVARCHAR(500)
declare @.tp datetime
SET @.tbl = 'tblState'
SET @.SQLString = N'SELECT @.tp = MAX(UpdateTime) FROM ' + @.tbl
SET @.ParmDefinition = N'@.tp datetime OUTPUT'
EXECUTE sp_executesql @.SQLString, @.ParmDefinition
,@.tp OUTPUT
Select @.tp as UpdateTime

How to retrieve unary operator for member?

Hi to everyone!

How to retrieve the value of the "UNARY_OPERATOR" property for a given dimension member?
MDSCHEMA_PROPERTIES schema contains information about this property but there is no such item in Properties collection of ADOMD.Member object.

I use the final version of SSAS2005 and MSOLAP.3 provider. I connect to server via ADOMD (not ADOMD.NET).
I have just discovered the way, but I think it's rather strange. You can execute a query with DIMENSION PROPERTIES statement like this:

SELECT { [Account].[Accounts].AllMembers } DIMENSION PROPERTIES UNARY_OPERATOR
ON ROWS, { [Category].[Category].Levels(0).AllMembers }
ON COLUMNS FROM [Finance] WHERE ([Measures].[Amount]
)

and needed property value is included in query results.

I'm still looking for another solution...

How to retrieve Top 3 records in the group level

Hi all,

I have a report which is grouped by a field called R_ID, which gives me a list of records for each R_ID. So here is the thing, I want to get only top 3 records for each R_ID. Is there any way to do this thing in the report level. I tried it from the query level, but the result is not like what I wanted.

Please let me know if some body has any idea.

Thx.

Doing this in a report wouldn′t be the best way as you would get all the data from the server and then only would display a part of it. better filter the data on the server and send only back the appropiate results, then you would have no problem on the client / reporting service. Anway, if you want to do this, you might have a look at the RowNumber() property in Reporting Services. You could set the Visibility of the row to Visible=True if the RowNumber("GroupName") equals or is less than 3 =IIF(Rownumber("GroupName") <= 3;True;False)

Doing this on the server depends on which server version you are using. SQL Server 2005 probably would enable you to use ROW_NUMBER() on the server side. SQL Server 2k does not implement this new function, so you would have to use something else instead.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

How to retrieve timestamp

Can any one please tell me how to retrieve values from timestamp column.
I am getting 1900-01-01 00:02:09.457. It is not storing current timestamp when record is created or modified.
Is there anything I need to set it up.
thanksCan any one please tell me how to retrieve values from timestamp column.
I am getting 1900-01-01 00:02:09.457. It is not storing current timestamp when record is created or modified.

Is there anything I need to set it up.

thanks

I don't believe the timestamp datatype actually contains a Date/Time, but instead, a unique binary number which represents an update to the row.

timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.

If you need a field to hold date/time information, use the datetime or smalldatetime datatypes.|||If you need a field to hold date/time information, use the datetime or smalldatetime datatypes.
=============================================

System is in production now. Even though I create a column with datetime the program will not update the column. So I thought setting up a column which is being automatically updated.

I like to delete some records based on the date last modified.

Thanks|||This is right out of the SQL Server help guide:

timestamp
Is used to indicate the sequence of SQL Server activity on a row, represented as an increasing number in a binary format. As a row is modified in a table, the timestamp is updated with the current database timestamp value obtained from the @.@.DBTS function. timestamp data is not related to the date and time of an insert or change to data. To automatically record times that data modifications take place in a table, use either a datetime or smalldatetime data type to record the events and triggers.

You'll never get actual date/time values from the timestamp datatype. You're going to have to change something if you want to know exactly when a row was last touched.

How to retrieve the T-SQL for a particular subscription?

In SQL 2005 NS, I have created a custom subscription management interface that allows for condition actions. I can currently create, update and delete subscriptions without a problem.

However, I would like to be able to retrieve the T-SQL that will be run when the event is fired to test it for correct syntax etc. I am hoping to notify the user of any possible issues as soon as the subscription is created as opposed to waiting for an error when the event fires. Since many matching fields are simply text entries, the user may enter some data values that cause the matching statement to fail and I would like to catch that error and notify the user at subscription creation.

To do this, I plan to create the subscription with a "disabled" status. The next step would be to retrieve and run the actual T-SQL that will be executed when an event is fired. If there are no errors then I will update the subscription to "enabled".

I cannot seem to find a class member or stored procedure that returns the resulting T-SQL for a single subscription.

Any help would be appreciated.

Thanks

Maybe, I didn't understand exactly what you are trying to accomplish, but as far as I can see, you do not need to retrieve T-SQL for a subscription creation. Instead, you can just do your validations in your SMI class. And you can maximize your GUI usage for validations.

For example, use as many drop-down menus or listboxes as possible instead of combo boxes, textboxes, etc. Let users choose the predefined values.

Also, say, for example, that there is a field in your subscription class which is a DateTime value. Don't let the user enter the date. Give him a Calendar or DateTimePicker controls.

If the user still has to enter some values manually (such as money values), do validations in GUI before calling a SMI method, such as

try {

double amount = Convert.ToDouble(textBox1.Text);

// invoke SMI method for creating or modifying the subscription

} catch (Exception ex) { // show error message to the user }

|||

Thank you for your response.

I am already performing these types of validations and using dropdowns/calendars where appropriate.

However, I also wanted to take it a step further in case something is missed in the validations. The bottom line is; you never know what a user is going to enter in a textbox.

I would like to retrieve the actual SQL that will be run for an individual subscription and test it.

How to retrieve the text of UDF in SQL Server 2005

In the previous versions of SQL Server, retrieving the text of User
Defined Functions was easy:
SELECT text FROM syscomments sc INNER JOIN sysobjects so ON sc.id =
so.id WHERE so.name = 'fn_dblog' ORDER BY sc.colid
For some reason the above statement doesn't work in SQL Server 2005 (it
works for procedures and for views, but not for functions).
How can I retrieve the text of User Defined Functions in SQL Server
2005?
TIA
Dariusz DziewialtowskiHi
No, it works very well for user's UDF as well. This udf is not created by
an user ,moreover if i'm mo mistaken it isnt supported by MS
However you can achive it by issuing
sp_helptext 'fn_dblog'
<dariusz.dziewialtowski@.gmail.com> wrote in message
news:1144554215.276141.57970@.u72g2000cwu.googlegroups.com...
> In the previous versions of SQL Server, retrieving the text of User
> Defined Functions was easy:
> SELECT text FROM syscomments sc INNER JOIN sysobjects so ON sc.id =
> so.id WHERE so.name = 'fn_dblog' ORDER BY sc.colid
> For some reason the above statement doesn't work in SQL Server 2005 (it
> works for procedures and for views, but not for functions).
> How can I retrieve the text of User Defined Functions in SQL Server
> 2005?
> TIA
> Dariusz Dziewialtowski
>|||Hi Uri,
Thank you for your help.

>This udf is not created by an user
Yes, I gave it only as an example.

>However you can achive it by issuing sp_helptext 'fn_dblog'
I didn't think of that - I have to retrieve the text programmatically,
from VB6 code, but if I cannot make the old method work - "SELECT text
FROM syscomments" - than I'll try to execute sp_helptext
programmatically.
Still it puzzles me why "SELECT text FROM syscomments" is failing for
User Defined Functions in SQL Server 2005.
Thanks again for your help.
Dariusz Dziewialtowski.|||(dariusz.dziewialtowski@.gmail.com) writes:
> Still it puzzles me why "SELECT text FROM syscomments" is failing for
> User Defined Functions in SQL Server 2005.
In general it isn't:
CREATE FUNCTION myudf() RETURNS int AS
BEGIN
RETURN (99)
END
go
SELECT text FROM syscomments WHERE id = object_id('myudf')
go
DROP FUNCTION myudf
works for me.
However, in your original post you had fn_dblog, and that function
has moved and no longer lives in master, as have all other system
procedures and system UDFs. They now live in the hidden resource
database.
Also beware that SQL 2005 completely changes how metadata is stored.
The system tables from SQL 2000 are now merely compatibility views
on top of the new catalog views. The catalog views in their turn
refers to the new system tables that are accessible outside system code.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hello Erland,
Thank you for your help - I was not aware about the changes in metadata
in SQL Server 2005. Thanks a lot for explaining them to me.
Dariusz Dziewialtowski.

How to retrieve the SQL query generated by processing a cube in ASSAS

I have seen several posts which talk about looking at the underlying SQL for a cube in SQL Server 2005, but I have been unable to locate where you can do this. When I process my cube I do not see any SQL in the Process progress dialog box in the way you do with AS2000.

I have tried running SQL profiler while processing but that didn't give anything either.

Could someone help me out please?

Thanks

In the processing progress dialog box, you should be able to expand the items listed and eventually get down to the detail of the SQL queries generated and executed.

However, I find that using SQL Server Profiler works better if your source for the SSAS database is a SQL Server database. When you use Profiler, you need to profile the SQL Server database, not the SSAS database. Profiling the SQL Server database should show the queries hitting the database.

HTH,

Dave Fackler

How to Retrieve the Rows where particular Column Data equals NULL

Hi all,

I have a datatable Employee which is containg columns Emp_ID, Emp_Name, Manager_ID

I want to retrieve all the rows whose Manager_ID=NULL

I wirte the query select * from Employee where Manager_ID=NULL

but it is not working. How to do this. Please help me.

with regards

Ravinder Reddy.K

You have to use IS operator on NULL..

select * from Employee where Manager_ID is NULL

remember,

NULL = NULL => always False

NULL IS NULL = > True

|||

>NULL = NULL => always False

Not always... this is dependent on the setting of ANSI_NULLS. Try the following as an example:

SET ANSI_NULLS ON
GO
SELECT CASE WHEN NULL = NULL THEN 'YES'
ELSE 'NO'
END
GO
--Result: 'No'


SET ANSI_NULLS OFF
GO
SELECT CASE WHEN NULL = NULL THEN 'YES'
ELSE 'NO'
END
GO
--Result: 'Yes'

Chris

||| Forget the Settings BUDDY