MSSQL Query Help
Dec 9, 2004
Having problem completing this query. I have a list of items. Some items need to be grouped by a list, some by a range. I was thinking of useing two tables, one for the items, and one for the groups. The groups would have something like groupid, title, listnumbers, rangelow, and rangehigh. The tables are in sql database. For example:
The list of items numbers are 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20. In the group table, it would be listed like this:
G1, Group1, 1,,
G1, Group1, 6,,
G2, Group2, ,2,5
G3, Group3,6,18,20
G3, Group3,7,15,17
G3, Group3,8,14,16
G3, Group3,9,11,13
In this example, Group1 is a list, group2 is a range, and group3 is a list of ranges. I can make a query that pulls all the items just in the groups:
SELECT ECC_ITEMS.NBR, Group.Group_Name, Group.Title, ECC_ITEMS.DESCR, ECC_ITEMS.REG_PRC
FROM Group, ECC_ITEMS
WHERE ECC_ITEMS.NBR Between Group.RangeLow And Group.RangeHigh Or ECC_ITEMS.NBR=Group.GroupItems
Now, I am not sure how to put the rest of the items (the ones that aren't in a group) in that query. I was thinking on making a union and the second query being a unmatched query. Not sure how to make it were that query is "unmatched" with a table in the same query. And ideas on how to make the second part of the union query?
View 3 Replies
ADVERTISEMENT
Jan 18, 2008
in mysql, we can have
select * from test where date like '%-02-01'
all result that ends with -02-01 will be displayed.
however, if i want to do it in ms sql, may i know what's the syntax for this select....like ?
Thanks.
View 1 Replies
View Related
May 30, 2008
Code:
Select id from tbl_account in Game_User DB
id is binary data
USE Billing
INSERT INTO tblUser (userId,cpId,userTypeId,userStatusId,gameServiceId) VALUES ('test','1','1','9','6') where userId = id from tbl_account
and
INSERT INTO tblUserInfo (userNumber,userId,cpId,userTypeId,userStatusId,gameServiceId) VALUES ('123','test','1','1','9','6') where userNumber = userNumber and userId = userId from tblUser
Im new when it comes to making SQL queries, so i need help badly.
Basically what i need the query to do is call from a DB "User" id where the id is in Binary data. from that i need it to insert into 2 other tables in the Billing DB.
the UserId needs to be the same in all areas, and when it inserts into tblUserInfo it has to pull from userNumber from tblUser after the rows are entered so that the userNumber in tblUser and tblUserInfo are the same.
Hopefully you can help. if any further information is needed please just ask and ill try to give as much as i know.
Thanks
David
View 2 Replies
View Related
May 7, 2007
i am familiar with php mySql combo, but from now on the boss wants me on MS SQL. i have a little query sender that used mySql and i tried to switch it over to use mssql_functions like:
php Code:
Original
- php Code
mssql_connect($host,$user,$password);
mssql_select_db($_POST['database']);
mssql_query($cxn,$_POST['query']);
mssql_num_rows($result) == 0;
mssql_connect($host,$user,$password);mssql_select_db($_POST['database']);mssql_query($cxn,$_POST['query']);mssql_num_rows($result) == 0;
When i run my script i get a blank screen. I am already bent out of shape trying to switch from mySql to this. any help would be appreciated. here is my code...
php Code:
Original
- php Code
<?php
/*Program: mssql_send.php
*Desc: PHP program that sends an SQL query to the
* MS SQL server and displays the results.
*/
echo "<html>
<head><title>MSSQL Query Sender</title></head>
<body>";
if(ini_get("magic_quotes_gpc") == "1")
{
$_POST['query'] = stripslashes($_POST['query']);
}
$host="yourhost";
$user="you";
$password="example";
/* Section that executes query and displays the results */
if(!empty($_POST['form']))
{
$cxn = mssql_connect($host,$user,$password);
mssql_select_db($_POST['database']);
$result = mssql_query($cxn,$_POST['query']);
echo "Database Selected: <b>{$_POST['database']}</b><br>
Query: <b>{$_POST['query']}</b>
<h3>Results</h3><hr>";
if($result == false)
{
echo "<h4>Error!</h4>";
}
elseif(mssql_num_rows($result) == 0)
{
echo "<h4>Query completed.
No results returned.</h4>";
}
else
{
/* Display results */
echo "<table border='1'><thead><tr>";
$finfo = mssql_fetch_field($result);
foreach($finfo as $field)
{
echo "<th>".$field->name."</th>";
}
echo "</tr></thead>
<tbody>";
for ($i=0;$i < mssql_num_rows($result);$i++)
{
echo "<tr>";
$row = mssql_fetch_row($result);
foreach($row as $value)
{
echo "<td>".$value."</td>";
}
echo "</tr>";
}
echo "</tbody></table>";
}
/* Display form with only buttons after results */
$query = str_replace("'","%&%",$_POST['query']);
echo "<hr><br>
<form action='{$_SERVER['PHP_SELF']}' method='POST'>
<input type='hidden' name='query' value='$query'>
<input type='hidden' name='database'
value={$_POST['database']}>
<input type='submit' name='queryButton'
value='New Query'>
<input type='submit' name='queryButton'
value='Edit Query'>
</form>";
exit();
}
/* Displays form for query input */
if (@$_POST['queryButton'] != "Edit Query")
{
$query = " ";
}
else
{
$query = str_replace("%&%","'",$_POST['query']);
}
?>
<form action="<?php echo $_SERVER['PHP_SELF'] ?>"
method="POST">
<table>
<tr><td style='text-align: right; font-weight: bold'>
Type in database name</td>
<td><input type="text" name="database"
value=<?php echo @$_POST['database'] ?> ></td>
</tr>
<tr><td style='text-align: right; font-weight: bold'
valign="top">Type in SQL query</td>
<td><textarea name="query" cols="60"
rows="10"><?php echo $query ?></textarea></td>
</tr>
<tr><td colspan="2" style='text-align: center'>
<input type="submit" value="Submit Query"></td>
</tr>
</table>
<input type="hidden" name="form" value="yes">
</form>
</body></html>
<?php/*Program: mssql_send.php *Desc: PHP program that sends an SQL query to the * MS SQL server and displays the results. */echo "<html> <head><title>MSSQL Query Sender</title></head> <body>";if(ini_get("magic_quotes_gpc") == "1"){ $_POST['query'] = stripslashes($_POST['query']);}$host="yourhost";$user="you";$password="example"; /* Section that executes query and displays the results */if(!empty($_POST['form'])){ $cxn = mssql_connect($host,$user,$password); mssql_select_db($_POST['database']); $result = mssql_query($cxn,$_POST['query']); echo "Database Selected: <b>{$_POST['database']}</b><br> Query: <b>{$_POST['query']}</b> <h3>Results</h3><hr>"; if($result == false) { echo "<h4>Error!</h4>"; } elseif(mssql_num_rows($result) == 0) { echo "<h4>Query completed. No results returned.</h4>"; } else { /* Display results */ echo "<table border='1'><thead><tr>"; $finfo = mssql_fetch_field($result); foreach($finfo as $field) { echo "<th>".$field->name."</th>"; } echo "</tr></thead> <tbody>"; for ($i=0;$i < mssql_num_rows($result);$i++) { echo "<tr>"; $row = mssql_fetch_row($result); foreach($row as $value) { echo "<td>".$value."</td>"; } echo "</tr>"; } echo "</tbody></table>"; } /* Display form with only buttons after results */ $query = str_replace("'","%&%",$_POST['query']); echo "<hr><br> <form action='{$_SERVER['PHP_SELF']}' method='POST'> <input type='hidden' name='query' value='$query'> <input type='hidden' name='database' value={$_POST['database']}> <input type='submit' name='queryButton' value='New Query'> <input type='submit' name='queryButton' value='Edit Query'> </form>"; exit();} /* Displays form for query input */if (@$_POST['queryButton'] != "Edit Query"){ $query = " ";}else{ $query = str_replace("%&%","'",$_POST['query']);}?><form action="<?php echo $_SERVER['PHP_SELF'] ?>" method="POST"><table> <tr><td style='text-align: right; font-weight: bold'> Type in database name</td> <td><input type="text" name="database" value=<?php echo @$_POST['database'] ?> ></td> </tr> <tr><td style='text-align: right; font-weight: bold' valign="top">Type in SQL query</td> <td><textarea name="query" cols="60" rows="10"><?php echo $query ?></textarea></td> </tr> <tr><td colspan="2" style='text-align: center'> <input type="submit" value="Submit Query"></td> </tr></table><input type="hidden" name="form" value="yes"></form> </body></html>
View 2 Replies
View Related
Sep 18, 2004
What would the MySQL equivalent for "SHOW TABLES" and "SHOW FIELDS" be in MSSQL?
View 8 Replies
View Related
Apr 18, 2004
Let's say that I have three tables:
Buyer
------
ID
Name
Adress
Session
-------
ID
Date
Pageviews
Buyer
Orders
-------
ID
DatePaid
Session
Now, I've been racking my brain on how to list the Buyers and the number of related rows in the Orders table. Add to this that I only want to count the Orders where DatePaid IS NOT NULL.
Any help would be enourmously appriceated.
View 3 Replies
View Related
May 2, 2007
I have this database running (ignore that that was done in Access, this is being made in Microsoft SQL Server 2005).
What I need to do is if you look at the tbl_events table and the tbl_timekeeperDetails table I need to make a query that:
Lists the names of all timekeepers (whether they are booked for a meeting or not), and the meetings at which they are timekeeping.
The tricky part of this is getting the query to show the timekeepers who aren't assigned to an event.
I have two seperate querys so far, but I'm presuming there must be a way of merging them or something.
I have this code so far:
select timekeeperTitle,timekeeperNameFirst,timekeeperNameLast,eventID
from timekeeperDetails,events
where timekeeperDetails.timekeeperID = events.timekeeperID
select timekeeperTitle,timekeeperNameFirst,timekeeperNameLast
from timekeeperDetails
If anyone has any ideas, please do post a reply or email me at paul [at] abscond [dot] org
It would be very much appreciated.
View 3 Replies
View Related
Jul 22, 2004
im managing most queries without any problems (im converting from access to mssql) but this one is causing me grief - how do i put this into mssql ?
SELECT dbo_Personal.ID, dbo_Personal.Surname1, dbo_Lead.SourceOfLead, dbo_Lead.DateOfLead, dbo_Mortgage.MortgageAppSubmitted, dbo_Mortgage.MortgageOfferedAccepted, dbo_Mortgage.MortgageDrawndown, dbo_Mortgage.MortgageApplicationClosed,
[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissionAnticipated]+[dbo_BuildingsAndContents.BandCCommissionAnticipated]+[dbo_OtherBusiness.OtherBusinessCommissionAnticipated] AS Expr1,
[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCommissionReceived]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions.OtherBusinessCommissionReceived] AS Expr2
, IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,[Expr1]*0.5)) AS Expr3
, IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,[Expr2]*0.5)) AS Expr4
FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID = dbo_Lead.ID) LEFT JOIN dbo_Mortgage ON dbo_Personal.ID = dbo_Mortgage.ID) LEFT JOIN dbo_OtherBusiness ON dbo_Personal.ID = dbo_OtherBusiness.ID) LEFT JOIN dbo_BuildingsAndContents ON dbo_Personal.ID = dbo_BuildingsAndContents.ID) LEFT JOIN dbo_Commissions ON dbo_Personal.ID = dbo_Commissions.ID) LEFT JOIN dbo_Life ON dbo_Personal.ID = dbo_Life.ID
WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));
View 9 Replies
View Related
Oct 24, 2005
I run the following statement from an update query in access but I can't find the way to run this same query in MSSQL. Please give me some ideas how to modify and run this in MSSQL.
Thank you
"UPDATE DISTINCTROW ZipToTerr, leadsUS SET leadsUS.Terr = [ZipToTerr]![TerrNum] WHERE ((([ZipToTerr].[BU]='W') AND (([ZipToTerr].[ZipFrom])<=[zip]) And (([ZipToTerr].[ZipTo])>=[zip])) And (([leadsUS].[terr]) = 1 ));"
View 8 Replies
View Related
Jan 18, 2007
Hi
I have a mysql query in my php script like
UNIX_TIMESTAMP() - UNIX_TIMESTAMP(sessioncreated) as sessionspan .
What is the equivalent of above query in mssql. I need the same query in mssql. Is there any function that does the same action in mssql2000.
Thanks in Advance.
stranger
View 3 Replies
View Related
Mar 21, 2007
I have 3 tables, that appear as follows (insignificant fields are not mentioned for brevity):
RETAIL(code, CurrentLocation) ~ 2.6 million records
LOCAUDIT(code, Date, Time, Location) ~ 3.6 million records
STAFF(ID, NAME) ~ 40K records
Each record in the RETAIL table represents a document. The LOCAUDIT table maintains history information for documents: locations they've been to. A location can be represented by a staff (from STAFF table), or an unlimited range of different names - not enumerated in a table.
The query we run tries to find the currentlocation for each document in the RETAIL table (if any). Since a document may have been to many location, I'm interested in the last location which has the max Date,Time.
To perform the query, I created two views:
HISTORY
=======
CREATE VIEW HISTORY
AS
SELECT CODE, "DATE", TIME, CAST("DATE" + ' ' + TIME AS datetime) AS UpdateDateTime, LOCATION
FROM LOCAUDIT
LASTHISTORY
==========
CREATE VIEW LASTHISTORY
AS
SELECT CODE, Max(UpdateDateTime) AS LastUpdated
FROM HISTORY
GROUP BY CODE
UPDATE RETAIL
SET CURRENTLOCATION = (CASE WHEN t3.NAME IS NULL THEN t2.LOCATION ELSE t3.NAME END)
FROM RETAIL AS t4
LEFT JOIN LASTHISTORY AS t1 ON (t4.CODE = t1.CODE)
LEFT JOIN HISTORY AS t2 ON (t1.ITEM = t2.ITEM AND t1.LastUpdated = t2.UpdateDateTime)
LEFT JOIN STAFF AS t3 ON (t2.LOCATION = t3.ID)
What the query does is update the current location of each document. If the current location is a staff, we find the name of the staff member (hence the case).
In addition to clustered indexes on the primary keys, I've also created an index on (Code, Date, Time) on LOCAUDIT.
However, the query still seems to take up to 3 hours sometimes to run on a server with 4 CPU's and a whole bunch of memory. Can anyone suggest some way to improve this, add more effective indexes, or rewrite the queries all together. Any help is appreciated..
View 4 Replies
View Related
Feb 17, 2007
I need to use mssql to create a ranking of some kind. This is the situation:
I need to assign position to a list of students based on thier scores. e.g
Student Score Position
StudentA 56 4
StudentB 78 1
StudentC 66 2
StudentD 56 4
I need to create the positions based on the scores of the ctudents.
I will appreciate any assistance.
Thank you.
View 3 Replies
View Related
Feb 21, 2008
Heya, I'm stuck on this query, we are currently moving from mysql to mssql, and this one has stumped me.
I used to use CONCAT, and i thought i'd found the correct syntax for MSSQL but it fails when used.
Code:
$sql = " SELECT *
FROM ol_user_2_group ug
INNER JOIN ol_users_details ud
ON ug.user_id = ud.user_id
INNER JOIN ol_user_rank ur
ON ug.user_id = ur.user_id
WHERE ug.group_id= ".$group_id."
AND ud.firstname + ' ' + ud.lastname AS fullname LIKE '%".$search."%'";
ud.firstname + ' ' + ud.lastname AS fullname LIKE '%".$search."%'";
is the part that fails with
Code:
'Incorrect syntax near the keyword 'AS'.
can anyone tell me what im doing wrong?
thanks in advance
View 4 Replies
View Related
Mar 21, 2006
Hi,
I got a big problem. I try to run a query on ms query analyzer or my .net-app, but in both cause, it wouldn't work. when i run the query in ms query analyzer it returns me this error-message:
Der aktuelle Zeilenwert der [verkauf_online]..[hs].[std_ftext].text-Spalte konnte nicht vom OLE DB-Provider 'MSDASQL' gelesen werden.
[OLE/DB provider returned message: Die angeforderte Konvertierung wird nicht unterstützt.]
OLE DB-Fehlertrace [OLE/DB Provider 'MSDASQL' IRowset::GetData returned 0x80040e1d].
sorry, its in german, i know but the most important fact is, that a conversion failed. i tried to read a text-field from a sybase asa-8-database which is linked with a ms sql-server 2000. I tried to find out more about the error-messe an the error-code (x80040e1d) but i couldn't find anything helpful.
Now, I just hope some of you can maybe help me.
View 2 Replies
View Related
Dec 14, 2006
Is there a way to do a SELECT TOP # using a variable for the #?
In other words I'm doing a SELECT TOP 50* FROM DATATABLE
If I pass an @value for the number
SELECT TOP @value* FROM DATATABLE doesn't work
I am generating a random sampling of data and I want to allow the user to select the number of results they choose to have.
Thanks in advance.
View 2 Replies
View Related
May 22, 2008
hilet me explain my need..following are the tables im using..tbl_company (company table - parent) id company 1 test 2 test123 tbl_dept (department table - master) id dept 1 dept1 2 dept2tbl_compdept (company departments table - child) cmpid deptid 1 1 2 1 2 2 wats my need is.. while the company is listing..by query using joins, result was like this..company depttest dept1test123 dept1test123 dept2i need company test123 should be listd only once..when i use group by or distinct means, all r listed..is there any way to filter out therepeating company list by just listing the company list only once..
View 5 Replies
View Related
Jan 8, 2007
Hi,
How can you get the Query Statement of the last executed SQL command.
I am not quite sure but I do remember coming across such a command ( maybe an undocumented one).
Thanks for your help and pointers.
My Best wishes for the new year to all the folks in the forum. Wishing you greater days ahead.
Warm Regards,
Ranjit S Hans.
---------------------------------------------------------------------
Everywhere is a walking distance if you have the time - Steven Wright
View 4 Replies
View Related
Mar 3, 2006
Here is our problem...We are doing a lot of selects against a table that has one large fieldin it.If we do a select against all the fields except for description, thequery comes back relatively quickly. If we add that last field (768chars) to the query, our query takes 10x longer (5 seconds vs 56seconds.)When we run the one without the description column, we can watchperfmon and see a very quick spike to physical disk. If we add in thedescription field we can see that the server becomes I/O bound - thedisk sits at 100% until the query is complete.We have tweaked the min query memory setting for the server but itseems to have had no effect no matter how high we set it. Is theresome point at which MSSQL decides it cannot perform the transaction inmemory? What would I increase to cure this problem?For example:TMZDIFF int410WRITETIMEcharno 16System_Namecharno 64Timestampcharno16Name charno32Mount_Pointcharno32Size intno4 10Space_Usedintno410Space_Availableintno410Inode_Sizeintno410Inodes_Usedintno410Inodes_Freeintno410Space_Used_Percentintno410Inodes_Used_Percentintno410FS_Type charno8Space_Available_Percentintno410Name_U ncharno32Descriptionncharno768
View 2 Replies
View Related
Jul 23, 2005
Hi all,is there any tool which is capable to convert query initially written formsaccess database to query for mssql server.I have tons of queries which contains iif, trim and similar functions whichshould be converted to case, ltrim(rtrim(, etc. etc.Does anyone know for tool which could do that automatically?Thanks in advance,Anabella
View 1 Replies
View Related
Aug 11, 2004
How many result-rows does mssql return should be used asynchronous method to use mssql cursor, can get the best performance in any time in any result offset?
i want to make the cursor fast in any time whatever how many results returned
View 2 Replies
View Related
Jul 24, 2007
Hello all,
I will try to supply all of the information available to me to help generate some answers to the question I have. I am doing some benchmark testing of an application of how it deals with various database queries, for example oracle 10g, DB2 and MSSQL 2005.
I have a very simple database called "student" that has various tables, with various columns, all as mentioned very simplistic. Each table has a few hundred rows of data, the most being one table with 2000 rows. The same database (structure and dataset), is replicated across all 3 DB's.
In order to do the testing, I have been recording the same queries (select * from table_a, select * from table_b etc etc) for each database at an interface which monitors traffic between the client and db server, the problem starts here; The size of the tcpdumps are vastly greater for MSSQL vs oracle or db2. Some examples:
100 query or transaction dump:
mssql=3.7mb
oracl10g=133kb
50,000 query or transaction dump:
mssql=1.8gb
oracl10g=0.6gb
db2=0.6gb
as mentioned each database's tables and data are identical to my knowledge and all queries are the same. The bloated dumps are making mssql performance numbers look bad.
So my question is: What could be the reason for MSSQLs client/server queries to contain so much information, has anyone else come across this? Is there any setting or something I could try to minimize it?
Thank you for your time.
View 2 Replies
View Related
Feb 10, 2008
Hi,
i was planning to create a database migration tool ..
its a certain database of a DMS (document management system) to
another DMS (two different DMS)... from DMS using msde 2000 server .. and tranfer to a DMS using a postgre sql or mssql .. depends ..
they have different table structures and names . . :D
i was thing of what language shall i use.. or what language is the best to work on this kind of project :)
hoping for your kind help guys. thanks :)
br
Frozenice
View 1 Replies
View Related
Sep 6, 2006
Does enabling/disabling Data Execution Prevention have a performanceimpact on SQL 2000 or SQL 2005?For SQL best performance - how should I configure for:Processor Scheduling:Programs or Background servicesMemory Usage:Programs or System Cache
View 9 Replies
View Related
Mar 24, 2008
Hi,
I am a bit new to the MSSQL server. In our application, we use so many SQL queries. To imporve the performance, we used the Database enigine Tuning tool to create the indexes. The older version of the application supports MSSQL 2000 also. To re-create these new indexes, I have an issue in running these "CREATE INDEX" commands as the statements generated for index creation are done in MSSQL 2005. The statements include "INCLUDES" keyword which is supported in MSSQL 2005 but not in MSSQL 2000.
Ex:-
CREATE INDEX IND_001_PPM_PA ON PPM_PROCESS_ACTIVITY
(ACTIVITY_NAME ASC, PROCESS_NAME ASC, START_TIME ASC, ISMONITORED ASC)
INCLUDE
(INSTANCE_ID, ACTIVITY_TYPE, STATUS, END_TIME, ORGANIZATION);
Any help in creating such indexes in 2000 version is welcome.
Thanks,
Suresh.
View 2 Replies
View Related
May 3, 2008
Hello
We are using SQL 2005 and now we are planning to use SQL 2000. what are the ways to do the process.
We taken the script spcificall for 2000 and run it in SQL 200. But we are getting the error in SCRIPT?
Could you please give me the step to do?
Thanks,
Sankar R
View 6 Replies
View Related
Jan 7, 2007
Environment:Server1 (Local)OS Windows 2000 ServerSQL Server 2000Server2 (Remote)OS Windows 2003 ServerSQL Server 2000(Both with most recent service packs)Using Enterprise Manager, we have set up the Link Server (LINK_A) inthe Local Server 1 to connect to Server 2.The SQL we need to run is the following:INSERT INTO table1(column1,column2)SELECT A.column1, A.column2FROM LINK_A.catalog_name.dbo.table2 AS AWHERE A.column1 xxxx;When we run this from the Query Analyzer, it completes with no problemsin a few seconds.Our problem:When we add the DTS Package as the ActiveX Script (VB Script) to theLocal Package, it times out at "obj_Conn.Execute str_Sql"Dim Sql, obj_ConnSet obj_Conn = CreateObject("ADODB.Connection")obj_Conn.Open XXXXobj_Conn.BeginTransstr_Sql = "INSERT INTO table1("str_Sql = str_Sql & "column1"str_Sql = str_Sql & ", column2"str_Sql = str_Sql & ")"str_Sql = str_Sql & " SELECT Ａ.column1"str_Sql = str_Sql & ", A.column2"str_Sql = str_Sql & " FROM LINK_A.catalog_name.dbo.table2 AS A"str_Sql = str_Sql & " WHERE A.column1 0"str_Sql = str_Sql & ";"obj_Conn.Execute str_Sql----------------------------------------------------------When we make a Stored Procedure and run the following SQL, it freezes.INSERT INTO table1(column1,column2)SELECT A.column1, A.column2FROM LINK_A.catalog_name.dbo.table2 AS AWHERE A.column1 xxxxWe've also tried the following with the same results;INSERT INTO table1(column1,column2)SELECT A.column1, A.column2FROM [LINK_A].[catalog_name].[dbo].[table2] AS AWHERE A.column1 xxxxThe same thing happens when we try to run the "SELECT" by itself.SELECT TOP 1 @test=A.column1FROM LINK_A.catalog_name.dbo.table2 AS AWHERE A.column1 xxxxORDER BY A.column1What is going wrong here, and how do we need to change this so that itruns without timing out or freezing?
View 2 Replies
View Related
Oct 26, 1999
Hello:
I am currently work on mssql 6.5. On my workstation, I have mssql 6.5
cient software.
However, I would like to install mssql 7.0 server on my nt workstation
and work with it to become familiar with 7.0. Can I install mssql 7.0
server on my nt workstation? Can mssql 6.5 client coexist with mssql 7.0
on the same machine if they are in different directories?
Thanks.
David Spaisman
View 1 Replies
View Related
Jun 15, 2006
Ben writes "I have a sql script that doesn't function very well when it's executed on a SQL 2000 server.
The scrpt looks like this:
---------------------------------------------------------------------------------------------------
USE [master]
GO
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME')
EXEC sp_addlogin N'SSDBUSERNAME', N'SSDBPASSWORD'
GO
GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME]
GO
GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME]
GO
GRANT CONNECT SQL TO [SSDBUSERNAME]
GO
GRANT CONTROL SERVER TO [SSDBUSERNAME]
GO
GRANT CREATE ANY DATABASE TO [SSDBUSERNAME]
GO
USE [master]
GO
If EXISTS (Select * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME')
ALTER LOGIN [SSDBUSERNAME] WITH PASSWORD=N'SSDBPASSWORD'
GO
GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME]
GO
GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME]
GO
GRANT CONNECT SQL TO [SSDBUSERNAME]
GO
GRANT CONTROL SERVER TO [SSDBUSERNAME]
GO
GRANT CREATE ANY DATABASE TO [SSDBUSERNAME]
GO
USE [master]
GO
IF EXISTS (select * from dbo.sysdatabases where name = 'ISIZ')
DROP DATABASE [ISIZ]
GO
USE [SurveyData]
GO
exec sp_adduser 'SSDBUSERNAME'
GRANT INSERT, UPDATE, SELECT, DELETE
TO SSDBUSERNAME
GO
USE [SurveyManagement]
GO
exec sp_adduser 'SSDBUSERNAME'
GRANT INSERT, UPDATE, SELECT, DELETE
TO SSDBUSERNAME
---------------------------------------------------------------
I need to be converted to a script that can be executed on both MSSQL 2000 and MSSQL 2005.
I was wondering if somebody there could help me with this problem?!
Thanks,
Ben"
View 1 Replies
View Related
Nov 17, 2007
I've been tasked to move our production databases on MSSQL 2000 to 2005. I've supported MSSQL since version 6.5 and performed migrations to successor versions.
Current Environment is MSSQL 2000 32-bit with current Service Packs.
I've performed mock migrations on Test servers upgrading all Production instances simultaneously from MSSQL 2000 to 2005 32-bit. The Test environment is identical to Production minus server name, IP etc. Also I have a separate server with MSSQL 2005 installed where I use the DETACH / ATTACH and BACKUP / RESTORE method for migration / acceptance testing. There are approximately 30 databases totaling 70 GB. This has gone as expected and fairly successful. Vendors have been coordinated with to update code and staff for acceptance testing.
I'd prefer going directly to MSSQL 2005 64-bit instead if possible due to memory benefits etc. This is where I'd like some feedback prior to borrowing a 64-bit server for testing.
Upgrade options:
1. Is it better to migrate from MSSQL 2000 32-bit to 2005 64-bit via:
a. DETACH / ATTACH
b. BACKUP / RESTORE
c. Is one method more advantageous relating to the end result?
2. Regarding XP clients, have issues been experienced with the default SQL Server driver or is an alternate recommended for XP clients to connect to a MSSQL 64-bit server databases?
3. If you have performed this migration and have relevant experience please pass them along.
View 3 Replies
View Related
Sep 29, 2006
DB is developed on local computer with MSSQL 2005 Express. My host is on MSSQL 2005 workgroup. Are they compatible, because I am getting errors? Is my approach wrong?
I have tried several approaches.
A) I created a backup of database on my local, then placed a copy on the server. Then I tried to restore through Server Management Studio. I get this error.
TITLE: Microsoft SQL Server Management Studio
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database.
RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3169)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2039&EvtSrc=MSSQLServer&EvtID=3169&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
B: I also have tried copying the database. I put it in the same path as the other databases that can be read with server management studio on the server. Then, tried to get to it through server managements studio and it did not appear. So I tried to attach it. Then I received this error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Attach database failed for Server 'MROACH1'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Could not find row in sysindexes for database ID 10, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
Could not open new database 'LodgingDB'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 602)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=602&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
C: I have also tried opening the Database, and back up file through Server Management Studio. without success.
D: I also tried Windows and Software update at microsoft update, but no updates were recommended for Version on Server.
I'm surprised this is so hard. My original data base was created in same family of software. 2005 MS SQL Express. I could use some direct help from someone experienced with this. Am I doing it wrong or are the DB versions incompatible.
Mark Roach
View 5 Replies
View Related
Jan 18, 2008
How to convert a database in MSSQL 2000 to MSSQL 2005 database.Is there any tool or documentation available for this?
View 3 Replies
View Related
Sep 25, 2007
I have my MSSQL hosted, any interface i can use so i can't connect to the data source?
for Mysql, i know i can use phpmyadmin, or mysql interface.
i have no idea for MSSQL, any advices?
i know there is one, which is Toad, but it has a lots of limitations, any good idea?
thanks.
View 2 Replies
View Related
Feb 17, 2008
Hi,
till now i have used mysql with php.now i want to use mssql with php.my php and apache versions are
php 5.2.3
apache 2.2.4
from where i can get mssql and what i need to run my php scripts with mssql database.iam newbie in mssql.any of your help will be surely appreciated
thanks
mrjameer
View 2 Replies
View Related
