Temp Table Created In The SSMS,but SSRS Gives Error
Feb 8, 2008
Hi ALL,
The sub report has stored procedure which uses ##temp table,to get it's results.
Stored procedure code is:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[rpt_FAP_Profile_claim] @TABLE_NM varchar(50),@MBR_ID varchar(50)
AS
SET NOCOUNT ON
DECLARE @SQL_TXT varchar(8000)
SET @SQL_TXT = ' SELECT * FROM STEP_PROFILE_CLAIM_' + @TABLE_NM + ' c WHERE c.MBR_ID = '''+ @MBR_ID + ''''
--SET @SQL_TXT = 'SELECT TOP 100 * FROM CLAIM'
EXEC(@SQL_TXT)
When I run this in SSMS gives error::-
Msg 208, Level 16, State 1, Line 1
Invalid object name 'STEP_PROFILE_CLAIM_765032'.
(1 row(s) affected)
How do I make it work and get the result in my sub report.Is any properties needs to be set in the SSMS or SSRS sub report or any other modificatons?.Or is some thing wrong in the temp table?.Plz help.
Thanks in advance.
View 1 Replies
ADVERTISEMENT
Dec 16, 2007
Below is a simplified table & dataset to illustrate a problem I'm experiencing with a more complex one.
Code Block
create table #test(
recno smallint PRIMARY KEY,
value decimal (18,2))
insert into #test values (1, 3.57)
insert into #test values (2, 5.32)
insert into #test values (3,6.29)
insert into #test values (4, 9.25)
insert into #test values (5, 0.84)
Method 1: I tried inserting rows from #test into a temp table (#table) as follows
Code Block
declare @n as nvarchar(3)
set @n = 1
while @n <= (select count(recno) from #test)
begin
exec ('
insert into ##table
select *,
originalrecno = (select recno from #test where recno = '+@n+')
from #test'
)
set @n = @n + 1
end
However, this yields an error message:
Code Block
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name '##table'.
Note - you can comment out the insert into ##table line above to view the results that I'm trying to put into ##table.
Method 2: next I tried explicitly creating ##table & rerunning the loop containing the insert
Code Block
create table ##table (
recno smallint,
value decimal (18,2),
originalrecno smallint)
declare @n as nvarchar(3)
set @n = 1
while @n <= (select count(recno) from #test)
begin
exec ('
insert into ##table
select *,
originalrecno = (select recno from #test where recno = '+@n+')
from #test'
)
set @n = @n + 1
end
This worked - it inserted the data from the select statements in the loop into ##table.
Question - why won't method 1 work?
View 6 Replies
View Related
May 18, 2006
Hi all,
I have SQL Server Management Studio Express (SSMS Express) and SQL Server 2005 Express (SS Express) installed in my Windows XP Pro PC that is on Microsoft Windows NT 4 LAN System. My Computer Administrator grants me the Administror Privilege to use my PC. I tried to use SQLQuery.sql (see the code below) to create a table "LabResults" and insert 20 data (values) into the table. I got Error Messages 102 and 156 when I did "Parse" or "Execute". This is my first time to apply the data type 'decimal' and the "VALUES" into the table. I do not know what is wrong with the 'decimal' and how to add the "VALUES": (1) Do I put the precision and scale of the decimal wrong? (2) Do I have to use "GO" after each "VALUES"? Please help and advise.
Thanks in advance,
Scott Chang
///////////--SQLQueryCroomLabData.sql--///////////////////////////
USE MyDatabase
GO
CREATE TABLE dbo.LabResults
(SampleID int PRIMARY KEY NOT NULL,
SampleName varchar(25) NOT NULL,
AnalyteName varchar(25) NOT NULL,
Concentration decimal(6.2) NULL)
GO
--Inserting data into a table
INSERT dbo.LabResults (SampleID, SampleName, AnalyteName, Concentration)
VALUES (1, 'MW2', 'Acetone', 1.00)
VALUES (2, 'MW2', 'Dichloroethene', 1.00)
VALUES (3, 'MW2', 'Trichloroethene', 20.00)
VALUES (4, 'MW2', 'Chloroform', 1.00)
VALUES (5, 'MW2', 'Methylene Chloride', 1.00)
VALUES (6, 'MW6S', 'Acetone', 1.00)
VALUES (7, 'MW6S', 'Dichloroethene', 1.00)
VALUES (8, 'MW6S', 'Trichloroethene', 1.00)
VALUES (9, 'MW6S', 'Chloroform', 1.00)
VALUES (10, 'MW6S', 'Methylene Chloride', 1.00
VALUES (11, 'MW7', 'Acetone', 1.00)
VALUES (12, 'MW7', 'Dichloroethene', 1.00)
VALUES (13, 'MW7', 'Trichloroethene', 1.00)
VALUES (14, 'MW7', 'Chloroform', 1.00)
VALUES (15, 'MW7', 'Methylene Chloride', 1.00
VALUES (16, 'TripBlank', 'Acetone', 1.00)
VALUES (17, 'TripBlank', 'Dichloroethene', 1.00)
VALUES (18, 'TripBlank', 'Trichloroethene', 1.00)
VALUES (19, 'TripBlank', 'Chloroform', 0.76)
VALUES (20, 'TripBlank', 'Methylene Chloride', 0.51)
GO
//////////Parse///////////
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '6.2'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'VALUES'.
////////////////Execute////////////////////
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '6.2'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'VALUES'.
View 7 Replies
View Related
Jan 21, 2002
Hi,
I want to create a nested SP which will dump out the results of All Temp Tables that are currently created in the session. The purpose of this is for createing a generic debugging SP which will write the contents of all temp tables to a debug table (when a certain flag is set).
I need to know how to:
- Get a list of all temp tables created
- Get a list from each temp table of the columns created.
Hope someone can help!
Cheers,
Andrew
View 1 Replies
View Related
Feb 12, 2008
Are they unique to a user/session? Like if 2 users simultaneously run the stored procedure?
TIA!
View 13 Replies
View Related
Aug 29, 2007
Hello,
I have created a package that check if a table exist otherwise it drop it, then it creates a new one (both with SSIS execute SQL task).
Then a Flow task run some transformation SSIS to load the table just created.
The problem is that when I run the package an error occur telling me that the table does not exist (if runs for the first time and in this case the table has not created yet).
How can I run a package that create a table and then Load it.
Thank
View 1 Replies
View Related
Nov 4, 2004
I'm creating the temporary table below...
CREATE TABLE #TempTest(P_ID int not null,
Type_1 varchar(1000) null default('N/A'),
Type_2 varchar(1000) default('N/A'),
Type_3 varchar(1000) default('N/A'),
Type_4 varchar(1000) default('N/A'),
Type_5 varchar(1000) default('N/A'),
Type_6 varchar(1000) default('N/A'),
Type_7 varchar(1000) default('N/A'),
Type_8 varchar(1000) default('N/A'),
Type_9 varchar(1000) default('N/A'),
Type_10 varchar(1000) default('N/A'),
Type_11 varchar(1000) default('N/A'),
Type_12 varchar(1000) default('N/A'),
Type_13 varchar(1000) default('N/A'))
When I attempt to dump data to it I get the followig error...
The total row size (13052) for table '#TempTest' exceeds the maximum number of bytes per row (8060). Rows that exceed the maximum number of bytes will not be added.
Is there any way to get around this as the field I'm taking the data from is varChar(1000) and I don't have any problems with that?
Thanks in Advance
View 2 Replies
View Related
Oct 10, 2006
hello friends!!
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'.
T.I.A
View 9 Replies
View Related
May 22, 2006
Hi all,
I got an error message 156, when I executed the following code:
////--SQLQueryParent&Child.sql---////////
Use newDB
GO
----Creating dbo.Person as a Parent Table----
CREATE TABLE dbo.Person
(PersonID int PRIMARY KEY NOT NULL,
FirstName varchar(25) NOT NULL,
LastName varchar(25) NOT NULL,
City varchar(25) NOT NULL,
State varchar(25) NOT NULL,
Phone varchar(25) NOT NULL)
INSERT dbo.Person (PersonID, FirstName, LastName, City, State, Phone)
SELECT 1, "George", "Washington", "Washington", "DC", "1-000-1234567"
UNION ALL
SELECT 2, "Abe", "Lincoln", "Chicago", "IL", "1-111-2223333"
UNION ALL
SELECT 3, "Thomas", "Jefferson", "Charlottesville", "VA", "1-222-4445555"
GO
----Creating dbo.Book as a Child table----
CREATE TABLE dbo.Book
(BookID int PRIMARY KEY NOT NULL,
BookTitle varchar(25) NOT NULL,
AuthorID int FOREIGN KEY NOT NULL)
INSERT dbo.Book (BookID, BookTitle, AuthorID)
SELECT 1, "How to Chop a Cherry Tree", 1
UNION ALL
SELECT 2, "Valley Forge Snow Angels", 1
UNION ALL
SELECT 3, "Marsha and ME", 1
UNION ALL
SELECT 4, "Summer Job Surveying Viginia", 1
UNION ALL
SELECT 5, "Log Chopping in Illinois", 2
UNION ALL
SELECT 6, "Registry of Visitors to the White House", 2
UNION ALL
SELECT 7, "My Favorite Inventions", 3
UNION ALL
SELECT 8, "More Favorite Inventions", 3
UNION ALL
SELECT 9, "Inventions for Which the World is Not Ready", 3
UNION ALL
SELECT 10, "The Path to the White House", 2
UNION ALL
SELECT 11, "Why I Do not Believe in Polls", 2
UNION ALL
SELECT 12, "Doing the Right Thing is Hard", 2
GO
---Try to obtain the LEFT OUTER JOIN Results for the Parent-Child Table
SELECT * FROM Person AS I LEFT OUTER JOIN Book ON I.ID=P.ID
GO
////---Results---//////
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'NOT'.
////////////////////////////////////////////////////
(1) Where did I do wrong and cause the Error Message 156?
(2) I try to get a Parent-Child table by using the LEFT OUTER JOIN via the following code statement:
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'NOT'.
Can I get a Parent-Child table after the error 156 is resolved?
Please help and advise.
Thanks,
Scott Chang
View 9 Replies
View Related
May 27, 2014
I try to modify a code to create a temp table but got an error.
Declare @headerid Int
Declare @Providerid Int
BELOW CODE IS WORKING:
SELECT @headerid=HEADERID,@Providerid=PROVIDERID
FROM CLAIM WITH(NOLOCK) WHERE EDI_CLM_NUM= 123
[code]...
Error:
Incorrect syntax near '='.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
View 5 Replies
View Related
Jun 22, 2007
Here I am creating a temp table with $ summations that I can later join with an employees table that I'm dumping into a flat file.
select
e.employee_no,
sum(p.fit) as sumfit,
sum(p.fica) - sum(p.medicare) as sumfica,
sum(p.medicare) as sumedic,
sum(p.fit_earnings) as pearnings,
sum(p.fit_earnings) as tearnings
into #earntable
from employees as e
left outer join pay_summary as p on e.employee_no =p.employee_no
where e.employee_no = 817 and
dateadd(d, 0, datediff(d, 0, p.dated)) between '20061231'and '20070401'
group by e.employee_no
select * from #earntable
When I go to look at the contents of the #earntable with the above select, I get this:
ODBC error 214 Procedure expects parameter '@handle' of type 'int'.
(42000)
The datatypes I'm trying to select into the temp table are all
numeric 12 except employee_no char 10.
What am I missing? A drop table statement?
View 6 Replies
View Related
Oct 12, 2015
In my sp I'm have an Insert statement. In the event that after the insert @@rowcount = 0 I'm throwing an error with RAISEERROR.
This is fine, however, in my CATCH block I'm referencing a local temp table but getting an error that it no longer exists.
My question is, does throwing an error drop any #temp tables? I thought they were in scope for the session?
View 4 Replies
View Related
Apr 9, 2008
Hi,
In a nutshell I'm unable to connect to SSRS within SSMS yet I can connect to SSIS, SSAS and the Database Engine within SSMS from the same named server instance ("BOODEMO")
"SQL Server 2005 Surface Area Configuration" reports that service status SSRS (DEMO) is running.
The only other problem that I have found which may be linked is that within "Reporting Services Configuration Manager BOODEMO" there is a red circled crossed next to "Report Manager Virtual Directory", yet this application has just recently reported that I successfully created a virtual directory.
Any help or constructive guidance would be greatly appreciated.
Thanks in advance,
Kieran.
View 8 Replies
View Related
Apr 9, 2014
Below are my temp tables
--DROP TABLE #Base_Resource, #Resource, #Resource_Trans;
SELECT data.*
INTO #Base_Resource
FROM (
SELECT '11A','Samsung' UNION ALL
[Code] ....
I want to loop through the data from #Base_Resource and do the follwing logic.
1. get the Resourcekey from #Base_Resource and insert into #Resource table
2. Get the SCOPE_IDENTITY(),value and insert into to
#Resource_Trans table's column(StringId,value)
I am able to do this using while loop. Is there any way to avoid the while loop to make this work?
View 2 Replies
View Related
May 31, 2007
Creating a temporary table in stored procedure and using a sql query to insert the data in temp. table.I am facing the error as :
String or binary data would be truncated.The statement has been terminated.
The procedure i created is as :
ALTER PROCEDURE fetchpersondetails
AS
CREATE Table #tempperson (personID int,FirstName nvarchar(200),LastName nvarchar(250),title nvarchar(150),Profession nvarchar(200),StreetAddress nvarchar(300),
StateAddress nvarchar(200),CityAddress nvarchar(200),CountryAddress nvarchar(200),ZipAddress nvarchar(200),Telephone nvarchar(200),Mobile nvarchar(200),
Fax nvarchar(200),Email nvarchar(250),NotesPub ntext,Affiliation nvarchar(200),Category nvarchar(200))
Insert into #tempperson
SELECT dbo.tblperson.personID, ISNULL(dbo.tblperson.fName, N'') + ' ' + ISNULL(dbo.tblperson.mName, N'') AS FirstName, dbo.tblperson.lname AS LastName,
dbo.tblperson.honor AS Title, dbo.tblperson.title AS Profession, dbo.tblperson.street + ' ' + ISNULL(dbo.tblperson.suite, N'') AS StreetAddress,
dbo.tblperson.city AS cityaddress, dbo.tblperson.state AS stateaddress, dbo.tblperson.postalCode AS zipaddress,
dbo.tblperson.Phone1 + ',' + ISNULL(dbo.tblperson.Phone2, N'') + ',' + ISNULL(dbo.tblperson.Phone3, N'') AS Telephone,
dbo.tblperson.mobilePhone AS mobile, dbo.tblperson.officeFax + ',' + ISNULL(dbo.tblperson.altOfficeFax, N'') + ',' + ISNULL(dbo.tblperson.altOfficeFax2,
N'') AS Fax, ISNULL(dbo.tblperson.Email1, N'') + ',' + ISNULL(dbo.tblperson.Email2, N'') + ',' + ISNULL(dbo.tblperson.Email3, N'') AS Email,
dbo.tblperson.notes AS NotesPub, dbo.tblOrganizations.orgName AS Affiliation, dbo.tblOrganizations.orgCategory AS Category,
dbo.tblCountry.countryNameFull AS countryaddress
FROM dbo.tblperson INNER JOIN
dbo.tblOrganizations ON dbo.tblperson.orgID = dbo.tblOrganizations.orgID INNER JOIN
dbo.tblCountry ON dbo.tblperson.countryCode = dbo.tblCountry.ISOCode
please let me know the solurion of this error.
View 2 Replies
View Related
Jun 13, 2006
I recent installed a named instance of SSRS 2005 on a remote server. The server has a side by side install of SSRS 2000 and SSRS 2005, with the SSRS 2005 being a named instance. The server also has SSL and MS Sharepoint Portal Server installed.
When I try and connect to the SSRS 2005 instance with my client install of Mangement Studio, I get the error message:
"The underlying connection was closed: Could not establish a trust relationship for the SSL/TLS secure channel. (System.Web.Services)
Additional Information: The remote certificate is invalid according to the validation procedure. (System)."
Obviously, this is a SLL issue, but, not being a security guru, I have no clue on how to fix this. I do know the certificate is valid, not expired and properly working. Both installs of SSRS are set up using the SSL certificate. Also, I am not connecting to SSIS with Management Studio, as I heard there is a bug connecting to both SSRS and SSIS at the same time.
Anyone have any ideas?
Thanks!!
Jeff
View 6 Replies
View Related
Nov 17, 2004
Hi all,
Looking at BOL for temp tables help, I discover that a local temp table (I want to only have life within my stored proc) SHOULD be visible to all (child) stored procs called by the papa stored proc.
However, the following code works just peachy when I use a GLOBAL temp table (i.e., ##MyTempTbl) but fails when I use a local temp table (i.e., #MyTempTable). Through trial and error, and careful weeding efforts, I know that the error I get on the local version is coming from the xp_sendmail call. The error I get is: ODBC error 208 (42S02) Invalid object name '#MyTempTbl'.
Here is the code that works:SET NOCOUNT ON
CREATE TABLE ##MyTempTbl (SeqNo int identity, MyWords varchar(1000))
INSERT ##MyTempTbl values ('Put your long message here.')
INSERT ##MyTempTbl values ('Put your second long message here.')
INSERT ##MyTempTbl values ('put your really, really LONG message (yeah, every guy says his message is the longest...whatever!')
DECLARE @cmd varchar(256)
DECLARE @LargestEventSize int
DECLARE @Width int, @Msg varchar(128)
SELECT @LargestEventSize = Max(Len(MyWords))
FROM ##MyTempTbl
SET @cmd = 'SELECT Cast(MyWords AS varchar(' +
CONVERT(varchar(5), @LargestEventSize) +
')) FROM ##MyTempTbl order by SeqNo'
SET @Width = @LargestEventSize + 1
SET @Msg = 'Here is the junk you asked about' + CHAR(13) + '----------------------------'
EXECUTE Master.dbo.xp_sendmail
'YoMama@WhoKnows.com',
@query = @cmd,
@no_header= 'TRUE',
@width = @Width,
@dbuse = 'MyDB',
@subject='none of your darn business',
@message= @Msg
DROP TABLE ##MyTempTbl
The only thing I change to make it fail is the table name, change it from ##MyTempTbl to #MyTempTbl, and it dashes the email hopes of the stored procedure upon the jagged rocks of electronic despair.
Any insight anyone? Or is BOL just full of...well..."stuff"?
View 2 Replies
View Related
Oct 22, 2007
I am transferring data from oracle and getting below error message.
I using 4 data flow tasks with in a single control flow and all the 4 tasks quueries same table but populates data in to different sql tables based on the where contidion
[OLE DB Source 1 [853]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "ORA-01652: unable to extend temp segment by 64 in tablespace TEMP ".
View 4 Replies
View Related
Nov 13, 2014
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''
[code]....
View 9 Replies
View Related
Feb 18, 2008
I writing a unit test which has one stored proc calling data from another stored proc. Each time I run dbo.ut_wbTestxxxxReturns_EntityTest I get a severe uncatchable error...most common cause is a trigger error. I have checked and rechecked the columns in both of the temp tables created. Any ideas as to why the error is occurring?
--Table being called.
ALTER PROCEDURE dbo.wbGetxxxxxUserReturns
@nxxxxtyId smallint,
@sxxxxxxxxUser varchar(32),
@sxxxxName varchar(32)
AS
SET NOCOUNT ON
CREATE TABLE #Scorecard_Returns
(
NAME_COL varchar(64),
ACCT_ID int,
ACCT_NUMBER varchar(10),
ENTITY_ID smallint,
NAME varchar(100),
ID int,
NUM_ACCOUNT int,
A_OFFICER varchar(30),
I_OFFICER varchar(30),
B_CODE varchar(30),
I_OBJ varchar(03),
LAST_MONTH real,
LAST_3MONTHS real,
IS int
)
ALTER PROCEDURE dbo.ut_wbTestxxxxReturns_EntityTest
AS
SET NOCOUNT ON
CREATE TABLE #Scorecard_Returns
(
NAME_COL varchar(64),
ACCT_ID int,
ACCT_NUMBER varchar(10),
ENTITY_ID smallint,
NAME varchar(100),
ID int,
NUM_ACCOUNT int,
A_OFFICER varchar(30),
I_OFFICER varchar(30),
B_CODE varchar(30),
I_OBJ varchar(03),
LAST_MONTH real,
LAST_3MONTHS real,
IS int
)
INSERT #Scorecard_Returns(
NAME_COL ,
ACCT_ID
ACCT_NUMBER ,
ENTITY_ID,
NAME,
ID,
NUM_ACCOUNT,
A_OFFICER,
I_OFFICER,
B_CODE,
I_OBJ ,
LAST_MONTH
LAST_3MONTHS,
IS
)
EXEC ISI_WEB_DATA.dbo.wbGetxxxxxcardUserReturns
@nId = 1,
@sSUser = 'SELECTED USER',
@sUName = 'VALID USER'
View 4 Replies
View Related
Oct 2, 2015
Because of a performance problem, somebody has given me a script which came from a SSRS report.
The code as supplied does not work when multivalued parameters are used.
Testing/tuning/building in SSMS is far superiour than in SSRS. So that's why I like to use SSRS for building the code/script/sql-statement.
Offcourse parameters have to be set correctly. (That is no problem).
Splitting of the multivalued parameter is not a problem either.
View 5 Replies
View Related
Oct 17, 2008
Have installed SQL 2008 on Windows 2008 and have come across a strange issue.
Â
When connecting to the Report Server cvia SSMS to enable "My Reports" (for example) the Report Server Properties are greyed out and we cannot alter them. This occurs when logged on both locally & remotely to the Win 2008 server.
Â
Interestingly, we have another (XP Pro) box with SQL 2008 on it. If we connect to the Win 2008 SQL report server from the XP machine's SSMS, then we have full control and can access and change the report server properties on the Win 2008 server.Â
Â
The relevant domain accounts are in the Admin group on the Win 2008 server and the XP Server so we are confused as to why we cannot edit the Win 2008 report server properties locally.
View 5 Replies
View Related
May 19, 2006
Hi all,
In MyDatabase, I have a TABLE dbo.LabData created by the following SQLQuery.sql:
USE MyDatabase
GO
CREATE TABLE dbo.LabResults
(SampleID int PRIMARY KEY NOT NULL,
SampleName varchar(25) NOT NULL,
AnalyteName varchar(25) NOT NULL,
Concentration decimal(6.2) NULL)
GO
--Inserting data into a table
INSERT dbo.LabResults (SampleID, SampleName, AnalyteName, Concentration)
VALUES (1, 'MW2', 'Acetone', 1.00)
INSERT €¦ ) VALUES (2, 'MW2', 'Dichloroethene', 1.00)
INSERT €¦ ) VALUES (3, 'MW2', 'Trichloroethene', 20.00)
INSERT €¦ ) VALUES (4, 'MW2', 'Chloroform', 1.00)
INSERT €¦ ) VALUES (5, 'MW2', 'Methylene Chloride', 1.00)
INSERT €¦ ) VALUES (6, 'MW6S', 'Acetone', 1.00)
INSERT €¦ ) VALUES (7, 'MW6S', 'Dichloroethene', 1.00)
INSERT €¦ ) VALUES (8, 'MW6S', 'Trichloroethene', 1.00)
INSERT €¦ ) VALUES (9, 'MW6S', 'Chloroform', 1.00)
INSERT €¦ ) VALUES (10, 'MW6S', 'Methylene Chloride', 1.00)
INSERT €¦ ) VALUES (11, 'MW7', 'Acetone', 1.00)
INSERT €¦ ) VALUES (12, 'MW7', 'Dichloroethene', 1.00)
INSERT €¦ ) VALUES (13, 'MW7', 'Trichloroethene', 1.00)
INSERT €¦ ) VALUES (14, 'MW7', 'Chloroform', 1.00)
INSERT €¦ ) VALUES (15, 'MW7', 'Methylene Chloride', 1.00)
INSERT €¦ ) VALUES (16, 'TripBlank', 'Acetone', 1.00)
INSERT €¦ ) VALUES (17, 'TripBlank', 'Dichloroethene', 1.00)
INSERT €¦ ) VALUES (18, 'TripBlank', 'Trichloroethene', 1.00)
INSERT €¦ ) VALUES (19, 'TripBlank', 'Chloroform', 0.76)
INSERT €¦ ) VALUES (20, 'TripBlank', 'Methylene Chloride', 0.51)
GO
A desired Pivot Table is like:
MW2 MW6S MW7 TripBlank
Acetone 1.00 1.00 1.00 1.00
Dichloroethene 1.00 1.00 1.00 1.00
Trichloroethene 20.00 1.00 1.00 1.00
Chloroform 1.00 1.00 1.00 0.76
Methylene Chloride 1.00 1.00 1.00 0.51
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I write the following SQLQuery.sql code for creating a Pivot Table from the Table dbo.LabData by using the PIVOT operator:
USE MyDatabase
GO
USE TABLE dbo.LabData
GO
SELECT AnalyteName, [1] AS MW2, AS MW6S, [11] AS MW7, [16] AS TripBlank
FROM
(SELECT SampleName, AnalyteName, Concentration
FROM dbo.LabData) p
PIVOT
(
SUM (Concentration)
FOR AnalyteName IN ([1], , [11], [16])
) AS pvt
ORDER BY SampleName
GO
////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I executed the above-mentioned code and I got the following error messages:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TABLE'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AnalyteName'.
I do not know what is wrong in the code statements of my SQLQuery.sql. Please help and advise me how to make it right and work for me.
Thanks in advance,
Scott Chang
View 6 Replies
View Related
Mar 17, 2008
hi all
Using Sql Server 2005 / Reporting Services
I created a view in sql server management Studio but can't see it in Reporting Services.
I appear to have the same security permissions?
what else am I missing?
thanks
jewel
View 1 Replies
View Related
Nov 13, 2007
I love SQL Server Reporting Services (SSRS), but it doesnt let me name the tabs when exporting to Excel. The feature is well documented as not being available.
So I figured I can use the cell(1,1) on each tab to contain the tab name, and have an SSIS job poll a file share, open the file, read the cell, rename tabs, rename the file, and then email out the file. Polling the directory and emailing is a piece of cake, but I can't find any way to rename a tab without having Excel loaded on the SERVER. Not to mention instantiating an entire instance of Excel on the SERVER every time I want to do this.
I want to leverage SSRS, and preserve the formatting of the excel file, which is why I don't just dump the data in with SSIS to begin with.
Anybody have any ideas?
View 4 Replies
View Related
Jan 3, 2008
hi,
I am new to SSRS. I am doing some report models for Ad-hoc reports. Im my database i have some users and their roles according to my requirement. User should only see the particular reports if he have privilege. how to customize...
User will access Report Builder to do adhoc report.
For example
I have 5 report Models
rptModel1
rptModel2
rptModel3
rptModel4
rptModel5
In my data base there are 3 users.
user1 have privilege to view rptModel1, rptModel3
user2 have privilege to view rptModel4, rptModel5
user3 have privilege to view rptModel2, rptModel5
When Particular user access the report builder, There i want to show only that particular Report models...
is there any posibilities to do this please Help me.
Thanks in Advance...
-Kannan
kannan1017@gmail.com
View 5 Replies
View Related
Jan 31, 2008
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50. insert into Table values('arun's',20) My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?
View 3 Replies
View Related
Jun 24, 1999
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.
thanks!!
Toni Eibner
View 2 Replies
View Related
May 12, 2015
In Master tabel i have these date datas
2015-05-10
2015-05-11
2015-05-12
SO when i try to load from  Master table to parent and child table i am using using expresssion likeÂ
select B.ID,A.* FROM FLATFILE_INVENTORY AS A JOIN DMS_INVENTORY AS B ONÂ
A.ACDealerID=B.DMSDEALERID AND A.StockNumber=B.STOCKNUMBER ANDÂ
A.InventoryDate=B.INVENTORYDATE AND A.VehicleVIN=B.VEHICLEVIN
WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE()) Â and convert(date,B.Ftpdate) = convert(date,getdate()) ;
If i use this Expression i am getting the current system date data's only  from Master table to parent and child tables.
My Problem is If i do this in my local sserver using the above Expression if i loaded today date and if need to load yesterday date i can change my system date to yesterday date and i can run this Expression.so that yeserday date data alone will get loaded from Master to parent and  child tables.
If i run this expression to remote server  i cannot change the system date in server.
while using this Expression for current date its loads perfectly but when i try to load yesterday data it takes current date date only not the yesterday date data.
What is the Expression on which ever  date i am trying load in  the master table  same date need to loaded in Parent and child table without changing the system Date.
View 10 Replies
View Related
Apr 14, 2015
Is there a way to show tables in alphabetical order when you expand the table node of a database from inside SSMS?
View 3 Replies
View Related
Aug 5, 2015
I get the following error when I try to expand the sql server log in the management studio. My login is in the securityadmin server role.
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
A severe error occurred on the current command. The results, if any, should be discarded. (Microsoft SQL Server, Error: 0)
View 5 Replies
View Related
Apr 23, 2007
===================================
System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)
===================================
any ideas?
this is actually a restore from a BAK file saved from the live server. we copied the bak file over and placed it on the test server hard drive, now we are trying to restore this using SSMS
View 4 Replies
View Related
