Getting - Setting Environment Variables

Hi,

1st. Issue

I have written a package that uses an environmet variable which contains certain information that the user must enter. A sql script task will then get the information from the environment variable and manipulate it.

I don't have a problem programmatically getting information from the environment variable. However, when I manually edit the contents of the environment variable, the changes won't take place unless the box is rebooted. Is there a way to refresh environment variables without rebooting?

2nd. Issue

I have written another package that also reads data from an environment variable but it must also manipulate the data and save the changes to the environment variable.

I have accomplished this programmatically by using GetEnvironmentVariable() and SetEnvironmentVariable(), but unfortunately the changes live during program execution only. Afte the program executes, the changes are wiped out.

How can I make changes stick to environment variables (programmatically)?

-- I found a solution to this:

When programmatically getting and/or setting an environment variable the 'target' parameter must be specified in the method call. By 'target' I mean the location in the registry where your environment variable is stored.

GetEnvironmentVariable( var, target )

SetEnvironmentVariable( OldValue, NewValue, target )

Please visit this link for more info:

http://msdn2.microsoft.com/en-us/library/96xafkes.aspx

Get the User Name for a login user

Hi Guys,

How can I get the current loged in UserName in windows in MSSQL?

The problem is:

Users login into their PC (Windows Login)Enter a URL of the intranet applicationThey would then be able to access the web base application (Intranet)The web application has a string connection to make a connection to the databaseI want to have a Trigger for my "products" table so if the table is modified (Insert/Update) all those changes be entered in "products_Audit" tableNow on "products_Audit" table I have a field "ChangedBy" where I want to store the windows loged in User Name (SYSTEM_USER just return the Username for the SQL connection that the web application use)

I would really appreciate all your help and suggestions.

Regards,
Mehdi

Hello,

In case of IntraNet where directory security does not allow anonymous users, then you can retrieve domain user information.

dim user as string = Request.ServerVariables("LOGON_USER")

and pass this variable for your database action query.

Get space used of all databases

Hello,
I need to get space information of all databases and report it. I create one
cursor that enter in every databases and report space used, but it returns
the following error.
"Server: Msg 8114, Level 16, State 5, Line 16
Error converting data type nvarchar to numeric."
If i treat my variables like normal text ('') and use the output generated
it works fine.
I send you part of the script and hope that you can help me
declare @.dbsize dec(15,0),
@.dbname sysname,
@.sql nvarchar(850)
declare dbid_cur cursor for
select [name]
from master..sysdatabases
where [name] <> 'tempdb' for read only
open dbid_cur
fetch next from dbid_cur into @.dbname
while @.@.fetch_status = 0
begin
set @.sql = 'use ' + @.dbname + nchar(13) + nchar(10) +
+ 'select ' + @.dbsize + '= cast(sum(convert(dec(15),size))as
varchar(20))'
print @.sql
-- sp_executesql @.sql
fetch next from dbid_cur into @.dbname
end
close dbid_cur
deallocate dbid_cur
Thanks and best regardsHave you looked at sp_helpdb? Does it give you what you want?
Keith
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:3001C30F-8E9C-496C-A90C-770CB8D41101@.microsoft.com...
> Hello,
> I need to get space information of all databases and report it. I create
one
> cursor that enter in every databases and report space used, but it returns
> the following error.
> "Server: Msg 8114, Level 16, State 5, Line 16
> Error converting data type nvarchar to numeric."
> If i treat my variables like normal text ('') and use the output generated
> it works fine.
> I send you part of the script and hope that you can help me
> declare @.dbsize dec(15,0),
> @.dbname sysname,
> @.sql nvarchar(850)
> declare dbid_cur cursor for
> select [name]
> from master..sysdatabases
> where [name] <> 'tempdb' for read only
> open dbid_cur
> fetch next from dbid_cur into @.dbname
> while @.@.fetch_status = 0
> begin
> set @.sql = 'use ' + @.dbname + nchar(13) + nchar(10) +
> + 'select ' + @.dbsize + '= cast(sum(convert(dec(15),size))as
> varchar(20))'
> print @.sql
> -- sp_executesql @.sql
> fetch next from dbid_cur into @.dbname
> end
> close dbid_cur
> deallocate dbid_cur
> Thanks and best regards|||How about
EXEC sp_msforeachdb 'USE ?; EXEC sp_spaceused'
(Note that the procedure is unsupported.)
http://www.aspfaq.com/
(Reverse address to reply.)
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:3001C30F-8E9C-496C-A90C-770CB8D41101@.microsoft.com...
> Hello,
> I need to get space information of all databases and report it. I create
one
> cursor that enter in every databases and report space used, but it returns
> the following error.
> "Server: Msg 8114, Level 16, State 5, Line 16
> Error converting data type nvarchar to numeric."
> If i treat my variables like normal text ('') and use the output generated
> it works fine.
> I send you part of the script and hope that you can help me
> declare @.dbsize dec(15,0),
> @.dbname sysname,
> @.sql nvarchar(850)
> declare dbid_cur cursor for
> select [name]
> from master..sysdatabases
> where [name] <> 'tempdb' for read only
> open dbid_cur
> fetch next from dbid_cur into @.dbname
> while @.@.fetch_status = 0
> begin
> set @.sql = 'use ' + @.dbname + nchar(13) + nchar(10) +
> + 'select ' + @.dbsize + '= cast(sum(convert(dec(15),size))as
> varchar(20))'
> print @.sql
> -- sp_executesql @.sql
> fetch next from dbid_cur into @.dbname
> end
> close dbid_cur
> deallocate dbid_cur
> Thanks and best regards

Get space used of all databases

Hello,
I need to get space information of all databases and report it. I create one
cursor that enter in every databases and report space used, but it returns
the following error.
"Server: Msg 8114, Level 16, State 5, Line 16
Error converting data type nvarchar to numeric."
If i treat my variables like normal text ('') and use the output generated
it works fine.
I send you part of the script and hope that you can help me
declare @.dbsize dec(15,0),
@.dbnamesysname,
@.sql nvarchar(850)
declare dbid_cur cursor for
select [name]
from master..sysdatabases
where [name] <> 'tempdb' for read only
open dbid_cur
fetch next from dbid_cur into @.dbname
while @.@.fetch_status = 0
begin
set @.sql = 'use ' + @.dbname + nchar(13) + nchar(10) +
+ 'select ' + @.dbsize + '= cast(sum(convert(dec(15),size))as
varchar(20))'
print @.sql
-- sp_executesql @.sql
fetch next from dbid_cur into @.dbname
end
close dbid_cur
deallocate dbid_cur
Thanks and best regards
Have you looked at sp_helpdb? Does it give you what you want?
Keith
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:3001C30F-8E9C-496C-A90C-770CB8D41101@.microsoft.com...
> Hello,
> I need to get space information of all databases and report it. I create
one
> cursor that enter in every databases and report space used, but it returns
> the following error.
> "Server: Msg 8114, Level 16, State 5, Line 16
> Error converting data type nvarchar to numeric."
> If i treat my variables like normal text ('') and use the output generated
> it works fine.
> I send you part of the script and hope that you can help me
> declare @.dbsize dec(15,0),
> @.dbname sysname,
> @.sql nvarchar(850)
> declare dbid_cur cursor for
> select [name]
> from master..sysdatabases
> where [name] <> 'tempdb' for read only
> open dbid_cur
> fetch next from dbid_cur into @.dbname
> while @.@.fetch_status = 0
> begin
> set @.sql = 'use ' + @.dbname + nchar(13) + nchar(10) +
> + 'select ' + @.dbsize + '= cast(sum(convert(dec(15),size))as
> varchar(20))'
> print @.sql
> -- sp_executesql @.sql
> fetch next from dbid_cur into @.dbname
> end
> close dbid_cur
> deallocate dbid_cur
> Thanks and best regards
|||How about
EXEC sp_msforeachdb 'USE ?; EXEC sp_spaceused'
(Note that the procedure is unsupported.)
http://www.aspfaq.com/
(Reverse address to reply.)
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:3001C30F-8E9C-496C-A90C-770CB8D41101@.microsoft.com...
> Hello,
> I need to get space information of all databases and report it. I create
one
> cursor that enter in every databases and report space used, but it returns
> the following error.
> "Server: Msg 8114, Level 16, State 5, Line 16
> Error converting data type nvarchar to numeric."
> If i treat my variables like normal text ('') and use the output generated
> it works fine.
> I send you part of the script and hope that you can help me
> declare @.dbsize dec(15,0),
> @.dbname sysname,
> @.sql nvarchar(850)
> declare dbid_cur cursor for
> select [name]
> from master..sysdatabases
> where [name] <> 'tempdb' for read only
> open dbid_cur
> fetch next from dbid_cur into @.dbname
> while @.@.fetch_status = 0
> begin
> set @.sql = 'use ' + @.dbname + nchar(13) + nchar(10) +
> + 'select ' + @.dbsize + '= cast(sum(convert(dec(15),size))as
> varchar(20))'
> print @.sql
> -- sp_executesql @.sql
> fetch next from dbid_cur into @.dbname
> end
> close dbid_cur
> deallocate dbid_cur
> Thanks and best regards

Get space used of all databases

Hello,
I need to get space information of all databases and report it. I create one
cursor that enter in every databases and report space used, but it returns
the following error.
"Server: Msg 8114, Level 16, State 5, Line 16
Error converting data type nvarchar to numeric."
If i treat my variables like normal text ('') and use the output generated
it works fine.
I send you part of the script and hope that you can help me
declare @.dbsize dec(15,0),
@.dbname sysname,
@.sql nvarchar(850)
declare dbid_cur cursor for
select [name]
from master..sysdatabases
where [name] <> 'tempdb' for read only
open dbid_cur
fetch next from dbid_cur into @.dbname
while @.@.fetch_status = 0
begin
set @.sql = 'use ' + @.dbname + nchar(13) + nchar(10) +
+ 'select ' + @.dbsize + '= cast(sum(convert(dec(15),size))as
varchar(20))'
print @.sql
-- sp_executesql @.sql
fetch next from dbid_cur into @.dbname
end
close dbid_cur
deallocate dbid_cur
Thanks and best regardsHave you looked at sp_helpdb? Does it give you what you want?
--
Keith
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:3001C30F-8E9C-496C-A90C-770CB8D41101@.microsoft.com...
> Hello,
> I need to get space information of all databases and report it. I create
one
> cursor that enter in every databases and report space used, but it returns
> the following error.
> "Server: Msg 8114, Level 16, State 5, Line 16
> Error converting data type nvarchar to numeric."
> If i treat my variables like normal text ('') and use the output generated
> it works fine.
> I send you part of the script and hope that you can help me
> declare @.dbsize dec(15,0),
> @.dbname sysname,
> @.sql nvarchar(850)
> declare dbid_cur cursor for
> select [name]
> from master..sysdatabases
> where [name] <> 'tempdb' for read only
> open dbid_cur
> fetch next from dbid_cur into @.dbname
> while @.@.fetch_status = 0
> begin
> set @.sql = 'use ' + @.dbname + nchar(13) + nchar(10) +
> + 'select ' + @.dbsize + '= cast(sum(convert(dec(15),size))as
> varchar(20))'
> print @.sql
> -- sp_executesql @.sql
> fetch next from dbid_cur into @.dbname
> end
> close dbid_cur
> deallocate dbid_cur
> Thanks and best regards|||How about
EXEC sp_msforeachdb 'USE ?; EXEC sp_spaceused'
(Note that the procedure is unsupported.)
--
http://www.aspfaq.com/
(Reverse address to reply.)
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:3001C30F-8E9C-496C-A90C-770CB8D41101@.microsoft.com...
> Hello,
> I need to get space information of all databases and report it. I create
one
> cursor that enter in every databases and report space used, but it returns
> the following error.
> "Server: Msg 8114, Level 16, State 5, Line 16
> Error converting data type nvarchar to numeric."
> If i treat my variables like normal text ('') and use the output generated
> it works fine.
> I send you part of the script and hope that you can help me
> declare @.dbsize dec(15,0),
> @.dbname sysname,
> @.sql nvarchar(850)
> declare dbid_cur cursor for
> select [name]
> from master..sysdatabases
> where [name] <> 'tempdb' for read only
> open dbid_cur
> fetch next from dbid_cur into @.dbname
> while @.@.fetch_status = 0
> begin
> set @.sql = 'use ' + @.dbname + nchar(13) + nchar(10) +
> + 'select ' + @.dbsize + '= cast(sum(convert(dec(15),size))as
> varchar(20))'
> print @.sql
> -- sp_executesql @.sql
> fetch next from dbid_cur into @.dbname
> end
> close dbid_cur
> deallocate dbid_cur
> Thanks and best regards

Get report in Excel

Hi,

I've made C# winform application where the user can enter it's parameters (I did this so I can do some inputcontrol on the users parameters which I can't in reportviewer cause it's Closed Source).

Now when the user presses the button Get report in Excel. The application should pass the parameters to the SQL statement and complete this. Then the report should be exported to Excel and Excel should start and show me the report.

All this should work behind the scenes and the user should just get the report in Excel. I've added the Web Reference from the report server to my project but I can't seem to find the correct method.

thanks in advance

Hello,

Have a look at the ReportExecutionService class for RS 2005:

http://msdn2.microsoft.com/en-US/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice(SQL.90).aspx

-Chris