Microsoft SQL Server 2005

71K

Solutions

25K

Contributors

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Share tech news, updates, or what's on your mind.

Sign up to Post

I have a column that I need to always be 4 characters

It may at timesbeb 2 or 3

In thise cases I need to pad out to the left some zeroes.
0
Free Tool: Port Scanner
LVL 9
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Hi Experts,

I have a subreport where I pass 2 parms:
=iif(Parameters!SelectFacilityCode.Value = "CAS-VHA","MOR",Left(Parameters!SelectFacilityCode.Value, 3))
And
=First(Fields!Region.Value, "HealthCheckDetails")

The subreport works fine when there is a record/data for each of the parms

But

When there is no record/data I get error msg about: "One or more parameters were not specified for the subreport".

On "CAS-VHA" there happens to be data if we look at "MOR" and this works fine.  But this is not always the case for other FacilityCode(s).

I need to find a way around this.

Looking for a way to send zero's to the subreport when there is no record.  And to not have this error come up.  Maybe something like, if no record then FacilityCode = "non-VHA" and on the subreport have if FaclityCode = "non" then Fields = 0.  I also see that this passed parm is coming from a dropdown selection from a user.  So with that said I would need something like:
=SWITCH(ISNOTHING(Sum(Fields!FacilityCode.Value)), Nothing,
(Sum(Fields!FacilityCode.Value)) = 0, Nothing,
(Sum(Fields!FacilityCode.Value)) <> 0, "Subreport.rdl")

But that example is for when there is nothing then do nothing.  I want if there is nothing then do subreport and then on subreport when nothing have zero's display in the fields where normally there are values.  So something like:
=SWITCH(ISNOTHING(Sum(Fields!FacilityCode.Value)), "iif(Parameters!SelectFacilityCode.Value = …
0
Hi there,
  I am in process of configuring logon trigger.
My need for this trigger is to restrict few SQL logins to only login from particular IP address:

I  got the below code from google, but when I setup the trigger, first time the user can able to login but next time when we disconnect and try again then the trigger is not letting to login the sql server even though we tried from the same machine(same IP address)

CREATE TRIGGER [TR_check_ip_address]
ON ALL SERVER
FOR LOGON
AS
BEGIN

    DECLARE @ip_addr varchar(48)

    SELECT @ip_addr = client_net_address
    FROM sys.dm_exec_connections
    WHERE session_id = @@SPID

    IF ORIGINAL_LOGIN() = 'Test' AND @ip_addr <> 'xxx.xx.xxx.xx'
        ROLLBACK;

END

thanks
Deepak
0
Can anyone give me step-by-step instructions on how to just get this task to successfully execute my stored procedure.  The error messages are getting worse
  • "Value does not fall within the expected range.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly"
  • "The query failed to parse. Attempted to read or write protected memory. This is often an indication that other memory is corrupt."
  • "The EXEC SQL construct or statement is not supported."

At this point, I'd just like to get the stored procedure working.  All it's doing is having a parameter string value passed into it from a user defined variable in the package and then assigning a value to the SP output parameter which then gets assigned to a different package user variable.

My environment:  Windows 7 Professional, SQL Server/Visual Studio 2005

Any suggestions?
0
I am having a problem with my production database and need to know how to fix this issue. This database is currently in use. The error I am getting when I run DBCC CHECKDB with NO_INFOMSGS is below:

Msg 8967, Level 16, State 216, Line 1
An internal error occurred in DBCC that prevented further processing. Contact Customer Support Services.
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.

I have ran DBCC CHECKDB with NO_INFOMSGS on the master, Tempdb, msdb, model and all came back fine but when I run it on my production database for our ERP system I get the above error. How can I fix this?
0
Hi,

I have some users in my company that access our Production SQL instance via SSMS. I would like to know if there a way to block all domain users from using SQL Management Studio, except domain admin, as domain admin has 'sysadmin' rights.

We have software that utilizing Integrated Security.  I would still like our users to be able to utilizing our software using the SQL Windows authenticate, but not able to logon via SSMS.

Thanks!!
0
Hi,

I'm using SSRS designing a report which will show
two different dates only at any one time
comparing activity type and customer engagement.

so like this:

Activity Type           Date1                 Date2
Running                   100                    150
Cycling                      200                    150

Currently I hold these three values in a table and I use SSRS matrix to display these values like the above.
I am trying to figure out how I can add details against each of the dates to show a % difference on activity week over week.
I'm thinking perhaps this can't be done using SSRS matrix?
Maybe  I need to hard code the design and "calculation" column?

What I would like this to look like is
Activity Type           Date1                 Date2         %Diff
Running                   100                    150               + 50%
Cycling                      200                    150              - 25%

Can anyone give me some guidance on this. I have spent time looking online, but I'm not seeing anything that is working for me.
Thank you in advance
P
0
Error:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Verified that all services were started.
Named pipes were enabled.
TCPIP were allowed.
0
We just upgraded a server running Windows 2003 and MS SQL server 2005 to Windows 2008.  This upgraded the IIS from version 6 to 7.  We were running Windows authentication for the SQL database and we have a number of ASP pages that worked fine under IIS6, but crash with an error 500 under IIS7.  The website reads data fine from the SQL database when but when attempting to write to it, that's when the webpage crashes with an error 500 and in the W3SVC1 folder under C;\Windows\System32\Logfiles, the logfile shows under the lines where it tried to post, an error of 80070005 Access Denied.  I am thinking it's not passing the Windows credentials to the SQL database but it's only doing it for one of the tables.  On other tables, it writes the data to the table, but then bombs out the rest of the page and gives the error 500 on the webpage.  Do I need to rewrite the webpages to be compatible with IIS7 or is there a way to make IIS7 compatible with my webpages?  Any suggestions?

Thanks
0
Thank you all in advance!
We have column (cyclesec) that stores time values in terms of seconds an an nvarchar.
I need to be able to convert this value to  'time'. ideally in the following format.

mm:ss

In other words, the column (cyclesec) currently stores a value of 00:10:00 as an nvarchar.
I need to convert it, move it to a new column (minsecCycletime), and make it show up as a "time" as such:

10:00

Thank you all in advance!
0

Microsoft SQL Server 2005

71K

Solutions

25K

Contributors

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.