I have a stored procedure that returns a set of tables based on data in a table for a company. These tables are being used to create drop down lists for criteria selection for the client. We have a Javascript based control that will make use of these based on the control name. So... ideally, I would like the stored procedure to return tables named "EmployeeID", "ResourceName", so that I can accurately name the controls when they are being created. The data that is returned is not static, so, for example, client 1 may see the EmployeeID drop down, while client 2 may not.
I searched and didnt see anyone that was able to accomplish this in this way, but wanted to post something here before I moved forward in a different direction.
I am thinking that I will likely return an extra table with the field names and their corresponding tables (i.e. "EmployeeID"/"Table0", "ResourceName"/"Table1", etc...)
In my report, I need to display column names (and its values) from my dataset, which uses the stored procedure and within it use the PIVOT statement. I cannot use the matrix control in report since I need to add another static column to the right of the matrix, but since that cannot be done ni SQL SSRS 2005, I need to PIVOT it through stored procedure. The parameter passed to stored procedure is the year and it is used for pivot. The statement looks like this:
@sql = 'SELECT * FROM (SELECT YearId, SchoolId, ReportText, OutputValue_Text from AggregateTable) AS AGAM PIVOT (MIN(OutputValue_Text) FOR YearId IN ([' + @YearId + '])) PVT'
EXEC(@sql)
So, since the years passed as parameters can change, I cannot hard-code it as column in my report, so I need to programatically display column names and values from my dataset, something like dataSet.Tables[0].Columns[].ColumnName; I have tried with writing the code, but I do not have much experience with it and I have not been very successful. Can someone help me with the code?
I am designing a package to export staging tables into a flat file.The names of the tables will be: TableAStaging_YYYYMM and TableBStaging_YYYYMM. As you can see the names of the tables will be changing each month.
The flat files will have similar naming: C:MyPathFlatFileTableAStaging__YYYYMM and C:MyPathFlatFileTableAStaging__YYYYMM.I want to run the package as an sql job in two steps, one for each table.I need to dynamically pass the table names and file names (together with the path) to the IS package.
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
I am new to SQL server and I am looking for the best practiceregarding the following:The GUI for the database I am creating has a number of questions like:A. Where are you currently living?1) In my own home2) In the home of a family member3) In a shelter4) On the streetHowever, for speed of data entry, the GUI (it's a web front-end,ASP/VBScript) accepts the number only. Futhermore the database onlystores the number entered.So for example for the above question, when the user is doing the dataentry, they would just enter a 1, 2, 3, or 4.However, when viewing the data, the end user wants to see the textthat corresponds to the number. So for example, if the user entered"3" for the above question, when they are viewing the data in the GUI(not to make edits, just viewing), they want to see "(3) In ashelter".My question is how best to display these friendly values?Should I do all the conversions in a stored procedure? If so, whatwould be the best way to do that? I tried something like this:SELECT Current_living =CASE Current_livingWHEN 1 THEN '(1) In my own home with my partner'WHEN 2 THEN '(2) In the home of a family member'ELSE NULLENDFROM tbl_DemographicInfo WHERE ClientID = @ClientIDbut I can't figure out how to do this for multiple fields.ALSO, I'm not sure if burying this stuff in a stored proc is the wayto go, because if the client wants a slight change to the friendlyname I have to get into the SP and change it. Seems like there mustbe a better way.I can also do this in the ASP code, but that also doesn't seem likethe best practice.I can have a lookup table, but again, where do I do the conversion?In an SP? With a view?Again, I'm looking for the best practice here. I know some ways Icould do it (like in the ASP), but I want to do it efficiently andcorrectly.Any help/comments are greatly appreciated.thanks in advance,Jon LaRosajlarosa at alumni dot brown dot edu
The notorious situation: a duplicate key exception. Users register themselves using fancy nicknames.This often leads to conflicts. In addition, as I point out in error reporting -- to except or not expect or return, the conflicts can not be prevented in a distributed environment (one ensures that there in no such name and still can get a concurrent conflict). For these reasons, the apps should be prepared to the conflicts. The cases to which apps are prepared, are not exceptional.
Meantime, if the engine insert process fails things naturally, we get an exception with a message like "Violation of UNIQUE KEY constraint 'col_name'. Cannot insert duplicate key in object 'table_name'. At first, it is not quite informative. Customers prefer to see something like: "a user with such a name already exists". Additionally, as explained in the referred topic, exception processing is extremely CPU consuming and should be not normally used as a function "feedback" tool.
The option to prevent the exception raising by preceding the INSERT op by a table-locking SELECT is not attractive either because of 1) locking and 2) double checking. Obviously, we get the conflict exception on INSERT because the DB engine precedes the insertion by a select. How it would check the existence of the same key in the table otherwise? I want to say that the only thing we should do is to override the default DB error handler.
How do I know that the error was caused by some specified constraint violation letting other issues to fail naturally? Should I parse the exception message looking for key_name?
What I am trying to do is to open an existing report in a Print Friendly format . What is the simplest way to do this using Visual Studio?
Here's the sort of thing I hope is feasible:
I can open my Report on a web page , then on that page can be a button / link for the user in case they want to open that report in a separate window. So I'd like to jump to that page by jumping to an URL and sending all parameters
something like :
http://Dev_Server/Reports/Pages/Report.aspx?ItemPath=%2fMy+Reports%2fTest+Report&rsaramOne=Parameters!paramOne.value + paramTwo=Parameters!paramTwo.value + etc all other parameters , I have on that report
Hi,I am using sql server 2005 and i am having table with a column which has unique key constraint.So if the user tries to enter the duplicate value for that particular column and try to save it ,it errors out in runtime..how do i raise the user friendly error message in this DB error, when user tries to save it.Thanks
I just started playing with sqlagent and am finding that viewing a specific message from an ssis step is cumbersome when other entries are also in the log. Exporting seems only able to export all history entries. Viewing right in the log viewer leads to a series of boundary expansions and right scrolls that dont seem convenient for quick identification of a problem in a medium sized ssis message. Right clicking on the message in the viewer seems like it is preparing the message for possible copy but it seems to end there. I'm already familiar with the different levels of verbosity available in the command line. Dont see anything useful on the web.
Is there a user friendly way to copy the contents of a specific message for perhaps pasting to notepad , so that problem identification is facilitated? Is there a not so user friendly way available, one taht doest involve 1st exporting the entire log?
Hello, Maybe anyone have done that before? I have table where i store SOURCE_TABLE_NAME and DESTINATION_TABLE_NAME, there is about 120+ tables. i need make SSIS package which selects SOURCE_TABLE_NAME from source ole db, and loads it to DESTINATION_TABLE_NAME in destination ole db.
I made such SSIS package. set ole db source data access mode to table or view name variable. set ole db destination data access mode to table or view name variable. set to variables defoult values (names of existing tables) but when i loop table names is changed, it reports error, that can map columns, becouse in new tables is different columns.
How do I use table names stored in variables in stored procedures?
Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000
I receive the error 'must declare table variable '@tablename''
I've looked into table variables and they are not what I would require to accomplish what is needed. After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.
Code Snippet
if exists(Select * from sysobjects where name = @temptablename) drop table @temptablename
It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.
Heres what the stored procedure does. I duplicate a table that is going to be modified by using 'select into temptable' I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA' then I truncate the original table that is being modified and insert the temporary table into the original.
Heres the actual SQL query that produces the temporary table error.
Code Snippet Select * into #temptableabcd from TableA
Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02) SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02', FROM TableB where ColumnB = 003860 Group By ColumnA, ColumnB
TRUNCATE TABLE TableA
Insert into TableA(ColumnA, ColumnB,Field_01, Field_02) Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02', From #temptableabcd Group by ColumnA, ColumnB
The above coding produces
Msg 208, Level 16, State 0, Line 1
Invalid object name '#temptableabcd'.
Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?
I have a function that returns a table from a comma-delimited string.
I want to take this a step further and create a function that will return a set of tablenames in a table based on a 'group' parameter which is a simple integer...1->9, etc.Obviously, what I am doing is not working out.
CREATE FUNCTION dbo.fnReturnTablesForGroup ( @whichgroup int ) RETURNS @RETTAB TABLE ( TABLENAME VARCHAR(50)
Hello, It is possible to write stored procedures which take table names as parameters; is it also possible to do this with table valued functions?
For example, a simple stored procedure is this:
CREATE PROCEDURE SelectTop(@tableName sysname) AS BEGIN
Execute('Select top 10 * from ' + @tableName + ';')
END
I want to be able to do the analogous thing with a table valued function (so that I can query the result set, without having to create a temp table). How should I do this (i.e., pass a tablename as an argument to a table valued function)?
Hi there. I haven't been able to figure out how to join a table on column on multiple table names. Here's the situation:
I have a table "tblJob" with a key of jobID. Now for every jobID, the program creates a new table that keeps track of the stock before the jobId was processed and after it was processed to give accurate stock levels and show the difference in stock levels. So, a jobID of 355 would be related to the table: "tblPreStock_335" and "tblPostStock_335". These 2 tables have all the materials in stock and the quantity. Therefore they show how much material was used. I need to figure out the difference in the material in the stock before and after the processing.
That means that I have to get a stockID, get the associated pre and post tables, and then display the difference of ALL the materials in the pre and post tables.
Could someone help me get started on the right path? Even a link to similiar problem that I haven't found would be nice.
I like to print the table names that starts with 'table'. I'm getting the same table name four times. Instead of four different table name.
create table table1(tableID int, tableName varchar (30)) insert table1 select 1, 'tableSam' union all select 2, 'tableDaniel' union all select 3, 'tableRon' union all select 4, 'tableKen' union all select 5, 'tableHonda'
create table table2(tableID int, tableName varchar (30)) insert table2 select 6, 'tableSam' union all select 7, 'tableDaniel' union all select 8, 'tableRon' union all select 9, 'tableKen' union all select 10, 'tableHonda'
create table table3(tableID int, tableName varchar (30)) insert table3 select 11, 'tableSam' union all select 12, 'tableDaniel' union all select 13, 'tableRon' union all select 14, 'tableKen' union all select 15, 'tableHonda'
create table table4 (tableID int, tableName varchar (30)) insert table4 select 16, 'tableSam' union all select 17, 'tableDaniel' union all select 18, 'tableRon' union all select 19, 'tableKen' union all select 10, 'tableHonda'
declare @string varchar(330) declare @tableName varchar(30) declare @count int set @count = 0
while (@count < 5) begin
select @tableName = table_name from information_schema.tables where table_name like 'table%'
In SQL Server Management Studio when browsing the tables they are all prefixed with dbo followed by the table name. Is there a way of turning this off.
Hi all, By using below query i can get no of tables having the give column name in a particular database, SELECT COUNT(*) AS CounterFROM syscolumnsWHERE (name = 'empno') but i want to know the table names too? any one please suggest me how to find table names too...... In other words i know the particular column name and right now i want to know the table names in which this column name exists.
hi... can someone pls tell me the sql statement to 1)Select all the tables in SQL server(only the ones created by me) 2)Select all the column names from a given table
Please help, I am inserting data into a newly created table with many rows similar to thoes below using a sp with a parameter @Extract CREATE PROCEDURE mytest @Extract int AS DECLARE @MyName varchar(25)--these keep changing for each total DECLARE @Counter varchar(2)--this will increment from 2 to 40 SELECT @MyName ='tblG3>74Ex' +convert(varchar(3), @Extract) SET @total1 =(SELECT COUNT(*) FROM @MyName WHERE (sSurgery_id ="S0" + @Counter) and (iExtract_nm = @Extract) SELECT @MyName ='tblG335-74Ex' +convert(varchar(3), @Extract) SET @total2 = (SELECT COUNT(*) FROM @MyName WHERE (sSurgery_id ="S0" + @Counter)and (iExtract_nm =@Extract) insert into tblNew([35-74],[>74],etc,etc,Report4) values (@total1,@total2,ect,ect,@report4) it all works until I try to use the @MyName Please put me out of my misery Many thanks David
While setting up a BCP in a DTS package, I've found a rather annoying 'feature' in DTS. When selecting the destination table, the display does not provide enough spaces to see the full table name. It is a drop down with a set width, and the database and owner are displayed with the table name. I hadn't noticed this problem in the past, but the database I'm working with now has several tables that start with the same letters, and when the drop down displays them, they all look the same.
Does anyone know of a work around for this so that I can see the full table names? Even being able to see them after the package is created would be nice, because then I could check to see if I guessed the correct table.
Using VB5|6/ADO/MSSQL6.5. I want to get the names of tables in a query. I.e. if query is "Select Name, Title from Employee inner join job on employee.empid = job.empid" I want a way to get "Name, Title" In VB 3 we used the VBSQL.VBX which had 2 functions for this: SQLTabCount and SQLTabName.
I was wondering how would I get the column names from a table? Not the results just a listing of column names. What is the command to get this information? I am using MS Access, but I posted here because MS SQL is the closest thing on this forum and the syntax is usually similar.
Hi, I need do get a columns names from a table? How to do this in pure SQL? I thought about creating a stored procedure or user function with a result of a string ( col1name,col2name ....) I do not know how to count the number of columns in a specyfied table? Any help would be appreciated. Thanks in advance. magicxxxx
here a quick rundown of what i want to do: database database1
database hold all my good information. database 2 is the copy for breaking. Im too a point that i need to get my 2nd DB to by like the first. I want to truncate all the tables in database2, and then export, or select from insert into. I just need the data, not triggers or anything like that.
So: How can i find the name of my tables (not views) in that database
For each (select table_name from system where database="database") copy database.table_name database1.table_name;
i hope that makes sense. I just need to restore my devel DB back to the original so i can mess it up again, Thanks
I have a cursor which populates a variable with the name of each user table within my DB. I'm trying to copy the tables one at a time by using a command like this: SELECT * INTO @NewTable FROM @OrigTable Query analyzer tells me that there's incorrect syntax near the keyword 'INTO'. This seems fair enough to me as I assume it's trying to put the data into the variable rather than the table name which is held within the variable. Does any know how I can get around this? Are there any alternative ways of copying the table structure (preferable without the data)?
