Hi Everyone,
In C# file:
bool abc; (it is working)
But in sql file: (bool is not working. What datatype should use for bool in the sql file?)
CREATE TABLEsb_payment_history (
sb_dispensing bool NULL <================ not working because bool is not known.
)
GO
Thanks,
May
You should use the bit datatype in your SQL table.
Hi
I am new to all SQL Related. I am trying to create a datasource to our old database we used in the company to write reports on the data. when I create a new data source and enter all required info, when testing the connection, i get a reply that I do not have the necessary permissions to use this object, although my account is specified as an administrator? can somebody please explain this to me.
I am moving this to the SQL Server Security forum.|||From where you are connecting (trying) to this new database?
Is it from Reporting services or any other application?
|||it is saved on my computer.|||You might check the permission for the user when trying to create this D-source connection, if the user do not have corresponding database privileges then it will fail.
Hi
I am new to all SQL Related. I am trying to create a datasource to our old database we used in the company to write reports on the data. when I create a new data source and enter all required info, when testing the connection, i get a reply that I do not have the necessary permissions to use this object, although my account is specified as an administrator? can somebody please explain this to me.
I am moving this to the SQL Server Security forum.|||From where you are connecting (trying) to this new database?
Is it from Reporting services or any other application?
|||it is saved on my computer.|||You might check the permission for the user when trying to create this D-source connection, if the user do not have corresponding database privileges then it will fail.
Hi
I am new to all SQL Related. I am trying to create a datasource to our old database we used in the company to write reports on the data. when I create a new data source and enter all required info, when testing the connection, i get a reply that I do not have the necessary permissions to use this object, although my account is specified as an administrator? can somebody please explain this to me.
Hi Hanel O,
Try posting this query to the MSDN SQL Server Data Access Forum:
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1&bcsi_scan_A9C07E6225287F19=z5cf+RkCipiHryeey+QUUQYAAAAmrYMB&bcsi_scan_filename=ShowForum.aspx
Also here is the MSDN SQL Server Forum main page, where you will find many SQL forums dedicated to different aspects of Microsoft SQL Server:
http://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=19&SiteID=1
Also try looking in SQL Server Books Online, which comes as part of the SQL Server install package.
Hope this helps,
Frank
Hi,
I need help......
I am trying to create a report whose datasource will be different instances of the same database.So both the databases contains the same tables with same schema but the thing is that teh data is different.I have to collect the daat from different instances of the same table a(as they contains different different data) and using the collection to generate the report.
It's very very urgent ...plz reply ...
Thanx in advance
HI,
I dont thnk this is possible to do(as far as i know you cant connect a datasource to different database at runtime and also you cant change datasource at runtime). Now to solve your problem i can think of these solutions -
1. Create two datasources(connecting to two different database), and in your report create two datasets connecting to each one of the datasources. Similarly create two instances of tables (or whatever control you want to display) and connect controls to dataets. Now on runtime show/hide the table based on data from which table you want to show.
This is not a verygood choice(actually very poor) and you can achieve this using two copies of same report connecting to different databases and by showing the appropriate report to user.
2. Better approch is to have two tables in the same database like a_1 and a_2 and pass a parametre to your sp specifying which table to be used. In SP you will have to create dynamic sql in which table name will be created based on the parametre passed.
Hope i am able to convey whats there in my mind....
|||Just came across this blog, it looks like we can dynamically change the connection string(did'nt tried though) -
http://blogs.msdn.com/bimusings/archive/2006/07/20/673051.aspx
I know there are already a few threads on this topic but I wanted to create another one - and directly ask - how could this limitation possibly exists - and even more important - why is it still a problem in 2005?
I want to summarize a few of the solutions that have been listed here and elseware online. We've tried them all and they all have some kind of problem:
1) Create hidden field(s) and then reference that field from the header: Doesn't work on any pages where the field isn't printed. Header value will be blank in this case.
2) Create hidden column(s) in the data table and reference that from the header: Works, but again, if the table is showing on every page, the value will be blank. i.e. - if you have two or more tables stacked, you would have to add the columns to every one. A very sloppy and not practical solution.
3) Do it with custom code: I've seen this approach recomended but have never actually seen someone provide a working example. From what I understand, custom code can't access the datasets (I'm using 2000 - maybe 2005 is different here?)
4) Use a queried parameter to hold the value- then reference that in the header: This seems to be the best contender so far. It seems to work sometimes, but on several machines here, the report designer goes into some kind of infinite loop and crashes once the queried parameter is added.
If anyone has any other approaches that I'm not aware of, or has another idea, please post it here. Microsoft - this is a huge problem for many people. Is there any chance of a fix for this any time soon?
Thank you.
~H
did you find a solution ?
|||I am very interested in a solution, too.Although I have found a 5th option, which works quite well for my scenario - nevertheless I don't consider it being very elegant (at least with "allow null values" enabled).
5) Use "Available values: From query"
All
report parameters are hidden and passed via the URL. The parameter
value is an ID, but in the page header my customer would like to see
the corresponding description.
I have have defined the available values to come from a query and specified the value and label fields. Now I can access the parameter value in the page header like e.g.
Parameters!ProcessId.Label
There's one drawback: if you're using parameters having "allow null value" enabled this version gives you an error when openen the report having passed NULL for the parameter (I have to translate the message from german to english):The process ID parameter value is missing.
select ProcessId, Desc from Processes
union
select ProcessId=NULL, Desc=NULL from Processes
Although I have found a 5th option, which works quite well for my scenario - nevertheless I don't consider it being very elegant (at least with "allow null values" enabled).
5) Use "Available values: From query"
All
report parameters are hidden and passed via the URL. The parameter
value is an ID, but in the page header my customer would like to see
the corresponding description.
I have have defined the available values to come from a query and specified the value and label fields. Now I can access the parameter value in the page header like e.g.
Parameters!ProcessId.Label
There's one drawback: if you're using parameters having "allow null value" enabled this version gives you an error when openen the report having passed NULL for the parameter (I have to translate the message from german to english):The process ID parameter value is missing.
select ProcessId, Desc from Processes
union
select ProcessId=NULL, Desc=NULL from Processes
I know there are already a few threads on this topic but I wanted to create another one - and directly ask - how could this limitation possibly exists - and even more important - why is it still a problem in 2005?
I want to summarize a few of the solutions that have been listed here and elseware online. We've tried them all and they all have some kind of problem:
1) Create hidden field(s) and then reference that field from the header: Doesn't work on any pages where the field isn't printed. Header value will be blank in this case.
2) Create hidden column(s) in the data table and reference that from the header: Works, but again, if the table is showing on every page, the value will be blank. i.e. - if you have two or more tables stacked, you would have to add the columns to every one. A very sloppy and not practical solution.
3) Do it with custom code: I've seen this approach recomended but have never actually seen someone provide a working example. From what I understand, custom code can't access the datasets (I'm using 2000 - maybe 2005 is different here?)
4) Use a queried parameter to hold the value- then reference that in the header: This seems to be the best contender so far. It seems to work sometimes, but on several machines here, the report designer goes into some kind of infinite loop and crashes once the queried parameter is added.
If anyone has any other approaches that I'm not aware of, or has another idea, please post it here. Microsoft - this is a huge problem for many people. Is there any chance of a fix for this any time soon?
Thank you.
~H
did you find a solution ?
|||I am very interested in a solution, too.Although I have found a 5th option, which works quite well for my scenario - nevertheless I don't consider it being very elegant (at least with "allow null values" enabled).
5) Use "Available values: From query"
All
report parameters are hidden and passed via the URL. The parameter
value is an ID, but in the page header my customer would like to see
the corresponding description.
I have have defined the available values to come from a query and specified the value and label fields. Now I can access the parameter value in the page header like e.g.
Parameters!ProcessId.Label
There's one drawback: if you're using parameters having "allow null value" enabled this version gives you an error when openen the report having passed NULL for the parameter (I have to translate the message from german to english):The process ID parameter value is missing.
select ProcessId, Desc from Processes
union
select ProcessId=NULL, Desc=NULL from Processes
Although I have found a 5th option, which works quite well for my scenario - nevertheless I don't consider it being very elegant (at least with "allow null values" enabled).
5) Use "Available values: From query"
All
report parameters are hidden and passed via the URL. The parameter
value is an ID, but in the page header my customer would like to see
the corresponding description.
I have have defined the available values to come from a query and specified the value and label fields. Now I can access the parameter value in the page header like e.g.
Parameters!ProcessId.Label
There's one drawback: if you're using parameters having "allow null value" enabled this version gives you an error when openen the report having passed NULL for the parameter (I have to translate the message from german to english):The process ID parameter value is missing.
select ProcessId, Desc from Processes
union
select ProcessId=NULL, Desc=NULL from Processes
I have become a big fan of the datasets in Visual Studio 2005. I usually create the SQL for each method in the table adapter; however, I am wondering if there is any 'built-in' functions in the C files for sql injection prevention? I have read that using stored procedures is a good method for prevention. Should I be using SP rather than SQL within my methods in the data table?
Hey,
Welcome to the forum. The key is, do you have any dataset queries that take user input and passes it to a SQL query directly? Then you may want to beware if that is true. I like the use of SPs because SQL Server will cache them for increased performance.
In addition, you should be able to use @.Parameters within a SQL query, and pass in the values as parameters, which is safer than embedding the response directly into the SQL query.
|||
wendycarpenter@.polk-county.net:
I am wondering if there is any 'built-in' functions in the C files for sql injection prevention?
Use parameterized queries!
http://aspnet.4guysfromrolla.com/articles/030106-1.aspx
|||I am using parameterized queries - I've included a sample below. SQL Injection prevention has been "in the air" a lot lately and I guess I want to make sure that I am doing what I need to do to keep my customers' data safe.
INSERT INTO [Case] ([cseYear], [cseNumber], [cseStatus], [csePartI], [csePartII], [cseDestruction])
VALUES (@.cseYear, @.cseNumber, @.cseStatus, @.csePartI, @.csePartII, @.cseDestruction);
select SCOPE_IDENTITY()
|||
I appreciate the link. Maybe I wasn't specific enough. I am using the built in dataset (.xsd), so I am not creating any sql statement within my code - I strictly call the method within the datatable of the table adapter within the dataset. Most of the work is done within the auto generated code within Visual Studio 2005.
|||Hey,
OK, which used the parameter approach I believe. That should be OK; I've seen several projects where Adapters were used even with secure info. Note that table adapters can also use stored procedures in case you want to be sure.
sql
I want to create a summary page for one of my report, depending on a
parameter, I would like to filter the dataset before creating the summary
fields.
I cannot figure out how to perform this filtering, my approach has been to
insert a Table control and map it to my dataset, then try and do some
filtering via the properties. When i then put something like
=sum(Fields!Sales.Value)
into the header (such that it is shown only once, not repeated), I get the
sum for the full dataset, not the filtered one.
Please help, is there any easier way todo this?
Lastly, I can't filter at the SQL level as the dataset is being used to
populate a chart below the summary.
Advice welcome.
Please help
Taz
Put the filter directly on the table (by opening the table properties dialog and selecting the filter tab).
Small sample report is attached at the bottom.
-- Robert
============================================================
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="AdventureWorks">
<DataSourceReference>AdventureWorks</DataSourceReference>
<rd:DataSourceID>c2dc0e71-020b-48e3-9005-53286c923eb2</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Table Name="table1">
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<rd:DefaultName>textbox7</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Sum</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="value_1">
<rd:DefaultName>value_1</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!value.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Footer>
<Filters>
<Filter>
<Operator>NotEqual</Operator>
<FilterValues>
<FilterValue>=2</FilterValue>
</FilterValues>
<FilterExpression>=Fields!id.Value</FilterExpression>
</Filter>
</Filters>
<DataSetName>DataSet1</DataSetName>
<Top>0.125in</Top>
<Width>4.33333in</Width>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="id">
<rd:DefaultName>id</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!id.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="value">
<rd:DefaultName>value</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!value.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>id</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
</TableColumns>
<Height>0.75in</Height>
</Table>
</ReportItems>
<Height>1in</Height>
</Body>
<rd:ReportID>427ef1de-ff4a-42ab-96eb-9b4fbdc898e0</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select 1 as id, 5 as value union
select 2, 6 union
select 3, 7</CommandText>
<DataSourceName>AdventureWorks</DataSourceName>
</Query>
<Fields>
<Field Name="id">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>id</DataField>
</Field>
<Field Name="value">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>value</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>4.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
Data Set Access from a matrix
I am trying to create a schedule by room number report.I am using a matrix.The column group that I am using is room number.There is no row group.When you group by room it is only allowing me access to the first data item for that room.Even though there are other data items for that room I can not access them.I can not access all data items in that grouping expression.Is there a way to get unobstructed access to a dataset while working in a matrix and grouping?
If you are not getting back the correct data... I would use a table format and grouping... you can "trick" the look of the report to appear like a matrix..
Hope this helps..