hidden views

hi ,
i have created views in database Test, in sqlServer.when i had to export Test to another database Test1, the views become hiddden.
i can't see the views and when i try to create them again, i get this msg:
There is already an object named 'view1' in the database.
so how can i resolve this problem?

thanks,

when u export view from one database to another the view is created as table in the target database. To migrate view , create script of the view and run that script in Target DB... ... do not select view in Export wizards only select tables...

if you are getting View1 already exists then :-

Check in Tables there will be a table called View1 , first drop that and run the script for view1 to create...

Madhu

|||When you choose to Generate script for the database objects, then it should have that value to drop the object before creating it, follow as suggested.|||

Well, thanks!

As u said, the views were created as tables in the target DB. So i droped them and made a new script for the views.

Myra

|||

thanks for ur help!

myra

Hidden max number of columns in Management Studio?

Hi,
The following is observed in the SQLServer 2005 Management Studio Visual
Editor:
Open table (Right Click option on a table) fails when I try it on a table
with lots of columns in it > 820 columns! Currently the table has no rows
(empty table). The same table opens fine with sql 2000 enterprise manager.
This table has been migrated/imported from sql 2000 without any errors! Other
imported tables with less number of columns can be opened in the visual
editor!
Wondering is there a hidden limit in terms of number of columns with
SQLserver 2005 Management Studio (Grid) Visual Editor?
I get SQL Executaion error
Error Source: Microsoft.VisualStudio.DataTools
Exception has been thrown by the target of an invocation
Is this a known behavior/limitation/bug? Is there a workaround?
Thanks
Kan,
See
http://www.sql-server-performance.co...ID=9016鵬
You may wish to report this as a bug at the suggested URL
http://lab.msdn.microsoft.com/produc...k/default.aspx
(also in the thread).
Steve Kass
Drew University
Kan wrote:

>Hi,
>The following is observed in the SQLServer 2005 Management Studio Visual
>Editor:
>Open table (Right Click option on a table) fails when I try it on a table
>with lots of columns in it > 820 columns! Currently the table has no rows
>(empty table). The same table opens fine with sql 2000 enterprise manager.
>This table has been migrated/imported from sql 2000 without any errors! Other
>imported tables with less number of columns can be opened in the visual
>editor!
>Wondering is there a hidden limit in terms of number of columns with
>SQLserver 2005 Management Studio (Grid) Visual Editor?
>I get SQL Executaion error
>Error Source: Microsoft.VisualStudio.DataTools
>Exception has been thrown by the target of an invocation
>Is this a known behavior/limitation/bug? Is there a workaround?
>Thanks
>
>

hi experts, can anyone have a look my simple questions, thxxx a lot!

Hi guys,

got another simple question (I am very new to sqlserver...), I have a c# desktop program, it connected to a sqlexpress 2005 server, and I need my program to connect to sql server reomotely , so I turn on TCP/IP protocol, my details are in below:

inside the server's tcp/ip on the ip tab, IP1 is set to active, enabled, the ip address is 192.168.0.3 , there is no port number for IP1, should there be a port number? dynamic port is 0

IP2 is set to active, enable with address 127.0.0.1 and also does not have a port number, dynamic port is 0

IPAll is using dynamic port 1921 but also has no port number.

for the client TCP/IP setting the default port is 1433 and it is also enabled.

I then set up a port forwarding service in my router with port (1921, I think that's the port number ?)when I run my application and sqlexpress server on the same machine, both of the following connectionstrings are working well for the program:

server= 62.31.81.210.\SQLEXPRESS,1921; user id='sa'; password='mypassword'; Database='EvoHealthSQLex'; Integrated Security=True

server= 192.168.0.3.\SQLEXPRESS,1921; user id='sa'; password='mypassword'; Database='EvoHealthSQLex'; Integrated Security=True

(I use dynamic port 1921 as the port number, don't know if it's right, but it works, but what do we need for TCP/IP setting the default port 1433? )

62.31.81.210 is the router's IP address, I attach sqlexpress's machine's(local ip address 192.168.0.3) on port forwarding service with port 1921, so above connection string is working right as I want to test wheter I can connect to sqlexpress server via real Ip address not just local Ip address.

then there is the problem: I try to run the same program on the other computer, but within the same local network (both sqlexpress server machine and application machine are connected to the same router ), I got a error message:

Login failed for user 'BRISTOL-1\Guest'

after some researching, I did the following:

I open the sql server management studio express(also free), go to 'Database' ->'EvoHealthSQLex'-> 'properties' ->'permissions'

I add a user called 'BRISTOL-1\Guest' and granted it with 'connect' and 'control' permissions, however, I still got the same error.

In 'Database' ->'EvoHealthSQLex'-> 'properties' ->'general'

I saw the owner part said 'BRISTOL-1\Ray'

the full computer name of machine running sqlexpress is 'bristol-1', and I login as user 'ray', I guess that's why the database owner is 'BRISTOL-1\Ray', on the other computer (which run the application), the full computer name is 'ray' and I login as 'rui', I don't know whether those info is useful.

Questions:

I wonder why the same error occured after I created the user 'BRISTOL-1\Guest', and I don't know why I am recoginzed as 'BRISTOL-1\Guest' on the other machine?

I wonder if I created a user, for example,say 'BRISTOL-1\ross' (does it have to use the same prefix BRISTOL-1?), when I use the program in other computer how do I specified myself as user 'BRISTOL-1\ross' or whatever ? does it have anything to do with connectionstring? as in the connection string, user id is 'sa', i don't think I should change the connectionstring to:

server= 192.168.0.3.\SQLEXPRESS,1921; user id='BRISTOL-1\ross' ; password='mypassword'; Database='EvoHealthSQLex'; Integrated Security=True - if this case is right, what the pw suppose to be?

I know those suppose to be very simple for you experts, I am just very new to sqlserver, and I can't find any related articles online...(is it because the solution is too obvious?)

I don't have anyone around me can help me so I post it here, any help will be very appreciate!!!

Ps: I just turn on the 'Guest' account in 'User Account' of 'Control Panel', but same error.

"user id" and "password" is used for SQL Authentication, while Integrated Security" is used for windows authentication. You needs to only use one of them. If both is specified, the result is not determinstics. I know some driver ignores SQL authentication in this case.

How to use configure these two authentication is a long story, I am sure you can find plenty of articles on the web or use bookonline.

In you case, if you use sa password, make sure you don't specify Integrated Security. If you want to use windows authentication, make sure that you client account has access privilieges to the SQL Server account.

|||

Hi, thanks so much for your rapid reply, I realized that I asked many stupid questions...

I just make a small progress now, after I downloaded BOL (just now), I fint out I need to greate a login rather than just create a user! :

the whole command:

CREATE LOGIN login_name { WITH <option_list1> | FROM <sources> } <sources> ::= WINDOWS [ WITH <windows_options> [ ,... ] ] | CERTIFICATE certname | ASYMMETRIC KEY asym_key_name <option_list1> ::= PASSWORD = 'password' [ HASHED ] [ MUST_CHANGE ] [ , <option_list2> [ ,... ] ] <option_list2> ::= SID = sid | DEFAULT_DATABASE = database | DEFAULT_LANGUAGE = language | CHECK_EXPIRATION = { ON | OFF} | CHECK_POLICY = { ON | OFF} | CREDENTIAL = credential_name <windows_options> ::= DEFAULT_DATABASE = database | DEFAULT_LANGUAGE = languageIn my case I did:CREATE LOGIN [BRISTOL-1\Guest] FROM WINDOWS; GOthen it works, I wonder is it anyother computer try to connect with the same sqlexpress server will be BRISTOL-1\Guest ?now I need to figure out how to connect from outside rather than local network, any suggestions :)thanks a lot Nan Tu!

HHEEEELLLPPP :o( .... My aspnet / sqlserver falls asleep??

Hi,
About every half hour, my sqlserver falls asleep.
When I try to browse my homepage (uses aspnet/sqlserver), I get this error:
Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding.
If I stop and start the SQL server service, everything works fine (for
the next half hour, then it stops again).
The service manager says, that the service is running and the "SQL
server service manager util" also shows, that the service is running.
The SQL serverlog does not state any errors.
I have 2 gigs of free hdd space.
My eventlog (this is also strange) is empty. Not a single entry in
either Application, Security or System.
The server has worked fine until now. Nobody has changed anything
(besides updating the server with MS security patches).
Any idea'
Heeeellllppp!!! :o(
M O J OLooks like you are not closing database connections.
So eventually SQL runs out of them.
George.
"M O J O" <mojo@._no_spam_delete_this_newwebsolutions.dk> wrote in message
news:eVoGG8soDHA.3612@.TK2MSFTNGP11.phx.gbl...
> Hi,
> About every half hour, my sqlserver falls asleep.
> When I try to browse my homepage (uses aspnet/sqlserver), I get this
error:
> Timeout expired. The timeout period elapsed prior to completion of the
> operation or the server is not responding.
> If I stop and start the SQL server service, everything works fine (for
> the next half hour, then it stops again).
> The service manager says, that the service is running and the "SQL
> server service manager util" also shows, that the service is running.
> The SQL serverlog does not state any errors.
> I have 2 gigs of free hdd space.
> My eventlog (this is also strange) is empty. Not a single entry in
> either Application, Security or System.
> The server has worked fine until now. Nobody has changed anything
> (besides updating the server with MS security patches).
> Any idea'
> Heeeellllppp!!! :o(
> M O J O
>|||Hi George,
Thanks for answering my post.
I've looked at the performance manager. I have max. 5 connections.
Any other suggestions?
Thanks,
M O J O
George Ter-Saakov wrote:
> Looks like you are not closing database connections.
> So eventually SQL runs out of them.
> George.
>
> "M O J O" <mojo@._no_spam_delete_this_newwebsolutions.dk> wrote in message
> news:eVoGG8soDHA.3612@.TK2MSFTNGP11.phx.gbl...
>>Hi,
>>About every half hour, my sqlserver falls asleep.
>>When I try to browse my homepage (uses aspnet/sqlserver), I get this
> error:
>>Timeout expired. The timeout period elapsed prior to completion of the
>>operation or the server is not responding.
>>If I stop and start the SQL server service, everything works fine (for
>>the next half hour, then it stops again).
>>The service manager says, that the service is running and the "SQL
>>server service manager util" also shows, that the service is running.
>>The SQL serverlog does not state any errors.
>>I have 2 gigs of free hdd space.
>>My eventlog (this is also strange) is empty. Not a single entry in
>>either Application, Security or System.
>>The server has worked fine until now. Nobody has changed anything
>>(besides updating the server with MS security patches).
>>Any idea'
>>Heeeellllppp!!! :o(
>>M O J O
>
>|||Have you tried just going into SQL Query Monitor or isql and entering a
query?
If this works your problem isn't SQL Server.
"M O J O" <mojo@._no_spam_delete_this_newwebsolutions.dk> wrote in message
news:eVoGG8soDHA.3612@.TK2MSFTNGP11.phx.gbl...
> Hi,
> About every half hour, my sqlserver falls asleep.
> When I try to browse my homepage (uses aspnet/sqlserver), I get this
error:
> Timeout expired. The timeout period elapsed prior to completion of the
> operation or the server is not responding.
> If I stop and start the SQL server service, everything works fine (for
> the next half hour, then it stops again).
> The service manager says, that the service is running and the "SQL
> server service manager util" also shows, that the service is running.
> The SQL serverlog does not state any errors.
> I have 2 gigs of free hdd space.
> My eventlog (this is also strange) is empty. Not a single entry in
> either Application, Security or System.
> The server has worked fine until now. Nobody has changed anything
> (besides updating the server with MS security patches).
> Any idea'
> Heeeellllppp!!! :o(
> M O J O
>|||>--Original Message--
>About every half hour, my sqlserver falls asleep.
Only 1% this could be the reason, but... which screen
saver do you use on the server? We had about the same
problem, when we realised that the server always went back
up every time someone touched the mouse. Apparantly
the "3D Pipes" screen saver used that much processor power
nothing that the server went virtually down. Setting the
screen saver to "Blank screen" solved the problem.
Just my 2ct.

Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set

Hi,

I have a problem with linked servers.

I have an application running against a SQLServer 2005 Express. For some limitations, I had to access from the same application to another database, but I cannot change to another server.

So I have 2 created a second instances, where the first one refers the second one and I created synonyms in the first one to access to all the objects in the second one, to emulate a database in the first instances, but running on the second one. The final idea is to move to another server, but for the testing I use another instance.

But when I try to access to the aplication database, I hav the following error: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

I searched solutions for this issue, but I only found to add SET ANSI_NULLS ON and SET ANSI_WARNINGS ON to my connection, before the queries, but I can't, because I cannot change the application.

If anyone can help me, I'd be veri greatfull

Best regards, ArielAriel,
I had a similar problem with SQL 2000 when I added a stored procedure to access the data on a linked server. I had created the stored proc through Query Analyzer and the work around was to create the stored proc through Enterprise Manager. Once I created within Enterprise Manager it worked.
A google search of this problem will provide results.|||The problem is the application have a lot of SPs, tables and views I have created the synonims.

The problem is only with the SPs?

If that, I can remove the synonims and create SPs that access the original ones.

Thanks, Ariel

HELP: How to delete a user from Reporting Services?

I have the following problem.
The system setup is as follows.
IIS and sqlserver are on the same server and I'm using windows integrated
security.
I initially created a windows login user (userA).
I then in the reporting services(RS) admin area, set the security permissions
for a directory(Dir1). The permissions were, userA is a member of the
browser role and the directory was set to the browser role.
Everything worked as it should at that point.
I then wanted to delete userA from the server. So i delete userA from the
'local users and groups' admin area in the control panel on the server.
This is when I started to have problems.
When I go back into RS to administor permissions, I tried to ammend the
'browser role' permissions for Dir1. When I try to save the changes the
following error is displayed.
'The user or group name 'userA' is not recognized. (rsUnknownUserName) '
Its seems that userA is still registered in RS.
Logically I understand why, I've only deleted userA from windows, not from RS.
My question is , How do I completely delete userA from RS.
I noticed a users table in the RS database but there are numerous other
tables which could have entries refering to userA.
Is there a way to be sure all references to userA are deleted.
The only way I could get round this was to re-generate userA as a windows
user and disable it.
This is not an ideal solution as I do not wish to have numerous disabled
windows login users.
Any suggestions would be gratefully received.
--
Message posted via http://www.sqlmonster.comSounds like you would have to delete userA from the folders on RS, then
delete the user profile. If possible, use a security group instead of
assigning individually.
"Stewart Waddell via SQLMonster.com" wrote:
> I have the following problem.
> The system setup is as follows.
> IIS and sqlserver are on the same server and I'm using windows integrated
> security.
> I initially created a windows login user (userA).
> I then in the reporting services(RS) admin area, set the security permissions
> for a directory(Dir1). The permissions were, userA is a member of the
> browser role and the directory was set to the browser role.
> Everything worked as it should at that point.
> I then wanted to delete userA from the server. So i delete userA from the
> 'local users and groups' admin area in the control panel on the server.
> This is when I started to have problems.
> When I go back into RS to administor permissions, I tried to ammend the
> 'browser role' permissions for Dir1. When I try to save the changes the
> following error is displayed.
> 'The user or group name 'userA' is not recognized. (rsUnknownUserName) '
> Its seems that userA is still registered in RS.
> Logically I understand why, I've only deleted userA from windows, not from RS.
>
> My question is , How do I completely delete userA from RS.
> I noticed a users table in the RS database but there are numerous other
> tables which could have entries refering to userA.
> Is there a way to be sure all references to userA are deleted.
> The only way I could get round this was to re-generate userA as a windows
> user and disable it.
> This is not an ideal solution as I do not wish to have numerous disabled
> windows login users.
> Any suggestions would be gratefully received.
>
> --
> Message posted via http://www.sqlmonster.com
>|||I almost never assign individual users to a role. I create a local group and
then add users (in my case domain users) to the group. I then assign a role
to that local group.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stewart Waddell via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:53C43C9C93255@.SQLMonster.com...
>I have the following problem.
> The system setup is as follows.
> IIS and sqlserver are on the same server and I'm using windows integrated
> security.
> I initially created a windows login user (userA).
> I then in the reporting services(RS) admin area, set the security
> permissions
> for a directory(Dir1). The permissions were, userA is a member of the
> browser role and the directory was set to the browser role.
> Everything worked as it should at that point.
> I then wanted to delete userA from the server. So i delete userA from the
> 'local users and groups' admin area in the control panel on the server.
> This is when I started to have problems.
> When I go back into RS to administor permissions, I tried to ammend the
> 'browser role' permissions for Dir1. When I try to save the changes the
> following error is displayed.
> 'The user or group name 'userA' is not recognized. (rsUnknownUserName) '
> Its seems that userA is still registered in RS.
> Logically I understand why, I've only deleted userA from windows, not from
> RS.
>
> My question is , How do I completely delete userA from RS.
> I noticed a users table in the RS database but there are numerous other
> tables which could have entries refering to userA.
> Is there a way to be sure all references to userA are deleted.
> The only way I could get round this was to re-generate userA as a windows
> user and disable it.
> This is not an ideal solution as I do not wish to have numerous disabled
> windows login users.
> Any suggestions would be gratefully received.
>
> --
> Message posted via http://www.sqlmonster.com

Help: about sqlserver installshield

Hi,
please
who know the sqlserver installshield how to config the database when install.
Have you docment about this?
my english is so bad, i hope everybody can understand,
thand you!What do you mean by config database while installing SQL Server?
Please explain your requirement.