I'm trying to create a number of tables that relate to projects which have numberic names (e.g. 93002, 93009, 93012). However it appears that I'm unable to create a table with numeric values. When I try to create the table I get:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.93009'.
Am I right in presumung that I can't create numerically named tables, and can anyone suggest a naming convention that isn't NineThreeZeroZeroNine ?
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.
when I run below query I got Error of Arithmetic overflow error converting numeric to data type numeric declare @a numeric(16,4)
set @a=99362600999900.0000
The 99362600999900 value before numeric is 14 and variable that i declared is of 16 length. Then why this error is coming ? When I set Length 18 then error removed.
I'm getting the above when trying to populate a variable. The values in question are : @N = 21 @SumXY = -1303765191530058.2251000000 @SumXSumY = -5338556963168643.7875000000
When I run, SELECT (@N * @SumXY) - (@SumXSumY * @SumXSumY) in QA I get the result OK which is -28500190448996439680147097583285.072256 ie 32 places to left of decimal and 6 to the right When I try the following ie to populate a variable with that value I get the error - SELECT R2Top = (@N * @SumXY) - (@SumXSumY * @SumXSumY)@R2Top is NUMERIC (38, 10)
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)
I have imported some data to sql2k from my old system. Somehow, it importedinvoice amount to char type.I just created another column called invamt2 type NUMERIC so I can copy orconvert content of invamt which is type CHAR. There are about 50,000records.How can I convert/cast from char type to numeric type ?Thanks
In my script component for "Opening Balance", I set my output columns like this:
Row.AccountNumber = CDate(rowValues(1)
Row.OpeningBalance = CDec(rowValues(2))
Row.ClosingBalance = Nothing
Because I don't have a value for ClosingBalance in this row, I set ClosingBalance = Nothing.
And for "Closing Balance", I set my output columns like this, because there's no value for OpeningBalance.
Row.AccountNumber = CDate(rowValues(1)
Row.OpeningBalance = Nothing
Row.ClosingBalance = Nothing
Then, in my column mappings I map OpeningBalance = OpeningBalance and ClosingBalance = ClosingBalance.
The idea being that if there's no value, then it should be set to NULL. However, in my table, I see that instead of NULLs, there's 0.00000. Which is not what I want to see.
I'm trying to pivot the data in a table but not aggregate numeric data, just rearrange the data in columns as opposed to rows.
For example, my initial table could be represented by this structure:
ID Label Value
1 a val1
1 b val2
1 c val3
1 d val4
1 e val5
2 a val6
2 b val7
2 c val8
2 d val9
2 e val10
and I am trying to get it into the following structure:
ID Label a Label b Label c Label d Label e
1 val1 val2 val3 val4 val5
2 val6 val7 val8 val9 val10
The number of labels is known beforehand, so we know how many columns to make. I can get a first step at pivoting it with 'case' statements, but obviously still end up with a row for each Label/Value pair since there is no aggregate function being applied. So I'm not sure how to get it flattened down like shown above?
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)?
I have an Excel/ vba module to update a SQL database. One of the data is numeric. If the data is a positive number, there is no problem, but if the number (zscore) in Excel is negative an error pops up: Error converting datatype varchar to numeric.
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.
Hi, Let's say I have 1000 registered users in database table and each of them has numeric ranking value. How can I get the position of each user in comparison to other users ranking value?
I am getting an error when I try to insert a number typed in a text box control into a Sql database table column’s type numeric(6,2). For example: If I type 35,22 into the text box, or 35, and then I submit the form to insert the data into database, I get the follow error:
System.FormatException: Input string was not in a correct format. Line 428: CmdInsert.Parameters.Add(New SqlParameter("@Measure", SqlDbType.decimal, "Measure"))
A piece of the SP inside Sql server:
USE market26 GO ALTER PROC new_offer @Offer_id bigint, @Measure numeric(6,2) = null, …
What is wrong? Why it doesn’ t accept the number typed in my text box?
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
