Export Sp_columns Result Into A Temp Table?

Mar 1, 2004

Can anyone show me the syntax for exporting the result of sp_columns into a new table?

I've tried using "SELECT EXEC sp_columns...INTO 'column_info'" but I keep getting syntax errors that are unclear to me.

Any help would be greatly appreciated.

ab

View 3 Replies


ADVERTISEMENT

Can You Caputure And Export Out Result Sets In Temp Tables???

Jan 8, 2001

I need to export out the data in a result set from temp tables. It is in a rather large Dts Stream. Thanks for your help. Brett

View 1 Replies View Related

Store Return Result From Sp_columns

Oct 31, 2007

I excute sp_columns in my Stored Procedure script to get the data type of a table column.
EXEC sp_columns @table_name = 'XXX', @column_name='YYY'
How do i store the column 'TYPE_NAME' in the return row into a variable so that i can use it later in my stored procedure?
Thanks
Hannah

View 1 Replies View Related

What's Wrong With #result Temp Table?

Dec 27, 2005

question: get 10 gram of the  gold  from Products? i cannot think a good algorithm,just use a clumsy proach to do this job ,but also failed,the last statement occur a Error "#result" have syntax error;
here my code:
--------test condition-------------Create table Qt(id int identity primary key,Q int)Goinsert into Qt(Q) values(8);insert into Qt(Q) values(6);insert into Qt(Q) values(6);insert into Qt(Q) values(5);insert into Qt(Q) values(3);insert into Qt(Q) values(3);insert into Qt(Q) values(1);insert into Qt(Q) values(0);Go-------------------------------
Create procedure Test2( @m int) ASdeclare @i int,@j int,@n int ,@id int ,@q int ,@last intset @i=1;set @j=1;Create table #result(id int, Q int)declare __cursor cursor  forselect * from Qt where Qt.Q <=@m order by Qt.q desc for read onlyOpen _cursor;select @n=@@cursor_rows;fetch last from _cursor into @lastif @n > 0 -------------------beginBegin_this:fetch absolute @i from __cursor into @id,@qif @q=@m begininsert into #result(id,Q) values(@id,@q);GoTo End_this;End-------------------else-------------------------------begin------------------------if @q=@lastbeginSet @j=@j+1;------------if @j>@n Goto End_this;elseBeginset @i=@j;Delete  from #result;End------------Endelsebegininsert into #result(id,Q) values(@id,@q);set @i=@i+1;end------------------------GoTo Begin_thisEnd-----------------------------
End_this:select id,Q from #result--close __cursor--deallocate __cursor
.....please help me, =A='
 

View 6 Replies View Related

How To Put The Sp_executesql Result In A Temp Table

Apr 13, 2001

I used sp_executesql to execute a dynamically built string containing an SQL statement, is there any way I can insert the results into a temporary table?

View 1 Replies View Related

Storing A Stored-proc's Result Into A Temp Table

Jul 20, 2005

I'm trying to write a SQL that stores a result from a stored-procedureinto a temporary table.Is there any way of doing this?

View 3 Replies View Related

Analysis :: Temp Table Like Structure In MDX To Process Intermediate Result

Aug 5, 2015

I have started working on SSAS since last week, I need to perform some calculations on the data fetched from the cube based on the parameters. SSRS is used to display the output and SSAS is used as a data source.

How i could perform the operations on the data fetched from the cube in SSAS? Does SSAS provides the storage structure like temp table in stored procedure where we can perform the various operations before sending final data back to the client side tool(SSRS)?

OR Is there any alternative way to perform the operations based on the input provided through the parameters

View 6 Replies View Related

Transact SQL :: How To Store Result Of Exec Command Into Temp Table

Apr 7, 2013

I wanted to insert the result-set of a Exec(@sqlcommand) into a temp table. I can do that by using:

Insert into #temp
Exec(@sqlcommand)

For this to accomplish we need to define the table structure in advance. But am preparing a dynamic-sql command and storing that in variable @sqlcommand and the output changes for each query execution. So my question is how to insert/capture the result-set of Exec(@sqlcommand) into a temp table when we don't know the table structure.

View 17 Replies View Related

How To Export A Temp Table To A Text File?

Apr 24, 2006

Hello everyone:

I create a temperal table to load data in a stored procedure. At last I want to export this temp table to a text file.

Any suggestion will be great appreciated.

ZYT

View 1 Replies View Related

Trying To Export A Global Temp Table Using SSIS.

Mar 1, 2006

Senerio:

 

I have to extract data from a read only table using a globel temp table then export it to another OLE DB connection or to a flat file. But in the new SSIS packages it does not allow you to do thisusing the global ## symbols in front of a table name. How do I get around this?

 

Thanks


 

View 6 Replies View Related

Problem In Executing Stored Procedure With Temp Table Returns Result Sets

Mar 23, 2006

Hi,
we are facing problem in executing a stored procedure from Java Session Bean,

coding is below.

pst = con.prepareStatement("EXEC testProcedure ?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
pst.setString(1, "IN");
//
rs = pst.executeQuery();
rs.last();
System.out.println(" Row Number "+rs.getRow());
rs.beforeFirst();
while(rs.next())
{
System.out.println(" Procedure is "+rs.getString(1));
}

same sp working perfectly with SQL Server 2000

am getting a error message

com.microsoft.sqlserver.jdbc.SQLServer
Exception: A server cursor cannot be opened on the given statement or statements
. Use a default result set or client cursor.



If a SP doesnt have a temp table, then there is no issue, SP executes perfectly, but if a SP has a temp table, this error occurs.

SP :

create proc testProcedure
@countrCode varchar(3)
as

select countryname INTO #TMPCOU from country where countryCode = @countrCode
SELECT COUNTRYNAME FROM #TMPCOU

Its really very urgent. Please help me...!

Rgds,

Venkatesh.

View 2 Replies View Related

Sp_columns In SQL2K5 Gives No Rows For Function That Return Table

May 9, 2006

Some automated tools use sp_columns to find out the columns for a table, view or UDF that returns table.

In SQL Server 2000 it gives columns back but in SQL Server 2005 it does not, compat level set at 80 and 90.

Does anyone have an idea what id going on here?

Repro script below. I expect the script to return information about the two columns in the table defined in fnTestColumnsFromFunctions().

if exists (select 1
from sysobjects
where id = object_id('dbo.fnTestColumnsFromFunctions')
and type in ('IF', 'FN', 'TF'))
drop function dbo.fnTestColumnsFromFunctions;
go

create function dbo.fnTestColumnsFromFunctions()
returns @TestTable table
(
ttID int,
ttName varchar(50)
)
as
begin
return;
end
go

declare @dbname sysname;
declare @n int;
set @dbname = db_name();
exec @n = dbo.sp_columns
@table_name = 'fnTestColumnsFromFunctions',
@table_owner = 'dbo',
@table_qualifier = @dbname,
@column_name = null,
@ODBCVer = 2;

if exists (select 1
from sysobjects
where id = object_id('dbo.fnTestColumnsFromFunctions')
and type in ('IF', 'FN', 'TF'))
drop function dbo.fnTestColumnsFromFunctions;
go

View 7 Replies View Related

T-SQL (SS2K8) :: Moving Values From Temp Table To Another Temp Table?

Apr 9, 2014

Below are my temp tables

--DROP TABLE #Base_Resource, #Resource, #Resource_Trans;
SELECT data.*
INTO #Base_Resource
FROM (
SELECT '11A','Samsung' UNION ALL

[Code] ....

I want to loop through the data from #Base_Resource and do the follwing logic.

1. get the Resourcekey from #Base_Resource and insert into #Resource table

2. Get the SCOPE_IDENTITY(),value and insert into to

#Resource_Trans table's column(StringId,value)

I am able to do this using while loop. Is there any way to avoid the while loop to make this work?

View 2 Replies View Related

Store The Stored Procedure Result In A Temp. Tabl

Mar 28, 2008

All,

I'm trying to store the results of my store procedure in a temp table. when I try it, I got the error saying...

"Insert exec cannot be nested"

I suspsect this is because I have a Insert Exec statement inside my stored procedure...

Is there any way to come over this issue ?

syntax of my code...

create table #Temp1 (ID int)

insert into #Temp1
EXEC SP1

when I try to run the above code I get the error "Insert exec cannot be nested"

SP syntax :

Create Procedure SP1
as
Begin
create table #Temp2
statements.....

Insert into #temp2
exec SP<Name>

staments.. cont...
END

View 1 Replies View Related

Export Result To The Excel

Nov 18, 2005

Hi,

I am a new in SQL Server.
I have a question.

I create the table called "tbl1"
I create a query called "qry1", this query retrieve the data from the "tbl1".

after running the query, and then convert the result to the Excel.

How can I do that?
Please let me know, thanks a lot. Thanks.

View 3 Replies View Related

Export A Query Result To Somewhere.

May 1, 2008

Suppose I want to query a table, how can I convert it into a word file table.
I want submit a report to my boss.

I am using SQL Server 2005 Management Studio Express.

Thanks

View 7 Replies View Related

Table-valued User-defined Function: Commands Completed Successfully, Where Is The Result? How Can I See Output Of The Result?

Dec 11, 2007

Hi all,

I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:

USE AdventureWorks;

GO

IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL

DROP FUNCTION dbo.ufnGetContactInformation;

GO

CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)

RETURNS @retContactInformation TABLE

(

-- Columns returned by the function

ContactID int PRIMARY KEY NOT NULL,

FirstName nvarchar(50) NULL,

LastName nvarchar(50) NULL,

JobTitle nvarchar(50) NULL,

ContactType nvarchar(50) NULL

)

AS

-- Returns the first name, last name, job title, and contact type for the specified contact.

BEGIN

DECLARE

@FirstName nvarchar(50),

@LastName nvarchar(50),

@JobTitle nvarchar(50),

@ContactType nvarchar(50);

-- Get common contact information

SELECT

@ContactID = ContactID,

@FirstName = FirstName,

@LastName = LastName

FROM Person.Contact

WHERE ContactID = @ContactID;

SELECT @JobTitle =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)

THEN (SELECT Title

FROM HumanResources.Employee

WHERE ContactID = @ContactID)

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE ContactID = @ContactID)

ELSE NULL

END;

SET @ContactType =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)

THEN 'Employee'

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN 'Vendor Contact'

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN 'Store Contact'

-- Check for individual consumer

WHEN EXISTS(SELECT * FROM Sales.Individual i

WHERE i.ContactID = @ContactID)

THEN 'Consumer'

END;

-- Return the information to the caller

IF @ContactID IS NOT NULL

BEGIN

INSERT @retContactInformation

SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;

END;

RETURN;

END;

GO

----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.

Thanks in advance,
Scott Chang

View 1 Replies View Related

Can I Export View Result To Excel ?

Jul 30, 2004

Good morning all,

I created a view that selects the rows of the records from a table that matches where condition.
I need to export the records to excel so I could send it out....
what is the best way to do?
Please tell me detail, I don't know much about SQL 2000, still learning.

Thank you,
Yanoroo

View 2 Replies View Related

SSIS EXPORT TO EXCEL DIFFERENT RESULT THAN DTS EXP

Jun 12, 2008

have now spent 3 days on finding a solution for this problem.

I export data from a table with a money data type column to excel. Before I do the export I'm creating the table in excel with a create table statement in "Execute sql task editor" like this:
CREATE TABLE `test` (
`MYmoney` Currency)

in the datapump I use OLDB source (local sql server) and destination OLE DB connection (excel destination).

When I open up the excel spreadsheet the Mymoney column will show dollar sign. The problem is that it should be Swedish kr currency showing. WHen I'm running the old DTS package in "Microsoft sql server dts designer components" in sql 2005 on the same server this result will be in the currency kr as it should be.

When I'm running the SSIS on my local machine the result also get right.

Any ideas?

Thanks

/Tobbe

View 4 Replies View Related

Export View Result Using Transferdatabase

Jul 23, 2005

I am trying to export the results of a view from an .adp to an .mdbusing TransferDatabase. The code I am using isDoCmd.TransferDatabase acExport, "Microsoft Access", _"C:\___workWayAheadImplementationXTRA_Samples.md b" _, acTable, "x_loe_vw", "LOE_tbl"When I run this I get an error that states" can't find the object 'x_loe_vw'".Yet it is defined as a view.Any ideas?Thanks,Jerry

View 2 Replies View Related

How To Export The Result Of A Sql 'for Xml' Query To An Xml Document

Aug 9, 2006

I have a sql query with a 'for xml explicit' clause. I would like to export the results of the query as an .xml file. I specify the query as 'SQL Command' in an OLEDB source adapter, but I don't know see an adapter that I can use for the destination. Any ideas? Thanks.

View 7 Replies View Related

Export The Query Result To Html

Oct 10, 2006



I am using the query window n SQL server 2005 express to execute some sql statement but i want to export the resut to html.. is that possible within the sql statement?

View 2 Replies View Related

SQL Server 2012 :: Result Export To EXCEL

Apr 27, 2015

using below query to compare columns in two tables.

SELECT
Col1 = ISNULL(a.name,b.name),
Col2 =
CASE
WHEN ISNULL(a.name,'') = '' THEN 'Table B'
WHEN ISNULL(b.name,'') = '' THEN 'Table A'

[code]...

How to export the result to EXCEL from SQL Server 2008 R2.

View 7 Replies View Related

How To Export Sql Query Result To An Excel Sheet

Jun 12, 2008

All,
Is there any way we can export the SQL Query result into an excel sheet, i tried the options 'results to grid, results to file' but nothing seems working.

View 2 Replies View Related

Integration Services :: Export Result Set To Excel Spreadsheet?

Nov 13, 2015

So I am trying to export my SQL Server Result Set from "OLE DB Source" to an Excel spreadsheet. This was working fine when I hard-coded the Excel spreadsheet path and file name. But now I am trying to create an Excel spreadsheet and file name using a variable...@ExcelFullyQualifiedName.

My "Excel Connection Manager" is defined with the following Properties...

DelayVaildation = TrueExpressions and Connection String ==> "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::ExcelFileFullyQualifiedName] + ";Extended Properties="Excel 12.0 XML;HDR=YES";"
"Excel Destination" Properties...
ValidateExternalMetadata = FalseCustom Properties ==> AccessMode = OpenRowset From VariableOpenRowsetVariable ==> User::ExcelFileFullyQualifiedName

When I attempt running my Package I am getting this error...

Error: 0xC0202009 at Data Flow Task, Excel Destination [100]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
Error: 0xC0202040 at Data Flow Task, Excel Destination [100]: Failed to open a fastload rowset for "serverfilesharessharedExport DataExport_Week_Of_2015_11_01.xlsx". Check that the
object exists in the database.
Error: 0xC004701A at Data Flow Task, SSIS.Pipeline: Excel Destination failed the pre-execute phase and returned error code 0xC0202040.

View 4 Replies View Related

Temp Table Vs Global Temp Table

Jun 24, 1999

I think this is a very simple question, however, I don't know the
answer. What is the difference between a regular Temp table
and a Global Temp table? I need to create a temp table within
an sp that all users will use. I want the table recreated each
time someone accesses the sp, though, because some of the
same info may need to be inserted and I don't want any PK errors.

thanks!!
Toni Eibner

View 2 Replies View Related

Writing Result Set On Text File And Export To Specific Location

Jul 8, 2013

Iam trying to crate a job, that writes the result set on text file and export to location like "abcxyz.txt"

job succeeds but i cant see any thing written on the file and i have given the same path in the job path option.

View 3 Replies View Related

Sp_columns Not Returnign Anything

Oct 13, 2006

Greetings all. I have a bizarre problem. I'm using sp columns to generate an html table with the field information of certain tables in my database. The table names are stored in an SQL Table called 'Manage_Tables' and I have 9 tables shown there. 6 out of the 9 are displaying correctly but the other 3 arent and the problem is coming from teh sp columns procedure that is returning nothing. why does this happen? all the tables are the same and have been created on teh same day by the same user with the rame rights and I have admin rights. Any clue?

View 1 Replies View Related

How To Get One Column From Sp_columns

Feb 27, 2008

Hi,
When I run sp_columns @table_name = someTable, I get many columns like TABLE_QUALIFIER, TABLE_OWNER and so on.
Is there a easy way to get only a COLUMN_NAME column?
Thanks 

View 2 Replies View Related

Underscore & Sp_columns ?!!

Oct 9, 2000

Hi friends,

I got troubles when I tried to pass table name to sp_columns via
@local_variable:

declare @tname as char(50)
set @tname = 'TB_MyTable'
exec sp_columns @tname

result of such script is:

TABLE_QUALIFIER ...[skipped]
-----------------------------------
(0 row(s) affected)

But if I will say:

exec sp_columns 'TB_MyTable'

result will be correct (all neccessary data about columns will be provided)

So, looks like if I pass table name which contains underscore via
@local_variable or SP parameter - result will be wrong.
If table name won't contains underscore - everything works fine and result
of script:

declare @tname as char(50)
set @tname = 'MyTable'
exec sp_columns @tname

will be absolutely correct.

Please, anybody can clarify this situation ???

Thanks in advance,

Michael

View 2 Replies View Related

Sp_columns Into A Variable

Mar 23, 2004

Hello,
I'm using sp_columns and would like to stock the result recordset into a variable (or simply get only the column name into a table variable in sql)

Is there any way to select only the column name and put it into a table variable ?

Thank

View 2 Replies View Related

Sp_columns Slow

Dec 3, 2007

In SQL 2005, I'm getting very slow response times with sp_columns. It takes
around 8 seconds to return the results of calling sp_columns.

Any ideas?

Thanks

-Dave

View 3 Replies View Related

Using Sp_columns Stored Procedure Trouble

May 12, 2005

Hello,

I'm trying to use the "sp_columns" stored procedure to pull in some
information about a table in my db, but I'm continuing to get the same
error:

ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
'sp_columns' expects parameter '@table_name', which was not supplied.

I'm not sure why, but I've re-written my code several times and can't
figure out just why this error is happening.  Here's a snippet of
the code:

Private Function GetDataType(ByVal columnName As String, ByVal table As String) As String
        Dim con As New
OdbcConnection(ConfigurationSettings.AppSettings.Get("LiquorLiabilityConnection"))
        Dim com As New OdbcCommand("sp_columns", con)
        com.CommandType = CommandType.StoredProcedure

        Dim param As New OdbcParameter("@table_name", table)
        param.OdbcType = OdbcType.VarChar
        com.Parameters.Add(param)

        param = New OdbcParameter("@column_name", columnName)
        param.OdbcType = OdbcType.VarChar
        com.Parameters.Add(param)

        Dim dr As OdbcDataReader
        Dim name As String

        con.Open()
        dr = com.ExecuteReader() 'THIS TRIGGERS THE ERROR
        While dr.Read
            name = dr("TYPE_NAME")
        End While
        dr.Close()
        con.Close()

        Return name
    End Function

Any suggestions would be appreciated.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved