**URGENT** Please Help ! Installation Issue

While installing MSRS i have taken care of all pre-requisites. I am
installing it on Windows 2003 server machine. I want to have the remote
sql-server instance for my reporting services. For Sql-server, i have taken
'Mixed mode installation' and given the username & password as 'sa'. As
specified if sql-server is a remote instance, then we should use sql server
Authentication as the 'Credential Type'.
I have selected the service account as 'Domain account'. And for Report
server database i have selected Sql login option & provided the username &
password as 'sa'. But its giving error as 'Login failed for user
domainname\machinename$'.
The exact error message is given below
"Sql setup failed to connect to the database service for server
configuration. The error was: Login failed for user
'Americas\HTNESX605VM09$'. Refer to the server error logs and setup logs for
more infirmation."
Did anyone encountered this problem before. This is very urgent if anyone
can give there suggestions for this problem.
Note: I am carrying out a remote installation connecting to my VMware server
using Terminal Services.
Thanks in advance.
Rajendra VaishnavSounds to me like the TS machine is not authenticated to do installations on
the SQL Server machine.
-prophead
"Rajendra" wrote:
> While installing MSRS i have taken care of all pre-requisites. I am
> installing it on Windows 2003 server machine. I want to have the remote
> sql-server instance for my reporting services. For Sql-server, i have taken
> 'Mixed mode installation' and given the username & password as 'sa'. As
> specified if sql-server is a remote instance, then we should use sql server
> Authentication as the 'Credential Type'.
> I have selected the service account as 'Domain account'. And for Report
> server database i have selected Sql login option & provided the username &
> password as 'sa'. But its giving error as 'Login failed for user
> domainname\machinename$'.
> The exact error message is given below
> "Sql setup failed to connect to the database service for server
> configuration. The error was: Login failed for user
> 'Americas\HTNESX605VM09$'. Refer to the server error logs and setup logs for
> more infirmation."
> Did anyone encountered this problem before. This is very urgent if anyone
> can give there suggestions for this problem.
> Note: I am carrying out a remote installation connecting to my VMware server
> using Terminal Services.
> Thanks in advance.
> Rajendra Vaishnav
>|||Hi Prophead,
The terminal server should have access to Sql server. While installing MSRS,
i had configured to use Sql authentication. So, ideally when try to connect
to the remote sql server to do the installation it should use Sql
authentication instead of windows authentication. Even if it is trying to use
the windows authentication, i am surprised why it should use an account
something like <domain name>\<servername$> instead I would only expect it to
use <domain name>\<user name>. Kindly advice.
Could this be to do something with installing MSMRS from Terminal services.
I am stuck at this point and need some help in proceeding further.
Thanks in advance.
Rajendra Vaishnav
"prophead" wrote:
> Sounds to me like the TS machine is not authenticated to do installations on
> the SQL Server machine.
> -prophead
> "Rajendra" wrote:
> > While installing MSRS i have taken care of all pre-requisites. I am
> > installing it on Windows 2003 server machine. I want to have the remote
> > sql-server instance for my reporting services. For Sql-server, i have taken
> > 'Mixed mode installation' and given the username & password as 'sa'. As
> > specified if sql-server is a remote instance, then we should use sql server
> > Authentication as the 'Credential Type'.
> >
> > I have selected the service account as 'Domain account'. And for Report
> > server database i have selected Sql login option & provided the username &
> > password as 'sa'. But its giving error as 'Login failed for user
> > domainname\machinename$'.
> >
> > The exact error message is given below
> > "Sql setup failed to connect to the database service for server
> > configuration. The error was: Login failed for user
> > 'Americas\HTNESX605VM09$'. Refer to the server error logs and setup logs for
> > more infirmation."
> >
> > Did anyone encountered this problem before. This is very urgent if anyone
> > can give there suggestions for this problem.
> >
> > Note: I am carrying out a remote installation connecting to my VMware server
> > using Terminal Services.
> >
> > Thanks in advance.
> > Rajendra Vaishnav
> >|||I don't have an answer for you, Ragendra, so I posted this thread as a new
question in hopes one of the MS guys will answer. It is posted under the
subject "Installation Security Issue".
-prophead
"prophead" wrote:
> Sounds to me like the TS machine is not authenticated to do installations on
> the SQL Server machine.
> -prophead
> "Rajendra" wrote:
> > While installing MSRS i have taken care of all pre-requisites. I am
> > installing it on Windows 2003 server machine. I want to have the remote
> > sql-server instance for my reporting services. For Sql-server, i have taken
> > 'Mixed mode installation' and given the username & password as 'sa'. As
> > specified if sql-server is a remote instance, then we should use sql server
> > Authentication as the 'Credential Type'.
> >
> > I have selected the service account as 'Domain account'. And for Report
> > server database i have selected Sql login option & provided the username &
> > password as 'sa'. But its giving error as 'Login failed for user
> > domainname\machinename$'.
> >
> > The exact error message is given below
> > "Sql setup failed to connect to the database service for server
> > configuration. The error was: Login failed for user
> > 'Americas\HTNESX605VM09$'. Refer to the server error logs and setup logs for
> > more infirmation."
> >
> > Did anyone encountered this problem before. This is very urgent if anyone
> > can give there suggestions for this problem.
> >
> > Note: I am carrying out a remote installation connecting to my VMware server
> > using Terminal Services.
> >
> > Thanks in advance.
> > Rajendra Vaishnav
> >

**URGENT** Page Break Error in Custom Report Viewer

Hi Guys,
We are using in our application , the "Report Viewer" Component which comes
as part of the Reporting services Samples .
We are having a problem when viewing the data in "Report Viewer" as
explained below.
There is a Group Expression on the List in the Report and the option "Insert
a page break after this table" on the Table is set.
But when the report is rendered and viewed in the "Report Viewer", no new
pages are created for each group and the data is displayed continuously. But
when we export the report into a "PDF" document, all different groups appear
on different pages properly with proper Page Breaks.
We also have applied the SP1 to the reporting services.
Any help is appreciated..
Regards
Raj ChidipudiI am having the same problem. any luck in finding a result
"Raj Chidipudi" wrote:
> Hi Guys,
> We are using in our application , the "Report Viewer" Component which comes
> as part of the Reporting services Samples .
> We are having a problem when viewing the data in "Report Viewer" as
> explained below.
> There is a Group Expression on the List in the Report and the option "Insert
> a page break after this table" on the Table is set.
> But when the report is rendered and viewed in the "Report Viewer", no new
> pages are created for each group and the data is displayed continuously. But
> when we export the report into a "PDF" document, all different groups appear
> on different pages properly with proper Page Breaks.
> We also have applied the SP1 to the reporting services.
> Any help is appreciated..
> Regards
> Raj Chidipudi
>
>|||I know this may sound odd but try this:
Make sure that your report satisfies the condition specified below
(Report.PageWidth - Report.LeftMargin - Report.Right Margin) >=Body.Width
"doyledirk" <doyledirk@.discussions.microsoft.com> wrote in message
news:BC90664A-2AB3-41D9-AD1D-B1B86D77AAAC@.microsoft.com...
>I am having the same problem. any luck in finding a result
> "Raj Chidipudi" wrote:
>> Hi Guys,
>> We are using in our application , the "Report Viewer" Component which
>> comes
>> as part of the Reporting services Samples .
>> We are having a problem when viewing the data in "Report Viewer" as
>> explained below.
>> There is a Group Expression on the List in the Report and the option
>> "Insert
>> a page break after this table" on the Table is set.
>> But when the report is rendered and viewed in the "Report Viewer", no new
>> pages are created for each group and the data is displayed continuously.
>> But
>> when we export the report into a "PDF" document, all different groups
>> appear
>> on different pages properly with proper Page Breaks.
>> We also have applied the SP1 to the reporting services.
>> Any help is appreciated..
>> Regards
>> Raj Chidipudi
>>
>>

*** Urgent help please ...

Can someone please help me resolve the following -
I have a table consisting of sales orders information with salesmen who lead
the order.
Now I have a need to find average by adding individual salesman's records
and then dividing it by total no. of salesmen in the table.
Ex. Saleman No., Order No.
1, 1
1, 2
2,3
1,4
3,5
3,6
In this case the output should be something like
Salesman #orders
1 3
2 1
3 2
Average = #total no. of orders / #total no. of salesmen
my query is -
select firstname, lastname, count(*) from project
where
ISNULL(CompleteFLAG,'N') ='N' )
group by firstname, lastname
order by firstname
I would like to get the average by adding count(*) for each person/#total
no. of persons.
Thank you,
-Me
Try something like this.
select distinct(Saleman No),count(Order No) as OrderNo from vendor
group by Saleman No
Maninder
MCDBA
|||On 13 Feb, 16:09, Me <M...@.discussions.microsoft.com> wrote:
> Can someone please help me resolve the following -
> I have a table consisting of sales orders information with salesmen who lead
> the order.
> Now I have a need to find average by adding individual salesman's records
> and then dividing it by total no. of salesmen in the table.
> Ex. Saleman No., Order No.
> 1, 1
> 1, 2
> 2,3
> 1,4
> 3,5
> 3,6
> In this case the output should be something like
> Salesman #orders
> 1 3
> 2 1
> 3 2
> Average = #total no. of orders / #total no. of salesmen
> my query is -
> select firstname, lastname, count(*) from project
> where
> ISNULL(CompleteFLAG,'N') ='N' )
> group by firstname, lastname
> order by firstname
> I would like to get the average by adding count(*) for each person/#total
> no. of persons.
> Thank you,
> -Me
It seems like your query contradicts the sample data you used. What is
the key of the Project table? Do you really store the sales person's
name next to each order? Does every order have a sales person
associated with it? Please always include DDL so that we don't have to
guess these things.
Perhaps there are sales people who haven't yet got any orders to their
names? In that case you'll need a SalesPerson table (which I would
hope you have in any case). Here's one possibility:
SELECT COUNT(*)
/(SELECT CAST(COUNT(*) AS REAL) FROM SalesPerson) AS AvgSales
FROM Project;
If you just want to include those who have sales, try:
SELECT COUNT(*)
/(SELECT CAST(COUNT(DISTINCT SalesPersonNo) AS REAL)
FROM Project) AS AvgSales
FROM Project;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Hi David,
Thanks for the reply!
Your solution helped me resolve my issue.
Appreciate your help!
-Me
"David Portas" wrote:

> On 13 Feb, 16:09, Me <M...@.discussions.microsoft.com> wrote:
> It seems like your query contradicts the sample data you used. What is
> the key of the Project table? Do you really store the sales person's
> name next to each order? Does every order have a sales person
> associated with it? Please always include DDL so that we don't have to
> guess these things.
> Perhaps there are sales people who haven't yet got any orders to their
> names? In that case you'll need a SalesPerson table (which I would
> hope you have in any case). Here's one possibility:
> SELECT COUNT(*)
> /(SELECT CAST(COUNT(*) AS REAL) FROM SalesPerson) AS AvgSales
> FROM Project;
> If you just want to include those who have sales, try:
> SELECT COUNT(*)
> /(SELECT CAST(COUNT(DISTINCT SalesPersonNo) AS REAL)
> FROM Project) AS AvgSales
> FROM Project;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>
>
>
|||Hi Maninder,
Thanks for the reply!
Actually I was looking for what David has in his thread.
But appreciate your keeping my tempo going.
Thank you again for your help!
-Me
"Maninder" wrote:

> Try something like this.
> select distinct(Saleman No),count(Order No) as OrderNo from vendor
> group by Saleman No
> Maninder
> MCDBA
>

*** Urgent help please ...

Can someone please help me resolve the following -
I have a table consisting of sales orders information with salesmen who lead
the order.
Now I have a need to find average by adding individual salesman's records
and then dividing it by total no. of salesmen in the table.
Ex. Saleman No., Order No.
1, 1
1, 2
2,3
1,4
3,5
3,6
In this case the output should be something like
Salesman #orders
1 3
2 1
3 2
Average = #total no. of orders / #total no. of salesmen
my query is -
select firstname, lastname, count(*) from project
where
ISNULL(CompleteFLAG,'N') ='N' )
group by firstname, lastname
order by firstname
I would like to get the average by adding count(*) for each person/#total
no. of persons.
Thank you,
-MeTry something like this.
select distinct(Saleman No),count(Order No) as OrderNo from vendor
group by Saleman No
Maninder
MCDBA|||On 13 Feb, 16:09, Me <M...@.discussions.microsoft.com> wrote:
> Can someone please help me resolve the following -
> I have a table consisting of sales orders information with salesmen who le
ad
> the order.
> Now I have a need to find average by adding individual salesman's records
> and then dividing it by total no. of salesmen in the table.
> Ex. Saleman No., Order No.
> 1, 1
> 1, 2
> 2,3
> 1,4
> 3,5
> 3,6
> In this case the output should be something like
> Salesman #orders
> 1 3
> 2 1
> 3 2
> Average = #total no. of orders / #total no. of salesmen
> my query is -
> select firstname, lastname, count(*) from project
> where
> ISNULL(CompleteFLAG,'N') ='N' )
> group by firstname, lastname
> order by firstname
> I would like to get the average by adding count(*) for each person/#total
> no. of persons.
> Thank you,
> -Me
It seems like your query contradicts the sample data you used. What is
the key of the Project table? Do you really store the sales person's
name next to each order? Does every order have a sales person
associated with it? Please always include DDL so that we don't have to
guess these things.
Perhaps there are sales people who haven't yet got any orders to their
names? In that case you'll need a SalesPerson table (which I would
hope you have in any case). Here's one possibility:
SELECT COUNT(*)
/(SELECT CAST(COUNT(*) AS REAL) FROM SalesPerson) AS AvgSales
FROM Project;
If you just want to include those who have sales, try:
SELECT COUNT(*)
/(SELECT CAST(COUNT(DISTINCT SalesPersonNo) AS REAL)
FROM Project) AS AvgSales
FROM Project;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi David,
Thanks for the reply!
Your solution helped me resolve my issue.
Appreciate your help!
-Me
"David Portas" wrote:

> On 13 Feb, 16:09, Me <M...@.discussions.microsoft.com> wrote:
> It seems like your query contradicts the sample data you used. What is
> the key of the Project table? Do you really store the sales person's
> name next to each order? Does every order have a sales person
> associated with it? Please always include DDL so that we don't have to
> guess these things.
> Perhaps there are sales people who haven't yet got any orders to their
> names? In that case you'll need a SalesPerson table (which I would
> hope you have in any case). Here's one possibility:
> SELECT COUNT(*)
> /(SELECT CAST(COUNT(*) AS REAL) FROM SalesPerson) AS AvgSales
> FROM Project;
> If you just want to include those who have sales, try:
> SELECT COUNT(*)
> /(SELECT CAST(COUNT(DISTINCT SalesPersonNo) AS REAL)
> FROM Project) AS AvgSales
> FROM Project;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>
>
>|||Hi Maninder,
Thanks for the reply!
Actually I was looking for what David has in his thread.
But appreciate your keeping my tempo going.
Thank you again for your help!
-Me
"Maninder" wrote:

> Try something like this.
> select distinct(Saleman No),count(Order No) as OrderNo from vendor
> group by Saleman No
> Maninder
> MCDBA
>

*** Urgent help please ...

Can someone please help me resolve the following -
I have a table consisting of sales orders information with salesmen who lead
the order.
Now I have a need to find average by adding individual salesman's records
and then dividing it by total no. of salesmen in the table.
Ex. Saleman No., Order No.
1, 1
1, 2
2,3
1,4
3,5
3,6
In this case the output should be something like
Salesman #orders
1 3
2 1
3 2
Average = #total no. of orders / #total no. of salesmen
my query is -
select firstname, lastname, count(*) from project
where
ISNULL(CompleteFLAG,'N') ='N' )
group by firstname, lastname
order by firstname
I would like to get the average by adding count(*) for each person/#total
no. of persons.
Thank you,
-MeTry something like this.
select distinct(Saleman No),count(Order No) as OrderNo from vendor
group by Saleman No
Maninder
MCDBA|||On 13 Feb, 16:09, Me <M...@.discussions.microsoft.com> wrote:
> Can someone please help me resolve the following -
> I have a table consisting of sales orders information with salesmen who lead
> the order.
> Now I have a need to find average by adding individual salesman's records
> and then dividing it by total no. of salesmen in the table.
> Ex. Saleman No., Order No.
> 1, 1
> 1, 2
> 2,3
> 1,4
> 3,5
> 3,6
> In this case the output should be something like
> Salesman #orders
> 1 3
> 2 1
> 3 2
> Average = #total no. of orders / #total no. of salesmen
> my query is -
> select firstname, lastname, count(*) from project
> where
> ISNULL(CompleteFLAG,'N') ='N' )
> group by firstname, lastname
> order by firstname
> I would like to get the average by adding count(*) for each person/#total
> no. of persons.
> Thank you,
> -Me
It seems like your query contradicts the sample data you used. What is
the key of the Project table? Do you really store the sales person's
name next to each order? Does every order have a sales person
associated with it? Please always include DDL so that we don't have to
guess these things.
Perhaps there are sales people who haven't yet got any orders to their
names? In that case you'll need a SalesPerson table (which I would
hope you have in any case). Here's one possibility:
SELECT COUNT(*)
/(SELECT CAST(COUNT(*) AS REAL) FROM SalesPerson) AS AvgSales
FROM Project;
If you just want to include those who have sales, try:
SELECT COUNT(*)
/(SELECT CAST(COUNT(DISTINCT SalesPersonNo) AS REAL)
FROM Project) AS AvgSales
FROM Project;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi David,
Thanks for the reply!
Your solution helped me resolve my issue.
Appreciate your help!
-Me
"David Portas" wrote:
> On 13 Feb, 16:09, Me <M...@.discussions.microsoft.com> wrote:
> > Can someone please help me resolve the following -
> >
> > I have a table consisting of sales orders information with salesmen who lead
> > the order.
> >
> > Now I have a need to find average by adding individual salesman's records
> > and then dividing it by total no. of salesmen in the table.
> >
> > Ex. Saleman No., Order No.
> > 1, 1
> > 1, 2
> > 2,3
> > 1,4
> > 3,5
> > 3,6
> >
> > In this case the output should be something like
> >
> > Salesman #orders
> > 1 3
> > 2 1
> > 3 2
> >
> > Average = #total no. of orders / #total no. of salesmen
> >
> > my query is -
> >
> > select firstname, lastname, count(*) from project
> > where
> > ISNULL(CompleteFLAG,'N') ='N' )
> > group by firstname, lastname
> > order by firstname
> >
> > I would like to get the average by adding count(*) for each person/#total
> > no. of persons.
> >
> > Thank you,
> > -Me
> It seems like your query contradicts the sample data you used. What is
> the key of the Project table? Do you really store the sales person's
> name next to each order? Does every order have a sales person
> associated with it? Please always include DDL so that we don't have to
> guess these things.
> Perhaps there are sales people who haven't yet got any orders to their
> names? In that case you'll need a SalesPerson table (which I would
> hope you have in any case). Here's one possibility:
> SELECT COUNT(*)
> /(SELECT CAST(COUNT(*) AS REAL) FROM SalesPerson) AS AvgSales
> FROM Project;
> If you just want to include those who have sales, try:
> SELECT COUNT(*)
> /(SELECT CAST(COUNT(DISTINCT SalesPersonNo) AS REAL)
> FROM Project) AS AvgSales
> FROM Project;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>
>
>|||Hi Maninder,
Thanks for the reply!
Actually I was looking for what David has in his thread.
But appreciate your keeping my tempo going.
Thank you again for your help!
-Me
"Maninder" wrote:
> Try something like this.
> select distinct(Saleman No),count(Order No) as OrderNo from vendor
> group by Saleman No
> Maninder
> MCDBA
>

* * * Passing Parameter - Urgent * * *

I program an ASP.NET application that uses SQL Server 2005 Reporting
Services.
Recently I came into a dead end when I had to pass a parameter from my
application
to the report. I 've looked over the Internet and found absolutely nothing.
If someone is
capable of doing that he/she doesn't have to be shy to share it with me.
Thanks in advance,
Nick Hatzis
P.S. : To answer by e-mail remove the "bog" from the username.Hi,
What kind of parameter you must pass?|||Hi Cesar,
I have to pass four string parameters.
Two that will appear in the report and two
(from-to) that will be used in the query.
Thanks in advance,
Nick Hatzis
P.S. : To answer by e-mail remove the "bog" from the username.
"Cesar" <Cesar@.discussions.microsoft.com> wrote in message
news:490E74EF-519C-4D5E-BB69-C8412CB909C2@.microsoft.com...
> Hi,
> What kind of parameter you must pass?|||Are you using an URL to open the report? In that case, you should look at
the topic "URL access" in the SQL Server 2005 Books Online.
If you have it locally, this should be the URL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsprg9/html/52c3f2a3-3d6d-4fee-9c46-83f366919398.htm
Or find it online at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_urlaccess_374y.asp
Kaisa M. Lindahl Lervik
"Nick Hatzis" <boghatzisn@.panafonet.gr> wrote in message
news:%23MK70oQ7GHA.1012@.TK2MSFTNGP05.phx.gbl...
>I program an ASP.NET application that uses SQL Server 2005 Reporting
> Services.
> Recently I came into a dead end when I had to pass a parameter from my
> application
> to the report. I 've looked over the Internet and found absolutely
> nothing.
> If someone is
> capable of doing that he/she doesn't have to be shy to share it with me.
> Thanks in advance,
> Nick Hatzis
> P.S. : To answer by e-mail remove the "bog" from the username.
>|||On Wed, 11 Oct 2006 11:01:52 +0300, "Nick Hatzis"
<boghatzisn@.panafonet.gr> wrote:
>I program an ASP.NET application that uses SQL Server 2005 Reporting
>Services.
>Recently I came into a dead end when I had to pass a parameter from my
>application
>to the report. I 've looked over the Internet and found absolutely nothing.
>If someone is
>capable of doing that he/she doesn't have to be shy to share it with me.
>Thanks in advance,
>Nick Hatzis
>P.S. : To answer by e-mail remove the "bog" from the username.
>
Are you using reportviewer or just going direct to the URL? If direct
to the URL, something like this:
Response.Redirect("http://199.199.199.199/ReportServer?%2fReportProjectName%2fReportName&rs:Command=Render&rs:Format=PDF&IssueID=11000")
That IssueID at the end is a parameter. Note the rs:Format that gets
the report directly as a PDF.
If you're using a reportviewer control, parameters are done like this:
Dim RptParm As New
Microsoft.Reporting.WebForms.ReportParameter("FileNumParam",
lblFileNum.Text) ' name/value pair for a parameter
Dim RptParms As New Generic.List(Of
Microsoft.Reporting.WebForms.ReportParameter)
RptParms.Add(RptParm)
' add more parameters as necessary to RptParams
Reportviewer1.ServerReport.SetParameters(RptParms)

(urgent)strange behaviour in excel file when previewed in SqlServer (importData)

Hi,

I am trying to import a excel file in to my database... The improt works fine.. But some of the data is missing though it is present in the excel spread sheet.

I have some data for cusip which are 9 characters and they be a combination of numbers wiht a letter.. for eg.. 123456789 or 12345R789. And in the my spread sheet there are around 73 rows.. until the 62 row it has numbers like 123456789 and from the 62 to 73 it has 12345R789 this is an just example but the data is in that format.

I went to sqlServer 2005 and imported the data using Tasks -ImportData and selected my excel spreadsheet, the user name and password for the database and i selected the sheet i want to import.. and when i preview the data ... Until the 62 row i can see the numbers and after that i cannot see any data in that column.... and when i import the data from 62 row the value is NULL...

So can some please tell me what going on why isnt that data been recognised by the importer in sql server.

Any help will be appreciated.

Regards

Karen

Seems to me that the data being imported is being imported as an int and hence the values which are not integer (row 62 and beyond) do not get imported into the table. However, this is just my guess. You might get better answer from Integration Services forum and I am moving this thread to the forum for you to get better assistance.

HTH,

|||

Yes that is correct. SSIS will look at the first few rows of Excel data to determine what the data type of the column is. As far as I know there is no easy way around that. Your best option is to ensure that one of the "R" values is in the first few rows.

Otherwise, you could do an import from .csv isntead of from Excel. That would give you more control.

Dylan.

|||

I struggle with the strange behaviour of excel imports for days now..

what helped a lot was this articel:

http://blog.lab49.com/?p=196

I hope it helps you too...

Mybe someone has a solution to my urgent excel import problem too... please look here http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2129460&SiteID=1

if you have a minute left...

Thanks

Jens

|||

Thank you every one for your input... I solved the problem by changing the cell format as text and then copied all the information back.. to the excel spread sheet as text and everything works fine...

Regards

Karen

(urgent)strange behaviour in excel file when previewed in SqlServer (importData)

Hi,

I am trying to import a excel file in to my database... The improt works fine.. But some of the data is missing though it is present in the excel spread sheet.

I have some data for cusip which are 9 characters and they be a combination of numbers wiht a letter.. for eg.. 123456789 or 12345R789. And in the my spread sheet there are around 73 rows.. until the 62 row it has numbers like 123456789 and from the 62 to 73 it has 12345R789 this is an just example but the data is in that format.

I went to sqlServer 2005 and imported the data using Tasks -ImportData and selected my excel spreadsheet, the user name and password for the database and i selected the sheet i want to import.. and when i preview the data ... Until the 62 row i can see the numbers and after that i cannot see any data in that column... and when i import the data from 62 row the value is NULL...

So can some please tell me what going on why isnt that data been recognised by the importer in sql server.

Any help will be appreciated.

Regards

Karen

A short answer(work around): import throgh an Access table will address this issue.

Or change the numbers in this column to text by adding an ' in front of these numbers before your import.

I hope someone can provide more elegant solution(s) to this issue.

(URgent)Problem with excel File

Hi,

I am trying to import data from a spread sheet to a sql server database... and one of the cells contains which are numeric and only and some are alpha numeric also... but when i try to import them to Sql server i get a NULL in the cells where there is Alpha numeric characters...

I have also tried opening a new spread sheet and setting the format for that particular column and text then i just paste it and then saving,.. but when i try to upload the data i am getting an error (thru my asp.net website) Saying that

No value given for one or more required parameters.

Hope someone can help me solve this.....

Regards

Karen

You could probably use BCP.exe to get this done quickly. The process would be to save the excel file as a .csv, then strip off the headers, if you use tab as your columns separator and \n as your row terminator, then you should be able to bcp it in with the -c option and no further work. Otherwise, you might need to create a format file. More information on how to use BCP can be found in books online for bcp.exe.

Hope that helps,

John

|||

Have you tried using OPENROWSET?

|||

Whats that about.. The user has data in spread sheet and right now i have built a interface that the user can directly log in and upload the excel files and then it will transfer the data to the database.. it works fine if a particular column doesnt have alpha numeric characters..

Regards

Karen

|||

Check out the following KB articles

http://support.microsoft.com/kb/321686 - This address's possible methods for importing from Excel to SQL Sever

Also http://support.microsoft.com/kb/194124/EN-US/

This article directly address's the issue of "Excel Values Returned as NULL Using DAO OpenRecordset"

This sounds like the issue you are facing

Regards

Nadreck

(URgent)Problem with excel File

Hi,

I am trying to import data from a spread sheet to a sql server database... and one of the cells contains which are numeric and only and some are alpha numeric also... but when i try to import them to Sql server i get a NULL in the cells where there is Alpha numeric characters...

I have also tried opening a new spread sheet and setting the format for that particular column and text then i just paste it and then saving,.. but when i try to upload the data i am getting an error (thru my asp.net website) Saying that

No value given for one or more required parameters.

Hope someone can help me solve this.....

Regards

Karen

You could probably use BCP.exe to get this done quickly. The process would be to save the excel file as a .csv, then strip off the headers, if you use tab as your columns separator and \n as your row terminator, then you should be able to bcp it in with the -c option and no further work. Otherwise, you might need to create a format file. More information on how to use BCP can be found in books online for bcp.exe.

Hope that helps,

John

|||

Have you tried using OPENROWSET?

|||

Whats that about.. The user has data in spread sheet and right now i have built a interface that the user can directly log in and upload the excel files and then it will transfer the data to the database.. it works fine if a particular column doesnt have alpha numeric characters..

Regards

Karen

|||

Check out the following KB articles

http://support.microsoft.com/kb/321686 - This address's possible methods for importing from Excel to SQL Sever

Also http://support.microsoft.com/kb/194124/EN-US/

This article directly address's the issue of "Excel Values Returned as NULL Using DAO OpenRecordset"

This sounds like the issue you are facing

Regards

Nadreck

(urgent)Insert data from a excel Spread sheet to SQl server

Hi,

I have a spread sheet which has 4 columns called cusip, Chartheader, growthdates and NAV.. and i also have the same number of columns in the Sql server... and I want to add another column called Rownumber and set it as int indentity... and when i try to import the data to sql server i am getting this error called

Received an invalid column length from the bcp client for colid 1.

How should i fix it.

Regards

Karen

usual way is:

1). Import data from Excel sheet;

2). Change the column names, format data if necessary;

3). Add an identity column.

|||

Without the int indentity column the import works fine... The reason i want to add the row number to the table is becauase after i transfer it to a staging database i am scrubbing the data and then inserting it to another database. But in the process of transferring data from the staging database to the actual databse the dates get jumbled up and is not in the same order that it was in the staging database.. and because of that my data in a particular sub report is not right.. this is what i am doing... in my asp.net program

If (flGrowth10K.PostedFile.FileName.ToLower.EndsWith(".xls")Or flGrowth10K.PostedFile.FileName.ToLower.EndsWith(".csv"))Then flGrowth10K.PostedFile.SaveAs(location6)' Connection String to Excel WorkbookTry Dim excelConnectionStringAs String =String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", location6)'"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=location;Extended Properties=Excel 8.0" ' Create Connection to Excel Workbook Using connectionAs Data.OleDb.OleDbConnection =New Data.OleDb.OleDbConnection(excelConnectionString)Dim commandAs Data.OleDb.OleDbCommand =New Data.OleDb.OleDbCommand("Select Cusip,FundName,ChartHeader,GrowthDates,GrowthNAV FROM [Sheet1$]", connection) connection.Open()' Create DbDataReader to Data Worksheet Using drAs Data.Common.DbDataReader = command.ExecuteReader()' SQL Server Connection StringDim sqlConnectionStringAs String = System.Configuration.ConfigurationManager.AppSettings(APPSETTINGS_CONNECTION)'"Data Source=.; Initial Catalog=Test;Integrated Security=True"Dim myconnectionAs New SqlConnection(System.Configuration.ConfigurationManager.AppSettings(APPSETTINGS_CONNECTION))Dim queryAs String ="Truncate Table Growth" myconnection.Open()Dim cmdAs New SqlCommand(query, myconnection) cmd.CommandType = CommandType.Text cmd.ExecuteScalar() myconnection.Close()' Bulk Copy to SQL Server Using bulkCopyAs SqlBulkCopy =New SqlBulkCopy(sqlConnectionString) bulkCopy.DestinationTableName ="Growth" bulkCopy.WriteToServer(dr)End UsingEnd Using connection.Close()End UsingCatch exAs ExceptionThrow exEnd Try End If Try oImportFunds =New ImportFunds(System.Configuration.ConfigurationManager.AppSettings(APPSETTINGS_CONNECTION))With oImportFunds .PeriodID = ddlPeriod.SelectedItem.Value .save()End With Catch exAs Exception lblErrorMsg.Text ="Error: Import Failed" + ex.Message lblErrorMsg.Visible =True End Try

I was wondering if i have to add Rownumber to the select clause in the oledbstatement too...

any help will be appreciated

Regards

Karen

|||

I am sorry that I didn't know you did it in your application. The error happen, I think, because your excel columns don't match your table's columns (Your table column 1 is an identity integer data now. Your application tried to insert Cusipinto this first column). If I was you, I will let the program work first, then mannually add the identity column after data imported.

|||

Thanks for your suggestion... But i cannot do that cause everytime the user updates the tables using the spread sheets i cannot go in add the row number. The reason is we have given full access to that co. to use our database. .. is there a way to specify in the oledb statement that the first column is an int identity... something in the select statement. right now i dont have the Row number in my database... so it works fine.. but on some imports things get imported as it is in the staging database and some some entries get jumbled up.

Regards

Karen

|||

Just curious(I doubt it will work) to see what happens if you change your SELECT to "Select 0, Cusip,FundName,ChartHeader,GrowthDates,GrowthNAV FROM [Sheet1$]"

|||

che3358,

I think so it worked the way u told me to do...any ways let me update my dbase and try running it from a different server...

and will let u know the results.//

Regards

Karen

|||

here's a quick and dirty way to do it, suitable for a one-time solution:

copy the table definition (less the identify and timestamp columns) and create a new table, temp_whatever

Make sure the column order in the table matches the column order in the spreadsheet.

Open the table in sql server manager.

Copy the data in the spreadsheet, minus column headings.

Paste it into the temp table.

use this statement, adjusting for table and column names, and ignoring all identify and timestamp columns:

insert into real_table (col1name, col2name, col3name,...) select col1name, col2name, col3name, ... from temp_whatever.

when done, drop temp_whatever.

:)

(urgent)how should i treat signed decimal values in sql server

Hi,

I want to transfer the data from a excel spread sheet to sql server. I have used the oledb comand and it works fine, and i have also used the sqlbulkcopy and it transfers the data properly.

But in my excel spread sheet i have many columns with data as -0.76 or 0.76 or something like that but when it transfer it to sqlserver that particular column in sql server shows it as 0.00762711864406778

So how i can i tell sql server to display the data as 0.76 instead of 0.00762711864406778

any help will be appreciated.

Regards

Karen

Hi,

The only thing I can think of is the cells in excel with your decimal numbers is formated 1. to display 2 decimal places only, whilst the value is much more precise 2. (that's guess) cell is formated as percentage, so after convesion there is decimal point moved 2 places left ....

Hope at least one of these is true

Tomek

|||

I changed the Format from Percentage to general but it still doesnt work.|||

So you've changed cell formating to general it shows in spreadsheet 0.76 and after transfer you get 0.00762711864406778

What db type has destination column, do you use vb(or any other language) or dts to transfer it?

Try to transfer it to flat file and then see which side cause problem.

Tomek

|||

I am using a oledb connection to connect to the excel spreadsheet and then using a sqlbulkcopy to copy it to sql server. Have changed the dbase field from varchar to money and when it was varchar i get 0.007627118464406778 in the db column and when i changed to money it is been stores as 0.0076

Regards

Karen

|||

This is strange, you changed the cell format from percentage to general and the cells still display 0.76? That seems wrong to me. When you change to general, it should change to .0076 right? So all we are left with as a mystery is why you only see .0076 and if you copy it over to SQL Server you see .0076.....otherstuff, right? Did you change it to "general" or "number"? Number and percentage have a box with "decimal places" in it, that specify how many decimal places to show, but general should show all of them up to the width of the box, the rest are truncated just like any other overflowing field. Is this what is happening? If you go to "number" and increase "decimal places" under formatting options to 10 or 50, does it show some of what SQL Server shows you?

If so, you should consider that Excel is different from SQL Server in that Excel is designed to be both front-end and database at the same time, so it understands the idea of truncating data on display implicitly while separately storing the truth. SQL Server does not implicitly do so, you would need to write a stored procedure or a query that explicitly rounds to see rounded numbers, so you might do 'SELECT round(column, 2.) from table' to get that rounding behavior that you desire. If you want a rounded table, meaning that the data itself is truncated, then you can write a quick t-sql statement to truncate the data in your table but that would affect the accuracy of any further calculations you do from that data.

Hope that helps,

John

|||

John,

Thanks for your answer, I am not doing any calculation for this field, it just gonna displayed in a report. so in the report i will multiply the field by 100 and that should give the right result.

Regards

Karen

(urgent)how should i treat signed decimal values in sql server

Hi,

I want to transfer the data from a excel spread sheet to sql server. I have used the oledb comand and it works fine, and i have also used the sqlbulkcopy and it transfers the data properly.

But in my excel spread sheet i have many columns with data as -0.76 or 0.76 or something like that but when it transfer it to sqlserver that particular column in sql server shows it as 0.00762711864406778

So how i can i tell sql server to display the data as 0.76 instead of 0.00762711864406778

any help will be appreciated.

Regards

Karen

You probably have the columns in excel formatted as a percentage column.

Multiply by 100 and round to the nearest .01

|||

the columns excel are in General, i have tried currency and number and text and they dont work

Regards

Karen

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly