create Procedure AddNewStyle @table_nm varchar (20)
declare @string varchar (5000)
select @string='Update product
Set Product_End_dt = '12/31/05'
from ' + @table_nm + ' c inner join Booktable b
On (c.book_id = b.book_id).............
exec (@string)
--
I need this kind of a proedure with the table name as avariable,but how do I concatinate the quotes in the date??Thanks for any help!
Reddy.
I have two parameters
I have dropdown with 2007 , 2008 values
the second dropdown should be disabled until the user select 2007 or 2008. if the user select 2007 i will populate the second dropdpwn a list with 2007 weeks. if the user selects 2008 the second dropdown will be populated with the 2008 weeks.
can i disable paprameter in sql server report
Thanks
I have read all the post about passing parameters from sql task in and out and have tried them all with no anvile. Frist of, they are too many types to deal with now, db types, SSIS types. Why the hausl. Can we make data type flow a bit more easy. Well, enough about the complain. I have been trying to dynamically set my sql query statement for my datasouce component using a variable set by a sql task. The tried a couple of methods.
Method 1
Using sql task, ole connection and stored proceduer( exec usp_mystoreprocedure ? OUTPUT) with output parameter (User::sql_query out varchar 0). My stored procedure generate a sql statement as nvarchar and sets the output variable with that. Now when i parse query, i get the error
TITLE: SQL Task
------------------------------
The query failed to parse. Syntax error, permission violation, or other nonspecific error
------------------------------
BUTTONS:
OK
------------------------------
So first of my parser is saying my syntax is wrong that that what most of the post in this form says.
When that didnt work, i decided to return the result and catch it with an returnvalue variable but that give the same error
Method 2:
I tried using ADO.NET instead of ole so did about the same thing except changed the parameter to @SQLQUERY which is the same name as my output parameter on my stored proceduere. Check the query to exe [dbo].[sp_GET_SQL_QUERY_FOR_SSISGRP] @SQLQUERY OUT and parameter settings on sql task as (User::sql_query out string @SQLQUERY OUT) and the error it generated when i run the task was
[[Execute SQL Task] Error: Executing the query "exe [dbo].[sp_GET_SQL_QUERY_FOR_SSISGRP] @SQLQUERY OUT" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 ("@SQLQUERY"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Method 3:
I made a QUERYSTATEMENT bridge table. The stored procedue inserts the sql query statement into the QUERYSTATMET TABLE and then i use sql task to pull the statement, set it to a variable and then set my sqlstatement for my datasource component using experessions. Every things works fine till the sql task exectues to pull the sql statement from the QUERYSTATEMENT TABLE.
First, I used OLE connection so my statement was "SELECT ? = STATEMENT FROM QUERYSTATEMENT WHERE TBLNAME = ' MYTABLENAME' " and parameter was set up as (User::sql_query out varchar 0). I parsed this statement and got the error
TITLE: SQL Task
------------------------------
The query failed to parse. Syntax error, permission violation, or other nonspecific error
------------------------------
BUTTONS:
OK
------------------------------
So it looks like OLE doesnt like me. I tried ADO.NET connection and changed the variables ? to @SQLQUERY. So my statement was now "SELECT @SQLQUERY = STATEMENT FROM QUERYSTATEMENT WHERE TBLNAME = ' MYTABLENAME' " and parameter was (User::sql_query out string @SQLQUERY). When i executed the sql task, i got
[Execute SQL Task] Error: Executing the query "SELECT @SQLQUERY = QUERYSTATEMENT FROM QUERYTEXT WHERE TBLNAME = 'CJP'" failed with the following error: "Must declare the scalar variable "@SQLQUERY".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
So at this point, im really out of luck. I even tried setting an object variable with a record set from the result of sql task executing -> SELECT QUERYSTATEMENT FROM QUERYTEXT WHERE TBLNAME = 'CJP'" but when i tried casting the object variable into a string in my datasource component expression, it indicated that variables of type User::Object cannot be used in expression. If someone can help me, I will be very glad. I would also like to know what types to use in SSIS sql task when getting data from a database. Example, I was returning a datatype of nvarchar from my db, do i recieve this datatype as string, AnsiString etc. I know int, numeric can map to Int32 (if from 86x) or Int64. Can we have a table of data mapping if possible.
PLEASE PLEASE PLEASE HELP !!!!!!
Hi,
I need to parameterize some values in the data flow so that i can chnage the values directly in parameter file and re run the data flow for new value in the passed in the parameter. This can be easy for other who do not know about the flow of data flow task as to where to change the variable/parameter.
How can this be accomplished. I want the data flow task to refer to this file before it starts executing and pick the appropriate value from the file.
Or is their any better way to accompalish what i want to do here in SSIS.???
tHNAKS FOR UR HELP FOLKS !!!
Hi,
I have declared an internal paramter and given the default non queried value as
select @split = case when max(rowid)%2 = 1 then (max(rowid)/2) + 1 else max(rowid)/2 end
and when i run it i am getting the error -- The property 'DefaultValue' of report parameter split doesnt have the expected type. What should i do to solve it. Any help is appreciated.
Regards,
Karen
Hi, I have a table in which I will insert several redundant data. Don't ask why, is Integration services, it only reads data and inserts it in a SQL table. THis way, I have a SQL table with several lines repeating them selves. What I want to do is create a procedure that reads the distinct data and inserts it in another table, but my problem is that I am not able to select data line by line on the original table to save it in local variables and insert it on the another table, I just can select the last line. I've tried a while cycle but no succeed. Here is my code: create proc insertLocalizationASdeclare @idAp int, @macAp varchar(20), @floorAp varchar(2), @building varchar(30), @department varchar(30)select @idAp = idAp from OLTPLocalization where idAp not in (select idAp from dimLocalization)select @macAp=macAp,@floorAp=floorAp,@building=building,@department=department from OLTPLocalizationif (@idAp <> null)beginInsert into dimLocalization VALUES(@idAp,@macAp,@floorAp,@building,@department)endGO This only inserts the last line in the "oltpLocalization" table. O the other hand, like this:create proc aaaaasdeclare @idAp as int, @macAp as varchar(50), @floorAp as int, @building as varchar(50), @department as varchar(50)while exists (select distinct(idAp) from OLTPLocalization)begin select @idAp =idAp from OLTPLocalization where idAp not in (select idAp from dimLocalization) select @macAp = macAp from OLTPLocalization where idAp = @idAp select @building = building from OLTPLocalization where idAp = @idAp select @department = department from OLTPLocalization where idAP = @idApif (@idAp <> null)begin insert into dimLocalization values(@idAp,@macAp,@floorAp,@building,@department)endendgo this retrieves every distinct idAp in each increment on the while statement. The interess of the while is really selecting each different line in the OLTPLocalization table. I did not find any foreach or for each statement, is there any way to select distinct line by line in a sql table and save each column result in variables, to then insert them in another table? I've also thought about web service, that reads the distinct data from the oltpLocalization into a dataset, and then inserts this data into the dimLocalization table. Is there anything I can do?Any guess?Really needing a hand here!Thanks a lot!
View 1 Replies View RelatedI'm using SS 2012.
I started with an inline table returning function with a hard coded input table name. This works fine, but my boss wants me to generalize the function, to give it in input table parameter. That's where I'm running into problems.
In one forum, someone suggested that an input parameter for a table is possible in 2012, and the example I saw used "sysname" as the parameter type. It didn't like that. I tried "table" for the parameter type. It didn't like that.
The other suggestion was to use dynamic sql, which I assume means I can no longer use an inline function.
This means switching to the multi-line function, which I will if I have to, but those are more tedious.
Any syntax for using the inline function to accomplish this, or am I stuck with multi-line?
A simple example of what I'm trying to do is below:
Create FUNCTION [CSH388102].[fnTest]
(
-- Add the parameters for the function here
@Source_Tbl sysname
)
RETURNS TABLE
AS
RETURN
(
select @Source_Tbl.yr from @Source_Tbl
)
Error I get is:
Msg 1087, Level 16, State 1, Procedure fnTest, Line 12
Must declare the table variable "@Source_Tbl".
If I use "table" as the parameter type, it gives me:
Msg 156, Level 15, State 1, Procedure fnTest, Line 4
Incorrect syntax near the keyword 'table'.
Msg 137, Level 15, State 2, Procedure fnTest, Line 12
Must declare the scalar variable "@Source_Tbl".
The input table can have several thousand rows.
I would like to create a procedure which create views by taking parameters the table name and a field value (@Dist).
However I still receive the must declare the scalar variable "@Dist" error message although I use .sp_executesql for executing the particularized query.
Below code.
ALTER Procedure [dbo].[sp_ViewCreate]
/* Input Parameters */
@TableName Varchar(20),
@Dist Varchar(20)
AS
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)
[code]....
Hi. I am trying to get a row count of each row of each table in the database. Is that possible? Using a SP or UDFS? I dont want the column size of each table but the total datasize of each row.So for example if I have 5 rows each in 3 tables I need a query that will return 15 rows with the size of each row(size of all coumn data summed together). Thanks.
View 6 Replies View RelatedHi,Is it possible to retrieve a single table from a backup in SQL 7.0?
TIA
Jay
Hello,
I've a database in Production db which is 300 MB and one of its table has
948900 rows then how can I know how much size this particular table is of.
Thanks.
Someone, I don't know who, deleted an important table in my database. I've only been able to find restore routines on full databases. Is there any way to restore just a single table?
View 3 Replies View RelatedHi,
How can I see which table is locked up by some particular process?
I know that I can view paricular spid from 'current activity'. But is there any way I can see which table is the center of problem?
I really appreciate your help..
Hi All
I want to convert table structure into xml in the following format.
Eg: <TABLE_NAME>
<COLUMN_NAME>
<IS_NULLABLE> </IS_NULLABLE>
<CONSTRAINT_TYPE> </CONSTRAINT_TYPE>
<CONSTRAINT_NAME> </CONSTRAINT_NAME>
<TABLE_CATALOG> </TABLE_CATALOG>
<LENGTH> </LENGTH>
</COLUMN_NAME>
</TABLE_NAME>
where TABLE_NAME is the table name in database and COLUMN_NAME is name of the column in above table and the remaning are the attributes to that column.
please suggesst me the sql query to get the following structre of any table in database,
Thanks
Rupal
Hi .... This is my problem .....
I have two table with the same structure (ID, DATE_1, DATE_2).... PLANS and AUD_PLANS. I need to create a trigger (FOR INSERT) that do the following things:
In PLAN table i have the next row: (1234, '01/01/2006', '30/01/2006'). I need to capture these values to compare with the row to insert. How can i do this?
I try this into de Trigger Code ....
DECLARE @FECHA_1_ANT AS datetime
DECLARE @FECHA_2_ANT AS datetime
DECLARE @FECHA_1_INS AS datetime
DECLARE @FECHA_2_INS AS datetime
Select @FECHA_1_ANT = P.DATE_1, @FECHA_2_ANT =P.DATE_2 from PLANS P inner join INSERTED I on P.ID = I.ID
But the values for @FECHA_1_ANT and @FECHA_2_ANT are the same that the values to insert.....
Please help me !!!!!
Thanks
Hi,
anyone could help me on how to do the inner join of two table from two different database. Below is my scripts:
Dim sqlconnection1 As New SqlConnection(ConfigurationSettings.AppSettings("appDSN"))
Dim sqlCommand2 As New SqlCommand("", SqlConnection1)
Dim sqlconnection2 As New SqlConnection(ConfigurationSettings.AppSettings("smDSN"))
Dim sqlCommandSM2 as new sqlCommand("", sqlconnectionSM2)
Sub Page_Load(Source as Object, E as EventArgs)
sqlCommand2.CommandText = "Select * from invitation inner join Guest on invitation.guestid = Guest.Guestid inner join Department on Department.departmentcode = Guest.deptCode where eventcode = '" & ecode & "'"
sqlconnection1.Open()
............................
The invitation table is from SqlConnection1 and the Department table is from the sqlconnection2. How to do this . Pls .. pls help me.
Ann123
Hi,
Could you please tell me removing table columns with null value will reduce the datafile size.
Data file size needs to be reduced to make free space on the disk, Could please suggest what data type can be choosen to be removed from the existing table so that more space can be made(Char,varhchar,int,date etc )
Thanks
John Jayaseelan
Hi
Is there a way to restore only one table from a DB backup.
I need restore one table not database because I have update others tables in this database.
thanks in advance
kinds regards
Abdel
Hi all, I need a favour from you... can i do a list to find out the table names used by a stored procedure. remember it's around 700 Stored Procedures. and following a rule <databasename>.dbo.<tablename>
View 1 Replies View RelatedHi
Can anybody tell me how can I find whether a column exists in my table or not???
Piyush
I accidently deleted the file that I was working on in notepad that had the tables and views that I created. Is there a way to retrieve a list and the code that I put in for the tables and views?
View 1 Replies View RelatedHi all,
I have to come up with a design choice for a DB Table for a financial firm(i.e. Tables with Fixed column or Value-Pair).
Case: SQL Server 2005 is used as a back-end for a web application(which basically is designed for doing web based analysis). The business people are insisting on a feature that enable power users to add fields to the underlying tables. One option proposed is named value-pair design approach? But from performance point of view, this seems a nightmare. How can this be integrated into static-column table approach. FYI, security policy of the client do not allow the underlying table structures to be changed by the user and currently
there are 5000 fields, with a possibility to grow.
Thanks so much for your suggestions!!
Dilla
Hi
How do i group 2 table rows?
I have a report with a table and it has 58 rows, in some of the rows i am displaying the data like this
Roth contribution (Heading)
data for the roth contribution.
So depending on the options checked sometimes the heading Roth contribution is at the end of the page and the data is the next page. so how can i group 2 table rows together.
I have tried clicking on the 2 rows and grouping it, but nothing seems to appear in the report..
any help will be appreciated.
Regards,
Karen
Hi All,
I would like to send a query via Table adapter using parameter that is not the whole field. (Its only a part of the filed).
I will try to illustrate my needs:
We have a list of items in a data grid.
I would like to get the list of items who are answering my item name search query.
(I need to change the list every time the user enter another key letter for the search).
I would like to do so in the data adapter, but I'm not able to use the LIKE statement.
Please please try to help me
P.S.
Do I need to use stored procedure, if so, can anyone give me an idea how to do so ?
Hello. I have a SP. I am passing two variables to it that are comma seperated lists (ids) so that I can use an IN clause. But since I cannot use these CSV in an IN clause directly I wrote a temp table that first parses the CS list and creates a temp table with a single column of IDS.Then in a second temp table I am selecting the data using enrollment_id IN (Select enrollment_id From #temptable)Then the SP selects data from the second temp table.This works exactly the way it is supposed to work. But when I call the SP from ASP.NET it doesn't return any data. I just cant seem to return data by selecting from a temp table. And my ASP.NET code is correct because I commented out everything and just returned the parameters I was passing and that worked. So does anyone know how to select data from a temp table in a SP when executed from ASP.NET?I even tried Table Variables and same thing. It works great in SQL but when called from ASP.NET it doesnt return any data. I am guessing it's probably losing its scope. Any quick help would be appreciated. Thank you.Here is a snippet of the SP (even this fails):CREATE PROCEDURE [dbo].[returnEnrollments](@organization_id int, @test_item_list ntext, @enrollment_list ntext)As--select @test_item_list as tilist, @enrollment_list as elistDeclare @report_start_date datetime;Declare @report_end_date datetime;Declare @test_item_id_list varchar(8000);Declare @enrollment_id_list varchar(8000);DECLARE @TestItemID varchar(10), @Pos intDECLARE @EnrollmentID varchar(10)--Set @IDList = @test_item_id_list;--Set @EnrollmentIDList = @enrollment_id_list;Set @test_item_id_list = Cast(@test_item_list As varchar(8000))Set @enrollment_id_list = Cast(@enrollment_list As varchar(8000))Select @report_start_date = report_start_date, @report_end_date = report_end_date From organization Where organization_id = @organization_id;-- Create a temp table to store all the enrollment idsIF OBJECT_ID('tempdb..#EnrollTemp') IS NOT NULLDROP TABLE #EnrollTempCREATE TABLE #EnrollTemp (enrollment_id int NOT NULL)SET @enrollment_id_list = LTRIM(RTRIM(@enrollment_id_list))+ ','SET @Pos = CHARINDEX(',', @enrollment_id_list, 1)IF REPLACE(@enrollment_id_list, ',', '') <> ''BEGIN WHILE @Pos > 0 BEGIN SET @EnrollmentID = LTRIM(RTRIM(LEFT(@enrollment_id_list, @Pos - 1))) IF @EnrollmentID <> '' And @EnrollmentID > 0 BEGIN INSERT INTO #EnrollTemp (enrollment_id) VALUES (CAST(@EnrollmentID AS int)) --Use Appropriate conversion END SET @enrollment_id_list = RIGHT(@enrollment_id_list, LEN(@enrollment_id_list) - @Pos) SET @Pos = CHARINDEX(',', @enrollment_id_list, 1) ENDENDSelect top 20 * From #EnrollTemp Edit: Just used a DataReader instead of a datatable and it works. Dont know why it would fail with a datatable.
View 4 Replies View RelatedHi everbody,
Anybody tell me how to add new table in merge replication using Enterprise manager or T-sql.
Thanks in advance
I have a table which has a blank as owner name when I see on Enterprise manager. Sp_changeobjectowner is not working on this to change the owner name to dbo.I am still able to create another table with same name under dbo.But I want to get rid of the table which has no ownername. When i run the drop table command it doesn't drop that!!It says invalid object!!Any help!!
thanks,
Di.
Hello,
I'm in trouble, I have just dropped a table from a sql dbase and now need to recreate it. I have no backup and I am panicking!!! I need to know if I can recrreate them .
If this can't be done let me know I have to bit the bullet
thanx
I have a table with with 4000000 lac records in one table and I tried to copy
the data from this table to an another table using
INSERT INTO TAB2
SELECT FROM TAB1
It failed sayin that the transaction log is full...and neeeds to back up the
transaction log to free some space.
Now if I try to delete the table TAB2 it system is hanging.
Any ideas
Thanks
Reddy
Since a user increased the size of a 5.5Gb database by 500Mb, the following error is being reported when they attempt to use the Recalculate button (I think they mave for some reason have recreated a table that they thought was missing, but I haven't been given the full details yet!):
Error 2541:[SQL Server]Table Corrupt: object id does not match between extent in allocation pages and Sysindexes; check the following extent alloc pg#=195072 extent#=195320 object id on extent=0 (object name = 0)object id in Sysindexes=471787584 (object name = C0122402RT0)
Other than this the database is behaving normally so far. How do I solve this problem (i'm hoping to get this sorted soon if anyone can help)?
thanks
Derek
hi all
i deleted 8 fields from a table and i need to retrieve them back, is there a way to do that.
please help it is very urgent
I have a huge table with 4 primary keys on it. I need to delete the data from this table ( approx. 5.6 millions records to be deleted). It takes a hell lot of time to delete it by normal query.
Can someone please suggest me a better way?
Any help will be appreciated.
Hi,
I have 7 excel files that i have to import to sql server database and in that 6 of them work fine. But when i try to import the 7 one i get an
oledb exception saying that External table is not in the expected format.
So can i someone please help me with this error.
Regards
Karen