My application is usningtraditional connection string:
<
addkey="DbConnection"value="driver={SQL Native Driver}; provider=SQLNCLI;server=localhost;trusted_connection=yes;database=ebay" />It works fine with database attached to SQL Server, how do I make this working with SQL Express attachable file in data directory?
I have tried this, but it does not work:
Provider=SQLNCLI;Server=RAF001\SQLExpress;AttachDbFilename=..\TestSite\App_Data\ebay.mdf;Database=ebay;Trusted_Connection=Yes;
I have a date value on a sql server database.
When viewing this value using Enterprise Manager Tools it shows the date as 2006-11-23 10:59:52
When i load this value into my webservice object to pass to my handheld device its shows the date as 2006-11-23T10:59:51.6130000-00:00
Im trying to retrieve a date value from my sql server ce 2.0 database.
When i use Query Analyser on the device and view the date it shows 2006-11-23 10:59:51.613000000
Now in my application when i use .ExecuteScalar or .ExecuteReader and reader.GetDate() to retrieve this value it returns 2006-11-23 10:59:51
I need to pass the date from the device to the webservice which will return any records greater than this date but since there is a minute difference in the dates it does not work right....
How can i sort this out?
My only idea is to convert the date being inserted on the device database to be of yyyy-mm-dd hh:mm:ss only
DateTime is not stored as yyyy-mm-dd or as MM-DD-YY or whatever other string representations of the date and time there are.
Here's what date time really is (http://msdn2.microsoft.com/en-us/library/ms172424.aspx):
Date and time data from January 1, 1753, to December 31, 9999, with an accuracy of one three-hundredth second, or 3.33 milliseconds. Values are rounded to increments of .000, .003, or .007 milliseconds.
Stored as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system's reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight. Seconds have a valid range of 0–59.
My point is: these dates of yours are the same, just printed and rounded differently. I’m assuming you mean a second difference, not minute - note EM rounds 1.6 second to 2 resulting in 10:59:52 while printing it on device results in 10:59.51 with .6 second been truncated.
sql
I have 5 cascading parameters. They all have default values. I would like the default value of each of these to be selected when the report opens.
It currently works correctly when I preview the report, however when I deploy the report to the server it does not. In the deployed report, the default value of the first parameter is not selected. However, when I select a value for this one, on postback the rest of these parameters get set to their default value. They're all configured the same. I'm confused as to why the first one doesn't default to its default value while the others do.
Got any ideas?
Here's how I have it configured:
Allow null value CHECKED
Allow blank value CHECKED
The rest are unchecked.
Available values: From query
Default Values: Non-queried with a value supplied that exists in the dataset.
This issue got resolved when I deleted all of my reports from the server and then redeployed them. Funny how that works...sqlI have 5 cascading parameters. They all have default values. I would like the default value of each of these to be selected when the report opens.
It currently works correctly when I preview the report, however when I deploy the report to the server it does not. In the deployed report, the default value of the first parameter is not selected. However, when I select a value for this one, on postback the rest of these parameters get set to their default value. They're all configured the same. I'm confused as to why the first one doesn't default to its default value while the others do.
Got any ideas?
Here's how I have it configured:
Allow null value CHECKED
Allow blank value CHECKED
The rest are unchecked.
Available values: From query
Default Values: Non-queried with a value supplied that exists in the dataset.
This issue got resolved when I deleted all of my reports from the server and then redeployed them. Funny how that works...Hello to all:
I have this request:
SELECT code_statusfac FROM factures WHERE code_statusfac in (:FILTRE)
While :FILTRE that can equal to some value, example:
' DIS02 ', ' DIS03 ', ' DIS04 ', etc.
When I execute the request and I replace:FILTRE by DIS04 that works well, but when I want to allocate him some value at the same moment it costs not.
What is what you have ideas PLEASE.
Thank you in advance.
Simple answer is use dynmaic query...
But be cautious about sql injection & other security concerns (it needs SELECT permission the table object).
Code Snippet
Exec ('SELECT code_statusfac FROM factures WHERE code_statusfac in (' + @.ListOfValues + ')')
Hi,
I would like to use the view of the company data in the following stored procedures
without needing to pass a value into the CompanyID identity column. Is there a way to do this? The following code contains the view, the insert trigger on the view, and the desired stored procedures.
Also, this code is given me problem as well the error message says it cannot convert varchar to int. This code is located in the insert trigger.
INSERT INTO State(StateName)
VALUES(@.StateName)
Here is the code of the company view, notice the C.CompanyID it is used in the GetCompany stored procedure.
SELECT C.CompanyID, C.CompanyName, A.Street, A.City, S.StateName, A.ZipCode, A.Country, P.PhoneNumber
FROM Company AS C INNER JOIN
Address AS A ON C.AddressID = A.AddressID INNER JOIN
State AS S ON A.StateID = S.StateID INNER JOIN
Phone AS P ON C.PhoneID = P.PhoneID
Here is code for the insert trigger on the view
CREATE TRIGGER InsertTriggerOnCustomerView
ON ViewOfCompany
INSTEAD OF INSERT
AS
BEGIN
DECLARE @.CompanyName VARCHAR(128)
DECLARE @.AddressID INT
DECLARE @.Street VARCHAR(256)
DECLARE @.City VARCHAR(128)
DECLARE @.StateID INT
DECLARE @.StateName VARCHAR(128)
DECLARE @.ZipCode INT
DECLARE @.Country VARCHAR(128)
DECLARE @.PhoneID INT
DECLARE @.PhoneNumber VARCHAR(32)
--DECLARE @.CompanyID INT
SELECT
--@.CompanyID = CompanyID,
@.CompanyName = CompanyName,
@.Street = Street,
@.City = City,
@.StateName = StateName,
@.ZipCode = ZipCode,
@.Country = Country,
@.PhoneNumber = PhoneNumber
FROM INSERTED
INSERT INTO State(StateName)
VALUES(@.StateName)
SET @.StateID = @.@.IDENTITY
INSERT INTO Address(Street, City, StateID, Country, ZipCode)
VALUES(@.Street, @.City, @.StateID, @.ZipCode, @.Country)
SET @.AddressID = SCOPE_IDENTITY()
INSERT INTO Phone(PhoneNumber)
VALUES(@.PhoneNumber)
SET @.PhoneID = SCOPE_IDENTITY()
INSERT INTO Company(CompanyName, AddressID, PhoneID)
VALUES(@.CompanyName, @.AddressID, @.PhoneID)
END
The stored procedures are here.
CREATE PROCEDURE GetCompany
(
@.CompanyID INT
)
AS
BEGIN
SELECT CompanyName,Street, City, StateName,
ZipCode, Country, PhoneNumber
FROM
ViewOfCompany
WHERE
CompanyID = @.CompanyID
END
GO
CREATE PROCEDURE AddCompany
(
@.CompanyName VARCHAR(128),
@.Street VARCHAR(256),
@.City VARCHAR(128),
@.StateName VARCHAR(128),
@.ZipCode VARCHAR(128),
@.Country VARCHAR(128),
@.PhoneNumber VARCHAR(32)
)
AS
BEGIN
INSERT INTO ViewOfCompany
VALUES(@.CompanyName, @.Street, @.City, @.StateName,
@.ZipCode, @.Country, @.PhoneNumber)
END
GO
Hi,
first of all you have a misdesign in your trigger. Triggers occur per statement not per row, so inserting 50 rows in a table will fire the trigger only once not 50 times. In your case the trigger is only executed once which means that it would ingnore the 49 other inserted rows. I would first fix that and perhaps come back with the error line where the error you are describing occurs, marking it with something like -- <<-- Error occurs here.
HTH; Jens Suessmeyer.
http:/www.sqlserver2005.de
|||I am only needing to add one company at a time. In testing the AddCompany stored procedure with Query Analyzer, it appears to work with the correct parameters. For example,
-- Insert First Company
EXEC AddCompany '', '', '', ...
-- Insert Second Company
EXEC AddCompany '', '', '', ...
With actual values, the records are inserted in the tables.
But, my delimma is in the AddCompany stored procedure I don't want to have to pass in a value for the identity column and I don't want to remove it from the view because I need it for the GetCompany stored procedure. And, I don't want to have to create a seperate view for each scenerio; so, my question is there anyway to achieve the desired goal.
I have been looking at the "check" and "no check" options, but I am not sure how they function.
hi,
i have a problem:
i got a report to which i pass a xml string like
<xml>
<somedata>
<somemoredata>
value
</somemoredata>
</somedata>
</xml>
in the visual studio it works just the way its supposed to be, but if i upload it on a report server and execute it there nothing happens. even no exception is thrown. the screen remains blank.
anyone got a solution or atleast a hint?
tia
found the problem:
the string i pass is only allowed to be about 1800 bytes large
I'm new to ssis and unfortunatly I'm having problems already. I'm trying to set a value to a variable in a Script Component using the following code:
Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try
Dim vars As IDTSVariables90
Me.VariableDispenser.LockForWrite(varName, vars)
Me.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub
It runs without any trouble and no errors. I've made several tests on the input variable and it's just fine. At the end the destination variable does not change. Everyone who is using that code does not complain.
The destination variable is a date ant it is writable.
Any ideia of what is happening or any alternative sugestion?
Thank you in advance
Variables are re-set to their original values after package execution. You can monitor their values during run-time by adding a breakpoint and putting a watch on the variable.
|||Try using these function !! It may solve u r problem...
Private Function writeVariable(ByVal varName As String, ByVal value As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockOneForWrite(varName, vars)
Try
vars(varName).Value = value
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
Private Function readVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
Dev
|||Thank you both very much. I will try to do as you suggest.
Anthony Martin wrote:
Variables are re-set to their original values after package execution.
Correct in what you see, but a bit of while lie, as there is no explicit reset, there is just no Save of the package state. The instance of the package that is executing is just thrown away at the end, so you never see what happened unless you ask, breakpoints etc
It is perhaps most confusing when in the designer and executing a package, that you cannot just examine a variable value when you feel like it, and the values are not there to view at the end of execution. The simple reason for this is that the instance in the designer is not actually the instance being executed, as the designer itself cannot execute a package. You'll notice that as part of execution a package is saved. This is because it needs to be saved, so it can be loaded by the debug host, an entirely separate process that needs to load the package from scratch. This debug host (dtsdebughost.exe) is what really runs the package for us, and it then sends little messages to the designer so we can see what is going on, and get the pretty colours and numbers. Similarly if we look at a variable value in the designer, even whilst a package is executing, we are looking at an offline copy. To see the real live copy in the debug host we need to use the Watch window and a breakpoint, so that the designer can ask explicitly for the current value.
Does that make sense?
|||It makes a lot of sense Anthony. Maybe my code worked but I just could not see the change. I looked at the package explorer and it kept it's original value.I feel a little embarassed for making you guys waste your time with something so trivial.
Again, thank you.
|||(Darren?) A common mistake, I know I made it. It is confusing, and certainly not immediately obvious as what you see all appears OK on the surface, just the values being wrong! Not a waste of time at all to ask.
I'm new to ssis and unfortunatly I'm having problems already. I'm trying to set a value to a variable in a Script Component using the following code:
Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try
Dim vars As IDTSVariables90
Me.VariableDispenser.LockForWrite(varName, vars)
Me.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub
It runs without any trouble and no errors. I've made several tests on the input variable and it's just fine. At the end the destination variable does not change. Everyone who is using that code does not complain.
The destination variable is a date ant it is writable.
Any ideia of what is happening or any alternative sugestion?
Thank you in advance
Variables are re-set to their original values after package execution. You can monitor their values during run-time by adding a breakpoint and putting a watch on the variable.
|||Try using these function !! It may solve u r problem...
Private Function writeVariable(ByVal varName As String, ByVal value As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockOneForWrite(varName, vars)
Try
vars(varName).Value = value
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
Private Function readVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
Dev
|||Thank you both very much. I will try to do as you suggest.
Variables are re-set to their original values after package execution.
Correct in what you see, but a bit of while lie, as there is no explicit reset, there is just no Save of the package state. The instance of the package that is executing is just thrown away at the end, so you never see what happened unless you ask, breakpoints etc
It is perhaps most confusing when in the designer and executing a package, that you cannot just examine a variable value when you feel like it, and the values are not there to view at the end of execution. The simple reason for this is that the instance in the designer is not actually the instance being executed, as the designer itself cannot execute a package. You'll notice that as part of execution a package is saved. This is because it needs to be saved, so it can be loaded by the debug host, an entirely separate process that needs to load the package from scratch. This debug host (dtsdebughost.exe) is what really runs the package for us, and it then sends little messages to the designer so we can see what is going on, and get the pretty colours and numbers. Similarly if we look at a variable value in the designer, even whilst a package is executing, we are looking at an offline copy. To see the real live copy in the debug host we need to use the Watch window and a breakpoint, so that the designer can ask explicitly for the current value.
Does that make sense?
|||It makes a lot of sense Anthony. Maybe my code worked but I just could not see the change. I looked at the package explorer and it kept it's original value.I feel a little embarassed for making you guys waste your time with something so trivial.
Again, thank you.
|||(Darren?) A common mistake, I know I made it. It is confusing, and certainly not immediately obvious as what you see all appears OK on the surface, just the values being wrong! Not a waste of time at all to ask.
