Help on Script Component assignment of output variable

Hi all,

Actually I′m working with the beta 2 of the Sql Server 2005 with SSIS. And it′s great fun! But I′m now experience a problem:

I′m working with a script component. In that script component I would like to assign a value to a specific column ("Row.Formula"). The type of "Row.Formula" is Unicode text stream [DT_NTEXT]. Whenever I try to assign a byte array or a string to that field I am getting a compliation error. I have to assign a variable with the type "blobcolumn". But I cannot initialize this variable because "blobcolumn" has no public constructor.

Any thoughts on that?

The code sample:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Avanade.AMCS.DataProcessorReplacement.Utility

Public Class ScriptMain Inherits UserComponent

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
Dim wholeRowBuffer As Byte()
Dim columns() As String

wholeRowBuffer = Row.WholeLine.GetBlobData(0, CInt(Row.WholeLine.Length))

columns = ScriptComponentHelper.RetrieveStringArrayOutOfBuffer(wholeRowBuffer)

Row.Name = columns(1)
Row.MenuName = columns(2)
Row.CascadeName = columns(3)

Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(columns(4))

'Conversion compliation error - cannot convert from byte array to 'Microsoft.SqlServer.Dts.Pipeline.BlobColumn'

Row.Formula = buffer

End Sub

End Class

To access BLOB data in Script component, please use AddBlobData and GetBlobData.

For example:

Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(columns(4))

Row.Formula.AddBlobData(buffer)

|||

---

Imports System
Imports System.Xml
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim i As Integer
Dim CoverNote As String()

CoverNote = Split(Row.CoverNote.ToString, "^")

For i = 0 To UBound(CoverNote)
With Output0Buffer
.AddRow()
.Key = Row.Key
.AgentCode = Row.AgentCode
.TransactionDate = Row.TransactionDate
.Branchcode = Row.BranchCode
.BatchNo = Row.BatchNo
.NoOfCoverNotes = Row.NoOfCoverNotes
.TotalGrossPremium = Row.TotalGrossPremium
.MedicalGrsPrem = Row.MedicalGrsPrem
Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(CoverNote(i))
.CoverNote = Row.CoverNote.AddBlobData(buffer)
End With
Next
End Sub
End Class

The above scripting which highlightted wtih red color was encounter error.
The error msg : Expression doed not produce value.
Note: sources column for CoverNote is text data type which contain a lot multivalue.
example 001^002^003^004^005

Anyonce know how to write the correct scripting for handle such column with text data type.I dont have any idea for this case with text data type.

Thanks in advance.

|||

The line should just read

Row.CoverNote.AddBlobData(...

So remove the assignment part of the highlighted statement; AddBlobData() returns nothing.

|||Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim i As Integer
Dim CoverNote As String()

CoverNote = Split(Row.CoverNote.ToString, "^")

For i = 0 To UBound(CoverNote)
With Output0Buffer
.AddRow()
.Key = Row.Key
.AgentCode = Row.AgentCode
.TransactionDate = Row.TransactionDate
.Branchcode = Row.BranchCode
.BatchNo = Row.BatchNo
.NoOfCoverNotes = Row.NoOfCoverNotes
.TotalGrossPremium = Row.TotalGrossPremium
.MedicalGrsPrem = Row.MedicalGrsPrem
Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(CoverNote(i))
Row.CoverNote.AddBlobData(buffer)
End With
Next
End Sub
End Class

Thanks jaegd.

I had try to modify the code as per suggested.

Other columns output were fine and ok, just CoverNote result output was NULL value.

Any other script I miss out and need to add in or modified to able support this CoverNOte with text data type?

Appreciate for any help.

|||

What is the pipeline data type of the input column Row.CoverNote -- DT_TEXT,DT_NEXT, or something else?

|||The pipeline data type of the input column Row.CoverNote is DT_TEXT|||

Since the source pipeline type is DT_TEXT , before you can meaningfully called Split() on the character data contained in the blob, convert the pipeline data type to a .NET String first. Retrieval of DT_TEXT data is performed by calling GetBlobData() on the named/typed accessor and then decoding the array of bytes returned.

In other words, replace the line

CoverNote = Split(Row.CoverNote.ToString, "^")

with

Dim blobLength As Int32 = Convert.ToInt32(Row.CoverNote.Length)

Dim blobData() As Byte = Row.CoverNote.GetBlobData(0, blobLength)

Dim blobCodePage As Int32 = Row.CoverNote.ColumnInfo.CodePage

Dim joinedCoverNote As String = Text.Encoding.GetEncoding(blobCodePage).GetString(blobData)

CoverNote = Split(joinedCoverNote, "^")

|||

I had tried the sripting as per suggested.

The problems had been solved.

Thanks for your helping.

Help on Script Component assignment of output variable

Hi all,

Actually I′m working with the beta 2 of the Sql Server 2005 with SSIS. And it′s great fun! But I′m now experience a problem:

I′m working with a script component. In that script component I would like to assign a value to a specific column ("Row.Formula"). The type of "Row.Formula" is Unicode text stream [DT_NTEXT]. Whenever I try to assign a byte array or a string to that field I am getting a compliation error. I have to assign a variable with the type "blobcolumn". But I cannot initialize this variable because "blobcolumn" has no public constructor.

Any thoughts on that?

The code sample:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Avanade.AMCS.DataProcessorReplacement.Utility

Public Class ScriptMain Inherits UserComponent

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
Dim wholeRowBuffer As Byte()
Dim columns() As String

wholeRowBuffer = Row.WholeLine.GetBlobData(0, CInt(Row.WholeLine.Length))

columns = ScriptComponentHelper.RetrieveStringArrayOutOfBuffer(wholeRowBuffer)

Row.Name = columns(1)
Row.MenuName = columns(2)
Row.CascadeName = columns(3)

Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(columns(4))

'Conversion compliation error - cannot convert from byte array to 'Microsoft.SqlServer.Dts.Pipeline.BlobColumn'

Row.Formula = buffer

End Sub

End Class

To access BLOB data in Script component, please use AddBlobData and GetBlobData.

For example:

Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(columns(4))

Row.Formula.AddBlobData(buffer)

|||

---

Imports System
Imports System.Xml
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim i As Integer
Dim CoverNote As String()

CoverNote = Split(Row.CoverNote.ToString, "^")

For i = 0 To UBound(CoverNote)
With Output0Buffer
.AddRow()
.Key = Row.Key
.AgentCode = Row.AgentCode
.TransactionDate = Row.TransactionDate
.Branchcode = Row.BranchCode
.BatchNo = Row.BatchNo
.NoOfCoverNotes = Row.NoOfCoverNotes
.TotalGrossPremium = Row.TotalGrossPremium
.MedicalGrsPrem = Row.MedicalGrsPrem
Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(CoverNote(i))
.CoverNote = Row.CoverNote.AddBlobData(buffer)
End With
Next
End Sub
End Class

The above scripting which highlightted wtih red color was encounter error.
The error msg : Expression doed not produce value.
Note: sources column for CoverNote is text data type which contain a lot multivalue.
example 001^002^003^004^005

Anyonce know how to write the correct scripting for handle such column with text data type.I dont have any idea for this case with text data type.

Thanks in advance.

|||

The line should just read

Row.CoverNote.AddBlobData(...

So remove the assignment part of the highlighted statement; AddBlobData() returns nothing.

|||Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim i As Integer
Dim CoverNote As String()

CoverNote = Split(Row.CoverNote.ToString, "^")

For i = 0 To UBound(CoverNote)
With Output0Buffer
.AddRow()
.Key = Row.Key
.AgentCode = Row.AgentCode
.TransactionDate = Row.TransactionDate
.Branchcode = Row.BranchCode
.BatchNo = Row.BatchNo
.NoOfCoverNotes = Row.NoOfCoverNotes
.TotalGrossPremium = Row.TotalGrossPremium
.MedicalGrsPrem = Row.MedicalGrsPrem
Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(CoverNote(i))
Row.CoverNote.AddBlobData(buffer)
End With
Next
End Sub
End Class

Thanks jaegd.

I had try to modify the code as per suggested.

Other columns output were fine and ok, just CoverNote result output was NULL value.

Any other script I miss out and need to add in or modified to able support this CoverNOte with text data type?

Appreciate for any help.

|||

What is the pipeline data type of the input column Row.CoverNote -- DT_TEXT,DT_NEXT, or something else?

|||The pipeline data type of the input column Row.CoverNote is DT_TEXT|||

Since the source pipeline type is DT_TEXT , before you can meaningfully called Split() on the character data contained in the blob, convert the pipeline data type to a .NET String first. Retrieval of DT_TEXT data is performed by calling GetBlobData() on the named/typed accessor and then decoding the array of bytes returned.

In other words, replace the line

CoverNote = Split(Row.CoverNote.ToString, "^")

with

Dim blobLength As Int32 = Convert.ToInt32(Row.CoverNote.Length)

Dim blobData() As Byte = Row.CoverNote.GetBlobData(0, blobLength)

Dim blobCodePage As Int32 = Row.CoverNote.ColumnInfo.CodePage

Dim joinedCoverNote As String = Text.Encoding.GetEncoding(blobCodePage).GetString(blobData)

CoverNote = Split(joinedCoverNote, "^")

|||

I had tried the sripting as per suggested.

The problems had been solved.

Thanks for your helping.

Help on Script Component assignment of output variable

Hi all,

Actually I′m working with the beta 2 of the Sql Server 2005 with SSIS. And it′s great fun! But I′m now experience a problem:

I′m working with a script component. In that script component I would like to assign a value to a specific column ("Row.Formula"). The type of "Row.Formula" is Unicode text stream [DT_NTEXT]. Whenever I try to assign a byte array or a string to that field I am getting a compliation error. I have to assign a variable with the type "blobcolumn". But I cannot initialize this variable because "blobcolumn" has no public constructor.

Any thoughts on that?

The code sample:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Avanade.AMCS.DataProcessorReplacement.Utility

Public Class ScriptMain Inherits UserComponent

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
Dim wholeRowBuffer As Byte()
Dim columns() As String

wholeRowBuffer = Row.WholeLine.GetBlobData(0, CInt(Row.WholeLine.Length))

columns = ScriptComponentHelper.RetrieveStringArrayOutOfBuffer(wholeRowBuffer)

Row.Name = columns(1)
Row.MenuName = columns(2)
Row.CascadeName = columns(3)

Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(columns(4))

'Conversion compliation error - cannot convert from byte array to 'Microsoft.SqlServer.Dts.Pipeline.BlobColumn'

Row.Formula = buffer

End Sub

End Class

To access BLOB data in Script component, please use AddBlobData and GetBlobData.

For example:

Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(columns(4))

Row.Formula.AddBlobData(buffer)

|||

---

Imports System
Imports System.Xml
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim i As Integer
Dim CoverNote As String()

CoverNote = Split(Row.CoverNote.ToString, "^")

For i = 0 To UBound(CoverNote)
With Output0Buffer
.AddRow()
.Key = Row.Key
.AgentCode = Row.AgentCode
.TransactionDate = Row.TransactionDate
.Branchcode = Row.BranchCode
.BatchNo = Row.BatchNo
.NoOfCoverNotes = Row.NoOfCoverNotes
.TotalGrossPremium = Row.TotalGrossPremium
.MedicalGrsPrem = Row.MedicalGrsPrem
Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(CoverNote(i))
.CoverNote = Row.CoverNote.AddBlobData(buffer)
End With
Next
End Sub
End Class

The above scripting which highlightted wtih red color was encounter error.
The error msg : Expression doed not produce value.
Note: sources column for CoverNote is text data type which contain a lot multivalue.
example 001^002^003^004^005

Anyonce know how to write the correct scripting for handle such column with text data type.I dont have any idea for this case with text data type.

Thanks in advance.

|||

The line should just read

Row.CoverNote.AddBlobData(...

So remove the assignment part of the highlighted statement; AddBlobData() returns nothing.

|||Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim i As Integer
Dim CoverNote As String()

CoverNote = Split(Row.CoverNote.ToString, "^")

For i = 0 To UBound(CoverNote)
With Output0Buffer
.AddRow()
.Key = Row.Key
.AgentCode = Row.AgentCode
.TransactionDate = Row.TransactionDate
.Branchcode = Row.BranchCode
.BatchNo = Row.BatchNo
.NoOfCoverNotes = Row.NoOfCoverNotes
.TotalGrossPremium = Row.TotalGrossPremium
.MedicalGrsPrem = Row.MedicalGrsPrem
Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(CoverNote(i))
Row.CoverNote.AddBlobData(buffer)
End With
Next
End Sub
End Class

Thanks jaegd.

I had try to modify the code as per suggested.

Other columns output were fine and ok, just CoverNote result output was NULL value.

Any other script I miss out and need to add in or modified to able support this CoverNOte with text data type?

Appreciate for any help.

|||

What is the pipeline data type of the input column Row.CoverNote -- DT_TEXT,DT_NEXT, or something else?

|||The pipeline data type of the input column Row.CoverNote is DT_TEXT|||

Since the source pipeline type is DT_TEXT , before you can meaningfully called Split() on the character data contained in the blob, convert the pipeline data type to a .NET String first. Retrieval of DT_TEXT data is performed by calling GetBlobData() on the named/typed accessor and then decoding the array of bytes returned.

In other words, replace the line

CoverNote = Split(Row.CoverNote.ToString, "^")

with

Dim blobLength As Int32 = Convert.ToInt32(Row.CoverNote.Length)

Dim blobData() As Byte = Row.CoverNote.GetBlobData(0, blobLength)

Dim blobCodePage As Int32 = Row.CoverNote.ColumnInfo.CodePage

Dim joinedCoverNote As String = Text.Encoding.GetEncoding(blobCodePage).GetString(blobData)

CoverNote = Split(joinedCoverNote, "^")

|||

I had tried the sripting as per suggested.

The problems had been solved.

Thanks for your helping.

sql

Help Normalizing an Address

While working with a database that contains subcontractors I came
across an interesting scenario with the respect to their addresses.
Currently all fields for the required data are in the one table and is
causing some problems with regards to updating the redundant info
across multiple entries.
Each sub-contractor can have one or multiple addresses. Main Address,
Remit To Address, Correspondance Address, etc...
Simple enough, but...
Some sub-contractors sell their receivables to a factoring company, so
in addition to typical address info, we must also have phone, fax,
contact info, etc., on hand in these situations.
Also, of the sub contractors that use a factoring company, (about 10%)
use the same factoring company. Redundant info.
Also, some sub contractors are a satilte location of a parent company
and althought their phyical address is different, their 'remit to' is
actually their parent co's remit to address.
Main (physical) addresses are important because we link their zip code
to lat/long database to find sub contractors closest to the particular
area of the country where a job needs to be done.
Right now we just have remit to fields in every sub contractor record.
When a Factoring company changes it's address, though, it can be a pain
to update.
Any suggestions?Without knowing how many records you are talking about, I think it would be
easiest to have one address table with a flag column indicating the type of
address and a fk column pointing back to the sub contractor. From an
administration standpoint, this would be easiest.
If you really feel the need to normalize out, then create a table with all
the unique addresses and use their UID as a Fk back to the sub contractor
table. This way when a new address comes in or an existing one is modified,
you can drop the old one and add the new one, while updating subs address
pointers.
"Clyde Venhause" wrote:

> While working with a database that contains subcontractors I came
> across an interesting scenario with the respect to their addresses.
> Currently all fields for the required data are in the one table and is
> causing some problems with regards to updating the redundant info
> across multiple entries.
> Each sub-contractor can have one or multiple addresses. Main Address,
> Remit To Address, Correspondance Address, etc...
> Simple enough, but...
> Some sub-contractors sell their receivables to a factoring company, so
> in addition to typical address info, we must also have phone, fax,
> contact info, etc., on hand in these situations.
> Also, of the sub contractors that use a factoring company, (about 10%)
> use the same factoring company. Redundant info.
> Also, some sub contractors are a satilte location of a parent company
> and althought their phyical address is different, their 'remit to' is
> actually their parent co's remit to address.
> Main (physical) addresses are important because we link their zip code
> to lat/long database to find sub contractors closest to the particular
> area of the country where a job needs to be done.
> Right now we just have remit to fields in every sub contractor record.
> When a Factoring company changes it's address, though, it can be a pain
> to update.
> Any suggestions?
>|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
It seems to be a basic Many-to-Many consideration. At first thought I'd
do it like this:
CREATE TABLE ContractorAddresses (
contractor_id integer not null references Contractors ,
address_id integer not null references Addresses ,
address_type_code integer not null references AddressTypes ,
CONSTRAINT PK_ContractorAddresses
PRIMARY KEY (contractor_id, address_id, address_type_code)
)
The table Address will not have a ContractorID, 'cuz the above table
provides that info. The table AddressTypes will hold data like this:
address_type_code description
-- --
1 Remittance
2 Correspondence
3 Main Office
.. etc. ...
The communication info should be in a different table, much like the
ContractorAddresses table.
CREATE TABLE ContactorContacts (
contractor_id integer not null references Contractors ,
contact_id integer not null references Contacts ,
contact_type_code integer not null references ContactTypes ,
CONSTRAINT PK_ContractorContacts
PRIMARY KEY (contractor_id, contact_id, contact_type_code)
)
The ContactTypes table would have data like this:
contact_type_code description
-- --
1 Purchaser
2 Inspector
3 Lawyer
.. etc. ...
The Contacts table will hold info on who to contact:
CREATE TABLE Contacts (
contact_id integer not null UNIQUE ,
person varchar(30) not null PRIMARY KEY,
.. other columns ? ... -- may change PK requirement
)
A phones table for the contacts:
CREATE TABLE ContactPhones (
contact_id integer not null references Contacts ,
phone_type_code integer not null references PhoneTypes ,
phone_nr char(10) not null,
CONSTRAINT PK_ContactPhones
PRIMARY KEY (contact_id, phone_type_code, phone_nr)
)
The PhoneTypes table would have data like this:
phone_type_code description
-- --
1 Fax
2 Office
3 Purchasing Office
.. etc. ...
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQmKiW4echKqOuFEgEQKgUACdF8x/K9jakhVTRe6okJI7qtFevNoAn0ky
DEbKy122rQJN1FlCOcaXp9jq
=gg/j
--END PGP SIGNATURE--
Clyde Venhause wrote:
> While working with a database that contains subcontractors I came
> across an interesting scenario with the respect to their addresses.
> Currently all fields for the required data are in the one table and is
> causing some problems with regards to updating the redundant info
> across multiple entries.
> Each sub-contractor can have one or multiple addresses. Main Address,
> Remit To Address, Correspondance Address, etc...
> Simple enough, but...
> Some sub-contractors sell their receivables to a factoring company, so
> in addition to typical address info, we must also have phone, fax,
> contact info, etc., on hand in these situations.
> Also, of the sub contractors that use a factoring company, (about 10%)
> use the same factoring company. Redundant info.
> Also, some sub contractors are a satilte location of a parent company
> and althought their phyical address is different, their 'remit to' is
> actually their parent co's remit to address.
> Main (physical) addresses are important because we link their zip code
> to lat/long database to find sub contractors closest to the particular
> area of the country where a job needs to be done.
> Right now we just have remit to fields in every sub contractor record.
> When a Factoring company changes it's address, though, it can be a pain
> to update.
> Any suggestions?
>

Help New to SQL server

Im working for a company that has a relatively small database but will continue growing. We would like to use SQL server to accomodate the mass users who will be accessing the data. I have never used sql server and would like to know where to get started. IE. books, websites(besides this one), prices. what will i need to get this database up and running?? I would like to have sql server as my backend and microsoft access as my frontend. any help would be greatly appriciated.

thanxhttp://www.sqlteam.com/store.asp

I would rethink Access as a front end...but that's just me...

Have you seen how much sql server costs?

You might want to look into MSDE...

How many users?

How much data are you talking about...

Is it internal to one office?

What is the application meant to do?|||Originally posted by Brett Kaiser
http://www.sqlteam.com/store.asp

I would rethink Access as a front end...but that's just me...

Have you seen how much sql server costs?

You might want to look into MSDE...

How many users?

How much data are you talking about...

Is it internal to one office?

What is the application meant to do?

-------------

Why would you rethink Access?? What else would you use as a frontend?

Cost is not an issue. How mush is it?

What is MSDE (microsoft developer edition??)

mmm possibly 200 users accessing from the web and 6 users from the office.

The data right now is about 40mb. I know this isnt alot. i could create the database in access then when necessary move it to SQL server, but access cant handle the amount of users i need.

The database will keep track of all billings, customer info, shipping infosql

Help needed in restoring the document library

Hi,
I am working in the SQL Server 2000 and Share Point Portal Server; we are
maintaining the SQL Backup for all the sites,
one of the users has deleted accidentally a document library from a
particular site. Can you please help me in restoring the particular document
library from the SQL Backup File?
Here are details of how to do this through the dedicated backup/restore
tool:
http://office.microsoft.com/en-us/sharepointportaladmin/HA011603461033.aspx
As this is not really a replication issue I'd recommend posting this in the
Sharepoint newsgroup.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Help needed for SSRS 2005(Regarding Individual folder Security)

Hi Experts,
I am working on SSRS 2005.
I want to implement security for each folder for different users on my
report server.
I have 6 different folders on my report server and I want that any
person can open them after entering a valid username and password. I
want set different username and password for each folder not a single
password for all folders.
I am not finding anyway to do this. So if anybody have any idea about
this please tell me.
Any help will be appreciated.
Regards
DineshOn May 3, 4:06 am, Dinesh <dinesh...@.gmail.com> wrote:
> Hi Experts,
> I am working on SSRS 2005.
> I want to implement security for each folder for different users on my
> report server.
> I have 6 different folders on my report server and I want that any
> person can open them after entering a valid username and password. I
> want set different username and password for each folder not a single
> password for all folders.
> I am not finding anyway to do this. So if anybody have any idea about
> this please tell me.
> Any help will be appreciated.
> Regards
> Dinesh
Normally, you would tie the folder-level security into Windows
authentication, based on user and group access (via select a folder ->
select the Properties tab -> select Security on the left-hand side ->
Edit item security on the top). You might make a new role w/only
certain members and that role only has access to certain folders;
however, again this is windows authentication based. The only other
way to go about it would be to create a parameter in each report that
accepts text input and use this as a password. Sorry that I could not
be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||As Enrique mentioned you can't do this.
This is not how Windows works. Consider this the same as if you had a file
server and were trying to do this. You could not do it.
Same result is to do the following. Create 6 local groups. To the local
groups add the users (you can add either individual domain users or domain
groups to the local groups). Then use these groups when setting up roles and
access to the folders.
You should read up on roles and security in RS.
One other possibility is to implement forms security. This is where you
integrate your own authentication mechanism.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dinesh" <dinesht15@.gmail.com> wrote in message
news:1178183193.254090.33670@.c35g2000hsg.googlegroups.com...
> Hi Experts,
> I am working on SSRS 2005.
> I want to implement security for each folder for different users on my
> report server.
> I have 6 different folders on my report server and I want that any
> person can open them after entering a valid username and password. I
> want set different username and password for each folder not a single
> password for all folders.
> I am not finding anyway to do this. So if anybody have any idea about
> this please tell me.
> Any help will be appreciated.
> Regards
> Dinesh
>

Help needed for SSRS 2005(Regarding html code)

Hi Experts,
I am working on SSRS 2005, and I am facing a problem in this.
We are using an ASP.net application in which user fills a form. Some
fields contain text fields and we are entering all the fields in our
database through application.
But when we are entering values from application then our database
contains some html code in some fields with data.
Now I want to show those fields in my report, Values coming from
database in my report but they are in html format. So it is not
readable in report.
Now you people tell me how to solve this issue in my reports. I want
that data in my report as user entered in the application form.
Any help will be appreciated.
Regards
DineshDinesh,
The only thing I can think of is writing a function which strips out the
HTML tags and then call that function for each field in your report. You
could make this function a Report Code Block or even better, put it in an
assembly referenced by the Report.
--
Andy Potter
blog : http://sqlreportingservices.spaces.live.com
info@.(NOSPAM)lakeclaireenterprises.com
"Dinesh" <dinesht15@.gmail.com> wrote in message
news:1176467678.190725.177080@.y80g2000hsf.googlegroups.com...
> Hi Experts,
> I am working on SSRS 2005, and I am facing a problem in this.
> We are using an ASP.net application in which user fills a form. Some
> fields contain text fields and we are entering all the fields in our
> database through application.
> But when we are entering values from application then our database
> contains some html code in some fields with data.
> Now I want to show those fields in my report, Values coming from
> database in my report but they are in html format. So it is not
> readable in report.
> Now you people tell me how to solve this issue in my reports. I want
> that data in my report as user entered in the application form.
> Any help will be appreciated.
> Regards
> Dinesh
>

Help Needed For reporting service error(An internal error occurred on the report server)

Hi experts
I am working on sql server reporting services 2005. And now a days i
am facing a problem.
Some times when i want to run a report the at that time i am geting
the following error msg
"An internal error occurred on the report server. See the error log
for more details."
This eroor not every time come when i run the reports and if i agian
pres the view report button then in 2nd or 3rd attemt
same report run and shows the correct data.
But it is not good to see any error msg during running the report. I
have checked the log file but there no eoor msg is
listed.
So if any one have any idea abt this how to solve this problem Please
help me.
Becuase now a days it is comming more freqently.
Any help will be gratefull.
Regards
DineshIt sounds like you have the PerfCounters issue on an AMD Processor server.
If you check the SRS error logs in C:\Program Files\Microsoft SQL
Server\MSSQL.#\Reporting Services\LogFiles you will probably find an
"ASSERT" message in there.
Edit your boot.ini file and add the following switch to your boot.ini and
see if that fixes the issue /usepmtimer
More information regarding this can be found here:
http://support.microsoft.com/kb/895980/en-us
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: Dinesh <dinesht15@.gmail.com>
> Newsgroups: microsoft.public.sqlserver.reportingsvcs
> Subject: Help Needed For reporting service error(An internal error
occurred on the report server)
> Date: Thu, 04 Oct 2007 02:23:20 -0700
> Hi experts
> I am working on sql server reporting services 2005. And now a days i
> am facing a problem.
> Some times when i want to run a report the at that time i am geting
> the following error msg
> "An internal error occurred on the report server. See the error log
> for more details."
> This eroor not every time come when i run the reports and if i agian
> pres the view report button then in 2nd or 3rd attemt
> same report run and shows the correct data.
> But it is not good to see any error msg during running the report. I
> have checked the log file but there no eoor msg is
> listed.
> So if any one have any idea abt this how to solve this problem Please
> help me.
> Becuase now a days it is comming more freqently.
> Any help will be gratefull.
> Regards
> Dinesh
>

Help Needed For reporting service 2005(Rendering in PDF)

Hi experts
I am working on sql server reporting services 2005.
Now a days i m developing a text report. Which shows text for selected
parameters.
I have added more table headers in my layout. And i am using one row
for heading and just below row for description.
This description is comming from database so it is variable, somtimes
it is having ony 100 chars and some times it is
having more than 20000 chars. Problem starts here when the number of
charcters are less then report is looking good in PDF
But when it is more then the description row moves on next page. I
have tried this by selecting the table property "Fit table in one page
if possible"
I have once cheked this property and once unchecked this but result is
same. Report looks good in report viewer but not looks good in PDF
format.
MY another question is it possible that i can bind two rows in the
table that if second rows goes in the second page then
the row above also goes on next page. RIght now heading row appears on
first page in pdf and then second row starts on new page after leaving
all the available space on first page.
So please if any body have any idea about my problem, Please help me.
Any help will be gratefull.
Regards
DineshTry to use group.
"Dinesh" wrote:
> Hi experts
> I am working on sql server reporting services 2005.
> Now a days i m developing a text report. Which shows text for selected
> parameters.
> I have added more table headers in my layout. And i am using one row
> for heading and just below row for description.
> This description is comming from database so it is variable, somtimes
> it is having ony 100 chars and some times it is
> having more than 20000 chars. Problem starts here when the number of
> charcters are less then report is looking good in PDF
> But when it is more then the description row moves on next page. I
> have tried this by selecting the table property "Fit table in one page
> if possible"
> I have once cheked this property and once unchecked this but result is
> same. Report looks good in report viewer but not looks good in PDF
> format.
> MY another question is it possible that i can bind two rows in the
> table that if second rows goes in the second page then
> the row above also goes on next page. RIght now heading row appears on
> first page in pdf and then second row starts on new page after leaving
> all the available space on first page.
> So please if any body have any idea about my problem, Please help me.
> Any help will be gratefull.
> Regards
> Dinesh
>

Help Needed For reporting service 2005(Regarding deployment)

Hi experts
I am working on sql server reporting services 2005.
And i want to know one thing. I have one solution in SSRS 2005 report
designer and currently i m dploying each and every report to their
respective folders.
But now i have to deploy all the reports again on some new server. So
is there any way by which i dont need to deploy each and every report
seprately. IS there any script available by which i can deploy all the
reports on my server.
And second thing i wish to know is it possible to make instalable file
for this solution so that where ever i want i can install these
reports. If yes then how can i make instalable file of my this
solution.
Any help will be gratefull.
Regards
DineshThe easiest way to deploy your reports is to have a solution with multiple
report projects in them. Each Report Project would represent a folder on
your report server. In the project properties you would have your
"TargetReportFolder" set to the correct deployment folder name for each of
the projects.
Then when you wanted to deploy your reports to a different server all you
would need to do is modify the "TargetServerURL" property to point to the
new server name and then redeploy your reports.
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: Dinesh <dinesht15@.gmail.com>
> Newsgroups: microsoft.public.sqlserver.reportingsvcs
> Subject: Help Needed For reporting service 2005(Regarding deployment)
> Date: Tue, 16 Oct 2007 03:39:27 -0700
> Organization: http://groups.google.com
> Hi experts
> I am working on sql server reporting services 2005.
> And i want to know one thing. I have one solution in SSRS 2005 report
> designer and currently i m dploying each and every report to their
> respective folders.
> But now i have to deploy all the reports again on some new server. So
> is there any way by which i dont need to deploy each and every report
> seprately. IS there any script available by which i can deploy all the
> reports on my server.
> And second thing i wish to know is it possible to make instalable file
> for this solution so that where ever i want i can install these
> reports. If yes then how can i make instalable file of my this
> solution.
> Any help will be gratefull.
> Regards
> Dinesh
>

Help Needed For reporting service 2005(Regarding default date value for report parameter)

Hi experts
I am working on sql server reporting services 2005.
I am using Date time control for my report parameter. And default
value i given =Today(). This is working fine.
But i want some days previous date for default value and when I am
writting Today()-1, Report is giving error.
So can any body tell me how i can do this for defalut value. I want to
give one month previous date as default value.
And second thing i wish to know is it possible to make instalable file
for this solution so that where ever i want i can
install these reports. If yes then how can i make instalable file of
my this solution.
Any help will be gratefull.
Regards
DineshOn Oct 23, 9:38 am, Dinesh <dinesh...@.gmail.com> wrote:
> Hi experts
> I am working on sql server reporting services 2005.
> I am using Date time control for my report parameter. And default
> value i given =Today(). This is working fine.
> But i want some days previous date for default value and when I am
> writting Today()-1, Report is giving error.
> So can any body tell me how i can do this for defalut value. I want to
> give one month previous date as default value.
> And second thing i wish to know is it possible to make instalable file
> for this solution so that where ever i want i can
> install these reports. If yes then how can i make instalable file of
> my this solution.
> Any help will be gratefull.
> Regards
> Dinesh
Hi!
You can try it;
(Date = DATEADD(Day, - 1, GetDate())
Hope this helps
Regards
Shima

Help needed for datediff function for SQL query

Hi Experts,
I am working on SSRS 2005, and I am facing a problem in counting the
no of days.
My database has many fields but here I am using only two fields
They are Placement_Date and Discharge_Date
If child is not descharged then Discharge_Date field is empty.

I am writing below query to count the number of days but is is not
working it is showing the error
"The conversion of a char data type to a datetime data type resulted
in an out-of-range datetime value."

select case
when convert(datetime,Discharge_Date,103) = '' then
datediff(day,CONVERT(datetime,Placement_Date,103), GETDATE())
else
datediff(day,CONVERT(datetime,Placement_Date,
103),CONVERT(datetime,Discharge_Date,103))
end NoOfDays
from Placement_Details
So please tell me where I am wrong?

Any help will be appriciated.
Regards
DineshWithout having your table structure and sample data, it is a bit difficult
to troubleshoot, but a few notes that can help you:

- Based on the style 103 that you use for the CONVERT function, seems you
are converting date stored as string in format "dd/mm/yyyy" to a datetime
type. The error that you get indicates that the date string cannot be
converted, because the day, month, or year portion is out of the allowed
range. You can easily simulate the error if you run something like this:
SELECT CONVERT(datetime, '23/13/2007', 103). The month cannot be 13 so it
fails with the error you get. You can use the LEFT, RIGHT and SUBSTRING
functions to extract the day, month, and year portion of both columns
(Placement_Date and Discharge_Date) and check for invalid values, then clean
your data. For year the down side value is 1753 (that is the lower limit for
datetime data type).

- It is always best to keep date values in columns of datetime data type.
That way you do not have to worry about the format and can benefit of using
all datetime functions with no need to convert.

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||Dinesh (dinesht15@.gmail.com) writes:

Quote:

Originally Posted by

I am working on SSRS 2005, and I am facing a problem in counting the
no of days.
My database has many fields but here I am using only two fields
They are Placement_Date and Discharge_Date
If child is not descharged then Discharge_Date field is empty.
>
I am writing below query to count the number of days but is is not
working it is showing the error
"The conversion of a char data type to a datetime data type resulted
in an out-of-range datetime value."
>
select case
when convert(datetime,Discharge_Date,103) = '' then

This does not really make sense. A datetime value cannot be the
empty string. By the default the empty string will convert to the
datetime value 1900-01-01 00:00:00.000, but I don't think that is
what you want.

Assuming a reasonably designed database, the test would be

WHEN Discharge_Date IS NULL THEN

Then again, since you seem to store dates in character values, this
may not be a reasonably designed database. :-)

Anyway, the problem appears to be that you have junk in your character
columns. As Plamen said, you should use the datetime data type to store
your dates instead.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Dinesh (dinesht15@.gmail.com) writes:

Quote:

Originally Posted by

I am working on SSRS 2005, and I am facing a problem in counting the
no of days.
My database has many fields but here I am using only two fields
They are Placement_Date and Discharge_Date
If child is not descharged then Discharge_Date field is empty.
>
I am writing below query to count the number of days but is is not
working it is showing the error
"The conversion of a char data type to a datetime data type resulted
in an out-of-range datetime value."
>
select case
when convert(datetime,Discharge_Date,103) = '' then
datediff(day,CONVERT(datetime,Placement_Date,103), GETDATE())
else
datediff(day,CONVERT(datetime,Placement_Date,
103),CONVERT(datetime,Discharge_Date,103))
end NoOfDays
from Placement_Details
So please tell me where I am wrong?

Oh, by the way, this SELECT should give you the rows with bad dates:

SET DATEFORMAT DMY
go
SELECT Discharge_Date, Placement_Date
FROM Placement_Details
WHERE isdate(Discharge_Date) = 0 OR
isdate(Placement_Date) = 0

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Help Needed For Date format in SSRS 2005

Hi experts,
I am working on SQL server 2005 reporting services and i am getting a
problem.
I am developing a report in which i am taking two parameters
one is FromDate and second is ToDate and i have changed thier Data
type as Date Time.
So it is giving callender control in reports. And default values for
both parameter is todays system date.
Now I want these dates in dd/mm/yyyy format so i changed the setting
of my system for the required date format.
In parameter selection box date format is correct it is comming in dd/
mm/yyyy format.
But again I am using a text box in the report body which tell us a
message that this report is contains the data between these dates.
For this I am using the expression
="The following data is for the period between " & Parameters!
FromDate.Value & " and " & Parameters!ToDate.Value
So here i am getting these dates in the mm/dd/yyyy format.
I have tried this also
="The following data is for the period between " & Parameters!
FromDate.Label & " and " & Parameters!ToDate.Label
The no date is comming in the text box.
I have tried Cdate function and other functions in DateTime function
which are available in the reporting services property box, But i am
not finding the solution for this problem.
So if any body is having any idea about this then please help me.
Any help wil be appriciated.
Regards
DineshHi Dinesh,
Make sure the report language is set to Australia (or any other language
that supports this format by default) , you find this under the properties
dialog box when you only select the form (not any controls on it).
Cheers
Matt
"Dinesh" wrote:
> Hi experts,
> I am working on SQL server 2005 reporting services and i am getting a
> problem.
> I am developing a report in which i am taking two parameters
> one is FromDate and second is ToDate and i have changed thier Data
> type as Date Time.
> So it is giving callender control in reports. And default values for
> both parameter is todays system date.
> Now I want these dates in dd/mm/yyyy format so i changed the setting
> of my system for the required date format.
> In parameter selection box date format is correct it is comming in dd/
> mm/yyyy format.
> But again I am using a text box in the report body which tell us a
> message that this report is contains the data between these dates.
> For this I am using the expression
> ="The following data is for the period between " & Parameters!
> FromDate.Value & " and " & Parameters!ToDate.Value
> So here i am getting these dates in the mm/dd/yyyy format.
> I have tried this also
> ="The following data is for the period between " & Parameters!
> FromDate.Label & " and " & Parameters!ToDate.Label
> The no date is comming in the text box.
> I have tried Cdate function and other functions in DateTime function
> which are available in the reporting services property box, But i am
> not finding the solution for this problem.
> So if any body is having any idea about this then please help me.
> Any help wil be appriciated.
> Regards
> Dinesh
>|||try something like:
Parameters!ToDate.Value.ToString("dd/mm/yyyy")
or
DateTime.Parse(Parameters!ToDate.Value).ToString("dd/mm/yyyy")
or
CDate(Parameters!ToDate.Value).ToString("dd/mm/yyyy")
I don't remember which one works or not
good luck!
"Dinesh" <dinesht15@.gmail.com> wrote in message
news:1189587299.387328.305020@.50g2000hsm.googlegroups.com...
> Hi experts,
> I am working on SQL server 2005 reporting services and i am getting a
> problem.
> I am developing a report in which i am taking two parameters
> one is FromDate and second is ToDate and i have changed thier Data
> type as Date Time.
> So it is giving callender control in reports. And default values for
> both parameter is todays system date.
> Now I want these dates in dd/mm/yyyy format so i changed the setting
> of my system for the required date format.
> In parameter selection box date format is correct it is comming in dd/
> mm/yyyy format.
> But again I am using a text box in the report body which tell us a
> message that this report is contains the data between these dates.
> For this I am using the expression
> ="The following data is for the period between " & Parameters!
> FromDate.Value & " and " & Parameters!ToDate.Value
> So here i am getting these dates in the mm/dd/yyyy format.
> I have tried this also
> ="The following data is for the period between " & Parameters!
> FromDate.Label & " and " & Parameters!ToDate.Label
> The no date is comming in the text box.
> I have tried Cdate function and other functions in DateTime function
> which are available in the reporting services property box, But i am
> not finding the solution for this problem.
> So if any body is having any idea about this then please help me.
> Any help wil be appriciated.
> Regards
> Dinesh
>

Help need to write a Query in VB - MsSql !

Hai ,

My database is Ms Sql and I am devolping in VB
The below is my query, it seems to be working but at the last there is some problem. If possible kindly correct the query. Actually the problem is the SalesIn Quantity value is not shown correctly, it seems to be working in SP which I created in Ms Sql, so I tried the same here, I hope with some modifications it could be correctly executed.

sql = "select Item as Itemid,Itemid as SoldItemId,Date as SoldDate,"
sql = sql & "(Qty * Unitcost) as Cost,Qty as SoldQty,ItemId as StockId,"
sql = sql & "Qty as StockQty,ItemId as SalesinId,Qty as SalesInQty from"
sql = sql & " Stock,Sales,StockDetail,SalesIn where Stock.Itemid *= Sales.Itemid AND "
sql = sql & "StockDetail.Itemid = Stock.Itemid AND SalesIn.ItemId = Sales.ItemId AND "
sql = sql & "StockDetail.WareHouse ='" & Text3.Text & "' AND SalesIn.Type ='" & Text4.Text & "' AND "
sql = sql & "Date Between '" & Text1.Text & "' AND '" & Text2.Text & "' Group By"
sql = sql & " Stock.ItemId,StockDetail.ItemId,Sales.ItemId,Sales In.ItemId,Sales.Date,Sales.Qty,Sales.UnitCost,Stoc kDetail.Qty,"
sql = sql & "SalesIn.Qty order by Stock.ItemId"
rst.Open sql, cnn, adOpenStatic, adLockReadOnly, adCmdText

Everything is coming correct except the SalesIn Quantity. If I remove and try means then the other things are showing correctly. I mean the ItemId and the Soldqty and the stockqty and everything is showing correctly here just for my reference I am showing all the ItemId.

Kindly view and reply me.

SalesTable, StockTable, StockDetail, SalesIn are the Four table I am taking here. All the four tables are Linked by the ItemId.

Thank you very much,
Chockyou need to qualify the columns in the SELECT the same way you have qualified them in the GROUP BY

suggestion: switch immediately to JOIN syntax rather than the "old style" joins using that darned asterisk beside the equal sign
select Stock.Item as Itemid
, StockDetail.Itemid as SoldItemId
, Sales.Date as SoldDate
, Sales.Qty * Sales.Unitcost as Cost
, Sales.Qty as SoldQty
, Stock.ItemId as StockId
, StockDetail.Qty as StockQty
, SalesIn.ItemId as SalesinId
, SalesIn.Qty as SalesInQty
from Stock
inner
join StockDetail
on Stock.Itemid = StockDetail.Itemid
left outer
join Sales
on Stock.Itemid = Sales.Itemid
left outer
join SalesIn
on Sales.ItemId = SalesIn.ItemId
where Sales.Date Between 'Text1.Text'
and 'Text2.Text'
and StockDetail.WareHouse = 'Text3.Text'
and SalesIn.Type ='Text4.Text'
group
by Stock.ItemId
, StockDetail.ItemId
, Sales.ItemId
, SalesIn.ItemId
, Sales.Date
, Sales.Qty
, Sales.UnitCost
, StockDetail.Qty
, SalesIn.Qty
order
by Stock.ItemIdfinal tip: never use a reserved word like Date as a column name

rudy
http://r937.com/|||Hi,

I modified the Left outer join as you said, the below is the query which i am currently using.

sql = "select Itemid as StockId,Itemid as StkDetailId,
ItemId as SoldItemId,ItemId as SalesinId,
SellingDate as SoldDate,"
sql = sql & "(SoldQty * UnitCost) as Cost,SoldQty as SoldQty,"
sql = sql & "StockQty as QtyInHnd,SaleinQty as SalesInQty from"
sql = sql & " Stock inner join StockDetail on
Stock.Itemid = StockDetail.Itemid"
sql = sql & " left outer join Sales on Stock.Itemid = Sales.Itemid"
sql = sql & " left outer join SalesIn on Sales.ItemId = SalesIn.Itemid"
sql = sql & " where Sales.Date Between '" & Text1.Text &
"' AND '" & Text2.Text & "' AND StockDetail.WareHouse ='" &
Text3.Text & "' AND "
sql = sql & "SalesIn.Type ='" & Text4.Text & "' Group By "
sql = sql & "Stock.Itemid,StockDetail.Itemid,Sales.Itemid,
SalesIn.Itemid,Sales.SellingDate,Sales.SoldQty,
Sales.UnitCost,StockDetail.QtyinHand,
SalesIn.SalesInQty order by Stock.ItemId

its executing, but I didn't get the output correctly, It didn't shows the record as per the Left Outer Join. Actually I need the output as below

StkId SoldCost QtyInHand SoldQty SaleInQty SDate SalesIn WareHouse
sl001 120 4 2 2 05/01/03 00 01
sl002 0 10 0 0 00 01
sl003 30 2 10 0 05/01/03 00 01
sl004 0 120 0 0 00 01

whethere the Item Sold or not all the Item Id should be listed with their details. The Stock and StockDetail is the Master for the ItemId, So i take that as Inner Join , now what I am getting the oupt put is

sl001 60 4 1 2 05/01/03 00 01
sl001 60 4 1 2 05/01/03 00 01
sl003 30 2 10 0 05/01/03 00 01

if above is not clear, I have attatched the Excel sheet.|||hai friend,

Now I am trying like this, will it work, is the way I am writing is correct or not. kindly let me know also now I will post this to UA,

here I am facing the error

Run-time error '-2147217900(80040e14)':
The Column Prefix 'subquery' doesnot match with a tablename or alias name used in the query.

sql1 = "select distinct ItemId,Description from Stock"
sql2 = "select Itemid,SoldQty,UnitCost,SoldDate from Sales where Sales.SoldDate Between ='" & Text1.Text & "' AND '" & Text2.Text & "'"

sql3 = "select distinct ItemId,WareHouse,StockQty from StockDetail where WareHouse ='" & Text3.Text & "'"

sql4 = "select distinct SalesType,ItemId,Date,ItemId,SalesInQty from Stock Left Join SalesIn on Stock.ItemId = SalesIn.ItemId where SalesInType ='" & Text4.Text & "' AND SalesIn.Date ='" & Text1.Text & "' AND '" & Text2.Text & "'

this is the Subquery

subquery = "select sql1.StockItemId,sql1.Description,sql2.SalesItemId ,"
subquery = subquery & "(sql2.SoldQty * sql2.Cost),sql2.SoldDate,sum(sql3.StockQty),"
subquery = subquery & "sum(sql4.SalesInQty) from "
subquery = subquery & "(((sql1 Left Join Sql2 on sql1.StockItemId=sql2.SaleItemId) Left Join sql3 on sql1.StockItemId = sql3.StockDetail.ItemId) Left Join sql4 on sql1.StockItemId = sql4.SalesInItemId) order by sql1.StockItemId"
this is the main query
mainquery = "select subquery.StockItemId,subquery.Decription,subquery. SoldItemId,"
mainquery = mainquery & "(subquery.SoldQty * subquery.Cost),subquery.SoldDate,subquery.StockQty ,"
mainquery = mainquery & "subquery.SalesInQty"

kindly when you have time view and reply me

Thankyou very much,
Chock.

help nedded for MS reporting service 2000

Hi,
I am working on microsoft SQl server 2000 reporting services. I am
developing an application in c#.net in VS2003 which
will call reports in windowds forms and ask for user to select the
parameters for them.
Till now i had finished . Now I want to select multiple values for
parameters. and wants that for each combination a seprate report will
be drawn and it the end all the reports are merged in to one.
i.e.
suppose there are two parameters in a report and there are 10 available
values for first one and 20 for second parameter.
now i have selected 2 values for first parameter and 4 for second
parameter.
how this i can do in my program.
Please help me. because i am not finding any way to do it.
Thanks And Regards
DineshYou are not finding it because you cannot do this in RS 2000. RS 2000 does
not support multi-select parameters. RS 2005 does.
If it is possible to go to RS 2005 I would. For one thing, VS 2005 comes
will new controls that make integrating with RS much much easier. Plus RS
2005 has many good improvements: multi-select parameters, end user sorting,
performance improvements, etcs.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dinesh" <dinesht15@.gmail.com> wrote in message
news:1164259279.407811.135710@.l12g2000cwl.googlegroups.com...
> Hi,
> I am working on microsoft SQl server 2000 reporting services. I am
> developing an application in c#.net in VS2003 which
> will call reports in windowds forms and ask for user to select the
> parameters for them.
> Till now i had finished . Now I want to select multiple values for
> parameters. and wants that for each combination a seprate report will
> be drawn and it the end all the reports are merged in to one.
> i.e.
> suppose there are two parameters in a report and there are 10 available
> values for first one and 20 for second parameter.
> now i have selected 2 values for first parameter and 4 for second
> parameter.
> how this i can do in my program.
> Please help me. because i am not finding any way to do it.
> Thanks And Regards
> Dinesh
>|||hi.......
i think u could not understand my problem properly.....
i know it is possible........
I had done the rendering and pring part of this for RS2000.
I had developed an application which will show all the reports on
report Server in a combobox.
here user will select a report if it contains parameters then it will
draw that many checklist boxes on the form if it does not have any
parameter then it will ask from user to select a format in which he
wantthe report.
now user will select a value for each parameter from checklistbox by
checking a single value in each check list box.
now those valuse will be passed in the rendor method to draw the
report.
after that report is drawn and printed.
till now i had finished.
now i wan to selct multiple values in checklistboxes.
suppose there are 2 parameters and user has selected 3 values in first
one and 2 values in second one
then i will get 6 parameters to pass in render method.
then after passing one set i will get one report i will store it in a
string varible and append others after it. ie. i will got total 6
reports which will be merged one after others.
if it is always fixed then no problem i can do it in for loop.
but since number of parameters are not fixed so i want to know how can
get the parameters set dynamically. for any combimation of parameters.
i.e.
i want each combination of parameter and want to pass it to render
method whioch i wrote in a seprate function.
Hope i have explained my problem completely.
if any body have any idea please tell me.
Regards
Dinesh
Bruce L-C [MVP] wrote:
> You are not finding it because you cannot do this in RS 2000. RS 2000 does
> not support multi-select parameters. RS 2005 does.
> If it is possible to go to RS 2005 I would. For one thing, VS 2005 comes
> will new controls that make integrating with RS much much easier. Plus RS
> 2005 has many good improvements: multi-select parameters, end user sorting,
> performance improvements, etcs.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Dinesh" <dinesht15@.gmail.com> wrote in message
> news:1164259279.407811.135710@.l12g2000cwl.googlegroups.com...
> > Hi,
> >
> > I am working on microsoft SQl server 2000 reporting services. I am
> > developing an application in c#.net in VS2003 which
> > will call reports in windowds forms and ask for user to select the
> > parameters for them.
> > Till now i had finished . Now I want to select multiple values for
> > parameters. and wants that for each combination a seprate report will
> > be drawn and it the end all the reports are merged in to one.
> >
> > i.e.
> > suppose there are two parameters in a report and there are 10 available
> > values for first one and 20 for second parameter.
> > now i have selected 2 values for first parameter and 4 for second
> > parameter.
> > how this i can do in my program.
> > Please help me. because i am not finding any way to do it.
> >
> > Thanks And Regards
> >
> > Dinesh
> >|||Hello,
I am trying to have both comma delimited and tab delimited output
option from sql 2000 reporting services. I have updated the config file
to have 2 output option in the render section of the config file. But I
always get 2 options listed on the dropdown as CSV - Comma Delimited, I
didn't get the tab-delimited option. Why my code in the config file
didn't get affected? Anything else I have to do, or is it all possible
in RS 2000.
Any help would be really appreciated.
Thanks,
Ravi

Help me: error on Home Page for some.

Hi there,

I'm new here and fairly new to .NET and C# too.

I've received a couple of complains about a website I've been working on thepast year or so: http://www.ipcamerademos.com/

I can open and browse the website fine, in both MS Internet Explorer andFirefox. But some people are getting some error opening up the website (homepage):

Server Error in '/' Application.

---

String or binary data would be truncated.
The statement has been terminated.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: String or binary data would be truncated.
The statement has been terminated.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): String or binary data would be truncated.

The statement has been terminated.]

System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857178

System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734790

System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188

System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838

System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +192

System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +380

System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135

ipcamerademos.index.Counter() +359

ipcamerademos.index.Page_Load(Object sender, EventArgs e) +86

System.Web.UI.Control.OnLoad(EventArgs e) +99

System.Web.UI.Control.LoadRecursive() +47

System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

Anyone here gets this error and knows what would cause it? Any hints orideas - have someone experienced something similar?

Thank you all in advance,

Marcio W. Dirickson.

The website opens fine with firefox and opera but crashes everytime with ie7 on vista. What are you storing in a database or getting from a database when you load the web page?|||

Hi Ken,

Most of the home page content is from the database (the main article and all the other articles). And we store a "counter" type information from the home page as well. From what I got replies (from users and visitors) with Windows XP and IE6 also had cases of this error...

Thanks for the reply...

|||

Is your data access code in a try catch block?

|||

I was able to open the website in firefox. When I viewed the source I found this. When you fix the loading xml error I think you will solve your problem.

<td valign="top"
<!-- Right -->
<table width="223"border="0"cellspacing="0"cellpadding="0">
<!--tr>
<td>< Shop >
Loading XML: failed. Error 404x.
</td>
</tr>
<tr>
<td height="5"></td>
</tr-->
<tr>

<td>
<!-- E-newsletter -->
<table width="223"border="0"cellspacing="0"cellpadding="0">
<tr>
<td
<table width="223"border="0"cellspacing="0"cellpadding="0"bordercolor="#374B8D"bgcolor="#4761B7"style="border: 5px solid #374B8D;">
<tr>
<td><img src="/images/td_top_blue.gif"width="213"height="12"alt=""border="0"></td>
</tr>

<tr>
<td class="subtitle_blue">E-NEWSLETTERS</td>
</tr>

|||

Hi again Ken,

I've checked the XLS and wasn't that... so I added some more catch blocks and found where the error was happening: where we grabbed and stored the logs information.

public void Counter()
{
try
{
// Insert Access Log
string IP = Request.ServerVariables["REMOTE_ADDR"];
string browser = Request.ServerVariables["HTTP_USER_AGENT"];
string source = Request.ServerVariables["HTTP_REFERER"];
conn.executeSQL("INSERT INTO tbl_ipc_logs (ipl_IP, ipl_browser, ipl_source, ipl_date) VALUES ('" + IP + "', '" + browser + "', '" + source + "', '" + DateTime.Now + "')");
conn.objConnection.Close();
conn.objConnection.Dispose();
}
catch(Exception)
{
// Response.Write("Access Logs : " + e);
// Response.Flush();
}
}

With the catch the page started working.

Thank you for the help!

Help me: About Sql server Internet merge Replication

i am working on merge replication over internet
i configure each and everything as Paul told in artical
i configure TCP\IP port at client network utility as 1433, also server at
server network utility as 1433
also FTP port at publisher properties is 21
but it is not working
it show error as "sql server not exist or access denied"
i configure client computer using client network utility,
server computer by server network utility
also proper publication and subcriber and ftp root
please solve my problem
need u'r kind help
thanking you
amy
I'm still pretty new at some of this replication stuff. But I went and
pinged the subscriber from the publisher and vice versa. Then I set up
Aliases through the configuration tool using TCP\IP and setting the alias
name equal to that of the Server (i.e. My local server: S-HUNLEY). I don't
know if this is helpful, but it sounds like to me that your servers just
can't see one another and an alias, using TCP/IP or even a linkedserver may
help.
Good Luck!!
Scott E. Hunley (MCAD)
Measure Twice, Cut Once!!!
"amy" wrote:

> i am working on merge replication over internet
> i configure each and everything as Paul told in artical
> i configure TCP\IP port at client network utility as 1433, also server at
> server network utility as 1433
> also FTP port at publisher properties is 21
> but it is not working
> it show error as "sql server not exist or access denied"
> i configure client computer using client network utility,
> server computer by server network utility
> also proper publication and subcriber and ftp root
> please solve my problem
> need u'r kind help
> --
> thanking you
> amy
|||thanks for reply
please tell me how to set up
Aliases through the configuration tool using TCP\IP
i done pinging from subsciber from publisher and also publisher to subsciber
thanking you
amy
"Scott Hunley" wrote:
[vbcol=seagreen]
> I'm still pretty new at some of this replication stuff. But I went and
> pinged the subscriber from the publisher and vice versa. Then I set up
> Aliases through the configuration tool using TCP\IP and setting the alias
> name equal to that of the Server (i.e. My local server: S-HUNLEY). I don't
> know if this is helpful, but it sounds like to me that your servers just
> can't see one another and an alias, using TCP/IP or even a linkedserver may
> help.
> Good Luck!!
> --
> Scott E. Hunley (MCAD)
> Measure Twice, Cut Once!!!
>
> "amy" wrote:
|||If you were able to ping the machines successfully from one another then
that's a good sign. Now, in the Configuration Tool:
1. Right-Click the Alias node on the tree.
2. Enter the Name of the server you are communicating with (make sure these
are exactly the same.
3. Enter a port number, by default SQL uses 1433
4. choose TCP/IP
5. Enter the IP address of the server your communication with.
That should give you a way of communicating to the server.
Try that and let me know.
Scott E. Hunley (MCAD)
Measure Twice, Cut Once...
"amy" wrote:
[vbcol=seagreen]
> thanks for reply
> please tell me how to set up
> Aliases through the configuration tool using TCP\IP
> i done pinging from subsciber from publisher and also publisher to subsciber
> --
> thanking you
> amy
>
> "Scott Hunley" wrote:
|||I got the same error all morning while working with my Replication problems.
I got past this error by going into the properties for the publication and
adding my IUSR_MACHINENAME account to the publication access list. It started
working like a champ. This is with SQL 2005, I'm not sure of how to do it
with SQL 2000.
Hope this helps.
"amy" wrote:

> i am working on merge replication over internet
> i configure each and everything as Paul told in artical
> i configure TCP\IP port at client network utility as 1433, also server at
> server network utility as 1433
> also FTP port at publisher properties is 21
> but it is not working
> it show error as "sql server not exist or access denied"
> i configure client computer using client network utility,
> server computer by server network utility
> also proper publication and subcriber and ftp root
> please solve my problem
> need u'r kind help
> --
> thanking you
> amy

Help me! The log file for database is full

Hi,
Im working with a sql server 2000 bd and i have a bd with simple recovery model. Each day i have the next error:

"The log file for database x is full. Backup the transaction log for the database to free up some log space"

I tried to limit the transaction log file to 500Mb but then I have this error. I have done the reduction manually of transaction log file but the next day i have got the same error. If i dont try to limit, this file grows a lot of (1GB) and then i havent got enough disk space. Can you help me, please?

Thanks a lot.
MemupiI will offer you 2 solutions

1- if you wish to retain the log file information, then bak it up regularly. This will mean that SQL server will reuse the log file space it has backed up. The log file may still grow, although it should level out.

2 - if you don't care about the information held in the log file (I suggest this is true based on your simple recovery model) then you can set this via Query Analyzer

exec sp_dboption $DB, 'trunc. log on chkpt.', 'on'

This will throw away log file segments where all the transactions are committed, and as such keep your log file small. Note it then means that you cannot recover using the transaction log file using this method.

Hope this answers your question|||I have tested the value of this option using the following select:
SELECT DATABASEPROPERTY ('Northwind', 'IsTruncLog')

and the returned value was '1'.

Also if i see the options of database i can see that the option "autoshrink" also is set.

Then, i dont know what is the problem.

Thanks a lot.

Originally posted by dbabren
I will offer you 2 solutions

1- if you wish to retain the log file information, then bak it up regularly. This will mean that SQL server will reuse the log file space it has backed up. The log file may still grow, although it should level out.

2 - if you don't care about the information held in the log file (I suggest this is true based on your simple recovery model) then you can set this via Query Analyzer

exec sp_dboption $DB, 'trunc. log on chkpt.', 'on'

This will throw away log file segments where all the transactions are committed, and as such keep your log file small. Note it then means that you cannot recover using the transaction log file using this method.

Hope this answers your question|||OK - try dbcc loginfo in Query Analyzer in the db you are having probs with. This returns a status field (amoungst others) - 2 is active 0 is inactive. If all the segments are active then the log file will have to grow - it also suggestes that the truncate is not happening. Inactive segments will be reused

Another point to note (re autoshrink) - the log file can only shrink from the end backwards - ie if the active segement is at the end of the file it eill not shrink.|||I have executed loginfo query and the result is only one active segment. But, this segment is the last. Then, the shrink is not effective?? What can i do at this point?

But if i'd execute a manual command to shrink only the log transaction directly from Sql enterprise, the log file would be shorter. What is the reason? I can test this point.

On the other hand, i have limited the file to 500Mb. What happens if i execute a big transaction and i dont have enough space in the log file to save all ? Can i have this problems? When i didnt limite the space of log file i didnt have any error (the problem of space disk, of course).

Originally posted by dbabren
OK - try dbcc loginfo in Query Analyzer in the db you are having probs with. This returns a status field (amoungst others) - 2 is active 0 is inactive. If all the segments are active then the log file will have to grow - it also suggestes that the truncate is not happening. Inactive segments will be reused

Another point to note (re autoshrink) - the log file can only shrink from the end backwards - ie if the active segement is at the end of the file it eill not shrink.|||where the active segment is last, I usually create a dummy table and update the columns in it until the active segment "moves". Using the dbcc command I can track this.

As for a log file of 500Mb - few transactions would require this much space I think, althoughI suspect the transaction would fail if you exceed your imposed limit - def if there is no disk space|||I have tested this, and always that the active segment changes it become a new segment that it is the last. I refer to the last segment as the last FSegNo.

Can i remove the transaction log or config to not use?

Originally posted by dbabren
where the active segment is last, I usually create a dummy table and update the columns in it until the active segment "moves". Using the dbcc command I can track this.

As for a log file of 500Mb - few transactions would require this much space I think, althoughI suspect the transaction would fail if you exceed your imposed limit - def if there is no disk space|||Or how can i change the transaction file to another disk? Then i could not limit the log.

Originally posted by memupi
I have tested this, and always that the active segment changes it become a new segment that it is the last. I refer to the last segment as the last FSegNo.

Can i remove the transaction log or config to not use?|||Can't not use a transaction log - not possible

You can add a second log file through ent manager quite easily. Never actually moved a log file although should be able to - have a look in BOL - alter database command perhaps.|||OK. Thanks for all. You has helped me a lot.

Originally posted by dbabren
Can't not use a transaction log - not possible

You can add a second log file through ent manager quite easily. Never actually moved a log file although should be able to - have a look in BOL - alter database command perhaps.|||Hi

since ur drive is running out of space.
u can free up some space in which the log file is already present
else look out for another which is free of space
1. create a folder to store logfiles
2. go to the properties of the job that is taking the backup of log
3. go to edit and change the location (drive) from previous to present drive where there is enough space

hope it will work

Help me print report button is not working

Hi
I am using Crystal Reports 8.5 version in VB 6.0. I am viewing reports
using CRviewer . When i click on Print report button in CR viewer controlnothing is happining. I am unable to print the report. please help meout. although with same printer settings other files(.Txt,.xls) get printed.
Thanx
RitikIs it possible for you to post the code over here ?