REPLACE Funtion For Multiple Values In SP/Reporting Services
Running into a problem with trying to pull multiple values in a stored procedure.
Ran Profiler for trace on what variable is coming back as when running in Reporting Services.
Profiler shows this:
exec apGetCompanysByRep @Product = N'Absorbent Pads,Airedale Terrier'
Trying to run Replace function but not quite getting the syntaxt correct. The stored procedure will work but when running report will often get unclosed quotation marks.
What I have:
Set @Product=
Replace(@Product, '''' + ',' + @product + '''', (@Product + '''' + ',' + ' ' + '''' + @product + ''''))
This is probably a little too much coding but I've tried several ways.
In the SP the code is as follows:
CREATE PROCEDURE [dbo].[apGetCompanysByRep]
@Magazine varchar(6) = null
, @DirectsMagazine varchar(6) = null
, @Category varchar(50) = null
, @SubCategory varchar(50) = null
--, @FirstName varchar(30)
, @LastName varchar(30)= null
, @Product varchar(1100)
AS
declare @SQL varchar(2000)
Set @Product=Replace(@Product, '''' + ',' + @product + '''', (@Product + '''' + ',' + ' ' + '''' + @product + ''''))
set @SQL = '
Thanks for any assistance anyone can give.
View Complete Forum Thread with Replies
Related Forum Messages:
Replace Funtion
how do i write a replace function that will replace a certain character with a return key (ie what happens when we do Ctrl+return key in SQL Enterprise table... so that the rest of the cell data in the column is on the next line?! SELECT REPLACE(tasks, '/', '????') AS EXPR1 FROM log_descriptions what should ???? be?
View Replies !
How To Replace &&<br&&> In Reporting Services ?
Hi there ! I was wondering how do I replace the <br> tags with vbcrlf in reporting services ? I have a dataset that returns a string of the following :- "Telephone<br>Mail<br>Email" I am trying to figure out how to modify the stored procedure so that it automatically breaks where it is needed in the reporting services. Any ideas ? Thanks ! Bernard
View Replies !
Removing Duplicated Filter Values In Reporting Services...
We are using Reporting Services to design and deliver reports. We have a report that we need to filter via Client, however the filter populates with multiple instances of the same client. We are pulling in the values for the filter from a dataset. We have users that are associated with clients. So multiple users have the same client. In this filter drop-down when it populates from the dataset it is populating the same client multiple times based upon how many users are associated with that client. All we need is each client a single time. We can do this by creating another dataset, but we would like to do it with the current dataset instead of making a trip back to the server. Your help is appreciated. Thanks!
View Replies !
Limitation Of The Size Of Values Of Parameters In Reporting Services
Is there any limitation on the size of the parameter string in sql server reporting services Report . I am unable to pass the parameter value to the Report from the web application if the value exceed 1600 chars. I am passing the value in the Web application using "Post" method so this is not a limitation of query string Max size 0f 2040 chars. TIA ,
View Replies !
Reporting Services With Multiple Language
Hi I want to show my reports in different language . For this i have added a parameter Language on the report that contain all the language . Now i want to change all the captions of Report and parameter list in selected language . How i will do it ? @mit
View Replies !
Reporting Services 2005 Multi-value Parameter Possible Values Search
Hi there, I have a problem with multi-value parameter in ReportingServices2005, hope to find a solution here. if I use ordinary parameter in a report(multi-value checkbox is not selected), after deployment I can click on drop down list and type possible value of that parameter using keyboard(it will be selected from what i typed). if I use multi-value parameter somewhy it doesn't respond to anything i type from my keyboard. Why is that? What should i do to make that multi-value parameter select possible result from what i type?
View Replies !
Using Reporting Services For Multiple Clients (databases)
Hi! We have multiple databases (1 per client) and we want to use reporting services for ad-hoc reporting. Our authentication is done by using client's database so security is custom. What is the best way to set this up? Is it possible to somehow allow users/admins to use only their database and never see any others? I know we can install a separate instance of RS and do that but I am wondering if there is a better way to run multiple clients doing different reports using exclusively their databases without having to create a new instance. Thanks
View Replies !
Reporting Services Creates Multiple Datasets With The Same Name....
When I am working in BIDS writing MDX for a report if I have any query parameters they create datasets behind them which is fine. The problem I run into is the fact that multiple datasets are created with the same name for some reason. This then throws and error when I try to run the report. The problem is I can't see all the datasets unless I view the XML code and delete them manually. Does anyone have ANY insight into why this is?
View Replies !
Saving Multiple Reports From Reporting Services
Hi everyone, I'm very new to SQL and SQL Reporting Services. However I've been given a task. How do I save multiple reporting services reports onto a predetermined location on a users local pc? Are there tools/applications that can be used to push files onto the users local pc? The application uses .NET but they've created reports using SQL Reporting Services. The user will select which report they want (out of 2). My task is to figure out how can we save those reports that they have selected onto their pc? Thanks in advance
View Replies !
Reporting Services:Can't Display Parameterized Multiple Charts
Hi, On a report I have created two datasets (X and Y), with same fields and report parameters, just the queries pulls data for two different regions. Now, I am trying to display the data on two different charts,on the same report, that would receive the same number and type of parameters. The first chart for region 'X' (dataset 'X')displays correct data; but the second chart doesn't display data for region 'Y' (dataset 'Y') though the parameters supplied is same.Please help.
View Replies !
Reporting Services:Can't Display Parameterized Multiple Charts
Hi, On a report I have created two datasets (X and Y), with same fields and report parameters, just the queries pulls data for two different regions. Now, I am trying to display the data on two different charts,on the same report, that would receive the same number and type of parameters. The first chart for region 'X' (dataset 'X')displays correct data; but the second chart doesn't display data for region 'Y' (dataset 'Y') though the parameters supplied is same.Please help.
View Replies !
Using Multiple Stored Procedures In One Report (SQL Server Reporting Services)
Hi all, I want to use 2 stored procedures in one report, but I could not find what is the way of doing this. I searched this subject on the intrernet, and I found some solutions. However, they used stored procedures in one stored procedure and used in one report. This is not the thing which I want to do, I want only do using 2 stored procedures in one report at the same time. Thanks, for now...
View Replies !
Exporting Data From Reporting Services Into Multiple Excel Worksheets
Hi, Please help me. I need to export around 1 million records to excel. As we know that there is a limit of approx 65,000 rows in one worksheet so the exporting functionality is failing. One way is that I should be able to configure the Reporting Service in such a way so that once 65,000 records gets populated in the first worksheet, then the rest 35,000 records gets popultaed in the second worksheet. I am not able to find out how this can be achieved in Reporting Services. Please help me in this. Thanks in advance. Regards Raman
View Replies !
SQL Reporting Services Issue Using Multiple Parameters Where Data Is Passed In From A Stored Procedure
I have an issue with using multiple parameters in SQL Reporting services where data is passed in from a stored procedure When running the report in design mode - I can type in a parameter sting and it runs fine In the report preview screen I can select single parameters by ticking the drop down list and again it runs fine as soon as I tick more than one I get an error An error occurred during local report processing Query execution failed for data set €˜data' Must declare the scalar variable '@parameter' Some info... The dataset 'workshop' is using a sproc to return the data string? I get multiple values back fine in the sproc using this piece of code (select [str] from iter_charlist_to_table( @Parameter, DEFAULT) )) I have report parameters set to Multi-Value Looking through the online books it says... You can define a multivalued parameter for any report parameter that you create. However, if you want to pass multiple parameter values back to a query, the following requirements must be satisfied: The data source must be SQL Server, Oracle, or Analysis Services. The data source cannot be a stored procedure. Reporting Services does not support passing a multivalued parameter array to a stored procedure. The query must use an IN clause to specify the parameter. Am I trying to do the impossible ?
View Replies !
Login Screen Appears Multiple Times In Reporting Services Using Integrated Windows Security
We have a RS 2005 instance connected to an AS 2005 cube. It is configured for URL access using the default Windows security. This is fine as it is not an Internet-facing application. I have added users in RS and they have custom security roles in the AS cube; they are able to interact with the cube via Excel. When these users go to the RS page, they are able to see everything on the site but are prompted multiple times for log in. The login screen only disappears when you hit cancel. They are then able to generate reports but have to keep hitting cancel on the persistent login screen. The only user that does not have this problem is a Local Administrator on the server that is hosting the RS. I have tried explicitly adding the URL (http://server/reports) to the Intranet zone (the site was being automatically identified as Intranet but did this anyway). Also, in desperation, I tried changing the User Authentication setting to "automatically log in with ID and password" but this did not solve the problem. Any ideas on how to resolve this?
View Replies !
Migrate From 32bit 2000 Reporting Services To 2005 64bit Reporting Services
Hello, I am trying to migrate my reports from SQL server 2000 reporting services 32bit to 2005 64bit. I am following the migration steps that MS specified. Restored my Reportserver and ReportserverTempDB databases Then I was using the configure Report services to upgrade these databases but I always end up getting the follwoing exception when I run the upgrade on the "Database Setup" configuration for 'ReportServerTempDB' database System.Data.SqlClient.SqlException: Could not locate entry in sysdatabases for database 'ReportServerTempDBTempDB'. No entry found with that name. Make sure that the name is entered correctly. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(String connectionString, String script) at ReportServicesConfigUI.SqlClientTools.SqlTools.ApplyScript(String connectionString, String script) It's driving me crazy, why is it looking for 'ReportServerTempDBTempDB' in the catalog instead of 'ReportServerTempDB'? Is it possible to migrate from 32bit to 64bit? Any help is appreciated
View Replies !
Compatibility Of SQL Express Reporting Services With SQL 2000 Reporting Services
I'm attempting to obtain a cost effective solution for my existing customers to develop reports on their SQL 2000 Server installations using their Reporting Services 2000. With products like Visual Basic.NET 2003 becoming almost impossible to obtain, I have at least one customer who is running into a dead end. One option possibly is the SQL Express with Advanced Services download, which has Reporting Services. My questions are as follows: Can the report designer component of SQL Express Reporting Services be configured to connect to an external database (which would happen to be a SQL 2000 database) to establish its datasets? Does the resultant designed report end up in an RDL file? If the customer published this report via the Reporting Services 2000 Report Manager, would the report be able to run? Sorry for asking a question like this that I could probably answer on my own, but my customer needed this answer yesterday. I have scoured the web and microsoft sites - and posted a question on the official SQL Reporting Services cateogy ... in an attempt to answer the basic question of how to design reports for Reporting Services 2000 in the absence of Visual Basic.NET 2003 (or other .NET 2003 tools) with no success. Thanks to anyone who can help. -- Mark
View Replies !
Replace Negative Values
hi, In the result of a function in my query, there are negative numbers. How do I replace them with a 0 or is there a function like ISNULL that replaces the values that are negative? thanks, maarten
View Replies !
To Replace Number Values
'000000' is a string with 6 charaters Requirement: I want to Replace 0 with 1. Replace 0 to 1 at position 3 output= '001000' Replace 0 to 1 at position 5 output= '000010'
View Replies !
Replace The NULL Values
Thank u Chirag.... but i should display all the records of that table not only price column. consider the column price in titles table in pubs database. If the price of any record is not defined it should be retrieved as -9999.
View Replies !
Find And Replace Values
I was wondering how to do a find and replace with SQL? Would I use a SET statement? I need to find a specific value and replace all of the results with a different value. Thanks for all responses.
View Replies !
Replace Values Of Null
select distinct case when item is null then replace(item,null,'-') else itemend end as item from itemstable i want to replace all null values to '-'...but still i m getting null values.. syntax is correct still not getting results.. can anyone help? thanks.
View Replies !
How To Replace Values In Different Columns At The Same Time
HI, I AM HAVING A TABLE WHICH HAS INCREMENTAL COLUMNS,WHERE COLUMNS GETS ADDED EVERY MONTH TO THE TABLE AND THE TABLE THEN CONTAINS PREVIOUS MONTH AND PRESENT MONTH DATA ABOUT CUSTOMERS ,DETAILS AND TRANSACTIONS. THE PROBLEM WITH THIS DATA IS ,IF THE CUSTOMER IS NEW ,THEN IN PREVIOUS MONTHS HIS INFORMATION IS NULL,WHICH HAVE TO BE CODED HAS "NOT PRESENT". NOW, HOW DO WE CONVERT ALL THE PREVIOUS COLUMNS FOR A PARTICULAR CUSTOMER HAS NULL AT THE SAME TIME ?. HERE IS HOW THE PROC WRITTENED FOR IT GOES :- DROP PROCEDURE DE_NAT CREATE PROCEDURE DE_NAT AS BEGIN DECLARE @MONMIN1 NVARCHAR(100),MON NVARCHAR(100),@YEAR NVARCHAR(100) , @MONYEAR NVARCHAR(100) SET @MONMIN1 = DATENAME((MONTH),DATEADD(MONTH,-1,GETDATE())) SET @MON = MONTH(GETDATE()) SET @YEAR = YEAR(GETDATE()) SET @MONYEAR = @MON + @YEAR EXEC('select A.CUSTOMERS,B.*,CAST(A.RFM_40D AS FLOAT) AS R40 INTO TSD_' + @MONYEAR + ' from TSD_20 A LEFT OUTER JOIN SD20 ' + @MONMIN1 + ' B ON A.CUSTOMERS = B.CUSTOMER') END THIS PROC JUST ADDS THE PRESENT MONTHS DATA TILL LAST MONTHS DATA. BUT IF A CUSTOMER IS NEW, THEN HOW DO I REPLACE THE NULL VALUES FOR THE PREVIOUS DATA TO 'NOT PRESENT' FOR EG :- IF THERE IS A NEW CUSTOMER ,HOW DO WE CHANGE :- CUSTOMERS ERTYYTRE RTYUUYTR TYUIIUYT QWERREWQ DFGHHGFD ----------- ---------- ---------- ---------- ----------- ---------- 101023 <NULL> <NULL> <NULL> <NULL> 1.0 102022 1.0 1.62.3 3.4 4.5 NOW, AS YOU CAN SEE, THAT FOR CUSTOMERS = '101023'. THE COLUMN DFGHHGFD IS, THIS MONTHS DATA , I WANT TO CHANGE ALL NULL VALUES PRESIDING IT AS "INACTIVE" CAN I CHANGE , ALL COLUMNS FROM NULL TO "INACTIVE" , AT THE SAME TIME. ? AS NEXT MONTH, AGAIN THE COLUMNS IS GONNA INCREASE WHICH WILL AGAIN CAUSE A PROBLEM . PLS TELL ME A METHOD , SO THAT I CAN DO THE NEEDFUL.
View Replies !
Replace Multiple LIKEs
I have a query below that performs horribly:@KeywordOne char(6),@KeywordTwo char(6),@KeywordThree char(6),@KeywordFour char(6),@KeywordFive char(6)SELECTc.SomethingFROMdbo.tblStuff cWHEREc.SomeColumnName = 0AND (c.Keyword LIKE '%' + @KeywordOne + '%' OR @KeywordOne is Null)AND (c.Keyword LIKE '%' + @KeywordTwo + '%' OR @KeywordTwo is Null)AND (c.Keyword LIKE '%' + @KeywordThree + '%' OR @KeywordThree isNull)AND (c.Keyword LIKE '%' + @KeywordFour + '%' OR @KeywordFour = isNull)AND (c.Keyword LIKE '%' + @KeywordFive + '%' OR @KeywordFive = isNull)The contents of column c.Keyword looks like this:Row1: 123456,321654,987987,345987Row2:Row3: 123456,987987etc.What can I do to get this to perform reasonably? I cannot use full-textsearch.Any help is appreciated.lq
View Replies !
Multiple Replace In Expression
Guys ... got a puzzle kinda problem am stuck up with so asking for your help (after all .. you are the gurus)... What i need to do is append Src. to any column name that comes in the expression. Here we go ... and the last query is part of what I was coming up with ... not exactly working :) Create Table ColumnNames(ColumnName varchar(256))goInsert into ColumnNamesSelect 'name'UNIONSelect 'dbid'UNIONSelect 'sid'UNIONSelect 'mode'UNIONSelect 'status'UNIONSelect 'status2'UNIONSelect 'crdate'UNIONSelect 'reserved'UNIONSelect 'category'UNIONSelect 'cmptlevel'UNIONSelect 'filename'UNIONSelect 'version'goDeclare @Expression varchar(256)Select @Expression = 'dbid = 1 AND cmptlevel = 100' Select replace(@Expression,ColumnName,'Src.'+ColumnName) from ColumnNames Where charindex('Src.',replace(@Expression,ColumnName,'S rc.'+ColumnName)) <> 0
View Replies !
Replace Multiple Spaces With One Space?
I need to map several columns of data from one database to anotherwhere the data contains multiple spaces (once occurance of a variablenumber or spaces) that I need to replace with a single space. Whatwould be the most efficient way to do this? I am using SQL2K. I wasthinking a function since I know of no single Transact-SQL commandthat can accomplish this task.
View Replies !
Replace Instance Of Multiple Char(32)
I have several fileds in a sql table which holds some text and a hell of a lot of white spaces (char(32)) which is causing some problems when I show the data on the front-end What I want to do is use sql replace function to replace the char(32). However, because there is text I can't do a simple replace as the text will become just one word ! Can anyone tell me how to loop through the field to find the char(32), where the char(32) is greater than say 5 sapces and then replace this with just one single space E.g. a field in a table contains the following : "my text field and loads of spaces then some more text with loads of spaces " I want to replace the char(32) with just one space so it looks like this : "my text filed and loads of spaces then some more text with loads of spaces" Thanks for your help
View Replies !
Search Replace Across Multiple Queries
Hey guys, In my DB i have hundreds on queries setup for all different reporting purposes.. We have just changed they way our system handles costing and are moving from a average cost function to a standard cost function.. This in turns, means that i need to replace any reports where i use the average cost field and replace it with the standard cost field. Is it possible, that i can do a full search and replace over all my queries, looking for a specific field name and replacing it with something else. I am dreading the idea of opening each individual queries and checking if it exists.. If anyone knows of software or someway to write a SP to accomadate this, please let me know. I would be most grateful. Thanks Scotty
View Replies !
Replacing Multiple Strings Using The REPLACE Function
I'm would like to replace all occurrences of "99999" and "-99999" with "" in a column using SSIS. I can use the REPLACE function in a Derived Column to replace one of those strings, for example: REPLACE(mycolumn,"99999",""). Or to replace both I could use REPLACE(REPLACE(mycolumn,"-99999",""),"99999",""). This seems kind of cumbersome and would get very complicated if I were replacing more strings with "". I'm guessing there is a better way. Can anyone help me out? Thanks, Ridium
View Replies !
Nested REPLACE Statements Using Multiple Columns
I need to pass 3 column values and one Formula string into 4 replace statements and output the result in one column. Nesting them in the usual way doesn't seem to work as that only allows for one column. My table consits of four columns...PF (numeric), Hours (numeric), TotalNumber INT, and Formula (nvatchar) My function needs to search and replace the Formula column for instances of all the three number columns and output the formula as a mathmatical formula rather than a string. Here is what I have so far which works fine if all three columns have a value, but if only one is null then it will retrun NULL and not the other two values. FUNCTION GetFormula (@numPF NUMERIC(10,2), @numHours NUMERIC(10,2), @intTotalNumber INT, @strFormula nvarChar(200)) RETURNS nvarchar(200) AS BEGIN DECLARE @strExpression nvarchar(200) SELECT @strExpression=REPLACE(@strFormula, 'TotalNumber',@intTotalNumber) SELECT @strExpression=REPLACE(@strExpression, 'PF',@numPF ) SELECT @strExpression=REPLACE(@strExpression, 'Hours',@numHours ) RETURN @strExpression END Many Thanks
View Replies !
Retrieving Multiple Values From One Field In SQL Server For Use In Multiple Columsn In Reports
I am trying to create a report using Reporting Services. My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report. I can currently get one value but how to get the information I need to be able to use in my reports. So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped. Any help would be appreciated. Thanks. I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work. What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields. As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem Thanks for the suggestions that were made, I apoligize for not making the problem clearer. Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting. --Pulls the Service Opportunity SELECT cs.value AS "Service Opportunity" FROM Cstudent cs INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid WHERE ca.name = 'Service Opportunity' --Pulls the Number of Hours SELECT cs.value AS 'Number of Hours' FROM Cstudent cs INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid WHERE ca.name ='Num of Hours' --Pulls the Person Grade Level SELECT cs.value AS 'Grade' FROM Cstudent cs INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid WHERE ca.name ='Grade' --Pulls the Person Number, First and Last Name and Grade Level SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade" FROM student s INNER JOIN cperson cs ON cs.personid = s.personid INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid WHERE cs.value =(SELECT cs.value AS 'Grade' WHERE ca.attributeid = cs.attributeid AND ca.name='Grade')
View Replies !
Multiple Columns With Different Values OR Single Column With Multiple Criteria?
Hi, I have multiple columns in a Single Table and i want to search values in different columns. My table structure is col1 (identity PK) col2 (varchar(max)) col3 (varchar(max)) I have created a single FULLTEXT on col2 & col3. suppose i want to search col2='engine' and col3='toyota' i write query as SELECT TBL.col2,TBL.col3 FROM TBL INNER JOIN CONTAINSTABLE(TBL,col2,'engine') TBL1 ON TBL.col1=TBL1.[key] INNER JOIN CONTAINSTABLE(TBL,col3,'toyota') TBL2 ON TBL.col1=TBL2.[key] Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record. I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information. for e.g.; i was thinking to concatinate both fields like col4= ABengineBA + ABBToyotaBBA and in search i use SELECT TBL.col4 FROM TBL INNER JOIN CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1 ON TBL.col1=TBL1.[key] Result = 1 row But it don't work in following scenario col4= ABengineBA + ABBCorola ToyotaBBA SELECT TBL.col4 FROM TBL INNER JOIN CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1 ON TBL.col1=TBL1.[key] Result=0 Row Any idea how i can write second query to get result?
View Replies !
Adding Values To A Parameter That Can Take Multiple Values
If I have a Select statement like this in my C# code: Select * From foods Where foodgroup In (@foodgroup) And I want @foodgroup to have these values ... "meat", "dairy", fruit", what is the correct way to add the parameter? I tried meat, dairy, fruit 'meat', 'dairy', 'fruit' but neither worked. Is this possible?
View Replies !
'Insert Into' For Multiple Values Given A Table Into Which The Values Need To Go
Please be easy on me...I haven't touched SQL for a year. Why given; Code Snippet USE [Patients] GO /****** Object: Table [dbo].[Patients] Script Date: 08/31/2007 22:09:29 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Patients]( [PID] [int] IDENTITY(1,1) NOT NULL, [ID] [varchar](50) NULL, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [DOB] [datetime] NULL, CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED ( [PID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF do I get Msg 102, Level 15, State 1, Line 3 Incorrect syntax near ','. for the following; Code Snippet INSERT INTO Patients (ID, FirstName,LastName,DOB) VALUES ( '1234-12', 'Joe','Smith','3/1/1960'), ( '5432-30','Bob','Jones','3/1/1960'); Thank you, hazz
View Replies !
SqlDataReader - Pulling Multiple Values Into Multiple Variables
Hello all,I'm trying to request a number of URLS (one for each user) from my database, then place each of these results into a separate string variables. I believed that SqlDataReader could do this for me, but I am unsure of how to accomplish this, or if I am walking down the wrong road. The current code is below (the section in question is in bold), please ignore the fact that I'm using MySQL as the commands work in the same way. public partial class main : System.Web.UI.Page{ String UserName; String userId; String HiveConnectionString; String Current_Location; ArrayList Location; public String Location1; public String Location2; public String Location3; //Int32 x = 0; private void Page_Load(object sender, EventArgs e) { if (User.Identity.IsAuthenticated) { UserName = Membership.GetUser().ToString(); userId = Membership.GetUser().ProviderUserKey.ToString(); HiveConnectionString = "Database=hive;Data Source=localhost;User Id=hive_admin;Password=West7647"; using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(HiveConnectionString)) { // Map Updates MySql.Data.MySqlClient.MySqlCommand Locationcmd = new MySql.Data.MySqlClient.MySqlCommand( "SELECT Location FROM tracker WHERE Location = IsOnline = '1'"); Locationcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Locationcmd.Connection = conn; conn.Open(); MySql.Data.MySqlClient.MySqlDataReader LocationReader = Locationcmd.ExecuteReader(); while (LocationReader.Read()) { Location1 = LocationReader.GetString(0); //Location2 = LocationReader.GetString(1); // This does not work.. } LocationReader.Close(); conn.Close(); // IP Display MySql.Data.MySqlClient.MySqlCommand Checkcmd = new MySql.Data.MySqlClient.MySqlCommand( "SELECT UserName FROM tracker WHERE PKID = ?PKID"); Checkcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Checkcmd.Connection = conn; conn.Open(); object UserExists = Checkcmd.ExecuteScalar(); conn.Close(); if(UserExists == null) { MySql.Data.MySqlClient.MySqlCommand Insertcmd = new MySql.Data.MySqlClient.MySqlCommand( "INSERT INTO tracker (PKID, UserName, IpAddress, IsOnline) VALUES (?PKID, ?Username, ?IpAddress, 1)"); Insertcmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress; Insertcmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName; Insertcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Insertcmd.Connection = conn; conn.Open(); Insertcmd.ExecuteNonQuery(); conn.Close(); } else { MySql.Data.MySqlClient.MySqlCommand Updatecmd = new MySql.Data.MySqlClient.MySqlCommand( "UPDATE tracker SET IpAddress = ?IpAddress, IsOnline = '1' WHERE UserName = ?Username AND PKID = ?PKID"); Updatecmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress; Updatecmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName; Updatecmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Updatecmd.Connection = conn; conn.Open(); Updatecmd.ExecuteNonQuery(); conn.Close(); } } } } Can anyone advise me on what I should be doing (even if its just a "you should be using this command) if this is not correct? In fact any pointers would be nice !Thanks everyone!
View Replies !
To Access Reporting Sevices (Report Manager, Reporting Web Services), Does One Has To Be An Admin On The Machine
Hi There, Our DBA has installed reporting services on a server and now in order to access the report manager, one has to be an Admin on that Server. I am guessing that there is a mistake in the configuration of Reporting Services. Usually it should allow anybody who was added to the roles in the properties section of the Report Manager, right? I have also added the users to the DB.. Also I am using Windows Authentication to access Report Catalog items (Reporting Services is installed on Server2) from a web Application(deployed on Server1) and displaying the report using report viewer. For some reason, server1 has to be in an Admin role on Server2 to access the report catalog/report. This is kinda strange for me as I don't want everybody to be an Admin on Server2. Can anybody please point in the right direction? Thanks.
View Replies !
SQL 2005 SP2 Reporting Services And Window SharePoint Services V3 Integration Config Issue
Hi, I have just install SQL 2005 SP2 and trying to get Window SharePoint Services V3 integrated with SQL 2005 SP2 reporting services. In SharePoint Central Administration, I select the Reporting Services Integration page and have setup the Report Server Web Service URL and Authentication Mode. I then goto Grant database access, specify the SQL server name, get promted for a username and password that has access SQL Reportserver and get the following error "The group name could not be found" Does anyone have any ideas? Thanks
View Replies !
Seeking An SQL Funtion ~ Do You Know IT
Hi All, Please can you help. i a looking for a funtion that will be able to view all the child (many) records in a one to many relation ship and make a list of the parents names....is there such a function??? Example In the one to many contacts db below, if this mystery function ran for "bill" it would say his "parents records" are Gary and Mac Parent Table~ Child Table Gary Bill Tom Harry Mac Bill Jef Tod Charles Les Helen any help here would be much apprecaited kind regards Tony UK
View Replies !
Reg Split Funtion
Hi, Is it possible to split the following value in sql server ? I have the value like 25 Email Accounts,50 Email Accounts in my sqlserver database. Here i need only the numeric value .ie 25,50.Is it possible? can any one give me the solution .. I am using ASP.Net and C# backend is SQL Server 2000. Thanks and Regards Arul
View Replies !
BCP Funtion Problem
Hi, I'm using the function bcp to transfer data from my table to a data file. When I use it at the dos command prompt, no problem everything works really well. But if I use query analyser I have an error: unable to open bcp host data-file. Here's the line: xp_cmdshell 'bcp table.owner.inventaire out c:1est.txt' The problem is not with xp_cmdshell because I can call simpler executables in query analyser.
View Replies !
Reporting Services - Analysis Services - Displaying Dimension Members As Columns
I think I've seen a similar post on a blog or on the forums - but it seems like this should be possible - I have an MDX query - that works fine in SQL Enterprise Manager, and has my dimension members on columns, and my measures on the rows. When I try the same query in Reporting Services, I get the error: "The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension.. Parameter name: mdx (MDXQueryGenerator)" Although it works when you pivot the view, I really need my data presented with the members on the columns and the measures on the rows. Another forum post mentioned using the SQL 9.0 driver, but I can't see this listed anywhere (the only one I see is the .NET framework Data Provider for Microsoft Analysis Services). Here's what my query looks like - SELECT { [Time].[Month].&[2006-09-01T00:00:00] , [Time].[Month].&[2006-10-01T00:00:00], [Time].[Month].&[2006-11-01T00:00:00], [Time].[Month].&[2006-12-01T00:00:00] } on COLUMNS, { [Measures].[Unique Users], [Measures].[UU Pct 1], [Measures].[UU Pct 2], } ON ROWS FROM [Cube] Any ideas? Thanks, Arjun
View Replies !
|
TRACKER
