Perhaps I was seeing things earlier, and it's really a minor thing, but when I display query results in Management Studio, isn't there a setting that turns the background of cells containing NULL to a pale yellow color? I could've sworn it used to do this, and I've searched high and low for a preference setting, but no joy.
Does this setting exist?
Thanks,
Dave
When you have SQL Server Express installed, and you drop a database in App_Code it will be attached and you can browse it with Server Explorer in Visual Studio. Same when you choose Website -> ASP.NET Configuration, you get a new ASPNETDB database in App_Code and it will be attached.My question: is it normal behaviour that I cannot see these databases in the Object Browser in SQL Server Management Studio?For the record: I also have SQL Server 2005 Dev installed, so the Management Studio I have is not the Express edition.
View 4 Replies View RelatedIs there any way to connect to a server automatically when SQL Server 2005 Management Studio starts?
I know it's not that tough to click 'Connect', but it's getting a bit annoying.
If I query sql server I get 10 results. But when I use if (myReader.Read()) I get only 7 results. I found that there was a Null field in the DB. I changed it and it worked.
The problem is I don't want to touch the database and set all null fields. There must be a way to get all results including the Null using sqlDataReader so that if (myReader.Read()) is used it does the right comparison.
// This code is called 10 times with a select * from where item="xxx"
P21Conn.Open();
SqlDataReader myReader = cmd.ExecuteReader();
if (myReader.Read()) {
thanks
Rod
Is there any way to have the results pane auto hide ? Im lazy and dont want to manually (ctl-R) hide each time i run something, then starting writing code again
View 1 Replies View Related
Hi there
I have a problem with SQL Server Management Studio after insdtall regarding querys result showing!
Problem:
I have a query i run and getting results in a grid view. But the ruslt is somehow corrupted. I guess you´d think its a sql problem but it is not. Because when i take the same query and run it in the old Query Analyzer the result is correct. For better explanation written the results below to show my problem.
In old Query Analyzer (correct)
1,00 60,00
1,00 34,80
1,00 79,20
-1,00 -79,20
In SQL Server Management Studio (corrupt)
1.000 60.00000000
1.000 34.80000000
-1.000 79.20000000
1.000 -79.20000000
What can cause this problem? Do I have to change some settings to make SQL Server Management Studio show the result correctly?
Best Regards
Michael
Is there any nice way when saving a result set grid to CSV (right click, save to CSV) to properly delimit text fields that contain commas? As it is, text data that includes commas cannot be safely exported to CSV.
Example:
SELECT 'Hi there, friend!', 'Hi' UNION ALL
SELECT 'Bye now', 'Bye'
I'm updating a process that recreates a large table every night. The table is the result of a bunch of nightly batch processes and holds a couple million records. In the past, each night at the end of the batch jobs the table would be dropped and re-created with the new data. This process was embodied in dynamic sql statements from an MFC C++ program, and my task is to move it to a SQL Server 2000 stored procedure that will be called from a .Net app. Here's the relevant code from my procedure:
sql Code:
Original
- sql Code
-- recreate new empty BatchTable table
print 'Dropping old BatchTable table...'
exec DropBatchTable --stored procedure called from old code that does a little extra work when dropping the table
-- validate drop
If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BatchTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Begin
RAISERROR( 'Unable to drop old BatchTable!',0,1) WITH NOWAIT
End Else Begin
print 'Old BatchTable dropped.'
End
print 'Creating new BatchTable...'
SELECT TOP 0 *, cast('' as char(3)) as Client, cast('' as char(12)) as ClientDB
INTO dbo.BatchTable
FROM differentDB.dbo.BatchArchives
--validate create
If Not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BatchTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Begin
RAISERROR( 'Unable to create new BatchTable!',0,1) WITH NOWAIT
End Else Begin
print 'New BatchTable Created.'
End
-- recreate new empty BatchTable table print 'Dropping old BatchTable table...' exec DropBatchTable --stored procedure called from old code that does a little extra work when dropping the table -- validate drop IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[BatchTable]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN RAISERROR( 'Unable to drop old BatchTable!',0,1) WITH NOWAIT END ELSE BEGIN print 'Old BatchTable dropped.' END print 'Creating new BatchTable...' SELECT TOP 0 *, CAST('' AS CHAR(3)) AS Client, CAST('' AS CHAR(12)) AS ClientDB INTO dbo.BatchTable FROM differentDB.dbo.BatchArchives --validate create IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[BatchTable]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN RAISERROR( 'Unable to create new BatchTable!',0,1) WITH NOWAIT END ELSE BEGIN print 'New BatchTable Created.' END
The print statements are there because the .net app will read them in and then write them to a log file. Some of the other mechanics are there to mimic the old process. The idea is to duplicate the old process first and then work on other improvements.
This works in Management studio. The .Net App reports that the old table was dropped, but when it tries to create the new table it complains that "There is already an object named 'BatchTable' in the database." I have verified that the old table is removed.
Any ideas on how to fix this?
I have a complex view in my sql 2005 database.
The view returns a column that could be null (as the result of a left outer join).
The coulmn that is returned is an integer.
Everything works fine if I run the view from SQL 2005 Management Studio.
My column value is always null if I use ADO.NET's SqlAdapter to return a DataTable.
Has anybody seen this behaviour before?
Any help appreciated.
Regards,
Paul.
Hello,
please, is there SO who can help me resolve my problem? I'm developing a simple ASP application.
I have 2 tables - T_Employees , P_Users
T_Employees = all employees of a company, P_Users = users of the system, P_Users is sub-set of T_Employees, PERNR is link
I need result of:
SELECT e.Name, u.Permitions FROM T_Employees AS e LEFT OUTER JOIN P_Users AS u ON e.PERNR = u.PERNR and (e.PERNR = 1) .
If an employee with PERNR = x is not a user (is not in P_Users) , I expect to recieve 1 row with u.Permitions=null
But what happens!
If I put this query to Management studio, I recieve 1 row (as I expect). Than I run exactly the same query in ASP page and it doesn't return any row (recordcount=0).
And what is even more strange - it worked. Suddenly I met this problem in all my pages and I can't find where the problem consist in. I always take SQL query from ASP VB into query analyzer and it works. But not on my pages.
It looks like the ASP works with different settings or as QA corrects my query before execution.
I'm totaly confused by this. Have you ever met ST like this?
Thanks for your advice
Petr
petr DOT chary@gmail DOT c o m
Hi all,I am facing an unusual issue here. I have a stored procedure, that return different set of result when I execute it from .NET component compare to when I execute it from SQL Management Studio. But as soon as I recompile the stored procedure, both will return the same results.This started to really annoying me, any thoughts or solution? Thanks very much guys
View 2 Replies View RelatedHi all,I asked a similar question a few weeks ago but I didn't explain myself to well so I was hoping for some more input.I have created a webpage in Visual web developer that contains a gridview that points to an SQL table.All works well as far as searching the table and sorting. The SQL table itself is Droped ( deleted ) and then re-created every 1 minute from a query to a linked DB2 database so that we have a "live" table of our DB2 system but in SQL.....However,There is now a requirement to be able to ADD data to this SQL table. eg: a comments field.. from the website.My procedure for re-creating the SQL DB is now useless as any data added through the website would be lost when the table gets re-created.What is the best way for me to achieve a permanent table in SQL that gets updated from the Linked server DB2 but that I can also add comments from the front end (Website Gridview)..I'm assuming some sort of update query and a join to a seperate, permanent table that contains the comments?.Please help. Ray..
View 1 Replies View Related
When I run my CLR/C# stored procedure inside Visual/BI Dev Studio, the results in a datatable are sent over pipe and displayed in output window without the grid format. I looked up inOptions and settings but couldn't find a way to change the format of output window to grid. I would like to be able to do that so I can copy/paste the results in excel.
I have written up a grid consisting of properties and units.The way it works is we have properties, and within properties there are units. They are two seperate tables.Some properties do not have any units so in the unit reference (UN_UREF) column for those records which do not have units and are NULL I would like to insert the text 'NO UNIT'.Please see below for my SQL for the grid which works fine.
create or replace view VWC_PROPMKUNIT_TEST AS ( SELECT
PR_SNAM, PR_NAME, PR_ADD1, PR_ADD2, PR_ADD3, PR_ADD4, PR_ADD5, PR_ADD6,
PR_POST, PR_OWN, UN_UREF, UN_NAME, UN_GFA
FROM PROP
LEFT JOIN UNIT
ON PR_SNAM=UN_BREF);
All I need to do now is insert 'NO UNIT' within the Unit Reference column where it is NULL.
I have table in sql server for Employee, it has(id, Employee, director),so the director field is id of the director's name,  so the director is employee and has director also, only the first director hasn't director( one Employee has not director);now, i want to use datagridviewer in c# for display as this table (id, employee, director), so the first record will be the (id, employee, '  ');i use this query:"select emp.ID ,emp.Name ,dir.Name as'director'  from table1 emp, table1 dir where (emp.director=dir.ID OR  dir.director IS NULL)"but when i display the datagridviewer , it don't display the first employee(first director) which hasn't director.
View 6 Replies View RelatedI can create a string into a local "@" variable. It can include a date/time, and text for my timing testing.
I need to be able to send that string to the results window where table output goes, rather than to the message window where a PRINT statement goes.
How do I do that?
Is it possible to run both Sql Server Management Studio: Express and full blown side by side?
I am developing with the full blown product but would like to test Management Studio Express on the same box.
Is this possible?
Thanks
Eric
I have recently installed the SQL Server Management Studio Express but I do not find Management Tools in order to create scheduled backups and shrinking of the databases. I was under the impression that this should be included in the Management Studio. I use the SQL 2005 Express for smaller customers who run the SQL on a desktop unit. I need a way to backup the data to a server machine for backup purposes. I have uninstalled and reinstalled to no avail.
I have a database in my "App_Data" folder of my visual studio project. I can view it fine in Visual Studio's built-in tools for managing a database attached to a solution. However i recently started playing around with the SQL Server Management Studio Express program. When i attach my database to Management Studio, and try to run my program it crashes. I think it might be a permissions error?!? When i detatch it and reattach it in visual studio it runs fine again. Any suggestions? ThanksJason
View 1 Replies View RelatedI am new to visual studio and I am still not sure of all its components and features.
I installed visual studio 2005 standard edition but cannot find SQL Server Management Studio?
I guess this must be because it is not included with Visual studio 2005 standard. Is it included with VS 2005 professional?
I want to add pictures of products to my shopping site using an SQL database and I’ve been told that SQL Server Management studio is required as it is a graphical tool.
How would I go about obtaining the SQL server management studio. There seems to be different versions of SQL server that it is confusing to know which one to purchase.
Will the SQL server 2005 version that comes with Visual studio standard be sufficient for me now right? I want to create a shopping site with hundreds, perhaps even thousands of products. I want to use an SQL server 2005 database. The database will include ‘dynamically generated’ product images if that is the correct terminology.
My goodness, it seems I still have so much to learn.
Thanks
I have created a database under management studio and i want it to be connected in visual studio but it failed
the error msgs said that the database can't be connected coz the database with same name exits but that is not true
How do i get the database that i am using in visual studio into my SQL server management studio?
i need to create some scripts to create stored procedures on a live server.
I have installed Visual Studio 2005 which includes SQL Server Express but not the Management Studio.
Can I install SQL Server Management Studio Express?
In Microsoft.COM website do we have a comparision between Management Studio and Management Studio express in term of functions and features of the tool ? (Not SQL Server and SQL Server Express !)
View 1 Replies View RelatedHi,
So I'm having an issue with a SqlTransaction and retrieving the auto-increment ID after an insert. Presumably this is because I have to commit the transaction before the ID's are generated, but I need the ID's before I commit the transaction (because their is another part of the transaction that requires them).
Is there any way to get these id's other than maybe getting the Max(ID) and auto-incrementing them myself in code to what they will be when the transaction is committed. If this is the best way how do I ensure no other connections can insert stuff while this transaction is going on?
Or is there a better way to do this whole thing? (i don't know really know how to do stored procedures, and presumably this would be the best way, but is there a good way to do it not Stored Procedures)
Thanks!
I am trying to insert into a SQL Server table from an Oracle database. This table has an auto-incremented field, and when I try to insert into this table I get the following error:
Code Snippet
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL
into column '<column_name>', table '<my_table>'; column does not allow nulls.
UPDATE fails.[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has
been terminated. (SQL State: 23000; SQL Code: 515)
When I turn off all the triggers in the database, I don't get this error. But that is just a test environment, the production environment will need to have those triggers activated.
Any ideas on what is going on here?
Thanks.
Just when I though I knew what I was doing. I ran into a roadblock.I have two tables, organizations & usersI am building a form online for orgs to input their own information.How do I insert into both tables when the org table needs to know what theuser_id is, but the user_id hasn't been created until the form is submitted?Any help or request for additional info would be more than great.Thanks!
View 1 Replies View RelatedHi,
I'm trying to get the text of all my SPs saved into text (*.sql) files. My first thought was to use sp_helptext and bcp the table to a text file but this isn't working (see my other post) so thought I'd try another method.
I can get the code from syscomment.text and concatenate the varchar(8000) field together in a text field. Unfortunately this isn't as easy as just text = text + newtext, how is this done?
Or am I doing it all comletely the wrong way? BTW, I have over 150 SPs so I can't save them individually.
Thanks!
Nick
Hi:
When I create a report, the wizard auto-renames fields.
E.g. if Table_COLUMN_Name=CustomerName, the list column header in the report will change to Customer Name. The wizard figures out Friendly names based on capitalization, and underscores and so on.
Similar behavior occurs even in SSAS projects Dimensions Wizard.
Is there a way to turn of this extra cuteness?
TIA
Kar
I'm having a problem with merge replication. Â My publisher and subscriber are SQL 2008 SP1, and my distributor is SQL 2008 R2. Â Currently, there is no access to the subscriber data aside from the replication agents, so there are no data changes going on there, data is only being modified on the Publisher. Â My plan was to get everything setup and do some internal testing before releasing the subscriber for use.
View 4 Replies View RelatedI have a (1) date field and (2) an auto-incrementing ID field that always throw me errors when I'm doing a programmatic insert.
(1) After doing many searches on the subject, I don't think I'm using the correct syntax for the date and can't find any suggestion that works. Would appreciate your knowledge on correct SQL syntax for inserting a "today's date" field.
(2) I'm using the following code to create a new auto-incrementing ID for each record but it seems that there should be a smoother method to force the field's value to auto-increment. Any ideas?
Private objCmd As SqlCommand Private strConn As New SqlConnection(ConfigurationManager.AppSettings("conn"))...objCmd = New SqlCommand("select max(ClientID) from tblClients", strConn)Dim intClientID As Int16 = objCmd.ExecuteNonQuery + 1
I am running a SQL stored procedure which runs 3 queries on 3 different SQL tables. What is my best option to export the results of these 3 queries to excel?
If it matters they are all SELECT queries, and at most will return < 500 rows.
Hello all,I'm a total newbie with SQL Server 2000 and I have a little problem whenmoving a database form Access 2000 to SQL Server 2000.In the Access database, each table has an auto-increment field.After importing the tables in SQL Server, all the auto-increment fieldsare turned into "int" type fields.Does anybody have an explanation for that mystery?Thanks in advance,Yan
View 3 Replies View Related