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
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='
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?
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
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.
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?
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
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
if exists (select 1 from sysobjects where id = object_id('dbo.fnTestColumnsFromFunctions') and type in ('IF', 'FN', 'TF')) drop function dbo.fnTestColumnsFromFunctions; 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.
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.
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.
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
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.
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?
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.
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...
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.
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.
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?
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
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
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 ?
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()