Embedded SQL Code In A ASP.NET Tree Node
Feb 27, 2008
I am trying to modify a piece of SQL where I want to get the date difference from todays date and the datestamp on the latest record on dbo.activitym1 table AC1 ..which matches the following criteria..WHERE (AC1.Type = 'Assignment' or AC1.Type = 'Reassignment') and PS1.number = AC1.number)
PS1.number is the master record ID
But this doesnt seem to work ..the best result i've been able to achieve is all records from the dbo.activitym1 table that match the where clause and i only want the latest by date
heres the code
any help would be most appriciatedSelect PS1.number, PS1.brief_description," +
" DateDiff(day, PS1.open_time, Getdate()) as 'Days_Open', PS1.company," +" (select DateDiff(day, AC1.datestamp, Getdate())" +
" From " + ConfigurationManager.AppSettings["DATABASE_NAME"] + ".dbo.activitym1 as AC1" +" WHERE (AC1.Type = 'Assignment' or AC1.Type = 'Reassignment') and PS1.number = AC1.number) as 'Days_Since_Last_Assigned'," +
" CONVERT(VARCHAR(10), PS1.open_time, 103) as 'Date_logged'" +
" From " + ConfigurationManager.AppSettings["DATABASE_NAME"] + ".dbo.probsummarym1 as PS1" +
"where (PS1.assignment = 'PROD - UK CENTRAL CPH COGNOS SUPPORT' OR " +
" PS1.assignment = 'PROD - UK CENTRAL CPH CONS HEALTH' OR " +
" PS1.assignment = 'PROD - UK CENTRAL CPH PUBLICATIONS' OR " +
" PS1.assignment = 'PROD - UK CENTRAL CPH TERRITORY PLANNER' OR " +
" PS1.assignment = 'PROD - UK CENTRAL CPH VIEWPLUS' OR " +
" PS1.assignment = 'PROD - UK CENTRAL EDITORIAL' OR " +
" PS1.assignment = 'PROD - UK CENTRAL EDITORIAL CHEMICAL PIONEER' OR " +
" PS1.assignment = 'PROD - UK CENTRAL EDITORIAL COMPANY PROFILES' OR " +
" PS1.assignment = 'PROD - UK CENTRAL EDITORIAL COMPANY SEARCH' OR " +
" PS1.assignment = 'PROD - UK CENTRAL EDITORIAL GENERIC PLNG & ANLYSER' OR " +
" PS1.assignment = 'PROD - UK CENTRAL EDITORIAL LIFECYCLE NPF' OR " +
" PS1.assignment = 'PROD - UK CENTRAL EDITORIAL LIFECYCLE PATENTS' OR " +
" PS1.assignment = 'PROD - UK CENTRAL EDITORIAL LIFECYCLE RDF' OR " +
" PS1.assignment = 'PROD - UK CENTRAL EDITORIAL MIDAS GENERICS' OR " +
" PS1.assignment = 'PROD - UK CENTRAL FORECASTING ANALOGUE' OR " +
" PS1.assignment = 'PROD - UK CENTRAL FORECASTING GENERICS MARKET PROGNOSIS' OR " +
" PS1.assignment = 'PROD - UK CENTRAL FORECASTING MARKET PROGNOSIS' OR " +
" PS1.assignment = 'PROD - UK CENTRAL FORECASTING OTC REVIEW' OR " +
" PS1.assignment = 'PROD - UK CENTRAL FORECASTING THERAPY FORECASTER' OR " +
" PS1.assignment = 'PROD - UK CENTRAL GLOB CHEM INTPACK' OR " +
" PS1.assignment = 'PROD - UK CENTRAL GLOB CLASSFN' OR " +
" PS1.assignment = 'PROD - UK CENTRAL GLOB CORP' OR " +
" PS1.assignment = 'PROD - UK CENTRAL GLOB CUSU' OR " +
" PS1.assignment = 'PROD - UK CENTRAL GLOB DSG' OR " +
" PS1.assignment = 'PROD - UK CENTRAL GLOB GPIN' OR " +
" PS1.assignment = 'PROD - UK CENTRAL GLOB INTPRD' OR " +
" PS1.assignment = 'PROD - UK CENTRAL GLOB KNOWLEDGELINK' OR " +
" PS1.assignment = 'PROD - UK CENTRAL GLOB LICENSING' OR " +
" PS1.assignment = 'PROD - UK CENTRAL GLOB LPIN' OR " +
" PS1.assignment = 'PROD - UK CENTRAL GLOB MARK SEG' OR " +
" PS1.assignment = 'PROD - UK CENTRAL GLOB OTHER' OR " +
" PS1.assignment = 'PROD - UK CENTRAL GLOB PRICES FACTORS' OR " +
" PS1.assignment = 'PROD - UK CENTRAL GLOB STANDARDS' OR " +
" PS1.assignment = 'PROD - UK CENTRAL GLOB WHO' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT ACCOUNTS' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT ADMINS' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT EXEC' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT HPA CODING' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT HPA CPMS' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT HPA PROD CTRL' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT HPA QUALITY CONTROL' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT INTL FIELDWORK' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT IT SOL' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT PANEL CTRL' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT PATIENT DATA CODIN' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT PATIENT DATA PROCESSING' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT PATIENT DATA QRY & SUPPORT' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT PRODUCT REFERENCE' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT PSO' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT QUALITY ASSURANCE' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT RESEARCH HOSPITAL GROUP' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT WARD' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SITTINGBOURNE' )" +
" and UPPER(PS1.Status) <> 'CLOSED'
View 2 Replies
ADVERTISEMENT
May 30, 2008
hi,
How to get 2 level tree node in the sql server 2005. We have table with data.On load we populate the tree in Asp.net.
Regards,
Vinayak Panchal
View 2 Replies
View Related
May 12, 2015
I have a tree and I need to copy a nested sub-tree (an element with its children, which in turn may have their owns) from one place to another.
The system should allow to handle up to 8 levels. I do know how to move, but cannot figure out how to copy.
Below is a working example With Create Table, Select and Cut / Paste (implemented via Update).
I would like to know how to copy a nested tree with reference id 4451 from Parent_Id 1 to Parent_Id = 2
--***** Table Definition With Insert Into to provide some basic data ****
IF (OBJECT_ID ('myRefTable', 'U') IS NOT NULL)
DROP TABLE myRefTable;
GO
CREATE TABLE myRefTable
(
Reference_Id INT DEFAULT 0 NOT NULL CONSTRAINT myRefTable_PK PRIMARY KEY,
[Code] ....
How to Copy nested sub-tree 4451 with all its children to Parent_Id 2, without deleting from Parent_Id = 1 ?
View 7 Replies
View Related
Sep 12, 2005
Hi all. Here's my problem: I have a tree linking macaddresses (bigints)in a tree structure. i want to get the path from node a to b.create table tree1(father bigint , child bigint);insert into tree1 (father,child) values (100,200);insert into tree1 (father,child) values (100,300);insert into tree1 (father,child) values (100,400);insert into tree1 (father,child) values (200,2000);insert into tree1 (father,child) values (200,3000);insert into tree1 (father,child) values (100,4000);insert into tree1 (father,child) values (2000,11111);you can see that 100 --> 200 --> 2000 --> 11111select * from tree1what i would like is a query that given two parameters returns the pathbetweenthem, in the case of 100,11111 i want to get100200200011111if possible as different rows, but columns will do to.of course i do not know the legnth of the path. it can be very bigthx in advanceTzvika
View 3 Replies
View Related
May 2, 2007
After having built a decision tree model to predict a boolean output attribute using 64-bit SQL Server 2005 (build 9.0.3054), we have observed that predictions for some cases are being done at non-leaf nodes in the tree.
Specifically, after executing a prediction join which returns:
- CaseTable.CaseID
- MiningModel.OutputAttribute
- PredictProbability(MiningModel.OutputAttribute)
- PredictNodeId(MiningModel.OutputAttribute)
and comparing the values of PredictNodeID(MiningModel.OutputAttribute) with the mining model content column [NODE_UNIQUE_NAME] to determine the actual "rule" used to make the case-level prediction.
We have observed that for a subset of cases, predictions are being made at nodes in the tree that are not leaf nodes. Specifically, predictions are being made at a node that is 3 levels deep. The leaf nodes below this inner-tree node are 2 levels further down the tree.
Also supporting the fact that that predictions are being made at this non-leaf node is that the PredictProbability corresponds exactly with the output attribute distribution at this non-leaf node.
In this particular application, we would have obtained better results if the predictions were made at the leaf-nodes.
A few questions:
1. Why are predictions with decision trees made at non-leaf nodes?
2. Is there a way to "force" predictions to occur at leaf nodes via DMX?
Thanks in advance for any information or advice.
- Paul
View 1 Replies
View Related
Oct 16, 2007
Hi all,
Could someone tell me if custom code function can capture the event caused by a user? For example, onclick event on the rendered report?
Also, can custom code function alter the parameters of the report, or refresh the report?
Thanks.
View 2 Replies
View Related
May 9, 2008
Hi all!
I am trying to organize a hierarchical data structure into a table. I need to have the possibility to set 2 parents for some nodes. Curently I see following two options:
Example 1
id parent_id name-----------------------------------1 0 Level 1 Parent A2 0 Level 1 Parent B3 1,2 Level 2 Child
Example 2
id parent_id name-----------------------------------1 0 Level 1 Parent A2 0 Level 1 Parent B3 1 Level 2 Child3 2 Level 2 Child
Is any of the two examples valid database logic wise? In fact, is it possible to achieve the requirement by using only one table?
Thanks in advance,
View 4 Replies
View Related
Sep 22, 2006
Hi,
is there a way to import a decision tree-model from pmml where a node contains two or more states of an attribute as the split-rule?
Example:
...
<Node recordCount="600">
<CompoundPredicate booleanOperator="or">
<SimplePredicate field="color" operator="equal" value="red" />
<SimplePredicate field="color" operator="equal" value="green" />
</CompoundPredicate>
<ScoreDistribution value="true" recordCount="200"/>
<ScoreDistribution value="false" recordCount="400"/>
</Node>
...
This node shoud contain all cases, whose color is red or green (The Microsoft DecisionTree-Algorithm would build a model with two steps like red/ not red and then green / not green). According to the DMG, this is valid PMML 2.1, but when trying to import the server complains about an unexpected value in the SimplePredicate-tag.
How can i import such a node in SqlServer 2005?
Thank you in advance for any help
Chris
View 8 Replies
View Related
Mar 21, 2007
Is it possible to have a sql select statment in the embedded code in a report?
View 5 Replies
View Related
Jan 14, 2008
In my report, I have a table with a nested group. The inner most group contains a column with values that are dynamically calculated in the report. I would like the outer group to display the sum of the inner group values. However, the outer group does not have access to the inner group scope.
As a solution, I am attempting to use the method detailed at this site:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1129743&SiteID=1
Essentially, the method is to declare a hash table in the embedded code where data can be stored. I slightly modified the code to the following:
Code Block
Public Dim hashTable as new System.Collections.HashTable
function setValue(key As Object, value As Object) As Object
If (Not hashTable.ContainsKey(key)) Then hashTable.Add(key, value)
Return value
end Function
function getValue(key As Object) As Object
If (hashTable.ContainsKey(key)) Then Return hashTable(key) Else Return Nothing
end Function
function sumRunningValue(key As Object, value As Object) As Object
If (Not hashTable.ContainsKey(key)) Then hashTable.Add(key, value) Else hashTable(key) = hashTable(key) + value
Return hashTable(key)
end Function
Inside the inner scope everything works great and I can even get the runnning value. But the reason I did this was to be able to access the value in the outer group. But, it doesn't work! When I call Code.getValue(key) I get Nothing back.
So, it seems this is a scope issue. Is anyone aware of a work around for this problem. Is it possible to declare the hashtable with a global scope, for instance? Is the code redeclared for every new group?
Thanks for any assistance,
Tyler
View 4 Replies
View Related
May 31, 2007
I have a problem in which I would like to use the value of the togglestate property for textbox in embedded code to drive some other logic.
My first question is, "Can it be done?" and if so, how?
View 2 Replies
View Related
May 31, 2007
Hi,
I was wondering if there's a way to connect to a shared datasource in the Code section of a report's properties. I want to connect using my shared datasource as defined in my solution and query it to return a value based on user input for a function I need to put there.
Thanks,
Greg
View 1 Replies
View Related
May 11, 2007
Hi,
I have a problem that seems impossible to resolve in RS but i need to be sure.
I'm creating an Embedded Code in VB of course, and i need to run a query within my code to be able to continue the rest of the code depending on the result of my query.
The problem is there is no adodb.recordset or adodb.connection like in normal VB.
So i created my query like as string:
Dim Sql as String
Sql="Select ..." into "from ........where...."
At this point if it was in normal VB,
i would do
MyConnection.Execute(SQL)
and then i will get the answer in my recordset.
I need to know if there is a way in RS to run my query since I cannot use use adodb ....
If it's not really possible, is there way to go around this problem????
Thanks for the Help
Mike
View 1 Replies
View Related
Sep 17, 2007
One of the users on another web site posted a question about how to associate users in a tree-like organization. That web site isn't well suited to posting code or ongoing discussions about code, so I'm going to post the example here. Feel free to discuss as you see fit.DROP TABLE LI_UserLinks
GO
DROP TABLE LI_Users
GO
DECLARE @d1DATETIME
, @d2DATETIME
, @d3DATETIME
, @d4DATETIME
, @d5DATETIME
, @d6DATETIME
, @d7DATETIME
, @d8DATETIME
SELECT @d1 = GetDate()
CREATE TABLE LI_Users (
uidINT
PRIMARY KEY (uid)
)
SELECT @d2 = GetDate()
CREATE TABLE LI_UserLinks (
uid_fromINT
CONSTRAINT XFK01LI_UserLinks FOREIGN KEY (uid_from)
REFERENCES LI_Users (uid)
, uid_toINT
CONSTRAINT SFK01LI_UserLinks FOREIGN KEY (uid_to)
REFERENCES LI_Users (uid)
CONSTRAINT XPKLI_UserLinks PRIMARY KEY (uid_from, uid_to)
)
ALTER TABLE LI_Userlinks
ADD CONSTRAINT XCK01LI_UserLinks CHECK (uid_from != uid_to)
SELECT @d3 = GetDate()
INSERT INTO LI_Users (
uid) SELECT n0 + 10 * n1 + 100 * n2 + 1000 * n3 + 10000 * n4 + 100000 * n5
FROM (SELECT 0 AS n0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z0
CROSS JOIN (SELECT 0 AS n1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z1
CROSS JOIN (SELECT 0 AS n2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z2
CROSS JOIN (SELECT 0 AS n3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z3
CROSS JOIN (SELECT 0 AS n4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z4
CROSS JOIN (SELECT 0 AS n5 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z5
SELECT @d4 = GetDate()
INSERT INTO LI_UserLinks (
uid_from, uid_to) SELECT
uid, 100 * uid + n0 + 10 * n1
FROM LI_Users
CROSS JOIN (SELECT 0 AS n0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z0
CROSS JOIN (SELECT 0 AS n1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z1
WHERE LI_Users.uid BETWEEN 0 AND 99
AND uid != 100 * uid + n0 + 10 * n1
SELECT @d5 = GetDate()
INSERT INTO LI_UserLinks (
uid_from, uid_to) SELECT
uid, 100 * uid + n0 + 10 * n1
FROM LI_Users
CROSS JOIN (SELECT 0 AS n0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z0
CROSS JOIN (SELECT 0 AS n1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z1
WHERE LI_Users.uid BETWEEN 100 AND 9999
SELECT @d6 = GetDate()
SELECT u.uid, r1.uid_to, r2.uid_to
FROM LI_Users AS u
INNER JOIN LI_UserLinks AS r1
ON (r1.uid_from = u.uid)
INNER JOIN LI_UserLinks AS r2
ON (r2.uid_from = r1.uid_to)
WHERE 1 = u.uid
SELECT @d7 = GetDate()
SELECT Count(DISTINCT u.uid), Count(DISTINCT r1.uid_to), Count(distinct r2.uid_to)
FROM LI_Users AS u
INNER JOIN LI_UserLinks AS r1
ON (r1.uid_from = u.uid)
INNER JOIN LI_UserLinks AS r2
ON (r2.uid_from = r1.uid_to)
SELECT @d8 = GetDate()
SELECT
DateDiff(ms, @d1, @d2)
, DateDiff(ms, @d2, @d3)
, DateDiff(ms, @d3, @d4)
, DateDiff(ms, @d4, @d5)
, DateDiff(ms, @d5, @d6)
, DateDiff(ms, @d6, @d7)
, DateDiff(ms, @d7, @d8)
, DateDiff(ms, @d1, @d8)-PatP
View 14 Replies
View Related
Sep 16, 2005
Wonder if somebody know if it exist som tools that makes it possible to geta graphical overview of relationships between C# source code components andtheir's interaction with the database tables/elemts (E.g SQL serverdatabase)A typecial rewengineering tool to get a grasp of a system with manycomponents acessing multiple tables in a rdm system.A tool that parses the C# code for sql/(databse interaction) and presentsthe interaction grahical in som way?Many thanksAksel
View 3 Replies
View Related
Apr 6, 2015
I got assignment, how to make it appear in the right order .
/* DROP TABLE EMP
SELECT * INTO Emp FROM (
SELECT 'A' EmpID, NULL ManID, 'Name' EmpName UNION ALL
SELECT 'MAC' EmpID, 'A' ManID, 'Name__' EmpName UNION ALL
SELECT '1ABA' EmpID, 'MAC' ManID, 'Name____' EmpName UNION ALL
SELECT 'ABB' EmpID, '1ABA' ManID, 'Name______' EmpName UNION ALL
SELECT 'XB' EmpID, 'A' ManID, 'Name__' EmpName UNION ALL
SELECT 'BAC' EmpID, 'XB' ManID, 'Name____' EmpName ) b
*/
[code]....
View 2 Replies
View Related
Apr 20, 2015
I have not used log shipping before and find myself in a position where I need to reboot the secondary node and then the primary node and I don't actually need to failover.
Is there anything I need to be aware of. When rebooting the secondary node I assume the transactions will be held in the primary nodes log till the secondary comes back and just carry on once back up?
When rebooting the primary node nothing needs to be done and the log shipping will just start again once it has come back?
View 3 Replies
View Related
Jun 12, 2007
I read these instructions:
http://msdn2.microsoft.com/en-us/library/ms191545(SQL.90).aspx
But I'm not sure if I have to install SQL Server first on node 2, then add it to the cluster. Or does adding it to the cluster also install the software?
Thanks
View 1 Replies
View Related
Oct 23, 2014
I'm contemplating running two availability groups on a two node WSFC. The WSFC is setup with a file share witness (i.e. no shared storage). Can I safely run 1 AG on one primary node, and the other AG on the other node (as primary). Each AG would have replicas on the passive node. This would effectively allow both servers to be in use at the same time. In a failover event, I understand that both workloads would transfer to a single server - so the box needs to be sized appropriately.
View 1 Replies
View Related
Jun 11, 2015
We are in the process of building a 3 node SQL Server Cluster (Server 2012/ SQL Server 2012), and we have configured the quorum so that all 3 nodes have a vote (no file share witness as we already have an odd number of nodes).
As I understand it, this should allow the cluster to run as long as 2 of the nodes remain online.
However, the validation report states that 2 node failures would be acceptable and, when we tested this by powering off two of the nodes, the cluster did indeed continue to run on a single node.
View 4 Replies
View Related
Aug 21, 2007
I configure Windows 2003 R2 and SQL 2005 two nodes Cluster. When I move cluster resource from one node to anther node it takes around 30 seconds to become online. So in that time if any query is running it stops responding.
So please suggest in this regard
View 2 Replies
View Related
Jan 16, 2008
I invoke xp_cmdshell proc from inside a stored procedure on a 2-node active/passive SQL 2005 SP2 Standard cluster. Depending on which server the xp_cmdshell gets executed on I need to pass different arguments in the shell command. I thought I could use host_name() function to get the runtime process server, however, I am finding that it's not behaving correctly. In one example I know my active node is server2, but the host_name() function is returning server1. The only thing that I could possible explain this is that the MSDTC cluster group is not always on the same active node as the SQL server group and in the case I am talking about the cluster groups are in this mode (differnet nodes). Does the xp_cmdshell get executed by the SQL active node or the MDTC active node? And what is the best way to find out which server is going to run my xp_cmdshell?
Thanks.
Edit:
Perhaps another by product of this is that if I run select host_name() from the Studio Management query window i get different results depending on which server I am running the Studio Management on. On server1 I get server1 and on server 2 I get server 2, all the while server2 is the active node. I need a different function that will always let me determine the correct server that'll be running the xp_cmdshell...
Edit 2: I guess I could determine the running host inside the command shell itself, but I am curious to see if i can do it (cleaner) from SQL.
View 1 Replies
View Related
Sep 18, 2000
I have a DTS package that transfers some columns defined as varchar(8000).
Sometimes, the values in the columns defined this way have embedded CR/LF in them. When they do, DTS does not always transfer the values to the target.
Any idea why? Workarounds? TIA.
View 1 Replies
View Related
Jul 8, 2004
Why is using an embedded SELECT statement faster than using an embedded UDF that has the same exact SQL code within?
Example (not syntax corrected)
Ex1:
SELECT myValue1, myValue2 FROM myTable WHERE aValue = (SELECT Value FROM someTable WHERE myIndex = 800)
is much faster than something like
Ex2:
SELECT myValue1, myValue2 FROM myTable WHERE aValue = (dbo.FN_myUDF(@vmyIndex))
Given that dbo.FN_myUDF has the same code as the embedded select in the first example.
TIA,
KB
View 4 Replies
View Related
Feb 18, 2006
I'm looking into a problem a friend is having, and I'll say right offthe bat that I work with with php and MySQL, and not MS SQL.What he is attempting to do (in MS SQL) is take two database fieldsfrom a table (string fields), multiply them together, and put them intoa third field. This third column in the table has not yet been createdthe time of running the query.If it needs to be multiple queries, that is fine. My first thought isto use a simple ALTER query to add the column to the table, then tocall a UPDATE function which uses a select statement inside of it. I'mnot sure if something like this can even be done.// ------------ Suggested queryUPDATE chrisslu SET 'discquantity' = '(SELECTchrisslu.quantity*chrisslu.nr_of_discFROM chrissluWHERE (str(period,6)>=? AND str(period,6)<=?))' WHERE(str(period,6)>=?Andstr(period,6)<=?)// ------------ End Suggested queryIt starts with an UPDATE, but replaces the value to be set with aSELECT statement. I honestly don't even think this query issyntactically correct, I'm just trying to get the general concept down:).So, question the first: Is this type of query possible? The reasonI'm doing this is because I was told MS SQL has no way of storingtemporary variables... otherwise I would just call a SELECT statement,store the variable, and UPDATE the new field from the variable afterthe ALTER statement.Second question: If it is possible, am I on the right track, or doesit need to be entered in completely different than what I have?Third: Regarding the 'type'. Do I need to do any kind of typecastingor conversion of the fields? Both chrisslu.quantity andchrisslu.nr_of_disc are string fields (that is what I was told, theymay be varchar of some kind). In order to use them in a mathstatement, do they have to be floats, or doubles, or something similar?I appreciate any response, I know this was a long winded question.Chris
View 9 Replies
View Related
May 22, 2007
Hey everyone,
Is there any way to embed a video in a report? It doesn't seem like it but I thought I may be missing something. Maybe directly editing the xml code? Thanks.
-Keith
View 1 Replies
View Related
Dec 4, 2006
I have embedded tabs in a text field that I want to import to a destination table.
I was thinking I need to replace the tabs with spaces. REPLACE(character_expression,searchstring,replacementstring) Anybody know how to specify ascii in the character expression.If there is a better way I am open to suggestions, however I do not way to remove this in the raw data but handle at transformation time. Thanks,Larry
View 9 Replies
View Related
Jul 11, 2007
Hi there!
I've an embedded image (logo) in my report, and in the preview it looks wonderful, but after deploy the logo doesnt appear in the report (report server).
The url of the dead-link-image is http://hamsql1/Reports$BI/Reserved.ReportViewerWebControl.axd?.
I thought that an embedded image is stored "in" the report, but it seems to be an other place. Is it nessecary to deploy an embedded image too? Is it a problem of permissions (i read it in another threat)? How to set these image-see-permission? Or is there an other problem?
Every idea is very welcome!
Thanks,
Torsten
View 6 Replies
View Related
Jun 11, 2008
I have a Select statement that was working just fine:
string sSqlCmd = "INSERT INTO SiloKeywords (Silo_ID, Keyword, UserName) SELECT Silo_ID,'" + Keyword + "', '" + strUsername + "' FROM SiloNames WHERE Silo_Name = '" + Uri + "'";
the PM now wants to capture the internal User, which I have done but I am having real problems with the syntax if the internal User exists:
string sSqlCmd = "INSERT INTO SiloKeywords (Silo_ID, Keyword, UserName, IsInternal) SELECT Silo_ID,'" + Keyword + "', '" + strUserName + "', if EXISTS(SELECT * FROM InternalUsersList WHERE Alias = '" + arrResult[1] + "') 1 ELSE 0 FROM SiloNames WHERE Silo_Name = '" + Uri + "'";
I am completely lost here.
Thanks for any help
View 3 Replies
View Related
Mar 18, 2004
Hi,
I am trying to find something like a light version of SQL Server 2k to store data for an application that will be distributed through a cd.
Since we also have a web front end for this application when the user is online, we prefer that we can reuse the code for connection on both the cd and the web.
Any idea what will work?
I have come accross MSDE 2k but was not able to find any documentation on the pros and cons for this product regarding its capacity, the type of data it can handle, security, ...
Please give me some pointer.
Thanks very much.
Baoha.
View 1 Replies
View Related
Aug 25, 2005
I was wondering if anyone had tried to embed sql server express in a .net application. Specifically, I want to use it from within the app only, similar to embedded mysql (libmysqld) or firebird. I've seen some articles on sql server express saying this can be done, however there is no info on how do to this. Anyone know how it might be done?
View 1 Replies
View Related
Oct 10, 2005
Beyond my control: I am finding control characters (likely tab) ismaking its way into address fields of our operational system. This ismessing me up when I load the data into our warehouse w/ BCP (fieldsget shifted).Is the a nifty way to strip control characters from data?TIARob
View 1 Replies
View Related
Jul 20, 2005
What I'd like to do is use the result of one query as the input foranother. eg:INSERT INTO foo VALUES ((SELECT id FROM people WHERE name = "bar"),10,'foobar') WHEREid = 1;Problem is, MSSQL wants a scalar value. Is there a way around this?Can someone tell me the correct syntax for what I want to do, or is thissomething that will have to be done outside SQL?Err. Hope I've been clear. Thanks for any help you folks can give.
View 2 Replies
View Related
