Need Help Calculating Values In View Code
Jan 26, 2008
I'm in the process of building various queries and I'm having issue calculation values in the following code. I need to calculate the maximum and minimum salary for exempt employees and include a column that calculates the difference in these numbers. I have no issue creating views and outputing data. When I try to calculate the values I receive errors:
My code is:
GO
IF OBJECT_ID ('Salaries') IS NOT NULL
DROP VIEW Salaries ;
GO
CREATE VIEW Salaries
AS
select max(Salary) from EMPLOYEE join JOB_TITLE on EMPLOYEE.JobID = JOB_TITLE.JobID where ExemptStatus='Exempt'
Group By Salary, ExemptStatus;
GO
SELECT * FROM [Salaries]
I'm receiving the following
Msg 4511, Level 16, State 1, Procedure Salaries, Line 3
Create View or Function failed because no column name was specified for column 1.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'Salaries'.
Thanks folks..
View 4 Replies
ADVERTISEMENT
Apr 17, 2008
Hello, I have 2 tables, first is the mineral values, 2nd is ore composition... Minerals table:
Mineral
Value
Mineral1
10
Mineral2
20Ore composition table:
Ore
Mineral1
Mineral2
Ore1
100
0
Ore2
0
200
Ore3
10
10Now what I need to do is to have a view (or something else that gerenates a table) that will give me the value of each ore. So for example ore 3 would be 10 X mineral1 value plus 10 X mineral2 value. So the result should be:
Ore
Value
Ore1
1000
Ore2
4000
Ore3
300Can anyone point me to some tutorial or example of a similar calculation with sql views?
View 2 Replies
View Related
Nov 13, 2015
I am working with a data set containing several years' of monetary values. I have entries for past dates and the associated values, and I also have entries for future dates. I need to populate the values of the future date records with the values from the same date the previous year. Is there any way this can be done in Power Pivot?
View 6 Replies
View Related
Mar 7, 2005
I have 2 separate queries, one produces a total and one doesn't at the moment as the criteria are not met.
I want to add the total field from both these queries together but becuase one is null value, the sum total is also null.
In access I would have used nz([Qry_1].[Total])+nz([Qry_2].[Total]) to get over this but SQL/adp doesnt seem to understand this.
Can you help?
View 1 Replies
View Related
Mar 7, 2005
I have 2 separate queries, one produces a total and one doesn't at the moment as the criteria are not met.
I want to add the total field from both these queries together but becuase one is null value, the sum total is also null.
In access I would have used nz([Qry_1].[Total])+nz([Qry_2].[Total]) to get over this but SQL/adp doesnt seem to understand this.
Can you help?
View 2 Replies
View Related
Sep 19, 2007
Hi, this is my problem. The query listed below gets a list of employees asigned to a project, to a customer and to a business unit (the Dimension field). The last two fields are the start date and the end date.
select
P.DIMENSION, P.CUSTACCOUNT, C.NAME as CUSTOMERNAME, PE.PROJID, P.NAME as PROJNAME, PE.EMPLID, E.NAME as EMPLNAME,
PE.FECHAASIGNACION, PE.FECHADESASIGNACION
from
PROJVALEMPLPROJSETUP PE, EMPLTABLE E, PROJTABLE P, CUSTTABLE C
where
PE.PROJID = P.PROJID and PE.EMPLID = E.EMPLID and P.CUSTACCOUNT = C.ACCOUNTNUM and (PE.FECHAASIGNACION >= '01/01/2007'
and PE.FECHADESASIGNACION >= '01/01/2007')
order by
P.DIMENSION, P.CUSTACCOUNT, PE.PROJID, PE.EMPLID
I need to get a listing showing how many employees started or ended their projects every months in the following format:
Business UNIT CLIENT PROJECT STARTING TOTAL JANUARY FEBRUARY .. TOTAL VARIATION
--------------------------------------------------------------------------------------------------------
1 1 1 25 3 -2 26 1
Starting total refers to all the employees that have a starting date before 1/1/2007 and a ending date after 1/1/2007
The monthly columns would be the total of employees with a starting date (+1) or ending date (-1) in that month.
The total column is the starting total plus the monthly totals.
And the variation is the difference of the total with the starting total.
Any ideas?
Thanks a lot.
View 1 Replies
View Related
Aug 23, 2007
Hello forum.
I have three columns in my table with the following datatypes:
Date - DateTime
Height - Decimal
Change_From_last_Height - Decimal
I am using "SQL Server 2005 Express Edition". I'm fairly new to SQL and would greatly appreciate any help or advice I can get.
The "date" column increases by an extra day in every new row and I then enter the new height of the plant. What I want to know is how I can get SQL server express to automatically enter the difference in height between the current row's height and that of the previous row.
Is it possible to automate the entry in the Change_From_Last_Height column in SQL?
Put another way, I know how to find the difference between two values in the same row but different columns, but how do I calculate the difference between values in adjacent Rows (ie. Rows next to each other)?
Thank you.
View 3 Replies
View Related
Feb 24, 2005
Hi,
I'm using scalar UDFs in SQL server to return computed values. I've been trying to use the same udfs to perform the same computations but from different tables, but I'm not sure how. Can someone please help???
Here's an example of a counter that I'm using to return the number of days.
CREATE FUNCTION [dbo].[MarketPulse_fn_Counter] (@date smalldatetime, @date2 smalldatetime, @osid int)
RETURNS int AS
BEGIN
return (select count(*) from MarketPulse_0ndqc
where stockosid = @osid and createdate <= @date and createdate >=@date2 )
END
How can I use this same UDF to do the same computation but to SELECT from another table and return that value?
View 4 Replies
View Related
Feb 19, 2012
I need to calculate a median on a column in a table. The code I have is:
Code:
Select gender,
CASE
when gender = 'F' then 'Female'
when gender = 'M' then 'Male'
else 'Unknown'
end as test,
datediff(day, [admit_date], getdate()) as 'datediffcal',
from [tbl_record]
How do I calculate the median on the datediffcal column?
It doesn't matter if the resultset only shows the median result. So if the output shows:
median
15
that's fine. Minimally, I need the median value.
View 5 Replies
View Related
Jul 23, 2005
I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?
View 3 Replies
View Related
May 7, 2007
I have to Lists filtered by the same field. One List shows all records with the field IsClokedIn value = TRUE and the other list =FALSE. I have only one dataset that retrieve the ClockIn and Clock out for one user. In a perfect world, the user MUST clock in and always clock out. So, when i created my dataset i ordered by UserClockingID (whick is primary key). So, this MUST retrieve a dataset with alternate records IN / OUT (TRUE or FALSE)
Ex.
UserClokingID UserID Time IsClockedIN
2323 34 8:32:03 TRUE
2324 34 12:07:02 FALSE
2325 34 13:05:03 TRUE
2326 34 14:53:02 FALSE
2327 34 15:10:03 TRUE
2328 34 17:32:02 FALSE
All i need is to calculate (sum then divide by 60) the minutes returned by the method DateDiff between the first row in my TRUE filtered list and the first row in my FALSE filetered list... and so on for the 2nd, 3rd ..... I can get the minutes by taking the values directly from the Fields!IsClockedIn.Value and using a condition to "grab" the previous record if the current record ClockedIn.Value=FALSE. I already did that.
my problem is that when i try to perform a calculation of textboxes values(those with the ammount of minutes for every pair of records from the datalist) out of the group. It says you can only do that inside the group or dataregion.
Is there any work around?
I really need to get this solved.
View 2 Replies
View Related
May 6, 2014
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid
View 9 Replies
View Related
Dec 11, 2014
I can retrieve the sql code of a view as follows:
exec sp_helptext @viewName
The result will be in several lines which shows the lines of the sqlcode...
Is it possible to return the sql but not the line numbers?
View 4 Replies
View Related
Apr 11, 2008
Hi,
I am trying to get source code of a view (SELECT part) into an TSQL variable, but I have no idea, where to find this source code and how to proceede the assignment ? Any idea ? thanks
View 8 Replies
View Related
May 7, 2007
Hello all, I need to prevent all users (other than sysadmin/dbo) privs to access the database code (SP/Views/UDF). What would be the best way to achieve that? TIA
View 5 Replies
View Related
Nov 5, 2007
I am interested in retrieving the original SQL code of various stored procedures & views that I have created & pass them into a field within a table.
I have used one before but cannot remember whether I used a stored proc or system view to retrieve this information, can anyone be of assistance???
View 3 Replies
View Related
Sep 15, 2015
Before we release an SSIS package into our test environment and then eventually into our production environment, we  set the package protection level to "EncryptAllWithPassword"
The "View Code" feature is a nice tool to find things that might be buried in the package somewhere (e.g an complex expression to a variable).
PROBLEM : After one sets the package protection level to "EncryptAllWithPassword", one cannot see the xml source code any longer. It's like compiling and saving cs to the bin. Is their a way to view the code again??
View 2 Replies
View Related
Jul 18, 2007
In my code behind file I have this function:
1 public void getAllSystems()
2 {
3 MTConnection con = MTConnection.CreateConnection(new Credential(new Context("ClearQuest - Boise", "Version 2.0", Micron.Application.Context.Environments.Production), "DSSPROD", Credential.DataSourceTypes.MSSQL, Credential.DataSourceProviders.Odbc));
4
5 string sqltest = " select T1.dbid,T12.systemname AS 'ParentSystem',T1.systemname AS 'SupportSystem',T1.systemstate,T1.features,T3.name,T4.stakeholder," +
6 " T4.fldcolumn_1,T1.systemwebpage,T1.description from ( ( ( ( ( ( ( ( CQ_ADMIN.micronsystem T1 INNER JOIN" +
7 " CQ_ADMIN.microngroup T6 ON T1.primarysupportgroup = T6.dbid ) INNER JOIN CQ_ADMIN.securitygroups T7 ON T1.secuirtygroup" +
8 " = T7.dbid ) LEFT OUTER JOIN CQ_ADMIN.parent_child_links T12mm ON T1.dbid = T12mm.child_dbid and 16778862 =" +
9 " T12mm.child_fielddef_id ) LEFT OUTER JOIN CQ_ADMIN.micronsystem T12 ON T12mm.parent_dbid = T12.dbid )" +
10 " LEFT OUTER JOIN CQ_ADMIN.parent_child_links T3mm ON T1.dbid = T3mm.parent_dbid and 16804095 =" +
11 " T3mm.parent_fielddef_id ) LEFT OUTER JOIN CQ_ADMIN.feature_1 T3 ON T3mm.child_dbid = T3.dbid ) LEFT OUTER" +
12 " JOIN CQ_ADMIN.parent_child_links T4mm ON T3.dbid = T4mm.parent_dbid and 16804263 = T4mm.parent_fielddef_id )" +
13 " LEFT OUTER JOIN CQ_ADMIN.stakeholder T4 ON T4mm.child_dbid = T4.dbid ) where T1.dbid <> 0 and ((T6.abbreviation =" +
14 " 'Test ES Eng Software' and T1.systemstate = 'Active')) and ((T7.dbid in (select parent_dbid from" +
15 " CQ_ADMIN.parent_child_links where parent_fielddef_id = 16778767 and child_dbid in (select child_dbid from" +
16 " CQ_ADMIN.parent_child_links where parent_fielddef_id = 16777328 and parent_dbid = 33664106)and T12.systemname IS NOT" +
17 " NULL ))) order by T3.name ASC,T1.systemname ASC";
18
19 System.Data.DataSet ds = con.ExecuteDataset(CommandType.Text, sqltest);
20
21 foreach (DataRow drCQ in ds.Tables[0].Rows)
22 {
23 string parent_system = drCQ["ParentSystem"].ToString();
24 string child_system = drCQ["SupportSystem"].ToString();
25 //Response.Write(child_system + "<B><I>'S PARENT IS</I></B> " + parent_system + "<BR />");
26 }
27 }
I would like to pull the values of parent_system and child_system into my main page but I don't know how to do that. Is it even possible?Thanks,Xana.
View 13 Replies
View Related
Jun 1, 2008
need help with my problem
i have this view
this code change the value field "new_unit" evry month from 1 > 2 > 3 > 4
like this evry 4 month it return to 1 >2..........
------------------------------------------ for example
if i put
unit_date = 01/05/2008
and unit=1
than new_unit=1
Code Snippet
SELECT empID, ShiftType, unit_date, unit, ISNULL(NULLIF ((unit - DATEDIFF(mm, unit_date, '01/01/' + CONVERT(varchar, YEAR(GETDATE())))) % 4, 0), 4)
AS new_unit
FROM dbo.empList
my question is how to create a stored procedure that move forward (all the employee) the "new_unit" field
in +1 OR "unit_date" value MONTH +1
like create stored procedure name "plus" + so if i run this name stored procedure name "plus"
the stored procedure go to the viewor table and change the code view or table value
so i forward all the the "new_unit" or "unit_date" value IN one (change the cycle)+1
it doesn't matter if it change the "unit" value in the table "dbo.empList" or "unit_date" value
the important thing is that i can forward +1 or backward -1
evry time i run the stored procedure i get +1 (in the "new_unit")
and olso
create stored procedure name "minus" + so if i run this name stored procedure name "minus"
this stored procedure that move backward the the "unit" value in the table "dbo.empList" or "unit_date" value in -1
TABLE dbo.empList
empid ShiftType unit_date unit
----------------------------------------------------------------------------
11111 6 01/01/2008 1
2222 8 01/03/2008 4
3333 9 01/04/2008 3
TNX for the help
View 7 Replies
View Related
Oct 6, 2006
Im trying to update the values int the database and the query is executed correctly but database is not updated and the returned message is one, I really dont know whats causing this here is my code.1 private void UpdatetheDatabase(string connection)
2 {
3 SqlConnection connect = new SqlConnection(connection);
4 SqlCommand update_customer_details = new SqlCommand("UPDATE [app_CustomerDetails] " +
5 " SET [Name]=@Name,[City]=@City, " +
6 " WHERE [ID]=@ID", connect);
7 update_customer_details.Parameters.Add("@Name", SqlDbType.NVarChar, 50).Value = txtCustomerName.Text;
8 update_customer_details.Parameters.Add("@City", SqlDbType.NVarChar, 50).Value = txtCity.Text;
9 update_customer_details.Parameters.Add("@ID", SqlDbType.Int, 4).Value = ID;
10 connect.Open();
11 int x = update_customer_details.ExecuteNonQuery();
12 connect.Close();
13 StatusLabel.Text = x.ToString();
14 }
your help will be highly appreciated.
View 7 Replies
View Related
Jan 1, 2006
How do I access sqldatasource data values in code behind and bind them to strings or texboxes etc. as oposed to using Eval in the markup?
I can create a new database connection, but I would like to use the data values from the autogenerated sqldatasource control
Many thanks,
View 1 Replies
View Related
Jul 20, 2005
I have a table called Orders with three fields, OrderID, Action,Status.Action and Status are code values with FK lookup to dbo.Action anddbo.Status respectively.Is it possible for me to return not just the code values but the filednames?For example:SELECT OrderID,Action,Status FROM Orders:Returns 1001,B,FHowever I would like dboAction.ActionName and dbo.Status.StatusName to bereturned instead.I.e Returns 1001,BUY,Filled.Is this possible?Thank you.
View 2 Replies
View Related
May 22, 2007
I have about 14 tables that I am trying to tie together in a view tosee a user's status in our company.Joe User:Email - ActiveADP - ActivePortal - Inactiveetc.We need to track application access across the company, so we havetables for each application that contain user info(username,password(encrypted), start date, end date, changed date) so that wecan track who has what, and when they were given access as well aswhen it was taken away.Every application table has a userID attached to it.What I would like to do is to take the userID field and look for theirapplication access across the company. To do this, i'll have to lookfor the max value in each table because someone could be given access,have it taken away, and be given it again. People move all over theplace here, so we have to be able to track who has what, when, and atwhat building.I started out with trying to left outer join the tables together, butit didn't work. I tried doing something along the lines of:selectesarfAppEmail.emailID,esarfAppEmail.esarfUserID,CASE WHEN esarfAppEmail.endDate IS NULL Then 'Active' else 'Inactive'end as EmailStatus--,
Quote:
View 2 Replies
View Related
Jan 2, 2006
How can I return values from my SQL view back into my aspx page and shove each returned value into variables I define for certain returned fields?
View 2 Replies
View Related
May 22, 2008
Hi,
I have a table of results for various measured quantites and i need to turn this into a view. Only problem is i need to seperate the measured quantities and their respective values into seperate columns.
At the moment I have something like:
Quantity : Value
--------------------
Quantity 1 : 0.12
Quantity 1 : 0.56
Quantity 2 : 2.36
Quantity 2 : 5.34
Quantity 2 : 4.13
Quantity 3 : 10
Quantity 3 : 15
and I need a view that looks like:
Quantity 1 : Quantity 2 : Quantity 3
-------------------------------------
0.12 : 2.36 : 10
0.56 : 5.34 : 15
null(?) : 4.13 : null(?)
I've tried using pivots but they don't seem to help
Any ideas?
View 7 Replies
View Related
Sep 15, 2015
I am creating a view and want to select records where the value of a Customer field (Klant Test Plan) is NULL or has exact the same value as for example customer field 2 (Klant Schedule).
I have already the code below:
SELECT DISTINCT
                     TOP (100) PERCENT dbo.Product.ERPKey, dbo.TestPlan.CoA, dbo.TestMethod.WorkInstruction, dbo.Customer.Name AS [Klant Testplan],
                     Customer_1.Name AS [Klant Schedule], LEFT(dbo.ShopFloor.ShopFloorNumber, 7) AS Schedule,
                     CASE WHEN Customer_1.Name = 'ItsMe BV' THEN Customer.Name ELSE Customer_1.Name END AS Customer
FROMÂ Â Â Â Â Â Â Â dbo.Customer AS Customer_1 INNER JOIN
[Code] ....
View 9 Replies
View Related
Apr 5, 2007
I am creating a website.
i have completed the GUI. i have labels on the left side of the form and a text box for each of those labels on the right side of the form. i have the two dropdown boxes that you have helped me create and bind. now im stuck on how to start the code to get this all working. i know what i need it to do (pseudocode) but dont know how to write the code for it. these books and online sources, really dont say where to put things, they just give examples, not stating where to place it.
I am using a SQL database called Manufacturers
i would like to insert or update, but not delete records wtih this tool
pixelsyndicate mentioned using the GUI to do the code for me, would love to know how to do this.
any help would be awesome, or any good beginner resources would help too
Thanks
View 1 Replies
View Related
Jul 18, 2007
I have code
Function GetDealCount(reportItems)
return iif(IsNothing(reportItems!txtDetailCountRows.Value), 0, reportItems!txtDetailCountRows.Value)
End Function
Function GetSumNotionalAmount(reportItems)
return iif(IsNothing(reportItems!txtDealSumNotionalAmount.Value), 0, reportItems!txtDealSumNotionalAmount.Value)
End Function
That I am calling from a textboxes in the page header
= Code.GetDealCount(ReportItems) & " Deal(s)"
also
= Parameters!BaseCurrency.Value + " " + Format(Code.GetSumNotionalAmount(ReportItems),"N2").ToString().Replace(",","'")
When I preview the report in VS.NET I get values showing.
When I deploy the report I just get #Error showing.
Also this report used to work fine in RS2000
Does anyone know the cause of this issue?
View 6 Replies
View Related
Jul 20, 2005
Hi thereGot a interesting problem (depends on a point of view.....).Background :-Agent (within Domino) is run daily basis which extracts data fromDomino Notes application to SQL Server 2000 database. Agent firstremoves ALL contents and then appends ALL data.Reporting Tool is SQL Reporting Services (very cool !!).Problem :-Within Domino Notes, it can have a field which is mult-value fieldi.e. contain multi valuese.g.(from Helpdesk application)!HowTo!!Access Email;!HowTo!!Access the Web;etc..Need to create a view, then use sql to create stored proc, to be usedas the dataset for report within SQL Reporting services.Format. (using above as data as example)!HowTo!!Access Email; (1 row) WWL/SDR/04023/010 (DocID)!HowTo!!Access the Web; (2 row) WWL/SDR/04023/010 (DocID)I have a unique KEY within table called DocID. The report will have 8separate datasets (i.e. using subreports), all linked back to UniqueKey. That's easy.The dataset causing me hassle is the 1 above. How do u split outvalues as separate rows ?Name of field called --> "ImpFunctionsImpacted".Also, notes expert, who's working on the agent, tell's me the data canbe split either as a comma or semi-colon.Any suggestions most welcome.
View 2 Replies
View Related
Oct 21, 2014
I have a comma separated field containing numerous 2 digit numbers that I would like splitting out by a corresponding unique code held in another field on the same row.
E.g
Unique Code Comma Separated Field
14587934 1,5,17,18,19,40,51,62,70
6998468 10,45,62,18,19
79585264 1,5,18
These needs to be in column format or held in an array to be used as conditional criteria.
Unique Code Comma Separated Value
79585264 1
79585264 5
79585264 18
View 5 Replies
View Related
Jul 14, 2015
How to set default values for selection list navigator without using parametrized queries for data source view.
View 2 Replies
View Related
Feb 2, 2008
,
Hi
In this code how can I create a new data source and new data source view and model and structure that it run dynamic.
In this code I have a lot of errors, that they are about server and database don€™t have in current code,
In this code, first I should definition server or no?
Database dbNew = new Database (databaseName,
Utils.GetSyntacticallyValidID(databaseName, typeof(Database)));
srv.Databases.Add(dbNew);
dbNew.Update(true);
***********************************************************
How can I create data source and data source view and model and structure?
Please say code of that, and guide me.
databasename and srv is unknown.
Do I add other reference with analysis services?
Please explain about these codes:
************************************************************************
1)
RelationalDataSource dsNew = new RelationalDataSource(
datasourceName,
Utils.GetSyntacticallyValidID(
datasourceName,
typeof(RelationalDataSource)));
db.DataSources.Add(dsNew);
dsNew.ConnectionString = connectionString;
dsNew.Update();
2)
RelationalDataSourceView rdsv;
rdsv = db.DataSourceViews.Add(
datasourceviewName,
Utils.GetSyntacticallyValidID(
datasourceviewName,
typeof(RelationalDataSourceView)));
rdsv.DataSourceID = ds.ID
***************************************************************
3)
OleDbConnection cn = new OleDbConnection(ds.ConnectionString);
OleDbCommand cmd = new OleDbCommand(
"SELECT * FROM [" + tableName + "] WHERE 0=1", cn);
OleDbDataAdapter ad = new OleDbDataAdapter(cmd);
DataSet dss = new DataSet();
ad.FillSchema(dss, SchemaType.Source);
*************************************************************
4)
// Make sure we have the name we thought
dss.Tables[0].TableName = tableName;
// Clone here - the original DataTable already belongs to a DataSet
rdsv.Schema.Tables.Add(dss.Tables[tableName].Clone());
rdsv.Update();
5)
MiningStructure ms = db.MiningStructures.Add(miningstructureName, Utils.GetSyntacticallyValidID(miningstructureName,
typeof(MiningStructure)));
ms.Source = new DataSourceViewBinding(dsv.ID);
ms.CaseTableName = "Customer";
Add columns:
ScalarMiningStructureColumn smsc;
// From table "Customer" we will add a couple of columns
// CustomerID - key
smsc = new ScalarMiningStructureColumn("Customer ID",
Utils.GetSyntacticallyValidID("Customer ID", typeof(ScalarMiningStructureColumn)));
smsc.IsKey = true;
smsc.Content = "Key";
smsc.KeyColumns.Add("Customer", "customer_id", OleDbType.Integer);
ms.Columns.Add(smsc);
*******************************************
6)
MiningModel mm = ms.MiningModels.Add(miningmodelName,
Utils.GetSyntacticallyValidID(miningmodelName,
typeof(MiningModel)));
mm.Algorithm = "Microsoft_Decision_Trees";
mm.Parameters.Add("COMPLEXITY_PENALTY", 0.3);
MiningModelColumn mc = new MiningModelColumn("Customer ID",
Utils.GetSyntacticallyValidID("CustomerID",
typeof(MiningModelColumn)));
mc.SourceColumnID = ms.Columns["Customer ID"].ID;
mc.Usage = "Key";
mm.Columns.Add(mc);
mm.Update();
Please exactly say, whatever I want
Thanks a lot for your answer
Please don€™t move this question because I don€™t know where I should write this.
View 1 Replies
View Related
Jul 27, 2006
Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here:
Function Main()
on error resume next
dim cn, i, rs, sSQL
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
set rs = DTSGlobalVariables("SQLstring").value
for i = 1 to rs.RecordCount
sSQL = rs.Fields(0).value
cn.Execute sSQL, , 128 'adExecuteNoRecords option for faster execution
rs.MoveNext
Next
Main = DTSTaskExecResult_Success
End Function
This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)
Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:
public Sub Main()
...
Dts.TaskResult = Dts.Results.Success
End Class
I get the following error when I attempt to compile this:
Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.
I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.
- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).
Given this statement:
dim cn, i, rs, sSQL
I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!
View 7 Replies
View Related
