Hi,
I have installed Visual Studio 2005 Reporting Services and try to make some subscription. The strange thing is that after I clicked "New Subscription" in the Reporting Manager and Report Delivery Option only shows the option "Report Server File Share" .I cannot select email subscription. Did I miss something out here?
THanks for the help
Josh
Hi Josh,
sure that you have configured the mail-settings using the RS-Frontend?
cheers
markus
Hi all,
I'm having a little problem with my sqlserver 2005.
I'm trying to crate a user in Microsoft SQL Server Management Studio throug a query.
Like this:
-
USE [master]
GO
CREATE LOGIN [MYUSER] WITH PASSWORD=N'MYPASSWORD', DEFAULT_DATABASE=[MYDB],
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER LOGIN [MYUSER] ENABLE
GO
USE [msdb]
GO
CREATE USER [MYUSER] FOR LOGIN [MYUSER];
GO
GRANT EXECUTE ON [sp_start_job] TO [MYUSER]
GO
EXEC sp_addrolemember 'db_datareader', 'MYUSER'
GO
EXEC sp_addrolemember 'db_datawriter', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentOperatorRole', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentUserRole', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentReaderRole', 'MYUSER'
GO
Use [MYDB]
GO
CREATE USER [MYUSER] FOR LOGIN [MYUSER]
GO
EXEC sp_addrolemember 'db_datareader', 'MYUSER'
GO
EXEC sp_addrolemember 'db_datawriter', 'MYUSER'
GO
And drops the user by executing following:
Use [MYDB]
GO
EXEC sp_dropuser 'MYUSER'
GO
USE [msdb]
GO
REVOKE EXECUTE ON [sp_start_job] TO [MYUSER]
GO
EXEC sp_droprolemember 'SQLAgentOperatorRole', 'MYUSER'
GO
EXEC sp_droprolemember 'SQLAgentUserRole', 'MYUSER'
GO
EXEC sp_droprolemember 'SQLAgentReaderRole', 'MYUSER'
GO
EXEC sp_dropuser 'MYUSER'
GO
USE [master]
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'MYUSER')
DROP LOGIN [MYUSER]
-Then if I from a query, first creates the user, then drops the user, I can't create him again? I get's the following error when trying:
Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
I'f i then runs the createscript again, the user is created correctly.
Is there a way to avoid this foced lockout? Or how do i "refreash" my connection, without dataloss?
Best Regards
Henrik
Have you tried executing your scripts from sqlcmd instead of Management Studio? Are you seeing the same behavior?
Thanks
Laurentiu
Hi Cristofor,
Thanks for you reply. I'v just tried that and get following:
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
But under "Server Properties" - "Connections"
The "Allow Remote connections to this server" is checked, with a query timeout on 600
|||In some SQL Server SKUs the remote connection is disabled by default. Use SQL Server Configuration Manager to enable TCP/IP (or named pipes) on your SQL Server instance (requires to restart SQL Server service). Also make sure the Windows firewall is configured to allow SQL Server to accept connections.
-Raul Garcia
SDE/T
SQL Server Engine
|||Hi Raul,
I have just enabled this it was disabled, but still im getting the same error.
But by executeing the script localy from the database server, it works fine. And can do it as many times as i please, whithout errors.
like this:
sqlcmd -sMYDBSERVER -E -i "C:\drop.sql"
And
sqlcmd -sMYDBSERVER -E -i "C:\Create.sql"
But it still drops the connection to the user elsewhere, on the first time it's used after creation.
Eg. First i drop the user, then i create it again with the scripts. Then i open a browser and goes to my website, wich uses this user to connect to the database to retrive and update information. Then the first time i try to login i get the error below. Then i push "F5" to refreash the page and all works fine again?
My only guess is that your web application is somehow caching/reusing the old connection (with the old login), and when you refresh the page it reestablishes the connection. Remember that when you drop and create a login it is a whole new principal, not the same one (even if the name and password is the same), and any established connection will be invalidated and the connection will be forcedly terminated by the server the next time any application tries to use it.
I would recommend using SQL profiler to trace back new/closed connections as well as existing sessions to try to detect if this is indeed the case.
-Raul Garcia
SDE/T
SQL Server Engine
|||Hi,
Thats also what im comming to. I haven't tryed the SQL profiler yet, but will do this later.
But i have found out that it's not enough only to stop and start the web site again on IIS. I have to restart the hole "IIS" to make the "refreash". So im thinking that the session is not terminated correctly from the website, when closing the browser/session?.
|||Besides your investigation on why (and if) the web application is reusing the connection instead of reconnecting, I have one question for you: are you dropping and recreating this user in order to create a whole new principal (i.e. a new SID, with no existing objects, etc.), or just as a mechanism to prevent this principal from establishing a new connection?
If you are using these scripts to prevent new connections, you may also want to consider disabling the login instead. By disabling the login, you prevent new connections without affecting existing ones, and without the need to cleanup any objects or references (i.e. users) this principal may have in the system. You can find the syntax for this operation in ALTER LOGIN (http://msdn2.microsoft.com/en-us/library/ms189828.aspx).
-Raul Garcia
SDE/T
SQL Server Engine
|||
Actually im useing thise scripts as a part of my deploymentkit. This is to ensure that all securitysettings to any databases, stored procedures etc are droped. So i afterwards can replace them with either the same or new ones, without leaving anything behind.
But i took the change and implemented it into my deployment. The first time i got the same error about the "transport-level error". But that was because i've missed another scipt later in the installation, it was some inserts-stament i tried to execute from a vb SqlConnection. Then i moved those two things around, so it was the last code and then it worked fine, i can now open my webpage without the "transport-level error" :) yeah .. even after installing, uninstalling, installing ....
So i have to go back to the websession-closing, in my case, because under the "Activity Monitor" in sql, i can see a connection long after closing the browser. So this is now another problem for me, to find a way to kill this one.
But im stil interested in fixing the other problem, because it's annoying that things has to come in the correct order.. And the differens between executing it from a sql-query (in sql 2005), from command promt and from my msi-package. -Wich by the way executes it by calling a shell in vb wich runs a .cmd-file containing the two sql-scripts.
sqlcmd -sMYDBSERVER -E -i "C:\drop.sql" -o "C:\DropDBUser.log"
sqlcmd -sMYDBSERVER -E -i "C:\Create.sql" -o "C:\CreateDBUser.log"
But for the moment im happy :)
|||
I’m glad to hear that. Feel free to let us know if you have further security questions in SQL Server, we are always looking forward to help our customers whenever we have a chance.
-Raul Garcia
SDE/T
SQL Server Engine
Hi all,
I'm having a little problem with my sqlserver 2005.
I'm trying to crate a user in Microsoft SQL Server Management Studio throug a query.
Like this:
-
USE [master]
GO
CREATE LOGIN [MYUSER] WITH PASSWORD=N'MYPASSWORD', DEFAULT_DATABASE=[MYDB],
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER LOGIN [MYUSER] ENABLE
GO
USE [msdb]
GO
CREATE USER [MYUSER] FOR LOGIN [MYUSER];
GO
GRANT EXECUTE ON [sp_start_job] TO [MYUSER]
GO
EXEC sp_addrolemember 'db_datareader', 'MYUSER'
GO
EXEC sp_addrolemember 'db_datawriter', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentOperatorRole', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentUserRole', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentReaderRole', 'MYUSER'
GO
Use [MYDB]
GO
CREATE USER [MYUSER] FOR LOGIN [MYUSER]
GO
EXEC sp_addrolemember 'db_datareader', 'MYUSER'
GO
EXEC sp_addrolemember 'db_datawriter', 'MYUSER'
GO
And drops the user by executing following:
Use [MYDB]
GO
EXEC sp_dropuser 'MYUSER'
GO
USE [msdb]
GO
REVOKE EXECUTE ON [sp_start_job] TO [MYUSER]
GO
EXEC sp_droprolemember 'SQLAgentOperatorRole', 'MYUSER'
GO
EXEC sp_droprolemember 'SQLAgentUserRole', 'MYUSER'
GO
EXEC sp_droprolemember 'SQLAgentReaderRole', 'MYUSER'
GO
EXEC sp_dropuser 'MYUSER'
GO
USE [master]
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'MYUSER')
DROP LOGIN [MYUSER]
-Then if I from a query, first creates the user, then drops the user, I can't create him again? I get's the following error when trying:
Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
I'f i then runs the createscript again, the user is created correctly.
Is there a way to avoid this foced lockout? Or how do i "refreash" my connection, without dataloss?
Best Regards
Henrik
Have you tried executing your scripts from sqlcmd instead of Management Studio? Are you seeing the same behavior?
Thanks
Laurentiu
Hi Cristofor,
Thanks for you reply. I'v just tried that and get following:
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
But under "Server Properties" - "Connections"
The "Allow Remote connections to this server" is checked, with a query timeout on 600
|||In some SQL Server SKUs the remote connection is disabled by default. Use SQL Server Configuration Manager to enable TCP/IP (or named pipes) on your SQL Server instance (requires to restart SQL Server service). Also make sure the Windows firewall is configured to allow SQL Server to accept connections.
-Raul Garcia
SDE/T
SQL Server Engine
|||Hi Raul,
I have just enabled this it was disabled, but still im getting the same error.
But by executeing the script localy from the database server, it works fine. And can do it as many times as i please, whithout errors.
like this:
sqlcmd -sMYDBSERVER -E -i "C:\drop.sql"
And
sqlcmd -sMYDBSERVER -E -i "C:\Create.sql"
But it still drops the connection to the user elsewhere, on the first time it's used after creation.
Eg. First i drop the user, then i create it again with the scripts. Then i open a browser and goes to my website, wich uses this user to connect to the database to retrive and update information. Then the first time i try to login i get the error below. Then i push "F5" to refreash the page and all works fine again?
My only guess is that your web application is somehow caching/reusing the old connection (with the old login), and when you refresh the page it reestablishes the connection. Remember that when you drop and create a login it is a whole new principal, not the same one (even if the name and password is the same), and any established connection will be invalidated and the connection will be forcedly terminated by the server the next time any application tries to use it.
I would recommend using SQL profiler to trace back new/closed connections as well as existing sessions to try to detect if this is indeed the case.
-Raul Garcia
SDE/T
SQL Server Engine
|||Hi,
Thats also what im comming to. I haven't tryed the SQL profiler yet, but will do this later.
But i have found out that it's not enough only to stop and start the web site again on IIS. I have to restart the hole "IIS" to make the "refreash". So im thinking that the session is not terminated correctly from the website, when closing the browser/session?.
|||Besides your investigation on why (and if) the web application is reusing the connection instead of reconnecting, I have one question for you: are you dropping and recreating this user in order to create a whole new principal (i.e. a new SID, with no existing objects, etc.), or just as a mechanism to prevent this principal from establishing a new connection?
If you are using these scripts to prevent new connections, you may also want to consider disabling the login instead. By disabling the login, you prevent new connections without affecting existing ones, and without the need to cleanup any objects or references (i.e. users) this principal may have in the system. You can find the syntax for this operation in ALTER LOGIN (http://msdn2.microsoft.com/en-us/library/ms189828.aspx).
-Raul Garcia
SDE/T
SQL Server Engine
|||
Actually im useing thise scripts as a part of my deploymentkit. This is to ensure that all securitysettings to any databases, stored procedures etc are droped. So i afterwards can replace them with either the same or new ones, without leaving anything behind.
But i took the change and implemented it into my deployment. The first time i got the same error about the "transport-level error". But that was because i've missed another scipt later in the installation, it was some inserts-stament i tried to execute from a vb SqlConnection. Then i moved those two things around, so it was the last code and then it worked fine, i can now open my webpage without the "transport-level error" :) yeah .. even after installing, uninstalling, installing ....
So i have to go back to the websession-closing, in my case, because under the "Activity Monitor" in sql, i can see a connection long after closing the browser. So this is now another problem for me, to find a way to kill this one.
But im stil interested in fixing the other problem, because it's annoying that things has to come in the correct order.. And the differens between executing it from a sql-query (in sql 2005), from command promt and from my msi-package. -Wich by the way executes it by calling a shell in vb wich runs a .cmd-file containing the two sql-scripts.
sqlcmd -sMYDBSERVER -E -i "C:\drop.sql" -o "C:\DropDBUser.log"
sqlcmd -sMYDBSERVER -E -i "C:\Create.sql" -o "C:\CreateDBUser.log"
But for the moment im happy :)
|||
I’m glad to hear that. Feel free to let us know if you have further security questions in SQL Server, we are always looking forward to help our customers whenever we have a chance.
-Raul Garcia
SDE/T
SQL Server Engine
sqlHi everyone,
I′m starting using SQL 2005 on visual studio 2005, and I have a question:
I have a table named employees in which I have 2 columns, one named "last used" which has the smalldatetime data type and the other named "salary" which has the decimal data type.
Now, I want to use a Select statement, to display in a new table 2 new columns, one with the sum of the salaries that correspond to the current month and the other with the sum of the values that belong to the previous month. I mean by this, all the salaries from july should be added up and be displayed in one column and all the ones from june should be added and displayed in the other column.
Can someone help me please?
Thanks in advance
Using this data set
..and this query
select
sum(case month(lastused) when month(getdate()) then salary end) as CurrentMonthSalary
,sum(case month(lastused) when month(getdate()) -1 then salary end) as PrevMonthSalary
from dbo.salary
...I return these results
I believe that's what you are looking for.
Tim
I have Visual Studio 2005 Team Developer. I don't need SQL2005 since I have SQL2000. How do I get Report Designer/Builder for VS2005? I would like to use the Report Designer/Builder to build reports for Windows applications. Do I HAVE to buy SQL 2005?
Thanks.
Reporting services is an add-on for SQL 2000, but it is available. You can download a 120-day evaluation version here:
http://www.microsoft.com/downloads/details.aspx?familyid=BA517C01-2E2F-4BC7-84AF-149B7637F807&displaylang=en
|||I don't want SQL2000 I already have SQL2000. What I want to find is the Report Builder/Designer to use with VB programs within Windows. It is suppose to plug into Visual Studio 2005 which I have but I don't want to spend the money for SQL2005 just to get the plug in. I don't need Report Server just the Report Designer/Builder.
Jim
|||Report Builder is included with Reporting Services. For example, when I load my SQL Server 2005 Reporting Services site and click Build Reports, it launches the Report Builder.|||It is also an snap-in to Visual Studio. Everything I have read is that you use it to develop reports for Windows Apps, which is what I want to do. I don't use SQL as a database generally. I normally use Access because they are small apps. But I would like to use the Report Builder/Designer to develop these app reports. That is where I'm trying to get to.
If I can get Report Builder/Designer into Visual Studio2005 then I can write reports for Windows Apps which they say you can do and is a replacement for Crystal. Also that you don't need SQL or SSRS to do this.
I just don't know where to get the snap/plug-in.
