Row Number Assigned Via RunningValue But Without Grouping

Jan 10, 2007

I have a report where certain columns have values that get repeated, but the client has a rigid requirement for not wanting these columns as groups in the reports (other programs and exports doing specific tasks with those values). In these reports, the "hide duplicates" value is checked, so as to give an aesthetic sense of grouping.

The data for the report, in raw form is:








Value 1
A

Value 1
B

Value 1
C

Value 2
D

Value 2
E

Value 2
F

Value 3
G

Value 3
Hetc...

In its aesthetic form, it is:








Value 1
A


B


C

Value 2
D


E


F

Value 3
G


H

My question is, how can I use RunningValue() to give me the following row numbers based upon those repeated values acting as grouping, since I'm not using formal grouping in the report design?











Value 1
1
A


2
B


3
C

Value 2
1
D


2
E


3
F

Value 3
1
G


2
H



Thanks for any input!

View 3 Replies


ADVERTISEMENT

Grouping Records && Assigning Sequential Number

Jul 20, 2005

I need to group records and assign a setid to the group. I have atable with data that looks like thisColA ColB94015 0106594016 0106594015 0108594015 0108633383 0091232601 00912I need to create a resultset using just sql to look like thisColA ColB GRP94015 01065 194016 01065 194015 01085 194015 01086 133383 00912 232601 00912 2The tricky part is resolving the many to many issue. A value in ColAcan belong to multiple values in ColB and a value in ColB can havemultiple values in ColA.

View 6 Replies View Related

RunningValue ?

Aug 1, 2006

I am having problems with the following -













this represents the dataset

Date
Customer
Voucher Number
Document Amount
Check Amount







7/15/2006
Company G
101
15.00
45.00

7/15/2006
Compnay G
101
25.00
45.00

7/15/2006
Compnay G
101
5.00
45.00

7/18/2006
Company A
102
35.00
35.00

7/25/2006
Company M
103
45.00
50.00

7/25/2006
Company M
103
15.00
50.00
















This represents the final report
this is what I am getting



Date
Customer
Voucher Number
Document Amount
Check Amount
Check Amount

(Hidden)
Group 1 Header = Voucher Number






(Hidden)
Detail
7/15/2006
Company G
101
15.00
45.00
45.00

(Hidden)
Detail
7/15/2006
Company G
101
25.00
45.00
45.00

(Hidden)
Detail
7/15/2006
Company G
101
5.00
45.00
45.00


Group 1 Footer
7/16/2006
Company G

45.00
45.00
135.00

(Hidden)
Group 1Header = Voucher Number






(Hidden)
Detail
7/18/2006
Company A
102
35.00
35.00
35.00


Group 1 Footer
7/19/2006
Company A

35.00
35.00
35.00

(Hidden)
Group 1 Header = Voucher Number






(Hidden)
Detail
7/25/2006
Company M
103
45.00
50.00
50.00

(Hidden)
Detail
7/25/2006
Company M
103
15.00
50.00
50.00


Group 1 Footer
7/25/2006
Company M
103
60.00
50.00
100.00


Report Footer

Grand Total:
140.00
130.00
270.00










(I am using a table)

I can get the correct total for the Group 1 Footer Check Amount by dividing the Total by a count of the Voucher Numbers, but I have not been able to add the Group 1 totals of the Check Amount for the Grand Total in the footer. I have made various attempts using RunningValue with the Group 1 Check Amount Total field, but always getting a error - mostly that the scope was not correct.

Any suggestions will be appreciated.

View 4 Replies View Related

RunningValue Problem

Nov 15, 2007

I have a question about using RunningValue.
My data has multiple records for one ID. But I only want to have a
runningtotal based on one value from each ID and grouping by area.


To illustrate my problem,


Area 1
ID 1 4
ID 1 4
ID 1 4
ID 1 4
ID 1 4


ID 2 1
ID 2 1
ID 2 1
ID 2 1


I would like to have the runningtotal to be equal to 5 and not 24.
If I use =RunningValue(Fields!Amt.Value, sum, "Area"), I will be
getting 24.


Currently I'm using a custom code to get the value in the ID group footer but I can't pass
the value to the "Area" group footer.
I tried using ReportItems to pass the value out but received an error.
The error message is given:
"The Value expression for the textbox 'textbox100' refers to the
report item 'textbox28'. Report item expressions can only refer to
other report items within the same grouping scope or a containing
grouping scope." I also tried using code.Total (Total is the global variable holding the sum) at the "Area" group footer but the value is "delayed". Meaning total of Area1 = 0, Area2 = Total of Area1 and so on.Any idea on how to shift the total up?

I also tried RunningValue(Fields!Amt.Value, MAX, "Area"), it only gives me
the maximum value in the Area group, not the running total of all the
maximum values of each ID.


As for RunningValue(Fields!Amt.Value, Count, "Area"), it gives me the
total rows in the Area group.


Is there a way to sum on distinct ID in the Area group? Or inserting
an iif condition in the runningvalue? I tried
Runningvalue(iif(First(Fields!ID.value), Fields!Amt.Value, Nothing),
sum, "Area") since I wanted the first value of each ID but it says it
cannot have an aggregate function within an aggregate.


Is there a method to write something along the line of >>> iif(Fields!
ID.Value = distinct, Fields!Amt.Value, Nothing).

Using Select Distinct in the query doesn't help because the other fields are different and I need to display all the records.



Can anybody help me?

View 3 Replies View Related

RunningValue Question

Jan 10, 2007

I have a form that uses some header information (like client ID with name, address, etc) and then uses a table for orders that customer has placed. I now need to print something like a page number (but not in the header or footer). When I try RowNumber, this does not work as the dataset is returning multiple records for the first customer and then the next item in the list (the next customer ID) skips the number of records from the first. So I need the first one to have 1, the second to have 2 and so on. I tries RunningValue, but for each record it starts back at 1. What I need is like a counter.

How would I go about this and thanks for the help.

View 7 Replies View Related

Using RunningValue Question

May 14, 2007

I have a couple of columns in a table where I would like to summarize the total value of that column in the group's footer. I also want to filter that total just by that column, so I am creating a RunningValue statement like this:



=RunningValue(iif(Fields!TypeClass=351154, Fields!ValueAmount.Value, 0), Sum, Nothing)



This works great with zero (0) values, however on the column that filters by the number 351154, the expression produces #error.



Is this because you cannot have a RunningValue in a group footer?



Thanks for the information.

View 1 Replies View Related

RunningValue() In Dataset

Jan 29, 2008

I've created a CalculatedField in my dataset, set to:

=RunningValue(Fields!Quantity.Value,Sum)

...however when I Preview the report, VS returns the error:

An error occurred during local report processing.
An internal error occurred on the report server. See the error log for more details

...and then blows up, requiring a restart. I've tried this on several different reports with same results.

I must have the RunningValue() as a Field for purposes of creating something like a ReverseRunningValue() (which adds from bottom-to-top instead of top-to-bottom). This stems from the need to perform a Previous() call, which requires a field as a parameter. (If I could use Previous() to refer to a textbox object, then I could put RunningValue() in a texbox instead, but this doesn't seem to work).


Any ideas?

Thx,
Mojo

View 1 Replies View Related

Multiple RunningValue

Nov 29, 2007

Hello,

I am trying to create sequential running totals based on the months. I have included the two expression I've been trying to use. I have one group created called table1_MO_NUMBER and four sequential fields that need individual running totals. I am using Reporting Services 2005. The Totals column is the result of the expression. I added an additional column that displays what the running totals should be. Any suggestions would be helpful. Barb













JANUARY










1
MTD NET ACTUAL
2,235





2
PLAN
3,158





3
FCST
3,200





4
LY MTD NET ACTUAL
1,853
Totals
Totals should be.





Act Bldgt
10,447
2235
iif(Fields!SEQ_NO.Value=1,RunningValue(Fields!BLDGT.Value, sum, Nothing ),0)



JANUARY RunningTotals
Plan Bldgt
#Error
3158
RunningValue(iif(Fields!SEQ_NO.Value=2, Fields!BLDGT.Value, 0), sum, Nothing)




Fcst Bldgt

3,200





Pr Bldgt

1,853


FEBUARY






1
MTD NET ACTUAL
2,512





2
PLAN
3,580





3
FCST
3,200





4
LY MTD NET ACTUAL
5,744







Act Bldgt
25,483
4748




FEBUARY RunningTotals
Plan Bldgt
#Error
6739





Fcst Bldgt

6,400





Pr Bldgt

7,597


MARCH






1
MTD NET ACTUAL
3,887





2
PLAN
3,780





3
FCST
3,200





4
LY MTD NET ACTUAL
4,494







Act Bldgt
40,843
8635




MARCH RunningTotals
Plan Bldgt
#Error
10518





Fcst Bldgt

9,600





Pr Bldgt

12,091

View 4 Replies View Related

Using RunningValue In Charts With Subreports

Jul 3, 2007

Hi,
I am creating some earned value reports that display project budget, actual cost to date, projected cost, and earned value. I have all of the data stored by month so I am using the Running Value function to display the data on a graph such that at any given point on the graph, it is showing total dollars as of that date. This works great for a single project or a summation of projects.

I also want the ability to create a batch set of reports for a series of projects, basically they input a list of project numbers, and a new graph is generated for each one. To do this, I created a "batch report" that has a subreport in a table's group with project id being the grouped on field, it passes that project id to the subreport as input.

I get exactly what I want - a new report for each project, but the problem is, running value on the graphs is not working across the projects. It's fine for the first project, but all subsequent projects are getting messed up. For instance, one project with a max budget of 200K is showing with a max budget of 400K, actuals are getting nulled out, etc. It's as if for some reason, the RunningValue isn't getting reset across the subreport....

An example of my value field on one of the lines (not copy and pasted so forgive any syntax errors - I'm not on my development box):





Code Snippet= Iif(MAX(Fields!actual_de.Value + Fields!actual_work.Value) >0, RunningValue(Fields!actual_de.Value + Fields!actual_work.Value, Sum, FakeSeries), Nothing)



(The point of the max is so that the graph stops on months without actuals - that is months that haven't occured yet)

My FakeSeries is just on projectID and is just for the purpose of resetting the Running Value (or so I thought).

Does anyone have any input or advice on how to get this working? Why is RunningValue getting messed up across subreports? If I only give the batch report one project ID, the data is great. (I know it's the running value because if I take out running value and just graph the SUM - all the values are correct). If anyone has an alternate way of doing this sort of batch report I'm all ears as well!

Thanks much!
Jen

View 1 Replies View Related

Sum Or RunningValue Incorrect From Group Footer

Apr 22, 2008

I've read a bunch of threads and can't seem to find an exact problem as what I'm having.

I believe my problem is simple enough to understand, but might not be that simple to solve. I'm hoping otherwise.

My report shows columns:

CustomerNumber
CustomerName
SummedInvoiceAmounts (summed from SQL query)
Region

The problem is that we have the same CustomerNumber with multiple CustomerNames:

CustomerNumber CustomerName Sum Region
ABC advancedballoon 1000 East
ABC AdvancedBall. 1000 East

As you can see they are the same company but in our Accounting system they show up mulitple times (about 20 companies like this). This is from clerks adding in multiple ways or misspelling, etc. Can't fix, but have to work around.

Anyhow, I have my detail row that shows two lines for the companies like this and one line for the companies that are correct. I have this report I have added two groups "Region", "CustomerNumber".

If I put all the detail Fields down in the CustomerNumber footer and hide the detail row the report looks fine to the eye. When adding a Sum down in the Region footer field though; it pulls in the added amount and makes the total wrong.

I tried running value, but it's basically the same as the sum for what I need and it doesn't work. Is there a simple way to do this using the reporting tool? In Crystal this was very easy, but I haven't figured this out yet. Any help would be greatly appreciated. Thanks.

View 1 Replies View Related

Transaction Rollback Is Used Before It Is Assigned A Value

Apr 6, 2006

I am moving some code that was created in visual studio 2002 into Visual Web Developer 2005 express edition and am getting a warning on some of my code before compilation.
The warning is "Variable lSqlTransaction is used before it has been assigned a value.  A null reference exception could result at runtime".  I experienced no problems in vs2002.
The offending line is the rollback command within the exception.  If I move it out of the exception it no longer flags the warning, if I move it after the throw statement it now longer flags the warning.  Any help appreciated.
Heres the code
  Public Shared Sub ExecuteNonQuery(ByVal lSQLCommand As SqlCommand)

Dim lSQLConnection As New SqlConnection(SQLConnString(enmSQLDB.enmSQLDB_TPSDB))
Dim lSQLTransaction As SqlTransaction

Try
'-----------------------------------------------------
'Try and open the database
'-----------------------------------------------------
lSQLConnection.Open()

'-----------------------------------------------------
'Create a transaction
'-----------------------------------------------------
lSQLTransaction = lSQLConnection.BeginTransaction

'-----------------------------------------------------
'Assign the connection and transaction to our object
'-----------------------------------------------------
lSQLCommand.Connection = lSQLConnection
lSQLCommand.Transaction = lSQLTransaction

'-----------------------------------------------------
'Execute the stored proc
'-----------------------------------------------------
lSQLCommand.ExecuteNonQuery()

'-----------------------------------------------------
'Use the transaction to commit the changes
'-----------------------------------------------------
lSQLTransaction.Commit()

Catch ex As Exception
'-----------------------------------------------------
'If any errors have been thrown then roll back without
'commiting....
'-----------------------------------------------------
lSQLTransaction.Rollback()

Throw New Exception("An error has occured whilst trying to update the database. " & _
"Your changes have not been saved.", ex)


Finally
If lSQLConnection.State = ConnectionState.Open Then
lSQLConnection.Close()
End If
End Try

End Sub
 

View 1 Replies View Related

Filegroup %f Has No Files Assigned To It.

Nov 20, 2007

I'm tryin to copy information from a fact table from one server to another server but when try using a DTS package to do this, an error pops up that says "the Filegroup <filegroup_name> has no files assigned to it."

what am i missing here?

View 4 Replies View Related

Trigger-assigned Value Changes When Row Replicates - Why?

Feb 14, 2008

Greetings,

We have a standard audit trigger on one of our tables. The trigger type is "after insert, update". The trigger populates four table columns, telling us the login and time the row was created and last updated.

We use replication to synchronize three servers. The trigger specifies "not for replication" on all servers.

The code used to grab the identity and time of the last update is shown below.


LastEditedDate = GETUTCDATE(),

LastEditedBy = SUSER_SNAME()

What I observe is that the LastEditedBy value is sometimes different for the same row on different servers even though the time value is identical. I don't know how to explain this. It looks like the login value is being recalculated during replication while the edit time is not.

For the record, the correct login will be something like "MYDOMjoe" while the incorrect value on another server will read "NT AUTHORITYNETWORK SERVICE". Do these symptoms ring a bell with anyone?

Thanks,
BCB

View 5 Replies View Related

SSIS Assigned Datatype

Apr 23, 2007

I have a problem with datatype mismatches. In my Data Flow I am reading a Flat File that includes a DateOfBirth which SSIS correctly assigns a datatype of [DT_DATE]. I need to check to see if the record has previously been submitted, so I do a Lookup that uses a SQL statement that combines fields (LastName, Zip & BirthDate) from three different tables. My problem is that SSIS assigns a datatype of [DT_DBTIMESTAMP] to BirthDate, which I can't use because of the mismatch. I have tried every form of re-formating the field in SQL that I can think of, but it still gets assigned that DT. And I'm unaware of a way of changing the DT once SSIS assigns it in the Lookup task.



Help!...very frustrated,

Dave

View 3 Replies View Related

Query Or Grouping Problem (some Kind Of Parallel Grouping?)

Nov 26, 2007

I'm really stumped on this one. I'm a self taught SQL guy, so there is probobly something I'm overlooking.

I'm trying to get information like this in to a report:

WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Detail #etc
WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Parts #etc

I'm unable to get the grouping right on this. Since the line details and line parts both are children of the line #, how do you do "parallel groups"?

There are 4 tables:

Work Order Header
Work Order Line
Work Order Line Details
Work Order Line Requisitions

The Header has a unique PK.
The Line uses the Header and a Line # as foreign keys that together are unique.
The Detail and requisition tables use the header and line #'s in addition to their own line number foreign keys. My queries ends up looking like this:

WO WOL WOLR WOLD
226952 10000 10000 10000
226952 10000 10000 20000
226952 10000 10000 30000
226952 10000 10000 40000
226952 10000 20000 10000
226952 10000 20000 20000
226952 10000 20000 30000
226952 10000 20000 40000
399999 10000 NULL 10000
375654 10000 10000 NULL
etc


Hierarchy:
WO > WOL > WOLD
WO > WOL > WOLR

It probobly isn't best practice, but I'm kinda new so I need some guidance. I'd really appreciate any help! Here's my query:

SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No,
[Work Order Requisition].[Line No_] AS WOLR_No, [Work Order Line Detail].[Line No_] AS WOLD_No
FROM [Work Order Header] LEFT OUTER JOIN
[Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN
[Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_] LEFT OUTER JOIN
[Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]

View 1 Replies View Related

Can A Value Be Assigned To An Output Parameter And Be Used In A Where Clause?

Dec 18, 2006

I need to update a table using a stored procedure that "UPSERTS". In this case, @OfficeId is passed with ParameterDirection.OutputSo when I update I assign the value to OfficeId like this (the office id value is being correctly assigned): SqlParameter OfficeIdParam = new SqlParameter("@OfficeId", SqlDbType.Int, 4);            if (_officeId > 0) { OfficeIdParam.Value = _officeId; }            OfficeIdParam.Direction = ParameterDirection.Output;   The following segment of the stored procedure is throwing an error that the OfficeId does not exist: Update --- @OfficeId int  output if not exists (select 1 from ORG_Office where OfficeId=@OfficeId)    begin        RAISERROR ('OfficeId does not exist in ORG_Office: E002', 16, 1) -- OfficeId does not exist.        return -1    end May this approach work, and if the operation turns out to be an Insert I may do this:select @error_code = @@ERROR, @OfficeId= scope_identity() Thank you,jspurlin 

View 1 Replies View Related

Multiple Variables Assigned To One Select

Apr 6, 2006

Hello,
Is there a way to assign multiple variables to one select statement as in the following example?
DECLARE @FirstName VARCHAR(100)
DECLARE @MiddleName VARCHAR(100)
DECLARE @LastName VARCHAR(100)
@FirstName, @MiddleName, @LastName = SELECT FirstName, MiddleName, LastName FROM USERS WHERE username='UniqueUserName'
 
I don't like having to use one select statement for each variable I need to pull from a query.  This is in reference to a stored procedure.
 
Thank you!
Cody

View 1 Replies View Related

How Are Device Cals Managed And Assigned.

Aug 21, 2007

Hello all,

This seems like a simple question but I still haven't been able to get an answer from Microsoft Canada. We're looking at buying 75 device cals to connect to a single instance of SQL Server Standard Edition. Our question is on how those licenses are assigned and/or activated.

Do we need to manually do something for each station or is it an automated process done the first time a station connects to the server? We also wat to understand what happens when a workstation dies and is replaced by another one. Do you have to do something on the server to transfer the license to the new station ?

In other words, when using device cals (and/or user cals), do you have to do any type of license management or is it all transparent ?

View 1 Replies View Related

Why Is Severity 20 Assigned To SSPI Errors

Aug 28, 2007

At our site, we page DBAs whenever an alert with a severity of 20 or greater is raised. This never was an issue until SQL Server 2005. Now every SSPI error is paged since it is a severity 20. I don't believe the errors are severe enough to qualify an assignment of 20. Anybody else have any thoughts on this?

Thanks.

View 4 Replies View Related

Rights Assigned To WindowsPower Users In SQL Express.

Nov 9, 2005

Hi,
In SQL Server Express Edition, what are the rights that are assigned to a Normal Windows User and PowerUser by default ?
When I install SQL Express on a clean machine and login as Power User I can add/edit/delete data but when I login as Normal Windows User I can see the data but not change it.
Please help me in this regard.

View 1 Replies View Related

Has SELECT INTO With IDENTITY() Function Changed The Way #'s Are Assigned?

Oct 10, 2007

We have SELECT INTO queries that use the IDENTITY function to assign a sequential row number to a result set based upon a sort order. This has been helpful in SQL Server 2000 for median determination. It appears, however, that in SQL Server 2005, the row numbers are not assigned sequentially, or maybe they are assigned before the sort order is applied.

Can anyone verify whether the IDENTITY function has changed behavior between 2000 and 2005? We would prefer not to have to make changes to existing queries. Thank you.


(BTW, the workaround we found so far is to put the initial SELECT...ORDER BY in a subquery, but then we had to include a phrase like TOP 10000000 to pass syntax check. Is there a better way?)

View 4 Replies View Related

How Do I Change The Status On My Database? Currently Is Is Assigned As Restricted User

Jun 19, 2008

I restored a  sql server database today. There is a problem though. In SQL server, right next to the name of the database are the words "Restricted User". Their seem to be some limitations on my access to this database now as I am not able to run some of my C# code against the database. How can I fix this in SQL server so that I am not a Restriced user.
 
Example of what I am talking about. In SQL server my database is named Gorlaz. Just to the left of this is a yellow Icon that represents the database "Gorlaz". For illustration purposes I will use the character "$" to substitute for the yellow database icon. So this is how it reads in SQL server management studio.
$Gorlaz(Restricted User)
 

View 3 Replies View Related

Restrict Role To CREATE TABLE On An Assigned SCHEMA

May 9, 2008

Can anyone help me with this? The objective is to allow an application user (with db_datareader & db_datawriter database roles assigned) to be able to create tables in the assigned schema (dbo) via a new role.


-- Create User

use master

go

create login DBA with password='xx', CHECK_EXPIRATION=ON, CHECK_POLICY=ON

use AdventureWorks

go

create user dba from login DBA
alter user dba with DEFAULT_SCHEMA=dbo

go

-- Create Role

use AdventureWorks

go

create role sp_ddl_role AUTHORIZATION dbo

grant CREATE TABLE to sp_ddl_role



-- assign user to Role

use AdventureWorks

go

exec sp_addrolemember sp_ddl_role, dba

go

-- Create Table statement then run with following error
Error:

Msg 2760, Level 16, State 1, Line 1

The specified schema name "dbo" either does not exist or you do not have permission to use it.

Remedy: Grant ALTER on schema::dbo to sp_ddl_role

My problem is that I want to restrict user permissions via this role to just the CREATE TABLE and granting ALTER on a SCHEMA will open up a whole lot more permissions from a security standpoint.

Question: How do I restirct this role to just the CREATE TABLE within a SCHEMA?

View 1 Replies View Related

Guest Principal Assigned To Datareader Role Does Have Access

Mar 1, 2007

Hello (help),

In SQL2000, when the Guest account was assigned into a role, such as db_datareader, then querying across databases worked just fine.

Specifically:
I have a Report Writer application that connects to the SQL Server with a login (ReportRunner) that actually has very limited permissions on a database.
The connection is then set (sp_setapprole) to use an Application role (App_RR) that has the necessary permissions.
The report-writer app calls a Stored Procedure that gathers data from several other databases (on the same SQL instance).
In SQL 2000, accessing these other databases was done through Guest - we assigned Guest to the db_datareader role. All worked fine.

We've just upgraded to SQL2005: reports started failing. It seems that although guest is assigned to the db_datareader role, the permissions for Guest don't allow selecting from tables via the db_datareader role: we've had to GRANT SELECT TO Guest specifically on the tables necessary for the report.

Is anyone aware of a design change withing SQL Server such that the Guest principal's roles are disregarded when assessing permission? Is there a new and better way to structure the permissions?

Thanks in advance for your help.
Mark Starr

View 9 Replies View Related

SSIS File System Path Assigned Randomly

Jul 19, 2006



We have been storing packages in the File System folder. We had noticed that there seemed to be times when re-importing an existing package did not seem to update it properly. We tried deleting the existing package first rather than overwriting it, but to no avail.

Today we noticed that there were two DTS90Packages folders, one on the C drive and one on the D drive. The dates on the files in those folders showed that sometimes the import put the file on one drive, and other times on the other drive.

The MsDtsSrvr.ini.xml file shows this: "<StorePath>..Packages</StorePath> ".

We intend to stored the packages in the msdb database instead in order to work around this problem.

Has anyone else noticed this happening?

Thanks,

Ron

View 3 Replies View Related

Determine User Assigned Rights To Public Role

Oct 10, 2007

Hi:

When I restore DB from testing to production, we want to remove extra access rights granted to public group. Is there a simple way to query to find out for which objects (table, view, sp, fn) that public group were granted select, delete, update insert, or execute rights?

My objective is to write a sp to remove all user assigned rights to public group (role), but not to deny any rights. How to do it?


Any suggestion will be appreciated.

View 1 Replies View Related

Accessing Windows Users Assigned To A Database Role

Mar 3, 2006

Is
there a way to find a list of Windows User accounts that are directly
or indirectly (through Windows Group membership) assigned to a database
role?


I could put work in to CLR programming or using a Linked Server to
Active Directory, but if there is a sys.* view available that can
provide me this
information directly it would be much easier.



I'll be looking into this further myself anyway and posting an answer
if I can find one, but if anyone has suggestions... well, thanks!

View 1 Replies View Related

Login Failed Using SQL Server Login For Assigned Script Componet Connection String

Oct 14, 2007



Hi,

I have a script component. What it does, it queries the a table using a connectionstring assigned to it in the Connection Managers Editor (which is an ado.net adapter). this works fine when i'm using a windows login in the ado.net connection manager. But when i changed my connection to use SQL server login, I encounter this error:



OnError,,,Add new records to Dim_T_Status (Case),,,10/14/2007 5:54:47 PM,10/14/2007 5:54:47 PM,-1073450910,0x,System.Data.SqlClient.SqlException: Login failed for user 'CS_REPORT'.
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)
OnError,,,LOAD AND UPDATE OCEAN Dimension Tables,,,10/14/2007 5:54:47 PM,10/14/2007 5:54:47 PM,-1073450910,0x,System.Data.SqlClient.SqlException: Login failed for user 'CS_REPORT'.
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)
OnError,,,LoadOCEANDimensions,,,10/14/2007 5:54:47 PM,10/14/2007 5:54:47 PM,-1073450910,0x,System.Data.SqlClient.SqlException: Login failed for user 'CS_REPORT'.
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)

How to go about this?

cherriesh

View 3 Replies View Related

The Number Of Requests For XXXServerXXXUser Has Exceeded The Maximum Number Allowed For A Single User

Oct 15, 2007



I have created a local user on Report Server Computer and the user has the administrative rights.
When i try to connect Report Server (http://xxx.xxx.xxx.xxx/reportserver) with this user's credantials. (ReportServer directory security is set -only- to Basic Authentication. ).
I get the following error.


Reporting Services Error
--------------------------------------------------------------------------------

The number of requests for "XXXServerXXXUser" has exceeded the maximum number allowed for a single user.
--------------------------------------------------------------------------------
SQL Server Reporting Services


Then i try to login using a different user with administrative rights on the machine, i can logon successfully.
The system is up for a month but this problem occured today?!? What could be the problem?!?

View 2 Replies View Related

How To Enter More Number Of Rows In A Table Having More Number Of Columns At A Time

Sep 24, 2007

Hi

I want to enter rows into a table having more number of columns

For example : I have one employee table having columns (name ,address,salary etc )
then, how can i enter 100 employees data at a time ?

Suppose i am having my data in .txt file (or ) in .xls

( SQL Server 2005)

View 1 Replies View Related

Transact SQL :: How To Select A Number Less Or Equal Than A Supplied Number

Jun 23, 2015

Got this query and I need the following result;

declare @NumberToCompareTo int
set @NumberToCompareTo = 8
declare @table table
(
number int
)
insert into @tableĀ 
select 4

[Code] ....

The query selects 4 and 5 of course. Now what I'm looking for is to retrieve the number less or equal to @NumberToCompareTo, I mean the most immediate less number than the parameter. So in this case 5

View 4 Replies View Related

How To Change A Decimal Number To Percent Format Number?

Oct 8, 2006

in my sql, i want to change a decimal number to percent format number, just so it is convenient for users. for example there is a decimal number 0.98, i want to change it to 98%, how can i complete it?

thks

View 4 Replies View Related

Limitations In Term Of Number Of Tasks And Number Of Columns

Jun 5, 2007

Hi,

I am currently designing a SSIS package to integrate data into a data warehouse fact table. This fact table has about 70 columns among which 17 are foreign keys for dimension tables.

To insert data in that table, I have to make several transformations and lookups. Given the fact that the lookups I have to make are a little complicated, I have about 70 tasks in my Data Flow.
I know it's a lot, but I can't find a way to make it simpler. It seems I really need all these tasks.

Now, the problem is that every new action I try to make on the package takes a lot of time. At design time, everything is very slow. My processor is eavily loaded each time I change a single setting in one of the tasks, and executing the package in debug mode takes for ages. If I take a look at the size of my package file on disk, it's more than 3MB.

Hence my question : Are there any limitations in terms of number of columns or number of tasks that can be processed within a Data Flow ?

If not, then do you have any idea why it's so slow ?

Thanks in advance for any answer.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved