As
Declare @TableUniqueIdentifier varchar(80), @SQLString varchar(5000)
set @TableUniqueIdentifier = newid()
set @TableUniqueIdentifier = 'Report_SomeFooReport' + @TableUniqueIdentifier
set @TableUniqueIdentifier = replace(@TableUniqueIdentifier, '-', '7')
set @SQLString = 'Create Table ' + @TableUniqueIdentifier + ' (xxx varchar(40))'
exec @SQLString
Return
Go
Set Quoted_Identifier Off
Go
Set Ansi_Nulls On
Go
-------------------------------------------
the error is:
Server: Msg 2812, Level 16, State 62, Line 12
Could not find stored procedure 'Create Table Report_SomeFooReport06EEEC8D7EA6A74D0178EDD79E999B (xxx varchar(40))'.
So may'be a format issue or something,
im trying to create "temp" tables for sql 2005 report services in my Stored procedures which would have a sql job to get deleted at 23:00
i have temp table name "#TempResult" with column names Memberid,Month,Year. Consider this temp table alredy has some rows from previuos query. I have one more table name "Rebate" which also has columns MemberID,Month, Year and some more columns. Now i wanted to insert rows from "Rebate" Table into Temp Table where MemberID.Month and Year DOES NOT exist in Temp table. MemberID + Month + Year should ne unique in Temp table
Id StdId TeacherName Day subject 1 1 Archana Monday English 2 1 Archana Tue Marathi 3 1 Shama Wed Hindi 4 1 shama Thus Hindi 5 1 Kavita Fri Hindi 6 2 Archana Mon english 7 2 Dipti Tues Hindi
Second table : Student
Id Sname Cid 1 Shalini 1 2 Monika 1 3 Rohan 3
I want to fetch uniq combination of stuid and subject.Result should show all subject of student whether may be teachername and day. If I choose shalini whose stuid is 1,all subject for shalini(hindi,english,marathi) should come. Record from either of three should come
Id StdId TeacherName Day subject 3 1 Shama Wed Hindi 4 1 shama Thus Hindi 5 1 Kavita Fri Hindi
I want fetch studentname along with teachername,day and subject whose cid = 1 here is my query
select Student.Sname,TeacherName, Day,subject from StudentTeacherRelation inner join Student Student.id = StudentTeacherRelation.StuId where cid = 1
I want place result of it in temp,Want fetch max(id) from temp table by doing group by on Sname and Subject.find all id from temp table where that id present in max id.
show Id StdId TeacherName Day subject where (1,2,3,4,5,6,7-- all id from temp) in (1,2,5,6,7 -- max id from temp by doing group by on Sname and subject)
So it will show record Id StdId TeacherName Day subject where id is 1,2,5,6,7.Only five record should come.How to do that?
I need to append data to the existing Table1 from a .txt file stored like this: "http://xx0opt02.corpuk.net/ABC_REPORTS/DATA/Table1.csv" (Columns are identical)
This .csv contains a rolling 7 days of stats. which means if it is added every Morning 6 of those Days will have been added before and must be Deleted.
I would like to schedule an automatic procedure to create a temp table in the server every day and a script removing duplicates.
I have a few questions:
*) Can I shedule from the Enterprise Console to read/create table from the above link to do this?
**) I heard I need an SQL agent. If so why and what is it?
***) Is it better to append data and then script removing Duplicates, or is it better to import into a temp table run comparison between the 2, Delete from Temp what is Common and then append whats left of the Temp to the Table1?
****) Please could someone paste a sample of CREATE TABLE from a http link like the one above?
I am getting frustrated with the accounting database table structure. I am really struggling to get the ‘par hours’. This is the amount of time that someone is assigned to work based on their workgroup in the workhour table.
Table 1 –Workhour table
Hours Resourceid workhourgroupcode dayid
NULL TJOHNSO TJOHNSO 1
8.0 TJOHNSO TJOHNSO 2
NULL TJOHNSO TJOHNSO 3
8.0 TJOHNSO TJOHNSO 4
NULL TJOHNSO TJOHNSO 5
8.0 TJOHNSO TJOHNSO 6
NULL TJOHNSO TJOHNSO 7
Table 2 - Time
Work DayHours
WorkGroup Resourceid DayID Non TimeDayWorked Hours worked
When I link these together by the resourceid dates worked, but since there is no date in the workhour table I cannot even do an outer join. The only think I could think of was creating a temp table (or a view) to return a line item for every day during the timeframe given. I created a dayID for they second table, I am not sure how to proceed. I need a line item to return for every day of the month so it will look at the workhours table to see how many hours someone is supposed to work.That will allow me to bring into Crystal, group and total.
I am working with the SP tuning. I want to know clearly about the temp tables.Instead of Select * into #table, it is always better to create temp table structure and insert the data later.Is it true in the case, if the data is small? insert into #table (select colmn1, column 2 from TableA join Table B on JoinC on joinD on..If they use several joins, in that situation, which way of creating temp table would be better?
i am trying to create stored procedure but i am getting error
create proc t @i int as
if @i = 1 begin select s Name,identity (int,1,1) as intid into #T from ( select 'SS' s) p end if @i = 2 begin select s Name,identity (int,1,1) as intid into #T from ( select 'S' s) p end
Server: Msg 2714, Level 16, State 1, Procedure t, Line 15 There is already an object named '#T' in the database. Server: Msg 170, Level 15, State 1, Procedure t, Line 17 Line 17: Incorrect syntax near 'p'.
In a Stored Proc I am creating the following temp table and index:
CREATE TABLE [dbo].[#ShipTo]( [Ship_to_Num] [int] NOT NULL, [Country_key] [nvarchar](3) NULL, CONSTRAINT [PK_ShipTo] PRIMARY KEY CLUSTERED ( [ship_to_Num] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
The stored Proc runs fine from "exec", but when you batch into a Job it gives the error that "PK_ShipTo" already exists! I even put in a drop table on #ShipTo, but the same effect.
Hello,I am interested in dynamically creating temp tables using avariable in MS SQL Server 2000.For example:DECLARE @l_personsUID intselect @l_personsUID = 9842create table ##Test1table /*then the @l_personsUID */(resultset1 int)The key to the problem is that I want to use the variable@l_personsUID to name then temp table. The name of the temp tableshould be ##Test1table9842 not ##Test1table.Thanks for you help.Billy
I need to create a Stored Procedure in SQL server which passes 6 input parameters Eg:
ALTER procedure [dbo].[sp_extract_Missing_Price] @DisplayStart datetime, @yearStart datetime, @quarterStart datetime, @monthStart datetime, @index int as
Once I declare the attributes I need to create a Temp table and update the data in it. Creating temp table
Once I have created the Temp table following query I need to run
SELECT date FROM #tempTable WHERE #temp.date NOT IN (SELECT date FROM mytable WHERE mytable.date IN (list-of-input-attributes) and index = @index)
The above query might return null result or a date .
In case null return output as "DataNotMissing" In case not null return date and string as "Datamissing"
hi,I wish to create a temporary table who's name is dynamic based on theargument.ALTER PROCEDURE [dbo].[generateTicketTable]@PID1 VARCHAR(50),@PID2 VARCHAR(50),@TICKET VARCHAR(20)ASBEGINSET NOCOUNT ON;DECLARE @DATA XMLSET @DATA = (SELECT dbo.getHistoryLocationXMLF (@PID1, @PID2) as data)CREATE TABLE ##@TICKET (DATA XML)INSERT INTO ##@TICKET VALUES(@DATA)ENDis what i have so far - although it just creates a table with a name of##@TICKET - which isn't what i want. I want it to evaluate the name.any ideas?
I could deploy across my environment, which is a mix of 2008R2/2012 servers, to give some information on log files. Running into a silly issue right off the bat. The table that DBCC LogInfo() conjures out of magic is different between the two. In 2012 it gained the RecoveryUnitID column. So I'm trying to write some logic to create a temp table based on which version is running. I would like to avoid a global temp table if possible. Here's what I've tried:
sp_executesql creates a table outside of the scope of my session: DECLARE @PrVers NVARCHAR(128) , @PrVersNum DECIMAL(10,2) , @StageTable NVARCHAR(1024) = N''
I need to create tables that are session specific. i.e When I login to a database i have to create a Table that can be accessed across all the procedures and triggers. When I log out the Tables should be droped. I understand that Local Temporary (#) tables can be created in annonymus block, but I need the tables to be created during login.
1. Example data with Diag Code = null ABCD1234ABCDEF1234567MEDI19921301
2. Example data with Diag Code <> null ABCD1234ABCDEF1234567MEDI19921301708.1
This runs fine in Access and if the Diag Code is null, Access does not care, it still puts all the other information together. Such as the examples above.
Well I tried doing this in a Stored Procedure and when I ran across a record that had a null value for the [Diag Code] it made the IndKey field Null for that record. If it was not null then it looked like Example 2.
What is the proper way of creating my unique field in SQL Server?
HiI tried the following from the help file...When you create or modify a unique index, you can set an option toignore duplicate keys. If this option is set and you attempt to createduplicate keys by adding or updating data that affects multiple rows(with the INSERT or UPDATE statement), the row that causes theduplicates is not added or, in the case of an update, discarded.For example, if you try to update "Smith" to "Jones" in a table where"Jones" already exists, you end up with one "Jones" and no "Smith" inthe resulting table. The original "Smith" row is lost because anUPDATE statement is actually a DELETE followed by an INSERT. "Smith"was deleted and the attempt to insert an additional "Jones" failed.The whole transaction cannot be rolled back because the purpose ofthis option is to allow a transaction in spite of the presence ofduplicates.But when I did it the original "Smith" row was not lost.I am doing something wrong or is the help file incorrect.Dan
I am looking for a way to create a temp column who's value would take the value of another column and prepend a value like this to it "domain". This is the Select statement I currently have:
SELECT Nalphakey,[Last Name],[First Name],[User Name],[E-mail Address],[User Name] FROM SkywardUserProfiles
I understand how to create an Alias for an existing column, but not sure how to do what I am wanting. I also understand that the following will do the concatenation that I need, but I have only used it in an UPDATE query, and I'm not sure how to use it within a Select statement or if that's even possible:
I want to create a local temporary table in execute sql task and and want to use the same in Data flow task as source table.
I follow the following steps to achieve this:
01. Created a new SSIS package 02. Create a connection string to "(local)/." server, "tempdb" database 03. Set the "RetainSameConnection" property value to "TRUE" 04. Set the "DelayValidation" to "TRUE", where ever I found this property 04. In Control Flow I added to items a. Execute SQL Task b. Data Flow Task 05. For "Execute SQL task" I set the connection to "tempdb" 06. I written the following query Create table #transfer_CompaniesToProcess_tbl ( companyID int not null ) GO 07. In Data Flow task I added "OLE DB Source" and "OLE DB Destination" 08. In "OLE DB Source" I changed the "Data access mode:" to "SQL command" 09. In "SQL command text:" I entered "select * from #transfer_CompaniesToProcess_tbl" 10. When I clicked on the "OK" button; I ended with following error:
TITLE: Microsoft Visual Studio ------------------------------ Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Invalid object name '#transfer_CompaniesToProcess_tbl'.".
------------------------------ ADDITIONAL INFORMATION: Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap) ------------------------------ BUTTONS: OK ------------------------------
I gone through the following article and it seems I missed some thing. http://blogs.conchango.com/jamiethomson/archive/2006/11/19/SSIS_3A00_-Using-temporary-tables.aspx
I need to find a method to assign unique Shift IDS to rows that correspond to a single shift. For instance, the first shift would begin on the first row when shift_start flag is turned on, and end on the third row when shift_end flag is turned on.
I am building a view to be used to drill down into a Lightswitch app I'm building and to drive this I want to use a view based off the selection of that value several other values will be given to the user to choose from related to the first selection. I've created a view using the following statement:
SELECT DISTINCT TOP (100) PERCENT ARSFamily, ARS_Index FROM dbo.csr_standards_cmsars ORDER BY ARSFamily
but the results come back with ALL the records of the source table (509 rows) when there should have only been 29 rows returned (the appropriate number of families or unique groups). The index is necessary to have Lightswitch use the view as a data source.what I'm doing wrong here?
To create a unique constraint on a multiple nullable column, we need to create a view with not null column and and then create a unique index on that view.
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.
i am inserting something into the temp table even without creating it before. But this does not give any compilation error. Only when I want to execute the stored procedure I get the error message that there is an invalid temp table. Should this not result in a compilation error rather during the execution time.?
--create the procedure and insert into the temp table without creating it. --no compilation error. CREATE PROC testTemp AS BEGIN INSERT INTO #tmp(dt) SELECT GETDATE() END
only on calling the proc does this give an execution error
Simple example: declare @tTable(col1 int) insert into @tTable(col1) values (1) select * from @tTable
Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.
But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.
Message: OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.
If on the source I have a new column, the script generated by SqlPackage.exe recreates the table on the background with moving the data into a temp storage. If the table is big, such approach can cause issues.
Example of the script is below: in the source project I added columns [MyColumn_LINE_1] and [MyColumn_LINE_5].
Is there any way I can make it generating an alter statement instead?
BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET XACT_ABORT ON; CREATE TABLE [dbo].[tmp_ms_xx_MyTable] ( [MyColumn_TYPE_CODE] CHAR (3) NOT NULL,
[Code] ....
The same script is generated regardless the table having data or not, having a clustered or nonclustered PK.
The SP UserPersist_GetByCriteria does a "SELECT * FROM tbl_User WHERE gender = @Gender AND culture = @Culture", so why am I receiving this error when both tables have the same structure?
The error is being reported as coming from UserPersist_GetByCriteria on the "SELECT * FROM tbl_User" line.
