Net Send

I got this script from Database journal and I found that very useful to DBA:
Create procedure netsend @.dbname varchar(50)
As
--Script Language and Platform: MS SQL 7.0 and MS SQL 2000
--Objecttive: Before restoreing,upgrading database,database administrator is
responsible to
--inform all the users in that database that they are going to be
disconnected and remind
--them to save their works.
--exec netsend 'xyz'--xyz is the database name
--Created by :Claire Hsu 2003/4/19
--Email:messageclaire@.yahoo.com
create table table1000(msg varchar(500))
insert into table1000
select "net send "+ltrim(rtrim(y.hostname))+ ' "we will log you out in 2 min
,please save your work"' from master.dbo.sysprocesses
y,master.dbo.sysdatabases x
where x.dbid = y.dbid and x.name = @.dbname
declare @.msgs varchar(500)
declare cur1 cursor for select msg from table1000
open cur1
fetch next from cur1 into @.msgs
while @.@.fetch_status = 0
begin
exec master.dbo.xp_cmdshell @.msgs
fetch next from cur1 into @.msgs
end
close cur1
deallocate cur1
drop table table1000
--Usage
--exec netsend 'xyz'
However when I ran this query, I got the error message as below:
Server: Msg 207, Level 16, State 3, Procedure netsend, Line 14
Invalid column name 'net send '.
I think the something wrong with the " net send", anyone can advise on this?
Thanks so muchDo not use colon, use apostrophes instead.

> select "net send "+ltrim(rtrim(y.hostname))+ ' "we will log you out in 2 min[/colo
r]
select 'net send ' + ltrim(rtrim(y.hostname)) + ' "we will log you out in 2
min
AMB
"Sql Fren" wrote:
> I got this script from Database journal and I found that very useful to DB
A:
> Create procedure netsend @.dbname varchar(50)
> As
> --Script Language and Platform: MS SQL 7.0 and MS SQL 2000
> --Objecttive: Before restoreing,upgrading database,database administrator
is
> responsible to
> --inform all the users in that database that they are going to be
> disconnected and remind
> --them to save their works.
> --exec netsend 'xyz'--xyz is the database name
> --Created by :Claire Hsu 2003/4/19
> --Email:messageclaire@.yahoo.com
>
> create table table1000(msg varchar(500))
> insert into table1000
> select "net send "+ltrim(rtrim(y.hostname))+ ' "we will log you out in 2 m
in
> ,please save your work"' from master.dbo.sysprocesses
> y,master.dbo.sysdatabases x
> where x.dbid = y.dbid and x.name = @.dbname
> declare @.msgs varchar(500)
> declare cur1 cursor for select msg from table1000
> open cur1
> fetch next from cur1 into @.msgs
> while @.@.fetch_status = 0
> begin
> exec master.dbo.xp_cmdshell @.msgs
> fetch next from cur1 into @.msgs
> end
> close cur1
> deallocate cur1
> drop table table1000
>
> --Usage
> --exec netsend 'xyz'
>
> However when I ran this query, I got the error message as below:
> Server: Msg 207, Level 16, State 3, Procedure netsend, Line 14
> Invalid column name 'net send '.
>
> I think the something wrong with the " net send", anyone can advise on thi
s?
> Thanks so much
>|||Correction,
Do not use quotation mark, use apostrophes instead.
> select "net send "+ltrim(rtrim(y.hostname))+ ' "we will log you out in 2 min[/colo
r]
select 'net send ' + ltrim(rtrim(y.hostname)) + ' "we will log you out in 2
min
AMB
"Alejandro Mesa" wrote:
> Do not use colon, use apostrophes instead.
>
> select 'net send ' + ltrim(rtrim(y.hostname)) + ' "we will log you out in
2
> min
>
> AMB
> "Sql Fren" wrote:
>

Neither System.DbNull or NULL?

How to set NULL values inside VB Script .Net? Both causes error:

.FECHAAP = NULL

.FECHAAP = System.DBNull

.FECHAAP = Nothing

-Jamie

|||Thanks

Negative unused space

I wrote simple script to check space used by tables:

CREATE TABLE #SpaceUsed(
TableName NVARCHAR(128),
NoOfRows INT,
Reserved NVARCHAR(18),
Data NVARCHAR(18),
Index_Size NVARCHAR(18),
Unused NVARCHAR(18)
)
GO
sp_msforeachtable "INSERT INTO #SpaceUsed EXEC sp_spaceused '?'"

SELECT * FROM #SpaceUsed

SELECT
CAST(Sum(CAST(Replace(Reserved,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalReserved,
CAST(Sum(CAST(Replace(Data,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalData,
CAST(Sum(CAST(Replace(Index_Size,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalIndex_Size,
CAST(Sum(CAST(Replace(Unused,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalUnused
FROM #SpaceUsed

DROP TABLE #SpaceUsed

and one of results looks strange to me:

TableName NoOfRows Reserved Data Index_Size Unused
--------------------------------------- ---- ------ ------ ------ ------
T_TableXX 50081 38024 KB 37432 KB 640 KB -48 KB

Anyone know reason of such result (negative value of unused space)?Use this :

CREATE TABLE #SpaceUsed(
TableName NVARCHAR(128),
NoOfRows INT,
Reserved NVARCHAR(18),
Data NVARCHAR(18),
Index_Size NVARCHAR(18),
Unused NVARCHAR(18)
)
GO
sp_msforeachtable "INSERT INTO #SpaceUsed EXEC sp_spaceused '?' ,@.updateusage='True'"

SELECT * FROM #SpaceUsed

SELECT
CAST(Sum(CAST(Replace(Reserved,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalReserved,
CAST(Sum(CAST(Replace(Data,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalData,
CAST(Sum(CAST(Replace(Index_Size,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalIndex_Size,
CAST(Sum(CAST(Replace(Unused,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalUnused
FROM #SpaceUsed

DROP TABLE #SpaceUsed|||Works fine. Thanx :)|||Originally posted by MST78
Anyone know reason of such result (negative value of unused space)? It happends all of the time if you don't regularly update your statistics. You can update a single table using UPDATE STATISTICS (http://msdn.microsoft.com/library/en-us/tsqlref/ts_ua-uz_1mpf.asp), or get them all at once if you have the time using DBCC UPDATEUSAGE (http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_24rp.asp).

-PatP

needed sql script to convert binary content

Hi all,

how r u everyone, guys i need a help, i have a interface in my application, adding attachments to issues, ie users can attach a file for a particular issue and if anyone have to view they ve click a link and it will download.

iam using filestream and binaryreader concept in that, ie read the file using filestream and then use binaryreader.readbytes method to convert it into byte array and store it in the database in a column with a image datatype as a binary content.

FileStream oImg;
BinaryReader oBinaryReader;
byte[] oImgByteArray;

oImg = new FileStream(sFilePath,FileMode.Open,FileAccess.Read);
oBinaryReader = new BinaryReader(oImg);
oImgByteArray = oBinaryReader.ReadBytes((int)oImg.Length);
oBinaryReader.Close();
oImg.Close();

this is the code iam using.

when i use this its taking so much of time to get uploaded. so what i thought of doing is save the file in a specific folder using some postedfile.saveas(not sure of syntax) and when the user wants to view the file they can just download the file. i can do this by googling but what i want is wat abt the existing attachments in the database. so i need a help to do this......

is it possible to create a query to read the files and convert it into original file and save it in the specific folder or do i ve to create a simple interface to create do it manually , pls someone help me what to do .....

thanks in advance.

waiting for a reply soon

Note: any unclear statement in my question kindly reply me

Wishes n Regards

Venkat.

Hi,

Based on my understanding, I understand that you're trying to pass the files in a specific folder to the client.

Yes, I think it is possible to do this. But we cannot save the binary content of the file in database. In your database, you can only store the path of the file, then just use the following code to push to client.

String filePath = Server.MapPath(PathOfFileFromDatabase);
System.IO.FileInfo fileInfo = new System.IO.FileInfo(filePath);
Response.ContentType = "application/octet-stream";
Response.AddHeader("Content-Disposition", String.Format(@."attachment;filename=\""{0}\""", filePath));
Response.AddHeader("Content-Length", fileInfo.Length.ToString());
Response.WriteFile(filePath);
Response.End();

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

Hi,Thanks for the reply, it was useful for me , but still wat iam looking for is , is it possible to do the same as a sql query , i mean i shld run a query or a stored procedure in query analyser that shld do same without using an interface , is it possible ?

is it possible to read a image from a db table using a sql query ?

Thanks in advance

Wishes n Regards

Need to trigger a script to run after merge replication finishes

After a subscriber connects to the network and merges the data with the
publisher I’m looking for a way to trigger a script to run. Is there
anything built into SQL Server to do such a thing?
Normally this is done with a final job step. Right click on your merge
agent, select agent properties, click on steps, and add a 4th job. Have the
3rd steps On Failure action be quit with failure, and on Success action be
go to next step.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Pauly C" <paulysc@.optonline.net> wrote in message
news:8E4CE71B-F2D0-432E-8FD5-887E7805174B@.microsoft.com...
> After a subscriber connects to the network and merges the data with the
> publisher I'm looking for a way to trigger a script to run. Is there
> anything built into SQL Server to do such a thing?