Re: [PHP-DB] Creating temp tables in MSSQL
Hi John, you need to specify a name for the column that you create with the convert function. Like this select distinct convert(varchar(36), amount) id into #temp1 from fred #temp1 will now have one column called id. #temp1 can be removed with droptable #temp1 or by closing the connection to the database. - Frank Thanks for your response Frank, but this is still driving me nuts and making me look like a moron at the same time (-: This simplified query: $query = SELECT distinct convert(varchar(36),a.traineeID) INTO #tempdata1; $query .= FROM tblSignIn_trainee a; $queryresult = MSSQL_QUERY($query,$cn) or die (Error in query: $query. .mssql_get_last_message()); does not work. The databases connection which I am using connects with db_datareader rights to SQL Server - from what I understand no special rights are needed to create a temporary table. Using mssql_get_last_message() gives me the message:Changed database context to 'thedatabasenameisprintedhere'. What the heck is going on here? Frank M. Kromann wrote: Hi, Any table that starts with # is a temp table. You can use create table #mytemp (...) or you should be able to use the query you suggest. You just have to make sure you have enough space in tempdb (a system database) - Frank Hi all, I've been handed a query which I'm trying to get to work. I'm using PHP to talk to MSSQL 7. My question is this: Can I create temp tables with mssql without executing the code in a stored procedure? The simplified version of the query is as follows: $query = SELECT distinct convert(varchar(36),traineeID); $query.= INTO #tempdata1; $query.= FROMtblSignIn_trainee a; Later on I could make a stored procedure, but at the moment I would like to just work with the query directly in the code if at all possible. Thanks, John -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Creating temp tables in MSSQL
Hi Matt, Tables in tempdb are deleted when the connection is closed or when the user deletes them with drop table #tempname. - Frank Hi Frank. When does this table get deleted? when the DB connection closes or does one have to run a process to clear the temp tables? Thanks, Matt On Tue, 2003-12-16 at 17:29, Frank M. Kromann wrote: Hi, Any table that starts with # is a temp table. You can use create table #mytemp (...) or you should be able to use the query you suggest. You just have to make sure you have enough space in tempdb (a system database) - Frank Hi all, I've been handed a query which I'm trying to get to work. I'm using PHP to talk to MSSQL 7. My question is this: Can I create temp tables with mssql without executing the code in a stored procedure? The simplified version of the query is as follows: $query = SELECT distinct convert(varchar(36),traineeID); $query.= INTO #tempdata1; $query.= FROMtblSignIn_trainee a; Later on I could make a stored procedure, but at the moment I would like to just work with the query directly in the code if at all possible. Thanks, John -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Matthew Vos Systems Analyst Product Development Techcom Software Solutions Inc. 416.398.5764 [EMAIL PROTECTED] * * * * * * * * * * This message contains information from Techcom Software Solutions Inc. which is confidential and privileged. If you are not an intended recipient, please refrain from any disclosure, copying, distribution or use of this information and note that such actions are prohibited. If you have received this transmission in error, please notify [EMAIL PROTECTED] This message and any attachments have been scanned for viruses. This message has been digitally signed to ensure authenticity. Key ID: CFEA28EC Fingerprint: 009F E13D 0450 55AF DC1C 4D77 1FE9 0DBB CFEA 28EC -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Creating temp tables in MSSQL
Hi Frank. When does this table get deleted? when the DB connection closes or does one have to run a process to clear the temp tables? Thanks, Matt On Tue, 2003-12-16 at 17:29, Frank M. Kromann wrote: Hi, Any table that starts with # is a temp table. You can use create table #mytemp (...) or you should be able to use the query you suggest. You just have to make sure you have enough space in tempdb (a system database) - Frank Hi all, I've been handed a query which I'm trying to get to work. I'm using PHP to talk to MSSQL 7. My question is this: Can I create temp tables with mssql without executing the code in a stored procedure? The simplified version of the query is as follows: $query = SELECT distinct convert(varchar(36),traineeID); $query.= INTO #tempdata1; $query.= FROMtblSignIn_trainee a; Later on I could make a stored procedure, but at the moment I would like to just work with the query directly in the code if at all possible. Thanks, John -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Matthew Vos Systems Analyst Product Development Techcom Software Solutions Inc. 416.398.5764 [EMAIL PROTECTED] * * * * * * * * * * This message contains information from Techcom Software Solutions Inc. which is confidential and privileged. If you are not an intended recipient, please refrain from any disclosure, copying, distribution or use of this information and note that such actions are prohibited. If you have received this transmission in error, please notify [EMAIL PROTECTED]. This message and any attachments have been scanned for viruses. This message has been digitally signed to ensure authenticity. Key ID: CFEA28EC Fingerprint: 009F E13D 0450 55AF DC1C 4D77 1FE9 0DBB CFEA 28EC signature.asc Description: This is a digitally signed message part
Re: [PHP-DB] Creating temp tables in MSSQL
Thanks for your response Frank, but this is still driving me nuts and making me look like a moron at the same time (-: This simplified query: $query = SELECT distinct convert(varchar(36),a.traineeID) INTO #tempdata1; $query .= FROM tblSignIn_trainee a; $queryresult = MSSQL_QUERY($query,$cn) or die (Error in query: $query. .mssql_get_last_message()); does not work. The databases connection which I am using connects with db_datareader rights to SQL Server - from what I understand no special rights are needed to create a temporary table. Using mssql_get_last_message() gives me the message:Changed database context to 'thedatabasenameisprintedhere'. What the heck is going on here? Frank M. Kromann wrote: Hi, Any table that starts with # is a temp table. You can use create table #mytemp (...) or you should be able to use the query you suggest. You just have to make sure you have enough space in tempdb (a system database) - Frank Hi all, I've been handed a query which I'm trying to get to work. I'm using PHP to talk to MSSQL 7. My question is this: Can I create temp tables with mssql without executing the code in a stored procedure? The simplified version of the query is as follows: $query = SELECT distinct convert(varchar(36),traineeID); $query.= INTO #tempdata1; $query.= FROMtblSignIn_trainee a; Later on I could make a stored procedure, but at the moment I would like to just work with the query directly in the code if at all possible. Thanks, John -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Creating temp tables in MSSQL
Hi all, I've been handed a query which I'm trying to get to work. I'm using PHP to talk to MSSQL 7. My question is this: Can I create temp tables with mssql without executing the code in a stored procedure? The simplified version of the query is as follows: $query = SELECT distinct convert(varchar(36),traineeID); $query.= INTO #tempdata1; $query.= FROMtblSignIn_trainee a; Later on I could make a stored procedure, but at the moment I would like to just work with the query directly in the code if at all possible. Thanks, John -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Creating temp tables in MSSQL
Hi, Any table that starts with # is a temp table. You can use create table #mytemp (...) or you should be able to use the query you suggest. You just have to make sure you have enough space in tempdb (a system database) - Frank Hi all, I've been handed a query which I'm trying to get to work. I'm using PHP to talk to MSSQL 7. My question is this: Can I create temp tables with mssql without executing the code in a stored procedure? The simplified version of the query is as follows: $query = SELECT distinct convert(varchar(36),traineeID); $query.= INTO #tempdata1; $query.= FROMtblSignIn_trainee a; Later on I could make a stored procedure, but at the moment I would like to just work with the query directly in the code if at all possible. Thanks, John -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
