Problem running Proc from Job

I have a stored procedure that runs fine using the Query Analyzer:

exec sp_ProcessRecords

However, when I create a job to run the stored proc once an hour, the job fails, with the following message:

Executed as user: sa. String or binary data would be truncated. [SQLSTATE 22001] (Error 8152) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.

I don't think it's a permission problem, since the job runs as sa.

I don't understand why it would work if I run it manually, but not when it runs as a job.

Any help would be greatly appreciated.Check your parameters that are being passed to the stored procedure. This means that some parameter that is being passed it too long for the datatype and will be truncated.|||Originally posted by rnealejr
Check your parameters that are being passed to the stored procedure. This means that some parameter that is being passed it too long for the datatype and will be truncated.

Unfortunately, the stored proc called from the job does not take any parameters.

The odd thing is that the stored proc will work fine if it is run manually from the Query Analyzer. The error only occurs when the proc is run from the job, using the exact syntax!

I'm at a loss...|||Can you post the stored proc code - or describe what it is doing ? If you are doing inserts/updates then the same problem can occur.sql

Problem Passing Report Parameters in URL

I'm currently trying to create a report that will take three parameters in the URL to generate a report: year, month, and day. The report is using an analysis services data source in RS 2005. Now, my problem is that the parameters being passed in the URL are not being selected in the drop down boxes for the parameters. The report does not throw any exceptions, and after the user selects values for the year, month, and day everything generates successfully.

Here is the URL format I am trying: http://localhost/ReportServer?/FolderName/ReportName&rs:Command=render&Year=2006&Month=6&Day=9

Currently, my parameters are all set to null for default values. They are also not multivalued, not internal, not hidden, don't allow blank, and don't allow null. I have also looked through the valid values in the drop down box for each parameter and verified that the values I supplied in the URL do exist. Also, the data types for each parameter is Integer just like the data type found in the database.

In my opinion, this problem seems directly related to using an analysis services data source. After I was getting this problem, I created a sample report using SQL Server data source, used the same three parameters, and passed them in the URL. Everything on that report worked perfectly.

Please help, I've looked thorugh almost every piece of documentation regarding parameters for Reporting Services and still no luck. Thanks!!

Are you sure those are the names of the parameters and not the prompts?

You can view the parameter names in Report Manager.

|||These are the correct names of the parameters. The prompt names match the actual parameter names.|||

Can you post the portion of the RDL which defines the parameters?

Also make sure that the value you are assigning is really a proper value, and not the label for a proper value.

|||

Actually, I just figured out the problem...

When you are working with an analysis services data source, the parameter values are different from the values listed in the database.

For example, suppose you need the parameter CalendarYear. In the database the values are 2004, 2005, 2006, etc... However, in reporting services the parameter values are actually [DimensionName].[FieldName].&[value] ... or ..... [Date Dim].[CalendarYear].&[2006]

In case you are ever unclear of a parameter value for something, just go to the "Data" tab, click the Dataset for the parameter, ie. "CalendarYear". Then click out of the Design View. Finally, click the execute query button and observe the values listed in the parameter value column. These are the list of valid values which can be used.

As a side note, when adding these parameter values in a URL, you will need to escape the ampersand character in the value expression otherwise it won't work. For example, CalendarYear=[Date Dim].[CalendarYear].%26[2006]

I hope this helps everyone. I know I spent a lot of time working on this.

Thanks!!

|||i am not sure about the answer given above as the correct date time format to acess in url is

Code Snippet
YYYY-MM-DDTHH:MM:SS

And the report that is created above is defalut to query type that should not be right

bye
arya|||

I'm experiencing a similar problem, with a special character in the FieldName part of the parameter:

Someting like:

CalendarYear=[Date Dim].[Calendar ?Year].%26[2006]

I've already tried escaping the ? and characters:

CalendarYear=[Date Dim].[Calendar %abYear%bb].%26[2006]

Any ideas ?

Problem Passing Report Parameters in URL

I'm currently trying to create a report that will take one parameters in the URL to generate a detail report. when i pass parameter as

http://marshad/ReportServer?/UpdatedByClickTest/S&rs:Command=Render&P1=123

it works fine . But i want to send master report's parameter to detail at run time which is selected in master report, then i got an error

i want to use

http://marshad/ReportServer?/UpdatedByClickTest/S&rs:Command=Render&P1=" &Parameters!p1.Value& "

but an error come i,e

" The path of the item '/UpdatedByClickTest/S,Parameters!p1.Value, "' is not valid. The full path must be less than 260 characters long, must start with slash; other restrictions apply. Check the documentation for complete set of restrictions. (rsInvalidItemPath)"

how can i pass parameter to remove this error?

plz any one help me i shall be thankfull to you

Hello Arshad,

Where are you trying to generate the URL?

If you just want to display a link, this will append your parajmeter to the URL:

="http://marshad/ReportServer?/UpdatedByClickTest/S&rs:Command=Render&P1=" + cStr(Parameters!p1.Value)

Hope this helps.

Jarret

|||

Thanks Jarret its working now i m using

="http://marshad/ReportServer?/UpdatedByClickTest/S&rs:Command=Render&P1=" & Parameters!p1.Value

now i want to use global server name becuase i will have to deploy reports to different servers e,g DEV, QA and Production

i m using code like

=Globals!ReportServerUrl & "?/UpdatedByClickTest/S&rs:Command=Render&P1="&Parameters!p1.Value

but its not working plz send me ur code.

|||

That should work, but not while you are designing the report. I tried what you posted and it worked when I deployed the report. When you deploy it to the server and run it from there, you should see the ReportServerURL showing correctly.

Jarret

Problem Passing Report Parameters in URL

I'm currently trying to create a report that will take three parameters in the URL to generate a report: year, month, and day. The report is using an analysis services data source in RS 2005. Now, my problem is that the parameters being passed in the URL are not being selected in the drop down boxes for the parameters. The report does not throw any exceptions, and after the user selects values for the year, month, and day everything generates successfully.

Here is the URL format I am trying: http://localhost/ReportServer?/FolderName/ReportName&rs:Command=render&Year=2006&Month=6&Day=9

Currently, my parameters are all set to null for default values. They are also not multivalued, not internal, not hidden, don't allow blank, and don't allow null. I have also looked through the valid values in the drop down box for each parameter and verified that the values I supplied in the URL do exist. Also, the data types for each parameter is Integer just like the data type found in the database.

In my opinion, this problem seems directly related to using an analysis services data source. After I was getting this problem, I created a sample report using SQL Server data source, used the same three parameters, and passed them in the URL. Everything on that report worked perfectly.

Please help, I've looked thorugh almost every piece of documentation regarding parameters for Reporting Services and still no luck. Thanks!!

Are you sure those are the names of the parameters and not the prompts?

You can view the parameter names in Report Manager.

|||These are the correct names of the parameters. The prompt names match the actual parameter names.|||

Can you post the portion of the RDL which defines the parameters?

Also make sure that the value you are assigning is really a proper value, and not the label for a proper value.

|||

Actually, I just figured out the problem...

When you are working with an analysis services data source, the parameter values are different from the values listed in the database.

For example, suppose you need the parameter CalendarYear. In the database the values are 2004, 2005, 2006, etc... However, in reporting services the parameter values are actually [DimensionName].[FieldName].&[value] ... or ..... [Date Dim].[CalendarYear].&[2006]

In case you are ever unclear of a parameter value for something, just go to the "Data" tab, click the Dataset for the parameter, ie. "CalendarYear". Then click out of the Design View. Finally, click the execute query button and observe the values listed in the parameter value column. These are the list of valid values which can be used.

As a side note, when adding these parameter values in a URL, you will need to escape the ampersand character in the value expression otherwise it won't work. For example, CalendarYear=[Date Dim].[CalendarYear].%26[2006]

I hope this helps everyone. I know I spent a lot of time working on this.

Thanks!!

|||i am not sure about the answer given above as the correct date time format to acess in url is

Code Snippet
YYYY-MM-DDTHH:MM:SS

And the report that is created above is defalut to query type that should not be right

bye
arya|||

I'm experiencing a similar problem, with a special character in the FieldName part of the parameter:

Someting like:

CalendarYear=[Date Dim].[Calendar ?Year].%26[2006]

I've already tried escaping the ? and characters:

CalendarYear=[Date Dim].[Calendar %abYear%bb].%26[2006]

Any ideas ?

Problem Passing Report Parameters in URL

I'm currently trying to create a report that will take three parameters in the URL to generate a report: year, month, and day. The report is using an analysis services data source in RS 2005. Now, my problem is that the parameters being passed in the URL are not being selected in the drop down boxes for the parameters. The report does not throw any exceptions, and after the user selects values for the year, month, and day everything generates successfully.

Here is the URL format I am trying: http://localhost/ReportServer?/FolderName/ReportName&rs:Command=render&Year=2006&Month=6&Day=9

Currently, my parameters are all set to null for default values. They are also not multivalued, not internal, not hidden, don't allow blank, and don't allow null. I have also looked through the valid values in the drop down box for each parameter and verified that the values I supplied in the URL do exist. Also, the data types for each parameter is Integer just like the data type found in the database.

In my opinion, this problem seems directly related to using an analysis services data source. After I was getting this problem, I created a sample report using SQL Server data source, used the same three parameters, and passed them in the URL. Everything on that report worked perfectly.

Please help, I've looked thorugh almost every piece of documentation regarding parameters for Reporting Services and still no luck. Thanks!!

Are you sure those are the names of the parameters and not the prompts?

You can view the parameter names in Report Manager.

|||These are the correct names of the parameters. The prompt names match the actual parameter names.|||

Can you post the portion of the RDL which defines the parameters?

Also make sure that the value you are assigning is really a proper value, and not the label for a proper value.

|||

Actually, I just figured out the problem...

When you are working with an analysis services data source, the parameter values are different from the values listed in the database.

For example, suppose you need the parameter CalendarYear. In the database the values are 2004, 2005, 2006, etc... However, in reporting services the parameter values are actually [DimensionName].[FieldName].&[value] ... or ..... [Date Dim].[CalendarYear].&[2006]

In case you are ever unclear of a parameter value for something, just go to the "Data" tab, click the Dataset for the parameter, ie. "CalendarYear". Then click out of the Design View. Finally, click the execute query button and observe the values listed in the parameter value column. These are the list of valid values which can be used.

As a side note, when adding these parameter values in a URL, you will need to escape the ampersand character in the value expression otherwise it won't work. For example, CalendarYear=[Date Dim].[CalendarYear].%26[2006]

I hope this helps everyone. I know I spent a lot of time working on this.

Thanks!!

|||i am not sure about the answer given above as the correct date time format to acess in url is

Code Snippet
YYYY-MM-DDTHH:MM:SS

And the report that is created above is defalut to query type that should not be right

bye
arya|||

I'm experiencing a similar problem, with a special character in the FieldName part of the parameter:

Someting like:

CalendarYear=[Date Dim].[Calendar ?Year].%26[2006]

I've already tried escaping the ? and characters:

CalendarYear=[Date Dim].[Calendar %abYear%bb].%26[2006]

Any ideas ?

sql

Problem opening local cube with ProClarity 6.2

I am new to BI and have a perplexing problem. I need to create a local cube for offline use in ProClarity 6.2 for mobile users.

I have created a sample cube in AS2005 using the following code:

CREATE GLOBAL CUBE [RainbowBranch]

Storage 'C:\SourceData\RainbowBranch.cub'

FROM [Rainbow Trade Sales]

(

MEASURE [Rainbow Trade Sales].[Adj Sales Value],

DIMENSION [Rainbow Trade Sales].[Branch]

)

The resultant cube can be opened in Excel 2003 with no problems, but when opened in ProClarity, there is no cube name to select, so I can never get to see the cube data.

Is there a problem with my cube build statement? Does it need more in terms of parameters, etc.

Regards,

Charles.

Hello. The coming 6.3 version of ProClarity, supposed to be released any day, will have strong improvements for local cubes. You can have a problem with a bug in 6.2

HTH

Thomas Ivarsson

|||

Thanks Thomas,

I heard about the forthcoming ProClarity 6.3 release. I believe that it will only be available from our vendor at the end of March, which is a problem in terms of making decisions at this point in time. Anyhow, back to my current problem; A colleague sent me a cub file to test, the cub is named "Debtors Analysis" - I think it is part of sample data but I do not know how it was generated (AS2000 perhaps or ProClarity 5.3 ?). This "Debtors Analysis" cub opens perfectly in both ProClarity 6.1 & 6.2 as well as in Excel 2003. This leads me to believe that my problem lies with my cub file - either something missing in my cub file which causes ProClarity to fail, or something extra in the cub file which ProClarity does not recognise.

Are you aware of any other way to create cub files that I can test in the meantime. Preferably not using another 3rd party, commercial product. Thanks.

Regards,

Charles.

|||Have you tried looking on the Proclarity community bulletin boards... There are many similar posts|||I have the same problem to open local cube.

Problem opening local cube with ProClarity 6.2

I am new to BI and have a perplexing problem. I need to create a local cube for offline use in ProClarity 6.2 for mobile users.

I have created a sample cube in AS2005 using the following code:

CREATE GLOBAL CUBE [RainbowBranch]

Storage 'C:\SourceData\RainbowBranch.cub'

FROM [Rainbow Trade Sales]

(

MEASURE [Rainbow Trade Sales].[Adj Sales Value],

DIMENSION [Rainbow Trade Sales].[Branch]

)

The resultant cube can be opened in Excel 2003 with no problems, but when opened in ProClarity, there is no cube name to select, so I can never get to see the cube data.

Is there a problem with my cube build statement? Does it need more in terms of parameters, etc.

Regards,

Charles.

Hello. The coming 6.3 version of ProClarity, supposed to be released any day, will have strong improvements for local cubes. You can have a problem with a bug in 6.2

HTH

Thomas Ivarsson

|||

Thanks Thomas,

I heard about the forthcoming ProClarity 6.3 release. I believe that it will only be available from our vendor at the end of March, which is a problem in terms of making decisions at this point in time. Anyhow, back to my current problem; A colleague sent me a cub file to test, the cub is named "Debtors Analysis" - I think it is part of sample data but I do not know how it was generated (AS2000 perhaps or ProClarity 5.3 ?). This "Debtors Analysis" cub opens perfectly in both ProClarity 6.1 & 6.2 as well as in Excel 2003. This leads me to believe that my problem lies with my cub file - either something missing in my cub file which causes ProClarity to fail, or something extra in the cub file which ProClarity does not recognise.

Are you aware of any other way to create cub files that I can test in the meantime. Preferably not using another 3rd party, commercial product. Thanks.

Regards,

Charles.

|||Have you tried looking on the Proclarity community bulletin boards... There are many similar posts

Problem On Store Report Parameter in Subscription ( by custom UI)

I wrote a interface on my website about create/update subscription .
when I get the parameter back by using GetReportParameters ()
the value was swap
for example:
I have 8 reportparameter: tcust, fcust, fdnnum, tdnnum, fdept, tdept, fairline, tairline.
all of them are string
when I create the subscription by:
tcust 1
fcust 2
fdnnum 3
tdnnum 4
fdept 5
tdept 6
fairline 7
tairline 8

then I get back that subscription
the value will be :
tcust 2

fcust 3

fdnnum 8

tdnnum 5

fdept 1

tdept 7

fairline 4

tairline 6

are there any order for storing the ReportParameter[] ?
thank youYou should not rely on order. ReportParameter has property Name which can be used to identify the parameter. See sample code at http://msdn2.microsoft.com/en-gb/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.getreportparameters.aspx

problem on sending message

Hi

few weeks ago I found an example of conversation using service broker.
I used the following code:

USE Test GO -- First, we need to create a message type. Note that our message type is -- very simple and allowed any type of content CREATE MESSAGE TYPE HelloMessage VALIDATION = NONE GO -- Once the message type has been created, we need to create a contract -- that specifies who can send what types of messages CREATE CONTRACT HelloContract (HelloMessage SENT BY INITIATOR) select * from sys.service_contracts GO -- The communication is between two endpoints. Thus, we need two queues to -- hold messages CREATE QUEUE [SenderQueue] with status = on select * from sys.service_queues --CREATE QUEUE ReceiverQueue Create QUEUE [ReceiverQueue] with status = on select * from sys.service_queues GO -- Create the required services and bind them to be above created queues CREATE SERVICE [Sender] ON QUEUE [SenderQueue] (HelloContract) CREATE SERVICE [Receiver] ON QUEUE [ReceiverQueue] (HelloContract) GO

Then I try to send a message:

DECLARE @.conversationHandle UNIQUEIDENTIFIER DECLARE @.message NVARCHAR(100) BEGIN BEGIN DIALOG @.conversationHandle FROM SERVICE Sender TO SERVICE 'Receiver' ON CONTRACT HelloContract -- Send a message on the conversation SET @.message = N'Hello, World'; SEND ON CONVERSATION @.conversationHandle MESSAGE TYPE HelloMessage (@.message) END

Then I read the message in the ReceiverQueue

RECEIVE message_body FROM dbo.receiverqueue
I get no messages, can you help me to discover why?

A master key has probably not been setup for that database. See the documentation on CREATE MASTER KEY -- http://msdn2.microsoft.com/en-us/library/ms174382.aspx.

Also, when messages cannot be delivered immediately, they are placed in sys.transmission_queue.

-mike

|||

BEGIN DIALOG @.conversationHandle
FROM SERVICE Sender
TO SERVICE 'Receiver'
ON CONTRACT HelloContract
with encryption = off

|||Thanks for helping!

Problem on query operation

Hello, I have my table Produits :

CREATE TABLE [dbo].[Produit] (
[Produit_ID] [int]IDENTITY (1, 1)NOT NULL ,
[Reference] [nvarchar] (50) COLLATE French_CI_ASNULL ,
[Designation] [nvarchar] (50) COLLATE French_CI_ASNULL ,
[Quantite] [int]NULL ,
[PrixU] [sql_variant]NULL ,
[MontantHT] [sql_variant]NULL ,
[TVA] [sql_variant]NULL ,
[Facture_ID] [int]NOT NULL
)
GO
here is stored procedure

 CREATE PROC spBaseTVA_Bis( @.Facture_IDint )AS SELECTSUM(MontantHT)AS montantHT, TVAFROM ProduitGROUP BY TVA, Facture_IDHAVING ( Facture_ID = @.Facture_ID)GOMy stored procedure  fill data in a datagrid with to column  TauxTVA and TVA  like this :
    TauxTVA      TVA
 
But I would like to add a theard column as (the value of the fisrt colum) *  ( the value of the second colum )
    
I would like to desplay data like this  :
    
     TauxTVA      TVA    Prod
       X           Y     X*Y
      
How can I modify my stored procedure to perform this ?
    
Regards
 

Hello,

This one will work with your table definition.

SELECTSUM(convert(int,MontantHT))AS montantHT,convert(int,TVA)as TVA,SUM(convert(int,MontantHT))*convert(int,TVA)as newColFROM

Produit

WHERE

( Facture_ID= @.Facture_ID)

GROUP

BY TVA, Facture_ID

However, I don't think the data types you defined here are approprite. In stead of sql_variant, you can redefine them to something in your case, such as int, float... for your calculations later on.

|||

Thank you !

This work very well;

But it possible to perform this :

TauxTVA TVA Prod
X Y X*Y

Z T Z*T

And in another column X +Z AND X*Y + Z*T

|||

You can use the previous result as a derived table and run a sum on top of it like this:

SELECT

SUM(a.montantHT)as Sum_montantHT,SUM(a. newCol)as new_SumFROM(SELECTSUM(convert(int,MontantHT))AS montantHT,convert(int,TVA)as TVA,SUM(convert(int,MontantHT))*convert(int,TVA)as newColFROM

Produit

WHERE

(

Facture_ID= 1)

GROUP

BY

TVA, Facture_ID)AS a

Problem on getting resultset from remote stored procedure

Hi!
I've a problem on getting resultset from remote stored procedure from the local server.
The stored procedure on the remote server:
create procedure sp_test
begin
create table #temp
( field_a char(10,
field_b char(15) )
insert into #temp
select * from abc
select * from #temp
end
on the local server, the command is
create table #temp
( field_a char(10,
field_b char(15) )
insert into #temp
select * from openquery(LINKEDSERVER,'LINKEDSERVER.DBNAME.dbo.sp_test')
/*****/
the server prompt that table #temp not found
if I simply run LINKEDSERVER.DBNAME.dbo.sp_test
it works.
Actually I need to prepare a stored procedure on the local server to execute sp_temp on remote server.
I tried to use other method to do the task, such as
insert into #temp
exec LINKEDSERVER.DBNAME.dbo.sp_test
but the server prompt that MSDTC service not found in the server.
as i'm not quite familer on MSDTC service, I tried to use other method.
for doing this task, is it a must to start the MSDTC service.
if I start it, what effect will be (performance, loading etc)
please help!! thanks!!
Yeah, for the most part just start DTC and let it go its merry way. It will not have a negative impact on performance. Can somebody check me on this please?|||

Yes, I just afraid the loading and performance would be affected after starting MSDTC.

Problem on ## table

Hi
I have a stored procedure , wherein i create a ## table and get values in
it, i have 3 more procedures which i call from the original procedure which
use the ##table.
When i execute the stored procedure for first time i get the desired
results. when i re-run it again it says the ## table exists . so when i
delete the ## table from tempdb and then re-run it again it works.
Is there a way to delete this ## table right in the begining of execution of
the stored procedure. when i try drop table ## it says the table does not
exists for the first time of execution, if i put the code if (exist whis
table = ##) even this does not work, if i say delte from tempdb.dbo.## even
this does not work
any suggestionTry,
if object_id('tempdb..##t') is not null
drop table ##t
...
AMB
"Rodger" wrote:

> Hi
> I have a stored procedure , wherein i create a ## table and get values in
> it, i have 3 more procedures which i call from the original procedure whic
h
> use the ##table.
> When i execute the stored procedure for first time i get the desired
> results. when i re-run it again it says the ## table exists . so when i
> delete the ## table from tempdb and then re-run it again it works.
> Is there a way to delete this ## table right in the begining of execution
of
> the stored procedure. when i try drop table ## it says the table does not
> exists for the first time of execution, if i put the code if (exist whis
> table = ##) even this does not work, if i say delte from tempdb.dbo.## eve
n
> this does not work
> any suggestion|||It sounds like you should use a local temp table rather than a global
temp table. Specify #tablename rather than ##tablename. Put the DROP
statement at the END of your code that uses the table so that it cleans
up there rather than at the beginning.
David Portas
SQL Server MVP
--|||1) If the stored Procedure is not running multiple instances concurrently,
(i,e., two processes calling it at the same time or overlappiing) Then why
don't you delete it at the end, when the stored Proc is done?
Drop Table ##TableName
or
2) Make it a permanent table and just delete all therecords in it a tteh
beginning (And maybe also at the end) of the stored proc.
or, if the SPs DO run concurrently, you could
3) change the data structure of the temp table so that records being
inserted can be identified as to which instance of the SP they were created
from, (Add an "Instance" or "RunNo" Column) and then modify all other
statements in the SP to affect only those records,
"Rodger" wrote:

> Hi
> I have a stored procedure , wherein i create a ## table and get values in
> it, i have 3 more procedures which i call from the original procedure whic
h
> use the ##table.
> When i execute the stored procedure for first time i get the desired
> results. when i re-run it again it says the ## table exists . so when i
> delete the ## table from tempdb and then re-run it again it works.
> Is there a way to delete this ## table right in the begining of execution
of
> the stored procedure. when i try drop table ## it says the table does not
> exists for the first time of execution, if i put the code if (exist whis
> table = ##) even this does not work, if i say delte from tempdb.dbo.## eve
n
> this does not work
> any suggestion|||You might want to consider writing a single procedure that replaces
this chain of calls. I have found that temp tables can often be
replaced with VIEWs, derived tables or CTEs.
Programming with temp tables in SQL Server is usually a sign that the
programmer is mimicking a procedural file system model of data in which
each of a series of procedural steps is written to a temp table (aka
"Scratch tape") and processed sequentially.

problem of creating database

I am using Visula Studio 2005. When i try to create database from the visual studio Tools option-> careate database and press ok then following error occur......

Failed to generate a user instance of SQL Server due to a failure in copying database files. The connection will be closed.

how can i fix the problem?

first of all you must have a clear picture what is this User intance and do u really want to create user instance. if the answer is yes refer this link the problem is discussed here

http://forums.microsoft.com/msdn/showpost.aspx?postid=98346&siteid=1

If you don't need user instance then go to SQL Server man agement studio and create database

Madhu

|||

i have told that when i am creating database in SQL SERVER 2005, it shows that it can't create instance of sql server. it clearly explains that the user instance is Sql Server instance. See my problem again.......

Here is the error

........

Failed to generate a user instance of SQL Server due to a failure in copying database files. The connection will be closed.

.........

|||

But...do you want to create user instance, or do you want to create a database on a server instance ? If the latter, you will have to use the server explorer, not the Add New Item > Database.

If you want to create a user instance, you will need to have SQLExpress installed on the machine and configured in the database option of Visual Studio.

Jens K. Suessmeyer

http://www.sqlserver2005.de

problem joining table is SQL query

HI All,
Can somebody help what's wrong with my SQL Query statement when i want to
create a join table query. It suppose work perfectly fine, but why it cant
work. The error that the ASP.Net throw out is "Object reference not set to
an instance of an object. " and "System.NullReferenceException: Object
reference not set to an instance of an object." at the line
dtrViewApplication.Close()
I have two table which are Job (JobID, JobTitle) and Application(JobID,
DateApplied, jobseekerID). I want both table field to be joined and display
by using Repeater.
Pls refer to the below for the source code.
Dim jobProvider As OleDbConnection
Dim cmdSelect As OleDbCommand
Dim dtrViewApplication As OleDbDataReader
Dim strSelect As String
jobProvider = New OleDbConnection
("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=C:\Inetpub\wwwroot\JobSeeker\
JobProvider.mdb")
strSelect = "select JobID, DateApplied, Job.JobTitle from
Application, Job where Application.JobID = Job.JobID And " & _
" jobSeekerID=@.jobSeekerID"
cmdSelect = New OleDbCommand(strSelect, jobProvider)
cmdSelect.Parameters.Add("@.LoginName", "JS12345")
Try
jobProvider.Open()
dtrViewApplication = cmdSelect.ExecuteReader
rptApplication.DataSource = dtrViewApplication
rptApplication.DataBind()
Catch ex As Exception
Label1.Text = ex.Message & vbCrLf & ex.Source & vbCrLf &
ex.StackTrace & vbCrLf & ex.TargetSite.Name
Finally
dtrViewApplication.Close()
jobProvider.Close()
End Try
Thanks in advance
--
Message posted via http://www.sqlmonster.comA NullReferenceException means that your code is attempting to use an object
that has not yet been initialized. This may be dtrViewApplication since
it's neither declared nor instantiated in your code snippet. I suggest you
check to ensure dtrViewApplication is instantiated before setting the
DataSource property.
Also, this is a SQL Server forum but it appears you are using Access. The
problem seems to be related more to your ASP.NET code rather than data
access. You'll probably get more help in an ASP.NET forum.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"chng yeekhoon via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f1dd26950b84414b87be4ebfe886a5ac@.SQLMonster.com...
> HI All,
> Can somebody help what's wrong with my SQL Query statement when i want to
> create a join table query. It suppose work perfectly fine, but why it cant
> work. The error that the ASP.Net throw out is "Object reference not set to
> an instance of an object. " and "System.NullReferenceException: Object
> reference not set to an instance of an object." at the line
> dtrViewApplication.Close()
> I have two table which are Job (JobID, JobTitle) and Application(JobID,
> DateApplied, jobseekerID). I want both table field to be joined and
> display
> by using Repeater.
> Pls refer to the below for the source code.
> Dim jobProvider As OleDbConnection
> Dim cmdSelect As OleDbCommand
> Dim dtrViewApplication As OleDbDataReader
> Dim strSelect As String
> jobProvider = New OleDbConnection
> ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
> Source=C:\Inetpub\wwwroot\JobSeeker\
> JobProvider.mdb")
> strSelect = "select JobID, DateApplied, Job.JobTitle from
> Application, Job where Application.JobID = Job.JobID And " & _
> " jobSeekerID=@.jobSeekerID"
>
> cmdSelect = New OleDbCommand(strSelect, jobProvider)
> cmdSelect.Parameters.Add("@.LoginName", "JS12345")
> Try
> jobProvider.Open()
> dtrViewApplication = cmdSelect.ExecuteReader
> rptApplication.DataSource = dtrViewApplication
> rptApplication.DataBind()
> Catch ex As Exception
> Label1.Text = ex.Message & vbCrLf & ex.Source & vbCrLf &
> ex.StackTrace & vbCrLf & ex.TargetSite.Name
> Finally
> dtrViewApplication.Close()
> jobProvider.Close()
> End Try
> Thanks in advance
> --
> Message posted via http://www.sqlmonster.com

Problem inserting decimal data

Hi all,
I have a number of columns in an SQL server table that are of type Decimal.
The problem is, when I create a stored procedure and add decimal data to the
table, the decimal places are chopped off. The number of decimal places to
be stored is set to auto, so that should be ok.
Does anyone know what would cause this?
Thanks to anyone who can help
SimonSimon
Can you show us your INSERT statement?
CREATE TABLE #Test
(
col DECIMAL (18,2)
)
GO
INSERT INTO #Test VALUES (12.5)
INSERT INTO #Test VALUES (10.55)
INSERT INTO #Test VALUES (8.99)
GO
SELECT * FROM #Test
"Simon Harvey" <sh856531@.microsofts_free_email_service.com> wrote in message
news:ueWav8PMEHA.808@.tk2msftngp13.phx.gbl...
> Hi all,
> I have a number of columns in an SQL server table that are of type
Decimal.
> The problem is, when I create a stored procedure and add decimal data to
the
> table, the decimal places are chopped off. The number of decimal places to
> be stored is set to auto, so that should be ok.
> Does anyone know what would cause this?
> Thanks to anyone who can help
> Simon
>

Problem inserting decimal data

Hi all,
I have a number of columns in an SQL server table that are of type Decimal.
The problem is, when I create a stored procedure and add decimal data to the
table, the decimal places are chopped off. The number of decimal places to
be stored is set to auto, so that should be ok.
Does anyone know what would cause this?
Thanks to anyone who can help
Simon
Simon
Can you show us your INSERT statement?
CREATE TABLE #Test
(
col DECIMAL (18,2)
)
GO
INSERT INTO #Test VALUES (12.5)
INSERT INTO #Test VALUES (10.55)
INSERT INTO #Test VALUES (8.99)
GO
SELECT * FROM #Test
"Simon Harvey" <sh856531@.microsofts_free_email_service.com> wrote in message
news:ueWav8PMEHA.808@.tk2msftngp13.phx.gbl...
> Hi all,
> I have a number of columns in an SQL server table that are of type
Decimal.
> The problem is, when I create a stored procedure and add decimal data to
the
> table, the decimal places are chopped off. The number of decimal places to
> be stored is set to auto, so that should be ok.
> Does anyone know what would cause this?
> Thanks to anyone who can help
> Simon
>

Problem inserting decimal data

Hi all,
I have a number of columns in an SQL server table that are of type Decimal.
The problem is, when I create a stored procedure and add decimal data to the
table, the decimal places are chopped off. The number of decimal places to
be stored is set to auto, so that should be ok.
Does anyone know what would cause this?
Thanks to anyone who can help
SimonSimon
Can you show us your INSERT statement?
CREATE TABLE #Test
(
col DECIMAL (18,2)
)
GO
INSERT INTO #Test VALUES (12.5)
INSERT INTO #Test VALUES (10.55)
INSERT INTO #Test VALUES (8.99)
GO
SELECT * FROM #Test
"Simon Harvey" <sh856531@.microsofts_free_email_service.com> wrote in message
news:ueWav8PMEHA.808@.tk2msftngp13.phx.gbl...
> Hi all,
> I have a number of columns in an SQL server table that are of type
Decimal.
> The problem is, when I create a stored procedure and add decimal data to
the
> table, the decimal places are chopped off. The number of decimal places to
> be stored is set to auto, so that should be ok.
> Does anyone know what would cause this?
> Thanks to anyone who can help
> Simon
>

Problem inserting data into SQL Server 2005 DB from WebForm controls

Hello all,

I am having problems running a stored proc from an aspx.cs file. Basically I want to extract data from webForm controls and create a new record in a DB table. I have watched the "Getting Started" videos on thi site, and the only difference between my code and the code of the demonstrator is the connection string.

My conn string:-dataSrc.ConnectionString =ConfigurationManager.ConnectionStrings["ProjectTblConnString"].ConnectionString;

Demonstrator conn string:-dataSrc.ConnectionString =ConfigurationManager.ConnectionStrings("ProjectTblConnString");

When I debug with the string as shown by the demonstrator the error list reports that ConnectionStrings is a property and I am trying to use it as a mothod,
 which is fair enough, but I am just confused as how it worked for the demonstrator and not myself. When I debug with the code I used above I get no error, but nothing is inserted in the DB.

Also, it looks as if the only class in my aspx.cs file that is actually being executed is PageLoad() - the remainder of the code seems to be ignored. Below is the entired aspx.cs file:-

 
1using System;2using System.Data;3using System.Configuration;4using System.Collections;5using System.Web;6using System.Web.Security;7using System.Web.UI;8using System.Web.UI.WebControls;9using System.Web.UI.WebControls.WebParts;10using System.Web.UI.HtmlControls;1112public partialclass CreateProject : System.Web.UI.Page13{14protected void Page_Load(object sender, EventArgs e)15 {1617if (User.Identity.IsAuthenticated ==false)18 {19 Server.Transfer("Default.aspx");20 }2122if (chkbox_startDate.Checked ==true)23 {24 txtbox_startDate.ReadOnly =true;25 txtbox_startDate.Text = (System.DateTime.Now.ToString());26 }2728 }2930protected void btn_create_Click(object sender, EventArgs e)31 {32//connection string for connecting to SQL Server DB33 SqlDataSource dataSrc =new SqlDataSource();34 dataSrc.ConnectionString = ConfigurationManager.ConnectionStrings["ProjectTblConnString"].ConnectionString;353637//insert data in table using the CreateNewProject stored proc in the DB38 dataSrc.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;39 dataSrc.InsertCommand ="CreateNewProject";40 dataSrc.InsertParameters.Add("Project_Title", txtbox_title.ToString() );41 dataSrc.InsertParameters.Add("Description", txtbox_desc.ToString() );42 dataSrc.InsertParameters.Add("Start_Date", txtbox_startDate.ToString() );43 dataSrc.InsertParameters.Add("Primary_Dev_Lang", list_devLang.ToString() );44 dataSrc.InsertParameters.Add("Dev_Environment", list_devEnv.ToString() );45 dataSrc.InsertParameters.Add("No_Junior_Devs", txtbox_juniorDevs.ToString() );46 dataSrc.InsertParameters.Add("No_Senior_Devs", txtbox_seniorDevs.ToString() );4748int rowsAffected = 0;49try50 {51 rowsAffected = dataSrc.Insert();52 }53catch (Exception ex)54 {55 Server.Transfer(Error.aspx);56 }57finally58 {59 dataSrc =null;60 }6162if (rowsAffected != 1)63 {64 label_confirm.Text ="Error, Contact system admin";65 }66else67 {68 label_confirm.Text ="Success";69 }7071 }//end btn_create7273 //if cancel is clicked set all values back to default74protected void btn_cancel_Click(object sender, EventArgs e)75 {76 txtbox_title.Text ="";77 txtbox_desc.Text ="";78 txtbox_startDate.Text ="";79 txtbox_seniorDevs.Text ="";80 txtbox_juniorDevs.Text ="";81 list_devEnv.SelectedIndex = 0;82 list_devLang.SelectedIndex = 0;83 chkbox_startDate.Checked =true;84 label_confirm.Text ="";85 }8687//if checked the current dateTime is inserted into txtbox88protected void chkbox_startDate_CheckedChanged(object sender, EventArgs e)89 {90if (chkbox_startDate.Checked ==true)91 {92 txtbox_startDate.ReadOnly =true;93 txtbox_startDate.Text = (System.DateTime.Now.ToString());94 }95else96 {97 txtbox_startDate.ReadOnly =false;98 txtbox_startDate.Text ="";99 }100 }//end chkox101102103}//end class
 

Any help anyome can give is greatly appreciated. This is part of my Final Year College Dissertation which is due in 3wks !!!!

Sláinte á chaire,

Seán

Hi,

ConnectionStrings is a property name that returns a collection of connection strings. to get the items in the collection, you will need to use [] instead of () in C#. In VB, () is OK. The is the syntax.

Please check the EnableViewState property for each of the textboxes on your page, to see if they have been set to True. If false, the data in it will get lost when postback.

Also, since you're using a SqlDataSource, if CreateNewProject is a stored procedure, the parameter names should contain a @. prefix. Please check in your database to see if the CreateNewProject stored procedure has been created properly. Also use, such as @.Project_Title as parameter name.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

Thanks for the reply Kevin.

As you can probably tell I am a newbie.

I have corrected all the problems you have mentioned, but it looks as if my "Submit" button is not executing any of the code at runtime. Any ideas on this?

problem inserting data in table - all rows are not returned

The procedure is inserting data into this table :
create table temp_XML
(xml_data varchar(8000))
I ran the trace and proc is running fine. But all the data is not
returned when I do select * from temp_xml.
I did some research and limited data by using a where clause.
The proc ran fine in some cases and returns all data and does not runs
fine in some where clauses. What do I need to do to return all the
rows.
But the proc runs fine in production without any limiting where clause
I have different procs like this and some run fine in development and
not in production and vice versa. The procs have successfully ran in
the development before also.
any sugesstions on how to fix this.VJ wrote:
> The procedure is inserting data into this table :
> create table temp_XML
> (xml_data varchar(8000))
> I ran the trace and proc is running fine. But all the data is not
> returned when I do select * from temp_xml.
> I did some research and limited data by using a where clause.
> The proc ran fine in some cases and returns all data and does not runs
> fine in some where clauses. What do I need to do to return all the
> rows.
> But the proc runs fine in production without any limiting where clause
> I have different procs like this and some run fine in development and
> not in production and vice versa. The procs have successfully ran in
> the development before also.
> any sugesstions on how to fix this.
Check if there is a faulty trigger on the table, especially one that
can't handle multiple row inserts. Also, are you sure you are capturing
any error message that the proc might be generating?
If you need more help, please post your code if you can.
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
--|||There are no triggers in the proc.
The proc is running fine in the other database. Something is limiting
the rows inserted in the development database now, but it was fine a
few minutes ago.
Please let me know your email address and I will send you the proc.
David Portas wrote:
> VJ wrote:
> Check if there is a faulty trigger on the table, especially one that
> can't handle multiple row inserts. Also, are you sure you are capturing
> any error message that the proc might be generating?
> If you need more help, please post your code if you can.
> --
> 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
> --