Credentials

When running reports inside the Visual Studio IDE the datasources lose the
credentials that were previously set up.
I use the 'Use a specific user name and password'.
Any ideas?Try setting the credentials in the advanced datasource options, and
then using the "Test Connection" button on the lower left. If the
credentials aren't correct, the connection will fail, and the report
manager will not keep them in the connection string. Of course, I may
have misinterpreted your problem, in which case sorry! Hope this helps.
Lance M.
Brian Shannon wrote:
> When running reports inside the Visual Studio IDE the datasources lose the
> credentials that were previously set up.
> I use the 'Use a specific user name and password'.
> Any ideas?|||Brian,
Are you saying that the credentials aren't in Report Manager when you
deploy, or are you saying that the credentials disappear while you are
designing the report in VS?
-Josh
Lance M wrote:
> Try setting the credentials in the advanced datasource options, and
> then using the "Test Connection" button on the lower left. If the
> credentials aren't correct, the connection will fail, and the report
> manager will not keep them in the connection string. Of course, I may
> have misinterpreted your problem, in which case sorry! Hope this helps.
> Lance M.
> Brian Shannon wrote:
> > When running reports inside the Visual Studio IDE the datasources lose the
> > credentials that were previously set up.
> >
> > I use the 'Use a specific user name and password'.
> >
> > Any ideas?|||The credentials disapprear while I am designing the report in VS.
Scenerio:
I create a report. Deploy the report. All works fine. I come back a few
days later to make a change and the credentials are gone. I simply have to
add username/password back. Then all works fine again. It seems like a
random problem.
There are 3 of us using the VS solution. Could that be causing problems?
Thanks.
"Josh" <bell.joshua@.gmail.com> wrote in message
news:1155234202.244242.157330@.h48g2000cwc.googlegroups.com...
> Brian,
> Are you saying that the credentials aren't in Report Manager when you
> deploy, or are you saying that the credentials disappear while you are
> designing the report in VS?
> -Josh
>
> Lance M wrote:
>> Try setting the credentials in the advanced datasource options, and
>> then using the "Test Connection" button on the lower left. If the
>> credentials aren't correct, the connection will fail, and the report
>> manager will not keep them in the connection string. Of course, I may
>> have misinterpreted your problem, in which case sorry! Hope this helps.
>> Lance M.
>> Brian Shannon wrote:
>> > When running reports inside the Visual Studio IDE the datasources lose
>> > the
>> > credentials that were previously set up.
>> >
>> > I use the 'Use a specific user name and password'.
>> >
>> > Any ideas?
>|||Hi Brian,
I am getting the same problem. Did you find a solution?
Regards,
Robbie Berghan
rberghan@.gmail.com
"Brian Shannon" wrote:
> The credentials disapprear while I am designing the report in VS.
> Scenerio:
> I create a report. Deploy the report. All works fine. I come back a few
> days later to make a change and the credentials are gone. I simply have to
> add username/password back. Then all works fine again. It seems like a
> random problem.
> There are 3 of us using the VS solution. Could that be causing problems?
> Thanks.
> "Josh" <bell.joshua@.gmail.com> wrote in message
> news:1155234202.244242.157330@.h48g2000cwc.googlegroups.com...
> >
> > Brian,
> >
> > Are you saying that the credentials aren't in Report Manager when you
> > deploy, or are you saying that the credentials disappear while you are
> > designing the report in VS?
> >
> > -Josh
> >
> >
> > Lance M wrote:
> >> Try setting the credentials in the advanced datasource options, and
> >> then using the "Test Connection" button on the lower left. If the
> >> credentials aren't correct, the connection will fail, and the report
> >> manager will not keep them in the connection string. Of course, I may
> >> have misinterpreted your problem, in which case sorry! Hope this helps.
> >>
> >> Lance M.
> >>
> >> Brian Shannon wrote:
> >> > When running reports inside the Visual Studio IDE the datasources lose
> >> > the
> >> > credentials that were previously set up.
> >> >
> >> > I use the 'Use a specific user name and password'.
> >> >
> >> > Any ideas?
> >
>
>

Creating View

Dear Sender,
I know that this is b'coz of GO statement. but GO is not a
T-SQL command and it works only inside Query analyser. if
the same string i execute through VB code by using
connection.execute, it doesn't recognise GO so i removed
it. It works for creating tables if i remove GO and
execute through VB-code. now i have to run this
query "CREATE VIEW" from my vb code and at one go as i do
for tables, i want to create all the views. ur suggestions
are welcome.
Billi98
You cannot combine CREATE VIEW with any other command in the same batch ("execution"). So you need to loop the
input file, and each time you find a GO, execute what you have in the buffer.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"billi98" <anonymous@.discussions.microsoft.com> wrote in message
news:49f701c42c33$be5f7e20$a601280a@.phx.gbl...
> Dear Sender,
> I know that this is b'coz of GO statement. but GO is not a
> T-SQL command and it works only inside Query analyser. if
> the same string i execute through VB code by using
> connection.execute, it doesn't recognise GO so i removed
> it. It works for creating tables if i remove GO and
> execute through VB-code. now i have to run this
> query "CREATE VIEW" from my vb code and at one go as i do
> for tables, i want to create all the views. ur suggestions
> are welcome.
> Billi98
>

Creating View

Dear Sender,
I know that this is b'coz of GO statement. but GO is not a
T-SQL command and it works only inside Query analyser. if
the same string i execute through VB code by using
connection.execute, it doesn't recognise GO so i removed
it. It works for creating tables if i remove GO and
execute through VB-code. now i have to run this
query "CREATE VIEW" from my vb code and at one go as i do
for tables, i want to create all the views. ur suggestions
are welcome.
Billi98Hello,
The GO statement only works through osql and isql (or in
this case Query Analyser).
All it does is signal the end of Transact-SQL statements,
including creating views SP or tables.
To be honest I am not too sure what your asking, you do
not actually need it when you are creating a view, but its
a 'nice to have' is you have a script you need running
though QA.
J
>--Original Message--
>Dear Sender,
>I know that this is b'coz of GO statement. but GO is not
a
>T-SQL command and it works only inside Query analyser. if
>the same string i execute through VB code by using
>connection.execute, it doesn't recognise GO so i removed
>it. It works for creating tables if i remove GO and
>execute through VB-code. now i have to run this
>query "CREATE VIEW" from my vb code and at one go as i do
>for tables, i want to create all the views. ur
suggestions
>are welcome.
>Billi98
>
>.
>|||You cannot combine CREATE VIEW with any other command in the same batch ("execution"). So you need to loop the
input file, and each time you find a GO, execute what you have in the buffer.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"billi98" <anonymous@.discussions.microsoft.com> wrote in message
news:49f701c42c33$be5f7e20$a601280a@.phx.gbl...
> Dear Sender,
> I know that this is b'coz of GO statement. but GO is not a
> T-SQL command and it works only inside Query analyser. if
> the same string i execute through VB code by using
> connection.execute, it doesn't recognise GO so i removed
> it. It works for creating tables if i remove GO and
> execute through VB-code. now i have to run this
> query "CREATE VIEW" from my vb code and at one go as i do
> for tables, i want to create all the views. ur suggestions
> are welcome.
> Billi98
>

Creating View

Dear Sender,
I know that this is b'coz of GO statement. but GO is not a
T-SQL command and it works only inside Query analyser. if
the same string i execute through VB code by using
connection.execute, it doesn't recognise GO so i removed
it. It works for creating tables if i remove GO and
execute through VB-code. now i have to run this
query "CREATE VIEW" from my vb code and at one go as i do
for tables, i want to create all the views. ur suggestions
are welcome.
Billi98You cannot combine CREATE VIEW with any other command in the same batch ("ex
ecution"). So you need to loop the
input file, and each time you find a GO, execute what you have in the buffer
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"billi98" <anonymous@.discussions.microsoft.com> wrote in message
news:49f701c42c33$be5f7e20$a601280a@.phx.gbl...
> Dear Sender,
> I know that this is b'coz of GO statement. but GO is not a
> T-SQL command and it works only inside Query analyser. if
> the same string i execute through VB code by using
> connection.execute, it doesn't recognise GO so i removed
> it. It works for creating tables if i remove GO and
> execute through VB-code. now i have to run this
> query "CREATE VIEW" from my vb code and at one go as i do
> for tables, i want to create all the views. ur suggestions
> are welcome.
> Billi98
>

Creating Test Data for a Table

Hello, say I have a Table with 2 columns:

integer ID

varchar Description

How can I insert a large number of test rows inside this table for experimentation purposes?

Thanx in advance!

u can use some existing table...transform and import that data...

or..there are a few data generator tools u can search and use them.... if u have visual studio 2005 for database developer, that has a cool data generation functionality too...

|||

Thanx, though I was looking for a T-SQL query equivalent solution

Maybe a while loop? I'm not so good at writing code in sql, any other suggestions please?

|||

JohDas wrote:

Hello, say I have a Table with 2 columns:

integer ID

varchar Description

How can I insert a large number of test rows inside this table for experimentation purposes?

Thanx in advance!

INSERT INTO
MyTable (id, Description)
(
SELECT
Id, Description
FROM
MyOtherTable
)|||

The following query may help you..

Create Table TestTable
(
Id int,
Description varchar(100)
)
go
SET NOCOUNT ON
Declare @.Count as int
Select @.Count = 10000
While @.Count >0
Begin
Insert Into TestTable values (@.Count, 'Description ' + Convert(Varchar,@.Count));
Select @.Count = @.Count - 1
End

You can change your Count init value as you want upto 2,147,483,647 ..

|||

ManiD wrote:

The following query may help you..

Create Table TestTable
(
Id int,
Description varchar(100)
)
go
SET NOCOUNT ON
Declare @.Count as int
Select @.Count = 10000
While @.Count >0
Begin
Insert Into TestTable values (@.Count, 'Description ' + Convert(Varchar,@.Count));
Select @.Count = @.Count - 1
End

You can change your Count init value as you want upto 2,147,483,647 ..

Nice, after seeing this I realised I mis understood the question |||Thanx a lot, this is the query I've been searching!!!|||

JohDas:

I try to avoid these WHILE loops as much as possible. I normally try to use a "numbers" table to generate my test data; this is normally much more efficient. One of the tools that I find helpful for generating repetitive data is the modulo "%" operator

-- --
-- Give a look to this link for a description of the use of a
-- "numbers" table:
--
-- http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html
--
-- A table that can be used on a "test only" basis is the
-- "master.dbo.spt_values" table; but be forwarned: this type
-- of use is not supported! In fact, if you use this table with
-- SQL Server 2000 you will find that you get a different range
-- of values for the "number" column than you do with SQL Server
-- 2005.
--
-- Nonetheless, I still frequently use this table as a source of
-- values for test data. If the range of values from this table
-- is not sufficient to provide enough data, then I might
-- generate my data by cross joining the spt_values table with
-- itself.
-- --
select number,
'Description #' + convert (varchar(10), number%21) Description
from master.dbo.spt_values (nolock)
where name is null

-- Output:

-- number Description
-- -- --
-- 0 Description #0
-- 1 Description #1
-- 2 Description #2
-- ...
-- 20 Description #20
-- 21 Description #0
-- 22 Description #1
-- ...

|||

Thanx Mugambo, it's a more complex solution (to comprehend ;) ) but I think it'll be faster

creating temp table inside SP

I am doing a code review of a stored procedure.
What the procedure does is to create a temp table and then based on parameters sent to
the procedure, call different stored procedures which inserts into the temp table just
created.
Are their any issues with procedures creating temp table on the fly. Any scalability
issues?
There is a good explanation in the BOL. See "DBCC DBREINDEX".
AMB
"rkusenet" wrote:

> I am doing a code review of a stored procedure.
> What the procedure does is to create a temp table and then based on parameters sent to
> the procedure, call different stored procedures which inserts into the temp table just
> created.
> Are their any issues with procedures creating temp table on the fly. Any scalability
> issues?
>
|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> There is a good explanation in the BOL. See "DBCC DBREINDEX".
>
> AMB
> "rkusenet" wrote:
|||Here's a short article - posted previously by Aaron - on the subject.
http://www.aspfaq.com/show.asp?id=2475
Enjoy.
"rkusenet" <usenet.rk@.gmail.com> wrote in message
news:3cstc2F6q880iU1@.individual.net...
>I am doing a code review of a stored procedure. What the procedure does is
>to create a temp table and then based on parameters sent to
> the procedure, call different stored procedures which inserts into the
> temp table just
> created.
> Are their any issues with procedures creating temp table on the fly. Any
> scalability
> issues?
>
|||"Michael C#" <howsa@.boutdat.com> wrote in message
news:ewjaY42RFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Here's a short article - posted previously by Aaron - on the subject.
> http://www.aspfaq.com/show.asp?id=2475
>
excellent information there.
thanks a lot.

creating temp table inside SP

I am doing a code review of a stored procedure.
What the procedure does is to create a temp table and then based on parameters sent to
the procedure, call different stored procedures which inserts into the temp table just
created.
Are their any issues with procedures creating temp table on the fly. Any scalability
issues?There is a good explanation in the BOL. See "DBCC DBREINDEX".
AMB
"rkusenet" wrote:
> I am doing a code review of a stored procedure.
> What the procedure does is to create a temp table and then based on parameters sent to
> the procedure, call different stored procedures which inserts into the temp table just
> created.
> Are their any issues with procedures creating temp table on the fly. Any scalability
> issues?
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> There is a good explanation in the BOL. See "DBCC DBREINDEX".
>
> AMB
> "rkusenet" wrote:
> > I am doing a code review of a stored procedure.
> > What the procedure does is to create a temp table and then based on parameters sent to
> > the procedure, call different stored procedures which inserts into the temp table just
> > created.
> > Are their any issues with procedures creating temp table on the fly. Any scalability
> > issues?
> >
> >|||Here's a short article - posted previously by Aaron - on the subject.
http://www.aspfaq.com/show.asp?id=2475
Enjoy.
"rkusenet" <usenet.rk@.gmail.com> wrote in message
news:3cstc2F6q880iU1@.individual.net...
>I am doing a code review of a stored procedure. What the procedure does is
>to create a temp table and then based on parameters sent to
> the procedure, call different stored procedures which inserts into the
> temp table just
> created.
> Are their any issues with procedures creating temp table on the fly. Any
> scalability
> issues?
>|||"Michael C#" <howsa@.boutdat.com> wrote in message
news:ewjaY42RFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Here's a short article - posted previously by Aaron - on the subject.
> http://www.aspfaq.com/show.asp?id=2475
>
excellent information there.
thanks a lot.sql

creating temp table inside SP

I am doing a code review of a stored procedure.
What the procedure does is to create a temp table and then based on paramete
rs sent to
the procedure, call different stored procedures which inserts into the temp
table just
created.
Are their any issues with procedures creating temp table on the fly. Any sca
lability
issues?There is a good explanation in the BOL. See "DBCC DBREINDEX".
AMB
"rkusenet" wrote:

> I am doing a code review of a stored procedure.
> What the procedure does is to create a temp table and then based on parame
ters sent to
> the procedure, call different stored procedures which inserts into the tem
p table just
> created.
> Are their any issues with procedures creating temp table on the fly. Any s
calability
> issues?
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> There is a good explanation in the BOL. See "DBCC DBREINDEX".
>
> AMB
> "rkusenet" wrote:
>|||Here's a short article - posted previously by Aaron - on the subject.
http://www.aspfaq.com/show.asp?id=2475
Enjoy.
"rkusenet" <usenet.rk@.gmail.com> wrote in message
news:3cstc2F6q880iU1@.individual.net...
>I am doing a code review of a stored procedure. What the procedure does is
>to create a temp table and then based on parameters sent to
> the procedure, call different stored procedures which inserts into the
> temp table just
> created.
> Are their any issues with procedures creating temp table on the fly. Any
> scalability
> issues?
>|||"Michael C#" <howsa@.boutdat.com> wrote in message
news:ewjaY42RFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Here's a short article - posted previously by Aaron - on the subject.
> http://www.aspfaq.com/show.asp?id=2475
>
excellent information there.
thanks a lot.

Creating tables from ASP.

Hi list, i wanto to use an ASP page to create tables from the devolopers
intranet.
Inside Code, i have the following conn string:
set objRS = CreateObject("ADODB.Recordset")
objcon.connectionstring = "Provider=SQLOLEDB.1;User
ID=sa;Password=XXXXXX;Persist Security Info=True;Initial Catalog=master;Data
Source=" & server
I have one string called sqltxt that uses the Create table statement. this
is the execution code: "3704,Operation is not allowed when the object is
closed"
Whats wrong'
Thanks.
objRS.open sqltxt, objcon
response.write err.number & "," & err.description & "<p>"
response.write sqltxt
ObjRS.CLOSE
When i try to execute this, i recieve the message :As a general suggestion, consider using a ADO command object instead of a
recordset object.
Anith|||Can u tell me how?
"Anith Sen" wrote:

> As a general suggestion, consider using a ADO command object instead of a
> recordset object.
> --
> Anith
>
>|||Assuming you really wanted to do this, you should use the Execute method off
the
Connection object like so:
(ah VBScript...Forgot how joyous it was <insert sarcasm> ;-> )
Dim oConn
Dim sSQL
Set oConn = CreateObject("ADODB.Connection")
oConn.ConnectionString = ...
oConn.Open
sSQL = "Create Table...
oConn.Execute sSQL
Set oConn = Nothing
HTH
Thomas
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:1CBE6886-30B6-4FD2-995E-DFFEC08D7123@.microsoft.com...
> Hi list, i wanto to use an ASP page to create tables from the devolopers
> intranet.
> Inside Code, i have the following conn string:
> set objRS = CreateObject("ADODB.Recordset")
> objcon.connectionstring = "Provider=SQLOLEDB.1;User
> ID=sa;Password=XXXXXX;Persist Security Info=True;Initial Catalog=master;Da
ta
> Source=" & server
> I have one string called sqltxt that uses the Create table statement. this
> is the execution code: "3704,Operation is not allowed when the object is
> closed"
> Whats wrong'
> Thanks.
> objRS.open sqltxt, objcon
> response.write err.number & "," & err.description & "<p>"
> response.write sqltxt
> ObjRS.CLOSE
> When i try to execute this, i recieve the message :|||Thanks for help Thomas, it works fine!!!
Now, i have a doubt.
When the transaction occurs, i use the err.number and err.description to see
any errors, but i coul not view anything. what do i need to see the "The
command(s) completed successfully."
Thanks again
"Thomas Coleman" wrote:

> Assuming you really wanted to do this, you should use the Execute method o
ff the
> Connection object like so:
> (ah VBScript...Forgot how joyous it was <insert sarcasm> ;-> )
> Dim oConn
> Dim sSQL
> Set oConn = CreateObject("ADODB.Connection")
> oConn.ConnectionString = ...
> oConn.Open
> sSQL = "Create Table...
> oConn.Execute sSQL
> Set oConn = Nothing
>
> HTH
>
> Thomas
>
>
> "Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
> news:1CBE6886-30B6-4FD2-995E-DFFEC08D7123@.microsoft.com...
>
>|||> When the transaction occurs, i use the err.number and err.description to seed">
> any errors, but i coul not view anything. what do i need to see the "The
> command(s) completed successfully."
From ASP? Of course not. From ADO code, you know that a command executed
successfully if an error was not thrown. If we are talking about VBScript,
you'll have to check for Err.Number right after you execute the statement li
ke
so:
sSQL = "Create Table...
oConn.Execute sSQL
If Err.Number <> 0 Then
..call error handler or do whatever in response to an error...
End If
Set oConn = Nothing
Since we're talking about the creation of the table, if the create table cal
l to
ADO fired successfully, then you should be ok to query against it or put dat
a in
it.
Thomas
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:A2338E8C-02A9-48DB-936C-325B78F9E811@.microsoft.com...
> Thanks for help Thomas, it works fine!!!
> Now, i have a doubt.
> When the transaction occurs, i use the err.number and err.description to s
ee
> any errors, but i coul not view anything. what do i need to see the "The
> command(s) completed successfully."
> Thanks again
> "Thomas Coleman" wrote:
>
<snip>|||Tinchos,

> Hi list, i wanto to use an ASP page to create tables from the devolopers
> intranet.
> ...
> Whats wrong'
Although Thomas already answered your question, you should really rethink
your approach. Very little good can come from allowing tables to be created
by the application ... why are you doing this in the first place?
-- Alex Papadimoulis|||I want to offer a service to developers (only datareader and datawriters) th
e
possibility to create custom tables or execute some scripts. I think its the
best solution to have less workload for me.
"Alex Papadimoulis" wrote:

> Tinchos,
>
> Although Thomas already answered your question, you should really rethink
> your approach. Very little good can come from allowing tables to be create
d
> by the application ... why are you doing this in the first place?
> -- Alex Papadimoulis|||So, how can i parse errors like "Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named 'table' in the database" '
Objcon.execute sqltxt
if err.number = 0 then
Response.write "Ok...
else
response.write err.number & "," & err.description & "<p>"
end if
if i try to create the table twice i dont recieve any error messages...
Thanks again!!!!|||Firstly, the system will throw an error if you execute a Create Table statem
ent
and the table exists. You need to be mindful of the *owner* of the table.
Specifically, make sure you prefix the table name with the owner like so:
Create Table dbo.TableName
(
..
)
Otherwise, the table will be created under the user's database name. So you
might get Joe.TableName, Fred.TableName and even dbo.TableName.
Secondly, you need to look at the Errors collection on the Connection object
and
trap the specific error number associated with the a duplicate table name.
Thus..
oConn.Execute sSQL
Select Case Err.Number
Case 0
'do nothing
Case 12344 <-- place the actual error number here
..tell user there was a duplicate
Case Else
..display general error
End Select
Thomas
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:AB4A1EB6-1374-43DD-9722-48BEA85AF2D5@.microsoft.com...
> So, how can i parse errors like "Server: Msg 2714, Level 16, State 6, Line
1
> There is already an object named 'table' in the database" '
> Objcon.execute sqltxt
> if err.number = 0 then
> Response.write "Ok...
> else
> response.write err.number & "," & err.description & "<p>"
> end if
> if i try to create the table twice i dont recieve any error messages...
> Thanks again!!!!

Creating Nested Tables inside of a Existing SQL Server Express Table

Hello,

Quick question, I hope, I am trying to create a table that has a column that is a nested table in SQL Server 2005 Express Edition. Any ideas how I could go about doing this?

Sincerely,

James Simpson

Straightway Technologies Inc.

The only way to "create a table that has a column that is a nested table", is to use an xml datatype.

Refer to Books Online, Topic: XML [SQL Server]

|||

As Arnie indicates, SQL Server doesn't support TABLE as a data type within a table. There is probably another way to accomlish what you're trying to do without going to XML, but it's hard to say without know what your goal is. In general, I'd suggest that you create a new table for your "nested" information and then use a 1:Many relationship between the two tables to map the nested info to the parent record.

Mike