GUID Param.dbtype In Designer Generated Code
Apr 2, 2008
When using a typed dataset in VS 2005 with SqlCe 3.1, it conitinues to make the param.dbtype for the GUID to be an 'object'. When as far as I can tell it needs to be a 'Guid' dbtype. So each time I modify the typed dataset I go in and manually change the various bugs and things work fine. My question is whether or not anyone knows a better work around for this after finding themselves in this situation.
I've attached a code sample of the erroneous designer code, and highlighted the line that should instead be set to DbType.Guid.
Code Snippet
global::System.Data.SqlServerCe.SqlCeParameter param = new global::System.Data.SqlServerCe.SqlCeParameter();
param.ParameterName = "@ProjectQuoteGUID";
param.DbType = global::System.Data.DbType.Object;
param.Size = 16;
param.IsNullable = true;
param.SourceColumn = "ProjectQuoteGUID";
this._commandCollection[1].Parameters.Add(param);
Thanks
View 2 Replies
ADVERTISEMENT
Oct 22, 2006
I downloaded the AdventureWorks OLTP Schema and am trying to use some of the design concepts that are used in this example but I don€™t quite understand some things.
First there is a column named rowguid, at the moment I assume that this is a GUID. If this is wrong most likely my next question will make no sense.
Second if that is indeed a guid all the tables have another id; example would be Employee table would have employeeid and rowguid, why do this? My best guess is that you would use the guid to easily insert (linked/sync) data into multiple tables without needing to retrieve the id from the table that was first input into. My scenario would be inserting an employee; to insert an employee you need to create a row in the employee, contact and address tables and for ease you would use the guid to link these rows. But if this is the case wouldn€™t there be sync issues that would arise?
If my guess is totally off please correct me also if anyone can direct me to some good resources that cover database design.
The schema can be downloaded at http://www.microsoft.com/downloads/details.aspx?familyid=0F6E0BCF-A1B5-4760-8D79-67970F93D5FF&displaylang=en#filelist
Thanks for any help.
View 1 Replies
View Related
Oct 23, 2005
Hi, just learning SQL2000.I have this code: mSQL = "UPDATE Contactpersonen SET Naam=? WHERE ContactpersoonID=?" Command = New SqlClient.SqlCommand(mSQL, C_CP) Command.Parameters.Add("Naam", txtNaam.Text) Command.Parameters.Add("CPID", SqlDbType.UniqueIdentifier).Value = m_CP_IDwhere m_CP_ID is defined as a GUID in: Public Property m_CP_ID() As Guid Get If Not viewstate("m_CP_ID") Is Nothing Then Return viewstate("m_CP_ID") End If 'Return 0 End Get Set(ByVal Value As Guid) viewstate("m_CP_ID") = Value End Set End PropertyWhen running the app I got this error:Server Error in '/4D' Application.--------------------------------------------------------------------------------
Line 1: Incorrect syntax near 'Naam'. Line 1: Incorrect syntax near '?'. (points to mSQL above)I know it has something to do with the GUID, but I cannot guess what.Help is appreciated, Ger.
View 2 Replies
View Related
Oct 24, 2005
Hi, I have the following code: Dim RowLoopIndex As Integer DsFuncties.Clear() A_Functies.Fill(DsFuncties) For RowLoopIndex = 0 To (DsFuncties.Tables("Functies").Rows.Count - 1) If DsFuncties.Tables("Functies").Rows(RowLoopIndex).Item("FunctieID") = Func_ID Then Func_naam = (DsFuncties.Tables("Functies").Rows(RowLoopIndex).Item("Functienaam")) DDL_Functie.SelectedIndex = RowLoopIndex + 1 Exit For End If NextFunc_ID has been declared as follows: Public Property Func_ID() As Guid Get If Not viewstate("Func_ID") Is Nothing Then Return viewstate("Func_ID") End If End Get Set(ByVal Value As Guid) viewstate("Func_ID") = Value End Set End PropertyOn the red line I got the following tooltip (error):Operator '=' is not defined for types 'System.Object' and 'System.Guid'.How can I define '=' and eg. '&' for type System.Guid ???? Or another solution ??Help is appreciated, Ger.
View 3 Replies
View Related
Feb 23, 2006
When running a step within my DTS package I'm receiving the following error - "Provider generated code execution exception: "EXCEPTION_ACCESS_VIOLATION".
I think it may be something to do with my global variable, but I'm not sure as I'm pretty certain I've set it all up correctly.
Below are screenprints showing my settings.
http://img153.imageshack.us/my.php?image=19tv1.jpg
http://img153.imageshack.us/my.php?image=25wz1.jpg
http://img153.imageshack.us/my.php?image=39pf.jpg
http://img164.imageshack.us/my.php?image=43nx.jpg
http://img164.imageshack.us/my.php?image=51ao.jpg
http://img164.imageshack.us/my.php?image=64lo.jpg
http://img164.imageshack.us/my.php?image=71yn.jpg
Any advice of fixing this would be greatly appreciated.
View 6 Replies
View Related
May 2, 2008
I currently have the following code in my designer file
<asp:SqlDataSource ID="SqlDataSource1" Runat="server" SelectCommand="select Site_name, system_id, ASP_Archive, sitetimes,HPOV_ROC, UPPER(CircuitType) as CircuitType, QwestCircuit_ID, SiteConfig,Site_Nat, PVC_VCI from tblASPCustomerWan order by Asp_Archive asc"
UpdateCommand="UPDATE tblASPCustomerWan SET [Site_name] = @Site_name, [system_id] = @system_id, [ASP_Archive] = @ASP_Archive, [sitetimes] = @sitetimes, [HPOV_ROC] = @HPOV_ROC, [CircuitType] = @CircuitType, [QwestCircuit_ID] = @QwestCircuit_ID, [SiteConfig]= @SiteConfig, [Site_Nat]=@Site_Nat,[PVC_VCI]=@PVC_VCI"
ConnectionString="server=localhost;Trusted_Connection=yes;uid=portal_user;pwd=Cr@zyP@55w0rd;database=CusPortal_Staging" />
I would like the change the connection so it takes it value from System.Configuration.ConfigurationManager.AppSettings("appStrConnection"), how can i do that??
View 1 Replies
View Related
Mar 28, 2007
I have 2 data sources that have recently been updated from SQL Express to full versions of 2005. The connection strings have been changed, and the changes appear in the code, but the data source designer still shows the SQLExpress portion of the connection string. This seems to be fouling up SSIS packages that are using these data sources. Has anyone else encountered this? If so, what can I do to fix this issue?
View 4 Replies
View Related
Jun 14, 2006
While Creating a script task in Control Flow, I am getting "Package Validation Error". Here is the complete message:
Error at Validate File and Load Data: The task is configured to pre-compile the script, but binary code is not found. Please visit the IDE in Script Task Editor by clicking Design Script button to cause binary code to be generated.
(Microsoft.DataTransformationServices.VsIntegration)
As mentioned in the message, I opened the script IDE and added the code I need. When I close the VSA IDE, package designer displays the same error message.
The worst part of whole story is that if I close the package designer and reopen it, I find that all the code I wrote in the script task has been deleted by the package designer. This is not at all acceptable as I saved the package the and still lost all my work. I did all the coding from scratch for that task.
Please respond if anyone faced similar problem.
Thanks in advance!
Anand
PS: If any one from Microsoft is reading this, please see what you guys are coding there. Due to the buggy software you deliver, I am loosing my credibility.<P< P>
View 5 Replies
View Related
May 23, 2008
Hello
I tried the Beta 1 of the service pack 1 to .net 3.5. If I try to add an entity (and try to save this), I get the Exception "No support for server-generated keys and server-generated values".
How can I add entities to my Sqlce- database?
I tried to give the id- column (primary key) in the database an identity, another time without identity, only primary key --> none of them worked. I always get the same error.
What do I have to change to make successfully a SaveChanges()?
Thanks for your help,
Gerald
View 21 Replies
View Related
Feb 19, 2007
If any one can help
OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.
Thanks
www.databasetimes.net
View 15 Replies
View Related
Mar 4, 2008
How do I update the OrderGUID column in Table B with Values from OrderGUID column in Table A. I have already populated the OrderGUID column in Table A using NEWSEQUENTIALID(). Now I need to populate the OrderGUID column in Table B with Matching GUID values from the OrderGUID Column in Table A.
Does any one have a script to accomplish this task. thanks
View 4 Replies
View Related
Jan 28, 2008
I would like to test following DMX, but it seems like we cannot use @param in DMX. If i indeed need what other tricks can avoid this constraint?
Declare @HCVS_MemberId nvarchar(15);
INSERT INTO test
(HCVS_MemberId, HCVS_MeasureDate, SysPressure, DiaPressure, Pluse)
OPENQUERY(Healthcare,
'SELECT TimeIndex, Quantity
FROM v_VitalSignForecast
WHERE HCVS_MemberId=@HCVS_MemberId AND HCVS_MeasureDate>=@From AND HCVS_MeasureDate<=@To')
Thanks,
Ricky.
View 3 Replies
View Related
Mar 12, 2008
is there a way in t-sql to pass a db name as a parameter, so that select * from [@passedDB].[dbo].[tableName] would work? Without dynamically building and executing the sql statement?
View 1 Replies
View Related
Sep 6, 2007
The following stored procedure sets a value for the @@RowCount global variable.
How do I make use of it in the Data Access Layer?
When I set the SPROC as the source for the object, the value numberRows does not appear to be an option. In the end I just want to set the value of @@RowCount to a Label.Text
What should I do?ALTER PROCEDURE dbo.ap_Select_ModelRequests_RequestDateTime
@selectDate datetime
,@selectCountry Int
AS
SELECT DISTINCT configname FROM ModelRequests JOIN
CC_host.dbo.usr_cmc As t2 ON
t2.user_id = ModelRequests.username JOIN
Countries ON
Countries.Country_Short = t2.country
WHERE RequestDateTime >= @selectDate and RequestDateTime < dateadd(dd,1, @selectDate)
AND configname <> '' AND interfacename LIKE '%DOWNLOAD%' AND result = 0 AND Country_ID = @selectCountry
ORDER BY configname
SELECT @@RowCount As numberRows
GO
View 2 Replies
View Related
Dec 27, 2007
I'm working with a dataset something like this:
TypeID Sales($)
------ -------
1 123.45
1 47.98
2 9.21
3 87.23
3 99.88
4 123.43
And a multivalued parameter that lets the user select which TypeIDs specifically he wants to see:
ParamID ParamValue
1 Q1
2 Q2
3 Q3
4 Q4
And in my Report, I have data showing up something like this:
CountofAllSales: 6
SumOfAllSales: 491.18
CountofCustomSales: (count of sales with type specified in parameter)
SumOfCustomSales: (sum of sales with type specified in parameter)
The count and sum of custom sales should show -ONLY- the numbers from the TypeIDs selected in the multi-value parameter. But the CountAll and SumAll show everything, regardless. This is where I run into problems. I can't seem to find an "in" clause in the SSRS expressions. If the TypeID parameter was single value, I could write something like this
Expression for CountOfCustomSales:
=SUM(iif(Fields!TypeID.Value = Parameters!TypeID.Value, 1, 0))
However, since its multi-valued, that won't work. You'd have to write something like:
=SUM(iif(Fields!TypeID.Value = Parameters!TypeID.Value(0), 1, 0)) +
SUM(iif(Fields!TypeID.Value = Parameters!TypeID.Value(1), 1, 0)) +
....
SUM(iif(Fields!TypeID.Value = Parameters!TypeID.Value(length), 1, 0))
And obviously this doesn't work when you don't know exactly how many elements are going to be selected.
What would be ideal would be something like an "in" clause, but I can't find any such functionality or think how to write my own function:
=SUM(iif(Fields!TypeID.Value in
Parameters!TypeID.Values, 1, 0))
Short of modifying the StoredProc itself (and for me, that means red tape. :( :( ) can anyone think of a way to count/sum only the values specified in an MVP??
View 1 Replies
View Related
Nov 5, 2006
Hi AllMy query is as follows:SELECT STRINGTEXT, TOKENIDFROM WEBSTRINGSWHERE TOKENID IN (6,20,234,19,32,4,800,177)All I want is my resultset to come back in the order that I have defined inthe IN clause, but unfortunately SQL is trying to be too helpful and sortsthe numbers in the IN clause so that the resultset comes back with a TOKENIDorder of 4,6,19,20,32,177,234,800.I don't want this bloody order I want 6,20,234,19,32,4,800,177!!Sorry for my rant, but its got my hot under the collar.Is there anyway round this?ThanksYobbo
View 3 Replies
View Related
Feb 20, 2007
Is there a limit to how many items you can have in a multi value param list. I have 20 items in an activity type param and when I chose "Select All" and run the report, it doesn't return. I opened profiler and picked up the following statement sent to SQL:
exec sp_executesql N'SELECT de.employeenumber as Employee_Id,de.employeelastname + '', '' + de.employeefirstname + '', '' + case when de.employeemiddlename=''N/A'' then '''' else de.employeemiddlename end as Employee_Name,da.activitytype as Activity_Type,da.activitycode as Activity_Code,da.activityname as Activity_Name,dd.fulldate as Completion_Date,das.currentattemptstatus as Current_Attempt,das.successstatus as Success_Status,das.completionstatus as Completion_Status,das.registrationstatus as Registration_Status,fa.score as Score,fa.dimgradeid as GradeId
FROM dimemployee de inner join factattempt fa on (de.dimemployeeid = fa.dimemployeeid) inner join dimattemptstatus das on (fa.dimattemptstatusid = das.dimattemptstatusid) inner join dimactivity da on (fa.dimactivityid = da.dimactivityid) inner join dimdate dd on (fa.attemptenddateid = dd.dimdateid)
WHERE
de.employeenumber = (@EmployeeId) and da.activitytype in (N''CBT'',N''Course'',N''Dart'',N''Discuss'',N''Document'',N''Evaluator'',N''JPM'',N''Lesson Plan'',N''Module'',N''Observation'',N''Procedure'',N''PSG'',N''Qual'',N''Read'',N''Reference'',N''Session'',N''Sign-off'',N''Simulator'',N''Task'',N''Trainer'')
and das.isvalidattempt = ''Yes'' and ((@LastAttempt=1 and das.currentattemptstatus = ''Yes'') or (@LastAttempt=0 and das.currentattemptstatus in (''Yes'',''No''))) and das.lmsmartcompletionstatus in (@CompletionStatus) and (dd.fulldate >= @StartDt or @StartDt is NULL) and (dd.fulldate <= @EndDt or @EndDt is NULL)
ORDER BY de.employeefirstname + '' '' + de.employeelastname,da.activitytype,da.activityname,dd.fulldate',N'@EmployeeId int,@LastAttempt nvarchar(1),@CompletionStatus nvarchar(10),@StartDt nvarchar(4000),@EndDt nvarchar(4000)',@EmployeeId=108001,@LastAttempt=N'1',@CompletionStatus=N'Successful',@StartDt=NULL,@EndDt=NULL
If I take any one of the items in "da.activitytype in" out (for example....N''Trainer'')...the query is fine. But if I run it as is, it never returns. I have also reduced the number of items in the MVP to 19 and "Select All" and it runs fine...it just bombs when I have 20 and Select All. Any ideas?
View 1 Replies
View Related
Apr 21, 2008
Hello....
I am trying for several weeks to figure out how can I create a stored procedure with parameters that returns all rows from a column (even then Nulls) when the parameter value is not set (or is '%', or anything tha might be. In a few words, in case the user hasn't input any data).
I 've created a WHERE clause that goes like this: WHERE fieldName LIKE @param + N'%' OR IS NULL
Well this query returns all rows in case the user hasn't input data but if the user inputs data it returns the correct rows but it is also returns null fields.
Thanks a lot in advance!
Manolo....
View 6 Replies
View Related
May 16, 2008
Is it possible to take a report input parameter and display it at the top of the report? StartDate/EndDate are two input params for my report that would be nice to display at the top.
thanks
(Reporting Services 2005)
View 6 Replies
View Related
Aug 8, 2006
I've got a stored procedure and one of the parameters is a DateTime. But no matter what I do to the string that's passed into the form for that field, it doesn't like the format. Here's my code: SqlConnection conn = new SqlConnection(KPFData.getConnectionString());
SqlCommand cmd = new SqlCommand("KPFSearchName", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = cmd.Parameters.Add("@DOB", SqlDbType.SmallDateTime);
param.Direction = dir;
param.Value = txtDOB.Text;
// also have tried this:
param.Value = Convert.ToDateTime(txtDOB.Text);
// and
param.Value = Convert.ToDateTime(txtDOB.Text).ToShortDateString;
No matter what I do I always get a formatting error - either I can't convert the string to a DateTime, or the SqlParameter is in the incorrect format, or something along those lines. I've spent a couple hours on this and hoping someone can point out my obvious mistake here...??Thanks for your help!!eddie
View 5 Replies
View Related
Jan 21, 2005
Hello,
I have a table with a foreign key field. I need to retrieve all the records where the foreign key matches any of a set. In plain ol' SQL this is accomplished with the IN(a,b,c) statement but I can't get that to work in a stored procedure.
How would I do this? I can imagine that I could parse the input string and create a temporary table and use that to do a join but that seems rather convoluted.
Any tips highly appreciated! If I'm not being clear I'll gladly post more details.
Thanks,
Noc
PS SQL 2000, ASP.NET 1.1, VS 2003.
View 2 Replies
View Related
Apr 7, 2006
I have a uniqueidentifier probleme when i want to execute my sql string. The "PortalID" is my uniqueidentifier passed on my StoredProc parameter. But i cant make it work. It said that i cant add a nvarchar with a uniqueidentifier.!!
what is the best way to do it!! i try to convert(nvarchar(40), @PortalID) but its not workingdeclare @sql nvarchar(4000)
set @sql = 'select
t.[PortalID],
t.[City],
t.[Name],
t.[Code],
ts.*
from [Hockey_Team_Statistics] ts inner join [Hockey_Teams] t on (t.[TeamID] = ts.[TeamID])
where ts.[SeasonYear] = '+CONVERT(nvarchar(10),@SeasonYear)+' and ts.[LeagueMode] = '+CONVERT(nvarchar(1),@LeagueMode)
+'t.[PortalID] = '+@PortalID+'
order by '+ @SortExpression +''
exec sp_executesql @sql
View 3 Replies
View Related
Aug 21, 2001
Hello everyone,
I am working through a tutorial and have stumbled into something that does not quite make sense to me. I was wondering if someone could help me understand this.
I have created this SP, this all makes sense to me due to the assignment of the artistname column value to the @artistname variable. In other words what is on the right of the equal sign is assigned to what is on the left.
create procedure ShowPopStyle
@style varchar(30),
@artistname varchar(30) output
as
select @artistname = artistname
from artists
where style = @style
go
Now when you execute this SP, what does not makes sense to me is if I need to declare a variable to hold the output, which I presume is null, shouldn't the @returnname be on the left side of the equal sign instead of the right?
declare @returnname varchar(30) -- variable for the output from the procedure
exec showpopstyle 'Pop', @artistname = @returnname output
print @returnname
Thanks
Kevin
View 2 Replies
View Related
May 9, 2008
Hi all,
I also have the same error, I am trying to do two things in my Stored Proc.
1) - Insert a parent record.
2) - Insert 1 or many records in the child table using the parent ID
The child records are being passed as a XML param.
I am also getting the
'Subqueries are not allowed in this context. Only scalar expressions are allowed.'
error when I try to create the procedure.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:pryder
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE TestProc
-- Add the parameters for the stored procedure here
@Name String ,
@bings XML
AS
BEGIN transaction
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @NewID as int
declare @err as int
-- Insert statements for procedure here
INSERT INTO PARENT
(Name)
VALUES (@Name)
SELECT @err = @@error
if @err <> 0
begin
rollback transaction
return @err
end
SELECT SCOPE_IDENTITY = @NewID
INSERT INTO Child
(ParentID,
name)
Values
((
SELECT @NewID, ParamValues.ID.value('.','VARCHAR(MAX)')
FROM @bings.nodes('bings/group') as ParamValues(ID)
))
SELECT @err = @@error
if @err <> 0 begin rollback transaction return @err end
commit transaction
return @@error
GO
View 7 Replies
View Related
Mar 19, 2008
I've decalred this stored procedure with a simple where and like statement. The problem is I don't see any result.
Here is my code
create procedure sp_Select_ProfileNames
@NameSearch varchar(50)
as
select ProfileFirstName +' '+ ProfileLastName as ProfileName
from Profiles
where ProfileLastName like '%@NameSearch%';
When i change this line:
where ProfileLastName like '%@NameSearch%';
to
where ProfileLastName like '%Bil%';
I see names starting with Bil, but when i enter Bil as param, I don't get anything.
What's wrong here ?
View 2 Replies
View Related
Dec 1, 2005
I have a rs report with a parameter(named Site) that is defined as multi-value. What I am trying to do is call the report from an html form with the parameter populated with a comma seperated list of values. The html code is a minimally modified version of the code found in ch. 11 of the hitchhikers guide(code included below after the solid line). If I don't select any items, then execution is fine.
View 6 Replies
View Related
Jun 18, 2007
Hello,
I have a VB.NET stored procedure as below:
Code Snippet
Partial Public Class StoredProcedures
Public Shared Sub My_UpdateCountsManaged( ByRef paramInOut As Integer)
'here I perform update statement using "paramInOut" passed form calling code
.......
'then I return value to the calling code
paramInOut = 555
End Sub
End Class
Calling code specifies a parameter like this:
Code Snippet
Dim param as Sqlparameter = New SqlParameter("@paramInOut", SqlDbType.Int)
param.Direction = ParameterDirection.InputOutput
param.Value = 999
cmd.Parameters.Add(param)
When I execute the code, it surely gets back "555" from SP, the problem is that SP never gets "999" from calling code despite ParamDirection is InputOutput. It always receives 0. I am afraid I don't understand something fundamental ?
Any help would be appreciated.
Thanks a lot,Fly.
View 4 Replies
View Related
Oct 27, 2006
Dear friends,
How can insert into a temp table 2 parameters. If it's only on, I dont have problem, but if I have 2 there is a error... The problem is the sintax of the execute SP ... thanks
ALTER PROCEDURE [dbo].[GD_SP_FACTURAS_TOTAL2]
AS
CREATE TABLE #TotaisDir
(
DirTotal bigint,
DirNome nvarchar(10)
)
DECLARE @DIR nvarchar(10)
DECLARE @Return_Status bigint
DECLARE LINHAS_CURSOR CURSOR FOR SELECT DIR_NOME FROM Direccao
OPEN LINHAS_CURSOR
FETCH NEXT FROM LINHAS_CURSOR INTO @DIR
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO #TotaisDir (DirNome,DirTotal) VALUES
('ww', EXECUTE dbo.GD_SP_FACTURA_ValorTotal @DIR)
FETCH NEXT FROM LINHAS_CURSOR INTO @DIR
END
CLOSE LINHAS_CURSOR
DEALLOCATE LINHAS_CURSOR
SELECT DirTotal FROM #TotaisDir
SELECT SUM(DirTotal) As SOMATOTAL FROM #TotaisDir
ERROR:
Msg 156, Level 15, State 1, Procedure GD_SP_FACTURAS_TOTAL2, Line 21
Incorrect syntax near the keyword 'EXECUTE'.
Msg 102, Level 15, State 1, Procedure GD_SP_FACTURAS_TOTAL2, Line 21
Incorrect syntax near ')'.
View 3 Replies
View Related
Mar 12, 2008
Reading about SQL Injection attacks I came across this example:
SSN="172-32-9999';DROP DATABASE pubs --"
SqlQuery = "SELECT au_lname, au_fname FROM authors WHERE au_id = '" + SSN + "'"
One remedy given was a parameterized query as follows:
Dim cmd As new SqlCommand("SELECT au_lname, au_fname FROM authors WHERE au_id = @au_id")
Dim param = new SqlParameter("au_id", SqlDbType.VarChar)
param.Value = SSN
cmd.Parameters.Add(param)
Why does this parameter which is defined as a varchar solve the problem? It's defined as a varchar, basically a string. Why is the result different in the solution? How is the query string in the second sample different from the one in the first? A simple question I know, but I've been wondering.
Thanks
Mike Thomas
View 7 Replies
View Related
May 1, 2008
Hi,
My package takes 1 global variable and I have set up jobs to run this SSIS with different variable values.
I select the package using the UI provided via the job set up screen. The job step type is Operating system (CmdExec).
When package are run by job, it seems that the job can not find the package. But, how can it not find the package when the package was selected from a list that the UI displayed? The error is below.
"Executed as user: <<LOGGEDIN USER(with admin right)>>. The process could not be created for step 1 of job 0xFECED6C09CC650489084E91C2FCF52FB (reason: The system cannot find the file specified). The step failed."
Thanks a lot in advance.
View 1 Replies
View Related
Nov 19, 2007
I want to pass a single char to a query and use that to set two flags.
(
@ID int,
@AssessedID int,
@CompetencyID int,
@Status char,
@Creator int
)
AS
UPDATE P4_Assessment
SET P4_Cancelled_f = (@Status = ('C')),
P4_Competent_f = (@Status = ('P')),
P4_Date = getdate(),
P4_Creator = @Creator
WHERE P4_ID = @ID
I want to set the P4_Cancelled_f to true (if @Status = 'C') or false if it doesn't.
This sort of syntax is fine in C#, but fails in a query. I also tried using IN (@Status IN ('C'))
Is this sort of logic possible in TSQL or should I use two parameters and set them in my code as 1 or 0?
TIA
View 3 Replies
View Related
Sep 21, 2007
Hi,
I'm exporting some data from a table to fixed-width file using SSIS.
The query inside the package is:
SELECT col1, col3, col6 FROM Table1
WHERE code = 5 AND status = 'S'
The 'code' value will be passed from the front-end.
How can I make the query dynamically take the 'code' value and export to a file?
If a user selects 'code' as 7 , then the query should change to
SELECT col1, col3, col6 FROM Table1
WHERE code = 7 AND status = 'S'
and with this it has to export to a file.
Is this possible? If so, any suggestions on how to do it? !!!
Thanks,
Siva.
View 9 Replies
View Related
May 9, 2006
Please help me on this one.
I need to return a value to VB.
I've tried returning a numeric value NUMERIC(25,20) via an output parameter but this didn't work. I'm know at a point in wich I created a bigint and multiplied the value so that the decimals are gone. However it only returns NULL?!?!?!?!!?!?
Here's part of my stored proc
CREATE PROCEDURE dbo.uspCalcWeightedAverage @StartDate2 varchar(10), @EndDate2 varchar(10), @InMarket nvarchar(50), @InProductType int, @InWeekDay int, @WeightedAverage bigint OUTPUTAS......SELECT @WeightedAverage = cast(10000000000 * (SUM(HHF.FACTOR) / COUNT(PDF.FLAG)) as bigint)FROM TBL_PRODUCTDEFS PDF INNER JOIN #DATESBETWEENINTERVAL DBI ON DATEPART(HH, [DBI].[DATE]) = [PDF].[HOUR] INNER JOIN tbl_historichourlyfactors HHF ON DATEPART(D,DBI.DATE) = HHF.DayID AND [PDF].[HOUR] = [HHF].[HOUR] AND DATEPART(M,DBI.DATE) = [HHF].[Month]WHERE PDF.MARKETID = @InMarketID AND PDF.PRODUCTTYPEID = @InProductTypeID AND [PDF].[WD-WE] = @InWeekDay AND HHF.MARKETID = @InMarketID AND PDF.FLAG = 1GROUP BY FLAG
When I retrieve the output param it returns a NULL value. the properties in VB say that the parameter has the following props:
attribute 64 (Long)
NumericScale 0 (Byte)
Precision 19 (Byte)
Size 0 (ADO_LNGPTR)
Type adBigInt
Value Null
I try to return it with the following code (got the code from a friend)
Public Function RunProcedure(ByVal v_strStoredProcName As String, ByRef r_varParamValues() As Variant) As ADODB.RecordsetDim objAdoRecordset As ADODB.RecordsetDim objAdoCommand As ADODB.CommandDim lngCtr As Long On Error GoTo RunCommand_Error ' Create cmd object Set objAdoCommand = New ADODB.Command Set objAdoCommand.ActiveConnection = m_oAdoConnection objAdoCommand.ActiveConnection = m_oAdoConnection objAdoCommand.CommandText = v_strStoredProcName objAdoCommand.CommandType = adCmdStoredProc Call objAdoCommand.Parameters.Refresh 'Stop For lngCtr = 0 To UBound(r_varParamValues) If objAdoCommand.Parameters(lngCtr + 1).Direction = adParamInput Then objAdoCommand.Parameters(lngCtr + 1).Value = r_varParamValues(lngCtr) End If Next Set objAdoRecordset = New ADODB.Recordset objAdoRecordset.CursorLocation = adUseClient Set objAdoRecordset = objAdoCommand.Execute 'Stop For lngCtr = 0 To objAdoCommand.Parameters.Count - 1 If objAdoCommand.Parameters(lngCtr).Direction = adParamOutput Or objAdoCommand.Parameters(lngCtr).Direction = adParamInputOutput Then r_varParamValues(lngCtr - 1) = objAdoCommand.Parameters(lngCtr).Value End If Next Set RunProcedure = objAdoRecordsetRunCommand_Exit: ' Collect your garbage here Exit FunctionRunCommand_Error: ' Collect your garbage here Call g_oGenErr.Throw("WeatherFcst.CDbsConn", "RunCommand")End Function
PLEASE HELP.
Regards,
Sander
View 1 Replies
View Related
