Multi Value Integer Parameter

I am trying to create a report with a multi value integer parameter. I have tried

="SELECT * FROM vProjectRequestStatus WHERE ProjectRequestStatusCode IN (" + Parameters!StatusCode.Value + ")"

I get an error stating

An error occurred during local report processing.

An error has occurred during report processing.

Cannot set the command text for data set 'ProjectRequestStatus'.

Error during processing of the CommandText expression of dataset 'ProjectRequestStatus'.

Has anybody worked with integers?

Thanks,

Fred

Why not just :

SELECT * FROM vProjectRequestStatus WHERE ProjectRequestStatusCode IN (@.StatusCode)

That should work fine. Just set up you parameter as an integer, and multi value.

BobP

|||

If Bobp's solution doesn't work, you can try putting the Join function around your parameter value. It might not be passing the parameters correctly. Something like this:

Code Snippet

="SELECT * FROM vProjectRequestStatus WHERE ProjectRequestStatusCode IN (" + Join(Parameters!StatusCode.Value, ",") + ")"

|||

When I try that, it errors out when I enter more than one value separated by commas when running the dataset. It works if I only enter one value.

The error is: Conversion failed when converting the nvarchar value '2, 3' to data type int.

When I preview the report I receive no errors but no data is displayed except in the page header and the first group header. (There is a 2nd and 3rd group) It does not matter whether I enter one or more values in the parameter. There is data in the Document Map which allows you to drill down to group 3 data.

Any ideas why there is no data?

Fred

|||

I just tried this solution by putting the join function in the expression. Like the solution above, I get the same results in the Preview. No data except in Group Header 1 and the Document Map allows me to drill down to Group 3.

I checked the Visibility flag in each cell and row and the whole table. All Hidden properties are False.

Fred

|||

Can you post the Parameter RDL snippet?

BobP

|||I found my problem. One of the groups did have the Visibility Hidden property set to True.

Multi Table Source

I am wondering how I can create an OLE DB Source component that can store a multi-table DataSet object. Is this something that is possible or do I need some custom object to do this? I'm sure I can create a multi-table destination object and create sources for each data table needed however, I need to get the data for 5 tables and do this about 30K times. I'm thinking this approach will perform better.

Here is what I've been trying to get working. (Note there is only one parameter that all the queries use - @.keyName)

SELECT * FROM Table1
WHERE (Key = ?)

SELECT * FROM Table2
WHERE (Key = ?)

SELECT * FROM Table3
WHERE (Key = ?)

SELECT * FROM Table4
WHERE (Key = ?)

SELECT * FROM Table5
WHERE (Key = ?)

TIA

Ian

You can have more than one OLE DB source on a given data flow. From there you can merge/union records as required.|||

Does that mean I should use a separate source for each table then merge them into one DataSet Destination? (Sorry, I'm new to SSIS)

A single procedure/statement returning multiple tables sounds more efficient, is this not possible?

|||

enizin wrote:

Does that mean I should use a separate source for each table then merge them into one DataSet Destination? (Sorry, I'm new to SSIS)

A single procedure/statement returning multiple tables sounds more efficient, is this not possible?

A SQL statement doesn't return a table. It returns a result set. Either write a SQL statement that selects from all of your tables and does the necessary joins or unions and then use that statement in an OLE DB source, or you can use an OLE DB source for each table -- which will have to be merged together to get one "result set."|||

Sorry, I'm used to referring to data tables within ADO.NET DataSets...

In the Management Studio, if I run this set of statements against the AdventureWorks database I can get a "dataset" containing each result set - all of which have different columns.

SELECT * FROM HumanResources.Employee WHERE EmployeeId = ?

SELECT * FROM HumanResources.EmployeeAddress WHERE EmployeeId = ?

SELECT * FROM HumanResources.EmployeeDepartmentHistory WHERE EmployeeId = ?

SELECT * FROM HumanResources.EmployeePayHistory WHERE EmployeeId = ?

It sounds like this wouldn't work in SSIS because one source cannot contain multiple result sets without performing a union as it can only contain one set of columns.

The reason for needing the data like this is I need to add/update/delete rows to/from each of these tables then save them to my destination database. For my purposes it sounds like using the multiple source option will be the best route.

Thanks for your help.

Multi Table Query

Does anyone know how to create a query using tables from different sql server database's? looking for the simplest solution.

I have two databases in sql server. Both are in the same "server registration"
How do i reference a table in another database?

Do i do something like this?

SELECT Database1.Table1.Fields, Database2.Table1.Fields
FROM Database1.table1.PKField INNER JOIN Database2.table1.FKField;You may try this

SELECT Database1.dbo.Table1.Fields,
Database2.dbo.Table1.Fields FROM
Database1.dbo.table1.PKField INNER JOIN
Database2.dbo.table1.FKField|||you might also want to look at the OPENQUERY funtion. buddu's suggestion will work but you can generate a buch of unwanted I/O if the table on the remote server is big.|||You only need to do the full naming convension in the FROM clause

SELECT x.column1,
x.column2,
y.column1,
y.column3
FROM database1.dbo.tableX x
JOIN database2.dbo.tabley y
ON x.pk = y.pk|||can anyone help me please...
what query/syntax could i use for retrieving fields from various tables in Access for my one FORM.?
Ive tried using every possible codes but then it didn't work out.
SHould i declare diffrent recordsets for accesing this?

Multi Select Parameters?

In SQL Reporting Services, Is there anyway to create a parameter in
which a user can be prompted to choose multiple values from a list?
Ultimately, the multi-selected values would be passed to the Dataset
query as an "IN" qualification.
Example:
SELECT first_name, last_name
FROM employees
LEFT OUTER JOIN department ON
employees.dept_id = department.dept_id
WHERE dept_name IN ('Accounting', 'Sales', Support')
Crystal Reports has this functionality but I can not figure out how to
do this in MSRS.
If you know how to do this and would like to help a guy out, could you
include step by step answer or a link to a step by step tutorial?
Thanks!Here are a couple of options:
Option #1: Use a query expression as follows: ="select top 10 name, type
from sysobjects where type in (" & Parameters!Report_Parameter_0.Value &
")".
Option #2: Check
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=0a3800c4-4180-419c-a117-bfa21b2de099.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"The Whistler" <sharris@.SLeasynews.com> wrote in message
news:ltnrg01f5qf251e89c5vsrmcdn7fu01k4i@.4ax.com...
> In SQL Reporting Services, Is there anyway to create a parameter in
> which a user can be prompted to choose multiple values from a list?
> Ultimately, the multi-selected values would be passed to the Dataset
> query as an "IN" qualification.
> Example:
> SELECT first_name, last_name
> FROM employees
> LEFT OUTER JOIN department ON
> employees.dept_id = department.dept_id
> WHERE dept_name IN ('Accounting', 'Sales', Support')
> Crystal Reports has this functionality but I can not figure out how to
> do this in MSRS.
> If you know how to do this and would like to help a guy out, could you
> include step by step answer or a link to a step by step tutorial?
> Thanks!sql

Multi Row Insert

According to the CTP3 BOL the following code should work just fine, unfortunately is giving me the error message (below the code):

create table FamilyNames (

Id int identity,

[Name] varchar(30)

);

go

insert into FamilyNames values (

('Andersson'),

('Ben-Gan'),

('Carlsson'),

('Davidsen')

);

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.

Does anybody know why this code fails?

Hi,

Try

insert into FamilyNames values

('Andersson'),

('Ben-Gan'),

('Carlsson'),

('Davidsen');

Kind regards,

Wesley

|||Thanks.

Multi report subscription

Hello,

Is it possible to create a subscriptions for more than one report ? (I would like to pack in one e-mail three or four reports).

Thanks, Regards

Braulio

Sorry, this is not currently supported.

Multi Page Report Help

Hello,

I need to create a report that will use a dataset as its datasource and display each row in that dataset in its own page. For example, if the dataset returned three rows, then the report would be three pages long with each page only displaying the data from one row. Any help would be greatly appreciated!!Right click the Details section, go to Section Expert and click 'New Page After'.sql

Multi Language Form

Hi,

I have a table in an MS SQL 2000 database that represents fields on a form.

CREATE TABLE [dbo].[TagData](
[FieldName] [nvarchar](255),
[UserID] [int] NOT NULL,
[Data] [nvarchar](255)
)

A requirement has come up where some of these fields must contain Hebrew, or any other unicode character, data and some of the fields will be English. How can I go about saving and retrieving this information.

The current solution is a legacy Classic ASP application and I suspect I am going to have to redo this in ASP.Net

Thanks,
Leon

Since the data type in the table is nvarchar, you can store any unicode values.

You select/insert into the table as normal way of inserting the data there will not be any difference in doing DML operations.

Sample insert statement is as follows:

INSERT INTO TagData values(N'Sample Field', 10, N'Sample Data'); --> please "N" is for specifying it as nvarchar.

When you want to display content in ASP.Net depending on the language setting, we have to use localization and Globalization concepts in ASP.Net

Following URL will provide you more info on the same in ASP.Net:

http://www.codeproject.com/useritems/localization.asp

|||

I found this link which helped a lot.

http://www.microsoft.com/globaldev/getWR/steps/wrg_codepage.mspx

sql

Multi Controller

For large databases backup the databases in to multiple backup devices
spanned across multiple disk controllers.
This will create multiple threads during backup right...
what is the command like let's assume we have three controller C: D: E:
the backup command we can write like
backup database DBName to disk = c:\DBDirectory
How can I take the backup into the multiple backup devices on the spot.
Thanks
Hi,
1. Create seperate Backup devices pointing to each controllers using
sp_addumpdevice system stored procedure.
2. Backup the database using Backup database command mentioning all the
devices
Backup database <dbname> to db1_control1,db1_control2 with Init
with init option will overwrite the backup files
Thanks
Hari
SQL Server MVP
"Rogers" <Rogers@.discussions.microsoft.com> wrote in message
news:19D4728B-E399-4E14-AE78-213371431378@.microsoft.com...
> For large databases backup the databases in to multiple backup devices
> spanned across multiple disk controllers.
> This will create multiple threads during backup right...
> what is the command like let's assume we have three controller C: D: E:
> the backup command we can write like
> backup database DBName to disk = c:\DBDirectory
> How can I take the backup into the multiple backup devices on the spot.
> Thanks
|||Just a note that you don't need dump devices to backup to multiple files.
You can specify the filename directly as well.
Andrew J. Kelly SQL MVP
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OR1YS4qOFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Hi,
> 1. Create seperate Backup devices pointing to each controllers using
> sp_addumpdevice system stored procedure.
> 2. Backup the database using Backup database command mentioning all the
> devices
> Backup database <dbname> to db1_control1,db1_control2 with Init
> with init option will overwrite the backup files
> Thanks
> Hari
> SQL Server MVP
>
> "Rogers" <Rogers@.discussions.microsoft.com> wrote in message
> news:19D4728B-E399-4E14-AE78-213371431378@.microsoft.com...
>
|||Backup database <dbname> to db1_control1,db1_control2 with Init
Is that means that the same backup will take place into two cotroller or
partially one and partially into another.
"Hari Prasad" wrote:

> Hi,
> 1. Create seperate Backup devices pointing to each controllers using
> sp_addumpdevice system stored procedure.
> 2. Backup the database using Backup database command mentioning all the
> devices
> Backup database <dbname> to db1_control1,db1_control2 with Init
> with init option will overwrite the backup files
> Thanks
> Hari
> SQL Server MVP
>
> "Rogers" <Rogers@.discussions.microsoft.com> wrote in message
> news:19D4728B-E399-4E14-AE78-213371431378@.microsoft.com...
>
>
|||Hi,
Partiall backup files will be send to each drives specified in Backup
database command
Thanks
Hari
SQL Server MVP
"Rogers" <Rogers@.discussions.microsoft.com> wrote in message
news:F17B751D-4C3D-4D1B-8294-508C4286E333@.microsoft.com...[vbcol=seagreen]
> Backup database <dbname> to db1_control1,db1_control2 with Init
> Is that means that the same backup will take place into two cotroller or
> partially one and partially into another.
>
> "Hari Prasad" wrote:
|||Thanks HARI.
"Hari Prasad" wrote:

> Hi,
> Partiall backup files will be send to each drives specified in Backup
> database command
> Thanks
> Hari
> SQL Server MVP
> "Rogers" <Rogers@.discussions.microsoft.com> wrote in message
> news:F17B751D-4C3D-4D1B-8294-508C4286E333@.microsoft.com...
>
>

Multi Column Keys

Is it possible to create multi column keys in SQL Express? If so, how?

Thanks

MisterT

I found the answer.

Just hold down the Shift key while clicking on the columns. Then click on the "Set primary key".

Have a good day !

Thanks

Multi casting

Just a question about multi casting.

If I create a copy of a data set using a multi cast, do operations on one of the output sets effect the other output set?

For example, in one ouput set I'm setting a column to NULL - this is actually updating the other set as well. Is this meant to happen?

Thanks!!

No. If that's happening then its a bug. Can you post a repro?

-Jamie

sql

Mulitple Create Views in Query Batch

I have a script that I am running from a Query Analyser that I want to put
into a SP eventually.
In the script, I have 5 Create Views (which I drop when the script exits).
But I have to have "GO" after each Create View or I will get an error:
'CREATE VIEW' must be the first statement in a query batch.'
The problem is I also have a beginning date and ending date that I am using
in my query after the Views are created. But I can't declare and set them
up until after the Create Views are done.
Create View...
go
Create View...
go
Create View
go
declare @.StartDate smallDateTime,@.EndDate smallDateTime
select @.StartDate = '07/01/05',@.EndDate = '09/30/05'
select ...
drop View...
What I would like to do is put the declares at the top of the script so that
it will be easier to find for the person running the script to allow them to
change dates as they will be running this 6 or 7 times for different date
ranges.
This is just a one time project, so I don't want to set up a SP at the
moment or write a simple GUI to handle it.
Is there a way to do this (put the dates at the top somehow)?
Also, is the multiple creation of Views a problem in a SP also?
Thanks,
TomTshad,
I've never done what you're asking and I'm not totally sure why you would.
That being said, your variable is batch specific and cannot span batches.
Also, I'm not sure how you would code multiple GOs in your stored procedure.
HTH
Jerry
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e1nZA57vFHA.3188@.TK2MSFTNGP14.phx.gbl...
>I have a script that I am running from a Query Analyser that I want to put
>into a SP eventually.
> In the script, I have 5 Create Views (which I drop when the script exits).
> But I have to have "GO" after each Create View or I will get an error:
> 'CREATE VIEW' must be the first statement in a query batch.'
> The problem is I also have a beginning date and ending date that I am
> using in my query after the Views are created. But I can't declare and
> set them up until after the Create Views are done.
> Create View...
> go
> Create View...
> go
> Create View
> go
> declare @.StartDate smallDateTime,@.EndDate smallDateTime
> select @.StartDate = '07/01/05',@.EndDate = '09/30/05'
> select ...
> drop View...
> What I would like to do is put the declares at the top of the script so
> that it will be easier to find for the person running the script to allow
> them to change dates as they will be running this 6 or 7 times for
> different date ranges.
> This is just a one time project, so I don't want to set up a SP at the
> moment or write a simple GUI to handle it.
> Is there a way to do this (put the dates at the top somehow)?
> Also, is the multiple creation of Views a problem in a SP also?
> Thanks,
> Tom
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:ec04AF8vFHA.720@.TK2MSFTNGP15.phx.gbl...
> Tshad,
> I've never done what you're asking and I'm not totally sure why you would.
> That being said, your variable is batch specific and cannot span batches.
> Also, I'm not sure how you would code multiple GOs in your stored
> procedure.
But if you can only create one View in a Batch, that would be a problem in a
SP where you may need to create more than one.
But I don't know how it is done either.
This is being done to do one specific script to move selected data into a
CSV file to move some data from a client site to ours. We are just going to
give them the script to run. They will run it from Query Analyser and they
can save the results to a tab delimited file and send it to us.
Tom
> HTH
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e1nZA57vFHA.3188@.TK2MSFTNGP14.phx.gbl...
>|||Tshad,
Your variables can be persisted across batches by using a temporary table or
a permanent table - table can be dropped at the end of the script. Why does
this need to be embedded in a proc? Can you just give them a .sql script to
run?
Jerry
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ee%23VbK8vFHA.3312@.TK2MSFTNGP09.phx.gbl...
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:ec04AF8vFHA.720@.TK2MSFTNGP15.phx.gbl...
> But if you can only create one View in a Batch, that would be a problem in
> a SP where you may need to create more than one.
> But I don't know how it is done either.
> This is being done to do one specific script to move selected data into a
> CSV file to move some data from a client site to ours. We are just going
> to give them the script to run. They will run it from Query Analyser and
> they can save the results to a tab delimited file and send it to us.
> Tom
>|||Look up CREATE SCHEMA in BOL.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e1nZA57vFHA.3188@.TK2MSFTNGP14.phx.gbl...
>I have a script that I am running from a Query Analyser that I want to put
>into a SP eventually.
> In the script, I have 5 Create Views (which I drop when the script exits).
> But I have to have "GO" after each Create View or I will get an error:
> 'CREATE VIEW' must be the first statement in a query batch.'
> The problem is I also have a beginning date and ending date that I am
> using in my query after the Views are created. But I can't declare and
> set them up until after the Create Views are done.
> Create View...
> go
> Create View...
> go
> Create View
> go
> declare @.StartDate smallDateTime,@.EndDate smallDateTime
> select @.StartDate = '07/01/05',@.EndDate = '09/30/05'
> select ...
> drop View...
> What I would like to do is put the declares at the top of the script so
> that it will be easier to find for the person running the script to allow
> them to change dates as they will be running this 6 or 7 times for
> different date ranges.
> This is just a one time project, so I don't want to set up a SP at the
> moment or write a simple GUI to handle it.
> Is there a way to do this (put the dates at the top somehow)?
> Also, is the multiple creation of Views a problem in a SP also?
> Thanks,
> Tom
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:elYYhN8vFHA.3756@.tk2msftngp13.phx.gbl...
> Tshad,
> Your variables can be persisted across batches by using a temporary table
> or a permanent table - table can be dropped at the end of the script. Why
> does this need to be embedded in a proc? Can you just give them a .sql
> script to run?
That is what I am doing. The problem is that the Declares for the dates are
halfway down the script. And I was just trying to make it easy on them. It
isn't a big problem, just that I would be nice for them to be able to change
the dates at the top of the script.
Tom
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ee%23VbK8vFHA.3312@.TK2MSFTNGP09.phx.gbl...
>|||SQL Server wants to see just one CREATE VIEW in a batch of its own.
That is just the rules. If you think about it, how could you use a
view that is created in the same batch as code that references it?
Only if this were procedural code that is executed, step by step, like
a 3GL, instead of an RDBMS.
But a better question is why would you create VIEWs and then drop them?
That is not what VIEWs are for. Use derived tables, CTE, etc. if you
want to to do this kind of thing. I'll bet you are still thinking in
3GL terms and want to fake a bunch of scratch files.|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:elYYhN8vFHA.3756@.tk2msftngp13.phx.gbl...
> Tshad,
> Your variables can be persisted across batches by using a temporary table
> or a permanent table - table can be dropped at the end of the script. Why
> does this need to be embedded in a proc? Can you just give them a .sql
> script to run?
It doesn't. And I did give them an sql script. I was trying to see if
there was a way to move the variable declares to the top of files in the
first batch. It wasn't necessary, just curious if there was a way to do it.
As far as the procedure, I was just asking as I may want to set up an SP
using multiple views and if you can't do it as a batch, I may not be able to
do it in a procedure, either.
Tom
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ee%23VbK8vFHA.3312@.TK2MSFTNGP09.phx.gbl...
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1127446041.456812.95650@.g47g2000cwa.googlegroups.com...
> SQL Server wants to see just one CREATE VIEW in a batch of its own.
> That is just the rules. If you think about it, how could you use a
> view that is created in the same batch as code that references it?
> Only if this were procedural code that is executed, step by step, like
> a 3GL, instead of an RDBMS.
Why not?
I am creating multiple Views that I am using temporarily and I am not sure
why it is obvious that you can't create a View and then immediately
reference it. There probably is a good reason for it, but I don't know what
or why it is.

> But a better question is why would you create VIEWs and then drop them?
> That is not what VIEWs are for. Use derived tables, CTE, etc. if you
> want to to do this kind of thing. I'll bet you are still thinking in
> 3GL terms and want to fake a bunch of scratch files.
You are right. That is the way I think. So shoot me. :)
This may not be what Views are for, but they solve a huge problem for me and
worked great.
May not have been the cleanest way, but I liked it. As a matter of fact, I
did use one table that I selected into and 5 Views, which worked great (took
a little time to put together with a great deal of help from Hugo).
As far as derived tables, I used those also. But for my select statement
(which was very large - at least I thought so) I was doing quite a bit of
work to get all my data to go across one line for each record ( I know there
are no fields, records or tables). This was for a csv file import/export.
According to what Hugo explained I was, in effect, using derived tables in
the form of Views. But if I had to replace all my references to my views
with derived tables, I think it would have been a bear to debug and my files
would have been 10-20 times larger.
I look at this as using the Views as a subroutine or macro that I call
instead of placing multiple instances of the same code throughout my select
statement. Much cleaner, even if not as efficient.
Tom|||>> Why not? <<
VIEWs can be built on VIEWs; they have to be created in order. Think
about it
NO, NO, NO!! You create VIEWs because they have meaning as data
elements that "persist" over many queries. There are no "temporary
kind of things" in a good data model. Damn it, man, you are still
writing scratch files in a 1960's COBOL system!
Not a problem, but if you do not learn, we will have to kill you\
. Hey, if I could not get at least a breakdown or a suicide during
final exams, the quarter was a waste!
VIEWs are good, but they are part of a schema design and need to be
planned as much as any other tabel. In spite of the myth, size does
not matter. In RDBMS, unlike sex, speed is better [note to self: I am
going har this quote again]

mulitiple selects in a stored procedure

Hi,
I am trying to create a pagination within a stored procedure but I need
to select from several tables:
First I am inserting the fields into a temp table and this works fine:
INSERT INTO #TempItems (Name)
SELECT Name FROM tblName
I thought I could get data from other tables using UNION:
INSERT INTO #TempItems (Name,Address,Telephone,Street)
SELECT Name FROM tblName
UNION
SELECT Address FROM tblAddress
UNION
SELECT Telephone FROM tblTelephone
UNION
SELECT Street FROM tblStreet
*These are bogus fields I have used as examples.
But the error I get is that the number of insert fields is less then the
select? Could anyone help on how the best way to achieve this?
many thanks in advance
Peter
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You can google the newsgroups (suggest .programming) for pagination
techniques. Your error is due to a misunderstanding of how union works.
Union merely combines separate result sets into one; you are attempting to
use it (incorrectly) as you would a join. Assuming that a union could be
used, you must specify 4 items within the select list of each select
statement that is part of the union, corresponding to the 4 columns to be
inserted. There are other flaws in your logic, but this should get you
started.
You should be inserting using something like the following
insert ...
select ...
from tblName inner join tblAddress on ...
inner join tblTelephone on ...
inner join tblStreet on ...
where ...
How those joins are made (and their type - inner, outer, cross, etc) I
cannot answer without knowing the relationships between the table.
"Peter Rooney" <peter@.whoba.co.uk> wrote in message
news:OoqGZO3rDHA.2456@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am trying to create a pagination within a stored procedure but I need
> to select from several tables:
> First I am inserting the fields into a temp table and this works fine:
> INSERT INTO #TempItems (Name)
> SELECT Name FROM tblName
> I thought I could get data from other tables using UNION:
> INSERT INTO #TempItems (Name,Address,Telephone,Street)
> SELECT Name FROM tblName
> UNION
> SELECT Address FROM tblAddress
> UNION
> SELECT Telephone FROM tblTelephone
> UNION
> SELECT Street FROM tblStreet
> *These are bogus fields I have used as examples.
>
> But the error I get is that the number of insert fields is less then the
> select? Could anyone help on how the best way to achieve this?
> many thanks in advance
> Peter
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!sql

MSSQLSERVER Error 17830

Hi,
Please help me with this before I get crazy....

I have VS 2005 and SQL server 2005 installed on XP ( with SP2 ) I create a new web project. Make a SQL connection to my database and configure the advanced options. Drag gridview component to my form and configure it to use the connection with edit option checked. When I run my form I can scroll and view the data with no problem at all. Even edit works fine BUT the changes to the row NEVER gets to the database. No error messages what so ever. XP:s Event View reports this:

Type: Error
Source: MSSQLSERVER
Class: Logon
Code: 17830

A network error occurred while establishing a connection; the connection has been closed. This may have been caused by client or server login timeout expiration. Time spent during login: total 31 ms, enqueued 1 ms, network writes 1 ms, network reads 30 ms, establishing SSL 29 ms, negotiating SSPI 0 ms, validating login 0 ms. [CLIENT: <local machine>]

Any ideas what is wrong?

Regards, Jarmo

Is it possible that in the connection string in the client application you've specified the option to require encryption?

If this is the case and the server doesn't have an SSL certificate configured from a trusted authority then the client driver may be dropping the connection during the SSL handshake because it doesn't trust the server's SSL certificate.

Hope this helps,
Vaughn

MSsQL2005; OPENROWSET, BLOB/IMAGE and STORED PROCEDURE problems

All,

I work with Microsoft SQL Server 2005 on windows XP professional.
I'd like to create stored procdure to add image to my database (jpg file).
I managed to do it using VARCHAR variable in stored procedure
and then using EXEC, but it don't work directly.

My Table definiton:
CREATE TABLE [dbo].[Users](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[Login] [char](10),
[Password] [char](20),
[Avatar] [image] NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

My working solution using stored procedure:
ALTER PROCEDURE [dbo].[AddUser]
@.Login AS VARCHAR(255),
@.Password AS VARCHAR(255),
@.AvatarFileLocation AS VARCHAR(255),
@.UserId AS INT OUTPUT
AS
BEGIN
SET @.Query = 'INSERT INTO USERS ' + CHAR(13)
+ 'SELECT '''+ @.Login + ''' AS Login, ' + CHAR(13)
+ '''' + @.Password + ''' AS Password,' + CHAR(13)
+ '(SELECT * FROM OPENROWSET(BULK ''' + @.AvatarFileLocation + ''', SINGLE_BLOB) AS OBRAZEK)'
EXECUTE (@.Query)
SET @.UserID = @.@.IDENTITY
END

I'd like to use statement in the stored procdure:
ALTER PROCEDURE [dbo].[AddUser]
@.Login AS VARCHAR(255),
@.Password AS VARCHAR(255),
@.AvatarFileLocation AS VARCHAR(255),
@.UserId AS INT OUTPUT
AS
BEGIN
DECLARE
@.Query AS VARCHAR(MAX)

SET @.AvatarFileLocation = 'C:\hitman1.jpg'
INSERT INTO USERS
SELECT @.Login AS Login,
@.Password AS Password,
(SELECT * FROM OPENROWSET(BULK @.AvatarFileLocation, SINGLE_BLOB) AS OBRAZEK)

SET @.UserID = @.@.IDENTITY

END

It generates error:
Incorrect syntax near '@.AvatarFileLocation'.

My question is:
Why it does not work and how to write the stored procedure code to run this code without errors.

Thanks for any reply

You can't use a variable inside OPENROWSET.

What you are doing, in any case, IS VERY DANGEROUS. There

are many ways in which dynamic SQL is vulnerable to SQL

injection. Please read about it, so that you don't lose

everything you have when a malicious user joins your

site/forum with a password like

O',0x; delete from USERS where Password <> 'O';return 0;--

You might start reading here:

http://www.sommarskog.se/dynamic_sql.html

Steve Kass

Drew University

Michal1979@.discussions.microsoft.com wrote:

> All,

>

> I work with Microsoft SQL Server 2005 on windows XP professional.

> I'd like to create stored procdure to add image to my database (jpg

> file).

> I managed to do it using VARCHAR variable in stored procedure

> and then using EXEC, but it don't work directly.

>

> My Table definiton:

> CREATE TABLE [dbo].[Users](

> [UserID] [int] IDENTITY(1,1) NOT NULL,

> [Login] [char](10),

> [Password] [char](20),

> [Avatar] [image] NULL,

> CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED

> (

> [UserID] ASC

> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

>

> My working solution using stored procedure:

> ALTER PROCEDURE [dbo].[AddUser]

> @.Login AS VARCHAR(255),

> @.Password AS VARCHAR(255),

> @.AvatarFileLocation AS VARCHAR(255),

> @.UserId AS INT OUTPUT

> AS

> BEGIN

> SET @.Query = 'INSERT INTO USERS ' + CHAR(13)

> + 'SELECT '''+ @.Login + ''' AS Login, ' + CHAR(13)

> + '''' + @.Password + ''' AS Password,' + CHAR(13)

> + '(SELECT * FROM OPENROWSET(BULK ''' + @.AvatarFileLocation + ''',

> SINGLE_BLOB) AS OBRAZEK)'

> EXECUTE (@.Query)

> SET @.UserID = @.@.IDENTITY

> END

>

> I'd like to use statement in the stored procdure:

> ALTER PROCEDURE [dbo].[AddUser]

> @.Login AS VARCHAR(255),

> @.Password AS VARCHAR(255),

> @.AvatarFileLocation AS VARCHAR(255),

> @.UserId AS INT OUTPUT

> AS

> BEGIN

> DECLARE

> @.Query AS VARCHAR(MAX)

>

> SET @.AvatarFileLocation = 'C:\hitman1.jpg'

> INSERT INTO USERS

> SELECT @.Login AS Login,

> @.Password AS Password,

> (SELECT * FROM OPENROWSET(BULK @.AvatarFileLocation, SINGLE_BLOB) AS

> OBRAZEK)

>

>

> SET @.UserID = @.@.IDENTITY

>

> END

>

>

> It generates error:

> Incorrect syntax near '@.AvatarFileLocation'.

>

> My question is:

> Why it does not work and how to write the stored procedure code to run

> this code without errors.

>

> Thanks for any reply

>

>

>

>|||

NNTP User

Lot thanks for the repy,

The article link is very interesting.

I understand risks from using dynamic sql, but I'm not goiing to allow user call my stored procedure directly.

All arguments of the stored procedure will be validated and constructed my my application.

My real question is why can I use OPENROWSET function in stored procedure when I build the query

using varchar variable and then execute it, although I can't do it direclty using OPENROWSET in

stored procedure.

|||You cannot use a variable like this:

OPENROWSET(BULK @.AvatarFileLocation, SINGLE_BLOB)

The file name must be hard-coded, like this:

OPENROWSET(BULK 'C:\picture.jpg', SINGLE_BLOB)

SK

Michal1979@.discussions.microsoft.com wrote:

> NNTP User

>

>

> Lot thanks for the repy,

>

> The article link is very interesting.

>

> I understand risks from using dynamic sql, but I'm not goiing to allow

> user call my stored procedure directly.

>

> All arguments of the stored procedure will be validated and constructed

> my my application.

>

> My real question is why can I use OPENROWSET function in stored

> procedure when I build the query

>

> using varchar variable and then execute it, although I can't do it

> direclty using OPENROWSET in

>

> stored procedure.

>

>

>

>

>

>|||

NNTP User,

Yes,I can not use directly OPENROWSET function in strored procedure, but I can use dynamic sql.

My problem is not how to store image using stored procedure (I can do it, however I don't like the way I do it)

but why can't I do it directly.

By the way I tried to use sp_executesql to run the code but it didn't work as well - so for now the only way

to store image or file using stored procedure is dynamic sql.

But I'm still wandering WHY? Is it a bug or something like that?

|||

As Steve indicated, you will have to specify the filename as a literal in the OPENROWSET call. Otherwise you will have to use dynamic SQL to form the entire statement and execute it. And with dynamic SQL you will have protect against SQL injection attacks. Optionally, you can do the following for a bulk import process:

1. Create temporary table to hold the user accounts

2. Use the new BulkCopy managed API to stream the user data from the client to server

3. Write SP to dump the rows from temporary table to the main table

For adding or modifying single values, you can just have a SP with image parameter and manipulate the data in the table. There is no need to use OPENROWSET which requires a file (creation of file by client, server having permissions to access file, etc) among other things and parameterization is not straight-forward.

|||

Umachandar Jayachandran - MS,

I'd like to know if there is other than OPENROWSET form of loading images or binary files into MSSQL,

I could missed it in documentation. It is sure that there will be no possibility to sql injection using dynamic sql

in my case.

Probably I should mentioned it before, the problem I described is not critical for me, becouse I found solution.

The solution may not be ideal (especialy for me) but it still works. Anyway probably I'll use C# and ADO.NET to

perform put and get image from database.

Maybe you know why the only way to use OPENROWSET in stored procedure is to use dynamic sql.

Thanks

MSsQL2005; OPENROWSET, BLOB/IMAGE and STORED PROCEDURE problems

All,

I work with Microsoft SQL Server 2005 on windows XP professional.
I'd like to create stored procdure to add image to my database (jpg file).
I managed to do it using VARCHAR variable in stored procedure
and then using EXEC, but it don't work directly.

My Table definiton:
CREATE TABLE [dbo].[Users](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[Login] [char](10),
[Password] [char](20),
[Avatar] [image] NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

My working solution using stored procedure:
ALTER PROCEDURE [dbo].[AddUser]
@.Login AS VARCHAR(255),
@.Password AS VARCHAR(255),
@.AvatarFileLocation AS VARCHAR(255),
@.UserId AS INT OUTPUT
AS
BEGIN
SET @.Query = 'INSERT INTO USERS ' + CHAR(13)
+ 'SELECT '''+ @.Login + ''' AS Login, ' + CHAR(13)
+ '''' + @.Password + ''' AS Password,' + CHAR(13)
+ '(SELECT * FROM OPENROWSET(BULK ''' + @.AvatarFileLocation + ''', SINGLE_BLOB) AS OBRAZEK)'
EXECUTE (@.Query)
SET @.UserID = @.@.IDENTITY
END

I'd like to use statement in the stored procdure:
ALTER PROCEDURE [dbo].[AddUser]
@.Login AS VARCHAR(255),
@.Password AS VARCHAR(255),
@.AvatarFileLocation AS VARCHAR(255),
@.UserId AS INT OUTPUT
AS
BEGIN
DECLARE
@.Query AS VARCHAR(MAX)

SET @.AvatarFileLocation = 'C:\hitman1.jpg'
INSERT INTO USERS
SELECT @.Login AS Login,
@.Password AS Password,
(SELECT * FROM OPENROWSET(BULK @.AvatarFileLocation, SINGLE_BLOB) AS OBRAZEK)

SET @.UserID = @.@.IDENTITY

END

It generates error:
Incorrect syntax near '@.AvatarFileLocation'.

My question is:
Why it does not work and how to write the stored procedure code to run this code without errors.

Thanks for any reply

You can't use a variable inside OPENROWSET.

What you are doing, in any case, IS VERY DANGEROUS. There

are many ways in which dynamic SQL is vulnerable to SQL

injection. Please read about it, so that you don't lose

everything you have when a malicious user joins your

site/forum with a password like

O',0x; delete from USERS where Password <> 'O';return 0;--

You might start reading here:

http://www.sommarskog.se/dynamic_sql.html

Steve Kass

Drew University

Michal1979@.discussions.microsoft.com wrote:

> All,

>

> I work with Microsoft SQL Server 2005 on windows XP professional.

> I'd like to create stored procdure to add image to my database (jpg

> file).

> I managed to do it using VARCHAR variable in stored procedure

> and then using EXEC, but it don't work directly.

>

> My Table definiton:

> CREATE TABLE [dbo].[Users](

> [UserID] [int] IDENTITY(1,1) NOT NULL,

> [Login] [char](10),

> [Password] [char](20),

> [Avatar] [image] NULL,

> CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED

> (

> [UserID] ASC

> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

>

> My working solution using stored procedure:

> ALTER PROCEDURE [dbo].[AddUser]

> @.Login AS VARCHAR(255),

> @.Password AS VARCHAR(255),

> @.AvatarFileLocation AS VARCHAR(255),

> @.UserId AS INT OUTPUT

> AS

> BEGIN

> SET @.Query = 'INSERT INTO USERS ' + CHAR(13)

> + 'SELECT '''+ @.Login + ''' AS Login, ' + CHAR(13)

> + '''' + @.Password + ''' AS Password,' + CHAR(13)

> + '(SELECT * FROM OPENROWSET(BULK ''' + @.AvatarFileLocation + ''',

> SINGLE_BLOB) AS OBRAZEK)'

> EXECUTE (@.Query)

> SET @.UserID = @.@.IDENTITY

> END

>

> I'd like to use statement in the stored procdure:

> ALTER PROCEDURE [dbo].[AddUser]

> @.Login AS VARCHAR(255),

> @.Password AS VARCHAR(255),

> @.AvatarFileLocation AS VARCHAR(255),

> @.UserId AS INT OUTPUT

> AS

> BEGIN

> DECLARE

> @.Query AS VARCHAR(MAX)

>

> SET @.AvatarFileLocation = 'C:\hitman1.jpg'

> INSERT INTO USERS

> SELECT @.Login AS Login,

> @.Password AS Password,

> (SELECT * FROM OPENROWSET(BULK @.AvatarFileLocation, SINGLE_BLOB) AS

> OBRAZEK)

>

>

> SET @.UserID = @.@.IDENTITY

>

> END

>

>

> It generates error:

> Incorrect syntax near '@.AvatarFileLocation'.

>

> My question is:

> Why it does not work and how to write the stored procedure code to run

> this code without errors.

>

> Thanks for any reply

>

>

>

>|||

NNTP User

Lot thanks for the repy,

The article link is very interesting.

I understand risks from using dynamic sql, but I'm not goiing to allow user call my stored procedure directly.

All arguments of the stored procedure will be validated and constructed my my application.

My real question is why can I use OPENROWSET function in stored procedure when I build the query

using varchar variable and then execute it, although I can't do it direclty using OPENROWSET in

stored procedure.

|||You cannot use a variable like this:

OPENROWSET(BULK @.AvatarFileLocation, SINGLE_BLOB)

The file name must be hard-coded, like this:

OPENROWSET(BULK 'C:\picture.jpg', SINGLE_BLOB)

SK

Michal1979@.discussions.microsoft.com wrote:

> NNTP User

>

>

> Lot thanks for the repy,

>

> The article link is very interesting.

>

> I understand risks from using dynamic sql, but I'm not goiing to allow

> user call my stored procedure directly.

>

> All arguments of the stored procedure will be validated and constructed

> my my application.

>

> My real question is why can I use OPENROWSET function in stored

> procedure when I build the query

>

> using varchar variable and then execute it, although I can't do it

> direclty using OPENROWSET in

>

> stored procedure.

>

>

>

>

>

>|||

NNTP User,

Yes,I can not use directly OPENROWSET function in strored procedure, but I can use dynamic sql.

My problem is not how to store image using stored procedure (I can do it, however I don't like the way I do it)

but why can't I do it directly.

By the way I tried to use sp_executesql to run the code but it didn't work as well - so for now the only way

to store image or file using stored procedure is dynamic sql.

But I'm still wandering WHY? Is it a bug or something like that?

|||

As Steve indicated, you will have to specify the filename as a literal in the OPENROWSET call. Otherwise you will have to use dynamic SQL to form the entire statement and execute it. And with dynamic SQL you will have protect against SQL injection attacks. Optionally, you can do the following for a bulk import process:

1. Create temporary table to hold the user accounts

2. Use the new BulkCopy managed API to stream the user data from the client to server

3. Write SP to dump the rows from temporary table to the main table

For adding or modifying single values, you can just have a SP with image parameter and manipulate the data in the table. There is no need to use OPENROWSET which requires a file (creation of file by client, server having permissions to access file, etc) among other things and parameterization is not straight-forward.

|||

Umachandar Jayachandran - MS,

I'd like to know if there is other than OPENROWSET form of loading images or binary files into MSSQL,

I could missed it in documentation. It is sure that there will be no possibility to sql injection using dynamic sql

in my case.

Probably I should mentioned it before, the problem I described is not critical for me, becouse I found solution.

The solution may not be ideal (especialy for me) but it still works. Anyway probably I'll use C# and ADO.NET to

perform put and get image from database.

Maybe you know why the only way to use OPENROWSET in stored procedure is to use dynamic sql.

Thanks

MSSQL2000 Client Unable to create new table

HELP!
I'm new to SQL Server and I'm sure this is a simple problem.
But I can't seem to solve it.
Here's the problem:
My website & MS SQL 2000 Server are being hosted by Networksolutions.
Thru the Client Enterprise Manager I am Unable to create a New Table.
This is the error message I get:
" [MS Design Tools]-ODBC error: [Microsoft] [ODBC SQL SERVER DRIVER] [SQL SERVER] SELECT permission denied on object 'sysobjects', database 'namedb', owner'db' " :confused:Someone must grant you the necessary permissions for the database.
You're not authorized to create objects, simply.|||Thank you for your insieght.
I'm sure your are correct.

MS-SQL: wheres auto increment?

It's been a long time since I've had to check an index for the highest value, then add 1, to create a new unique key. These past few years, it seems this is usually done for you. But now that I'm working with MS-SQL, I don't see it. Is it there? It's doesn't seem to be inherent in the definition.you will need to set the field/column to be an identity column

Make it an integer - don't allow nulls - then, depending on which app you're using to create it - set the column to be an identity column|||Set Identity Seed = yes for the column|||Identity Seed is numeric and indicates the starting number to use for Auto Numbering...It cannot be set to "Yes"|||I think he meant "Identity". You set the Identity to yes and identity seed is the starting number.|||Yea, sorry, it was supposed to be "Identity"

MsSQL Security Issue

Hi, I having an issue on MsSQL Security Issue. Wish you guys can help me.
My problem is now I have create a login user and password for each Database. The problem is if the database file (.MDF, .ldf) has been copy out..user do attach manually, then put any new user and password to that db..so all my data will be view by others..is there any solution for this?
Another Question is for each DB..can we kick out sa user and put in our new user for that DB? So sa no permission on view/update/Add in records for that db?

Thanks you.Please view this tread
http://www.dbforums.com/showthread.php?threadid=970286

Basically u can only control ppl from accessing Enterprise Manager;SQL Service Agent by setting Window Authetication rights to users login to u'r Windows.

If a person can't enter the enterprise manager, can't shutdown the SQL Service Agent, they can't cut and copy the mdf and ldf files.

Normally, SA has full user rights to all dbs under its instance. It would be bad user management in your company if your force to kick out SA from a DB!!!!! , the SA password is given to the company DBA.

I don't think it is possible to kick SA rights. Even if SA is not the owner of the DB, it can enter any database in its instance.|||All database files should be placed so that the database server can access them, but no one else can. This is extremely important.

Backup tapes containing the information must be similarly secured.|||I see, thanks you. Actually my situation is a bit different. If we handle normally, it can handle as you said, but my db will goto client office, and the owner of the db don't want client manually open db and modify or copy as their own purpose...that why i looking for this solution..
anyway..thanks you ^.^|||Maybe I'm not understanding your particular issue but couldn't you encrypt the data that is of concern? There are better ways to secure your data but if you 2 way encrypt it and store the encryption/decryption routine in a dll then you should be good to go right? You would have your data access layer implicitly use the encryption/decryption function.

Of course people could still mess up the data but you could have a sanity check built into it somewhere.

Sorry that this isn't a SQL solution but there you go.

HTH,
Dan