Hi people,I wasn't sure where to post this, it's a specific SQL syntax type problem, and didn't know where it should go. Sorry if there's a better place for it!I want to update table 'Pupils' with some data from table 'TempAttendance'. I have an id number in the temp table that matches up with the pupil's id number in the proper 'Pupils' table. I've tried loads, but I'm struggling! So I want to update table Pupils.attendance with the value from TempAttendance.attendance where the ID = the ID. I can't get the syntax right. Pleas help if you can! Thanks
GLOBAL STRING Line1_BatchID GLOBAL STRING Line1_CompletedDate GLOBAL INT Line1_CompletedQty GLOBAL STRING Line1_CompletedTime GLOBAL STRING Line1_ExpiryDate GLOBAL INT Line1_Qty GLOBAL STRING Line1_OrderID
GLOBAL INT Line1_BatchID_ID GLOBAL INT Line1_CompletedDate_ID GLOBAL INT Line1_CompletedQty_ID GLOBAL INT Line1_CompletedTime_ID GLOBAL INT Line1_ExpiryDate_ID GLOBAL INT Line1_OrderStatus_ID
GLOBAL STRING Line2_BatchID GLOBAL STRING Line2_CompletedDate GLOBAL INT Line2_CompletedQty GLOBAL STRING Line2_CompletedTime GLOBAL STRING Line2_ExpiryDate GLOBAL STRING Line2_OrderStatus GLOBAL INT Line2_Qty GLOBAL STRING Line2_OrderID
GLOBAL INT Line2_BatchID_ID GLOBAL INT Line2_CompletedDate_ID GLOBAL INT Line2_CompletedQty_ID GLOBAL INT Line2_CompletedTime_ID GLOBAL INT Line2_ExpiryDate_ID GLOBAL INT Line2_OrderStatus_ID
GLOBAL STRING Line3_BatchID GLOBAL STRING Line3_CompletedDate GLOBAL INT Line3_CompletedQty GLOBAL STRING Line3_CompletedTime GLOBAL STRING Line3_ExpiryDate GLOBAL STRING Line3_OrderStatus GLOBAL INT Line3_Qty GLOBAL STRING Line3_OrderID
GLOBAL INT Line3_BatchID_ID GLOBAL INT Line3_CompletedDate_ID GLOBAL INT Line3_CompletedQty_ID GLOBAL INT Line3_CompletedTime_ID GLOBAL INT Line3_ExpiryDate_ID GLOBAL INT Line3_OrderStatus_ID
GLOBAL STRING result GLOBAL STRING result2
FUNCTION getOrderDetail(STRING Product_ID) INT counter = 0 INT statusSQL, sqlResult; STRING Sql1 STRING Sql2 STRING Sql3
result = ""
statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success Sql1 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'productid') AND (DataValue = '" + Product_ID + "')" sqlResult = SQLExec(statusSQL, Sql1); IF sqlResult = 0 THEN //If SQL Success WHILE SQLNext(statusSQL) = 0 DO IF result <> "" THEN result = result + "," END result = result + SQLGetField(statusSQL, "SetId") END END
Sql2 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'order status') AND (DataValue = 'pending') AND (SetId IN (" + result + ")) Order by SetID" sqlResult = SQLExec(statusSQL, Sql2); IF sqlResult = 0 THEN //If SQL Success result = "" IF SQLNext(statusSQL) = 0 THEN //Select first SetId result = SQLGetField(statusSQL, "SetId") END END
Sql3 = "SELECT Id, DataValue FROM ProductionDataField WHERE (SetId IN (" + result + ")) and (IsActive = 1) Order by field" sqlResult = SQLExec(statusSQL, Sql3); IF sqlResult = 0 THEN //If SQL Success WHILE SQLNext(statusSQL) = 0 DO IF Product_ID = "Biscuit" THEN IF counter < 9 THEN IF counter = 0 THEN Line1_BatchID_ID = SQLGetField(statusSQL , "ID") END IF counter = 1 THEN Line1_CompletedDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 2 THEN Line1_CompletedQty_ID = SQLGetField(statusSQL , "ID") Line1_CompletedQty = SQLGetField(statusSQL , "DataValue") END IF counter = 3 THEN Line1_CompletedTime_ID = SQLGetField(statusSQL , "ID") END IF counter = 4 THEN Line1_ExpiryDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 5 THEN Line1_OrderStatus_ID = SQLGetField(statusSQL , "ID") END IF counter = 6 THEN Line1_OrderID = SQLGetField(statusSQL , "DataValue") END IF counter = 7 THEN
END IF counter = 8 THEN Line1_Qty = SQLGetField(statusSQL , "DataValue") END END END
IF Product_ID = "ChocolateBiscuit" THEN IF counter = 0 THEN Line2_BatchID_ID = SQLGetField(statusSQL , "ID") END IF counter = 1 THEN Line2_CompletedDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 2 THEN Line2_CompletedQty_ID = SQLGetField(statusSQL , "ID") Line2_CompletedQty = SQLGetField(statusSQL , "DataValue") END IF counter = 3 THEN Line2_CompletedTime_ID = SQLGetField(statusSQL , "ID") END IF counter = 4 THEN Line2_ExpiryDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 5 THEN Line2_OrderStatus_ID = SQLGetField(statusSQL , "ID") END IF counter = 6 THEN Line2_OrderID = SQLGetField(statusSQL , "DataValue") END IF counter = 7 THEN
END IF counter = 8 THEN Line2_Qty = SQLGetField(statusSQL , "DataValue") END END
IF Product_ID = "PeanutButterBiscuit" THEN IF counter = 0 THEN Line3_BatchID_ID = SQLGetField(statusSQL , "ID") END IF counter = 1 THEN Line3_CompletedDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 2 THEN Line3_CompletedQty_ID = SQLGetField(statusSQL , "ID") Line3_CompletedQty = SQLGetField(statusSQL , "DataValue") END IF counter = 3 THEN Line3_CompletedTime_ID = SQLGetField(statusSQL , "ID") END IF counter = 4 THEN Line3_ExpiryDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 5 THEN Line3_OrderStatus_ID = SQLGetField(statusSQL , "ID") END IF counter = 6 THEN Line3_OrderID = SQLGetField(statusSQL , "DataValue") END IF counter = 7 THEN
END IF counter = 8 THEN Line3_Qty = SQLGetField(statusSQL , "DataValue") END END
counter = counter + 1 END END
END SQLEnd(statusSQL) SQLDisconnect("DSN=SQLSRV_TBLS") END
FUNCTION UpdateBatchID(STRING Product_ID) INT statusSQL, sqlResult; STRING Sql1 statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success IF Product_ID = "Biscuit" THEN Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line1_BatchID + "' WHERE [Id] = " + IntToStr(Line1_BatchID_ID) END IF Product_ID = "ChocolateBiscuit" THEN Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line2_BatchID + "' WHERE [Id] = " + IntToStr(Line2_BatchID_ID) END IF Product_ID = "PeanutButterBiscuit" THEN Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line3_BatchID + "' WHERE [Id] = " + IntToStr(Line3_BatchID_ID) END
sqlResult = SQLExec(statusSQL, Sql1); END
SQLEnd(statusSQL) SQLDisconnect("DSN=SQLSRV_TBLS") END
FUNCTION UpdateCompletedQty(STRING Product_ID) INT statusSQL, sqlResult; STRING Sql1 statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success IF Product_ID = "Biscuit" THEN Sql1 = "UPDATE ProductionDataField SET DataValue = '" + IntToStr(Line1_CompletedQty) + "' WHERE [Id] = " + IntToStr(Line1_CompletedQty_ID) END IF Product_ID = "ChocolateBiscuit" THEN Sql1 = "UPDATE ProductionDataField SET DataValue = '" + IntToStr(Line2_CompletedQty) + "' WHERE [Id] = " + IntToStr(Line2_CompletedQty_ID) END IF Product_ID = "PeanutButterBiscuit" THEN Sql1 = "UPDATE ProductionDataField SET DataValue = '" + IntToStr(Line3_CompletedQty) + "' WHERE [Id] = " + IntToStr(Line3_CompletedQty_ID) END
sqlResult = SQLExec(statusSQL, Sql1); END
SQLEnd(statusSQL) SQLDisconnect("DSN=SQLSRV_TBLS") END
FUNCTION UpdateCompletedOrder(STRING Product_ID) INT statusSQL, sqlResult; STRING Sql1 statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success IF Product_ID = "Biscuit" THEN Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line1_CompletedDate + "' WHERE [Id] = " + IntToStr(Line1_CompletedDate_ID) sqlResult = SQLExec(statusSQL, Sql1);
Sql1 = "UPDATE ProductionDataField SET [DataValue] = 'Completed' WHERE [Id] = " + IntToStr(Line3_OrderStatus_ID) sqlResult = SQLExec(statusSQL, Sql1); END END
SQLEnd(statusSQL) SQLDisconnect("DSN=SQLSRV_TBLS") END
INT FUNCTION getLine1_Qty() RETURN Line1_Qty END
INT FUNCTION getLine1_CompletedQty() RETURN Line1_CompletedQty END
STRING FUNCTION getLine1_OrderID() RETURN Line1_OrderID END
STRING FUNCTION getLine1_BatchID() RETURN Line1_BatchID END
INT FUNCTION getLine2_Qty() RETURN Line2_Qty END
INT FUNCTION getLine2_CompletedQty() RETURN Line2_CompletedQty END
STRING FUNCTION getLine2_OrderID() RETURN Line2_OrderID END
STRING FUNCTION getLine2_BatchID() RETURN Line2_BatchID END
INT FUNCTION getLine3_Qty() RETURN Line3_Qty END
INT FUNCTION getLine3_CompletedQty() RETURN Line3_CompletedQty END
STRING FUNCTION getLine3_OrderID() RETURN Line3_OrderID END
STRING FUNCTION getLine3_BatchID() RETURN Line3_BatchID END
FUNCTION dbTest()
INT statusSQL, sqlResult; INT counter = 0 STRING Sql1 STRING Sql2 STRING Sql3 STRING Product_ID = "Biscuit" result = ""
statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success Sql1 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'productid') AND (DataValue = '" + Product_ID + "')" sqlResult = SQLExec(statusSQL, Sql1); IF sqlResult = 0 THEN //If SQL Success WHILE SQLNext(statusSQL) = 0 DO IF result <> "" THEN result = result + "," END result = result + SQLGetField(statusSQL, "SetId") END END
Sql2 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'order status') AND (DataValue = 'pending') AND (SetId IN (" + result + "))" sqlResult = SQLExec(statusSQL, Sql2); IF sqlResult = 0 THEN //If SQL Success result = "" IF SQLNext(statusSQL) = 0 THEN result = SQLGetField(statusSQL, "SetId") END END
Sql3 = "SELECT Id, DataValue FROM ProductionDataField WHERE (SetId IN (" + result + ")) and (IsActive = 1) Order by field" sqlResult = SQLExec(statusSQL, Sql3); IF sqlResult = 0 THEN //If SQL Success WHILE SQLNext(statusSQL) = 0 DO IF Product_ID = "Biscuit" THEN IF counter = 0 THEN Line1_BatchID_ID = SQLGetField(statusSQL , "ID") Line1_BatchID = SQLGetField(statusSQL , "DataValue") END IF counter = 1 THEN Line1_CompletedDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 2 THEN Line1_CompletedQty_ID = SQLGetField(statusSQL , "ID") Line1_CompletedQty = SQLGetField(statusSQL , "DataValue") END IF counter = 3 THEN Line1_CompletedTime_ID = SQLGetField(statusSQL , "ID") END IF counter = 4 THEN Line1_ExpiryDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 5 THEN Line1_OrderStatus_ID = SQLGetField(statusSQL , "ID") END IF counter = 6 THEN Line1_OrderID = SQLGetField(statusSQL , "DataValue") END IF counter = 7 THEN
END IF counter = 8 THEN Line1_Qty = SQLGetField(statusSQL , "DataValue") END END
IF Product_ID = "ChocolateBiscuit" THEN IF counter = 0 THEN Line2_BatchID_ID = SQLGetField(statusSQL , "ID") Line2_BatchID = SQLGetField(statusSQL , "DataValue") END IF counter = 1 THEN Line2_CompletedDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 2 THEN Line2_CompletedQty_ID = SQLGetField(statusSQL , "ID") Line2_CompletedQty = SQLGetField(statusSQL , "DataValue") END IF counter = 3 THEN Line2_CompletedTime_ID = SQLGetField(statusSQL , "ID") END IF counter = 4 THEN Line2_ExpiryDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 5 THEN Line2_OrderStatus_ID = SQLGetField(statusSQL , "ID") END IF counter = 6 THEN Line2_OrderID = SQLGetField(statusSQL , "DataValue") END IF counter = 7 THEN
END IF counter = 8 THEN Line2_Qty = SQLGetField(statusSQL , "DataValue") END END
IF Product_ID = "PeanutButterBiscuit" THEN IF counter = 0 THEN Line3_BatchID_ID = SQLGetField(statusSQL , "ID") Line3_BatchID = SQLGetField(statusSQL , "DataValue") END IF counter = 1 THEN Line3_CompletedDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 2 THEN Line3_CompletedQty_ID = SQLGetField(statusSQL , "ID") Line3_CompletedQty = SQLGetField(statusSQL , "DataValue") END IF counter = 3 THEN Line3_CompletedTime_ID = SQLGetField(statusSQL , "ID") END IF counter = 4 THEN Line3_ExpiryDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 5 THEN Line3_OrderStatus_ID = SQLGetField(statusSQL , "ID") END IF counter = 6 THEN Line3_OrderID = SQLGetField(statusSQL , "DataValue") END IF counter = 7 THEN
END IF counter = 8 THEN Line3_Qty = SQLGetField(statusSQL , "DataValue") END END
counter = counter + 1 END END
END SQLEnd(statusSQL) SQLDisconnect("DSN=SQLSRV_TBLS") END
Here i am going to ask some questions in SSIS pls clarify my doubts...
1. Can i include a package with in another package? like If i have 20 packages..i need to run all packages at a time ..for this can i create a package and can i place all those packages in to a single package?
2. If i have 700 packages then how to run those packages all at a time?
3.Can i develop a Workflow or package in SSIS with out using Data Flow Tasks? Like with the help of only control Flow Tasks can i create a package or workflow?
There are a few questions I have on the parameters you can use in a connection string. I didn't see a post with these already, and I think they may be helpful for those just starting to figure out how things work. I couldn't seem to find these defined in the msdn anywhere. What exactly do the following do:
"server=.\sqlexpress;" or "data source=.\sqlexpress;" - Is there a difference in using these, or are they completely interchangeable without any side effects? So far it seems I have been able to swap them for each other without anything happening however there may be something I don't see happening behind the scenes!
"Trusted_Connection=yes;" - Is this a workaround for getting SQL Server to work on vista's UAC or something? If not what does it do?
"Integrated Security=true;" - I know this has to be used with User Instance=true, but is there anywhere that says what it's actual function is?
"database=myDB;" - If I use this in a string it causes my program to crash. It also has a side effect of making me have to stop all sqlservr.exe processes and restart the service before my program will run again without it included in the string. Not sure why this is the case, however here is the string I try to use:
Now with using that string, the point of having both the "AttachDbFilename="c:\sfsf\sads\myDB.mdf";" and the "database=myDB;" options is that I read if you use those together it causes the SQL Server to first check and see if the database is already attached. If it is then it doesn't reattach the database, and just uses the current attached database. While this makes sense it doesn't seem to work for me since as I stated above anytime I use the database option it crashes the app. Any ideas? If that isn't how it is used what is the "database=" option for?
There may be more options I don't know about yet, but those are the ones I am not clear on. Hopefully one of you more experienced folks and shed some light on them!
I'm evaluating SQL 2005 Everywhere Edition for use by our desktop application. I'm a traditional SQL Server developer and I rely heavily on stored-procedures to encapsulate basic data manipulations across multiple tables and inside multi-statement transactions.
I was excited to see an in-process version of SQL released and my thought was "this is great... now I can ditch the tediousness of individual OLEDB/.NET commands, and write batches of T-SQL and just focus on the data manipulations". But, alas, it seems I cannot. Why is SQL Everywhere Edition limited to executing a single SQL statement at a time?
For example, my application would like to update mutlipe rows in one table, delete multiple rows from another, and insert multiple rows into a third. I can do that with 3 T-SQL statements in a single small batch in a very readable way with full blown SQL Server. (and I can put that batch in a stored procedure and re-use it efficiently later.) If I contemplate how to do that with OLEDB and the single statement limitation of SQL Everywhere, it's a lot more code and a lot less appealing/maintainable. I want as much of my app to be using declarative code and as little as possible tied up in tedious OLEDB calls. Is this not possible with SQL Everywhere Edition?
Please add some more substantial needed info in ALL the web pages of "SQL Server 2005 Compact Edition RC1, Release Candidate 1", like at:
http://support.microsoft.com/kb/920700
for: 1) Clarifying the confusing name changes of the Mobile Edition to Everywhere Edition then to CE, 2) Giving installion instructions of the RC1 for mobile devices
I am new comer to SQL server. I have one question: if you have some tables need need be reviewed (maybe use "select")website visitors, what is the best way to set the permission so that the visitors can only read from the table, not have the right to change it?
I have a SQL script that needs to change from a particular DB to the Master DB (to grant EXECUTE permissions to a few SPs in the Master DB), then change back to the original DB. I will not know at design time what the DB name is.
What I am trying to do is something like this:
Use Master GO Grant EXECUTE on sp_OACreate TO C2SRolePowerUsers GO Declare @DBName varchar(32) EXECUTE xp_instance_regread 'HKEY_LOCAL_MACHINE', 'SOFTWAREODBCODBC.iniMyDSNName', 'Database', @param = @DBName OUTPUT Use @DBName GO
The problems:
1) I have to do this in a single pass using ADO and the script. The reason is that it is part of a generic routine, looping through a list off commands, and cannot perform one command, change the DB reference via ADO, then change back in separate ADO commands.
2) It fails on the second USE command.
I have looked to see if there is some sort of "EVAL()" function, or a stored procedure to change the current DB. No luck.
Can u briefly explain in which cases decimal,numeric,money datatypes is used any difference between them and explain what is the precision and scale for those datatypes and can u increase the precision and scale in money datatype thank in advance
Dear Expert,How can I reuse the column, instead of select the whole things again.Example as below :selectcolumn1 as A,column2 as B,column3 * A as Cfrom dummy ;in MS SQL I have to do like this,selectcolumn1 as A,column2 as B,column3 * column1 as Cfrom dummy ;ThanksDesmond
I'm doing a shopping cart using SQL Express and Visual Studio Web Developer on C#, ASP.NET
I recieved error when adding a order: The variable name '@oid' has already been declared. Variable names must be unique within a query batch or stored procedure. The codes are: comm = new SqlCommand("SELECT IDENT_CURRENT('Orders') as NewOrderID ");
Are there any examples of using DTS and XML/XLS? specifically, importing data. I have searched through BOL and cant find any, nor is there any reference to XML in the book "Professional SQL 2000 DTS"
I see a lot of example, but where do these example procedures go. Likedeclare (whatever)?below is an example i read. Where do you put this to make it execute, is itthe view screen or the stored procedure screen?I'm using MSDE now to learn, and I can't get nothing working except simpleselect query statements.In the northwind example (northwindcs), how would I do a parameter querylike this:Have a dialog box ask user to enter customerid to bring up. (in a query nownot a form)Also, how would you check if a certain customerid exist? Example, CHOPS isone customerid. If I wanted to use a query to check if it exist, and returnno records, but just do an action (like add a record) if it didn't exist,how?[color=blue]> CPU SQL> (ms)> -- Convert to varchar (implicitly) and compare right two digits> -- (original version -- no I didn't write it)> 4546 select sum(case right(srp,2)> when '99' then 1 when '49' then 1 else 0 end)> from sf>> -- Use LIKE for a single comparison instead of two, much faster> -- Note that the big speedup indicates that> -- CASE expr WHEN y then a WHEN z then b .> -- recalculates expr for each WHEN clause> 2023 select sum(case when srp like '%[49]9' then 1 else 0 end)> from sf[/color]I tried some variations of this, and indeed it seems that there is a costwhen the expression appears with several WITH clauses. I tried a variationof this, where I supplemented the test table with a char(2) column, so Icould factor out that the WITH clauses themselves were not the culprits.CREATE TABLE realthing (realta real NOT NULL,lasttwo char(2) NOT NULL)goINSERT realthing (realta, lasttwo)SELECT r, right(r, 2)FROM (SELECT r = convert(real, checksum(newid()))FROM Northwind..Orders aCROSS JOIN Northwind..Orders b) AS fgoDBCC DROPCLEANBUFFERSgoDECLARE @start datetimeSELECT @start = getdate()SELECT SUM(CASE right(realta, 2)WHEN '99' THEN 1WHEN '49' THEN 1WHEN '39' THEN 1ELSE 0 END)FROM realthingSELECT datediff(ms, @start, getdate()) -- 20766 ms.goDBCC DROPCLEANBUFFERSgoDECLARE @start datetimeSELECT @start = getdate()SELECT SUM(CASE WHEN right(realta, 2) LIKE '[349]9' THEN 1 ELSE 0 END)FROM realthingSELECT datediff(ms, @start, getdate()) -- 8406 ms.goDBCC DROPCLEANBUFFERSgoDECLARE @start datetimeSELECT @start = getdate()SELECT SUM(CASE lasttwoWHEN '99' THEN 1WHEN '49' THEN 1WHEN '39' THEN 1ELSE 0 END)FROM realthingSELECT datediff(ms, @start, getdate()) -- 920 ms.goDBCC DROPCLEANBUFFERSgoDECLARE @start datetimeSELECT @start = getdate()SELECT SUM(CASE WHEN lasttwo LIKE '[349]9' THEN 1 ELSE 0 END)FROM realthingSELECT datediff(ms, @start, getdate()) -- 1466 ms.Thus, when using the char(2) column LIKE is slower despite that thereis only one WHEN condition. So indeed it seems that right(realta, 2)is computed thrice in the first test.Another funny thing is the actual results from the queries - they aredifferent. When I ran:select count(*) from realthing where lasttwo <> right(realta, 2)The result was about half of the size of realthing! I can't see thatthis difference affects the results though.Now, your article had a lot more tests, but I have to confess thatyou lost me quite early, because you never discussed what is theactual problem. Since you are working with floating-poiont numbersthere is a great risk that different methods not only has differentexecution times, but also gives different results.--Erland Sommarskog, SQL Server MVP, Join Bytes!Books Online for SQL Server SP3 athttp://www.microsoft.com/sql/techin.../2000/books.asp
hi , I am using ASP.NET with SQL Server. I have a function ABC() which creates,open ,and then dispose sqlserver's connection . I am using ABC() twice in one .aspx page ,my friend who build this function argued that this function will open only one connection in everypage ,no matter how many times we call function ABC() in a single page ,while i denies . Please tell me ,because our whole company database acces relies on this single ABC() function.
Hello everybody,i have a advance question about a specific sql problem:My table A have for example 3 columns.in the third column are words seperated by ~.ID COL2 COL3--------------1 ab test~dummy~ddd2 cd testdata2~sjhfdg~sdf3 ef sd~test4 gh sd~cvNow i want two lists:1.) used Values for column 3:Values------testdummydddtestdata2sjhfdgsdfsdcv2.) used values plus IDValue ID----------test 1test 3sd 3sd 4cv 4dummy 1....Is it posible to produce such a list with nearly one SQL -Statement or withtemporaly tables ?Thanks in advanceT.KindermannDatabase Administrator----------------------------------------------------------------------------Thomas KindermannE-MAIL: Reply to TKINDER<x>@GMX.DE without <x>
Hi,I'm wondering if there's any advance order by 'function'/workaround,which is reasonably efficient for MS SQL Server, without resorting tosome third party indexing/search engine to achieve the following.The mechanism is to record each instance of a pattern match and orderby rows with most matches first (DESC). Simplistic match but that's aseparate issue.Sample:create table tmp (col varchar(50));insert into tmpvalues ('a barking dog');insert into tmpvalues ('a dog and cat fights over dog food');insert into tmpvalues ('lovable dog is not barking dog=nice dog');The goal for the Sample is to return resultsets in the followingorder:lovable dog is not barking dog=nice dog -- 3 matchesa dog and cat fights over dog food -- 2 matchesa barking dog -- 1 matchThanks.
Hello I am working on a sql express table and while configuring the steps after I select the data source and the selectment statement window shows, I want to use the advanced tab but it is greyed out. I want to be able to add edit and delete my data. I have administrator rights for this project and the workstation so thats not the issue. What I am tryng to accomplish is extending a website to manage it's content and users. Also the table has colums and the colums has test data within them I tested a query and the connection had a successful return. Maybe it's a configuration thing I am unaware of.
Does SQL Express Advanced allow remote connections or do I have to have SQL Express installed on each computer and attach the MDF in order for other people to use a database.
I have a database that is not large but the JET engine in Access runs too slow for the data that I have. The SQL Express engine is more robust and returns the queries much faster than Access, especially when I am dealing with tables that contain a million records. I don't want to get into using the resources of a server for this task. I installed SQL Express Adv and would like to use my computer as host for the database but I cannot connect to the instance even when I open the computer, grant permission etc...
I know that SQL Express typically does not allow remotes connections but I thought maybe the advance version may. Your assistance is greatly appreciated.
I have a prob with the following code... in that the value of the amount of the year 2001 is 0 so it is showing me division by zero prob can any one change the code and let me know the details
declare @year1 int declare @year2 int declare @month int
set @year1 = 2002 set @year2 = 2001 set @month =9
select case when sum(case WHEN a.oper_year = @year1 THEN a.amount else 0 end) = sum(case WHEN a.oper_year = @year2 THEN a.amount else 0 end) then 0 else ( (sum(case WHEN a.oper_year = @year1 THEN a.amount else 0 end) - sum(case WHEN a.oper_year = @year2 THEN a.amount else 0 end))/ sum(case WHEN a.oper_year = @year2 THEN a.amount else 0 end) )* 100 end as Percentage, from oper_sundata a, oper_type_new b where a.site_id = b.sun_site
Hi Any help with this would be greatly appreciated. I have two tables First Table is called "Team" see columns and data below TeamId, TeamName, MemberId 1, White Team, 1 2, Grey Team, Null
Second Table is called "Members" see columns and data below MemberId, Name 1, Jim Smith
I want to display both tables in a gridview as follows TeamId, TeamName, MemberId, Name 1, White Team, 1, Jim Smith 2, White Team , Null, Null
I'm using the following sql procedure to do this
Select Team.TeamId, Team.TeamName, Team.MemberId, Meember.Name From Team Inner Join Members on Members.MemberId = Team.MemberId
My Problem is that this select statement returns the first row but not the second row. The reason for this is the second row's memberId is Null. However, I still need to display this row even if the data is some of the data is null. Can anyone point out the correct sql statement for this?
What is the purpose of the "N" preceding the parameter values in theSQL examples?Here is an example copied from Books Online, SP_ATTACH_DB:EXEC sp_attach_db @dbname = N'pubs',@filename1 = N'c:Program FilesMicrosoft SQLServerMSSQLDatapubs.mdf',@filename2 = N'c:Program FilesMicrosoft SQLServerMSSQLDatapubs_log.ldf'I've found that my sp_attach_db routine works without the "N", but Ineed to know what it is that I don't know.Thank you everybody for all your help.
Where can i find examples of T-SQL executed by one instance of SQL Server 2000 to communicate to a 2005 SQL Broker instance (both located on the same server)?